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

2024/09/28 (土) JAWS-UG富山#1 & JAWS-UG北陸新幹線#2 で発表した資料

イベントページ
https://jawsug-toyama.connpass.com/event/328774/

Transcript

1. ECU
   DPSFͱ"84
   'BSHBUFͰ
   σʔλม׵
   
   ּݪ
   ޺ +"846(
   ෋ࢁ
   
   ๺཮৽װઢ
2. ࣗݾ঺հ  @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
3. ࠓ೔͸ࣗՈ༻ंͰ෋ࢁ΁ 
4. ΋ͭࣽࠐΈ͏ͲΜ͕৯΂ͨͯ͘ 
5. ӽޙݠ৴4",&·ͭΓ  IUUQTXXXLFOTIJOTBLFDPN
6. Ϋϥεϝιου্ӽΦϑΟε 
7. ΫϥεϝιουͷڧΈʮ৘ใൃ৴ʯ 
8. +"846(
   ৽ׁ
   ։࠵ܗࣜ  ຊฤษڧձ
   wෆఆظ։࠵ʗʙ࣌ؒఔ౓ʗΦϑϥΠϯ
   wࣄલʹςʔϚܾΊͯ։࠵
   w࣍ճ ౔
   ੜ੒"*։ൃϋϯζΦϯ!৽ׁӺपล
   ϓνΩϟονΞοϓձ
   wຖि໦༵໷࣌ʗ࣌ؒఔ౓ʗΦϯϥΠϯ
   wͦͷ৔ͰؾʹͳΔهࣄݟͨΓɺ͓೰Έ૬ஊड͚ͨΓ
9. ໨࣍  w&5-ͱ&-5
   wECU
   DPSFʹ͍ͭͯ
   w'BSHBUFͰECU
   DPSFΛಈ͔͢
   w؆୯ͳσϞ
   w·ͱΊ
