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.7k
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
450
Extensible Effects: beyond the Monad Transformers
hiroki6
1
660
High Performance Scala/high_performance_scala
hiroki6
4
3.8k
並行四方山話/tales_of_concurrency
hiroki6
0
71
Scalaでの並行・並列処理戦略/strategy-for-concurrency-and-parallel-by-scala
hiroki6
9
2.7k
Monad Error with Cats/monad-error-with-cats
hiroki6
0
520
scala_multi_thread.pdf
hiroki6
0
290
Extensible Effects with Scala/eff-with-scala
hiroki6
0
900
Try Cats
hiroki6
0
530
Other Decks in Technology
See All in Technology
ギークの理想が7つ集まるエムスリーで夢を叶えよう - エムスリー株式会社
m3_engineering
1
260
Scaling Technical Excellence at 104: Evolution in AWS and Developer Empowerment
scotthsieh825
1
160
Flutter研修【MIXI 24新卒技術研修】
mixi_engineers
PRO
0
160
開発生産性をむしろ向上させる セキュリティパートナーの作り方 / Dev Productivity Con 2024
flatt_security
0
390
サービスの持続的な成長と技術負債について
siva_official
PRO
10
4.4k
AutomatedLabを使って内部ペンテストを勉強しよう! -やられ社内ネットワークの自動構築-
n_etupirka
1
610
クラウド利用者の「責任」をどう果たす?AWSセキュリティ対策のススメ #AWSSummit
hiashisan
0
280
大規模ドラレコデータ収集・機械学習基盤を支える AWS CDK 〜導入・運用事例紹介〜
pemugi
0
110
テストケースの自動生成に生成AIの導入を試みた話と生成AIによる今後の期待
shift_evolve
0
190
簡単に始めるSnowflakeの機械学習
nayuts
1
190
Azure Pipelinesを使用したCICDベースラインアーキテクチャ実践
yuriemori
0
190
Amazon FSx for NetApp ONTAPのパフォーマンスチューニング要素をまとめてみた #cm_odyssey #devio2024
non97
0
220
Featured
See All Featured
How To Stay Up To Date on Web Technology
chriscoyier
784
250k
Scaling GitHub
holman
458
140k
It's Worth the Effort
3n
181
27k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
502
140k
Building an army of robots
kneath
301
42k
Code Review Best Practice
trishagee
58
16k
Fight the Zombie Pattern Library - RWD Summit 2016
marcelosomers
228
16k
Infographics Made Easy
chrislema
238
18k
The Brand Is Dead. Long Live the Brand.
mthomps
52
36k
Bash Introduction
62gerente
607
210k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
26
1.6k
A Modern Web Designer's Workflow
chriscoyier
689
190k
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