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

Transcript

1. SnowflakeʹMySQLͱJOIN͢ΔػೳΛ
 ࣮૷͢Δ גࣜձࣾαΠόʔΤʔδΣϯτ AIࣄۀຊ෦
 ࠇ࡚ ༏ଠ (@kuro_m88)

2. ࠇ࡚ ༏ଠ • גࣜձࣾαΠόʔΤʔδΣϯτ
 Dynalyst ։ൃ੹೚ऀ • ޿ࠂ഑৴γεςϜͷόοΫΤϯυΛ࡞͍ͬͯ·͢ • ීஈ͸AWS

   x Scalaͷ࢓ࣄ͕ଟ͍Ͱ͢ @kuro_m88 @kurochan

3. Federated QueryΛ࡞Γ͍ͨ

4. Federated Query • Redshiftʹ౥ࡌ͞Ε͍ͯΔ΍ͭ • Redshift͔ΒPostgreSQLʹΫΤϦΛ౤͛Δ͜ͱ͕Ͱ͖Δ • ͜Ε͕Ͱ͖Δͱɺ
 ϩάσʔλͱϑΝΫτσʔλͷ
 ݁߹͕Ͱ͖ͨΓͯ͠خ͍͠

   • MySQL͸·ͩbeta

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

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

   • ࣮૷ͯ͠Έ·ͨ͠ • CyberAgent / snowflake-aws-mysql-connector • https://github.com/CyberAgent/snowflake-aws-mysql-connector

7. Federated QueryΛ
 ࣮૷͢Δ

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

   ͱΓ͋͑ͣLambda Function + API Gateway

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

   ϨεϙϯεΛ੔ܗ͢Δ

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

11. SnowflakeͷUDF • UDF = User Defined Function • UDF͸SQLͷଞʹJavascript͕ॻ͚Δ •

    UDF͸ʮߦʯΛฦؔ͢਺ • External Function΋ʮߦʯΛฦؔ͢਺ • ࠓճ͸SnowflakeͷςʔϒϧͱRDSͷςʔϒϧΛJOIN͍ͨ͠…

12. UDTF • User Defined Table Function • ʮςʔϒϧʯΛฦؔ͢਺ • UDF:

    select my_function(); • UDTF: select * from table(my_table_function()); • ͋ͱ͸ؔ਺Λςʔϒϧؔ਺ʹม׵͢Δؔ਺͕͋Ε͹ɺ
 External FunctionΛUDTFͰϥοϓ͢Ε͹͍͍͸ͣ…ʂ

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

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

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

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

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

18. UDTFͷத਎ͷSQL • ΫΤϦ݁Ռ͕gzipѹॖ͞Ε͍ͯͨΒల։͠ɺ
 ૉͷJSONͰ͋Ε͹ͦͷ··ύʔε͢Δ

19. ࢖͍ํͷΠϝʔδ

20. ࢖͍ํͷΠϝʔδ

21. None