Amazon Athena
ユーザーガイド

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

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

ROW の作成

注記

このセクションの例では、使用するサンプルデータの作成手段として ROW を使用します。Athena 内のテーブルに対してクエリを実行する場合は、ROW データ型を作成する必要はありません (データソースから既に作成されています)。CREATE_TABLE を使用すると、Athena は STRUCT を定義し、データを入力して、データセット内の各行で ROW データ型を作成します。基となる ROW データ型は、サポートされている 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 を使用した配列内でのキーワードの検索

次の例では、regexp_like 関数を使用して、配列内の要素内でキーワードのデータセットを検索する方法を示します。入力として評価対象の正規表現パターン、またはパイプ (|) で区切られた要素のリストを使用し、パターンを評価して、指定された文字列にそれが含まれるかどうかを判別します。

正規表現パターンは文字列内に含まれている必要がありますが、文字列と一致している必要はありません。文字列全体と一致させるには、冒頭に ^、末尾に $ を付けてパターンを囲みます (例: '^pattern$')。

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

このデータセットを検索して特定のキーワードを持つサイトを見つけるには、類似した SQL regexp_like 演算子の代わりに LIKE を使用します。多数のキーワードの検索には regexp_like の方が効率的です。

例 1: regexp_like の使用

この例のクエリでは、regexp_like 関数を使用して用語 'politics|bigdata' を検索します。これは配列内の値で見つかります。

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 | +----------------+

例 2: regexp_like の使用

次の例のクエリでは、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

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

+------------------------------------+ | hostname | terms | total | +----------------+-------------------+ | news.cnn.com | politics | 241 | +----------------+-------------------+ | aws.amazon.com | big data | 10 | +----------------+-------------------+