メニュー
Amazon Athena
ユーザーガイド

複合型の配列とネストされた構造のクエリ

ソースデータには、複雑なデータ型とネスト構造を持つ配列が含まれている場合があります。このセクションの例では、Athena のクエリを使用して、要素のデータ型の変更、配列内の要素の検索、値の順序付け、およびキーワードの検索を行う方法を示します。

ROW の作成

注記

このセクションの例では、使用するサンプルデータの作成手段として ROW を使用します。Athena 内のテーブルに対してクエリを実行する場合は、ROW データ型を作成する必要はありません (Presto でデータソースから既に作成されています)。CREATE_TABLE を使用すると、Athena はSTRUCT を定義し、Presto を通じてデータを設定します。逆に、Presto はユーザーに代わり、データセットの行ごとに ROW データ型を作成します。基となる ROW データ型は、Presto でサポートされている SQL データ型の名前付きフィールドで構成されます。

WITH dataset AS ( SELECT ROW('Bob', 38) AS users ) SELECT * FROM dataset

このクエリは以下を返します。

+-------------------------+ | users | +-------------------------+ | {field0=Bob, field1=38} | +-------------------------+

CAST を使用した配列内のフィールド名の変更

ROW 値が含まれている配列のフィールド名を変更するには、ROW 宣言を CAST できます。

WITH dataset AS ( SELECT CAST( ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER) ) AS users ) SELECT * FROM dataset

このクエリは以下を返します。

+--------------------+ | users | +--------------------+ | {NAME=Bob, AGE=38} | +--------------------+

注記

上の例では、nameVARCHAR として宣言しています。Presto での型に合わせるためです。この STRUCTCREATE TABLE ステートメント内で宣言する場合は、String 型を使用します。Hive では、このデータ型を String として定義するためです。

. 表記を使用した配列のフィルタ処理

次の例では、ドット . 表記を使用して、AWS CloudTrail ログの userIdentity 列から accountId フィールドを選択します。詳細については、「AWS CloudTrail ログのクエリ」を参照してください。

SELECT CAST(useridentity.accountid AS bigint) as newid FROM cloudtrail_logs LIMIT 2;

このクエリは以下を返します。

+--------------+ | newid | +--------------+ | 112233445566 | +--------------+ | 998877665544 | +--------------+

値の配列にクエリを実行するには、次のクエリを発行します。

