ZOZOTOWNのDWHをRedshiftからBigQueryにお引越しした話 / Moving ZOZOTOWN DWH from Redshift to BigQuery

Copyright © ZOZO Technologies, Inc. All Rights Reserved.
גࣜձࣾ;0;0ςΫϊϩδʔζ
ϚʔέςΟϯάΦʔτϝʔγϣϯνʔϜ
Ԙ࡚݈߂
;0;0508/ͷ%8)Λ
3FETIJGU͔Β#JH2VFSZʹ
͓Ҿӽͨ͠͠࿩

View Slide

ࣗݾ঺հ
৽ଔͰ7"4*-:ʹೖࣾޙɺ."Λܦͯ;0;0ςΫϊϩδʔζ΁ɻ
ੲ͸3BJMTͰXFC "1*Λ࡞ͬͨΓɺݕࡧαʔόʔͷύϑΥʔϚϯ
ενϡʔχϯάΛ΍ͬͨΓɻ࠷ۙͷ͓࢓ࣄ͸σʔλج൫ͷ੔ཧͱ
͔ɺϝʔϧɾϓογϡ഑৴ج൫ͷ੔ཧͱ͔ɻ
;0;0ςΫϊϩδʔζ
ϚʔέςΟϯάΦʔτϝʔγϣϯνʔϜ
Ԙ࡚݈߂

View Slide

໨࣍
wαʔϏε঺հ
w3FETIJGU࣌୅ͷ঺հ
w#JH2VFSZҠߦͷಈػ
wσʔλϨΠΫҠߦ
wσʔλϚʔτҠߦ
wΠϯϑϥ঺հ
w৽ͨʹੜͨ͡໰୊఺ͱͦͷղܾࡦ
w͜Ε͔Βͷల๬
w·ͱΊ
˞ٕज़ͷ࿩ଟΊͰ͢ɻΤϞ࿩͸΄΅ͳ͍Ͱ͢ɻ

View Slide

