ROW の作成 CAST を使用した配列内のフィールド名の変更 . 表記を使用した配列のフィルタ処理ネスト値を含む配列のフィルタ処理 UNNEST を使用した配列のフィルタ処理 regexp_like を使用した配列内でのキーワードの検索

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

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

ROW の作成
CAST を使用した配列内のフィールド名の変更
. 表記を使用した配列のフィルタ処理
ネスト値を含む配列のフィルタ処理
UNNEST を使用した配列のフィルタ処理
regexp_like を使用した配列内でのキーワードの検索

`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 値が含まれている配列のフィールド名を変更するには、CAST 宣言を ROW できます。


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

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


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

注記

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

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

次の例では、ドット accountId 表記を使用して、userIdentity ログの AWS CloudTrail 列から . フィールドを選択します。詳細については、「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 | bigdata |  10    |
+----------------+-------------------+

ブラウザで JavaScript が無効になっているか、使用できません。

AWS ドキュメントを使用するには、JavaScript を有効にする必要があります。手順については、使用するブラウザのヘルプページを参照してください。

ドキュメントの表記規則

配列を使用したマップの作成

地理空間データのクエリ

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

ROW の作成

注記

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

注記

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

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

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

regexp_like を使用した配列内でのキーワードの検索

例 1: regexp_like の使用

例 2: regexp_like の使用

`ROW` の作成

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

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

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

`regexp_like` を使用した配列内でのキーワードの検索

例 1: `regexp_like` の使用

例 2: `regexp_like` の使用