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

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

Kurochan
April 04, 2021

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

Kurochan

April 04, 2021
Tweet

More Decks by Kurochan

Other Decks in Technology

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