ציון עמודת זהות

במאמר הזה נסביר איך ליצור ולהשתמש בעמודות זהות, שלפעמים נקראות עמודות עם הגדלה אוטומטית, שמשמשות ליצירה ולתחזוקה של מפתחות ראשיים בטבלאות. כשמכניסים שורה לטבלה שיש בה עמודת זהות, מערכת BigQuery יוצרת ערך ייחודי של מספר שלם לעמודה הזו.

סקירה כללית

עמודת זהות היא עמודה INT64 שאוכלסת בערכים ייחודיים שנוצרו על ידי המערכת.

תרחיש השימוש העיקרי בעמודות זהות הוא יצירה של מפתחות ראשיים. אפשר גם ליצור מפתחות ראשיים באמצעות הפונקציה GENERATE_UUID כדי ליצור מחרוזות ייחודיות, אבל בדרך כלל עדיף להשתמש בעמודות זהות מהסיבות הבאות:

  • ערכים מסוג integer (מספר שלם) דורשים פחות נפח אחסון מאשר ערכים מסוג string (מחרוזת).
  • שימוש במספרים שלמים לצירופי טבלאות יעיל יותר משימוש במחרוזות.

הערכים של עמודת זהות נוצרים על סמך ערך התחלתי שמגדיר את הערך הראשון, וערך תוספת שמגדיר את ההפרש המינימלי בין ערכים שנוצרים ברצף.

לערכים שנוצרו בעמודת הזהות יש את המאפיינים הבאים:

  • ייחודי. הערכים שנוצרים באופן אוטומטי הם ייחודיים בטבלה.
  • סדר חלקי. אין ערובה לכך שהערכים שנוצרו יהיו בסדר עולה או יורד.
  • דליל. אין ערובה לכך שהערכים שנוצרו יהיו עוקבים. יכול להיות שחלק מהערכים ידלגו, אבל הערכים בעמודת הזהות תמיד יהיו בהפרש של כפולה של הגידול שציינתם.

מגבלות

  • בטבלה יכולה להיות עמודת זהות אחת לכל היותר.
  • אפשר לקרוא מטבלאות עם עמודות זהות באמצעות SQL מדור קודם, אבל אי אפשר לכתוב לטבלאות עם עמודות זהות באמצעות SQL מדור קודם.
  • אי אפשר להשתמש באשכול או בחלוקה למחיצות בעמודת זהות.

  • אי אפשר לבצע את הפעולות הבאות להעתקת טבלה אם בטבלת המקור או בטבלת היעד יש עמודת זהות:

    • העתקת טבלה עם WRITE_APPEND או WRITE_TRUNCATE write disposition
    • העתקה של טבלה עם כמה מקורות

  • אי אפשר להזרים נתונים באמצעות Storage Write API‏ (gRPC) או באמצעות השיטה tabledata.insertAll של API לטבלאות עם עמודות זהות.

יצירת עמודות של זהויות

אפשר ליצור עמודת זהות כשיוצרים טבלה חדשה באמצעות הצהרת DDL‏ CREATE TABLE. משתמשים בפסקה GENERATED AS IDENTITY כדי להגדיר עמודה INT64 כעמודת זהות. בטבלה יכולה להיות עמודת זהות אחת לכל היותר. אפשר לציין אחד ממצבי היצירה הבאים, שקובעים אם אפשר להזין ערכים בעמודת הזהות באופן ידני:

  • ‫GENERATED ALWAYS AS IDENTITY: הערכים תמיד נוצרים על ידי המערכת. אי אפשר לספק ערך משלכם כשמוסיפים או מעדכנים נתונים בעמודה הזו. אם לא מציינים את ALWAYS או BY DEFAULT, המערכת משתמשת ב-ALWAYS.

  • ‫GENERATED BY DEFAULT AS IDENTITY: אפשר להוסיף או לשנות ערכים בעמודת הזהות. ב-BigQuery לא נאכפת הייחודיות של הערכים שמוסיפים או משנים.

    אם לא מציינים את העמודה או מספקים את הערך NULL כשמכניסים נתונים, BigQuery יוצר ערך באופן אוטומטי. העמודה של הזהות לא יכולה להכיל את הערך NULL. אם רוצים להשתמש בערך שנוצר בהצהרת INSERT,‏ MERGE או UPDATE, אפשר להשתמש במילת המפתח DEFAULT או NULL.

בדוגמה הבאה נוצרת הטבלה mydataset.id_table עם עמודת זהות id שמתחילה ב-0 ועולה ב-5:

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

הוספת מאפיין עמודה של זהות לעמודה

כדי לשנות עמודה קיימת כך שתפיק ערכי זהות, משתמשים בהצהרת ה-DDL‏ ALTER TABLE ALTER COLUMN SET GENERATED. ההצהרה הזו משנה עמודה קיימת של INT64 לעמודת זהות. היא לא ממלאת מחדש ערכים בשורות קיימות בעמודת הזהות.

שימוש בפקודות DML עם עמודות זהות

אפשר להשתמש בפקודות DML כמו INSERT,‏ MERGE ו-UPDATE עם עמודות זהות. בקטעים הבאים נעשה שימוש בטבלה mydataset.mytable שיש לה עמודת זהות בשם id ועמודת מחרוזת בשם data:

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

הוספת נתונים

