עדכון נתונים בטבלה מחולקת למחיצות באמצעות 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 מוסיפה שורה למחיצה mytable של “2017-05-01” מ-1 במאי 2017: 

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"

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

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

לדוגמה, ההצהרה הבאה INSERT מוסיפה שורות לטבלה עם חלוקה למחיצות 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. כדי להפנות למחיצה, משתמשים בעמודה הווירטואלית _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');

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

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

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

מומלץ לבצע הרצה יבשה כדי לוודא שהשאילתה מחזירה 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) לפי זמני כתיבת הנתונים באמצעות פסאודו-העמודה _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.

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