10.  ETLͱELT
11. Α͋͘Δߏ੒ 
12. Α͋͘Δߏ੒  (MVFͰ&5-ॲཧ
    1BSRVFUϑΝΠϧΛ 4ʹอଘ "UIFOBͰ
    ෼ੳΫΤϦ࣮ߦ
13. &5- 
14. &5-  σʔλநग़
    &YUSBDU σʔλม׵
    5SBOTQPSU σʔλอଘ
    -PBE
15. &5-  σʔλநग़
    &YUSBDU σʔλม׵
    5SBOTQPSU σʔλอଘ
    -PBE σʔλΛूΊͯɺܾ·ͬͨܗʹอଘ͢Δ
16. &-5 
17. &-5  σʔλநग़
    &YUSBDU σʔλม׵
    5SBOTQPSU σʔλอଘ
    -PBE σʔλΛूΊͨΒɺ༻్ʹԠͯ͡Ճ޻͢Δ
18. ;FSP&5-౷߹Ͱ&-5  σʔλநग़
    &YUSBDU σʔλม׵
    5SBOTQPSU σʔλอଘ
    -PBE
19. &5-ͱ&-5  &5-
    wܾ·ͬͨܗʹ੔͑ͯอଘ
    wσʔλΛѻ͍΍͘͢͢Δ
    &-5
    wσʔλΛҰՕॴʹूΊͯɺՃ޻
    wอଘઌͷ%#ΤϯδϯͰॲཧ
    42-
20.  dbt core
21. &-5ͷz5zΛѻ͏
    wECU
    EBUB
    CVJME
    UPPM
    ͸σʔλετΞ಺ͷՃ޻Λαϙʔτ ͢Δπʔϧ
    
    wECU
    -BCT
    ͕ࣾ։ൃ
    w42-ͷ4&-&$5จΛϕʔεʹίϯϙʔωϯτԽɾύΠϓϥ ΠϯԽ͕Մೳ
    wECU
    DPSF
    044
    ͱ
    ECU
    DMPVE
    ༗ঈ൛
    ͕͋Δ ECU 
22. Ϟσϧͷྫ 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
23. υΩϡϝϯτͷྫ ECU 
24. +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
25. ςετ
    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
26. σϓϩΠ
    ࣮૷಺༰ͷ൓ө
    υΩϡϝϯτ࡞੒ɹɹɹɹɹυΩϡϝϯταʔόىಈ
    ςετ ECU  dbt run dbt docs generate dbt test dbt docs serve
27.  FargateͰdbt coreΛಈ͔͢
28. w&$4
    'BSHBUF্Ͱىಈ
    wECUBUIFOBར༻
    w4UFQ
    'VODUJPOTͰ
    &$4λεΫىಈ
    w4όέοτ͸σʔλ༻
    ͱυΩϡϝϯτ༻Λ
    ༻ҙ ߏ੒ਤ 
29. w"84ྺ
    ೥Ҏ্
    wओʹ$MPVEGPSNBUJPOͰΠϯϑϥఆٛ
    w$%,ྺ
    ੲW৮͚ͬͯͨͲɺW͸͜͜೥͘Β͍
    w'BSHBUFྺ
    ϋϯζΦϯఔ౓
    wECUྺ
    ϋϯζΦϯఔ౓ ࢲͷεΩϧηοτ
    
    ݱࡏ 
30. wDELϓϩδΣΫτ഑ԼʹECUϓϩδΣΫτΛ࡞੒
    wDEL
    UZQFTDSJQU
    
    ECU
    QZUIPO "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 .
31. σΟϨΫτϦߏ੒ྫ  / ├─ bin │ └─ dbt_core_env.ts ## CDKؔ࿈ ├─ dbt ## Dockerίϯςφؔ࿈ │ ├─ dbt_src ## dbtؔ࿈ │ │ ├─ <dbtؔ࿈ϑΝΠϧɾσΟϨΫτϦ> │ │ ├─ dbt_project.yml │ │ ├─ profiles.yml │ │ └─ run_dbt.sh │ ├─ Dockerfile │ └─ requirements.txt ├─ lib │ └─ dbt_core_env-stack.ts ## CDKελοΫؔ࿈ ├─ .gitignore ├─ cdk.json └─ <লུ>
32. $%,
    EFQMPZ  ## CDKσϓϩΠ npx cdk deploy
33. 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" ]
34. wECU
    SVO
    ΍
    ECU
    EPDT
    HFOFSBUF
    Λ࣮ߦ͠ɺυΩϡϝϯτΛ 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 ""
35. w&$3ʹͯϓογϡίϚϯυ͕දࣔ͞ΕΔͷͰɺͦΕʹैͬͯ ΠϝʔδΛϓογϡ &$3ʹίϯςφΠϝʔδΛϓογϡ  ## docker login aws ecr get-login-password --region ap-northeast-1 | docker login --username AWS -- password-stdin <AWSΞΧ΢ϯτID>.dkr.ecr.ap-northeast-1.amazonaws.com ## docker build docker build -t dbtcoreenvkas-dbt-ecr-repo . ## tagging docker tag dbtcoreenvkas-dbt-ecr-repo:latest <AWSΞΧ΢ϯτID>.dkr.ecr.ap- northeast-1.amazonaws.com/dbtcoreenvkas-dbt-ecr-repo:latest ## ECRʹϓογϡ docker push <AWSΞΧ΢ϯτID>.dkr.ecr.ap-northeast-1.amazonaws.com/dbtcoreenvkas-dbt-ecr- repo:latest
36. εςʔτϚγϯىಈͯ͠ɺ'BSHBUFλεΫΛ࣮ߦ 
37. "UIFOBʹECUͰఆٛͨ͠Ϗϡʔ͕࡞੒͞Ε͍ͯΔ 
38.  ؆୯ͳσϞ
39. wࣗಈతʹQVCMJD
    
    QSJWBUFͷTVCOFUͭੜ੒ɻ
    wQVCMJDʹ/"5
    (BUFXBZɺQSJWBUFʹ'BSHBUFىಈɻ
    w1VCMJD
    4VCOFUͭʹ/"5
    (BUFXBZ͕֤ʑ഑ஔ͞ΕΔͷͰɺ ؾ͔ͮͳ͍ؒʹྉ͕ۚ݁ߏ͔͔ͬͯ͘ΔͷͰɺ஫ҙ 5JQT
    $%,Ͱ'BSHBUFىಈ͢Δࡍʹ71$ఆٛলུͰ͖Δ 
40. wຖճ&$3ʹϓογϡίϚϯυ࣮ߦ͢Δͷ͕໘౗
    wFDTQSFTTP
    ͋ͨΓΛಋೖ͢Δͱ؆ུԽͰ͖ͦ͏ ߟྀࣄ߲
    'BSHBUFλεΫσϓϩΠΛ؆ུԽ͍ͨ͠  IUUQTEFWDMBTTNFUIPEKQBSUJDMFTFDTQSFTTPFDTEFQMPZNFOUUPPM
41. w·ͩશͯΛ௥͍͖Εͯͳ͍ͷͰɺֶशத
    w%FWFMPQFST*0΍༗ࣝऀͷྗΛआΓΔ ߟྀࣄ߲
    ECUͷϕετϓϥΫςΟε  IUUQTEFWDMBTTNFUIPEKQBSUJDMFTECUCFTUQSBDUJDFCZECUBUIFOB
42.  ·ͱΊ
43. ·ͱΊ  wECU
    ͸
    &-5
    ͷ
    z5z
    Λѻ͏ɺσʔλετΞ಺ͷՃ޻Λߦ͏ͨ Ίͷπʔϧ
    w42-͕ѻ͑Ε͹ɺҰ௨ΓͷσʔλՃ޻͕ߦ͑Δ
    w'BSHBUFͰECU
    DPSFΛಈ͔ͤ͹ɺαʔόʔϨεʹىಈՄೳ
    wπʔϧΛదٓ૊Έ߹ΘͤͯɺΑΓྑ͍σʔλج൫ߏஙΛ໨ࢦ ͦ͏