RESTでクエリ実行ができる「Snowflake SQL API」を試してみた

「カスタマーストーリー」を支えるツール特集

#Snowflake

大高大輔

2021.09.09

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは！DA(データアナリティクス)事業本部サービスソリューション部の大高です。

Snowflakeには、Snowflake SQL APIというREST APIが用意されており、これを利用することでRESTでクエリやDDL、DMLの実行をすることができます。（2021年9月現在はプレビュー機能です）

今回はこの「Snowflake SQL API」を下記のドキュメントをベースに実際に試していきたいと思います。

認証情報の準備

Snowflake SQL APIでは認証情報をリクエストヘッダに追加する必要があります。このため、まずはリクエストヘッダに追加する情報を取得していきます。

認証方法の選択

認証方法としては「OAuth」または「キーペア認証」を利用することができます。

認証方法によるリクエストヘッダへの設定はそれぞれ以下のようになりますが、今回は「キーペア認証」を利用したいと思います。

OAuth認証

HTTPヘッダ	値	備考
Authorization	Bearer ＜OAuthトークン＞	必須
X-Snowflake-Authorization-Token-Type	OAUTH	省略可(省略時はOAUTH指定と同じ扱い)

キーペア認証

HTTPヘッダ	値	備考
Authorization	Bearer ＜JWT＞	必須
X-Snowflake-Authorization-Token-Type	KEYPAIR_JWT	省略不可

Snowflake上での事前準備

「キーペア認証」については、以前こちらのエントリで試してみましたので、事前にこちらの手順で準備をしておきます。

上記手順の中で「ユーザーの公開鍵のフィンガープリント」が取得できるようになっているはずなので、これを取得します。

foo_bar#(no warehouse)@(no database).(no schema)>DESC USER foo_bar;
╒═══════════════════════════════╤═════════════════════════════════════════════════════╤═════════╤══════════════════════════════════════════════╕
│ property                      │ value                                               │ default │ description                                  │
╞═══════════════════════════════╪═════════════════════════════════════════════════════╪═════════╪══════════════════════════════════════════════╡
│ NAME                          │ FOO_BAR                                             │ null    │ Name                                         │
├───────────────────────────────┼─────────────────────────────────────────────────────┼─────────┼──────────────────────────────────────────────┤
...
├───────────────────────────────┼─────────────────────────────────────────────────────┼─────────┼──────────────────────────────────────────────┤
│ RSA_PUBLIC_KEY_FP             │ SHA256:TiC4NxC1a3Jzr7A7VTg2/I5io4UC84XlZ/01py4RytY= │ null    │ Fingerprint of user's RSA public key.        │
├───────────────────────────────┼─────────────────────────────────────────────────────┼─────────┼──────────────────────────────────────────────┤
│ RSA_PUBLIC_KEY_2_FP           │ null                                                │ null    │ Fingerprint of user's second RSA public key. │
├───────────────────────────────┼─────────────────────────────────────────────────────┼─────────┼──────────────────────────────────────────────┤
...
╘═══════════════════════════════╧═════════════════════════════════════════════════════╧═════════╧══════════════════════════════════════════════╛
25 Row(s) produced. Time Elapsed: 0.114s
foo_bar#(no warehouse)@(no database).(no schema)>!quit
Goodbye!

この例では SHA256:TiC4NxC1a3Jzr7A7VTg2/I5io4UC84XlZ/01py4RytY= がフィンガープリントになりますので、この値を控えておきます。

リクエストヘッダに追加する認証情報の生成

フィンガープリントが取得できたので、さらにこれを利用した「JSON Web トークン（JWT）」を生成していきます。

今回はドキュメントに記載の通りPythonでやりたいと思うので、パッケージにはpyjwtとcryptographyをインストールします。

私はpipenvを利用しているので、以下のようにセットアップしました。なお、アカウント情報については.envファイルに環境変数として出しておきますが、pipenv以外だとpython-dotenvを利用すると良いかなと思います。

$ pipenv --python 3
$ pipenv install pyjwt cryptography

