עדכון נתונים בטבלה מחולקת למחיצות באמצעות DML

בדף הזה מובאת סקירה כללית על התמיכה בשפת טיפול בנתונים (DML) בטבלאות עם חלוקה למחיצות.

מידע נוסף על DML:

• מבוא ל-DML
• תחביר DML
• עדכון נתוני טבלה באמצעות שפת טיפול בנתונים

טבלאות שמשמשות בדוגמאות

ההגדרות הבאות של סכימת JSON מייצגות את הטבלאות שמופיעות בדוגמאות בדף הזה.

‫mytable: טבלה מחולקת למחיצות (Partitions) לפי זמני כתיבת הנתונים

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
    ]

‫mytable2: טבלה רגילה (לא מחולקת למחיצות)

    [
      {"name": "id", "type": "INTEGER"},
      {"name": "ts", "type": "TIMESTAMP"}
    ]

‫mycolumntable: טבלה מחולקת למחיצות שמחולקת למחיצות באמצעות העמודה ts TIMESTAMP

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
      {"name": "field3", "type": "BOOLEAN"}
      {"name": "ts", "type": "TIMESTAMP"}
    ]

בדוגמאות שבהן מופיע COLUMN_ID, מחליפים אותו בשם העמודה שרוצים לבצע בה פעולה.

הוספת נתונים

משתמשים בפקודת INSERT DML כדי להוסיף שורות לטבלה מחולקת למחיצות.

הוספת נתונים לטבלאות מחולקות למחיצות (Partitions) לפי זמני כתיבת הנתונים

כשמשתמשים בפקודת DML כדי להוסיף שורות לטבלה מחולקת למחיצות (Partitions) לפי זמני כתיבת הנתונים, אפשר לציין את המחיצה שאליה השורות יתווספו. כדי להפנות למחיצה, משתמשים בעמודה הווירטואלית _PARTITIONTIME.

לדוגמה, ההצהרה הבאה INSERT מוסיפה שורה למחיצה של 1 במאי 2017 של mytable – “2017-05-01”.

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01"),
  1,
  "one"

אפשר להשתמש רק בחותמות זמן שתואמות לגבולות תאריכים מדויקים. לדוגמה, פקודת ה-DML הבאה מחזירה שגיאה:

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01 21:30:00"),
  1,
  "one"

הערה: אפשר לשנות את עמודת ה-pseudocolumn _PARTITIONTIME גם באמצעות הצהרת UPDATE.

הוספת נתונים לטבלאות מחולקות למחיצות

הוספת נתונים לטבלה עם חלוקה למחיצות באמצעות DML זהה להוספת נתונים לטבלה ללא חלוקה למחיצות.

לדוגמה, ההצהרה הבאה INSERT מוסיפה שורות לטבלה מחולקת למחיצות (Partitions) mycolumntable על ידי בחירת נתונים מ-mytable2 (טבלה ללא חלוקה למחיצות).

INSERT INTO
  project_id.dataset.mycolumntable (ts,
    field1)
SELECT
  ts,
  id
FROM
  project_id.dataset.mytable2

מחיקת נתונים

משתמשים במשפט DML DELETE כדי למחוק שורות מטבלה מחולקת למחיצות.

מחיקת נתונים בטבלאות מחולקות למחיצות (Partitions) לפי זמני כתיבת הנתונים

ההצהרה הבאה DELETE מוחקת את כל השורות מהמחיצה "2017-06-01" של mytable מ-1 ביוני 2017, שבה field1 שווה ל-21. אפשר להפנות למחיצה באמצעות עמודת ה-pseudocolumn‏ _PARTITIONTIME.

DELETE
  project_id.dataset.mytable
WHERE
  field1 = 21
  AND _PARTITIONTIME = "2017-06-01"

מחיקת נתונים בטבלאות מחולקות

מחיקת נתונים בטבלה עם חלוקה למחיצות באמצעות DML זהה למחיקת נתונים מטבלה ללא חלוקה למחיצות.

לדוגמה, ההצהרה הבאה של DELETE מוחקת את כל השורות מהמחיצה "2017-06-01" של mycolumntable מ-1 ביוני 2017, שבה field1 שווה ל-21.