WITH dataset AS ( SELECT ARRAY[ CAST(ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Alice', 35) AS ROW(name VARCHAR, age INTEGER)), CAST(ROW('Jane', 27) AS ROW(name VARCHAR, age INTEGER)) ] AS users ) SELECT * FROM dataset

次の結果が返されます。

+-----------------------------------------------------------------+ | users | +-----------------------------------------------------------------+ | [{NAME=Bob, AGE=38}, {NAME=Alice, AGE=35}, {NAME=Jane, AGE=27}] | +-----------------------------------------------------------------+

ネスト値を含む配列のフィルタ処理

大きな配列にはネスト構造が含まれている場合があり、ネスト構造内の値をフィルタ処理または検索する必要があります。

ネストされた BOOLEAN 値を含む値配列のデータセットを定義するには、次のクエリを発行します。

WITH dataset AS ( SELECT CAST( ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ) AS sites ) SELECT * FROM dataset

次の結果が返されます。

+----------------------------------------------------------+ | sites | +----------------------------------------------------------+ | {HOSTNAME=aws.amazon.com, FLAGGEDACTIVITY={ISNEW=true}} | +----------------------------------------------------------+

次に、この要素の BOOLEAN 値のフィルタ処理およびアクセスには、引き続きドット . 表記を使用します。

WITH dataset AS ( SELECT CAST( ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ) AS sites ) SELECT sites.hostname, sites.flaggedactivity.isnew FROM dataset

このクエリは、ネストされたフィールドを選択し、次の結果を返します。

+------------------------+ | hostname | isnew | +------------------------+ | aws.amazon.com | true | +------------------------+

UNNEST を使用した配列のフィルタ処理

ネスト構造を含む配列を、そのいずれかの子要素でフィルタ処理するには、UNNEST 演算子を使用してクエリを発行します。UNNEST の詳細については、「ネスト配列のフラット化」を参照してください。

たとえば、次のクエリでは、データセットのサイトのホスト名を見つけます。

WITH dataset AS ( SELECT ARRAY[ CAST( ROW('aws.amazon.com', ROW(true)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ), CAST( ROW('news.cnn.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ), CAST( ROW('netflix.com', ROW(false)) AS ROW(hostname VARCHAR, flaggedActivity ROW(isNew BOOLEAN)) ) ] as items ) SELECT sites.hostname, sites.flaggedActivity.isNew FROM dataset, UNNEST(items) t(sites) WHERE sites.flaggedActivity.isNew = true

返される結果は次のとおりです。

+------------------------+ | hostname | isnew | +------------------------+ | aws.amazon.com | true | +------------------------+

配列内でのキーワードの検索

データセットを検索して配列の要素内からキーワードを見つけるには、regexp_like 関数を使用します。

ホスト名と flaggedActivity 要素が含まれているサイトの配列について考えます。この要素内の ARRAY には、複数の MAP 要素が含まれており、要素ごとに異なる一般的なキーワードと人気度カウントを示しています。この配列の MAP 内で特定のキーワードを検索するとします。

このデータセットを検索して特定のキーワードを持つサイトを見つけるには、regexp_like 関数を使用します。

注記

このクエリでは、SQL の LIKE 演算子を使用せずに、regexp_like 関数を使用します。この関数では、評価対象の正規表現パターン、または | (OR 演算子と呼ばれる) で区切られた用語のリストを、入力として使用します。さらに、多数のキーワードの検索には regexp_like 関数を使用するほうが効率的です。この関数のパフォーマンスは SQL の LIKE 演算子のパフォーマンスを上回ります。

WITH dataset AS ( SELECT ARRAY[ CAST( ROW('aws.amazon.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']), MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']), MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']), MAP(ARRAY['term', 'count'], ARRAY['iot', '74']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ), CAST( ROW('news.cnn.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['politics', '241']), MAP(ARRAY['term', 'count'], ARRAY['technology', '211']), MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']), MAP(ARRAY['term', 'count'], ARRAY['iot', '170']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ), CAST( ROW('netflix.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']), MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']), MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']), MAP(ARRAY['term', 'count'], ARRAY['iot', '4']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ) ] AS items ), sites AS ( SELECT sites.hostname, sites.flaggedactivity FROM dataset, UNNEST(items) t(sites) ) SELECT hostname FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags) WHERE regexp_like(flags['term'], 'politics|bigdata') GROUP BY (hostname)

このクエリは 2 つのサイトを返します。

+----------------+ | hostname | +----------------+ | aws.amazon.com | +----------------+ | news.cnn.com | +----------------+

配列内の値の順序付け

配列内の値を順序付けるには、ORDER BY を使用します。次の例のクエリでは、regexp_like 関数を使用して検索語句に一致するサイトの人気度スコアを合計し、最高から最低の順に並べます。

WITH dataset AS ( SELECT ARRAY[ CAST( ROW('aws.amazon.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['bigdata', '10']), MAP(ARRAY['term', 'count'], ARRAY['serverless', '50']), MAP(ARRAY['term', 'count'], ARRAY['analytics', '82']), MAP(ARRAY['term', 'count'], ARRAY['iot', '74']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ), CAST( ROW('news.cnn.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['politics', '241']), MAP(ARRAY['term', 'count'], ARRAY['technology', '211']), MAP(ARRAY['term', 'count'], ARRAY['serverless', '25']), MAP(ARRAY['term', 'count'], ARRAY['iot', '170']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ), CAST( ROW('netflix.com', ROW(ARRAY[ MAP(ARRAY['term', 'count'], ARRAY['cartoons', '1020']), MAP(ARRAY['term', 'count'], ARRAY['house of cards', '112042']), MAP(ARRAY['term', 'count'], ARRAY['orange is the new black', '342']), MAP(ARRAY['term', 'count'], ARRAY['iot', '4']) ]) ) AS ROW(hostname VARCHAR, flaggedActivity ROW(flags ARRAY(MAP(VARCHAR, VARCHAR)) )) ) ] AS items ), sites AS ( SELECT sites.hostname, sites.flaggedactivity FROM dataset, UNNEST(items) t(sites) ) SELECT hostname, array_agg(flags['term']) AS terms, SUM(CAST(flags['count'] AS INTEGER)) AS total FROM sites, UNNEST(sites.flaggedActivity.flags) t(flags) WHERE regexp_like(flags['term'], 'politics|bigdata') GROUP BY (hostname) ORDER BY total DESC

このクエリは次の結果を返します。

+------------------------------------------------+ | hostname | terms | total | +------------------------------------------------+ | news.cnn.com | [politics,serverless] | 241 | +------------------------------------------------+ | aws.amazon.com | [serverless] | 10 | +------------------------------------------------+