Pythonのコードは基本的にはドキュメントと同じですが、アカウント情報を環境変数から取得したり、キーにパスフレーズが設定されている場合への対応など、少し修正しています。

import os
from datetime import datetime, timedelta, timezone

# This example relies on the PyJWT module (https://pypi.org/project/PyJWT/).
import jwt
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives import serialization

ACCOUNT_IDENTIFIER = os.environ['ACCOUNT_IDENTIFIER']
USER_NAME = os.environ['USER_NAME']
PUBLIC_KEY_FP = os.environ['PUBLIC_KEY_FP']
PRIVATE_KEY_FILE_PATH = os.environ['PRIVATE_KEY_FILE_PATH']
PASSPHRASE = os.environ['PASSPHRASE']

with open(PRIVATE_KEY_FILE_PATH) as f:
    private_key = f.read()

if(PASSPHRASE):
    private_key = serialization.load_pem_private_key(
        private_key.encode(), password=PASSPHRASE.encode(), backend=default_backend()
    )

# Construct the fully qualified name of the user in uppercase.
# - Replace <account_identifier> with your account identifier.
#   (See https://docs.snowflake.com/en/user-guide/admin-account-identifier.html .)
# - Replace <user_name> with your Snowflake user name.
account = ACCOUNT_IDENTIFIER
# Get the account identifier without the region, cloud provider, or subdomain.
if '.global' not in account:
    idx = account.find('.')
    if idx > 0:
        account = account[0:idx]
    else:
        # Handle the replication case.
        idx = account.find('-')
        if idx > 0:
            account = account[0:idx]
# Use uppercase for the account identifier and user name.
account = account.upper()
user = USER_NAME.upper()
qualified_username = account + "." + user

# Get the current time in order to specify the time when the JWT was issued and the expiration time of the JWT.
now = datetime.now(timezone.utc)
# Specify the length of time during which the JWT will be valid. You can specify at most 1 hour.
lifetime = timedelta(minutes=59)

# Create the payload for the token.
payload = {
    # Set the issuer to the fully qualified username concatenated with the public key fingerprint (calculated in the
    # previous step).
    "iss": qualified_username + '.' + PUBLIC_KEY_FP,

    # Set the subject to the fully qualified username.
    "sub": qualified_username,

    # Set the issue time to now.
    "iat": now,

    # Set the expiration time, based on the lifetime specified for this object.
    "exp": now + lifetime
}

# Generate the JWT. private_key is the private key that you read from the private key file in the previous step when you
# generated the public key fingerprint.
encoding_algorithm = "RS256"
token = jwt.encode(payload, key=private_key, algorithm=encoding_algorithm)
# If you are using a version of PyJWT prior to 2.0, jwt.encode returns a byte string, rather than a string.
# If the token is a byte string, convert it to a string.
if isinstance(token, bytes):
    token = token.decode('utf-8')
decoded_token = jwt.decode(token, key=private_key.public_key(), algorithms=[encoding_algorithm])
print("Generated a JWT with the following payload:\n{}".format(decoded_token))

print("JSON Web Token:")
print(token)

このコードを実行すると、以下のように出力されます。

$ python snowflake-jwt-generator.py 
Generated a JWT with the following payload:
{'iss': 'SAMPLEACCOUNT.FOO_BAR.SHA256:TiC4NxC1a3Jzr7A7VTg2/I5io4UC84XlZ/01py4RytY=', 'sub': 'SAMPLEACCOUNT.FOO_BAR', 'iat': 1631170568, 'exp': 1631174108}
JSON Web Token:
eyJ0e...(snip)...GbyLA

ここで出力された「JSON Web Token」の値を、リクエストヘッダに設定することになります。

Snowflake SQL APIを利用してみる

これで準備ができたので、実際にリクエストを送信してみましょう。

まずは、簡単なクエリとしてSELECT CURRENT_REGION()を実行してみます。REST APIの呼び出しにはcurlコマンドを利用します。

