3 分で読むことができます

SnowflakeのGET_OBJECT_REFERENCES関数を物理テーブル化するPROCEDUREをつくる

執筆者 bird 更新日時 2021年12月23日

Topics: snowflake

今回はSnowflakeの超便利関数「GET_OBJECT_REFERENCES」を物理テーブル化するPROCEDUREを作ってみました。

今回は前回投稿した記事、「PythonでSnowflakeのGET_OBJECT_REFERENCES関数を物理テーブル化する」の続きという位置づけですので、ぜひこちらもご覧ください🙇

（GET_OBJECT_REFERENCES関数については以前の記事で紹介しております。）

前回のおさらい

前回はPythonでGET_OBJECT_REFERENCES関数を物理テーブル化していきました。

実行結果2

今回も、前回作成したテーブルと同じものを作ります。

今回使用するデータも前回と同じです。

  • TEST_A/B
    • テーブル
  • TEST_C
    • TEST_Aを参照するビュー
  • TEST_D
    • TEST_Bを参照するビュー
  • TEST_E
    • TEST_C/Dを参照するビュー

物理化してみた

今回作成したPROCEDUREは以下になります。

CREATE OR REPLACE PROCEDURE BIRD_DEMO.TO_TABLE.CREATE_GET_OBJECT_REFERENCES()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var sql_command1 = `show views in database BIRD_DEMO;`;
var stmt1 = snowflake.createStatement({sqlText : sql_command1});
var rs1 = stmt1.execute();
var objectReferences = [];
while (rs1.next()) {
// Snowflakeが提供するViewは対象外
if (rs1.schema_name == "INFORMATION_SCHEMA") {
continue;
}
var sql_command2 = `select * from table(get_object_references(
database_name => '` + rs1.database_name + `'
, schema_name => '` + rs1.schema_name + `'
, object_name => '` + rs1.name + `'));`;
var stmt2 = snowflake.createStatement({sqlText : sql_command2}
);
var rs2 = stmt2.execute();
while (rs2.next()) {
// stmt2を実行して得られた値で連想配列を作成し、配列に追加
var objectReference = {
database_name : rs2.DATABASE_NAME
, schema_name : rs2.SCHEMA_NAME
, object_name : rs2.OBJECT_NAME
, referenced_database_name : rs2.REFERENCED_DATABASE_NAME
, referenced_schema_name : rs2.REFERENCED_SCHEMA_NAME
, referenced_object_name : rs2.REFERENCED_OBJECT_NAME
, referenced_object_type : rs2.REFERENCED_OBJECT_TYPE
};
objectReferences.push(objectReference);
}
}
// 配列をJSON文字列化
var objectReferencesJson = JSON.stringify(objectReferences);
var sql_command3 = `create or replace temporary table BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_TEMP
(REFERENCE variant) as select ('` + objectReferencesJson + `');`;
var stmt3 = snowflake.createStatement({sqlText : sql_command3});
var rs3 = stmt3.execute();
// flatten関数を使用し、JSON文字列のkeyをカラムとして構造化
var sql_command4 = `create or replace table BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_2
as
select
value:database_name::varchar AS DATABASE_NAME
, value:schema_name::varchar AS SCHEMA_NAME
, value:object_name::varchar AS OBJECT_NAME
, value:referenced_database_name::varchar AS REFERENCED_DATABASE_NAME
, value:referenced_schema_name::varchar AS REFERENCED_SCHEMA_NAME
, value:referenced_object_name::varchar AS REFERENCED_OBJECT_NAME
, value:referenced_object_type::varchar AS REFERENCED_OBJECT_TYPE
from
BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_TEMP
, lateral flatten ( input => REFERENCE)`;
var stmt4 = snowflake.createStatement({sqlText : sql_command4});
var rs4 = stmt4.execute();
return 'Create Table GET_OBJECT_REFERENCES Done.';
$$
;
view raw snowflake_get_object_references_procedure.sql hosted with ❤ by GitHub

処理の流れをブロックごとに記載していきます。

1. show viewsコマンドでviewの一覧を取得し、ループさせてGET_OBJECT_REFERENCES関数を実行する

var sql_command1 = `show views in database BIRD_DEMO;`;
var stmt1 = snowflake.createStatement({sqlText : sql_command1});
var rs1 = stmt1.execute();
var objectReferences = [];
while (rs1.next()) {
// Snowflakeが提供するViewは対象外
if (rs1.schema_name == "INFORMATION_SCHEMA") {
continue;
}
var sql_command2 = `select * from table(get_object_references(
database_name => '` + rs1.database_name + `'
, schema_name => '` + rs1.schema_name + `'
, object_name => '` + rs1.name + `'));`;
var stmt2 = snowflake.createStatement({sqlText : sql_command2}
);
var rs2 = stmt2.execute();
view raw snowflake_get_object_references_procedure_1.sql hosted with ❤ by GitHub

ここまでは前回のPythonで実装した処理と同じです。

2. 結果をループさせて連想配列化し、配列にpush

while (rs2.next()) {
// stmt2を実行して得られた値で連想配列を作成し、配列に追加
var objectReference = {
database_name : rs2.DATABASE_NAME
, schema_name : rs2.SCHEMA_NAME
, object_name : rs2.OBJECT_NAME
, referenced_database_name : rs2.REFERENCED_DATABASE_NAME
, referenced_schema_name : rs2.REFERENCED_SCHEMA_NAME
, referenced_object_name : rs2.REFERENCED_OBJECT_NAME
, referenced_object_type : rs2.REFERENCED_OBJECT_TYPE
};
objectReferences.push(objectReference);
}
}
view raw snowflake_get_object_references_procedure_2.sql hosted with ❤ by GitHub

