dely tech blog

クラシル・TRILLを運営するdely株式会社の開発ブログです

SnowflakeのSQL APIをRubyで試してみた

こんにちは。クラシル開発部でバックエンドエンジニアの高松です(@takarotoooooo

この記事は dely Advent Calendar 2021 12日目の記事です。

昨日はknchstさんの「クラシルiOSのパッケージマネジメントについて」というお話でしたiOSの開発にも興味がある方はぜひ見てみてください!

今回は先日データエンジニアのharryさんが「クラシルでのSnowflakeデータパイプラインのお話&活用Tips」で紹介していたSnowflakeをアプリケーション側から利用する方法を試してみたので紹介します

はじめに

アプリケーションからログデータを利用できると何がうれしいのか?

クラシルでは現在Snowflakeを用いてログデータの可視化・分析を行っています
分析結果を元に意思決定を行い、改修することで収集したデータを開発に還元しています

f:id:takarotoooooo:20211209103526j:plain

しかし可視化・分析だけでなく、アプリケーションからログデータを利用できるようにすることで、コンテンツ配信やレコメンドの最適化など実現可能な施作を増やすことができるため、結果としてサービス、プロダクトで解決できる課題の幅を広げることができます

f:id:takarotoooooo:20211209103758j:plain

注意点

今回はキーペア認証方式で利用する方法をご紹介しています
また、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を作成します
なお、ここではGemjwtを利用して生成を行なっています

フィールド
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 `< JWT >`
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