最初に確認したとおり、AuthorizationヘッダにはJWTの値を、X-Snowflake-Authorization-Token-TypeにはKEYPAIR_JWTを指定します。クエリのステートメントは今回は直接文字列で指定します。

$ curl -i -X POST \
    -H "Authorization: Bearer eyJ0e...(snip)...GbyLA" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    -d "{\"statement\": \"SELECT CURRENT_REGION()\"}" \
    https://SAMPLEACCOUNT.ap-northeast-1.aws.snowflakecomputing.com/api/statements

結果は以下が返却されました。AWS_AP_NORTHEAST_1が取得できていますね。

HTTP/1.1 200 OK
Content-Type: application/json
Date: Thu, 09 Sep 2021 07:03:42 GMT
Expect-CT: enforce, max-age=3600
Link: </api/statements/019ed407-0000-2943-0000-00b40147e0e6?requestId=414a0619-d40a-46cf-aa46-bc5b1cc5dd50&page=0&pageSize=-1>; rel="first",</api/statements/019ed407-0000-2943-0000-00b40147e0e6?requestId=0950911e-3c87-4bef-be06-61fed5cdf91b&page=0&pageSize=-1>; 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: 835
Connection: keep-alive

{
  "resultSetMetaData" : {
    "page" : 0,
    "numPages" : 1,
    "numRows" : 1,
    "format" : "json",
    "rowType" : [ {
      "name" : "CURRENT_REGION()",
      "database" : "",
      "schema" : "",
      "table" : "",
      "byteLength" : 16777216,
      "scale" : null,
      "precision" : null,
      "type" : "text",
      "nullable" : true,
      "collation" : null,
      "length" : 16777216
    } ]
  },
  "data" : [ [ "0", "AWS_AP_NORTHEAST_1" ] ],
  "code" : "090001",
  "statementStatusUrl" : "/api/statements/019ed407-0000-2943-0000-00b40147e0e6?requestId=89bdc7ab-d66c-4bc8-aab2-cd501ccbdddc",
  "requestId" : "89bdc7ab-d66c-4bc8-aab2-cd501ccbdddc",
  "sqlState" : "00000",
  "statementHandle" : "019ed407-0000-2943-0000-00b40147e0e6",
  "message" : "Statement executed successfully.",
  "createdOn" : 1631171022606
}

今度は、リクエスト内容であるクエリのステートメントはrequest-body.jsonというファイルに分けて、別のリージョン一覧を取得するクエリを実行してみます。

{
    "statement": "SHOW REGIONS"
}

$ curl -i -X POST \
    -H "Authorization: Bearer eyJ0e...(snip)...GbyLA" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "User-Agent: myApplicationName/1.0" \
    -H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
    -d "@request-body.json" \
    https://SAMPLEACCOUNT.ap-northeast-1.aws.snowflakecomputing.com/api/statements

HTTP/1.1 200 OK
Content-Type: application/json
Date: Thu, 09 Sep 2021 07:15:16 GMT
Expect-CT: enforce, max-age=3600
Link: </api/statements/019ed413-0000-2949-0000-00b40147f0e2?requestId=9e36c9c6-b48f-4087-a0e5-40c08b50c1bd&page=0&pageSize=-1>; rel="first",</api/statements/019ed413-0000-2949-0000-00b40147f0e2?requestId=411426f0-cd17-4403-95bf-b9bd4389136a&page=0&pageSize=-1>; 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: 3481
Connection: keep-alive

