ジョブの実行時に利用するSnowflakeのロールが指定できるようになりました #データ統合基盤 #CSアナリティクス

Snowflake＆CSA連携

#CSA: Job Management Console(JMC)

#Snowflake

#クラスメソッド

大高大輔

2020.12.16

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

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

弊社プロダクトのデータ分析基盤「カスタマーストーリーアナリティクス」(Customer Story Analytics:CSA)のサービスである「CSA Job Management Console」(CSA JMC)では、Snowflakeに対して「テーブルの作成」や「ジョブ」による「データ連携」および「SQL」を実行することが可能です。

この「テーブルの作成」や「ジョブ」による「データ連携」および「SQL」を実行する際に、Snowflakeへの接続に利用する「ロール」を明示的に指定できるようになりました。

これにより、Snowflakeへの接続ユーザが利用できる「ロール」であれば、任意の「ロール」に切り替えてクエリを実行することが可能となります。イメージとしては、以下のようなイメージとなります。

例えば、特定の「ロール」でないとアクセスできないデータベースやテーブルに対して、接続設定の「ロール」に限定せずに任意の「ロール」に切り替えてクエリを実行することができます。

当エントリでは、実際に特定の「ロール」でのみ実行できるSQLクエリを実行するジョブを作成して試してみたいと思います。

CSA JMCの挙動確認バージョン

当エントリの内容は以下のCSA JMCバージョンで挙動を確認しています。

CSA JMC v5.3.0

SQLクエリの準備

まずは、JMCで実行するSQLクエリを準備します。

今回は以下のようにROLE_DEMOデータベースのREGIONテーブルをSELECTするだけのクエリとします。このROLE_DEMOデータベース、および、REGIONテーブルはSYSADMINロールがオーナーとなっており、他のロールには権限を付与していません。

従って、今回JMCで設定している接続情報のロールDEVELOPERではアクセスできないテーブルとなっています。

SELECT R_NAME FROM ROLE_DEMO.PUBLIC.REGION;

実際に、それぞれのロールでクエリをsnowsqlで実行して確認します。

foo_bar#XSMALL_WH@(no database).(no schema)>USE ROLE DEVELOPER;
╒══════════════════════════════════╕
│ status                           │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.140s
foo_bar#XSMALL_WH@(no database).(no schema)>USE WAREHOUSE XSMALL_WH;
╒══════════════════════════════════╕
│ status                           │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.101s
foo_bar#XSMALL_WH@(no database).(no schema)>SELECT R_NAME FROM ROLE_DEMO.PUBLIC.REGION;
002003 (02000): SQL compilation error:
Database 'ROLE_DEMO' does not exist or not authorized.

権限が無いので、Database 'ROLE_DEMO' does not exist or not authorized.というエラーになりました。

foo_bar#XSMALL_WH@(no database).(no schema)>USE ROLE SYSADMIN;
╒══════════════════════════════════╕
│ status                           │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.129s
foo_bar#XSMALL_WH@(no database).(no schema)>USE WAREHOUSE XSMALL_WH;
╒══════════════════════════════════╕
│ status                           │
╞══════════════════════════════════╡
│ Statement executed successfully. │
╘══════════════════════════════════╛
1 Row(s) produced. Time Elapsed: 0.136s
foo_bar#XSMALL_WH@(no database).(no schema)>SELECT R_NAME FROM ROLE_DEMO.PUBLIC.REGION;
╒═════════════╕
│ R_NAME      │
╞═════════════╡
│ AFRICA      │
├─────────────┤
│ AMERICA     │
├─────────────┤
│ ASIA        │
├─────────────┤
│ EUROPE      │
├─────────────┤
│ MIDDLE EAST │
╘═════════════╛
5 Row(s) produced. Time Elapsed: 0.160s

こちらは問題なくSELECTできていますね。このSQLクエリをcan_select_with_sysadmin_role.sqlとして保存し、JMCに登録しておきます。

ジョブでロールを指定して実行する

次に、ロールにSYSADMINを指定した「ジョブ」を作成し、実際にジョブを実行してみます。

「ジョブ」での具体的なロール指定の箇所はこちらです。

初期画面では接続設定で指定しているロールが設定されているので、ここをプルダウンからSYSADMINへ変更します。

あとは通常通り、「構成要素」として「SQL」のcan_select_with_sysadmin_role.sqlを指定して保存します。

保存したらジョブを実行して、成功しているのを確認します。

無事成功していたので、今度はSnowflake側のクエリ履歴画面から該当クエリIDを特定します。

クエリIDが特定できたら、以下のクエリで本当にSYSADMINロールクエリが実行されたか確認してみます。

USE ROLE ACCOUNTADMIN;
USE DATABASE SNOWFLAKE;
USE SCHEMA ACCOUNT_USAGE;

SELECT
  QUERY_TEXT,
  ROLE_NAME,
  WAREHOUSE_NAME
FROM
  QUERY_HISTORY
WHERE
  QUERY_ID = '0198f1a8-00a4-25c2-0000-0949001a823a'
;

╒═════════════════════════════════════════════╤═══════════╤════════════════╕
│ QUERY_TEXT                                  │ ROLE_NAME │ WAREHOUSE_NAME │
╞═════════════════════════════════════════════╪═══════════╪════════════════╡
│ SELECT R_NAME FROM ROLE_DEMO.PUBLIC.REGION; │ SYSADMIN  │ XSMALL_WH      │
╘═════════════════════════════════════════════╧═══════════╧════════════════╛
1 Row(s) produced. Time Elapsed: 0.870s

想定どおり、ロールはSYSADMINでクエリが実行されていました！