SnowflakeにMySQLとJOINする機能を 実装する

#SnowVillage​ LIVE 004
https://www.youtube.com/watch?v=zY7z2IEtqCc

Transcript

1. SnowflakeʹMySQLͱJOIN͢ΔػೳΛ

   ࣮૷͢Δ
   גࣜձࣾαΠόʔΤʔδΣϯτ AIࣄۀຊ෦

   ࠇ࡚ ༏ଠ (@kuro_m88)
   

   View Slide

2. ࠇ࡚ ༏ଠ
   • גࣜձࣾαΠόʔΤʔδΣϯτ

   Dynalyst ։ൃ੹೚ऀ
   • ޿ࠂ഑৴γεςϜͷόοΫΤϯυΛ࡞͍ͬͯ·͢
   • ීஈ͸AWS x Scalaͷ࢓ࣄ͕ଟ͍Ͱ͢
   @kuro_m88 @kurochan
   

   View Slide

3. Federated QueryΛ࡞Γ͍ͨ
   

   View Slide

4. Federated Query
   • Redshiftʹ౥ࡌ͞Ε͍ͯΔ΍ͭ
   • Redshift͔ΒPostgreSQLʹΫΤϦΛ౤͛Δ͜ͱ͕Ͱ͖Δ
   • ͜Ε͕Ͱ͖Δͱɺ

   ϩάσʔλͱϑΝΫτσʔλͷ

   ݁߹͕Ͱ͖ͨΓͯ͠خ͍͠
   • MySQL͸·ͩbeta
   

   View Slide

5. ཁ͢Δʹ͜Ε͕΍Γ͍ͨ
   

   View Slide

6. SnowflakeͰ΋΍Γ͍ͨ
   • Snowflakeʹຬ଍͸͍ͯ͠Δ͕ɺRedshift͕ત·͍͠ͱࢥ͏ػೳΛͻͱ্ͭ͛
   Δͱ͢Ε͹Federated Query
   • ݱঢ়ͦͷΑ͏ͳػೳ͸ͳ͍͠ɺಉҰVPC಺ʹSnowflakeͱRDS͕ଘࡏ͠ͳ͍ͨ
   Ί௨৴͢Δͷ͸೉ͦ͠͏…
   • ત·͍͠ͷͰͳΜͱ͔ͦΕͬΆ͍΋ͷΛࣗ࡞͍ͨ͠
   • ࣮૷ͯ͠Έ·ͨ͠
   • CyberAgent / snowflake-aws-mysql-connector
   • https://github.com/CyberAgent/snowflake-aws-mysql-connector
   

   View Slide

7. Federated QueryΛ

   ࣮૷͢Δ
   

   View Slide

8. Snowflakeͱ֎෦αʔϏεͷ࿈ܞ
   • Snowflakeʹ͸External Functionͱ͍͏ػೳ͕͋ΓɺSQLͷؔ਺ͱͯ͠AWS
   Lambdaͷؔ਺Λݺͼग़͢͜ͱ͕Մೳ
   • Lambda FunctionͰRDSʹΞΫηεͯ͠ɺͦͷ݁ՌΛProxy͢Ε͹Snowflake
   ͔ΒಈతʹΫΤϦ͕౤͛ΒΕΔͷͰ͸ͳ͍͔…ʁ
   • ͱΓ͋͑ͣLambda Function + API Gateway
   

   View Slide

9. Lambda Function
   • ΄΄proxyͯ͠Δ͚ͩ
   • Lambda FunctionͷҾ਺͔ΒSQLͷจࣈྻΛऔΓग़͢
   • DBʹΫΤϦ͢Δ
   • ϨεϙϯεΛ੔ܗ͢Δ
   

   View Slide

10. LambdaͷϨεϙϯεαΠζ੍ݶ
    • Lambda FunctionͷϨεϙϯε͸6MB·Ͱ
    • ༗ݶͳͷͰϨεϙϯεΛѹॖͯ͠΋Αͦ͞͏
    • Ϩεϙϯε͕େ͖ͦ͏ͩͬͨΒѹॖ͢Δ͜ͱʹ

    (Ϩεϙϯεʹѹॖ͔ͨ͠Ͳ͏͔ͷϑϥά΋ೖΕ͓ͯ͘)
    

    View Slide

11. SnowflakeͷUDF
    • UDF = User Defined Function
    • UDF͸SQLͷଞʹJavascript͕ॻ͚Δ
    • UDF͸ʮߦʯΛฦؔ͢਺
    • External Function΋ʮߦʯΛฦؔ͢਺
    • ࠓճ͸SnowflakeͷςʔϒϧͱRDSͷςʔϒϧΛJOIN͍ͨ͠…
    

    View Slide

12. UDTF
    • User Defined Table Function
    • ʮςʔϒϧʯΛฦؔ͢਺
    • UDF: select my_function();
    • UDTF: select * from table(my_table_function());
    • ͋ͱ͸ؔ਺Λςʔϒϧؔ਺ʹม׵͢Δؔ਺͕͋Ε͹ɺ

    External FunctionΛUDTFͰϥοϓ͢Ε͹͍͍͸ͣ…ʂ
    

    View Slide

13. ؔ਺Λςʔϒϧؔ਺ʹม׵͢Δؔ਺: flattenؔ਺
    

    View Slide

14. ؔ਺Λςʔϒϧؔ਺ʹม׵͢Δؔ਺: flattenؔ਺
    

    View Slide

15. API IntegrationΛઃఆ͢Δ
    • API Gateway(Lambda)Λݺͼग़͢ํ๏Λఆٛ͢Δ
    

    View Slide

16. External FunctionΛఆٛ͢Δ
    • Snowflake͔ΒAPI GateawyΛݺͼग़͢
    

    View Slide

17. UDTFΛఆٛ͢Δ
    • UDTFͷதͰExternal FunctionΛݺΜͰɺ݁ՌΛflatten͢Δ
    

    View Slide

18. UDTFͷத਎ͷSQL
    • ΫΤϦ݁Ռ͕gzipѹॖ͞Ε͍ͯͨΒల։͠ɺ

    ૉͷJSONͰ͋Ε͹ͦͷ··ύʔε͢Δ
    

    View Slide

19. ࢖͍ํͷΠϝʔδ
    

    View Slide

20. ࢖͍ํͷΠϝʔδ
    

    View Slide

21. View Slide