限りなくストリーミングに近いバッチ処理を目指して / #DPCT 20190416

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

→   !2

Slide 3

Slide 3 text

Slide 4

Slide 4 text

→     〜々   !4

Slide 5

Slide 5 text

!5 …

Slide 6

Slide 6 text

!6 … …

Slide 7

Slide 7 text

Slide 8

Slide 8 text

Slide 9

Slide 9 text

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 ...