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.6k
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
440
Extensible Effects: beyond the Monad Transformers
hiroki6
1
650
High Performance Scala/high_performance_scala
hiroki6
4
3.7k
並行四方山話/tales_of_concurrency
hiroki6
0
64
Scalaでの並行・並列処理戦略/strategy-for-concurrency-and-parallel-by-scala
hiroki6
9
2.6k
Monad Error with Cats/monad-error-with-cats
hiroki6
0
480
scala_multi_thread.pdf
hiroki6
0
270
Extensible Effects with Scala/eff-with-scala
hiroki6
0
860
Try Cats
hiroki6
0
510
Other Decks in Technology
See All in Technology
20分で完全に理解するGrafanaダッシュボード
hamadakoji
1
250
元インフラエンジニアに成る / Human Resources to Human Relations
bobtani
4
900
ServiceNow Knowledge Learning Rise up
manarobot
0
200
非同期推論システムによるコスト削減と信頼性向上
koki_nishihara
0
190
エンジニア候補者向け資料2024.04.24.pdf
macloud
0
3.3k
KubeConにproposalを送りたい人へのアドバイス
sat
PRO
3
230
データベース02: データベースの概念
trycycle
0
150
ChatGPT for IT Service Management (IT Pro)
dahatake
7
1.5k
Java EE/Jakarta EEの現状と将来―クラウドネイティブ時代にJava EEは対応できるのか?―
takakiyo
1
140
開発生産性大幅アップ!Postman VS Code拡張機能
nagix
2
370
Vertex AI を中心に 生成AIのアップデートを共有します
kaz1437
0
290
エンジニアのキャリアをちょっと楽しくする3本の軸/Three Pillars to Make an Engineer's Career More Enjoyable
kwappa
0
2.6k
Featured
See All Featured
Teambox: Starting and Learning
jrom
128
8.4k
The Straight Up "How To Draw Better" Workshop
denniskardys
227
130k
VelocityConf: Rendering Performance Case Studies
addyosmani
320
23k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
19
1.7k
Optimising Largest Contentful Paint
csswizardry
8
2.4k
Testing 201, or: Great Expectations
jmmastey
28
6.3k
Git: the NoSQL Database
bkeepers
PRO
422
63k
Art, The Web, and Tiny UX
lynnandtonic
289
19k
Put a Button on it: Removing Barriers to Going Fast.
kastner
58
3k
The Invisible Side of Design
smashingmag
294
49k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
244
20k
Designing with Data
zakiwarfel
96
4.8k
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