同じkeyの連想配列を格納した配列が出来上がります。

3. 2の配列の中の連想配列をJSON化し、Snowflakeでtempテーブル化

// 配列をJSON文字列化
var objectReferencesJson = JSON.stringify(objectReferences);
var sql_command3 = `create or replace temporary table BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_TEMP
(REFERENCE variant) as select ('` + objectReferencesJson + `');`;
var stmt3 = snowflake.createStatement({sqlText : sql_command3});
var rs3 = stmt3.execute();
view raw snowflake_get_object_references_procedure_3.sql hosted with ❤ by GitHub

SnowflakeはJSON等の半構造化データをサポートしています。（詳しくはこちら）

tempテーブルを確認してみます。

select
*
from
BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_TEMP
view raw select_get_object_references_temp.sql hosted with ❤ by GitHub

temp-1

問題なく作成されています。

4. 3のtempテーブルをフラット化し、GET_OBJECT_REFERENCES関数の結果と同じ構造のテーブルを作成

// flatten関数を使用し、JSON文字列のkeyをカラムとして構造化
var sql_command4 = `create or replace table BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_2
as
select
value:database_name::varchar AS DATABASE_NAME
, value:schema_name::varchar AS SCHEMA_NAME
, value:object_name::varchar AS OBJECT_NAME
, value:referenced_database_name::varchar AS REFERENCED_DATABASE_NAME
, value:referenced_schema_name::varchar AS REFERENCED_SCHEMA_NAME
, value:referenced_object_name::varchar AS REFERENCED_OBJECT_NAME
, value:referenced_object_type::varchar AS REFERENCED_OBJECT_TYPE
from
BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_TEMP
, lateral flatten ( input => REFERENCE)`;
var stmt4 = snowflake.createStatement({sqlText : sql_command4});
var rs4 = stmt4.execute();
view raw snowflake_get_object_references_procedure_4.sql hosted with ❤ by GitHub

flatten関数を使用することで、半構造化データを構造化しています。（詳しくはこちら）

ちなみに、flatten関数を使用した結果をそのまま出力すると以下になります。

select
*
from
BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_TEMP
, lateral flatten ( input => REFERENCE)
view raw flatten.sql hosted with ❤ by GitHub

flatten

配列の要素ごとにレコード化されていますね。

select句で「value:キー名」と指定して、値を取り出すことができます。

それでは実行結果を確認してみましょう。

select
*
from
BIRD_DEMO.TO_TABLE.GET_OBJECT_REFERENCES_2
view raw select_get_object_references_2.sql hosted with ❤ by GitHub

実行結果3

しっかり物理化されています！👏

実行時間

ひとつ課題点としては、当然ですがViewの数が多いと実行時間が増えてしまいます。

業務で試した際、View数107で約1分かかりました。

Pythonで実装したものも同じくらい時間がかかるので、おそらく全View分のGET_OBJECT_REFERENCES関数を実行するところがネックになっていると思われます。

おわりに

いかがだったでしょうか。

PROCEDUREで実装するときに、Python用コネクタAPIの「executemany」メソッドにあたるものが見つからなったため、違うロジックで実装してみました。

噂によると、依存関係を一括して取得できるようになるという発表がSnowdayであったそうです！これは朗報ですね。

最後まで読んでいただき、ありがとうございます！🙇

bird

執筆者 bird
4 分で読むことができます。
Snowflakeの料金体系｜クレジットと費用最適化のポイントをご紹介
2021年 6月 28日
5 分で読むことができます。
【Snowflake】新機能「Streamlit in Snowflake」とは何者か！？
2023年 9月 21日
1 分で読むことができます。
誤ってupdateしてしまったレコードをtime travelで復元する
2021年 12月 27日
5 分で読むことができます。
【禁断の比較？】SnowflakeとTreasure Dataを比べてみました
2021年 5月 24日
1 分で読むことができます。
【Snowflake】Task（タスク）を作成してみよう
2020年 8月 6日
6 分で読むことができます。
【Snowflake】初めてStreamを使ったデータパイプライン構築をした話
2022年 12月 16日
運営会社
メニュー

Copyright © 2020 INSIGHT LAB, Inc.