半構造化データのクエリ

Amazon Redshift を使用すると、JSON、Avro、Ion などの半構造化データを構造化データとともにクエリおよび分析できます。半構造化データとは、階層構造またはネスト構造を可能にする柔軟なスキーマを持つデータを指します。以下のセクションでは、Amazon Redshift のオープンデータ形式のサポートを使用して半構造化データをクエリする方法について説明します。これにより、複雑なデータ構造から有益な情報を引き出すことができます。

Amazon Redshift は PartiQL 言語を使用して、リレーショナルデータ、半構造化データ、ネストされたデータへの SQL 互換アクセスを提供します。

PartiQL は、動的型で動作します。このアプローチにより、構造化、半構造化、およびネストされたデータセットの組み合わせに対して、直感的なフィルタリングや結合、および集約を行えるようになります。PartiQL 構文では、ネストされたデータにアクセスするときに、パスナビゲーションにドット付き表記と配列添字を使用します。また、FROM 句の項目が配列を反復処理し、不正なオペレーションに使用できるようにします。以下では、パスおよび配列のナビゲーション、ネスト解除、または結合を、SUPER データ型で行う場合の、さまざまなクエリパターンについて説明します。

次の例で使用されるテーブルの詳細については、「SUPER サンプルデータセット」を参照してください。

ナビゲーション

Amazon Redshift は PartiQL を使用して、それぞれ [...] 角括弧とドット表記を使用して、配列と構造体へのナビゲーションを可能にします。さらに、ドット表記を使用して構造体に、角括弧表記を使用して配列にナビゲーションを混在させることができます。例えば、次の例では、c_orders SUPER データ列が構造体を持つ配列であり、属性の名前が o_orderkey であると仮定しています。

テーブル customer_orders_lineitem にデータを挿入するには、次のコマンドを実行します。IAM ロールを独自の認証情報に置き換えます。

COPY customer_orders_lineitem FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/customer_orders_lineitem'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;

Amazon Redshift では、表記のプレフィックスとしてテーブルエイリアスも使用します。次の例では、前の例と同じクエリを実行します。

SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

フィルタリング、結合、集約など、すべてのタイプのクエリでドットと角括弧の表記を使用できます。この表記は、通常の場合に列参照を含んでいるクエリで使用します。次の例では、結果をフィルタリングする SELECT ステートメントを使用します。

SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0]. o_orderkey IS NOT NULL;

次の例では、GROUP BY 句と ORDER BY 句の両方で角括弧とドットのナビゲーションを使用します。

SELECT c_orders[0].o_orderdate,
       c_orders[0].o_orderstatus,
       count(*)
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderkey IS NOT NULL
GROUP BY c_orders[0].o_orderstatus,
         c_orders[0].o_orderdate
ORDER BY c_orders[0].o_orderdate;

ネストされていないクエリ

クエリをネスト解除するために、Amazon Redshift は PartiQL 構文を使用しながら SUPER 配列を反復処理します。このために、クエリの FROM 句を使用して配列上をナビゲートします。前の例を使用して、次の例では、属性値 c_orders を繰り返し処理しています。

SELECT c.*, o FROM customer_orders_lineitem c, c.c_orders o;

ネスト解除構文は、FROM 句の拡張です。標準 SQL では、FROM 句 x (AS) y は x と関連する各タプルを y が反復処理することを意味します。この場合、x は関連を指し、y はその関連 x のためのエイリアスを指します。同様に、FROM 句項目 x (AS) y を使用してネスト解除する PartiQL 構文では、y が(SUPER) 配列式 x 内の各 (SUPER) 値を反復処理することを意味します。この場合、x は SUPER 表現であり、y は x のエイリアスです。

左のオペランドは、通常のナビゲーションのためにドットと角括弧の表記を使用することもできます。前の例では、customer_orders_lineitem c は customer_order_lineitem ベースのテーブルに対する反復処理であり、c.c_orders o は c.c_orders 配列に対する反復処理です。配列内の配列である o_lineitems 属性を反復処理するには、複数の句を追加する必要があります。

SELECT c.*, o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

