SnowflakeのSQL REST APIを呼び出してみた

分析屋

2023年12月21日 23:05

分析屋の下滝です。

SnowflakeのSQL REST APIを使ってSELECT 文を呼び出してみます。

公式ドキュメントはここにありますので、詳しい内容はこちらを参照してください。

この記事では、ひとまず動くまでをさくっと短く紹介することを狙いとしています。

SQL REST APIを呼び出す

呼び出すまでの準備

認証方法として、以下の2つがあるのですが、使い分けを理解しきれていないので「キーペア認証の使用」で試してみます。
・OAuth の使用
・キーペア認証の使用

まず最初のステップとして秘密キーを生成します。詳しい説明はこちらを参照してください。

秘密キーの生成には、opensslを使います（他のものでもできるかもしれません。opensslのインストールはこちらの記事などを参照）。

以下、Snowflakeの公式のドキュメントにしたがって、作っていきます。

では、最初のコマンドはこちら。

$ openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

パスフレーズの入力が求められます。求められないやり方は以下です。

$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

実行後、rsa_key.p8というファイルが生成されます。

続いて、公開キーを生成します。

$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

rsa_key.pubというファイルが生成されます。

続いては、snowflake上でコマンドを実行していきます。ALTER USER コマンドを実行して、公開キーをSnowflakeのユーザーに割り当てます。ここでは、shimotaki というユーザーに割り当てました。

ALTER USER shimotaki SET RSA_PUBLIC_KEY='MIIBIjANBg...'

続いてDESCRIBE USER コマンドを実行して、ユーザーの公開キーを検証していきます。検証がうまくいくと、このユーザーに公開キーが適切に設定された、ということになるそうです。

DESC USER shimotaki;
SELECT TRIM((SELECT "value" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  WHERE "property" = 'RSA_PUBLIC_KEY_FP'), 'SHA256:');

出力はこうなります。公開キーのフィンガープリントを抜き出しています。
sgr5Iv62MJLgUn8Qwvloy3QDj61EE4oTiNG2KbRlOyc=

この結果をopensslの以下のコマンドとの結果と比べます。

$ openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64

以下が出力されました。
sgr5Iv62MJLgUn8Qwvloy3QDj61EE4oTiNG2KbRlOyc=

同じ結果となりました。

続いてSnowflakeに接続できることを確認します。ここではsnowsqlで試しています（確認するだけなので必須の設定ではないです）。

snowsql -a <account_identifier> -u <user> --private-key-path <path>/rsa_key.p8

<account_identifier>と<user>に適切なものを入れます。<user>は、公開キーを割り当てたユーザーになります。

snowsql -a gd99999.ap-northeast-1.aws -u shimotaki --private-key-path rsa_key.p8

接続がうまくいくと、

shimotaki#(no warehouse)@(no database).(no schema)>

のようなモードになります。確認終了です。

続いて、JWT （JSON Webトークン）とフィンガープリントを生成します（このステップがどういう役割なのかは不明です）。

python（とjavaとnodeの例があります）を使って生成ができるようです。Snowflakeの公式のドキュメントに、生成用のプログラムが紹介されています。

このsql-api-generate-jwt.pyを使って生成します。

その前に必要なライブラリをインストールします。
pip install pyjwt

インストールできたら実行します。--accountには<account_identifier>を、user=には<user>を設定してください。

python sql-api-generate-jwt.py --account=gd99999.ap-northeast-1.aws --user=shimotaki --private_key_file_path=rsa_key.p8

JWTが主力されますのでコピーします。

JWT:
eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJHRDcyNjY0LlNISU1PVEFL
…

ここまででリクエストを投げる準備が整いました。

リクエストを投げる

リクエストを投げます。curlで試しますが何でもいけます。投げるテンプレは以下となります。

curl -i -X POST 
-H "Content-Type: application/json" 
-H "Authorization: Bearer <jwt>" 
-H "Accept: application/json" 
-H "User-Agent: myApplicationName/1.0" 
-H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" 
-d "@request-body.json" 
"https://<account_identifier>.snowflakecomputing.com/api/v2/statements"

<jwt> は、認証用に生成した JWT です。
myApplicationName は、アプリケーションの識別子の例です。
<account_identifier> は、使用するアカウント識別子です。

リクエストの本文はrequest-body.jsonに書いています。

{
"statement": "select 1000",
"timeout": 60,
"database": "TEST",
"schema": "PUBLIC",
"warehouse": "XS"
}

テーブルにデータを入れるのが面倒なので
select 1000
として、固定値で1000を返すようにしています。

テンプレに入れるとこうなります。

curl -i -X POST 
-H "Content-Type: application/json" 
-H "Authorization: Bearer eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJHRDcyNjY0LlNISU1PVEFL…" 
-H "Accept: application/json" 
-H "User-Agent: myApplicationName/1.0" 
-H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" 
-d "@request-body.json" 
"https://gd99999.ap-northeast-1.aws.snowflakecomputing.com/api/v2/statements"

実行結果はこうなります。

HTTP/1.1 200 OK
Content-Type: application/json
Date: Thu, 21 Dec 2023 13:45:48 GMT
Expect-CT: enforce, max-age=3600
Link: </api/v2/statements/01b12339-0001-3053-0001-03ee0007422e?requestId=51e51541-d98c-41ca-9039-17467aa170f3&partition=0>; rel="first",</api/v2/statements/01b12339-0001-3053-0001-03ee0007422e?requestId=14f0419d-c22f-44c7-99f0-74b11c052aed&partition=0>; rel="last"
Strict-Transport-Security: max-age=31536000
Vary: Accept-Encoding, User-Agent
X-Content-Type-Options: nosniff
X-Country: Japan
X-Frame-Options: deny
X-XSS-Protection: 1; mode=block
Content-Length: 845
Connection: keep-alive

{
"resultSetMetaData" : {
"numRows" : 1,
"format" : "jsonv2",
"partitionInfo" : [ {
"rowCount" : 1,
"uncompressedSize" : 8
} ],
"rowType" : [ {
"name" : "1000",
"database" : "",
"schema" : "",
"table" : "",
"precision" : 4,
"byteLength" : null,
"type" : "fixed",
"scale" : 0,
"nullable" : false,
"collation" : null,
"length" : null
} ]
},
"data" : [ ["1000"] ],
"code" : "090001",
"statementStatusUrl" : "/api/v2/statements/01b12339-0001-3053-0001-03ee0007422e?requestId=9924c21e-768b-4bef-8f74-5af37c9583cd",
"requestId" : "9924c21e-768b-4bef-8f74-5af37c9583cd",
"sqlState" : "00000",
"statementHandle" : "01b12339-0001-3053-0001-03ee0007422e",
"message" : "Statement executed successfully.",
"createdOn" : 1703166348492
}

dataに1000が入って返ってきていることが分かります。

今回は以上です。

株式会社分析屋について

ホームページはこちら。

noteでの会社紹介記事はこちら。

専用の採用ページはこちら。

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。