Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

1f745ff900e1be51aedae18cae76593c?s=128

Kurochan

April 04, 2021
Tweet

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