Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
GAEを用いたBQ Load戦略/gae_bq_load_strategy
Search
fuzyco
April 24, 2018
Technology
2
1.9k
GAEを用いたBQ Load戦略/gae_bq_load_strategy
bq_sushi#7での発表資料
fuzyco
April 24, 2018
Tweet
Share
More Decks by fuzyco
See All by fuzyco
Functional Error&Retry Handling
hiroki6
2
570
Extensible Effects: beyond the Monad Transformers
hiroki6
1
810
High Performance Scala/high_performance_scala
hiroki6
4
4.2k
並行四方山話/tales_of_concurrency
hiroki6
0
110
Scalaでの並行・並列処理戦略/strategy-for-concurrency-and-parallel-by-scala
hiroki6
9
3.1k
Monad Error with Cats/monad-error-with-cats
hiroki6
0
620
scala_multi_thread.pdf
hiroki6
0
340
Extensible Effects with Scala/eff-with-scala
hiroki6
0
1k
Try Cats
hiroki6
0
570
Other Decks in Technology
See All in Technology
混合雲環境整合異質工作流程工具運行關鍵業務 Job 的經驗分享
yaosiang
0
190
MCP ✖️ Apps SDKを触ってみた
hisuzuya
0
370
ブラウザのAPIで Nintendo Switch用の特殊なゲーム用コントローラーを体験型コンテンツに / IoTLT @ストラタシス・ジャパン
you
PRO
0
140
可観測性は開発環境から、開発環境にもオブザーバビリティ導入のススメ
layerx
PRO
2
660
QA業務を変える(!?)AIを併用した不具合分析の実践
ma2ri
0
150
Biz職でもDifyでできる! 「触らないAIワークフロー」を実現する方法
igarashikana
7
3.5k
プロダクト開発と社内データ活用での、BI×AIの現在地 / Data_Findy
sansan_randd
0
180
AIエージェントによる業務効率化への飽くなき挑戦-AWS上の実開発事例から学んだ効果、現実そしてギャップ-
nasuvitz
5
1.2k
serverless team topology
_kensh
3
230
頭部ふわふわ浄酔器
uyupun
0
110
CNCFの視点で捉えるPlatform Engineering - 最新動向と展望 / Platform Engineering from the CNCF Perspective
hhiroshell
0
140
AIとともに歩んでいくデザイナーの役割の変化
lycorptech_jp
PRO
0
890
Featured
See All Featured
YesSQL, Process and Tooling at Scale
rocio
173
15k
How To Stay Up To Date on Web Technology
chriscoyier
791
250k
Leading Effective Engineering Teams in the AI Era
addyosmani
7
640
GraphQLとの向き合い方2022年版
quramy
49
14k
Building an army of robots
kneath
305
46k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
34
2.3k
Statistics for Hackers
jakevdp
799
220k
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
4k
Distributed Sagas: A Protocol for Coordinating Microservices
caitiem20
333
22k
Build The Right Thing And Hit Your Dates
maggiecrowley
38
2.9k
Side Projects
sachag
455
43k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
132
19k
Transcript
copyright Fringe81 Co.,Ltd. GAEBQ Load Fringe81
1
copyright Fringe81 Co.,Ltd. l (Hiroki Fujino) l @Fringe81
l l 2
copyright Fringe81 Co.,Ltd. / Ø Ø
l GAE" l BQ Load l #! l GAETaskQueue 3
copyright Fringe81 Co.,Ltd. 4
copyright Fringe81 Co.,Ltd. Columva l (2017/11~) l SaaS l
ETL 5
copyright Fringe81 Co.,Ltd. Columva 6 l (2017/11~) l SaaS
l ETL Columva GAEBQ GCP
copyright Fringe81 Co.,Ltd. App Engine Big Query
App Engine 7 /
copyright Fringe81 Co.,Ltd. lStandard Environment lPaaS l
lJava8/Scala l 8 App Engine Big Query App Engine /
copyright Fringe81 Co.,Ltd. l l&"# l&!% lAPI l
$ 9 App Engine Big Query App Engine /
copyright Fringe81 Co.,Ltd. BigQuery Big Query In Out 10
copyright Fringe81 Co.,Ltd. BigQuery lSQL l Big Query In Out
11
copyright Fringe81 Co.,Ltd. BigQuery l Big Query In
Out 12
copyright Fringe81 Co.,Ltd. 13
copyright Fringe81 Co.,Ltd. BQ 14
copyright Fringe81 Co.,Ltd. 15
copyright Fringe81 Co.,Ltd. BigQuery 16
copyright Fringe81 Co.,Ltd. 17 BigQuery
copyright Fringe81 Co.,Ltd. 18 %#" TaskQueue GAE
SE& !$60 GAE
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
copyright Fringe81 Co.,Ltd. App Engine Push Queue "! Enqueue GAE
"! #$ Push Queue 20
copyright Fringe81 Co.,Ltd. App Engine Push Queue *)'#Enqueue GAE
*)'#&,.! Push Queue # "! )(.-$%+ 21
copyright Fringe81 Co.,Ltd. BQ
22 BigQuery
copyright Fringe81 Co.,Ltd. !" #
23
copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert)
l Ver.2 (BQ Load) l Ver.3 (Pull Queue BQ Load) 24
copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ
Load) l Ver.3 (Pull Queue BQ Load) 25
copyright Fringe81 Co.,Ltd. Streaming Insert BQ
Streaming Insert Ver.1(Streaming Insert) 26 BigQuery
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)
copyright Fringe81 Co.,Ltd. 28
copyright Fringe81 Co.,Ltd. BQ
BQ 29
copyright Fringe81 Co.,Ltd. BQ!" !"
!" !" BQ!" !" ## 30
copyright Fringe81 Co.,Ltd. Streaming Insert SELECT DELETE
BigQuery SELECT DELETE Streaming Insert 31
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
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]
copyright Fringe81 Co.,Ltd. Streaming Insert 34 l%*$(1("!#& 1MB) l()$ '
%*$ %*$
copyright Fringe81 Co.,Ltd. Streaming Insert 90
35
copyright Fringe81 Co.,Ltd. Streaming Insert 90 BQ
36
copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ
Load) l Ver.3 (Pull Queue BQ Load) 37
copyright Fringe81 Co.,Ltd. GCS BQ Load BQ! ! !
Cloud Storage Ver.2 (BQ Load) BQ Load BigQuery 38
copyright Fringe81 Co.,Ltd. GCS BQ Load BQ! s
Ver.2 (BQ Load) 39 ! ! Cloud Storage BQ Load BigQuery
copyright Fringe81 Co.,Ltd. BQ Load 40 lGoogle
lSDK (Java, Python, Go…)
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”}
copyright Fringe81 Co.,Ltd. BQ Load GCP Cloud Storage
Cloud Dataflow Load Load BigQuery 42
copyright Fringe81 Co.,Ltd. GCS BQ Load BQ! Ver.2
(BQ Load) 43 ! ! Cloud Storage BQ Load BigQuery
copyright Fringe81 Co.,Ltd. Google Cloud Storage !, )$(./# GCS <=>
BQ'/% "$&+'/%*- GCSJSON*- 44
copyright Fringe81 Co.,Ltd. GCS => BQ Cloud Storage BQ Load
hoge1.json hoge_table hoge2.json hoge3.json hoge_table hoge_table BigQuery 45
copyright Fringe81 Co.,Ltd. BQ Load l Streaming Insert% BQ Load%
l -.,+* & ) l $-.,+ *)! ) l ACID + u *-.,% #)'#")(Atomicity) u -.,%)("(Consistency) 46
copyright Fringe81 Co.,Ltd. 47
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
copyright Fringe81 Co.,Ltd. rateLimitExceeded 49
copyright Fringe81 Co.,Ltd. GCS BQ Load BQ %
Ver.2 (BQ Load) Load 50 % "$%! Cloud Storage BQ Load BigQuery #
copyright Fringe81 Co.,Ltd. BQ Load BQ
Load 51
copyright Fringe81 Co.,Ltd. BQ Load l 15'"% 10 $" l
* '"% 1,000 $"( ) l * #& (!'$" #& ) 10,000 $"( ) 52
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*'!
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,& %
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
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
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
copyright Fringe81 Co.,Ltd. BQ Load Load
l 10,000URI l 10,000,000 l 15TB 58
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&/*
copyright Fringe81 Co.,Ltd. l Ver.1 (Streaming Insert) l Ver.2 (BQ
Load) l Ver.3 (Pull Queue BQ Load) 60
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 "#&*
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 +- %),-( '-$
copyright Fringe81 Co.,Ltd. Pull Queue &%$# Queue
! " Pull Queue &%$3 &%$1 &%$2 Enqueue &%$# 63
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
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
copyright Fringe81 Co.,Ltd. Pull Queue Load *&'-%$ "Load$
66 l 0#).+- 1,000 (,+/ %!) (,+ l (,+10,000URI l (,+10,000,000*&'- l (,+15TB 1(,+ #
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 /!$.
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 +- %),-( '-$
copyright Fringe81 Co.,Ltd. Pull Queue × BQ Load BQ
Load Cloud Storage Pull Queue Lease id: 3 id: 2 id: 1 Lease BigQuery 69
copyright Fringe81 Co.,Ltd. 70 l 1 100 5
l 100
copyright Fringe81 Co.,Ltd. 71 l (+* 1 100!5
l -(+* #!)&', " 100)&', GAE% BQ Load %$
copyright Fringe81 Co.,Ltd. 72 % *0 0
Streaming Insert 1MB/1, % ,-!) /0% 90 BQ Load 15TB/1+' 1000+'/1$0'- 10000+'/1(.% &#"
copyright Fringe81 Co.,Ltd. l BQ Load *$%-# l GAE+',.-#"!
BQ Load#! l GAE&() 73
copyright Fringe81 Co.,Ltd. lGAE+TQ , Dataflow
74
copyright Fringe81 Co.,Ltd. 75