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

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

View Slide

→  
!2

View Slide

 
!3

View Slide

→  
 
〜々
 
!4

View Slide

!5
…

View Slide

!6
…
…

View Slide

 
 
!7

View Slide

!8

View Slide

…
!9

View Slide

 
 
!10

View Slide

!11

View Slide

!12
 
〜
 
〜
〜

View Slide

!13
 
 

View Slide

!14
 

View Slide

!15
…

View Slide

!16
〜
〜
〜

View Slide

!17
←
→

View Slide

!18
！
→

View Slide

!19

View Slide

!20

View Slide

!21
updated_at id
id, created_at

View Slide

!22
→

View Slide

!23
SELECT * 
FROM table 
WHERE updated_at > :last_updated_at

View Slide

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

View Slide

!25
updated_at id
…
id, created_at
…

View Slide

!26

View Slide

!27
 
〜
〜 
〜

View Slide

!28

View Slide

!29
 
〜
〜 
〜

View Slide

!30

View Slide

!31

View Slide

!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

View Slide

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

View Slide

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

View Slide

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

View Slide

INSERT, UPDATE, DELETE, MERGE
〜
→
!36

View Slide

→
!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)

View Slide

→
!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  

View Slide

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

View Slide

!40

View Slide

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

View Slide

!42
〜
→

View Slide

!43

View Slide

!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”

View Slide

!45

View Slide

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

View Slide

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

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

kyontan

More Decks by kyontan

Other Decks in Programming

Featured

Transcript