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

ࠇ࡚ ༏ଠ • גࣜձࣾαΠόʔΤʔδΣϯτ
 Dynalyst ։ൃ੹೚ऀ • ޿ࠂ഑৴γεςϜͷόοΫΤϯυΛ࡞͍ͬͯ·͢ • ීஈ͸AWS x Scalaͷ࢓ࣄ͕ଟ͍Ͱ͢ @kuro_m88 @kurochan

Federated QueryΛ࡞Γ͍ͨ

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

ཁ͢Δʹ͜Ε͕΍Γ͍ͨ

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

Federated QueryΛ
 ࣮૷͢Δ

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

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

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

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

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

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

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

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

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

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

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

࢖͍ํͷΠϝʔδ

࢖͍ํͷΠϝʔδ

No content