ããã«ã¡ã¯ãã¯ã©ã·ã«éçºéšã§ããã¯ãšã³ããšã³ãžãã¢ã®é«æŸã§ãïŒ @takarotoooooo ïŒ ãã®èšäºã¯ dely Advent Calendar 2021 12æ¥ç®ã®èšäºã§ãã æšæ¥ã¯knchstããã®ã ã¯ã©ã·ã«iOSã®ããã±ãŒãžãããžã¡ã³ãã«ã€ã㊠ããšããã話ã§ããiOSã®éçºã«ãèå³ãããæ¹ã¯ãã²èŠãŠã¿ãŠãã ããïŒ ä»åã¯å
æ¥ããŒã¿ãšã³ãžãã¢ã®harryãããã ã¯ã©ã·ã«ã§ã®SnowflakeããŒã¿ãã€ãã©ã€ã³ã®ãè©±ïŒæŽ»çšTips ãã§ç޹ä»ããŠããSnowflakeãã¢ããªã±ãŒã·ã§ã³åŽããå©çšããæ¹æ³ã詊ããŠã¿ãã®ã§ç޹ä»ããŸã ã¯ããã« ã¢ããªã±ãŒã·ã§ã³ãããã°ããŒã¿ãå©çšã§ãããšäœãããããã®ãïŒ ã¯ã©ã·ã«ã§ã¯çŸåšSnowflakeãçšããŠãã°ããŒã¿ã®å¯èŠåã»åæãè¡ã£ãŠããŸã åæçµæãå
ã«æææ±ºå®ãè¡ããæ¹ä¿®ããããšã§åéããããŒã¿ãéçºã«éå
ããŠããŸã ãããå¯èŠåã»åæã ãã§ãªããã¢ããªã±ãŒã·ã§ã³ãããã°ããŒã¿ãå©çšã§ããããã«ããããšã§ãã³ã³ãã³ãé
ä¿¡ãã¬ã³ã¡ã³ãã®æé©åãªã©å®çŸå¯èœãªæœäœãå¢ããããšãã§ãããããçµæãšããŠãµãŒãã¹ããããã¯ãã§è§£æ±ºã§ãã課é¡ã®å¹
ãåºããããšãã§ããŸã 泚æç¹ ä»åã¯ããŒãã¢èªèšŒæ¹åŒã§å©çšããæ¹æ³ãã玹ä»ããŠããŸã ãŸããSQL APIã¯ãã¬ãã¥ãŒæ©èœãªã®ã§æ£åŒãªãªãŒã¹ãããéã«ã¯ä»æ§ãå°ãå€ãã£ãŠããå¯èœæ§ãããã®ã§ã泚æãã ãã docs.snowflake.com æºå å
¬ééµãšç§å¯éµã®ãã¢ãçæ å
¬ééµãSnowflakeãŠãŒã¶ãŒã«å²ãåœãŠã å
¬ééµãšç§å¯éµã®ãã¢ãçæ ç§å¯éµãçæããŸã openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 äžã§çæããç§å¯éµãå
ã«å
¬ééµãçæããŸã openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub å
¬ééµãSnowflakeãŠãŒã¶ãŒã«å²ãåœãŠã äžã§çºè¡ããå
¬ééµã®å
å®¹ãæ¥ç¶ã«å©çšãããŠãŒã¶ãŒã«èšå®ããŸãã ïŒãŠãŒã¶ãŒã®å€æŽã¯SECURITYADMINããŒã«ã®ãŠãŒã¶ãŒä»¥äžã§ããå¿
èŠãããã®ã§ãæš©éãè¶³ããªãå Žåã¯ç¶ãã¹ã人ã«ãé¡ãããŠãã ããïŒ alter user [USER_NAME] set rsa_public_key= ' [PUBLIC_KEY_VALUE] ' ; æ¥ç¶ããŠã¿ã å¿
èŠãªãã® ç§å¯éµ Snowflakeã®ã¢ã«ãŠã³ãèå¥å Snowflakeã®ã¢ã«ãŠã³ã Snowflakeã®ãŠãŒã¶ãŒå å
¬ééµã®ãã£ã³ã¬ãŒããªã³ããäœã private_key = OpenSSL :: PKey :: RSA .new(pemlines) public_key = private_key.public_key public_key_fp = %( SHA256: #{ Base64 .encode64( Digest :: SHA256 .digest(public_key.to_der)).strip }) JWTã®äœæ ãã€ããŒãã«ä»¥äžã®ãã£ãŒã«ããæã€JWTãäœæããŸã ãªããããã§ã¯Gem jwt ãå©çšããŠçæãè¡ãªã£ãŠããŸã ãã£ãŒã«ã å€ iss ` `.` `.` ` sub ` `.` ` iat JWT ãçºè¡ãããæéïŒUTC ã®ãšããã¯éå§ããã®ç§æ°ïŒ exp JWT ã®æå¹æéãåããæéïŒUTC ã®ãšããã¯éå§ããã®ç§æ°ïŒ jwt_created_at = Time .now.getutc lifetime = 60 * 60 qualified_username = %(#{ account.upcase } . #{ user_name.upcase }) payload = { ' iss ' : %(#{ qualified_username } . #{ public_key_fp }) , ' sub ' : qualified_username, ' iat ' : jwt_created_at.to_i, ' exp ' : (jwt_created_at + lifetime).to_i } algorithm = ' RS256 ' token = JWT .encode(payload, private_key, algorithm) APIãªã¯ãšã¹ã çæããJWTãå©çšããŠãäžèšã®URLã«POSTãªã¯ãšã¹ããéä¿¡ããŸã https://<ã¢ã«ãŠã³ãèå¥å>.snowflakecomputing.com/api/statements ãªã¯ãšã¹ãããã é
ç®å å€ Authorization Bearer ` ` Accept application/json Content-Type application/json X-Snowflake-Authorization-Token-Type KEYPAIR_JWT ãªã¯ãšã¹ããã㣠é
ç®å å€ statement å®è¡ããSQL timeout ã¿ã€ã ã¢ãŠãç§æ° database ããŒã¿ããŒã¹å schema ã¹ããŒãå warehouse ãŠã§ã¢ããŠã¹å role ããŒã« ãªã¯ãšã¹ããã©ã¡ãŒã¿ é
ç®å å€ requestId ãªã¯ãšã¹ãæ¯ã®uuid async falseã§åæçã«ããŒã¿ãååŸ pageSize ååŸããããŒã¿ãµã€ãº http = Net :: HTTP .new( %(#{ <ã¢ã«ãŠã³ãèå¥å> } .snowflakecomputing.com ) , 443 ) http.use_ssl = true query_string = { requestId : SecureRandom .uuid, async : false , pageSize : 15 , nullable : true }.map { |k, v| %(#{ k } = #{ v }) }.join( ' & ' ) path = %(#{ uri.path } ? #{ query_string }) header = { ' Authorization ' : %( Bearer #{ token }) , ' Accept ' : ' application/json ' , ' Content-Type ' : ' application/json ' , ' User-Agent ' : ' applicationName/applicationVersion ' , ' X-Snowflake-Authorization-Token-Type ' : ' KEYPAIR_JWT ' } params = { statement :'SELECT * FROM CUSTOMER;' , timeout : 60 , database : ' SNOWFLAKE_SAMPLE_DATA ' , schema : ' TPCH_SF1 ' , warehouse : <ãŠã§ã¢ããŠã¹å>, role : <ããŒã«>, } response = http.post(path, params.to_json, header) pp JSON .parse(response) ãããªæãã§çµæãè¿ã£ãŠããŸã { " resultSetMetaData " => { " page " => 0 , " pageSize " => 15 , " numPages " => 10000 , " numRows " => 150000 , " format " => " json " , " rowType " => [{ " name " => " C_CUSTKEY " , " database " => " SNOWFLAKE_SAMPLE_DATA " , " schema " => " TPCH_SF1 " , " table " => " CUSTOMER " , " scale " => 0 , " precision " => 38 , ~~~~~ " data " => [[ " 0 " , " 30001 " , " Customer#000030001 " , " Ui1b,3Q71CiLTJn4MbVp,,YCZARIaNTelfst " , " 4 " , " 14-526-204-4500 " , " 8848.47 " , " MACHINERY " , " frays wake blithely enticingly ironic asymptote " ], ~~~~~ " code " => " 090001 " , " statementStatusUrl " => " /api/statements/01a0cf10-0000-30b4-0000-40d5007afd72?requestId=30139506-efcb-40c0-af02-1cfb97c64b30&pageSize=15 " , " requestId " => " 30139506-efcb-40c0-af02-1cfb97c64b30 " , " sqlState " => " 00000 " , " statementHandle " => " 01a0d3bf-0000-30ee-0000-40d5007cf11e " , " message " => " Statement executed successfully. " , " createdOn " => 1639031029314 } ãããã« ä»åã¯Snowflakeã®ããŒã¿ãã¢ããªã±ãŒã·ã§ã³ã§ååŸããæ¹æ³ã玹ä»ããŸãã å®çšã«åããŠãããããšè©ŠããŠãããããšæããŸã SQL APIã¯ãŸã ãã¬ãã¥ãŒæ©èœãªã®ã§ãå
¬åŒã«ãªãªãŒã¹ãããŠå®çšã§ããããšã楜ãã¿ã«åŸ
ã£ãŠããŸã ãã詳ããæ
å ±ã¯äžèšã«æ²èŒãããŠããŸãã®ã§ã詊ããŠã¿ãŠãããããšè¯ããšæããŸã docs.snowflake.com delyã§ã¯ãšã³ãžãã¢ããã¶ã€ããŒãPdMãç©æ¥µæ¡çšããŠããŸãããå¿åãåŸ
ã¡ããŠãããŸãïŒ ãã²äžç·ã«ã¯ã©ã·ã«ãããè¯ããµãŒãã¹ã«ããŠãããŸãããïŒ dely.jp