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.8k
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
540
Extensible Effects: beyond the Monad Transformers
hiroki6
1
780
High Performance Scala/high_performance_scala
hiroki6
4
4.1k
並行四方山話/tales_of_concurrency
hiroki6
0
100
Scalaでの並行・並列処理戦略/strategy-for-concurrency-and-parallel-by-scala
hiroki6
9
3k
Monad Error with Cats/monad-error-with-cats
hiroki6
0
590
scala_multi_thread.pdf
hiroki6
0
320
Extensible Effects with Scala/eff-with-scala
hiroki6
0
1k
Try Cats
hiroki6
0
560
Other Decks in Technology
See All in Technology
Oracle Cloud Infrastructure:2025年6月度サービス・アップデート
oracle4engineer
PRO
2
210
AIの最新技術&テーマをつまんで紹介&フリートークするシリーズ #1 量子機械学習の入門
tkhresk
0
130
標準技術と独自システムで作る「つらくない」SaaS アカウント管理 / Effortless SaaS Account Management with Standard Technologies & Custom Systems
yuyatakeyama
3
1.2k
プロダクトエンジニアリング組織への歩み、その現在地 / Our journey to becoming a product engineering organization
hiro_torii
0
120
rubygem開発で鍛える設計力
joker1007
2
190
AWS CDK 実践的アプローチ N選 / aws-cdk-practical-approaches
gotok365
6
690
生成AIでwebアプリケーションを作ってみた
tajimon
2
140
250627 関西Ruby会議08 前夜祭 RejectKaigi「DJ on Ruby Ver.0.1」
msykd
PRO
2
230
CSS、JSをHTMLテンプレートにまとめるフロントエンド戦略
d120145
0
280
Liquid Glass革新とSwiftUI/UIKit進化
fumiyasac0921
0
180
LinkX_GitHubを基点にした_AI時代のプロジェクトマネジメント.pdf
iotcomjpadmin
0
170
解析の定理証明実践@Lean 4
dec9ue
0
170
Featured
See All Featured
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
3.9k
Writing Fast Ruby
sferik
628
61k
Code Review Best Practice
trishagee
68
18k
Music & Morning Musume
bryan
46
6.6k
Keith and Marios Guide to Fast Websites
keithpitt
411
22k
Fireside Chat
paigeccino
37
3.5k
The Psychology of Web Performance [Beyond Tellerrand 2023]
tammyeverts
48
2.8k
Gamification - CAS2011
davidbonilla
81
5.3k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
29
9.5k
Helping Users Find Their Own Way: Creating Modern Search Experiences
danielanewman
29
2.7k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
29
1.8k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
367
26k
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