הגדרת ערכי ברירת מחדל לעמודות

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

במאמר הזה מוסבר איך להגדיר, לשנות ולהסיר ערכי ברירת מחדל, ואיך ערכי ברירת המחדל פועלים עם פקודות של שפת טיפול בנתונים (DML), משימות טעינה וממשקי ה-API של הסטרימינג.

ביטוי של ערך ברירת מחדל

ביטוי ערך ברירת המחדל של עמודה חייב להיות ליטרל או אחת מהפונקציות הבאות:

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

אפשר ליצור ערך ברירת מחדל של STRUCT או ARRAY באמצעות הפונקציות האלה, כמו [CURRENT_DATE(), DATE '2020-01-01'].

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

הגדרת ערכי ברירת מחדל

כשיוצרים טבלה חדשה, אפשר להגדיר את ערך ברירת המחדל של העמודות. משתמשים בהצהרת DDL של CREATE TABLE ומוסיפים את מילת המפתח DEFAULT ואת ביטוי ערך ברירת המחדל אחרי שם העמודה והסוג. בדוגמה הבאה נוצרת טבלה בשם simple_table עם שתי עמודות STRING, a ו-b. בעמודה b יש את ערך ברירת המחדל 'hello'.

CREATE TABLE mydataset.simple_table (
  a STRING,
  b STRING DEFAULT 'hello');

כשמוסיפים נתונים ל-simple_table ומשמיטים את העמודה b, המערכת משתמשת בערך ברירת המחדל 'hello' במקום זאת. לדוגמה:

INSERT mydataset.simple_table (a) VALUES ('val1'), ('val2');

הטבלה simple_table מכילה את הערכים הבאים:

+------+-------+
| a    | b     |
+------+-------+
| val1 | hello |
| val2 | hello |
+------+-------+

אם עמודה היא מסוג STRUCT, צריך להגדיר את ערך ברירת המחדל לכל השדה STRUCT. אי אפשר להגדיר את ערך ברירת המחדל לקבוצת משנה של השדות. ערך ברירת המחדל של מערך לא יכול להיות NULL או להכיל רכיבי NULL. בדוגמה הבאה נוצרת טבלה בשם complex_table ומוגדר ערך ברירת מחדל לעמודה struct_col, שמכילה שדות בתצוגת עץ, כולל סוג ARRAY:

CREATE TABLE mydataset.complex_table (
  struct_col STRUCT<x STRUCT<x1 TIMESTAMP, x2 NUMERIC>, y ARRAY<DATE>>
    DEFAULT ((CURRENT_TIMESTAMP(), NULL),
             [DATE '2022-01-01', CURRENT_DATE()])
);

אי אפשר להגדיר ערכי ברירת מחדל שמפרים אילוץ בעמודה, כמו ערך ברירת מחדל שלא תואם לסוג עם פרמטרים או ערך ברירת מחדל של NULL כשמצב העמודה הוא REQUIRED.

שינוי ערכי ברירת המחדל

כדי לשנות את ערך ברירת המחדל של עמודה, בוחרים באחת מהאפשרויות הבאות:

המסוף

1. נכנסים לדף BigQuery במסוף Google Cloud .

   כניסה ל-BigQuery

2. בחלונית השמאלית, לוחצים על Explorer:

   

   אם החלונית הימנית לא מוצגת, לוחצים על הרחבת החלונית הימנית כדי לפתוח אותה.

3. בחלונית Explorer, מרחיבים את הפרויקט, לוחצים על Datasets ובוחרים את מערך הנתונים.

4. לוחצים על סקירה כללית > טבלאות, ואז לוחצים על טבלה.

5. לוחצים על הכרטיסייה Schema.

6. לוחצים על עריכת הסכימה. יכול להיות שתצטרכו לגלול כדי לראות את הכפתור הזה.

7. בדף Current schema, מאתרים את השדה ברמה העליונה שרוצים לשנות.

8. מזינים את ערך ברירת המחדל של השדה הזה.

9. לוחצים על Save.

SQL

משתמשים בהצהרת ה-DDL‏ ALTER COLUMN SET DEFAULT.

1. נכנסים לדף BigQuery במסוף Google Cloud .

   כניסה ל-BigQuery

2. בעורך השאילתות, מזינים את ההצהרה הבאה:

   ALTER TABLE mydataset.mytable
   ALTER COLUMN column_name SET DEFAULT default_expression;

3. לוחצים על הפעלה.

מידע נוסף על הרצת שאילתות זמין במאמר הרצת שאילתה אינטראקטיבית.

