リブセンスのデータ分析基盤を支えるRedshiftとAirflow

Slide 1

Slide 1 text

Slide 2

Slide 2 text

w ೥ೖࣾ w σʔλ෼ੳج൫ʮ-JWFTFOTF"OBMZUJDTʯͷ ։ൃɺӡ༻Λ΍ͬͯ·͢ w झຯ+Ϧʔά؍ઓ w ޷͖ͳ"84αʔϏε3FETIJGU4 ٢෢ਖ਼࢙ !UBLF@N גࣜձࣾϦϒηϯεςΫϊϩδΧϧϚʔέςΟϯά෦ σʔλϓϥοτϑΥʔϜάϧʔϓ ࣗݾ঺հ

Slide 3

Slide 3 text

w "QBDIF"JSGMPXͱ͸ w Ϧϒηϯεͷ"JSGMPXγεςϜߏ੒ w ϦϒηϯεͰͷ"JSGMPX׆༻ࣄྫ "HFOEB

Slide 4

Slide 4 text

Ϧϒηϯεͷσʔλ෼ੳج൫

Slide 5

Slide 5 text

w σʔλऩू w ϩάσʔλ ΞΫηεϩάɺΠϕϯτϩάɺΠϯϓϨογϣϯϩά w σʔλಉظ ࣄۀͷ8FCαʔϏε w ֎෦σʔλ ޿ࠂɺ4&0 w σʔλ෼ੳɺ׆༻ w ΞυϗοΫ෼ੳ w ,1*νΣοΫ w μογϡϘʔυ w σʔλιʔε w Ϩίϝϯυ w ޿ࠂΞτϦϏϡʔγϣϯ෼ੳ Ϧϒηϯεͷσʔλ෼ੳج൫

Slide 6

Slide 6 text

Ϧϒηϯεͷσʔλ෼ੳج൫ 3 F E T I J G U E D Y M B S H F º ς ʔϒϧ ਺ Ϩί ʔ υ ਺ ԯ Ϩί ʔ υ ར ༻ ༰ ྔ 5 # ΞΫ ςΟ ϒϢʔ β ໊ ΞυϗοΫɾΫΤϦ EBZ # * δϣϒ ɾ Ϋ Τ Ϧ E B Z Ϧ Ϧ ʔε ೥ ݄

Slide 7

Slide 7 text

Ϧϒηϯεͷσʔλ෼ੳج൫

Slide 8

Slide 8 text

Ϧϒηϯεͷσʔλ෼ੳج൫ ࠓ೔͸͜ͷ࿩Λ͠·͢

Slide 9

Slide 9 text

w ϫʔΫϑϩʔΛεέδϡʔϦϯάɺϞχλϦϯά͢ΔͨΊͷ πʔϧ w ݩʑ͸"JSCOC͕ࣾ։ൃ͠ɺࠓ͸"QBDIFιϑτ΢ΣΞࡒஂ ͷΠϯΩϡϕʔγϣϯϓϩάϥϜʹՃೖ w 1ZUIPOͰ࣮૷͞Ε͍ͯΔ "QBDIF"JSGMPXͱ͸

Slide 10

Slide 10 text

w ݩʑɺόονॲཧΛ3VCZͷSBLFͰॻ͍͍ͯͨ ࠓ΋ w ౰࣌ͷεέδϡʔϧ؅ཧ͸DSPO w XIFOFWFSͱ͍͏HFNΛ࢖ͬͯɺλεΫεέδϡʔϧͷ όʔδϣϯ؅ཧ͸͍ͯͨ͠ w ͚ͲݟͮΒ͍ͷͰTQSFBETIFFUͰ࣮ߦ࣌ؒͷ؅ཧΛͯ͠ ͍ͨ w ͔͠͠ஈʑλεΫɺόονͷྔ͕૿͖͑ͯͯʜ w ґଘؔ܎΋ෳࡶʹͳ͖ͬͯͯʜ w ఘΊͯɺδϣϒϚωδϝϯτπʔϧͷಋೖΛܾΊͨ "JSGMPXΛಋೖͨ͠ܦҢ

Slide 11

Slide 11 text

w "JSGMPXಋೖ౰࣌ɺൺֱͨ͠΋ͷ w -VJHJ w %JHEBH w 3VOEFDL w ਖ਼௚ɺͲΕ΋Ұ௕Ұ୹ͰܾΊख͸ͳ͔ͬͨʜ w ໰୊͕͋ͬͨ࣌ʹҰ൪ʮӡ༻ͰΧόʔʯग़དྷͦ͏ͩͬͨͷ ͕"JSGMPXͩͬͨ ଞͷδϣϒϚωδϝϯτπʔϧͱͷൺֱ

Slide 12

Slide 12 text