DELETE
  project_id.dataset.mycolumntable
WHERE
  field1 = 21
  AND DATE(ts) = "2017-06-01"

שימוש בפקודת DML DELETE למחיקת מחיצות

אם משפט DELETE שעומד בדרישות מכסה את כל השורות במחיצה, המערכת של BigQuery מסירה את כל המחיצה. ההסרה הזו מתבצעת ללא סריקת בייטים או שימוש במשבצות. בדוגמה הבאה של DELETE statement מוצג כל החלק של מסנן בעמודה _PARTITIONDATE pseudocolumn:

DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

סיבות נפוצות לפסילה

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

• כיסוי חלקי של מחיצה
• הפניות לעמודות שלא משמשות לחלוקה
• נתונים שהוטמעו לאחרונה באמצעות BigQuery Storage Write API או legacy streaming API
• מסננים עם שאילתות משנה או פרדיקטים לא נתמכים

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

עסקה בכמה כרטיסי אשראי

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

DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;

-- find the partition which we want to replace
SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging);

-- delete the entire partition from mytable
DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY;

-- insert the new data into the same partition in mytable
INSERT INTO mydataset.mytable
SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY;

COMMIT TRANSACTION;

עדכון נתונים

משתמשים בהצהרת UPDATE כדי לעדכן שורות בטבלה מחולקת למחיצות.

עדכון נתונים בטבלאות מחולקות למחיצות (Partitions) לפי זמני כתיבת הנתונים

ההצהרה הבאה של UPDATE מעבירה שורות ממחיצה אחת לאחרת. שורות במחיצה “2017-05-01” (1 במאי 2017) של mytable שבהן field1 שווה ל-21 מועברות למחיצה “2017-06-01” (1 ביוני 2017).

UPDATE
  project_id.dataset.mytable
SET
  _PARTITIONTIME = "2017-06-01"
WHERE
  _PARTITIONTIME = "2017-05-01"
  AND field1 = 21

עדכון נתונים בטבלאות עם חלוקה למחיצות

עדכון נתונים בטבלה מחולקת למחיצות באמצעות DML זהה לעדכון נתונים מטבלה שלא מחולקת למחיצות. לדוגמה, ההצהרה הבאה UPDATE מעבירה שורות ממחיצה אחת לאחרת. שורות במחיצה “2017-05-01” של mytable מ-1 במאי 2017, שבהן field1 שווה ל-21, מועברות למחיצה “2017-06-01” מ-1 ביוני 2017.

UPDATE
  project_id.dataset.mycolumntable
SET
  ts = "2017-06-01"
WHERE
  DATE(ts) = "2017-05-01"
  AND field1 = 21

DML בטבלאות מחולקות למחיצות (Partitions) לפי שעה, חודש ושנה

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

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'

או דוגמה אחרת לטבלאות עם חלוקה למחיצות עם עמודה DATETIME:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    dt_column BETWEEN DATETIME("2020-01-01")
    AND DATETIME("2020-05-01");'

שימוש בהצהרת MERGE

משתמשים בMERGE פקודת DML כדי לשלב פעולות INSERT, UPDATE ו-DELETE בטבלה מחולקת למחיצות לפקודה אחת ולבצע אותן באופן אטומי.

הסרת מחיצות מיותרות כשמשתמשים בהצהרת MERGE

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

כל אחת מהדוגמאות הבאות שולחת שאילתה לטבלה מחולקת למחיצות (Partitions) לפי זמני כתיבת הנתונים באמצעות עמודת ה-Pseudocolumn‏ _PARTITIONTIME כמסנן.

שימוש בשאילתת משנה כדי לסנן נתוני מקור

בדוגמה הבאה של משפט MERGE, שאילתת המשנה בסעיף USING מסננת לפי פסאודו-העמודה _PARTITIONTIME בטבלת המקור.

MERGE dataset.target T
USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED THEN
  DELETE

