Save 37% off PRO during our Black Friday Sale! »

S3にあるデータをAthenaのクエリで取得してLambda (Pandas) で加工してみた / akiba-aws-online7

82d6167c4d14393c2e20b37a74b363c5?s=47 kasacchiful
November 12, 2021

S3にあるデータをAthenaのクエリで取得してLambda (Pandas) で加工してみた / akiba-aws-online7

2021/11/12 (金) に、AKIBA.AWS にて発表した資料です。

82d6167c4d14393c2e20b37a74b363c5?s=128

kasacchiful

November 12, 2021
Tweet

Transcript

  1. 4ʹ͋ΔσʔλΛ"UIFOBͷΫΤϦͰऔಘ ͯ͠-BNCEB 1BOEBT ͰՃ޻ͯ͠Έͨ Ϋϥεϝιουגࣜձࣾɹּݪ޺  ",*#""840OMJOF

  2. ຊ೔͓࿩͢͠Δ͜ͱ  wҰൠతʹɺ"UIFOB͸෼ੳ༻్ʹ࢖͍·͕͢ɺࠓճ͸4όέοτʹ͋Δ σʔλΛऔಘ͢Δ༻్Ͱ࢖ͬͨࡍͷ͓࿩Λ͠·͢ɻ w"UIFOBΫΤϦΛ-BNCEB͔Β࣮ߦ͠ɺΫΤϦ݁ՌΛ-BNCEBͰऔಘͨ͠ ޙɺ1BOEBTͰσʔλՃ޻Λߦ͍·ͨ͠ɻ w"UIFOBΫΤϦ࣮ߦɾ݁Ռऔಘʹ͸ɺ"84%BUB8SBOHMFS͕ศརͰ͢ɻ

  3. ࣗݾ঺հ  w ּݪ޺ w ೥݄δϣΠϯ w σʔλΞφϦςΟΫεࣄۀຊ෦ϏδωειϦϡʔγϣϯ෦Ϗοά σʔλνʔϜ w

    ޷͖ͳ"84αʔϏε4ɺ-BNCEBɺ4BHF.BLFS w ৽ׁݝ৽ׁࢢࡏॅ w +"846(৽ׁࢧ෦ʗ1ZUIPOػցֶशษڧձJO৽ׁʗ+B445৽ׁʗ 48"/**ʗFUD @kasacchiful @kasacchiful
  4. +"846(/JJHBUB  ʮϓνΩϟονΞοϓձʯΛɺຖि໦༵ΑΓ։࠵ w IUUQTKBXTVHOJJHBUBDPOOQBTTDPN

  5.  ೔ ։࢝ w IUUQTQZNMOJJHBUBDPOOQBTTDPNFWFOU 1ZUIPOػցֶशษڧձJO৽ׁ 

  6. ຊ୊  AthenaΫΤϦͰσʔλऔಘ

  7. "NB[PO"UIFOB  4಺ͷσʔλΛඪ४42-Ͱ෼ੳՄೳͳαʔϏε w ඞཁͳ͜ͱ w 4όέοτʹ͋Δ෼ੳର৅ͷσʔλϑΝΠϧΛࢦఆ w εΩʔϚΛఆٛ (MVF%BUB$BUBMPHΛ࢖༻

     w 42-ΫΤϦΛॻ͍࣮ͯߦ
  8. ͜Μͳ༻్Ͱ"UIFOBΛ࢖ͬͨ  ෼ੳͰ͸ͳ͘ɺ&5-ͷz&zͷ༻్Ͱ࢖༻ w &YUSBDU நग़ Ͱ"UIFOBΛ࢖༻ w 4όέοτʹ͋ΔϑΝΠϧͷ਺͕ඇৗʹଟ͍ w

    ύʔςΟγϣϯԽ͞ΕͯϑΝΠϧ͕֨ೲ͞Ε͍ͯΔ w ෳ਺ϑΝΠϧʹ෼ࢄ͞Ε͍ͯΔσʔλͷҰ෦෼͚ͩ࢖͍͍ͨ w ෳ਺ϑΝΠϧͷσʔλΛ࿈݁ͯ͠࢖͍͍ͨ w 5SBOTGPSN-PBE͸-BNCEBͰ
  9. ࢼͯ͠ΈΔ  4಺ʹ͋Δ1BSRVFUσʔλΛ"UIFOBΫΤϦͰऔಘ w Πϯϓοτ4όέοτʹ͋Δ1BSRVFUϑΝΠϧ w ύʔςΟγϣϯԽ͞Εͯ֨ೲ w "UIFOBͰΫΤϦ࣮ߦˠ݁ՌΛ-BNCEBͰऔಘ w

    ΫΤϦ݁Ռ͕4όέοτʹอଘ͞ΕΔͷͰɺ-BNCEBͰऔಘ ͢Δ w ݁Ռ಺༰Λ֬ೝ w ࠓճ͸"1*ୟ͍ͯ-BNCEBىಈͤ͞ɺΫΤϦ݁ՌΛฦ͚ͩ͢ʹ ͢Δ
  10. ·ͣ͸࣮ߦ݁Ռ͔Β  -BNCEB͔Β"UIFOBΫΤϦ࣮ߦ͠ɺ݁ՌΛ1BOEBTʹ֨ೲͯ͠+40/ग़ྗ

  11. Πϯϓοτσʔλ  4όέοτ಺ʹ͋Δ1BSRVFUϑΝΠϧͷσʔλ w l4EBUBMBLFCVDLFUMPHVTFS@JEEBUFTBNQMFQBSRVFUz w VTFS@JEͱEBUF ZZZZNNEE ͰύʔςΟγϣϯԽ log1/1/2021/11/01/sample_data_64_0.parque

    t log1/1/2021/11/02/sample_data_59_0.parque t log1/1/2021/11/03/sample_data_56_0.parque t log1/1/2021/11/04/sample_data_57_0.parque t log1/1/2021/11/05/sample_data_58_0.parque t log1/1/2021/11/06/sample_data_65_0.parque t log1/1/2021/11/07/sample_data_61_0.parque t log1/1/2021/11/08/sample_data_63_0.parque t log1/1/2021/11/09/sample_data_62_0.parque t log1/1/2021/11/10/sample_data_60_0.parquet
  12. "UIFOBͷςʔϒϧఆٛ  (MVF%BUB$BUBMPH࢖༻ w ࠓճ͸4FSWFSMFTT'SBNFXPSLͷSFTPVSDFTͰఆٛ $MPVE'PSNBUJPO  w (MVF%BUBCBTF(MVF5BCMF"UIFOB8PSL(SPVQ

  13. "UIFOBͷςʔϒϧఆٛ  (MVF%BUB$BUBMPH࢖༻ w 5BCMF͸εΩʔϚઃఆ΍ύʔςΟγϣϯઃఆͳͲఆٛ

  14. -BNCEB࣮૷  -BNCEB͔Β"UIFOBʹΫΤϦ࣮ߦ͠ɺ݁ՌΛऔಘ͢Δ w -BNCEBʹ͸4ɺ"UIFOBɺ(MVF΁ͷΞΫηε͕Ͱ͖Δݖݶ͕ඞཁ w ݁ՌΛ-BNCEB্ͷ1BOEBTσʔλϑϨʔϜʹ֨ೲͰ͖Ε͹ɺ͋ͱ͸1BOEBTΛૢ࡞ͯ͠σʔ λՃ޻Ͱ͖Δ

  15. ΫΤϦ࣮ߦ  TUBSU@RVFSZ@FYFDVUJPO

  16. ΫΤϦ࣮ߦ  RVFSZ@FYFDVUJPO@JE͔ΒɺΫΤϦͷ࣮ߦঢ়ଶΛ֬ೝ

  17. ΫΤϦ࣮ߦ  ࣮ߦ݁ՌΛऔಘ

  18. "UIFOBΫΤϦ࣮ߦͷࢀߟهࣄ  IUUQTEFWDMBTTNFUIPEKQBSUJDMFTSVOBNB[POBUIFOBTRVFSZXJUIBXTMBNCEBKB

  19. ࣮ߦ݁Ռͷදࣔ  औಘ࣮ͨ͠ߦ݁Ռ͸ɺ1BOEBTσʔλϑϨʔϜΛ+40/Խͨ͠಺༰Λฦ͢

  20. ࣮ߦ݁Ռ ࠶ܝ  -BNCEB͔Β"UIFOBΫΤϦ࣮ߦ͠ɺ݁ՌΛ1BOEBTʹ֨ೲͯ͠+40/ग़ྗ

  21. ๊͍ͨײ৘  AthenaΫΤϦ࣮ߦ ໘౗ͩͳ

  22. "84%BUB8SBOHMFS͕ศར  IUUQTHJUIVCDPNBXTMBCTBXTEBUBXSBOHMFS

  23. "84%BUB8SBOHMFSͷಛ௃  1BOEBTͷػೳΛ"84ʹ֦ு͢ΔɺΦʔϓϯιʔεͷ1ZUIPOϥΠϒϥϦ w 1BOEBTσʔλϑϨʔϜͱ"84ͷσʔλؔ࿈ͷαʔϏεͱΛ͏·͘઀ଓͯ͘͠ΕΔ w 3FETIJGU(MVF"UIFOB&.3ͳͲ w ௨ৗͷ&5-λεΫʹඞཁͳؔ਺͕ἧ͍ͬͯΔ

  24. ΫΤϦ࣮ߦ  BUIFOBSFBE@TRM@RVFSZͰαΫοͱσʔλϑϨʔϜऔಘ

  25. ࣮ߦ݁Ռ "84%BUB8SBOHMFS࢖༻  -BNCEB͔Β"UIFOBΫΤϦ࣮ߦ͠ɺ݁ՌΛ1BOEBTʹ֨ೲͯ͠+40/ग़ྗ

  26. ஫ҙ఺  ϑΝΠϧαΠζ͕େ͖ͯ͘ɺͦͷ··ͩͱ-BNCEBʹ৐Βͳ͍ w -BNCEBͷσϓϩΠύοέʔδ͸ඇѹॖ࣌ʹ.#ҎԼʹ͢Δඞཁ͕͋Δ w "84%BUB8SBOHMFSΛී௨ʹQJQΠϯετʔϧ͢Δͱɺ.#௒͑Δ w (JU)VCͷ3FMFBTFϖʔδʹ͋Δɺ-BNCEB-BZFS༻ͷ[JQϑΝΠϧΛར༻͠Α͏

  27. "84%BUB8SBOHMFSΛ-BNCEBͰ࢖͏ࢀߟهࣄ  IUUQTEFWDMBTTNFUIPEKQBSUJDMFTVTFBXTXSBOHMFSJOBXTMBNCEB

  28. 4UFQ'VODUJPOTͷεςʔτϚγϯ্Ͱͷ࣮ߦ  4಺ʹ͋Δ1BSRVFUσʔλΛ"UIFOBΫΤϦͰऔಘ w Ұ൪࠷ޙͷ.BQλεΫʹ࣮ͯߦ w લஈͷ1BSBMMFMλεΫʹͯॲཧ͞Εͨෳ਺ϑΝΠϧ͔Βɺඞ ཁͳσʔλ͚ͩநग़͢Δࡍʹ"UIFOB࢖༻ w ฒྻ࣮ߦͷࡍ͸ɺ"UIFOBͷΫΥʔλʹ஫ҙ

  29. "UIFOBͷΫΥʔλ  "UIFOBͷ4UBSU2VFSZ&YFDVUJPOͷΫΥʔλ w .BQλεΫͰ"UIFOBͷΫΤϦΛ࣮ߦ͢ΔͱɺΫΥʔλʹҾ͔͔ͬΓ΍͍͢ w ্ݶ؇࿨ਃ੥͢Δ͔ɺεςʔτϚγϯͷ.BY$PODVSSFODZͰಉ࣮࣌ߦ਺Λ཈͑Δ͔ IUUQTEPDTBXTBNB[PODPNKB@KQBUIFOBMBUFTUVHTFSWJDFMJNJUTIUNM

  30. ·ͱΊ  • ҰൠతʹɺAthena͸෼ੳ༻్ʹ࢖͍·͕͢ɺࠓճ͸ S3όέοτʹ͋ΔσʔλΛऔಘ͢Δ༻్Ͱ࢖͍·͠ ͨɻ • AthenaΫΤϦΛLambda͔Β࣮ߦ͠ɺΫΤϦ݁ՌΛऔ ಘͯ͠PandasͰσʔλՃ޻Ͱ͖·͢ɻ •

    AthenaΫΤϦ࣮ߦɾ݁Ռऔಘʹ͸ɺAWS Data Wrangler͕ศརͰ͢ɻ
  31. None