הגדרת ערך ברירת המחדל לעמודה משפיעה רק על הוספות עתידיות לטבלה. הוא לא משנה נתונים קיימים בטבלה. בדוגמה הבאה, ערך ברירת המחדל של העמודה a מוגדר כ-SESSION_USER():

ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();

אם מוסיפים שורה ל-simple_table ומשמיטים את העמודה a, המערכת משתמשת במשתמש הנוכחי בסשן.

INSERT mydataset.simple_table (b) VALUES ('goodbye');

הטבלה simple_table מכילה את הערכים הבאים:

+------------------+---------+
| a                | b       |
+------------------+---------+
| val1             | hello   |
| val2             | hello   |
| [e-mail] | goodbye |
+------------------+---------+

הסרת ערכי ברירת המחדל

כדי להסיר את ערך ברירת המחדל של עמודה, בוחרים באחת מהאפשרויות הבאות:

המסוף

1. נכנסים לדף BigQuery במסוף Google Cloud .

   כניסה ל-BigQuery

2. בחלונית השמאלית, לוחצים על Explorer:

   

   אם החלונית הימנית לא מוצגת, לוחצים על הרחבת החלונית הימנית כדי לפתוח אותה.

3. בחלונית Explorer, מרחיבים את הפרויקט, לוחצים על Datasets ובוחרים קבוצת נתונים.

4. לוחצים על סקירה כללית > טבלאות, ובוחרים את הטבלה.

5. בחלונית הפרטים, לוחצים על הכרטיסייה Schema.

6. לוחצים על עריכת הסכימה. יכול להיות שתצטרכו לגלול כדי לראות את הכפתור הזה.

7. בדף Current schema, מאתרים את השדה ברמה העליונה שרוצים לשנות.

8. מזינים NULL בתור ערך ברירת המחדל.

9. לוחצים על Save.

SQL

משתמשים בהצהרת ה-DDL‏ ALTER COLUMN DROP DEFAULT.

1. נכנסים לדף BigQuery במסוף Google Cloud .

   כניסה ל-BigQuery

2. בעורך השאילתות, מזינים את ההצהרה הבאה:

   ALTER TABLE mydataset.mytable ALTER COLUMN column_name DROP DEFAULT;

   אפשר גם להסיר את ערך ברירת המחדל מעמודה על ידי שינוי הערך שלה ל-NULL באמצעות הצהרת ה-DDL‏ ALTER COLUMN SET DEFAULT.

3. לוחצים על הפעלה.

מידע נוסף על הרצת שאילתות זמין במאמר הרצת שאילתה אינטראקטיבית.

שימוש בפקודות DML עם ערכי ברירת מחדל

אפשר להוסיף לטבלה שורות עם ערכי ברירת מחדל באמצעות פקודת ה-DML‏ INSERT. המערכת משתמשת בערך ברירת המחדל כשלא מצוין ערך לעמודה, או כשמילת המפתח DEFAULT משמשת במקום ביטוי הערך. בדוגמה הבאה נוצרת טבלה ונוספת שורה שבה כל ערך הוא ערך ברירת המחדל:

CREATE TABLE mydataset.mytable (
  x TIME DEFAULT CURRENT_TIME(),
  y INT64 DEFAULT 5,
  z BOOL);

INSERT mydataset.mytable (x, y, z) VALUES (DEFAULT, DEFAULT, DEFAULT);

הטבלה mytable נראית כך:

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
+-----------------+---+------+

לעמודה z אין ערך ברירת מחדל, ולכן NULL משמש כברירת המחדל. אם ערך ברירת המחדל הוא פונקציה, כמו CURRENT_TIME(), הוא מחושב בזמן כתיבת הערך. קריאה ל-INSERT עם ערך ברירת המחדל של העמודה x שוב תגרום לערך שונה של TIME. בדוגמה הבאה, רק לעמודה z יש ערך שמוגדר באופן מפורש, והעמודות שהושמטו משתמשות בערכי ברירת המחדל שלהן:

INSERT mydataset.mytable (z) VALUES (TRUE);

הטבלה mytable נראית כך:

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
| 22:18:29.890547 | 5 | true |
+-----------------+---+------+

אפשר לעדכן טבלה עם ערכי ברירת מחדל באמצעות פקודת ה-DML‏ MERGE. בדוגמה הבאה נוצרות שתי טבלאות, ואחת מהן מתעדכנת באמצעות הצהרת MERGE:

CREATE TABLE mydataset.target_table (
  a STRING,
  b STRING DEFAULT 'default_b',
  c STRING DEFAULT SESSION_USER())
AS (
  SELECT
    'val1' AS a, 'hi' AS b, '[e-mail]' AS c
  UNION ALL
  SELECT
    'val2' AS a, 'goodbye' AS b, SESSION_USER() AS c
);