כשבודקים את תוכנית הביצוע של השאילתה, רואים שהשאילתה המשנית מופעלת קודם. רק השורות במחיצה '2018-01-01' בטבלת המקור נסרקות. הנה השלב הרלוונטי בתוכנית השאילתה:

READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)

שימוש במסנן בsearch_condition של when_clause

אם search_condition מכיל מסנן, אופטימיזציית השאילתות מנסה לצמצם את המחיצות. לדוגמה, בהצהרת MERGE הבאה, כל פסקה של WHEN MATCHED ו-WHEN NOT MATCHED מכילה מסנן בעמודה הווירטואלית _PARTITIONTIME.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
  DELETE

במהלך שלב הצירוף, רק המחיצות הבאות נסרקות בטבלת היעד: '2018-01-01', ‏'2018-01-02' ו-'2018-01-03' – כלומר, איחוד של כל המסננים search_condition.

מתוכנית הביצוע של השאילתה:

READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))

עם זאת, בדוגמה הבאה, פסוקית WHEN NOT MATCHED BY SOURCE לא כוללת ביטוי סינון:

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = COLUMN_ID + 1

כדי לחשב את סעיף WHEN NOT MATCHED BY SOURCE, השאילתה הזו צריכה לסרוק את כל טבלת היעד. כתוצאה מכך, לא מתבצעת הסרה של מחיצות.

שימוש בפרדיקט קבוע עם הערך False ב-merge_condition

אם משתמשים יחד בסעיפים WHEN NOT MATCHED ו-WHEN NOT MATCHED BY SOURCE, בדרך כלל BigQuery מבצע איחוד חיצוני מלא, שלא ניתן לצמצם. עם זאת, אם תנאי המיזוג משתמש בפרדיקט קבוע של false, ‏ BigQuery יכול להשתמש בתנאי המסנן כדי לבצע pruning של מחיצות. מידע נוסף על השימוש בפרדיקטים קבועים של false מופיע בתיאור של סעיף merge_condition במאמרי העזרה בנושא הצהרת MERGE.

בדוגמה הבאה, מתבצעת סריקה רק של המחיצה '2018-01-01' בטבלאות היעד והמקור.

MERGE dataset.target T
USING dataset.source S
ON FALSE
WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN
  INSERT(COLUMN_ID) VALUES(COLUMN_ID)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
  DELETE

שימוש במסנן ב-merge_condition

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

בדוגמה הבאה, הביטוי merge_condition משמש כפרדיקט לאיחוד בין טבלאות המקור והיעד. אופטימיזציית השאילתות יכולה להעביר את התנאי הזה למטה כשהיא סורקת את שתי הטבלאות. כתוצאה מכך, השאילתה סורקת רק את המחיצה '2018-01-01'בטבלת היעד ובטבלת המקור.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND
  T._PARTITIONTIME = '2018-01-01' AND
  S._PARTITIONTIME = '2018-01-01'
WHEN MATCHED THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

בדוגמה הבאה, התנאי merge_condition לא מכיל פרדיקט לטבלת המקור, ולכן אי אפשר לבצע גיזום של המחיצה בטבלת המקור. ההצהרה מכילה פרדיקט לטבלת היעד, אבל ההצהרה משתמשת בסעיף WHEN NOT MATCHED BY SOURCE ולא בסעיף WHEN MATCHED. כלומר, השאילתה צריכה לסרוק את כל טבלת היעד כדי למצוא את השורות שלא תואמות.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND T._PARTITIONTIME = '2018-01-01'
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

מגבלות

מידע על מגבלות DML מופיע בקטע Limitations בדף DML reference.

מכסות

מידע על מכסות של DML זמין במאמר הצהרות DML בדף מכסות ומגבלות.

תמחור

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

אבטחת טבלאות

כדי לשלוט בגישה לטבלאות ב-BigQuery, אפשר לעיין במאמר בנושא שליטה בגישה למשאבים באמצעות IAM.

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

• איך יוצרים טבלאות עם מחיצות
• איך שולחים שאילתות לטבלאות עם חלוקה למחיצות
• מבוא ל-DML
• איך כותבים פקודות DML באמצעות תחביר DML