˓೔ຊ࠷େڃͷϑΝογϣϯγϣοϐϯάαΠτΞϓϦ
˓ Ҏ্ͷγϣοϓɺ Ҏ্ͷϒϥϯυͷऔΓѻ͍ 
ʢ೥݄຤࣌఺ʣ
˓ৗ࣌ສ఺Ҏ্ͷ঎඼ΞΠςϜ਺ͱ 
ɹຖ೔ฏۉ ఺Ҏ্ͷ৽ண঎඼Λܝࡌ
˓ଈ೔഑ૹαʔϏεΪϑτϥοϐϯάαʔϏεπέ෷͍ͳͲ
IUUQ[P[PKQ

View Slide

˓೔ຊ࠷େڃͷϑΝογϣϯίʔσΟωʔτΞϓϦ
˓ ສμ΢ϯϩʔυಥഁɺίʔσΟωʔτ౤ߘ૯਺͸ສ݅Ҏ্ 
ʢͱ΋ʹ೥݄຤࣌఺ʣ
˓શੈքʢ"QQ4UPSF(PPHMFQMBZ͕ར༻ՄೳͳશͯͷࠃʣͰ 
ɹμ΢ϯϩʔυ͕Մೳ
˓ສਓҎ্ͷϑΥϩϫʔΛ࣋ͭϢʔβʔʢ8&"3*45"ʣ΋஀ੜ
IUUQTXFBSKQ

View Slide

˓౰͕ࣾಠࣗʹ։ൃͨ͠࠾ੇ༻ϘσΟʔεʔπ
˓શମʹࢪ͞ΕͨυοτϚʔΧʔΛεϚʔτϑΥϯΧϝϥͰ౓ࡱ
Ө͢Δ͜ͱͰɺମܕσʔλΛܭଌ
˓ܭଌͨ͠ମܕσʔλ͸ɺॠ࣌ʹ%ϞσϧԽ͞Εɺ;0;0508/Ξ
ϓϦʹอଘɻ%Ϟσϧ͸͋ΒΏΔ֯౓ʹಈ͔͢͜ͱ͕Ͱ͖ɺମܕΛ
౓νΣοΫ͢Δ͜ͱ͕Մೳ
IUUQ[P[PKQ[P[PTVJU

View Slide

˓ʮ;0;046*5ʯͰܭଌͨ͠ମܕσʔλΛ΋ͱʹɺ 
ɹҰਓͻͱΓͷମܕʹ߹ͬͨʮ͋ͳͨαΠζʯͷΞΠςϜ
˓ʮڀۃͷϑΟοτײʯΛ࣮ݱͨ͠ϕʔγοΫΞΠςϜΛఏڙ
ɹɹάϩʔόϧαΠτʮ;0;0DPNʯͰւ֎ల։
˓ΞΠςϜ̩γϟπɺσχϜύϯπɺγϟπɺϏδωεεʔπɺ
ωΫλΠɺϘʔμʔ̩γϟπɺ௕କΫϧʔωοΫ̩γϟπͳͲ
IUUQ[P[PKQQC

View Slide

3FETIJGU࣌୅ͷ঺հ σʔλϑϩʔਤ

4
%#
3FETIJGU
%BUB1JQFMJOF
%#
5BMFOEͰసૹ 
೔࣍όον

ɾɾɾ
0O1SFN%#T %BUB-BLF
%BUB8BSF)PVTF
ҎԼͷͭΛ؅ཧ
ɾ4ˠ3FETIJGUͷσʔλసૹ
ɾͦͷޙͷूܭΫΤϦͷ࣮ߦ

View Slide

4ʹೖΕ͍ͯͨσʔλ
w;0;0508/΍8&"3ͷϚελσʔλ
w Ϣʔβʔ৘ใ
w ঎඼৘ใ
w ஫จ৘ใ
w(PPHMF"OBMZUJDT͔Βऔಘͨ͠ΞΫηεϩά 8FCɾωΠςΟϒΞϓϦ

wϝʔϧ΍ϓογϡͷ഑৴ϩά
w૯ςʔϒϧ਺
w૯σʔλαΠζ5#

View Slide

0O1SFN%#Tˠ4΁ͷసૹʹ͸5BMFOEΛ࢖༻
w5BMFOE0QFO4UVEJPͱ͍͏ແঈͷ&5- &YUSBDU 5SBOTGFS -PBE
πʔϧΛ࢖༻
w(6*Ͱ&5-ॲཧΛهड़͢Δ
w5BMFOEͰهड़ͨ͠&5-όονΛ8JOEPXTλεΫεέδϡʔϥʔͰىಈ
ͭͭͷΞΠίϯ͕࣮ߦ͢ΔॲཧΛදݱ
ɾϑΝΠϧೖग़ྗ $47 &YDFM FUD

ɾ%#ೖग़ྗ 42-4FSWFS 1PTUHSF42- 0SBDMF FUD

ɾσʔλฤू ஔ׵ɺιʔτɺूܭͳͲ

&5-ॲཧͷྫ

View Slide

4ˠ3FETIJGU΁ͷసૹʹ͸%BUB1JQFMJOFΛ࢖༻
wσʔλϫʔΫϑϩʔΦʔέετϨʔγϣϯαʔϏε
w4ˠ3FETIJGUͱ3FETIJGU্ͰͷूܭΫΤϦͷ࣮ߦΛ୲౰
w3FETIJGU$PQZ"DUJWJUZͰ4ˠ3FETIJGUͷసૹΛ࣮ݱ
wసૹ׬ྃޙʹ4RM"DUJWJUZͰ3FETIJGU্Ͱͷσʔλूܭͷ42-จΛ࣮ߦ
w౰࣌ͷ໰୊఺
w5BMFOEʹΑΔసૹ͕׬ྃͨ͠ޙʹ%BUB1JQFMJOF͕ಈ͘ඞཁ͕͋Δ͕ɺىಈ࣌ࠁ͸ܾΊଧͪ
wσʔλϚʔτूܭͷͨΊͷ4RM"DUJWJUZಉ࢜ͷґଘײؔ܎͸ਓ͕ؒొ࿥

View Slide

#JH2VFSZҠߦ΁ͷಈػ
w3FETIJGUͰ͸ҎԼͷ΋ͷΛ؅ཧ͢Δඞཁ͋Γ ϊ΢ϋ΢΋ෆ଍

w ෼ࢄΩʔɾιʔτΩʔ
w ετϨʔδͷۭ͖༰ྔ
w ϊʔυ਺ɾΠϯελϯελΠϓ
w#JH2VFSZͷํ͕ΑΓϚωʔδυͳαʔϏε
w ΠϯσοΫεͱ͍͏֓೦ͳ͘ɺϚγϯύϫʔΛڧҾʹ࢖ͬͯϑϧεΩϟϯ
w ॠؒతʹ͸୆Ҏ্ͷͷϊʔυͰܭࢉ͞ΕΔ͜ͱ΋͋Γ
w ετϨʔδ͸࣮࣭ແݶͰ͍҆ 5#NPOUI

wͱ͜ΖͰɺ"UIFOB͸ʁ
w ೥݄ࠒ͸౰࣌͸ొ৔ͨ͠͹͔Γ
w ΫΤϦͷ࣮ߦ଎౓͸#JH2VFSZͷํ͕ߴ଎
w ˞σʔλͷอଘܗࣜΛྻࢦ޲ʹͨ͠Βߴ଎ʹͳ͍͔ͬͯͨ΋ʢʁʣ
3FETIJGU #JH2VFSZ

View Slide

%BUB-BLFҠߦ
4
%#
3FETIJGU
%#
5BMFOEͰసૹ 
೔࣍όον

ɾɾɾ
0O1SFN%#T %BUB-BLF
#JH2VFSZ
4ʹอଘ͞Ε͍ͯΔDTWH[Λ
#JH2VFSZʹ೔࣍Ͱϩʔυ

View Slide

4ˠ#JH2VFSZͷͨΊʹ࢖༻ͨ͠ཁૉٕज़
w&NCVML
w 5SFBTVSF%BUB੡ͷ044
w &5-πʔϧ
w େྔͷσʔλΛόονసૹ͢Δ͜ͱʹಛԽ
w σʔλͷೖྗɾՃ޻ɾग़ྗΛ͢Δ෦෼͸ϓϥάΠϯͱͯ͠ఏڙ
w ઃఆϑΝΠϧ͸:".-Ͱॻ͔Ε͍ͯΔ
w%JHEBH
w ͪ͜Β΋5SFBTVSF%BUB੡ͷ044
w ϫʔΫϑϩʔ؅ཧπʔϧ
w େྔͷ&NCVMLδϣϒΛޮ཰తʹ؅ཧ
w ฒྻ࣮ߦɾϦτϥΠɾλεΫͷ࣮ߦঢ়گͷՄࢹԽɾ࣮ߦϩάͷදࣔ
w ઃఆϑΝΠϧ͸:".- ૬౰ͷϑΥʔϚοτ
Ͱॻ͔Ε͍ͯΔ

View Slide

&NCVMLͷ:".-঺հ
in:
type: file
path_prefix: /path/to/csv_files
decoders:
- {type: gzip}
parser:
charset: UTF-8
newline: LF
type: csv
delimiter: ','
quote: '"'
escape: '"'
null_string: 'NULL'
trim_if_not_quoted: false
skip_header_lines: 1
allow_extra_columns: false
allow_optional_columns: false
columns:
- {name: id, type: long}
- {name: account, type: long}
- {name: time, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
- {name: purchase, type: timestamp, format: '%Y%m%d'}
- {name: comment, type: string}
out: {type: stdout}
$47ϑΝΠϧΛTUEPVUʹग़ྗ
సૹઃఆ͸͢΂ͯ:".-Ͱॻ͔Ε͍ͯΔ 
ҎԼͷ෦෼ΛϓϥάΠϯͰॻ͘͜ͱ͕ग़དྷΔ
ɾJOQVU ϑΝΠϧγεςϜ΍4ͳͲ͔ΒಡΈग़͠

ɾEFDPEFS ѹॖϑΝΠϧͷղౚͳͲ

ɾQBSTFS σγϦΞϥΠζ

ɾGJMUFS ϋογϡԽͳͲͷσʔλͷม׵ॲཧ

ɾPVUQVU ϑΝΠϧγεςϜ΍4΁ͷॻ͖ग़͠

ɾFODPEFS ϑΝΠϧͷѹॖͳͲ

ɾGPSNBUUFS γϦΞϥΠζ

View Slide

&NCVMLͷ:".-঺հ XJUI-JRVJE

in:
type: file
path_prefix: { env.FILENAME } # ①
decoders:
- {type: gzip}
parser:
{% include 'common/csv_format' %} # ②
columns:
- {name: id, type: long}
- {name: account, type: long}
- {name: time, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
- {name: purchase, type: timestamp, format: '%Y%m%d'}
- {name: comment, type: string}
out: {type: stdout}
$47ϑΝΠϧΛTUEPVUʹग़ྗ
&NCVML͸-JRVJEςϯϓϨʔτΤϯδϯΛ౥ࡌ
ҎԼͷ޻෉ʹΑͬͯ:".-ϑΝΠϧΛ%3:ʹͰ͖Δ
ᶃ ؀ڥม਺Λ:".-ϑΝΠϧʹల։
ᶄ ڞ௨͢ΔॲཧΛଞͷϑΝΠϧʹॻ͖ग़ͯ͠ಡΈࠐΈ
JGͱ͔GPSͱ͔ͷ੍ޚߏจΛ࢖͏͜ͱ΋ग़དྷΔ

View Slide

%JHEBHͷϫʔΫϑϩʔ঺հ
+my_task_1:
sh>: "echo this task runs first."
+my_task_2:
sh>: "echo this task runs next."
ஞ࣮࣍ߦ
+run_if_param_is_true:
if>: ${param}
_do:
echo>: ${param} == true
৚݅෼ذ
+repeat:
for_each>:
fruit: [apple, orange]
_do:
echo>: ${fruit}
ϧʔϓ
+repeat:
for_each>:
fruit: [apple, orange]
_parallel: true
_do:
echo>: ${fruit}
ฒྻ࣮ߦ
:".- ˞ݫີʹ͸ҧ͏
ͰϫʔΫϑϩʔΛهड़
खଓ͖ܕݴޠʹࣅͨงғؾ
γʔέϯγϟϧͳϧʔϓΛฒྻ࣮ߦʹ੾Γସ͑Δͷ͕؆୯

View Slide

%JHEBHͷϫʔΫϑϩʔ঺հ XJUI3VCZ

timezone: UTC
+set_tables:
require: tasks.rb
rb>: set_tables
+transfer_tables:
for_each>:
table: ${tables}
_parallel: true
_do:
sh>: embulk run embulk/${table}.yml
def set_tables
tables = Dir.glob('embulk/*.yml')
Digdag.env.store(tables: tables)
end
TBNQMFEJH UBTLTSC
FNCVMLҎԼʹ֨ೲ͞Ε͍ͯΔ͢΂ͯͷసૹఆٛΛಡΈग़ͯ͠
ͦΕΒΛҾ਺ʹͯ͠FNCVMLΛฒྻʹ࣮ߦ

View Slide

ͳͥ5BMFOEΛ࢖͍ଓ͚ͳ͔ͬͨͷ͔
w&NCVML%JHEBHΛ࢖͏͜ͱͰʮී௨ʯͷιϑτ΢ΣΞ։ൃͰͷϊ΢ϋ΢Λ׆͔͔ͨͬͨ͠
w సૹઃఆͷϫʔΫϑϩʔͷ:".-ΛHJUIVCͰόʔδϣϯ؅ཧ
w ຊ൪؀ڥ΁ग़͢લʹνʔϜ಺ͰϨϏϡʔ
w $JSDMF$*ͳͲͷ$*4BB4Ͱࣗಈςετ
w ຊ൪൓ө࡞ۀͷࣗಈԽ
w%JHEBHΛ࢖͏͜ͱͰλεΫͷฒྻ౓ͷௐ੔Λ͠΍͔ͨͬͨ͘͢͠
w &NCVML͸ՄೳͳݶΓϚϧνεϨουͰಈ࡞ͯ͠$16ΛߜΓਚ͘͢
w $16ͷڧ͍ΠϯελϯεΛ૿΍ͯ͠ෳ਺୆Ͱ෼ࢄ࣮ߦͤ͞Δ͜ͱͰશςʔϒϧͷసૹ࣌ؒΛ୹ॖԽ

View Slide

σʔλϨΠΫҠߦखॱ
4
%#
3FETIJGU
%#
5BMFOEͰసૹ 
೔࣍όον

ɾɾɾ
0O1SFN%#T
ᶃ 4ͷશσʔλDTWϑΝΠϧʹରͯ͠ 
FNCVMLHVFTTΛ࣮ߦ͠సૹઃఆͷ਽ܗΛ࡞Δ
ᶄ͢΂ͯͷϑΝΠϧΛ#JH2VFSZʹసૹͯ͠ΈΔ
ᶅ3FETIJGUͱͷࠩ෼͕ͳ͘ͳΔ·ͰసૹઃఆΛमਖ਼

View Slide

3FETIJGUͱ#JH2VFSZͰͷσʔλࠩҟͷݪҼ
wଟ͘ͷ৔߹͸&NCVMLͷ:".-Ͱͷܕࢦఆϛε
w &NCVMLHVFTTͰ͸DTWϑΝΠϧͷεΩʔϚΛͷਫ਼౓ͰਪଌͰ͖ͳ͍ ܕ৘ใ͕ͳ͍$47ͷݶք

w ઌ಄ߦͷΈΛΈͯεΩʔϚͷਪଌΛߦ͏
w ϑΝΠϧͷޙํͰ༧૝֎ͷσʔλ͕͋Δ͜ͱ΋͋Δ͜ͱ΋͋Δ
w &NCVMLͷ࣮ߦϩάͰεΩοϓ͞ΕͨߦΛ֬ೝͯ͠ɺ3FETIJGUͷεΩʔϚͱಥ͖߹Θͤ
w ˞ࠓʹࢥ͑͹࠷ॳʹ3FETIJGUͷεΩʔϚͱͷಥ͖߹ΘͤΛ͓͚ͯ͠͹ྑ͔ͬͨɾɾɾ
w&NCVMLͷDTWύʔαʔ͸จࣈྻதͷaΛਖ਼͘͠ύʔεग़དྷͳ͍
w ԿճϦτϥΠͯ͠΋ফ͑ͳ͍ΤϥʔΛ͔֬ΊΔͨΊʹόΠφϦΤσΟλͰ֬ೝ
w &NCVMLͷιʔείʔυΛ֬ೝͯ͠ݪҼʹؾ෇͘
w (JU)VCʹ*TTVF͸্͕͍ͬͯΔ͚Ͳ௚͞Ε͍ͯͳ͍ɾɾɾ

View Slide

4ˠ#JH2VFSZͷసૹͷߴ଎ԽͷͨΊʹߦͬͨ͜ͱ
wFNCVMLPVUQVUCJHRVFSZϓϥάΠϯͷσʔλసૹͷ༷ࢠ͸Լਤͷͱ͓Γ
w($4ʹ഑ஔ͢ΔϑΝΠϧΛແѹॖʹ͢Δͱసૹ଎౓͕਺ഒߴ଎ʹͳͬͨ ᶅͷ෦෼

w($4͔Β#JH2VFSZ΁ͷσʔλϩʔυ͕ฒྻʹͳͬͨ͜ͱ͕ཁҼ
ৄࡉͳ࿩͸ͪ͜Β
IUUQTRJJUBDPNTIJP[BLJJUFNTCFDG

View Slide

ࠩ෼ߋ৽ͷ࢓૊Έ 3FETIJGU%BUBQJQFMJOF

wҰ෦ͷڊେͳςʔϒϧ͸ຖ೔શྔߋ৽Λ͍ͯ͠ͳ͍
w࠷ۙʹมߋͷ͋ͬͨߦͷΈ͕4ʹ഑ஔ͞ΕΔ
w3FETIJGU$PQZ"DUJWJUZͷJOTFSU.PEFΦϓγϣϯͰࠩ෼ߋ৽Λ࣮ݱ
w "11&/% ୯७ʹςʔϒϧʹ௥ه

w 07&383*5&@&9*45*/(Λ࢖༻ ओΩʔΛࢦఆ͠ɺओΩʔͷॏෳ͕͋ͬͨΒ্ॻ͖

View Slide

ࠩ෼ߋ৽ͷ࢓૊Έ #JH2VFSZ

wಉ༷ͷࠩ෼ߋ৽ͷ࢓૊ΈΛ#JH2VFSZͰ࡞Δඞཁ͕͋Γ
w"11&/%ํࣜͷࠩ෼ߋ৽
w 4%,͔ΒΫΤϦΛ౤͛Δ࣌ͷXSJUFΦϓγϣϯͰBQQFOEΛࢦఆ
w07&383*5&ํࣜͷࠩ෼ߋ৽
w 8*/%08ؔ਺Ͱچςʔϒϧͷσʔλ͔৽ςʔϒϧͷσʔλ͔Λ൑ผ
w ॏෳ͢Δ৔߹͸৽ςʔϒϧͷσʔλͷΈΛ࢒͢
w ࢀߟ਺ඦ(#ͷσʔλΛ.Z42-͔Β#JH2VFSZʹಉظ͢Δ
w IUUQTUFDINFSDBSJDPNFOUSZ
w͜Εͩͱߋ৽ॲཧ͕ႈ౳ʹͳΒͳ͍ͷͰɺ΋͏Ұ೧Γͨ͠

View Slide

ႈ౳ͳࠩ෼ߋ৽ͷ࢓૊Έ #JH2VFSZ

w೔ຖͷόοΫΞοϓςʔϒϧΛ࡞Δ͜ͱͰࠩ෼ߋ৽Λႈ౳ʹ
wᶃʙᶅͷͲͷॲཧͰམͪͯ΋҆શʹ࠷ॳ͔Β΍Γ௚͢͜ͱ͕Ͱ͖Δ
w#JH2VFSZʹ͸τϥϯβΫγϣϯ͕ͳ͍ͷͰɺΫΤϦΛ౤͛ΔଆͰௐ੔
ᶃDPQZ ᶅDPQZ
ᶄBQQFOE
[email protected]@ [email protected]@ [email protected]
table_yesterday = table('table_name_20181201')
table_yesterday.copy('table_name_20181202') # ①
table_today = table('table_name_20181202')
query("集計クエリ", destination_table: 'table_name_20181202', write: 'append') # ②
table_today.copy('table_name') # ③

View Slide

σʔλϚʔτͷҠߦ
4
%#
3FETIJGU
%#
5BMFOEͰసૹ 
೔࣍όον

ɾɾɾ
0O1SFN%#T
#JH2VFSZ
%BUB1JQFMJOF
%BUB1JQFMJOFͰ࣮ߦ͞Ε͍ͯͨ42-Λ
#JH2VFSZʹҠ২

View Slide

σʔλϚʔτͷҠߦ
w3FETIJGUͷ42-Ͱॻ͔ΕͨσʔλϚʔτूܭ༻ͷΫΤϦΛ#JH2VFSZͷ42-ʹม׵
w1PTUHSF42-ํݴͷ42-Λ#JH2VFSZํݴͷ42-ʹॻ͖׵͑
wඞཁͳ΋ͷࠜؾ
w/6--ΛؚΜͩͱ͖ͷ+0*/ͷڍಈ͕ҧͬͨΓ
wؔ਺ͷҾ਺ͷॱ൪͕ҧͬͨΓ
wҰ෦ͷؔ਺͸#JH2VFSZʹͳ͔ͬͨΓ
wFUD

View Slide

σʔλϚʔτͷूܭͷґଘؔ܎ղܾ
w͋ΔσʔλϚʔτ͕ผͷσʔλϚʔτͷूܭ݁ՌΛ࢖͍ͬͯΔ৔߹ɺґଘؔ܎͕ൃੜ
wॱ൪͸कΓͭͭɺՄೳͳݶΓฒྻ౓͸্͍͛ͨ
wNBLFίϚϯυͷKΦϓγϣϯΈ͍ͨͳײ͡
EBUBNBSU
EBUBNBSU
EBUBNBSU
4&-&$5ʙ'30.EBUBNBSU
+0*/EBUBNBSUʙ
EBUBNBSUͷूܭΑΓ΋ઌʹूܭ

View Slide

σʔλϚʔτͷ42-จΛղੳͯ͠ґଘؔ܎Λάϥϑߏ଄ %"(
ʹ͢Δ 
sql.scan(/(?:FROM|JOIN)\s+`(.+?)`/i)
%"(Λղੳ͍͔ͯͭ͘͠ͷฒྻ࣮ߦՄೳͳάϧʔϓʹ෼ׂ
" #
#EFQFOETPO"
ᶃ ᶄ

View Slide

σʔλϚʔτͷ42-จΛղੳͯ͠ґଘؔ܎Λάϥϑߏ଄ %"(
ʹ͢Δ 
sql.scan(/(?:FROM|JOIN)\s+`(.+?)`/i)
%"(Λղੳ͍͔ͯͭ͘͠ͷฒྻ࣮ߦՄೳͳάϧʔϓʹ෼ׂ
" #
#EFQFOETPO"
ᶃ ᶄ
ݱࡏͷґଘؔ܎άϥϑ
਺ݸͷσʔλϚʔτ

View Slide

Πϯϑϥ঺հ σʔλϑϩʔਤ

4
%#
#JH2VFSZ
%#
5BMFOEͰసૹ 
೔࣍όον

ɾɾɾ
σʔλूܭ
ϫʔΫϑϩʔ؅ཧ

View Slide

Πϯϑϥ঺հ αʔόʔߏ੒ਤ

%BUB$FOUFS
%JSFDU$POOFDU
71$1FFSJOH
0UIFS71$
($4
#JH2VFSZ
%JHEBHNBTUFS
"VSPSB
%JHEBHXPSLFS
"VUP4DBMJOH

ɾɾɾ
4
"-#

View Slide

Πϯϑϥ঺հ αʔόʔߏ੒ਤ

%BUB$FOUFS
%JSFDU$POOFDU
71$1FFSJOH
0UIFS71$
($4
#JH2VFSZ
%JHEBHNBTUFS
"VSPSB
%JHEBHXPSLFS
"VUP4DBMJOH

ɾɾɾ
4
"-#
δϣϒΩϡʔ
λεΫͷ࣮ߦঢ়گ
࣮ߦϩά
αʔόʔΛ૿΍ͯ͠
ฒྻ౓Ξοϓ
λεΫͷ࣮ߦ؀ڥ͸
%PDLFSίϯςφ

View Slide

৽ͨʹൃੜͨ͠໰୊ͱͦͷରॲ
wσʔλอଘྉۚ௓Ͷ্͕Γ 
ɹˠఆظతʹݹ͍σʔλΛ࡟আ
wΫΤϦྉۚ௓Ͷ্͕Γ 
ɹˠ1BSUJUJPOFE5BCMF
wຊ൪ʹग़͢·ͰམͪΔ͔ෆ໌ͳΫΤϦ 
ɹˠNBTUFSϚʔδલʹ$JSDMF$*Ͱͷࣗಈςετ

View Slide

σʔλอଘྉۚ௓Ͷ্͕Γ
wࠩ෼ߋ৽ͷ࣌ʹաڈͷςʔϒϧ͕࢒Γଓ͚Δ
wͭͷςʔϒϧͷαΠζ͕਺5#
w#JH2VFSZ͸ετϨʔδ͕ແ੍ݶͱ͸͍͓͑ۚ͸͔͔Δ
wରॲ೔෇ͷݹ͗͢Δςʔϒϧͷࣗಈ࡟আ
ᶃDPQZ ᶅDPQZ
ᶄBQQFOE
[email protected]@ [email protected]@ [email protected]
ແݶʹ૿͑Δաڈςʔϒϧ

View Slide

ΫΤϦྉۚ௓Ͷ্͕Γ
w͋ΔσʔλϚʔτͷεΩϟϯྔ͕਺5#ʹͳ͍ͬͯͨ
w4&-&$5ͳͲͷअѱͳ͜ͱ͸͍ͯ͠ͳ͍
w୯ʹඇৗʹॎ௕ͳϩάςʔϒϧͩͬͨ ਺೥෼

wඞཁͳσʔλ͸௚ۙͷ΋ͷ͚ͩ
wରॲ5JNF1BSUJUJPOFEςʔϒϧ
w5*.&45".1ܕͷྻΛࢦఆͯ͠ύʔςΟγϣϯ෼ׂ
w8)&3&۟ͰߜΓࠐΉ͜ͱͰεΩϟϯྔΛ཈͑ΒΕΔ

View Slide

ຊ൪ʹग़͢·ͰམͪΔ͔ෆ໌ͳΫΤϦ
wσʔλϚʔτ࡞੒༻ͷΫΤϦͷϛε͕සൃ
wจࣈίʔυ͕4+*4
wςʔϒϧ໊Λࢀর͢Δͱ͖ͷϓϩδΣΫτ໊๨Ε
w॥؀ࢀর͢ΔσʔλϚʔτ
wFUD
w$JSDMF$*ͰࣗಈςετΛͯ͠ຊ൪ʹग़͢લʹݕ஌
164) */70,&
$PEF%FQMPZ
$PEF1JQFMJOF
*/70,&
ϫʔΫϑϩʔͷຊ൪൓ө

View Slide

#JH2VFSZͷར༻Λ͓ߟ͑ͷํ΁ͷΞυόΠε
w#JH2VFSZ͸ύϫϑϧ͗͢ΔͷͰɺ՝ֹۚʹ͝༻৺
w ਺5#ͷσʔλΛҰॠͰεΩϟϯ͢Δ
w 2VPUBͷઃఆ
w ՝ֹۚΛຖ೔νΣοΫ
w೔ຊޠυΩϡϝϯτΛ৴༻͠ͳ͍
w ӳޠ൛ʹ͔͠ଘࡏ͠ͳ͍ػೳ
w ӳޠ൛Ͱ͸#FUBͷදه͕औΕ͍ͯΔػೳ
wӳޠυΩϡϝϯτ͢Β৴༻͠ͳ͍
w ࠷ऴతʹҰ൪৴པͰ͖Δͷ͸4%,ͷιʔείʔυͱ3&45"1*ͷυΩϡϝϯτ
w 4%,ͷ࢖͍ํΛ஌Γ͍ͨ৔߹͸ɺυΩϡϝϯτͰ͸ͳ͘6OJU5FTUΛಡΈ·͠ΐ͏
w ˞3VCZ൛ͷ4%,͚ͩͷ໰୊͔΋͠Εͳ͍

View Slide

͜Ε͔Βͷల๬
4
%#
#JH2VFSZ
%#
5BMFOEͰసૹ 
೔࣍όον

ɾɾɾ
σʔλूܭ
ϫʔΫϑϩʔ؅ཧ
͜ͷ෦෼΋&NCVMLʹஔ͖׵͑
4Λհͣ͞ʹ௚઀#JH2VFSZʹసૹ
ΦϯϓϨ%#Ͱߦ͍ͬͯΔूܭॲཧΛ
#JH2VFSZʹΦϑϩʔυ

View Slide

·ͱΊ
w;0;0508/ͷ%8)Λ3FETIJGU͔Β#JH2VFSZʹҾͬӽͨ͠͠
w#JH2VFSZʹͨ͜͠ͱʹΑͬͯӡ༻ෛՙͷ௿ݮʹܨ͕ͬͨ
w3FETIJGU࣌୅ͱൺ΂ͯਓ͕ؒؾʹ͢Δඞཁͷ͋ΔʮϞϊʯ͕ݮͬͨ
w׬શϚωʔδυαʔϏεͳͷͰྗٕΛ࢖͍΍͍͢
w·ͩ·ͩ΍Γ͍ͨ͜ͱ͕ଟ͗ͯ͢ਓ͕଍Γͳ͍
wϒʔε΋ग़͍ͯ͠ΔͷͰؾܰʹ༡ͼʹདྷ͍ͯͩ͘͞ʙ
wIUUQTUFDI[P[PDPNSFDSVJU

View Slide

ZOZOTOWNのDWHをRedshiftからBigQueryにお引越しした話 / Moving ZOZOTOWN DWH from Redshift to BigQuery

ZOZOTOWNのDWHをRedshiftからBigQueryにお引越しした話 / Moving ZOZOTOWN DWH from Redshift to BigQuery

Takehiro Shiozaki

More Decks by Takehiro Shiozaki

Other Decks in Technology

Featured

Transcript