Amazon Redshift では、AT キーワードを使用して配列の反復処理を行う際の、配列インデックスもサポートしています。句 x AS y AT z は、配列 x を反復処理し、配列インデックスとしてフィールド z, を生成します。次の例は、配列インデックスがどのように機能するかを示しています。

SELECT c_name,
       orders.o_orderkey AS orderkey,
       index AS orderkey_index
FROM customer_orders_lineitem c, c.c_orders AS orders AT index 
ORDER BY orderkey_index;

c_name             | orderkey | orderkey_index
-------------------+----------+----------------
Customer#000008251 | 3020007  |        0
Customer#000009452 | 4043971  |        0
  (2 rows)

次の例では、スカラー配列の繰り返し処理を行います。

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index;

 index | element
-------+----------
     0 | 1
     1 | 2.3
     2 | 45000000
(3 rows)

次の例では、複数のレベルの配列を繰り返し処理します。この例では、複数の UNNEST 句を使用して、最も内側の配列を反復処理します。f.multi_level_array AS 配列は multi_level_array を反復処理します。配列 AS 要素は、multi_level_array 内の配列に対する反復処理を表します。

CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array;

SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element;

   array   | element
-----------+---------
 [1.1,1.2] | 1.1
 [1.1,1.2] | 1.2
 [2.1,2.2] | 2.1
 [2.1,2.2] | 2.2
 [3.1,3.2] | 3.1
 [3.1,3.2] | 3.2
(6 rows)

FROM 句の詳細については、「FROM 句」を参照してください。

オブジェクトのピボット解除

オブジェクトのピボット解除を実行する際、Amazon Redshift は、PartiQL 構文により SUPER オブジェクトを反復処理します。この処理は、UNPIVOT キーワードを指定しながらクエリの FROM 句を使用することで実現されます。この場合、式は c.c_orders[0] オブジェクトです。このクエリ例では、オブジェクトから返された各属性を反復処理します。

SELECT attr as attribute_name, json_typeof(val) as value_type 
FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr 
WHERE c_custkey = 9451;

 attribute_name  | value_type
-----------------+------------
 o_orderstatus   | string
 o_clerk         | string
 o_lineitems     | array
 o_orderdate     | string
 o_shippriority  | number
 o_totalprice    | number
 o_orderkey      | number
 o_comment       | string
 o_orderpriority | string
(9 rows)

ネスト解除構文と同様に、ピボット解除構文も FROM 句の拡張の 1 つです。違いは、ピボット解除構文では UNPIVOT キーワードを使用して、配列ではなくオブジェクトを反復処理することを示す点です。これは、AS value_alias を使用してオブジェクト内のすべての値を反復処理し、AT attribute_alias を使用してすべての属性を反復処理します。次の構文フラグメントを検討してください。

UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift は、次のように、単一の FROM 句でオブジェクトのピボット解除と配列のネスト解除の使用をサポートしています。

SELECT attr as attribute_name, val as object_value
FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr 
WHERE c_custkey = 9451;

オブジェクトのピボット解除を使用する場合、Amazon Redshift では相関ピボット解除はサポートされません。具体的には、異なるクエリレベルで複数のピボット解除が存在し、内側のピボット解除が外側のピボット解除を参照している場合が考えられます。Amazon Redshift は、このタイプの複数にわたるピボット解除をサポートしていません。

FROM 句の詳細については、「FROM 句」を参照してください。PIVOT および UNPIVOT を使用して構造化データをクエリする方法の例については、「PIVOT と UNPIVOT の例」を参照してください。

動的型付け

動的型付けでは、ドットと角括弧のパスから抽出されたデータを明示的にキャストする必要はありません。Amazon Redshift では、動的型付けを使用して、クエリで使用する前にデータ型を宣言することなく、スキーマレスの SUPER データを処理します。動的型付けでは、明示的に Amazon Redshift 型にキャストすることなく、SUPER データ列に移動した結果が使用されます。動的型付けは、結合および GROUP BY 句で最も便利です。次の例では、通常の Amazon Redshift 型にドット式と角括弧式を明示的にキャストする必要がない SELECT ステートメントを使用しています。タイプの互換性および変換の詳細については、「型の互換性と変換」を参照してください。

