Slide 1

Slide 1 text

copyright Fringe81 Co.,Ltd. GAEBQ Load Fringe81       1

Slide 2

Slide 2 text

copyright Fringe81 Co.,Ltd.  l  (Hiroki Fujino) l @Fringe81 l   l  2

Slide 3

Slide 3 text

copyright Fringe81 Co.,Ltd.  /  Ø  Ø  l GAE"   l BQ Load l   #! l GAETaskQueue 3

Slide 4

Slide 4 text

copyright Fringe81 Co.,Ltd. 4  

Slide 5

Slide 5 text

copyright Fringe81 Co.,Ltd. Columva l  (2017/11~) l SaaS l ETL 5

Slide 6

Slide 6 text

copyright Fringe81 Co.,Ltd. Columva 6 l  (2017/11~) l SaaS l ETL Columva GAEBQ GCP 

Slide 7

Slide 7 text

copyright Fringe81 Co.,Ltd.    App Engine Big Query App Engine  7 /

Slide 8

Slide 8 text

copyright Fringe81 Co.,Ltd.  lStandard Environment lPaaS l   lJava8/Scala l 8   App Engine Big Query App Engine /

Slide 9

Slide 9 text

copyright Fringe81 Co.,Ltd.  l l&"# l&!% lAPI  l $  9  App Engine Big Query App Engine /

Slide 10

Slide 10 text

copyright Fringe81 Co.,Ltd. BigQuery Big Query In Out 10

Slide 11

Slide 11 text

copyright Fringe81 Co.,Ltd. BigQuery lSQL l Big Query In Out 11

Slide 12

Slide 12 text

copyright Fringe81 Co.,Ltd. BigQuery l   Big Query In Out 12

Slide 13

Slide 13 text

copyright Fringe81 Co.,Ltd.  13

Slide 14

Slide 14 text

copyright Fringe81 Co.,Ltd. BQ      14

Slide 15

Slide 15 text

copyright Fringe81 Co.,Ltd.     15

Slide 16

Slide 16 text

copyright Fringe81 Co.,Ltd. BigQuery    16 

Slide 17

Slide 17 text

copyright Fringe81 Co.,Ltd.   17    BigQuery    

Slide 18

Slide 18 text

copyright Fringe81 Co.,Ltd. 18 %#"  TaskQueue   GAE SE& !$60   GAE

Slide 19

Slide 19 text

copyright Fringe81 Co.,Ltd. App Engine Push Queue Push Queue App Engine App Engine 1 3 2 2 Enqueue    Push Queue  GAE 2 Push 3 Enqueue 3 Push 19

Slide 20

Slide 20 text

copyright Fringe81 Co.,Ltd. App Engine Push Queue "! Enqueue GAE "! #$ Push Queue       20

Slide 21

Slide 21 text

copyright Fringe81 Co.,Ltd. App Engine Push Queue  *)'#Enqueue GAE   *)'#&,.! Push Queue  #  "!    )(.-$%+  21

Slide 22

Slide 22 text

copyright Fringe81 Co.,Ltd.    BQ     22 BigQuery  

Slide 23

Slide 23 text

copyright Fringe81 Co.,Ltd.   !"  #    23

Slide 24

Slide 24 text

copyright Fringe81 Co.,Ltd.    l Ver.1 (Streaming Insert) l Ver.2 (BQ Load) l Ver.3 (Pull Queue  BQ Load) 24

Slide 25

Slide 25 text

copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ Load) l Ver.3 (Pull Queue  BQ Load)    25

Slide 26

Slide 26 text

copyright Fringe81 Co.,Ltd. Streaming Insert BQ     Streaming Insert   Ver.1(Streaming Insert) 26 BigQuery 

Slide 27

Slide 27 text

copyright Fringe81 Co.,Ltd. Streaming Insert l   BigQuery    l $0.01/200MB BigQuery Streaming Insert Table User id INTEGER name STRING 27   User(id: Long, name: String)

Slide 28

Slide 28 text

copyright Fringe81 Co.,Ltd.  28

Slide 29

Slide 29 text

copyright Fringe81 Co.,Ltd.  BQ      BQ   29

Slide 30

Slide 30 text

copyright Fringe81 Co.,Ltd.  BQ!"  !"    !"  !"  BQ!"    !"  ## 30

Slide 31

Slide 31 text

copyright Fringe81 Co.,Ltd. Streaming Insert   SELECT DELETE   BigQuery SELECT DELETE Streaming Insert 31

Slide 32

Slide 32 text

copyright Fringe81 Co.,Ltd. Streaming Insert streaming insert   BigTable   select BigTable  [https://cloud.google.com/blog/big-data/2017/06/life-of-a-bigquery-streaming-insert] 32

Slide 33

Slide 33 text

copyright Fringe81 Co.,Ltd. Streaming Insert streaming insert  # BigTable!" select BigTable!"  33 deleteupdate  90  [https://cloud.google.com/blog/big-data/2017/06/life-of-a-bigquery-streaming-insert]

Slide 34

Slide 34 text

copyright Fringe81 Co.,Ltd. Streaming Insert 34 l%*$(1("!#& 1MB) l()$ '   %*$ %*$ 

Slide 35

Slide 35 text

copyright Fringe81 Co.,Ltd. Streaming Insert   90   35

Slide 36

Slide 36 text

copyright Fringe81 Co.,Ltd. Streaming Insert 90   BQ      36

Slide 37

Slide 37 text

copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ Load) l Ver.3 (Pull Queue  BQ Load)    37

Slide 38

Slide 38 text

copyright Fringe81 Co.,Ltd. GCS BQ Load BQ! !  ! Cloud Storage   Ver.2 (BQ Load)   BQ Load BigQuery 38  

Slide 39

Slide 39 text

copyright Fringe81 Co.,Ltd. GCS BQ Load BQ! s   Ver.2 (BQ Load) 39 !  ! Cloud Storage   BQ Load BigQuery  

Slide 40

Slide 40 text

copyright Fringe81 Co.,Ltd. BQ Load 40 lGoogle    lSDK  (Java, Python, Go…)

Slide 41

Slide 41 text

copyright Fringe81 Co.,Ltd. BQ Load CSV JSON BigQuery Load  1 BQ     Load 41 Table User id INTEGER name STRING id, name 1, “hoge” 2, “fuga” {“id”: 1, “name”: “hoge”} {“id”: 2, “name”: “fuga”}

Slide 42

Slide 42 text

copyright Fringe81 Co.,Ltd. BQ Load GCP   Cloud Storage Cloud Dataflow Load Load BigQuery 42

Slide 43

Slide 43 text

copyright Fringe81 Co.,Ltd. GCS BQ Load BQ!   Ver.2 (BQ Load) 43 !  ! Cloud Storage   BQ Load BigQuery  

Slide 44

Slide 44 text

copyright Fringe81 Co.,Ltd. Google Cloud Storage !, )$(./# GCS <=> BQ'/%  "$&+'/%*-   GCSJSON*- 44

Slide 45

Slide 45 text

copyright Fringe81 Co.,Ltd. GCS => BQ Cloud Storage BQ Load hoge1.json hoge_table hoge2.json hoge3.json hoge_table hoge_table BigQuery 45

Slide 46

Slide 46 text

copyright Fringe81 Co.,Ltd. BQ Load l Streaming Insert% BQ Load% l -.,+* & ) l $-.,+ *)! ) l ACID +  u *-.,% #)'#")(Atomicity) u -.,%)("(Consistency) 46

Slide 47

Slide 47 text

copyright Fringe81 Co.,Ltd.     47

Slide 48

Slide 48 text

copyright Fringe81 Co.,Ltd. BQ Load error=BigQueryError{reason=rateLimitExceeded, location=table.write, message=Exceeded rate limits: too many table update operations for this table… 48

Slide 49

Slide 49 text

copyright Fringe81 Co.,Ltd. rateLimitExceeded 49

Slide 50

Slide 50 text

copyright Fringe81 Co.,Ltd. GCS BQ Load BQ %   Ver.2 (BQ Load) Load  50 % "$%! Cloud Storage  BQ Load BigQuery  #

Slide 51

Slide 51 text

copyright Fringe81 Co.,Ltd.   BQ Load   BQ Load   51

Slide 52

Slide 52 text

copyright Fringe81 Co.,Ltd. BQ Load l 15'"% 10 $" l * '"% 1,000  $"( ) l * #& (!'$" #& ) 10,000  $"( )  52

Slide 53

Slide 53 text

copyright Fringe81 Co.,Ltd. BQ Load l 15)0836!10"%%/53$ l ; )0836! 1,000 "%%/53$9 +&) l ; )47/,-19.28/53+"47/,-1:! 10,000 "%%/53$9 +&:  53   "#" +(Load*'! 

Slide 54

Slide 54 text

copyright Fringe81 Co.,Ltd. BQ Load l 15$-614 10 ""+31! l 9 $-614 1,000  ""+31!7 '#) l 9 $25+()/7*06+31'  25+()/8 10,000  ""+31!7 '#8  54 .6,& %

Slide 55

Slide 55 text

copyright Fringe81 Co.,Ltd. BQ Load l 15"*3.1 10 (0. l 6 "*3.1 1,000  (0.4 $ ) l 6 "/2(%&,4'-3(0.$ /2(%&,5 10,000  (0.4 $ 5  55 +3)$! BQLoad#7

Slide 56

Slide 56 text

copyright Fringe81 Co.,Ltd. BQ Load    Cloud Storage gs://bucket/dir/* [gs://bucket/dir/test1.txt, gs://bucket/dir/test2.txt] [gs://bucket/dir1/*, gs://bucket/dir2/*]           Load Load Load BigQuery 56

Slide 57

Slide 57 text

copyright Fringe81 Co.,Ltd. BQ Load    Cloud Storage gs://bucket/dir/* [gs://bucket/dir/test1.txt, gs://bucket/dir/test2.txt] [gs://bucket/dir1/*, gs://bucket/dir2/*]     1   Load Load Load BigQuery 57    

Slide 58

Slide 58 text

copyright Fringe81 Co.,Ltd. BQ Load    Load   l  10,000URI l  10,000,000 l  15TB  58

Slide 59

Slide 59 text

copyright Fringe81 Co.,Ltd.  *1&%BQ Load1"0#!0 Load *62391!0 .&' Load%0 59 l <!&/5:79$() 1,000 *,,487+'; 1 -) 487* l 487' 10,000URI+' l 487' 10,000,0006239+' l 487' 15TB+' 1487&/*

Slide 60

Slide 60 text

copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ Load) l Ver.3 (Pull Queue  BQ Load)    60

Slide 61

Slide 61 text

copyright Fringe81 Co.,Ltd. GCS%),-($#!  Pull Queue BQLoad  BQ Load Pull Queue   +- %),-( '-$    Ver.3 (Pull Queue × BQ Load) Cloud Storage BigQuery 61 Pull Queue %),-( '-$  +- '-$ %),-( Cloud Storage  "#&*

Slide 62

Slide 62 text

copyright Fringe81 Co.,Ltd. GCS%),-($#!  Pull Queue BQLoad  BQ Load   Ver.3 (Pull Queue × BQ Load) Cloud Storage BigQuery 62 Pull Queue %),-( '-$  +- '-$ %),-( Cloud Storage  "#&* Pull Queue   +- %),-( '-$ 

Slide 63

Slide 63 text

copyright Fringe81 Co.,Ltd. Pull Queue &%$#   Queue  ! "   Pull Queue &%$3 &%$1 &%$2 Enqueue &%$#  63

Slide 64

Slide 64 text

copyright Fringe81 Co.,Ltd. Pull Queue    Enqueue Pull Queue id: 3 id: 2 id: 1  3(payload: 3)  1(payload: 1)  2(payload: 2) Enqueue 64

Slide 65

Slide 65 text

copyright Fringe81 Co.,Ltd. Pull Queue lease payload      Pull Queue Lease 3(payload: 3) 1(payload: 1) 2(payload: 2) id: 3 id: 2 id: 1 65

Slide 66

Slide 66 text

copyright Fringe81 Co.,Ltd. Pull Queue   Load *&'-%$ "Load$ 66 l 0#).+- 1,000   (,+/ %!) (,+  l (,+10,000URI l (,+10,000,000*&'- l (,+15TB 1(,+ #

Slide 67

Slide 67 text

copyright Fringe81 Co.,Ltd. Pull Queue   Load (4017/. ,#%Load". 67 l :#-3857 &' 1,000 (**265)%9 /+) 265( l 265% 10,000URI)% l 265% 10,000,0004017)% l 265% 15TB)% 1265#-( Pull Queue'' Load (4017 /!$.

Slide 68

Slide 68 text

copyright Fringe81 Co.,Ltd. GCS%),-($#!  Pull Queue BQLoad Pull Queue %),-( '-$  +-   Ver.3 (Pull Queue × BQ Load) Cloud Storage  BQ Load BigQuery 68 '-$ %),-( Cloud Storage  "#&* Pull Queue   +- %),-( '-$ 

Slide 69

Slide 69 text

copyright Fringe81 Co.,Ltd. Pull Queue × BQ Load  BQ Load Cloud Storage Pull Queue Lease id: 3 id: 2 id: 1 Lease   BigQuery 69

Slide 70

Slide 70 text

copyright Fringe81 Co.,Ltd.  70 l  1 100 5  l    100

Slide 71

Slide 71 text

copyright Fringe81 Co.,Ltd.  71 l (+* 1 100!5  l -(+* #!)&', "  100)&', GAE% BQ Load  %$

Slide 72

Slide 72 text

copyright Fringe81 Co.,Ltd.  72  %  *0 0  Streaming Insert  1MB/1, % ,-!) /0% 90  BQ Load  15TB/1+' 1000+'/1$0'- 10000+'/1(.% &#"

Slide 73

Slide 73 text

copyright Fringe81 Co.,Ltd.  l BQ Load *$%-# l GAE+',.-#"! BQ Load#! l GAE&()  73

Slide 74

Slide 74 text

copyright Fringe81 Co.,Ltd.  lGAE+TQ  , Dataflow     74

Slide 75

Slide 75 text

copyright Fringe81 Co.,Ltd.    75