CREATE TABLE mydataset.source_table (
  a STRING DEFAULT 'default_val',
  b STRING DEFAULT 'Happy day!')
AS (
  SELECT
    'val1' AS a, 'Good evening！' AS b
  UNION ALL
  SELECT
    'val3' AS a, 'Good morning!' AS b
);

MERGE mydataset.target_table T
USING mydataset.source_table S
ON T.a = S.a
WHEN NOT MATCHED THEN
  INSERT(a, b) VALUES (a, DEFAULT);

התוצאה היא:

+------+-----------+--------------------+
| a    | b         | c                  |
+------+-----------+--------------------+
| val1 | hi        | [e-mail]     |
| val2 | goodbye   | [e-mail] |
| val3 | default_b | [e-mail] |
+------+-----------+--------------------+

אפשר לעדכן טבלה עם ערכי ברירת מחדל באמצעות פקודת ה-DML‏ UPDATE. בדוגמה הבאה מעדכנים את הטבלה source_table כך שכל שורה בעמודה b תהיה שווה לערך ברירת המחדל שלה:

UPDATE mydataset.source_table
SET b =  DEFAULT
WHERE TRUE;

התוצאה היא:

+------+------------+
| a    | b          |
+------+------------+
| val1 | Happy day! |
| val3 | Happy day! |
+------+------------+

הוספת טבלה

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

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT FALSE AS z UNION ALL SELECT FALSE AS Z'

בטבלה mytable נעשה שימוש בערכי ברירת המחדל של העמודות x ו-y:

+-----------------+---+-------+
|        x        | y |   z   |
+-----------------+---+-------+
| 22:13:24.799555 | 5 |  NULL |
| 22:18:29.890547 | 5 |  true |
| 23:05:18.841683 | 5 | false |
| 23:05:18.841683 | 5 | false |
+-----------------+---+-------+

טעינת נתונים

אפשר לטעון נתונים לטבלה עם ערכי ברירת מחדל באמצעות הפקודה bq load או ההצהרה LOAD DATA. ערכי ברירת המחדל חלים כשהנתונים שנטענו מכילים פחות עמודות מאשר טבלת היעד. ערכי NULL בנתונים שנטענו לא מומרים לערכי ברירת מחדל.

לפורמטים בינאריים, כמו AVRO,‏ Parquet או ORC, יש סכימות של קבצים מקודדים. אם בסכימת הקובץ חסרות עמודות מסוימות, המערכת תשתמש בערכי ברירת המחדל.

לפורמטים של טקסט, כמו JSON ו-CSV, אין סכימת קבצים מקודדת. כדי לציין את הסכימה שלהם באמצעות כלי שורת הפקודה של BigQuery, אתם יכולים להשתמש בדגל --autodetect או לספק סכימת JSON. כדי לציין את הסכימה שלהן באמצעות ההצהרה LOAD DATA, צריך לספק רשימה של עמודות. הדוגמה הבאה טוענת רק את העמודה a מקובץ CSV:

LOAD DATA INTO mydataset.insert_table (a)
FROM FILES(
  uris = ['gs://test-bucket/sample.csv'],
  format = 'CSV');

שימוש ב-Storage Write API ‏ (gRPC)

‫Storage Write API ‏ (gRPC) מאכלס ערכי ברירת מחדל רק אם בסכימה של זרם הכתיבה חסר שדה שכלול בסכימה של טבלת היעד. במקרה כזה, השדה החסר מאוכלס בערך ברירת המחדל בעמודה בכל כתיבה. אם השדה קיים בסכימה של זרם הכתיבה אבל חסר בנתונים עצמם, השדה החסר מאוכלס בערך NULL. לדוגמה, נניח שאתם כותבים נתונים בטבלה ב-BigQuery עם הסכימה הבאה:

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

סכימת זרם הכתיבה הבאה לא כוללת את השדה c שקיים בטבלת היעד:

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
]

נניח שאתם מעבירים בסטרימינג את הערכים הבאים לטבלה:

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

התוצאה היא:

+-------+-------+-----------+
| a     | b     | c         |
+-------+-------+-----------+
| val_a | val_b | default_c |
| val_a | NULL  | default_c |
+-------+-------+-----------+

הסכימה של זרם הכתיבה מכילה את השדה b, ולכן לא נעשה שימוש בערך ברירת המחדל default_b גם כשלא צוין ערך לשדה. מכיוון שהסכימה של זרם הכתיבה לא מכילה את השדה c, כל שורה בעמודה c מאוכלסת בערך ברירת המחדל של טבלת היעד default_c.

הסכימה הבאה של זרם הכתיבה תואמת לסכימה של הטבלה שאליה אתם כותבים:

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
  {
    "name": "c",
    "type": "STRING",
  }
]