SELECT c_orders[0].o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus = 'P';

c_orders[0].o_orderstatus が文字列「P」の場合、このクエリの等号は true と評価されます。他のすべての場合、等号は false と評価されます。これには、等号の引数が異なるタイプである場合も含まれます。

動的型と静的型

動的型付けを使用しないと、c_orders[0].o_orderstatus が文字列、整数、または構造体のいずれであるかを判断できません。c_orders[0].o_orderstatus が SUPER データ型であると判断できるのは、Amazon Redshift スカラー、配列、または構造体の場合のみです。c_orders[0].o_orderstatus の静的型は、SUPER データ型です。従来、型は SQL では暗黙的に静的型です。

Amazon Redshift は、スキーマレスデータの処理に動的型付けを使用します。クエリがデータを評価すると、c_orders[0].o_orderstatus は特定のタイプであることが判明します。例えば、customer_orders_lineitem の最初のレコードで c_orders[0].o_orderstatus を評価すると、整数になることがあります。2 番目のレコードを評価すると、文字列になることがあります。これらは、式の動的型です。

動的型を持つドット式と角括弧式で SQL 演算子または関数を使用すると、Amazon Redshift は、それぞれの静的型で標準の SQL 演算子または関数を使用する場合と同様の結果を生成します。この例では、パス式の動的型が文字列である場合、文字列 'P' との比較は意味を持ちます。c_orders[0].o_orderstatus の動的型が文字列以外のデータ型であるときはいつでも、等式が false を返します。誤って入力された引数が使用されると、他の関数は null を返します。

次の例では、静的型付けを使用して前のクエリを書き込みます。

SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR = 'P'
           ELSE FALSE END;

等価述語と比較述語の次の違いに注意してください。前の例では、等価述語を下回る述語に置き換えると、セマンティクスは false ではなく null を生成します。

SELECT c_orders[0]. o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus <= 'P';

この例では、c_orders[0].o_orderstatus が文字列の場合、アルファベット順で「P」以下であれば、Amazon Redshift は true を返します。アルファベット順で 'P' より大きい場合、Amazon Redshift は false を返します。ただし、c_orders[0].o_orderstatus が文字列でない場合、Amazon Redshift では次のクエリに示すように、異なるタイプの値を比較できないため、Amazon Redshift は null を返します。

SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR <= 'P'
           ELSE NULL END;

動的型付けは、最小限の比較を行える型の比較から除外されません。例えば、CHAR と VARCHAR の両方の Amazon Redshift スカラー型を SUPER に変換できます。これらは、Amazon Redshift CHAR および VARCHAR 型に似た末尾の空白文字を無視するなど、文字列と同等です。同様に、整数型、小数型、および浮動小数点値は SUPER 値と同等です。特に 10 進数列の場合、各値は異なるスケールを持つこともできます。Amazon Redshift では、これらを引き続き動的型と見なします。

また、Amazon Redshift は、オブジェクトや配列の深さを評価し、すべての属性を比較するなど、深さが等しいと評価されるオブジェクトや配列の等価性もサポートしています。deep equal を実行するプロセスは時間がかかる可能性があるため、deep equal の使用には注意が必要です。

結合での動的型付けの使用

結合の場合、動的型付けは、長い CASE WHEN 分析を実行して表示される可能性のあるデータ型を見つける代わりに、値をさまざまな動的型と自動的に一致させます。例えば、組織がパートキーに使用していた形式を時間の経過とともに変更したと仮定します。

発行された最初の整数部分キーは、'A55' などの文字列部分キーに置き換えられ、後で文字列と数字を組み合わせた ['X', 10] などの配列部分キーに再び置き換えられます。Amazon Redshift では、パートキーについて長いケース分析を行う必要がなく、次の例に示すように、結合を使用できます。

SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE l.l_partkey = ps.ps_partkey
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

次の例では、動的型付けを使用しない場合、同じクエリがどれほど複雑で非効率的であるかを示しています。

SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey)
           THEN l.l_partkey::integer = ps.ps_partkey::integer
           WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey)
           THEN l.l_partkey::varchar = ps.ps_partkey::varchar
           WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey)
                AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0])
                AND IS_INTEGER(l.l_partkey[1]) AND IS_INTEGER(ps.ps_partkey[1])
           THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar
                AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer
           ELSE FALSE END
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

Lax のセマンティクス

デフォルトでは、SUPER 値に対するナビゲーションオペレーションは、ナビゲーションが無効であるときにエラーを返す代わりに null を返します。SUPER 値がオブジェクトでない場合、または SUPER 値がオブジェクトであるが、クエリで使用される属性名が含まれていない場合、オブジェクトのナビゲーションは無効です。例えば、次のクエリは、SUPER データ列の cdata で無効な属性名にアクセスします。

SELECT c.c_orders.something FROM customer_orders_lineitem c;

SUPER 値が配列でない場合、または配列インデックスが範囲外の場合、配列ナビゲーションは null を返します。次のクエリは、c_orders[1][1] が範囲外であるため、null を返します。

SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

Lax セマンティクスは、動的型付けを使用して SUPER 値をキャストする場合に特に便利です。SUPER 値を間違ったタイプにキャストすると、キャストが無効な場合、エラーではなく null が返されます。例えば、次のクエリは、オブジェクト属性 o_orderstatus の文字列値 'Good' を INTEGER にキャストできないため、null を返します。Amazon Redshift は、VARCHAR から INTEGER へのキャストではエラーを返しますが、SUPER キャストではエラーを返しません。

SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

内観の種類

SUPER データ列は、SUPER 値に関する動的型およびその他の型情報を返す検査関数をサポートします。最も一般的な例は、SUPER 値の動的型に応じて、ブール値、数値、文字列、オブジェクト、配列、または null の値を持つ VARCHAR を返す JSON_TYPEOF スカラー関数です。Amazon Redshift は、SUPER データ列に対して次のブール関数をサポートしています。

入力値が null の場合、これらの関数はすべて false を返します。IS_SCALAR、IS_OBJECT、および IS_ARRAY は相互に排他的であり、null を除くすべての可能な値をカバーします。

データに対応するタイプを推測するために、Amazon Redshift は次の例に示すように、SUPER 値のタイプ (のトップレベル) を返す JSON_TYPEOF 関数を使用します。

SELECT JSON_TYPEOF(r_nations) FROM region_nations;
 json_typeof
 -------------
 array
(1 row)

SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations;
 json_typeof
 -------------
 number

Amazon Redshift では、この値を SUPER ではなく VARCHAR 列に挿入するのと同様に、単一の長い文字列として認識します。列が SUPER であるため、単一の文字列は引き続き有効な SUPER 値であり、その差は JSON_TYPEOF に記載されています。

SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations;
 is_varchar
-------------
 true
(1 row)

SELECT r_nations[4].n_name FROM region_nations
WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) 
           THEN r_nations[4].n_nationkey::INTEGER = 15 
           ELSE false END;

順

Amazon Redshift では、動的型が異なる値間の SUPER 比較を定義していません。文字列である SUPER 値は、数値である SUPER 値より小さくも大きくもありません。SUPER 列で ORDER BY 句を使用するために、Amazon Redshift では、ORDER BY 句を使用して SUPER 値をランク付けするときに観察されるさまざまなタイプ間の全順序を定義します。動的型間の順序は、ブール値、数値、文字列、配列、オブジェクトです。次の例では、さまざまなタイプの順序を示しています。

INSERT INTO region_nations VALUES 
(100,'name1','comment1','AWS'), 
(200,'name2','comment2',1),
(300,'name3','comment3',ARRAY(1, 'abc', null)),
(400,'name4','comment4',-2.5),
(500,'name5','comment5','Amazon');

SELECT r_nations FROM region_nations order by r_nations;

r_nations
----------------
 -2.5
 1
 "Amazon"
 "AWS"
 [1,"abc",null]
(5 rows)

ORDER BY 句の詳細については、「ORDER BY 句」を参照してください。