Slide 1

Slide 1 text

όονॲཧΛ ετϦʔϛϯάʹ͍ۙ ໨ࢦͯ͠ ݶΓͳ͘

Slide 2

Slide 2 text

→ 
 !2

Slide 3

Slide 3 text


 !3

Slide 4

Slide 4 text

→ 
 
 〜 々 
 !4

Slide 5

Slide 5 text

!5 …

Slide 6

Slide 6 text

!6 … …

Slide 7

Slide 7 text


 
 !7

Slide 8

Slide 8 text

!8

Slide 9

Slide 9 text

!8

Slide 10

Slide 10 text

… !9

Slide 11

Slide 11 text


 
 !10

Slide 12

Slide 12 text

!11

Slide 13

Slide 13 text

!12 
 〜 
 〜 〜

Slide 14

Slide 14 text

!13 
 


Slide 15

Slide 15 text

!14 


Slide 16

Slide 16 text

!15 …

Slide 17

Slide 17 text

!16 〜 〜 〜

Slide 18

Slide 18 text

!17 ← →

Slide 19

Slide 19 text

!18 ! →

Slide 20

Slide 20 text

!19

Slide 21

Slide 21 text

!20

Slide 22

Slide 22 text

!21 updated_at id id, created_at

Slide 23

Slide 23 text

!22 →

Slide 24

Slide 24 text

!23 SELECT *
 FROM table
 WHERE updated_at > :last_updated_at

Slide 25

Slide 25 text

!24 SELECT *
 FROM table
 WHERE updated_at > :last_updated_at 
 …

Slide 26

Slide 26 text

!25 updated_at id … id, created_at …

Slide 27

Slide 27 text

!26

Slide 28

Slide 28 text

!27 
 〜 〜
 〜

Slide 29

Slide 29 text

!28

Slide 30

Slide 30 text

!29 
 〜 〜
 〜

Slide 31

Slide 31 text

!30

Slide 32

Slide 32 text

!31

Slide 33

Slide 33 text

!32 
 SELECT * EXCEPT(rn) FROM ( SELECT *, row_number() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn FROM ( SELECT * FROM dataset.diff -- diff UNION ALL SELECT * FROM dataset.master -- destination ) ) WHERE rn = 1

Slide 34

Slide 34 text

bq query --destination_table=dataset.master " " !33 SELECT * EXCEPT(rn) FROM ( SELECT *, row_number() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn FROM ( SELECT * FROM dataset.diff -- diff UNION ALL SELECT * FROM dataset.master -- destination ) ) WHERE rn = 1 ɾɾɾ

Slide 35

Slide 35 text

dataset.master 
 !34 bq query --destination_table=dataset.master "..." dataset
 .master

Slide 36

Slide 36 text

dataset.master 
 !35 bq query --destination_table=dataset.master "..." dataset
 .master

Slide 37

Slide 37 text

INSERT, UPDATE, DELETE, MERGE 〜 → !36

Slide 38

Slide 38 text

→ !37 MERGE dataset.master T USING ( SELECT * EXCEPT(rn) FROM ( SELECT *, row_number() over (PARTITION BY id ORDER BY updated_at DESC) AS rn FROM dataset.diff) WHERE rn = 1 ) S ON T.id = S.id WHEN MATCHED AND T.updated_at < S.updated_at THEN UPDATE SET id = S.id, ..., updated_at = S.updated_at WHEN NOT MATCHED THEN INSERT (id, ..., updated_at) VALUES (id, ..., updated_at)

Slide 39

Slide 39 text

→ !38 MERGE dataset.master T USING ( SELECT * EXCEPT(rn) FROM ( SELECT *, row_number() over (PARTITION BY id ORDER BY updated_at DESC) AS rn FROM dataset.diff) WHERE rn = 1 ) S ON T.id = S.id WHEN MATCHED AND T.updated_at < S.updated_at THEN UPDATE SET id = S.id, ..., updated_at = S.updated_at WHEN NOT MATCHED THEN INSERT (id, ..., updated_at) VALUES (id, ..., updated_at) MERGE 


Slide 40

Slide 40 text

_YYYYMMDD updated_at → !39 INSERT dataset.master (id, ..., updated_at) SELECT id, ..., updated_at FROM dataset.diff

Slide 41

Slide 41 text

!40

Slide 42

Slide 42 text

!41 {“id”: 37, “name”: “Sana Natori”} {“id”: 418, “name” “Yuni Akatsuki”}

Slide 43

Slide 43 text

!42 〜 →

Slide 44

Slide 44 text

!43

Slide 45

Slide 45 text

!44 ɾ“2019-01-01 01:02:03 JST” ɾ“2019-01-01 01:02:03+0900” ɾ“2019-01-01 01:02:03+09:00” ɾ“2019-01-01 01:02:03 +0900” ɾ“2019-01-01 01:02:03 +09:00”

Slide 46

Slide 46 text

!45

Slide 47

Slide 47 text

!46 → → ɾgsutil -m \
 -o 'GSUtil:parallel_process_count=X'
 -o 'GSUtil:parallel_thread_count=Y' \
 cp ... ɾ gsutil -m cp ...

Slide 48

Slide 48 text

!47

Slide 49

Slide 49 text

!48 → 
 natr: NeAr realTime meRger

Slide 50

Slide 50 text

!49 → → → 〜 →

Slide 51

Slide 51 text

!50 → → → 〜 →

Slide 52

Slide 52 text

!51 
 


Slide 53

Slide 53 text

!52 → 
 〜 
 〜

Slide 54

Slide 54 text

!53 …

Slide 55

Slide 55 text

!54 → 


Slide 56

Slide 56 text

!55

Slide 57

Slide 57 text

!56 


Slide 58

Slide 58 text

→ !57 〜

Slide 59

Slide 59 text

!58

Slide 60

Slide 60 text

!59 
 


Slide 61

Slide 61 text

!60

Slide 62

Slide 62 text

!61 ✅ ✅ ✅ ✅ 
 …