{
  "resultSetMetaData" : {
    "page" : 0,
    "numPages" : 1,
    "numRows" : 24,
    "format" : "json",
    "rowType" : [ {
      "name" : "snowflake_region",
      "database" : "",
      "schema" : "",
      "table" : "",
      "byteLength" : 16777216,
      "type" : "text",
      "scale" : null,
      "precision" : null,
      "length" : 16777216,
      "collation" : null,
      "nullable" : true
    }, {
      "name" : "cloud",
      "database" : "",
      "schema" : "",
      "table" : "",
      "byteLength" : 16777216,
      "type" : "text",
      "scale" : null,
      "precision" : null,
      "length" : 16777216,
      "collation" : null,
      "nullable" : true
    }, {
      "name" : "region",
      "database" : "",
      "schema" : "",
      "table" : "",
      "byteLength" : 16777216,
      "type" : "text",
      "scale" : null,
      "precision" : null,
      "length" : 16777216,
      "collation" : null,
      "nullable" : true
    }, {
      "name" : "display_name",
      "database" : "",
      "schema" : "",
      "table" : "",
      "byteLength" : 16777216,
      "type" : "text",
      "scale" : null,
      "precision" : null,
      "length" : 16777216,
      "collation" : null,
      "nullable" : true
    } ]
  },
  "data" : [ [ "0", "AWS_US_WEST_2", "aws", "us-west-2", "US West (Oregon)" ], [ "1", "AWS_US_EAST_1", "aws", "us-east-1", "US East (N. Virginia)" ], [ "2", "AWS_AP_SOUTHEAST_2", "aws", "ap-southeast-2", "Asia Pacific (Sydney)" ], [ "3", "AWS_EU_WEST_1", "aws", "eu-west-1", "EU (Ireland)" ], [ "4", "AWS_AP_SOUTHEAST_1", "aws", "ap-southeast-1", "Asia Pacific (Singapore)" ], [ "5", "AWS_CA_CENTRAL_1", "aws", "ca-central-1", "Canada (Central)" ], [ "6", "AWS_EU_CENTRAL_1", "aws", "eu-central-1", "EU (Frankfurt)" ], [ "7", "AWS_US_EAST_2", "aws", "us-east-2", "US East (Ohio)" ], [ "8", "AWS_AP_NORTHEAST_1", "aws", "ap-northeast-1", "Asia Pacific (Tokyo)" ], [ "9", "AWS_AP_SOUTH_1", "aws", "ap-south-1", "Asia Pacific (Mumbai)" ], [ "10", "AWS_EU_WEST_2", "aws", "eu-west-2", "EU (London)" ], [ "11", "AWS_AP_NORTHEAST_2", "aws", "ap-northeast-2", "Asia Pacific (Seoul)" ], [ "12", "AZURE_EASTUS2", "azure", "eastus2", "East US 2 (Virginia)" ], [ "13", "AZURE_WESTEUROPE", "azure", "westeurope", "West Europe (Netherlands)" ], [ "14", "AZURE_AUSTRALIAEAST", "azure", "australiaeast", "Australia East (New South Wales)" ], [ "15", "AZURE_CANADACENTRAL", "azure", "canadacentral", "Canada Central (Toronto)" ], [ "16", "AZURE_SOUTHEASTASIA", "azure", "southeastasia", "Southeast Asia (Singapore)" ], [ "17", "AZURE_WESTUS2", "azure", "westus2", "West US 2 (Washington)" ], [ "18", "AZURE_SWITZERLANDN", "azure", "switzerlandn", "Switzerland North (Zurich)" ], [ "19", "AZURE_CENTRALUS", "azure", "centralus", "Central US (Iowa)" ], [ "20", "AZURE_NORTHEUROPE", "azure", "northeurope", "North Europe (Ireland)" ], [ "21", "GCP_US_CENTRAL1", "gcp", "us-central1", "US Central 1 (Iowa)" ], [ "22", "GCP_EUROPE_WEST2", "gcp", "europe-west2", "Europe West 2 (London)" ], [ "23", "GCP_EUROPE_WEST4", "gcp", "europe-west4", "Europe West 4 (Netherlands)" ] ],
  "code" : "090001",
  "statementStatusUrl" : "/api/statements/019ed413-0000-2949-0000-00b40147f0e2?requestId=ab94ce8f-977b-4043-b004-cd3afd345e49",
  "requestId" : "ab94ce8f-977b-4043-b004-cd3afd345e49",
  "sqlState" : "00000",
  "statementHandle" : "019ed413-0000-2949-0000-00b40147f0e2",
  "message" : "Statement executed successfully.",
  "createdOn" : 1631171716728
}

想定通り、リージョン一覧が取得できましたね。