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

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

למידע נוסף על 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 כדי להוסיף שורות לטבלה מחולקת למחיצות.

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

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

לדוגמה, ההצהרה הבאה של INSERT מוסיפה שורה למחיצה של mytable מהתאריך 1 במאי 2017 – “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"

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

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

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

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

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

מחיקת נתונים

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

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

ההצהרה הבאה של 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 כוללת את כל המחיצה של מסנן בעמודה המדומה _PARTITIONDATE:

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

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

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

• כיסוי חלקי של המחיצות
• הפניות לעמודות שלא משמשות לחלוקה למחיצות
• נתונים שנוספו לאחרונה באמצעות BigQuery Storage Write API (gRPC)‎ או BigQuery Storage Write API (REST)‎
• מסננים עם שאילתות משנה או פרדיקטים לא נתמכים

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

כשיטה מומלצת, כדאי לבצע הרצה על יבש כדי לוודא שתוצאות השאילתה הן 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.

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

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

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 בטבלאות שמחולקות למחיצות לפי שעה, חודש ושנה

אתם יכולים להשתמש בהצהרות 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

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

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

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

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