RedshiftのSuper型を試してみた

背景・目的

RedshiftのSUPER型でハマったのでメモを残しておきます。

まとめ

• SUPER型に登録する際には、JSON_PARSE関数を通した上でKEY項目は小文字で登録する必要がありました。

概要

SUPER型とは

SUPER データ型を使用して、半構造化データまたはドキュメントを値として保存する。
半構造化データは、SQL データベースで使用されるリレーショナルデータモデルの厳密な表形式の構造に準拠してない。これには、データ内の個別のエンティティを参照するタグが含まれる。
配列、ネストされた構造体、JSONなどのシリアル化形式に関連付けられているその他の複雑な構造体など含めることができる。

考慮事項

• JDBC ドライバーのバージョン 1.2.50、ODBC ドライバーのバージョン 1.4.17 以降、および Amazon Redshift Python ドライバーのバージョン 2.0.872 以降を使用する。
• SUPER データ型は、個々の SUPER フィールドまたはオブジェクトに対して最大 1 MB のデータのみをサポート

実践

事前準備

テーブル作成

CREATE SCHEMA test

CREATE TABLE test.super_type_table_case_insensitive(
  id INTEGER
  ,super_column SUPER
)


===

SELECT * FROM svv_all_columns where SCHEMA_NAME ='test' AND TABLE_NAME='super_type_table_case_insensitive'


database_name,schema_name,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,numeric_precision,numeric_scale,remarks
sample,test,super_type_table_case_insensitive,id,1,,YES,integer,,32,0,
sample,test,super_type_table_case_insensitive,super_column,2,,YES,super,,,,

実施結果

パターン	JSON_PARSEを使用して登録しているか？	大文字/小文字登録	抽出可否
1	✕	大文字	✕
2	✕	小文字	✕
3	○	大文字	✕
4	○	小文字	○

• JSON_PARSE関数を介して登録し、JSONのキー名は小文字で登録する必要がある。

アスタリスクで抽出

SELECT * FROM test.super_type_table_case_insensitive order by id

===

id,super_column
1,"""{\""ID\"":\""1\"",\""NESTEDS_VALUES\"":{\""KEY1\"":\""1000\"",\""KEY2\"":\""2000\""},\""ARRAY_VALUES\"":[1,2,3]}"""
2,"""{\""id\"":\""1\"",\""nested_values\"":{\""key1\"":\""1000\"",\""key2\"":\""2000\""},\""array_values\"":[1,2,3]}"""
3,"{""ID"":""1"",""NESTEDS_VALUES"":{""KEY1"":""1000"",""KEY2"":""2000""},""ARRAY_VALUES"":[1,2,3]}"
4,"{""id"":""1"",""nested_values"":{""key1"":""1000"",""key2"":""2000""},""array_values"":[1,2,3]}"

列名を指定して抽出

SELECT id, super_column.id FROM test.super_type_table_case_insensitive order by 1

===

id,id
1,
2,
3,
4,"""1"""

列の型を確認

select id, JSON_TYPEOF(super_column) from test.super_type_table_case_insensitive ORDER BY 1

===

id,json_typeof
1,string
2,string
3,object
4,object

各項目のデータタイプを確認

  SELECT 
         id
         , JSON_TYPEOF(super_column.id) 
         , JSON_TYPEOF(super_column.nested_values.key1)
         , JSON_TYPEOF(super_column.nested_values.key2)
         , JSON_TYPEOF(super_column.array_values[0])
	     , JSON_TYPEOF(super_column.array_values[1])
         , JSON_TYPEOF(super_column.array_values[2]) 
    FROM test.super_type_table_case_insensitive 
ORDER BY 1

===

id,json_typeof,json_typeof,json_typeof,json_typeof,json_typeof,json_typeof
1,null,null,null,null,null,null
2,null,null,null,null,null,null
3,null,null,null,null,null,null
4,string,string,string,number,number,number

個別の確認結果

登録1（KEY名が大文字）

INSERT INTO test.super_type_table_case_insensitive(id,super_column) VALUES(1, '{"ID":"1","NESTEDS_VALUES":{"KEY1":"1000","KEY2":"2000"},"ARRAY_VALUES":[1,2,3]}')

SELECT * FROM test.super_type_table_case_insensitive WHERE id=1

id,super_column
1,"""{\""ID\"":\""1\"",\""NESTEDS_VALUES\"":{\""KEY1\"":\""1000\"",\""KEY2\"":\""2000\""},\""ARRAY_VALUES\"":[1,2,3]}"""

===

SELECT ID, SUPER_COLUMN.ID FROM test.super_type_table_case_insensitive WHERE ID=1

id,id
1,

登録2（KEY名が小文字）

INSERT INTO test.super_type_table_case_insensitive(id,super_column) VALUES(2, '{"id":"1","nested_values":{"key1":"1000","key2":"2000"},"array_values":[1,2,3]}')

SELECT * FROM test.super_type_table_case_insensitive WHERE id=2

id,super_column
2,"""{\""id\"":\""1\"",\""nested_values\"":{\""key1\"":\""1000\"",\""key2\"":\""2000\""},\""array_values\"":[1,2,3]}"""

===

SELECT ID, super_column.id  FROM test.super_type_table_case_insensitive WHERE id=2

id,id
2,

登録3（JSON_PARSEでKEY名を大文字で登録）

INSERT INTO test.super_type_table_case_insensitive(id,super_column) VALUES(3, JSON_PARSE('{"ID":"1","NESTEDS_VALUES":{"KEY1":"1000","KEY2":"2000"},"ARRAY_VALUES":[1,2,3]}'))

SELECT * FROM test.super_type_table_case_insensitive WHERE id=3

id,super_column
3,"{""ID"":""1"",""NESTEDS_VALUES"":{""KEY1"":""1000"",""KEY2"":""2000""},""ARRAY_VALUES"":[1,2,3]}"

===

-- 小文字で抽出

SELECT id, super_column.id FROM test.super_type_table_case_insensitive WHERE id=3

id,id
3,

===

-- 大文字で抽出

SELECT ID, SUPER_COLUMN.ID FROM test.super_type_table_case_insensitive WHERE id=3

id,id
3,

登録4（JSON_PARSEでKEY名を小文字で登録）

INSERT INTO test.super_type_table_case_insensitive(id,super_column) VALUES(4, JSON_PARSE('{"id":"1","nested_values":{"key1":"1000","key2":"2000"},"array_values":[1,2,3]}'))

SELECT * FROM test.super_type_table_case_insensitive WHERE id=4

id,super_column
4,"{""id"":""1"",""nested_values"":{""key1"":""1000"",""key2"":""2000""},""array_values"":[1,2,3]}"

===


SELECT id, super_column.id FROM test.super_type_table_case_insensitive WHERE id=4

id,id
4,"""1"""

その他

配列とネストされたオブジェクトへのアクセス

SELECT id, super_column.id ,super_column.nested_values.key1, super_column.nested_values.key2, super_column.array_values[0] , super_column.array_values[1], super_column.array_values[2] FROM test.super_type_table_case_insensitive WHERE id=4

===

id,id,key1,key2,array_values,array_values,array_values
4,"""1""","""1000""","""2000""",1,2,3

参考