נניח שאתם מעבירים בסטרימינג את הערכים הבאים לטבלה:

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

בסכימה של זרם הכתיבה לא חסרים שדות שכלולים בטבלת היעד, ולכן לא חלים ערכי ברירת המחדל של אף אחת מהעמודות, גם אם השדות מאוכלסים בנתונים שמועברים בסטרימינג:

+-------+-------+------+
| a     | b     | c    |
+-------+-------+------+
| val_a | val_b | NULL |
| val_a | NULL  | NULL |
+-------+-------+------+

אפשר לציין הגדרות של ערכי ברירת מחדל ברמת החיבור ב-default_missing_value_interpretation בתוך ההודעה AppendRowsRequest. אם הערך מוגדר כ-DEFAULT_VALUE, הערך החסר יקבל את ערך ברירת המחדל גם כשהעמודה מוצגת בסכימת המשתמש.

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

לדוגמה, המפה {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE} מציינת שכל הערכים החסרים ב-col1 מפורשים כ-NULL, וכל הערכים החסרים ב-col2 מפורשים כערך ברירת המחדל שמוגדר ל-col2 בסכימת הטבלה.

אם שדה לא נמצא במפה הזו ויש בו ערכים חסרים, הערכים החסרים מפורשים כ-NULL.

המפתחות יכולים להיות רק שמות של עמודות ברמה העליונה. המפתחות לא יכולים להיות שדות משנה של מבנה, כמו col1.subfield1.

שימוש ב-BigQuery Storage Write API ‏ (REST)

‫Storage Write API (REST) מאכלס ערכי ברירת מחדל ברמת השורה כשנתונים נכתבים בטבלה. אם בשורה מסוימת חסרות עמודות עם ערכי ברירת מחדל, ערכי ברירת המחדל יחולו על העמודות האלה.

לדוגמה, נניח שיש לכם את סכימת הטבלה הבאה:

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

נניח שאתם מעבירים בסטרימינג את הערכים הבאים לטבלה:

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}
{}

התוצאה היא:

+-------+------------+-----------+
| a     | b          | c         |
+-------+------------+-----------+
| val_a | val_b      | default_c |
| val_a | default_b  | default_c |
| NULL  | default_b  | default_c |
+-------+------------+-----------+

השורה הראשונה שנוספה לא מכילה ערך בשדה c, ולכן ערך ברירת המחדל default_c נכתב בעמודה c. השורה השנייה שנוספה לא מכילה ערכים בשדות b או c, ולכן ערכי ברירת המחדל שלהם נכתבים בעמודות b ו-c. השורה השלישית שהוספתם לא מכילה ערכים. הערך שנכתב בעמודה a הוא NULL כי לא הוגדר ערך ברירת מחדל אחר. ערכי ברירת המחדל default_b ו-default_c נכתבים בעמודות b ו-c.

הצגת ערכי ברירת המחדל

כדי לראות את ערך ברירת המחדל של עמודה, שולחים שאילתה לתצוגה INFORMATION_SCHEMA.COLUMNS. השדה של העמודה column_default מכיל את ערך ברירת המחדל של העמודה. אם לא מוגדר ערך ברירת מחדל, הערך הוא NULL. בדוגמה הבאה מוצגים שמות העמודות וערכי ברירת המחדל של הטבלה mytable:

SELECT
  column_name,
  column_default
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

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

+-------------+----------------+
| column_name | column_default |
+-------------+----------------+
| x           | CURRENT_TIME() |
| y           | 5              |
| z           | NULL           |
+-------------+----------------+

מגבלות

• אפשר לקרוא מטבלאות עם ערכי ברירת מחדל באמצעות SQL מדור קודם, אבל אי אפשר לכתוב לטבלאות עם ערכי ברירת מחדל באמצעות SQL מדור קודם.
• אי אפשר להוסיף עמודה חדשה עם ערך ברירת מחדל לטבלה קיימת. עם זאת, אפשר להוסיף את העמודה בלי ערך ברירת מחדל, ואז לשנות את ערך ברירת המחדל שלה באמצעות הצהרת ה-DDL‏ ALTER COLUMN SET DEFAULT.
• אי אפשר להעתיק טבלת מקור ולהוסיף אותה לטבלת יעד שיש בה יותר עמודות מאשר בטבלת המקור, ולעמודות הנוספות יש ערכי ברירת מחדל. במקום זאת, אפשר להריץ את INSERT destination_table SELECT * FROM source_table כדי להעתיק את הנתונים.

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

• למידע נוסף על טעינת נתונים ל-BigQuery, קראו את המאמר מבוא לטעינת נתונים.