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

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

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. ࢖͍ํͷΠϝʔδ