w δϣϒͷεέδϡʔϦϯά w DSPOܗࣜͰࢦఆՄೳ w λεΫͷґଘؔ܎ΛίʔυͰهड़ w 1ZUIPOͰ%"(΍λεΫΛهड़ w %"(༗ޮඇ८ճάϥϑɻ͜͜Ͱ͸λεΫؒͷ࣮ߦॱ ΍ґଘؔ܎Λࣔ͢άϥϑ w 8FC6*ʹΑΔλεΫ΍ϨϙʔτͷϏδϡΞϥΠζ "JSGMPXͰͰ͖Δ͜ͱ

Slide 13

Slide 13 text

w 1ZUIPOͷίʔυͰهड़ w 0QFSBUPSͰλεΫΛͭͣͭهड़ w CBTIίϚϯυΛ࣮ߦ͢Δ#BTI0QFSBUPSͷྫ λεΫͷهड़ # DAG dag = DAG(‘test_dag’, start_date=datetime(2018, 4, 6) # bashίϚϯυλεΫͷྫ test_task = BashOperator( task_id='test_task', bash_command='ls -al’, dag=dag)

Slide 14

Slide 14 text

w λεΫؒͷґଘؔ܎ΛϏοτγϑτԋࢉࢠΛ࢖ͬͯهड़ λεΫؒͷґଘؔ܎ # DAG dag = DAG(‘test_dag’, start_date=datetime(2018, 4, 6) # bashίϚϯυλεΫͷྫ task_ls = BashOperator( task_id='task_ls', bash_command='ls -al', dag=dag) task_echo = BashOperator( task_id='task_echo', bash_command='echo "hoge"', dag=dag) task_date = BashOperator( task_id='task_date', bash_command='date "+%F %T"', dag=dag) # λεΫؒͷґଘؔ܎ΛϏοτԋࢉࢠͰهड़ task_ls >> task_echo task_ls >> task_date

Slide 15

Slide 15 text

͜Μͳ%"(΋ඳ͚·͢

Slide 16

Slide 16 text

࢖༻Ͱ͖Δ0QFSBUPS w #BTI0QFSBUPSͷଞʹɺ1ZUIPO0QFSBUPS 1PTUHSFT0QFSBUPS 44)0QFSBUPSͳͲɻଞʹ΋৭ʑ w "84ؔ࿈ͩͱ w 3FETIJGU5P45SBOTGFS w 4'JMF5SBOTGPSN0QFSBUPS w &$40QFSBUPS w &NS"EE4UFQT0QFSBUPS ͳͲͷΑ͏ͳ΋ͷ΋͋Δ

Slide 17

Slide 17 text

"JSGMPX%"(Ұཡ w ొ࿥͞Ε͍ͯΔ%"(ͷϦετ w ࢦఆͷσΟϨΫτϦʹஔ͔Ε͍ͯΔ1ZUIPOͷιʔεϑΝΠ ϧ܈͔Βੜ੒

Slide 18

Slide 18 text

%"(5SFF7JFX w %"(ͷλεΫ͝ͱͷεςʔλεҰཡ w ͲͷλεΫ͕ɺ͍ͭͲͷΑ͏ͳεςʔλε͔ͩͬͨҰ໨ྎવ

Slide 19

Slide 19 text

%"(5BTL%VSBUJPO w %"(ͷλεΫ͝ͱͷ࣮ߦ࣌ؒͷਪҠάϥϑ

Slide 20

Slide 20 text

%"((BOUU w ࣮ߦ͞Εͨ%"(ͷΨϯτνϟʔτ w 5BTL%VSBUJPOͱ͋ΘͤͯϘτϧωοΫ͕ࢹ֮Խ

Slide 21

Slide 21 text

"JSGMPXͷαʔϏε

Slide 22

Slide 22 text

"JSGMPXͷαʔϏε WebGUIͷॲཧ DAGͷεέδϡʔϧ΍DAG RunͳͲ ϝλσʔλ͕֨ೲ͞ΕͨDB (DAG Run: DAGΛΠϯελϯεԽͨ͠΋ͷ)

Slide 23

Slide 23 text

"JSGMPXͷαʔϏε DAGΛ؂ࢹ͠ɺεέδϡʔϧʹ Ԡͯ͡DAG RunΛੜ੒ OperatorΛΩϡʔΠϯά

Slide 24

Slide 24 text

"JSGMPXͷαʔϏε OperatorͷॲཧΛ࣮ߦ ΩϡʔΠϯά͞Εͨ OperatorΛऔಘ Celeryͷ݁Ռ֨ೲ

Slide 25

Slide 25 text

&YFDVUPS w εέδϡʔϦϯάɺ΍λεΫͷ࣮ߦͷฒྻɺ෼ࢄॲཧΛͲͷ Α͏ͳ࢓૊ΈͰ࣮ߦ͢Δ͔Λܾఆ w 4FRVFOUJBM&YFDVUPS w -PDBM&YFDVUPS w $FMFSZ&YFDVUPS w %BTL&YFDVUPS w .FTPT&YFDVUPS w &YFDVUPSʹΑͬͯɺTDIFEVMFS΍XPSLFSͷڍಈ͕มΘΔ w 4FRVFOUJBM&YFDVUPSͷ৔߹ɺXPSLFS͕ෆཁʹͳΔ

Slide 26

Slide 26 text

Ϧϒηϯεͷ"JSGMPXӡ༻ߏ੒

Slide 27

Slide 27 text

Ϧϒηϯεͷ"JSGMPXӡ༻ߏ੒ Airflow Webserver Airflowϝλσʔλ؅ཧ AirflowϝοηʔδϒϩʔΧʔ (Celery)

Slide 28

Slide 28 text

Ϧϒηϯεͷ"JSGMPXӡ༻ߏ੒ Ruby rakeλεΫ GlueͰS3ͷσʔλ Λparquetม׵ Redshift΁ͷΫΤϦ ࣮ߦ EMR/SparkͰσʔ λม׵ॲཧ

Slide 29

Slide 29 text

w 3FETIJGUʹର͢ΔఆظతͳΫΤϦ࣮ߦ w αϚϦςʔϒϧͷੜ੒ͱ͔ɺ7"$66.ͷ࣮ߦ؅ཧͱ͔ w ϔϧενΣοΫܥΫΤϦͷ࣮ߦ w &.3ͷىಈτϦΨ w &.3࣮ߦલͷલॲཧɺޙॲཧΛهड़ w લॲཧऴྃޙɺ&.3ىಈɺTUFQ௥Ճɺॲཧऴྃ଴ͪɺΫϥε λఀࢭɺޙॲཧͷྲྀΕΛ%"(Ͱهड़ w 3FETIJGUͷΫΤϦͰॻ͘͜ͱ͕ࠔ೉ͳෳࡶͳॲཧΛ&.3Ͱॲ ཧ w (MVFىಈτϦΨ w 3FETIJGU4QFDUSVN༻ʹ1BSRVFUม׵͢Δॲཧ w &.3ಉ༷ɺ(MVF࣮ߦલͷલॲཧɺޙॲཧΛهड़ ͲͷΑ͏ʹ"JSGMPXΛ׆༻͍ͯ͠Δ͔

Slide 30

Slide 30 text

ґଘؔ܎ͷ͋Δσʔλੜ੒

Slide 31

Slide 31 text

ґଘؔ܎ͷ͋Δσʔλੜ੒ base_table͔Β table1Λੜ੒

Slide 32

Slide 32 text

ґଘؔ܎ͷ͋Δσʔλੜ੒ ੜ੒ͨ͠table1͔Β table2Λੜ੒

Slide 33

Slide 33 text

ґଘؔ܎ͷ͋Δσʔλੜ੒ ੜ੒ͨ͠table2͔Β table3Λੜ੒

Slide 34

Slide 34 text

*OUFSMFBWFETPSULFZ͕͋Δςʔϒϧੜ੒ͱ7"$66.

Slide 35

Slide 35 text

*OUFSMFBWFETPSULFZ͕͋Δςʔϒϧੜ੒ͱ7"$66. table1͔Βtable2Λ ੜ੒

Slide 36

Slide 36 text

*OUFSMFBWFETPSULFZ͕͋Δςʔϒϧੜ੒ͱ7"$66. table2ʹVACUUM REINDEXΛ ࣮ߦ͢ΔͨΊSQSʹenqueue

Slide 37

Slide 37 text

*OUFSMFBWFETPSULFZ͕͋Δςʔϒϧੜ੒ͱ7"$66. ผDAGͰఆظతʹ SQSϙʔϦϯά

Slide 38

Slide 38 text

*OUFSMFBWFETPSULFZ͕͋Δςʔϒϧੜ੒ͱ7"$66. SQSϝοηʔδऔಘͨ͠Βɺ table2ʹVACUUM REINDEX࣮ߦ

Slide 39

Slide 39 text

&.3Ͱॲཧ͞ΕͨσʔλΛ3FETIJGU΁$01:

Slide 40

Slide 40 text

&.3Ͱॲཧ͞ΕͨσʔλΛ3FETIJGU΁$01: EMRΫϥελΛੜ੒

Slide 41

Slide 41 text

&.3Ͱॲཧ͞ΕͨσʔλΛ3FETIJGU΁$01: EMRʹεςοϓΛ௥Ճ͠ɺ S3ͷσʔλΛม׵ɺੜ੒ EMRͰੜ੒͞ΕͨσʔλΛ S3ʹ֨ೲ

Slide 42

Slide 42 text

&.3Ͱॲཧ͞ΕͨσʔλΛ3FETIJGU΁$01: ੜ੒͞ΕͨS3ͷσʔλΛ RedshiftʹCOPY

Slide 43

Slide 43 text

&.3Ͱॲཧ͞ΕͨσʔλΛ3FETIJGU΁$01: EMRΫϥελΛ࡟আ

Slide 44

Slide 44 text

w λεΫͷεέδϡʔϧ΍ґଘؔ܎͕੔ཧͰ͖ͨ w (6*ͰҰ໨ྎવ w ίʔυϕʔεͰ%"(؅ཧ͕Ͱ͖ΔΑ͏ʹͳͬͨ w ࣮ߦ݁Ռɺ࣮ߦ࣌ؒ΋؅ཧͰ͖ΔΑ͏ʹͳͬͨ w ࣮ߦ݁Ռͷϩάͷ؅ཧָ͕ʹͳͬͨ w ࠓ·Ͱ͸όοναʔόʹೖͬͯɺ௚઀ϩάϑΝΠϧΛݟ Δ͔͠ͳ͔ͬͨ w (6*্Ͱ֬ೝͰ͖ɺো֐ͷݪҼͷௐࠪ΋ָʹͳͬͨ w 8PSLFS͕εέʔϧͰ͖ΔΑ͏ʹͳͬͨ w ͕ɺࠓͷͱ͜Ζؒʹ߹͍ͬͯΔͷͰɺ·ͩͯ͠ͳ͍ "JSGMPXΛಋೖͯ͠Α͔ͬͨ͜ͱ

Slide 45

Slide 45 text

w ఀࢭޙͷ෮چ͕݁ߏ໘౗ w 3FETIJGUͷϊʔυ௥ՃͳͲɺҰ࣌తʹఀࢭ͢Δඞཁ͕͋Δͱ͖ w ෮چޙɺཷ·͍ͬͯͨεέδϡʔϧ͕·ͱΊ࣮ͯߦ͞ΕΔ໰୊ w ϔϧενΣοΫΫΤϦͳͲ୹͍पظͰ࣮ߦ͞ΕΔ%"(ͱ͔ w ҰؾʹΫΤϦΛ౤͛ͯ͘ΔͷͰ%P4ঢ়ଶʹ w όʔδϣϯͰ$BUDIVQΛແޮʹ͢Δઃఆ͕ग़དྷͨͷͰɺղফ w &YFDVUJPO%BUFͱ࣮ࡍͷ࣮ߦ͕࣌ؒͣΕͯΔ w &YFDVUJPO%BUF͕ͳͷʹɺ࣮ࡍʹ͸ ʹ࣮ߦ͞ΕͯͨΓ w ଟ෼λΠϜκʔϯॲཧʹ໰୊͕͋ΔΜ͡Όͳ͍͔ͱਪଌ "JSGMPXӡ༻Ͱۤ࿑ͨ͜͠ͱ

Slide 46

Slide 46 text

w λεΫࣦഊޙͷ࠶࣮ߦ w %"(ͷ్தͰࣦഊͨ͠৔߹ɺ͋ͱͰࣦഊͨ͠ͱ͜Ζ͔Β࠶࣮ߦ ग़དྷΔ͕ɺࠓ͸खಈͰSBLFΛ௚઀࣮ߦ͍ͯ͠Δ w ࠶։ΦϖϨʔγϣϯΛ΋͏গָ͠ʹͰ͖ΔΑ͏ݟ௚͍ͨ͠ w λεΫ࣮૷ํ๏ w Ҏલͷӡ༻Ͱ࢖͍ͬͯͨ3VCZ3BLFλεΫΛͦͷ··࢖͍ͬͯ Δ͕1ZUIPOʹஔ͖׵͍͑ͯͬͨ΄͏͕͍͍͔Ͳ͏͔໎͍தʜ w "JSGMPX"1*ͷ0QFSBUPSΛ௚઀ୟ͚ΔϝϦοτ͸େ͖͍ w ͕ɺ%"(ͱϩδοΫ͸෼཭͍ͨ͠ w %PDLFSԽ w &$4΍&,4Ͱӡ༻Ͱ͖ΔΑ͏ʹ͍ͨ͠ʜ ࠓޙͷ՝୊

Slide 47

Slide 47 text

w ϦϒηϯεςΫϊϩδΧϧϚʔέςΟϯά෦ͷ೔ʑͷ׆ಈͰ֫ಘ ͨ͠৘ใΛެ։͍ͯ͠·͢ w IUUQBOBMZUJDTMJWFTFOTFDPKQ w "JSGMPXʹ͍ͭͯ΋ઌ೔هࣄΛެ։͠·ͨ͠ͷͰɺซͤͯ͝ࢀর͘ ͍ͩ͞ w IUUQBOBMZUJDTMJWFTFOTFDPKQFOUSZ 13-JWFTFOTF%BUB"OBMZUJDT#MPH