SnowflakeでJSONをフラット化するビューを自動で作成する

Snowflakeにて、JSONを格納したVARIANT型カラムのテーブルを リレーショナル型の列に変換(フラット化)するビューを ストアドプロシージャで自動的に生成する。
2021.08.21

データアナリティクス事業本部、池田です。
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も簡単にフラット化できるようになることを切に願っています…

Snowflakeで半構造化データのカラム定義検出を試してみた

関連情報/参考にさせていただいたページ