כשמוסיפים נתונים לטבלה עם עמודת זהות, אפשר להשמיט את עמודת הזהות מרשימת העמודות כדי ליצור ערך בשבילה. ההצהרה הבאה INSERT משמיטה את העמודה id, ו-BigQuery יוצר עבורה ערכים:

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

התוצאה דומה לתוצאה הבאה, אבל סדר ההקצאה של הערכים שנוצרו לשורות עשוי להיות שונה:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

אם עמודת הזהות מוגדרת באמצעות GENERATED BY DEFAULT AS IDENTITY, אפשר לציין ערך משלכם לעמודה. אפשר גם להשתמש במילת המפתח DEFAULT או ב-NULL כדי ש-BigQuery ייצור ערך.

ההצהרה הבאה INSERT מספקת ערך לשורה אחת, ומשתמשת ב-DEFAULT או ב-NULL כדי ליצור ערכים לשתי השורות האחרות:

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

התוצאה אמורה להיראות כך:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

אם עמודת זהות מוגדרת עם GENERATED ALWAYS AS IDENTITY, אפשר להשתמש במילת המפתח DEFAULT בלבד כדי ש-BigQuery ייצור ערך. אי אפשר לספק ערך משלכם או להשתמש ב-NULL.

מיזוג נתונים

אפשר להשתמש בהצהרה MERGE כדי למזג נתונים לתוך טבלה עם עמודת זהות. אם בעמודת הזהות מוגדר מצב יצירה GENERATED BY DEFAULT AS IDENTITY, אפשר להשתמש במילות המפתח DEFAULT או NULL כדי ליצור ערך כשמוסיפים או מעדכנים נתונים כחלק מהצהרת MERGE.

בדוגמה הבאה, המערכת ממזגת את mydataset.source_table עם mydataset.mytable, מוסיפה שורה חדשה אם אין התאמה בעמודה data ומעדכנת את העמודה id לערך חדש שנוצר אם יש התאמה:

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

התוצאה אמורה להיראות כך:

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

אם עמודת הזהות משתמשת במצב יצירה GENERATED ALWAYS AS IDENTITY, אי אפשר לכלול את עמודת הזהות באף סעיף של עדכון מיזוג. כדי להשתמש במשפט merge insert, אפשר להשמיט את עמודת הזהות מרשימת העמודות או להשתמש במילת המפתח DEFAULT.

עדכון נתונים

אפשר להשתמש בהצהרה UPDATE כדי לעדכן ערכים בעמודת זהות שמשתמשת במצב יצירה GENERATED BY DEFAULT AS IDENTITY. אפשר להשתמש במילות המפתח DEFAULT או NULL כדי ליצור ערך חדש.

בדוגמה הבאה, כל הערכים בעמודה id מתעדכנים לערכים חדשים שנוצרו:

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

התוצאה אמורה להיראות כך:

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

אם בעמודת הזהות נעשה שימוש במצב יצירה GENERATED ALWAYS AS IDENTITY, אי אפשר לעדכן את עמודת הזהות.

הוספה לטבלה

אפשר להשתמש בפקודה bq query עם הדגל --append_table כדי להוסיף את תוצאות השאילתה לטבלת יעד שיש לה עמודת זהות. אם השאילתה לא כוללת את עמודת הזהות, נוצר בשבילה ערך.

בדוגמה הבאה, הנתונים של עמודה data מצורפים רק ל-mydataset.mytable:

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

נוספת שורה חדשה עם ערך id שנוצר ל-mydataset.mytable.

טעינת נתונים

אפשר לטעון נתונים לטבלה עם עמודת זהות באמצעות הפקודה bq load או ההצהרה LOAD DATA. אם עמודת הזהות לא מופיעה בנתוני המקור או בסכימה, המערכת יוצרת עבורה ערכים. אם עמודת הזהות היא GENERATED ALWAYS AS IDENTITY, צריך להשמיט אותה.

בדוגמה הבאה, הנתונים נטענים מקובץ CSV‏ data.csv אל mydataset.mytable. הקובץ מכיל רק נתונים בעמודה data:

"X"
"Y"

הפקודה bq load הבאה טוענת את data.csv אל mydataset.mytable, בלי שורת הכותרת ועם ציון העמודה data בלבד בסכימה:

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

משימת הטעינה יוצרת id ערכים לשורות החדשות.

הסרת המאפיין של עמודת הזהות

אפשר להסיר את מאפיין הזהות מעמודה באמצעות הצהרת DDL‏ ALTER TABLE ALTER COLUMN DROP GENERATED.

בדוגמה הבאה מוסרות מאפייני עמודת הזהות מהעמודה id בטבלה mydataset.mytable:

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

מידע על עמודות של זהויות

כדי לראות את הגדרת עמודת הזהות של עמודה מסוימת, שולחים שאילתה לתצוגה INFORMATION_SCHEMA.COLUMNS.

בדוגמה הבאה מוצגים פרטי עמודת הזהות של עמודות ב-mydataset.mytable:

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

התוצאה אמורה להיראות כך:

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

אפשר גם להריץ שאילתה בעמודה ddl של התצוגה INFORMATION_SCHEMA.TABLES כדי לראות את ההגדרה של עמודת הזהות בהצהרת ה-DDL‏ CREATE TABLE של טבלה.

המאמרים הבאים