Amazon Redshift は、2025 年 11 月 1 日以降、新しい Python UDF の作成をサポートしなくなります。Python UDF を使用する場合は、その日付より前に UDF を作成してください。既存の Python UDF は引き続き通常どおり機能します。詳細については、ブログ記事を参照してください。

半構造化データのクエリ

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

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

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

ナビゲーション

Amazon Redshift は PartiQL を使用して、それぞれ [...] 角括弧とドット表記を使用して、配列と構造体へのナビゲーションを可能にします。さらに、ドット表記を使用して構造体に、角括弧表記を使用して配列にナビゲーションを混在させることができます。例えば、次のステートメントでは、SUPER オブジェクトの 1 レベル深くネストされた配列の 3 番目の要素のみを選択します。

SELECT super_object.array[2];
         
 array
---------------
 third_element

フィルタリング、結合、集計などのデータ操作を実行するときは、ドットと角括弧の表記を使用できます。これらの表記は、通常列参照を含んでいるクエリ内の任意の場所で使用できます。例えば、次のステートメントでは、タイプ UPDATED のイベントの数を選択します。

SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0]."eventType" = 'UPDATED';
         
 eventType | count
-----------+-------
 "UPDATED" | 1    

PartiQL ナビゲーションの詳細な例については、「Amazon Redshift で半構造化データを使用する例」を参照してください。

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

クエリのネストを解除するために、Amazon Redshift には、SUPER 配列を反復処理する 2 つの方法があります。FROM 句の PartiQL 構文と UNNEST オペレーションです。どちらのネスト解除メソッドも同じ出力になります。UNNEST オペレーションについては、「FROM 句」を参照してください。UNNEST オペレーションの使用例については、「UNNEST の例」を参照してください。

Amazon Redshift は、クエリの FROM 句で PartiQL 構文を使用して SUPER 配列をナビゲートできます。前の例を使用して、次の例では、属性値 c_orders を繰り返し処理しています。

SELECT orders.*, o FROM customer_orders orders, orders.c_orders o;

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 element, index 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;

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

FROM 句の詳細については、「FROM 句」を参照してください。SUPER クエリをネスト解除するその他の例については、「Amazon Redshift で半構造化データを使用する例」を参照してください。

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

オブジェクトのピボット解除を実行する際、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 句」を参照してください。SUPER タイプでピボットを使用する例については、「Amazon Redshift で半構造化データを使用する例」を参照してください。

動的型付け

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

注文のステータスが shipped である行を検索する次の例を考えてみます。

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

c_orders[0].o_orderstatus の値が文字列「shipped」の場合、この例のクエリの等号は 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;

順

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

SUPER クエリで GROUP BY と ORDER BY を使用する例については、「半構造化データのフィルタリング」を参照してください。