dbt coreとFargateでデータ変換 / 20240928-jawsug-toyama-hokuriku-shinkansen

Slide 1

Slide 1 text

ECUDPSFͱ"84'BSHBUFͰ σʔλม׵ ּݪ޺ +"846(෋ࢁ๺཮৽װઢ

Slide 2

Slide 2 text

ࣗݾ঺հ @kasacchiful @kasacchiful ּݪ޺ wΫϥεϝιουגࣜձࣾσʔλࣄۀຊ෦ w৽ׁݝ৽ׁࢢࡏॅ w޷͖ͳ"84αʔϏε4ɺ-BNCEBɺ4UFQ'VODUJPOT w+"846(৽ׁࢧ෦ʗ1ZUIPOػցֶशษڧձJO৽ׁʗ+B445৽ׁ ʗ48"/**ʗ$MPVE fl BSF.FFUVQ/JJHBUBʗFUD w"84$PNNVOJUZ#VJMEFS+BQBO"84"MM $FSUJ fi DBUJPOT&OHJOFFS

Slide 3

Slide 3 text

ࠓ೔͸ࣗՈ༻ंͰ෋ࢁ΁

Slide 4

Slide 4 text

΋ͭࣽࠐΈ͏ͲΜ͕৯΂ͨͯ͘

Slide 5

Slide 5 text

ӽޙݠ৴4",&·ͭΓ IUUQTXXXLFOTIJOTBLFDPN

Slide 6

Slide 6 text

Ϋϥεϝιου্ӽΦϑΟε

Slide 7

Slide 7 text

ΫϥεϝιουͷڧΈʮ৘ใൃ৴ʯ

Slide 8

Slide 8 text

+"846(৽ׁ։࠵ܗࣜ ຊฤษڧձ wෆఆظ։࠵ʗʙ࣌ؒఔ౓ʗΦϑϥΠϯ wࣄલʹςʔϚܾΊͯ։࠵ w࣍ճ ౔ ੜ੒"*։ൃϋϯζΦϯ!৽ׁӺपล ϓνΩϟονΞοϓձ wຖि໦༵໷࣌ʗ࣌ؒఔ౓ʗΦϯϥΠϯ wͦͷ৔ͰؾʹͳΔهࣄݟͨΓɺ͓೰Έ૬ஊड͚ͨΓ

Slide 9

Slide 9 text

໨࣍ w&5-ͱ&-5 wECUDPSFʹ͍ͭͯ w'BSHBUFͰECUDPSFΛಈ͔͢ w؆୯ͳσϞ w·ͱΊ

Slide 10

Slide 10 text

ETLͱELT

Slide 11

Slide 11 text

Α͋͘Δߏ੒

Slide 12

Slide 12 text

Α͋͘Δߏ੒ (MVFͰ&5-ॲཧ 1BSRVFUϑΝΠϧΛ 4ʹอଘ "UIFOBͰ ෼ੳΫΤϦ࣮ߦ

Slide 13

Slide 13 text

&5-

Slide 14

Slide 14 text

&5- σʔλநग़ &YUSBDU σʔλม׵ 5SBOTQPSU σʔλอଘ -PBE

Slide 15

Slide 15 text

&5- σʔλநग़ &YUSBDU σʔλม׵ 5SBOTQPSU σʔλอଘ -PBE σʔλΛूΊͯɺܾ·ͬͨܗʹอଘ͢Δ

Slide 16

Slide 16 text

&-5

Slide 17

Slide 17 text

&-5 σʔλநग़ &YUSBDU σʔλม׵ 5SBOTQPSU σʔλอଘ -PBE σʔλΛूΊͨΒɺ༻్ʹԠͯ͡Ճ޻͢Δ

Slide 18

Slide 18 text

;FSP&5-౷߹Ͱ&-5 σʔλநग़ &YUSBDU σʔλม׵ 5SBOTQPSU σʔλอଘ -PBE

Slide 19

Slide 19 text

&5-ͱ&-5 &5- wܾ·ͬͨܗʹ੔͑ͯอଘ wσʔλΛѻ͍΍͘͢͢Δ &-5 wσʔλΛҰՕॴʹूΊͯɺՃ޻ wอଘઌͷ%#ΤϯδϯͰॲཧ 42-

Slide 20

Slide 20 text

dbt core

Slide 21

Slide 21 text

&-5ͷz5zΛѻ͏ wECU EBUBCVJMEUPPM ͸σʔλετΞ಺ͷՃ޻Λαϙʔτ ͢Δπʔϧ wECU-BCT͕ࣾ։ൃ w42-ͷ4&-&$5จΛϕʔεʹίϯϙʔωϯτԽɾύΠϓϥ ΠϯԽ͕Մೳ wECUDPSF 044 ͱECUDMPVE ༗ঈ൛ ͕͋Δ ECU

Slide 22

Slide 22 text

Ϟσϧͷྫ ECU {{ config( materialized='view' )}} with final as ( select c.customer_id, c.first_name, c.last_name, co.first_order_date, co.most_recent_order_date, coalesce(co.number_of_orders, 0) as number_of_orders from {{ ref('customers') }} as c left join {{ ref('customer_orders') }} as co on c.customer_id = co.customer_id ) select * from final

Slide 23

Slide 23 text

υΩϡϝϯτͷྫ ECU

Slide 24

Slide 24 text

+JOKBςϯϓϨʔτΛ࢖ͬͨΫΤϦ wෳࡶͳΫΤϦॲཧͷهड़͕Մೳ wศརͳϚΫϩ͕༻ҙ͞Ε͍ͯΔ ECU select c.customer_id, c.first_name, c.last_name, co.first_order_date, co.most_recent_order_date, coalesce(co.number_of_orders, 0) as number_of_orders from {{ ref('customers') }} as c left join {{ ref('customer_orders') }} as co on c.customer_id = co.customer_id

Slide 25

Slide 25 text

ςετ wTDIFNBZNMఆٛʹͯɺϞσϧ͕ظ଴௨Γಈ࡞͢Δ͔ςε τՄೳ ECU version: 2 models: - name: customers columns: - name: customer_id tests: - unique - not_null - name: stg_customers columns: - name: customer_id tests: - name: stg_orders columns: - name: order_id tests: - unique - not_null - name: status tests: - accepted_values: values: ['placed', 'shipped', 'completed', 'return_pending', 'returned'] - name: customer_id tests: - not_null - relationships: to: ref('stg_customers') field: customer_id

Slide 26

Slide 26 text

σϓϩΠ ࣮૷಺༰ͷ൓ө υΩϡϝϯτ࡞੒ɹɹɹɹɹυΩϡϝϯταʔόىಈ ςετ ECU dbt run dbt docs generate dbt test dbt docs serve

Slide 27

Slide 27 text

FargateͰdbt coreΛಈ͔͢

Slide 28

Slide 28 text

w&$4'BSHBUF্Ͱىಈ wECUBUIFOBར༻ w4UFQ'VODUJPOTͰ &$4λεΫىಈ w4όέοτ͸σʔλ༻ ͱυΩϡϝϯτ༻Λ ༻ҙ ߏ੒ਤ

Slide 29

Slide 29 text

w"84ྺ೥Ҏ্ wओʹ$MPVEGPSNBUJPOͰΠϯϑϥఆٛ w$%,ྺੲW৮͚ͬͯͨͲɺW͸͜͜೥͘Β͍ w'BSHBUFྺϋϯζΦϯఔ౓ wECUྺϋϯζΦϯఔ౓ ࢲͷεΩϧηοτ ݱࡏ

Slide 30

Slide 30 text

wDELϓϩδΣΫτ഑ԼʹECUϓϩδΣΫτΛ࡞੒ wDELUZQFTDSJQUECUQZUIPO "84ͷߏ੒͸$%,Ͱఆٛ ## CDKϓϩδΣΫτ࡞੒ npx cdk init --langage typescript mkdir dbt cd dbt pip install dbt-core dbt-athena-community ## dbtϓϩδΣΫτ࡞੒ dbt init dbt_src --profiles-dir .

Slide 31

Slide 31 text

σΟϨΫτϦߏ੒ྫ / ├─ bin │ └─ dbt_core_env.ts ## CDKؔ࿈ ├─ dbt ## Dockerίϯςφؔ࿈ │ ├─ dbt_src ## dbtؔ࿈ │ │ ├─ │ │ ├─ dbt_project.yml │ │ ├─ profiles.yml │ │ └─ run_dbt.sh │ ├─ Dockerfile │ └─ requirements.txt ├─ lib │ └─ dbt_core_env-stack.ts ## CDKελοΫؔ࿈ ├─ .gitignore ├─ cdk.json └─ <লུ>

Slide 32

Slide 32 text

$%,EFQMPZ ## CDKσϓϩΠ npx cdk deploy

Slide 33

Slide 33 text

wSVO@ECUTIΛ࣮ߦ͢ΔΑ͏ʹઃఆ %PDLFS fi MF FROM python:3.12 ADD dbt_src /dbt_src COPY requirements.txt / RUN pip install -U pip RUN pip install --no-cache-dir -r /requirements.txt RUN pip install --no-cache-dir awscli WORKDIR /dbt_src RUN chmod -R 755 . ENTRYPOINT [ "/bin/sh", "-c" ] CMD [ "./run_dbt.sh" ]

Slide 34

Slide 34 text

wECUSVO΍ECUEPDTHFOFSBUFΛ࣮ߦ͠ɺυΩϡϝϯτΛ 4όέοτʹίϐʔ SVO@ECUTI #!/bin/bash set -e echo "Running dbt commands" dbt run --profiles-dir . --project-dir . --target dev echo "" echo "Generate documentation files" dbt docs generate --profiles-dir . --project-dir . --target dev echo "" echo "Copying dbt documentation files for hosting" aws s3 cp target/ s3://dbtcoreenvkas-dbt-doc-bucket/dbt_core_env/ --recursive -- exclude="*" --include="*.json" --include="*.html" echo ""

Slide 35

Slide 35 text

w&$3ʹͯϓογϡίϚϯυ͕දࣔ͞ΕΔͷͰɺͦΕʹैͬͯ ΠϝʔδΛϓογϡ &$3ʹίϯςφΠϝʔδΛϓογϡ ## docker login aws ecr get-login-password --region ap-northeast-1 | docker login --username AWS -- password-stdin .dkr.ecr.ap-northeast-1.amazonaws.com ## docker build docker build -t dbtcoreenvkas-dbt-ecr-repo . ## tagging docker tag dbtcoreenvkas-dbt-ecr-repo:latest .dkr.ecr.ap- northeast-1.amazonaws.com/dbtcoreenvkas-dbt-ecr-repo:latest ## ECRʹϓογϡ docker push .dkr.ecr.ap-northeast-1.amazonaws.com/dbtcoreenvkas-dbt-ecr- repo:latest

Slide 36

Slide 36 text

εςʔτϚγϯىಈͯ͠ɺ'BSHBUFλεΫΛ࣮ߦ

Slide 37

Slide 37 text

"UIFOBʹECUͰఆٛͨ͠Ϗϡʔ͕࡞੒͞Ε͍ͯΔ

Slide 38

Slide 38 text

؆୯ͳσϞ

Slide 39

Slide 39 text

wࣗಈతʹQVCMJDQSJWBUFͷTVCOFUͭੜ੒ɻ wQVCMJDʹ/"5(BUFXBZɺQSJWBUFʹ'BSHBUFىಈɻ w1VCMJD4VCOFUͭʹ/"5(BUFXBZ͕֤ʑ഑ஔ͞ΕΔͷͰɺ ؾ͔ͮͳ͍ؒʹྉ͕ۚ݁ߏ͔͔ͬͯ͘ΔͷͰɺ஫ҙ 5JQT$%,Ͱ'BSHBUFىಈ͢Δࡍʹ71$ఆٛলུͰ͖Δ

Slide 40

Slide 40 text

wຖճ&$3ʹϓογϡίϚϯυ࣮ߦ͢Δͷ͕໘౗ wFDTQSFTTP͋ͨΓΛಋೖ͢Δͱ؆ུԽͰ͖ͦ͏ ߟྀࣄ߲'BSHBUFλεΫσϓϩΠΛ؆ུԽ͍ͨ͠ IUUQTEFWDMBTTNFUIPEKQBSUJDMFTFDTQSFTTPFDTEFQMPZNFOUUPPM

Slide 41

Slide 41 text

w·ͩશͯΛ௥͍͖Εͯͳ͍ͷͰɺֶशத w%FWFMPQFST*0΍༗ࣝऀͷྗΛआΓΔ ߟྀࣄ߲ECUͷϕετϓϥΫςΟε IUUQTEFWDMBTTNFUIPEKQBSUJDMFTECUCFTUQSBDUJDFCZECUBUIFOB

Slide 42

Slide 42 text

·ͱΊ

Slide 43

Slide 43 text

·ͱΊ wECU͸&-5ͷz5zΛѻ͏ɺσʔλετΞ಺ͷՃ޻Λߦ͏ͨ Ίͷπʔϧ w42-͕ѻ͑Ε͹ɺҰ௨ΓͷσʔλՃ޻͕ߦ͑Δ w'BSHBUFͰECUDPSFΛಈ͔ͤ͹ɺαʔόʔϨεʹىಈՄೳ wπʔϧΛదٓ૊Έ߹ΘͤͯɺΑΓྑ͍σʔλج൫ߏஙΛ໨ࢦ ͦ͏