Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Sign up for free
Menu
Search
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Pricing
Search
Sign in
Sign up for free
BigQuery Schema Migration #bq_sushi
Search
Naotoshi Seo
April 08, 2016
Technology
6.2k
2
Share
Embed
Copy iframe code
Copy JS code
Copy link
Start on current slide
BigQuery Schema Migration #bq_sushi
Naotoshi Seo
April 08, 2016
Transcript
34 slides
#JH2VFSZͷςʔϒϧΛ .JHSBUF ΧϥϜՃɺআɺ ܕมߋ ͢Δ 2016/04/08 @sonots #bq_sushi 3
ࣗݾհ • ඌར @sonots • DeNA ੳج൫ • Fluentd ίϛολ
• Ruby ίϛολ • ࠷ۙ embulk ۀ • embulk-output-bigquery • embulk-filter-column, etc
• 4݄23ൃചʂ • σʔλऩूಛू • Fluentd / Embulk • DeNA
/ Cookpad ͷࣄྫ
ΞδΣϯμ • ฐࣾͰͷ BigQuery ར༻ • εΩʔϚมߋͷඞཁੑ • BigQuery ʹ͓͚ΔεΩʔϚมߋͷࠔ͞
• εΩʔϚมߋͷઓུ
ฐࣾͰͷ BigQuery ར༻ • West (US) Ͱ̍Ҏ্લ͔Βར༻ • JP ͰϘνϘν͍࢝Ί͍ͯΔ
• σʔλҠߦπʔϧ࡞ͬͯΔ • hdfs2bigquery • vertica2bigquery • bigquery2hdfs • bigquery2vertica
ฐࣾͰͷੳۀ • σʔλҠߦ Hadoop/Vertica ӡ༻ج൫νʔϜ • ੳۀΞφϦετ͕ߦ͏ • BigQuery ʹΫΤϦΛ͛ΔͷΞφϦετ
εΩʔϚมߋͷඞཁੑ(1) • ϩάʹΧϥϜ͕Ճ͞Εͨ • ͬͺΓΧϥϜ͕ফ͞Εͨ • ΧϥϜͷܕΛؒҧ͑ͨ • INTEGER ͬΆ͍ͱࢥͬͯͨΒ
11,12 Έ͍ͨͳ ͕ೖͬͯΔߦ͕͋ͬͯ STRING ͡Όͳ͍ͱμϝ ͩͬͨͱ͔͋Δ͋Δ
εΩʔϚมߋͷඞཁੑ(2) • BigQuery ςʔϒϧ໊ϕετϓϥΫςΟε • ςʔϒϧ໊લஔࢺ_ˋY%m%d • ຖ৽͘͠ςʔϒϧΛ࡞Δ • ຖεΩʔϚ࠶ఆٛͷνϟϯε͕͋Δ
• εΩʔϚมߋ͠ͳͯ͘ྑ͍͡ΌΜʁ
εΩʔϚมߋͷඞཁੑ(3) • ̎ͭͷςʔϒϧͰΧϥϜͷܕ͕ҧ͏ͱΤϥʔʂ SELECT name FROM TABLE_DATE_RANGE(data.people_, TIMESTAMP('2014-03-26'), TIMESTAMP('2014-03-27')) WHERE
age >= 35 • Ωϟετ͢Δͱ͍͏ख͋Δ͕ɺੳ࣌ଞͷ͜ ͱʹ಄Λ͍͍ͨͷͰආ͚͍ͨ
εΩʔϚมߋπʔϧͷఏڙ • ͋Δ͖࢟(εΩʔϚ)Λఏࣔ͢Δͱɺ • ΧϥϜͷՃ • ΧϥϜͷআ • ΧϥϜͷܕมߋ •
Λࣗಈผͯ͠ɺεΩʔϚมߋͰ͖ΔΑ͏ʹ ͍ͯ͋͛ͨ͠
BigQuery ʹ͓͚Δ εΩʔϚมߋͷࠔ͞
εΩʔϚมߋͷࠔ • BigQuery ʹ ALTER TABLE ͕ͳ͍ • ΧϥϜՃͷAPI͋Δ •
ΧϥϜআɺܕมߋͷ API ͕ͳ͍ Ͳ͏͢Δ͔ʁͱ͍͏
ΧϥϜՃ • patch_table (or update_table) API ͰͰ͖Δ client.patch_table(project_id, dataset_id, table_id,
{ schema: { fields: [ {name:"time", type:"TIMESTAMP", mode:"NULLABLE"}, {name:"id", type:"INTEGER", mode:"NULLABLE"}, ] } }) google-api-ruby-client
ΧϥϜՃ(ҙ) • ͢Ͱʹ͋ΔεΩʔϚ + Ճ͢ΔΧϥϜ • get_table ͰεΩʔϚΛऔಘͯ͠Ϛʔδͯ͛͠Δ response =
get_table(project_id, dataset_id, table_id) columns = response.schema.fields.map {|col| col.to_h } columns << {name:"id", type:"INTEGER", mode:"NULLABLE"} google-api-ruby-client
patch tables ͷ੍ • Ճͨ͠ΧϥϜඌʹՃ͞ΕΔ • ՃͰ͖Δͷ NULLABLE ·ͨ REPEATED
͚ͩ • mode: REQUIRED ͳΧϥϜ͕ՃͰ͖ͳ͍ • มߋ REQUIRED => NULLABLE ͚ͩ • NULLABLE Λ REQUIRED ʹͰ͖ͳ͍ • REPEATED ʹͰ͖ͳ͍
ΧϥϜআɺܕมߋ
ΧϥϜআɺܕมߋ • ̎ͭͷઓུ • (1) export & filter & load
• (2) select & copy
(1) export & filter & load • gcs ʹ export
• embulk ΒͳʹΒͰ download ͭͭ͠Λม ͢Δ filtering ॲཧΛߦ͏ • BQ ʹ load ͠ͳ͓͢
(1) export & filter & load • ར • ՝ۚ͞Εͳ͍
• ܽ • ҰϩʔΧϧʹμϯϩʔυ্ͯ͛͢͠ ͜ͱʹͳΔͷͰඇৗʹ͍ɻɻɻ
(2) select & copy • insert_job API ʹ query ͱ
destination_table Λࢦఆ insert_job(project_id, { configuration: { query: { query:"SELECT ... FROM [...]", destination_table: { dataset_id: dataset_id, table_id: table_id }, } } }, {})
(2) select & copy ͷྫ SELECT STRING(business_id) AS business_id, STRING(full_address)
AS full_address, schools, BOOLEAN(open) AS open, FROM [dataset_id.table_id] • ΩϟετͰܕมߋ • ੍: ܕมߋ͢Δͱ mode: NULLABLE ʹͳΔ • ࢦఆ͠ͳ͔ͬͨΧϥϜআ͞ΕΔ
(2) select & copy • ར • ͍ • ܽ
• ՝ۚ͞ΕΔ
(2) select & copy Λ࠾༻ (1) export & filter &
load ΔͳΒ HDFS͔ΒσʔλLoadΓͯ࣌ؒ͋͠·ΓมΘΒͳ͍ ...
Further dive into select & copy
RECORD ܕͷΩϟετํ๏ SELECT INTEGER(votes.funny) AS votes.funny, INTEGER(votes.useful) AS votes.useful, INTEGER(votes.cool)
AS votes.cool, FROM [dataset_id.table_id] • υοτ۠ΓͰࢦఆ͢Δ
select & copy ͰͷΧϥϜՃ SELECT column1, INTEGER(NULL) AS column2, INTEGER(NULL)
AS (record.column3), FROM [dataset_id.table_id] • INTEGER ܕͷ column2 ΛՃ • RECORD ܕͷ record ΧϥϜͰͳ͘ record_column3 ͱ͍͏໊લͷΧϥϜ͕Ͱ͖Δɻɻɻ • patch table API ͬͨ΄͏͕ྑͦ͞͏ɻɻɻ
mode: REPEATED ΧϥϜͷࢦఆ • SELECT ͰࢦఆͰ͖Δ REPEATED ΧϥϜ̍ͭͩ ͚ɺͳͲͷ੍͕͋ͬͨΓ •
SELECT ͢Δͱߦ͕૿͑ΔͷͰɺREPEATED Χϥ Ϝͷͳ͍ߦ͕૿͑ͨςʔϒϧΛ࡞Δ͜ͱʹͳΔ • Ͳ͏ݫͦ͠͏
Atomic ͳςʔϒϧͷஔ • ௨ৗͷઓུ • มલͷςʔϒϧ => มޙ • atomic
ʹ swap • BigQuery ʹ rename ͕ͳ͍ʂແཧʂʁ • copy ͷ destination_table Λࣗࣗʹࢦఆ • atomic ʹ swap ͞ΕΔʂʂ
·ͱΊ
·ͱΊΔͱ • ΧϥϜͷՃ͕ඞཁͳ߹ɺ·ͣ patch table • ΧϥϜͷআɺ·ͨܕมߋ͕ඞཁͳ߹ɺ ͔ͦ͜Β͞Βʹ select &
copy • copyઌࣗࣗΛࢦఆ͢Δ͜ͱͰ atomic ʹ swap Ͱ͖Δ
੍ • mode: REPEATED ΧϥϜΛѻ͑ͳ͍ • mode: NULLABLE ΧϥϜͷΈՃՄೳ •
ܕมߋ͢Δͱ mode: NULLABLE ʹͳΔ
https://github.com/sonots/bigquery_migration/
͍ํ require 'bigquery_migration' config = { json_keyfile: '/path/to/your-project-000.json' dataset: 'your_dataset_name'
table: 'your_table_name' } columns = [ { name: 'string', type: 'STRING' }, { name: 'record', type: 'RECORD', fields: [ { name: 'integer', type: 'INTEGER' }, { name: 'timestamp', type: 'TIMESTAMP' }, ] } ] migrator = BigqueryMigration.new(config) migrator.migrate_table(columns: columns)
• 4݄23ൃചʂ • σʔλऩूಛू • Fluentd / Embulk • DeNA
/ Cookpad ͷࣄྫ
More Decks by Naotoshi Seo
See All by Naotoshi Seo
ZOZOTOWNリプレイス2020
sonots
5
40k
Red Chainer and Cumo: Practical Deep Learning in Ruby at RubyKaigi 2019
sonots
1
4.9k
Introduction of Cumo, and Integration to Red Chainer
sonots
1
1.3k
Implementation of Cumo, a CUDA-aware version of Ruby/Numo
sonots
1
2.1k
Fast Numerical Computing and Deep Learning in Ruby with Cumo
sonots
0
10k
CuPy improvments around memory
sonots
3
1.8k
DeNA AIシステム部におけるクラウドを活用した機械学習基盤の構築
sonots
4
6.5k
Triglav - Data Driven Workflow Tool
sonots
1
4.4k
DeNA流データエンジニアリングの極意
sonots
17
13k
Other Decks in Technology
See All in Technology
エンタープライズIAMについて - ID・権限をどう統制するか
oidfj
1
310
セキュリティチームは どのコンテナイメージの脆弱性対応を優先すべきか 〜CISA新指針とSCCで作る脆弱性トリアージ〜
swdtkuy
0
330
Kansai WordPress Meetup@大阪『ブロックテーマを作る上で知っておくべきこと
torounit
0
310
攻撃と守りは何が変わり、何が変わらないのか~最近の不正アクセスから考える、リスク評価と標準の使いどころ
oidfj
2
320
AWS Well-Arhitected Frameworkセキュリティの柱 について
miu_crescent
PRO
2
250
技術書典で本を売る流れ
yoshimura_datam
0
110
個人ASを取得して、自宅Kubernetes上のVMをグローバルIPで公開した話
munenick
0
230
認知負荷の減らし方 ~開発にも日常業務にも活かせるノウハウ~
minorun365
PRO
17
10k
ポケモンバトルのAIエージェント
koji_kawamura
0
220
認知の質も文化醸成も。数年取り組んで見えてきた技術広報のKPI設計 / Tech PR KPIs: Lessons on Awareness Quality and Culture After Several Years
bitkey
PRO
0
210
現場に渡し、構造で守る AI エージェントを活用したセルフサーブのデータ基盤の運用
legalontechnologies
PRO
0
110
RAGの改善を速く回すための評価基盤
yosukeyoshida
6
3.6k
Featured
See All Featured
Building a A Zero-Code AI SEO Workflow
portentint
PRO
0
850
How to Talk to Developers About Accessibility
jct
2
630
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
9
1.7k
Designing for Performance
lara
612
70k
The agentic SEO stack - context over prompts
schlessera
0
1k
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
12
2k
Visualization
eitanlees
153
17k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
PRO
203
77k
Six Lessons from altMBA
skipperchong
29
4.6k
Impact Scores and Hybrid Strategies: The future of link building
tamaranovitovic
0
490
JAMstack: Web Apps at Ludicrous Speed - All Things Open 2022
reverentgeek
1
680
How to Get Subject Matter Experts Bought In and Actively Contributing to SEO & PR Initiatives.
livdayseo
1
250