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

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

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

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

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

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

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

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

כשיוצרים טבלה חדשה, אפשר להגדיר את ערך ברירת המחדל של העמודות. משתמשים בCREATE TABLE הצהרת DDL ומוסיפים את מילת המפתח 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.

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

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

הגדרת ערך ברירת מחדל לעמודה משפיעה רק על הוספות עתידיות לטבלה. הוא לא משנה נתונים קיימים בטבלה. בדוגמה הבאה, ערך ברירת המחדל של העמודה 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   |
| user@example.com | goodbye |
+------------------+---------+

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

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

שימוש בפקודות 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, '123@google.com' 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        | 123@google.com     |
| val2 | goodbye   | default@google.com |
| val3 | default_b | default@google.com |
+------+-----------+--------------------+

אפשר לעדכן טבלה עם ערכי ברירת מחדל באמצעות פקודת 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');

Write API

ממשק Storage Write API מאכלס ערכי ברירת מחדל רק אם בסכימה של זרם הכתיבה חסר שדה שנכלל בסכימה של טבלת היעד. במקרה כזה, השדה החסר יאוכלס בערך ברירת המחדל בעמודה לכל פעולת כתיבה. אם השדה קיים בסכימת מקור הנתונים לכתיבה אבל חסר בנתונים עצמם, השדה החסר יאוכלס בערך 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.

שימוש ב-API method ‏insertAll

השיטה tabledata.insertAll ב-API מאכלסת ערכי ברירת מחדל ברמת השורה כשנתונים נכתבים לטבלה. אם בשורה מסוימת חסרות עמודות עם ערכי ברירת מחדל, המערכת תחיל את ערכי ברירת המחדל על העמודות האלה.

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

[
  {
    "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 מדור קודם.
• אי אפשר להוסיף עמודה חדשה עם ערך ברירת מחדל לטבלה קיימת. עם זאת, אפשר להוסיף את העמודה בלי ערך ברירת מחדל, ואז לשנות את ערך ברירת המחדל שלה באמצעות הצהרת ALTER COLUMN SET DEFAULT DDL.
• אי אפשר להעתיק טבלת מקור ולהוסיף אותה לטבלת יעד שיש בה יותר עמודות מאשר בטבלת המקור, והעמודות הנוספות מכילות ערכי ברירת מחדל. במקום זאת, אפשר להריץ את הפקודה INSERT destination_table SELECT * FROM source_table כדי להעתיק את הנתונים.

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

• מידע נוסף על טעינת נתונים ל-BigQuery זמין במאמר מבוא לטעינת נתונים.