Skip to content

Instantly share code, notes, and snippets.

@gxercavins
Created February 16, 2020 10:28
Show Gist options
  • Save gxercavins/7ba13e46f99c361dc330f7b8f5333def to your computer and use it in GitHub Desktop.
Save gxercavins/7ba13e46f99c361dc330f7b8f5333def to your computer and use it in GitHub Desktop.
SO question 60246807
-- create t1
CREATE TABLE
overwrite.t1 (sales INT64,
sdate DATE)
PARTITION BY
sdate;
-- create t2
CREATE TABLE
overwrite.t2 (sales INT64,
pdate DATE)
PARTITION BY
pdate;
-- populate t1
INSERT overwrite.t1 (sales, sdate)
VALUES (1,DATE('2020-01-01')),
(2,DATE('2020-01-02')),
(3,DATE('2020-01-03'));
-- populate t2
INSERT overwrite.t2 (sales, pdate)
VALUES (20,DATE('2020-01-02')),
(30,DATE('2020-01-03')),
(40,DATE('2020-01-04'));
-- delete target partitions
DELETE
overwrite.t2
WHERE
pdate BETWEEN "2020-01-01" and "2020-01-02";
-- update target partitions from source
INSERT overwrite.t2 (sales, pdate)
SELECT sales, sdate AS pdate
FROM overwrite.t1
WHERE sdate BETWEEN "2020-01-01" and "2020-01-02";
-- merge example (consider what will happen with more than one row per partition)
-- MERGE overwrite.t2
-- USING overwrite.t1
-- ON t2.pdate = t1.sdate
-- AND t1.sdate BETWEEN "2020-01-01" and "2020-01-02"
-- WHEN MATCHED THEN
-- UPDATE SET t2.sales = t1.sales
-- WHEN NOT MATCHED THEN
-- INSERT(sales, pdate) VALUES(t1.sales, t1.sdate)
@gxercavins
Copy link
Author

gxercavins commented Feb 16, 2020

original target table:

Screenshot from 2020-02-16 10-22-29

target table updated from source:

Screenshot from 2020-02-16 10-45-40

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment