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 InsertBQ
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 LoadBQ!
!
!
Cloud Storage
Ver.2 (BQ Load)
BQ Load
BigQuery
38
Slide 39
Slide 39 text
copyright Fringe81 Co.,Ltd.
GCS
BQ LoadBQ!
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 LoadBQ!
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 LoadBQ %
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.110
(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 QueueBQLoad
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 QueueBQLoad
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
leasepayload
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 QueueBQLoad
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