#SnowVillage LIVE 004 https://www.youtube.com/watch?v=zY7z2IEtqCc
SnowflakeʹMySQLͱJOIN͢ΔػೳΛ ࣮͢ΔגࣜձࣾαΠόʔΤʔδΣϯτ AIࣄۀຊ෦ ࠇ࡚ ༏ଠ (@kuro_m88)
View Slide
ࠇ࡚ ༏ଠ• גࣜձࣾαΠόʔΤʔδΣϯτ 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ͷؔͱͯ͠AWSLambdaͷؔΛݺͼग़͢͜ͱ͕Մೳ• 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• UDFSQLͷଞʹ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ؔ
API IntegrationΛઃఆ͢Δ• API Gateway(Lambda)Λݺͼग़͢ํ๏Λఆٛ͢Δ
External FunctionΛఆٛ͢Δ• Snowflake͔ΒAPI GateawyΛݺͼग़͢
UDTFΛఆٛ͢Δ• UDTFͷதͰExternal FunctionΛݺΜͰɺ݁ՌΛflatten͢Δ
UDTFͷதͷSQL• ΫΤϦ݁Ռ͕gzipѹॖ͞Ε͍ͯͨΒల։͠ɺ ૉͷJSONͰ͋Εͦͷ··ύʔε͢Δ
͍ํͷΠϝʔδ