この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部、池田です。
Snowflakeでは、JSONなどの半構造化データを柔軟に扱うことができるそうです。
JSONを VARIANT型
のカラムのテーブルにインポートして、
ビューでフラット化する、というのはよくあるパターンではないかと思います。
(例えば この辺のブログ でもやってます。)
それをやろうとした時に、現状のSnowflakeではビューのDDL定義は予め作成しなければいけません。 JSONの種類が少なければ、JSON Schemaや実ファイルを見ながらなんとか作れるかと思うのですが、 種類が多くなると手作業は辛いです。
そのビュー作成を自動化する公式のブログがありましたので、 その内容をやってみつつ、(私にとって)使い易いように手を加えてみることにしました。
【 Automating Snowflake’s Semi-Structured JSON Data Handling 】
【 Automating Snowflake’s Semi-Structured JSON Data Handling: Part 2 】
公式ブログの内容をやってみる
今回使用するJSONは↓です。 (パート1に掲載されているサンプルJSONを、パート2の内容が確認できるよう少し複雑に直したものです。)
{
"ID": 1,
"color": [{
"name": "black",
"type": "lower"
}, {
"name": "BLACK",
"type": "upper"
}],
"category": "hue",
"type": "primary",
"code": {
"rgb": [255, 255, 255],
"hex": "#000"
}
}, {
"ID": 2,
"color": [{
"name": "white",
"type": "lower"
}, {
"name": "WHITE",
"type": "upper"
}],
"category": "value",
"code": {
"rgb": [0, 0, 0],
"hex": "#FFF"
}
}, {
"ID": 3,
"color": [{
"name": "red",
"type": "lower"
}, {
"name": "RED",
"type": "upper"
}],
"category": "hue",
"type": "primary",
"code": {
"rgb": [255, 0, 0],
"hex": "#FF0"
}
}, {
"ID": 4,
"color": [{
"name": "blue",
"type": "lower"
}, {
"name": "BLUE",
"type": "upper"
}],
"category": "hue",
"type": "primary",
"code": {
"rgb": [0, 0, 255],
"hex": "#00F"
}
}, {
"ID": 5,
"color": [{
"name": "yellow",
"type": "lower"
}, {
"name": "YELLOW",
"type": "upper"
}],
"category": "hue",
"type": "primary",
"code": {
"rgb": [255, 255, 0],
"hex": "#FF0"
}
}, {
"ID": 6,
"color": [{
"name": "green",
"type": "lower"
}, {
"name": "GREEN",
"type": "upper"
}],
"category": "hue",
"type": "secondary",
"code": {
"rgb": [0, 255, 0],
"hex": "#0F0"
}
}
color
をオブジェクトの配列に、
code.rgb
を配列に変更しています。
↓VARIANT型のカラムのテーブルに格納するとこんな感じ。
colors テーブル
V
{ "ID": 1, "category": "hue", "code": { "hex": "#000", "rgb": [ 255, 255, 255 ] }, "color": [ { "name": "black", "type": "lower" }, { "name": "BLACK", "type": "upper" } ], "type": "primary" }
{ "ID": 2, "category": "value", "code": { "hex": "#FFF", "rgb": [ 0, 0, 0 ] }, "color": [ { "name": "white", "type": "lower" }, { "name": "WHITE", "type": "upper" } ] }
{ "ID": 3, "category": "hue", "code": { "hex": "#FF0", "rgb": [ 255, 0, 0 ] }, "color": [ { "name": "red", "type": "lower" }, { "name": "RED", "type": "upper" } ], "type": "primary" }
{ "ID": 4, "category": "hue", "code": { "hex": "#00F", "rgb": [ 0, 0, 255 ] }, "color": [ { "name": "blue", "type": "lower" }, { "name": "BLUE", "type": "upper" } ], "type": "primary" }
{ "ID": 5, "category": "hue", "code": { "hex": "#FF0", "rgb": [ 255, 255, 0 ] }, "color": [ { "name": "yellow", "type": "lower" }, { "name": "YELLOW", "type": "upper" } ], "type": "primary" }
{ "ID": 6, "category": "hue", "code": { "hex": "#0F0", "rgb": [ 0, 255, 0 ] }, "color": [ { "name": "green", "type": "lower" }, { "name": "GREEN", "type": "upper" } ], "type": "secondary" }
また、当ブログのクエリなどは全て SYSADMIN
ロールで作業しています。
Automating Snowflake’s Semi-Structured JSON Data Handling
パート1の方では、シンプルな構造のJSONに対してフラット化ビューの作成を試みています。
途中のコードや詳しい解説は公式ブログの方をご覧下さい。ポイントだけ記載していきます。
FLATTEN という関数を使って、JSONの要素名やその型を取得します。
FLATTEN は、半構造化データをリレーショナル表現に変換するために使用できます。
(初めはこのFLATTENを噛ませるだけでフラット化してくれるのかと思ったのですが、そんなことはなかった…)
実装の肝となるのが、FLATTENと正規表現を使った↓このSQLです。
SELECT DISTINCT
regexp_replace(regexp_replace(f.path,'\\[(.+)\\]'),'(\\w+)','"\\1"') AS path_name,
DECODE (substr(typeof(f.value),1,1),'A','ARRAY','B','BOOLEAN','I','FLOAT','D','FLOAT','STRING') AS attribute_type,
REGEXP_REPLACE(REGEXP_REPLACE(f.path, '\\[(.+)\\]'),'[^a-zA-Z0-9]','_') AS alias_name
FROM
colors,
LATERAL FLATTEN(v, RECURSIVE=>true) f
WHERE TYPEOF(f.value) != 'OBJECT'
↑の結果は↓のような感じです。
PATH_NAME ATTRIBUTE_TYPE ALIAS_NAME
"ID" FLOAT ID
"category" STRING category
"code"."hex" STRING code_hex
"code"."rgb" ARRAY code_rgb
"code"."rgb" FLOAT code_rgb
"color" ARRAY color
"color"."name" STRING color_name
"color"."type" STRING color_type
"type" STRING type
ここまでの情報が手に入れば、あとはJavaScriptでぐるぐるすれば ビューのDDLを生成できそうですね。
Automating Snowflake’s Semi-Structured JSON Data Handling: Part 2
シンプルな構造のJSONを扱ったパート1に対して、 パート2では、 カラム名の大/小文字の考慮・カラム型の判定方法の考慮・配列の要素への対応・オブジェクトの配列への対応、 を行っています。
その辺りも網羅した最終版のコードが↓になります。
(私の環境の問題かもしれませんが、公式ブログ上のコードは \
が消えていて、そのままでは使えなかったので直したものを掲載します。)
クリックでSQLを表示する/折りたたむ
create_view_over_json ストアドプロシージャ
CREATE OR REPLACE PROCEDURE create_view_over_json (TABLE_NAME varchar, COL_NAME varchar, VIEW_NAME varchar, COLUMN_CASE varchar, COLUMN_TYPE varchar)
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
// CREATE_VIEW_OVER_JSON - Craig Warman, Snowflake Computing, DEC 2019
//
// This stored procedure creates a view on a table that contains JSON data in a column.
// of type VARIANT. It can be used for easily generating views that enable access to
// this data for BI tools without the need for manual view creation based on the underlying
// JSON document structure.
//
// Parameters:
// TABLE_NAME - Name of table that contains the semi-structured data.
// COL_NAME - Name of VARIANT column in the aforementioned table.
// VIEW_NAME - Name of view to be created by this stored procedure.
// COLUMN_CASE - Defines whether or not view column name case will match
// that of the corresponding JSON document attributes. When
// set to 'uppercase cols' the view column name for a JSON
// document attribute called "City.Coord.Lon" would be generated
// as "CITY_COORD_LON", but if this parameter is set to
// 'match col case' then it would be generated as "City_Coord_Lon".
// COLUMN_TYPE - The datatypes of columns generated for the view will match
// those of the corresponding JSON data attributes if this param-
// eter is set to 'match datatypes'. But when this parameter is
// set to 'string datatypes' then the datatype of all columns
// in the resulting view will be set to STRING (VARCHAR) or ARRAY.
//
// Usage Example:
// call create_view_over_json('db.schema.semistruct_data', 'variant_col', 'db.schema.semistruct_data_vw', 'match col case', 'match datatypes');
//
// Important notes:
// - JSON documents may contain attributes that are actually reserved words, which
// may cause SQL compilation errors to be thrown during the CREATE VIEW execution.
// The easiest work-around in this case is to specify the 'match col case' for the
// COLUMN_CASE parameter since this causes column names to be enclosed by double
// quotes when the view is generated.
// - Sometimes there cases where the JSON documents attributes with the same name
// but actually contain data with different datatypes. For example, one document
// might have an attribute that contains the value "1" while another document
// has the same attribute with a value of "none". This may lead to problems since
// Snowflake will interpret the datatype of the first document as being numeric,
// while the second would be a string value. Specifying 'string datatypes' for the
// COLUMN_TYPE parameter should help alleviate such issues.
// - Column names for arrays in the JSON document structure will be prefixed by the
// path to the array. For example, consider a simple array such as:
// "code": {
// "rgb": [255,255,0]
// The corresponding view columns in this case would be code_rgb_0, code_rgb_1, and
// code_rgb_2.
// - Column names for object arrays are similarly generated. For example, consider an
// object array such as:
// contact: {
// phone: [
// { type: "work", number:"404-555-1234" },
// { type: "mobile", number:"770-555-1234" }
// The corresponding view columns in this case would be contact_phone_type and
// contact_phone_number.
// - This procedure will work for arrays that are one level deep in the JSON structure.
// Nested arrays will be materialized in the view as columns of type ARRAY.
// - This procedure does not provide provisions for handling cases where multiple JSON
// document schemas exist within a given dataset. In other words, the views it
// generates reflect "flattened" versions of all the underlying JSON document schemas
// found within the given dataset. Multi-schema support could be added by
// implementing a pre-process step whereby JSON document records are written to
// separate columns (or tables) based on their schema prior to generating
// corresponding views with this procedure, possibly through the use of materialized
// views. An alternative approach would be to add WHERE clauses to this procedure's
// element and array queries so as to isolate the proper JSON schema for each
// execution (note that this would require inclusion of corresponding WHERE clauses
// in the generated views as well).
// - Execution of this procedure may take an extended period of time for very
// large datasets, or for datasets with a wide variety of document attributes
// (since the view will have a large number of columns)
//
// Attribution:
// I leveraged code developed by Alan Eldridge as the basis for this stored procedure.
var alias_dbl_quote = "";
var path_name = "regexp_replace(regexp_replace(f.path,'\\\\[(.+)\\\\]'),'(\\\\w+)','\"\\\\1\"')" // This generates paths with levels enclosed by double quotes (ex: "path"."to"."element"). It also strips any bracket-enclosed array element references (like "[0]")
var attribute_type = "DECODE (substr(typeof(f.value),1,1),'A','ARRAY','B','BOOLEAN','I','FLOAT','D','FLOAT','STRING')"; // This generates column datatypes of ARRAY, BOOLEAN, FLOAT, and STRING only
var alias_name = "REGEXP_REPLACE(REGEXP_REPLACE(f.path, '\\\\[(.+)\\\\]'),'[^a-zA-Z0-9]','_')" ; // This generates column aliases based on the path
var table_list = TABLE_NAME;
var col_list = "";
var array_num = 0;
if (COLUMN_CASE.toUpperCase().charAt(0) == 'M') {
alias_dbl_quote = "\""; } // COLUMN_CASE parameter is set to 'match col case' so add double quotes around view column alias name
if (COLUMN_TYPE.toUpperCase().charAt(0) == 'S') {
attribute_type = "DECODE (typeof(f.value),'ARRAY','ARRAY','STRING')"; } // COLUMN_TYPE parameter is set to 'string datatypes' so typecast to STRING instead of value returned by TYPEPOF function
// Build a query that returns a list of elements which will be used to build the column list for the CREATE VIEW statement
var element_query = "SELECT DISTINCT \n" +
path_name + " AS path_name, \n" +
attribute_type + " AS attribute_type, \n" +
alias_name + " AS alias_name \n" +
"FROM \n" +
TABLE_NAME + ", \n" +
"LATERAL FLATTEN(" + COL_NAME + ", RECURSIVE=>true) f \n" +
"WHERE TYPEOF(f.value) != 'OBJECT' \n" +
"AND NOT contains(f.path,'[') "; // This prevents traversal down into arrays
// Run the query...
var element_stmt = snowflake.createStatement({sqlText:element_query});
var element_res = element_stmt.execute();
// ...And loop through the list that was returned
while (element_res.next()) {
// Add any non-array elements and datatypes to the column list
// They will look something like this when added:
// col_name:"name"."first"::STRING as "name_first",
// col_name:"name"."last"::STRING as "name_last"
// Note that double-quotes around the column aliases will be added
// only when the COLUMN_CASE parameter is set to 'match col case'
if (element_res.getColumnValue(2) != 'ARRAY') {
if (col_list != "") {
col_list += ", \n";}
col_list += COL_NAME + ":" + element_res.getColumnValue(1); // Start with the element path name
col_list += "::" + element_res.getColumnValue(2); // Add the datatype
col_list += " as " + alias_dbl_quote + element_res.getColumnValue(3) + alias_dbl_quote; // And finally the element alias
}
// Array elements get handled in the following section:
else {
array_num++;
var simple_array_col_list = "";
var object_array_col_list = "";
// Build a query that returns the elements in the current array
var array_query = "SELECT DISTINCT \n"+
path_name + " AS path_name, \n" +
attribute_type + " AS attribute_type, \n" +
alias_name + " AS attribute_name, \n" +
"f.index \n" +
"FROM \n" +
TABLE_NAME + ", \n" +
"LATERAL FLATTEN(" + COL_NAME + ":" + element_res.getColumnValue(1) + ", RECURSIVE=>true) f \n" +
"WHERE REGEXP_REPLACE(f.path, '.+(\\\\w+\\\\[.+\\\\]).+', 'SubArrayEle') != 'SubArrayEle' "; // This prevents return of elements of nested arrays (the entire array will be returned in this case)
// Run the query...
var array_stmt = snowflake.createStatement({sqlText:array_query});
var array_res = array_stmt.execute();
// ...And loop through the list that was returned.
// Add array elements and datatypes to the column list
// The way that they're added depends on the type of array:
//
// Simple arrays:
// These are lists of values that are addressible by their index number
// For example:
// "code": {
// "rgb": [255,255,0]
// These will be added to the view column list like so:
// col_name:"code"."rgb"[0]::FLOAT as code_rgb_0,
// col_name:"code"."rgb"[1]::FLOAT as code_rgb_1,
// col_name:"code"."rgb"[2]::FLOAT as code_rgb_2
//
// Object arrays:
// Collections of objects that addressible by key
// For example:
// contact: {
// phone: [
// { type: "work", number:"404-555-1234" },
// { type: "mobile", number:"770-555-1234" }
// These will be added to the view column list like so:
// a1.value:"type"::STRING as "phone_type",
// a1.value:"number"::STRING as "phone_number"
// Along with an additional LATERAL FLATTEN construct in the table list:
// FROM mydatabase.public.contacts,
// LATERAL FLATTEN(json_data:"contact"."phone") a1;
//
while (array_res.next()) {
if (array_res.getColumnValue(1).substring(1) == "") { // The element path name is empty, so this is a simple array element
if (simple_array_col_list != "") {
simple_array_col_list += ", \n";}
simple_array_col_list += COL_NAME + ":" + element_res.getColumnValue(1); // Start with the element path name
simple_array_col_list += "[" + array_res.getColumnValue(4) + "]"; // Add the array index
simple_array_col_list += "::" + array_res.getColumnValue(2); // Add the datatype
simple_array_col_list += " as " + alias_dbl_quote + element_res.getColumnValue(3) + "_" + array_res.getColumnValue(4) + alias_dbl_quote; // And finally the element alias - Note that the array alias is added as a prefix to ensure uniqueness
}
else { // This is an object array element
if (object_array_col_list != "") {
object_array_col_list += ", \n";}
object_array_col_list += "a" + array_num + ".value:" + array_res.getColumnValue(1).substring(1); // Start with the element name (minus the leading '.' character)
object_array_col_list += "::" + array_res.getColumnValue(2); // Add the datatype
object_array_col_list += " as " + alias_dbl_quote + element_res.getColumnValue(3) + array_res.getColumnValue(3) + alias_dbl_quote; // And finally the element alias - Note that the array alias is added as a prefix to ensure uniqueness
}
}
// If no object array elements were found then add the simple array elements to the
// column list...
if (object_array_col_list == "") {
if (col_list != "") {
col_list += ", \n";}
col_list += simple_array_col_list;
}
// ...otherwise, add the object array elements to the column list along with a
// LATERAL FLATTEN clause that references the current array to the table list
else {
if (col_list != "") {
col_list += ", \n";}
col_list += object_array_col_list;
table_list += ",\n LATERAL FLATTEN(" + COL_NAME + ":" + element_res.getColumnValue(1) + ") a" + array_num;
}
}
}
// Now build the CREATE VIEW statement
var view_ddl = "CREATE OR REPLACE VIEW " + VIEW_NAME + " AS \n" +
"SELECT \n" + col_list + "\n" +
"FROM " + table_list;
// Now run the CREATE VIEW statement
var view_stmt = snowflake.createStatement({sqlText:view_ddl});
var view_res = view_stmt.execute();
return view_res.next();
$$;
CALL create_view_over_json('colors', 'v', 'colors_flat', 'uppercase cols', 'match datatypes');
のように呼び出します。
引数については↓
TABLE_NAME
: フラット化したい対象のVARIANT型のカラムを持つテーブル名COL_NAME
: 対象のVARIANT型のカラム名VIEW_NAME
: 作成するビュー名COLUMN_CASE
uppercase cols
: 列名は大文字で定義match col case
: 列名の大/小文字はJSONの要素(key名)に従う
COLUMN_TYPE
match datatypes
: JSON内の情報からカラム型を定義(型を1つに特定できないとエラー)string datatypes
: 全て文字列型(と配列)として定義
この ストアドプロシージャ を使って、当ブログ冒頭のサンプルJSONをフラット化した結果が↓です。
colors_flat ビュー
ID CATEGORY CODE_HEX CODE_RGB_0 CODE_RGB_1 CODE_RGB_2 COLOR_NAME COLOR_TYPE TYPE
1 hue #000 255 255 255 black lower primary
1 hue #000 255 255 255 BLACK upper primary
2 value #FFF 0 0 0 white lower
2 value #FFF 0 0 0 WHITE upper
3 hue #FF0 255 0 0 red lower primary
3 hue #FF0 255 0 0 RED upper primary
4 hue #00F 0 0 255 blue lower primary
4 hue #00F 0 0 255 BLUE upper primary
5 hue #FF0 255 255 0 yellow lower primary
5 hue #FF0 255 255 0 YELLOW upper primary
6 hue #0F0 0 255 0 green lower secondary
6 hue #0F0 0 255 0 GREEN upper secondary
CODE_RGB_0
などのように配列はそれぞれ独立したカラムにして、
オブジェクトの配列だった COLOR
配下の要素は複数のレコードとして生成してくれています。
良い感じっぽいですね。たぶん。
気になったこと
カラム型を判定しているところなんですが…
var attribute_type = "DECODE (substr(typeof(f.value),1,1),'A','ARRAY','B','BOOLEAN','I','FLOAT','D','FLOAT','STRING')"; // This generates column datatypes of ARRAY, BOOLEAN, FLOAT, and STRING only
のようになっていて、BOOLEAN・FLOAT・STRINGにしか分類してないのですよね…
特に日付や時刻を扱いたい場合は、この辺のカスタマイズが必要そう。
実運用するには、使いたいJSONや型の状況に合わせて、手直しは必要そうな感触です。
スキーマ配下のテーブルを全てまとめてフラット化する
前章のストアドプロシージャでは、1つのテーブルからフラット化した1つのビューを作成できました。
そんな折には、複数のテーブルからそれぞれのビューを一気に作って欲しい気分になりました。
指定したスキーマ配下のテーブル名を拾って、前章のストアドプロシージャを繰り返し呼び出してくれる機能を実装してみました。
検証した環境は↓の感じ。
json
スキーマ: VARIANT型(JSON)のカラムのテーブルだけが複数存在relational
スキーマ: jsonスキーマのテーブルをフラット化したビューの作成先(空っぽ)create_view_over_json
ストアドプロシージャ: 前章のモジュールと同じものを作成済みconvert_json_in_schema
ストアドプロシージャ: 今回新たに作成するもの(後述)
実装したストアドプロシージャは↓です。
convert_json_in_schema ストアドプロシージャ
CREATE OR REPLACE PROCEDURE convert_json_in_schema (JSON_SCHEMA_NAME varchar, COL_NAME varchar, RELATIONAL_SCHEMA_NAME varchar)
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
// 処理対象のテーブルの取得
const GET_TABLES_SQL = `SHOW TABLES IN SCHEMA ${JSON_SCHEMA_NAME};`;
let get_tables_stmt = snowflake.createStatement({sqlText:GET_TABLES_SQL});
let tables_res = get_tables_stmt.execute();
let tables = []; // スキーマ内のJSON格納テーブル名の配列
while(tables_res.next()) {
tables.push(tables_res.getColumnValue("name"));
}
// 結果を格納するテーブルを作成
const UNIXTIME = String((new Date()).getTime()).substring(0, 10);
const RES_TABLE_NAME = `${UNIXTIME}_results`;
const CREATE_DEST_SQL = (`CREATE TABLE ${RELATIONAL_SCHEMA_NAME}."${RES_TABLE_NAME}" (
json_schema_name VARCHAR(100) DEFAULT '${JSON_SCHEMA_NAME}',
table_name VARCHAR(100),
result BOOLEAN);`).replace(/\r?\n/g, "");
let create_stmt = snowflake.createStatement({sqlText:CREATE_DEST_SQL});
create_stmt.execute();
// 別のストアドプロシージャを呼び出して、ビューを作成する
for (const table_name of tables) {
const CALL_SQL = `CALL create_view_over_json('${JSON_SCHEMA_NAME}.${table_name}', '${COL_NAME}', '${RELATIONAL_SCHEMA_NAME}.${table_name}', 'uppercase cols', 'match datatypes');`;
let call_stmt = snowflake.createStatement({sqlText:CALL_SQL});
let is_view_created;
try {
let call_res = call_stmt.execute();
call_res.next();
is_view_created = Boolean(call_res.getColumnValue(1) == "true");
} catch {
is_view_created = false;
}
// 結果を記録
const OUTPUT_SQL = `INSERT INTO ${RELATIONAL_SCHEMA_NAME}."${RES_TABLE_NAME}" (table_name, result) VALUES ('${table_name}', ${is_view_created});`;
let output_stmt = snowflake.createStatement({sqlText:OUTPUT_SQL});
output_stmt.execute();
}
return `Done. Check results at ${RELATIONAL_SCHEMA_NAME}."${RES_TABLE_NAME}" !!!`;
$$
CALL convert_json_in_schema('json', 'v', 'relational');
のような感じで、VARIANT型カラムのテーブルを含むスキーマ名・VARIANT型のカラム名(フラット化対象)・各ビューの作成先スキーマ、
を渡して呼んであげると、まとめて処理してくれます。
各テーブルの処理結果は、 {実行時刻}_results
という名称でテーブルを作って、
そこに格納してくれます。
↓こんな感じ。
↑COLORS4というテーブルはわざと失敗するようなJSONにしたので、
4つのテーブルに対して3つビューが作成されています。
ビュー作成結果を格納したテーブルには↓こんな風に記録されます。
{実行時刻}_results テーブル
JSON_SCHEMA_NAME TABLE_NAME RESULT
json COLORS TRUE
json COLORS2 TRUE
json COLORS3 TRUE
json COLORS4 FALSE
おわりに
これで自動化することで多少楽になりそうですね。
ですが…Parquetなどはすでに列定義の検出ができるようになっているようなので、 JSONも簡単にフラット化できるようになることを切に願っています…
関連情報/参考にさせていただいたページ
- Automating Snowflake’s Semi-Structured JSON Data Handling
- Automating Snowflake’s Semi-Structured JSON Data Handling: Part 2
- 半構造化データ型
- FLATTEN
- ステップ3。データをフラット化する
- Snowflake 半構造化データ用の関数を色々使ってみた
- Snowflake 半構造化データサポートの解説 | Snowflake Advent Calendar 2019 #SnowflakeDB
- ストアドプロシージャの概要
- ストアドプロシージャ API
- Snowflakeで半構造化データのカラム定義検出を試してみた
- ステージングされた半構造化データファイル内の列定義の検出