SELECT - Amazon Athena
概要パラメータAmazon S3 内にあるソースデータのファイルの場所の取得 一重引用符のエスケープ追加リソース

SELECT

0 個以上のテーブルからデータの行を取得します。

注記

このトピックでは、参照用に概要情報を提供します。SELECT と SQL 言語の使用に関する包括的な情報は、このドキュメントでは説明しません。Athena に固有の SQL の使用については、「Amazon Athena での SQL クエリに関する考慮事項と制約事項」および「Amazon Athena で SQL クエリを実行する」を参照してください。データベースの作成、テーブルの作成、および Athena のテーブルに対する SELECT クエリの実行の例については、使用を開始する を参照してください。

概要

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT [ count | ALL ] ]
注記

SQL SELECT ステートメントの予約語は、二重引用符で囲む必要があります。詳細については、「SQL SELECT ステートメントでエスケープする予約語」を参照してください。

パラメータ

[ WITH with_query [, ....]]

WITH を使用すると、ネストされたクエリをフラット化したり、サブクエリを簡素化できます。

WITH 句を使用した再帰クエリの作成は、Athena エンジンバージョン 3 以降でサポートされています。最大再帰深度は 10 です。

WITH 句はクエリの SELECT リストに先行し、SELECT クエリ内で使用する 1 つ以上のサブクエリを定義します。

各サブクエリは、ビュー定義と同じように、一時テーブルを定義します。一時テーブルは FROM 句で参照できます。このテーブルはクエリを実行時のみに使用します。

with_query 構文は次のとおりです。

subquery_table_name [ ( column_name [, ...] ) ] AS (subquery)

各パラメータの意味は次のとおりです。

  • subquery_table_name は、WITH 句のサブクエリの結果を定義する一時テーブルの一意な名前です。各 subquery には、FROM 句で参照可能なテーブル名を付ける必要があります。

  • column_name [, ...] は、出力列名の省略可能なリストです。列名の数は、subquery で定義した列数以下でなければなりません。

  • subquery は、任意のクエリステートメントです。

[ ALL | DISTINCT ] select_expression

select_expression は、選択する行を決定します。select_expression には、次のいずれかの形式が使用されます。

expression [ [ AS ] column_alias ] [, ...]
row_expression.* [ AS ( column_alias [, ...] ) ]
relation.*
*

  • expression [ [ AS ] column_alias ] 構文は出力列を指定します。オプションの [AS] column_alias 構文は、出力の列に使用されるカスタム見出し名を指定します。

  • row_expression.* [ AS ( column_alias [, ...] ) ] の場合、row_expression は データ型 ROW の任意の式です。行のフィールドは、結果に含まれる出力列を定義します。

  • relation.* の場合、relation の列が結果に含まれます。この構文では列のエイリアスは使用できません。

  • アスタリスク * は、すべての列を結果セットに含めることを指定します。

  • 結果セット内の列の順序は、select 式による指定の順序と同じです。select 式が複数の列を返す場合、列の順序はソースリレーションまたは行タイプの式で使用されている順序に従います。

  • 列のエイリアスを指定すると、そのエイリアスは既存の列または行のフィールド名よりも優先されます。select 式に列名がない場合は、インデックスが 0 の匿名の列名 (_col0、_col1、_col2, ...) が出力に表示されます。

  • ALL はデフォルトです。ALL は、それを省略した場合と同じように扱われます。すべての列のすべての行が選択され、重複も含まれます。

  • DISTINCT は、列に重複する値が含まれているときに、個別の値のみを返すために使用します。

FROM from_item [, ...]

クエリへの入力を示します。from_item は、以下に示すように、ビュー、結合コンストラクト、サブクエリのいずれかです。

from_item は以下のいずれかです。

  • table_name [ [ AS ] alias [ (column_alias [, ...]) ] ]

    table_name は行の選択元であるターゲットテーブルの名前であり、alias は SELECT ステートメントの出力に渡す名前です。column_alias は指定した alias の列を定義します。

-または-

  • join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

    join_type は以下のいずれかです。

    • [ INNER ] JOIN

    • LEFT [ OUTER ] JOIN

    • RIGHT [ OUTER ] JOIN

    • FULL [ OUTER ] JOIN

    • CROSS JOIN

    • ON join_condition | USING (join_column [, ...])。join_condition では、複数のテーブルにおいて結合キーの列名を指定できます。join_column を使用するには、join_column が両方のテーブルに存在している必要があります。

[ WHERE condition ]

指定した condition に従って結果をフィルタリングします。通常、condition には次の構文が含まれています。

column_name operator value [[[AND | OR] column_name operator value] ...]

演算子は、比較演算子 =、>、<、>=、<=、<>、!= のいずれかになります。

次のサブクエリ式も、WHERE 句で使用できます。

  • [NOT] BETWEEN integer_A AND integer_B - 次の例のように、2 つの整数間の範囲を指定します。列のデータ型が varchar の場合、最初に列を整数にキャストする必要があります。

    SELECT DISTINCT processid FROM "webdata"."impressions"
WHERE cast(processid as int) BETWEEN 1500 and 1800
ORDER BY processid

  • [NOT] LIKE value - 指定したパターンを検索します。次の例のように、パーセント記号 (%) をワイルドカード文字として使用します。

    SELECT * FROM "webdata"."impressions"
WHERE referrer LIKE '%.org'

  • [NOT] IN (value[, value[, ...]) - 次の例のように、列で使用できる値のリストを指定します。

    SELECT * FROM "webdata"."impressions"
WHERE referrer IN ('example.com','example.net','example.org')
[ GROUP BY [ ALL | DISTINCT ] grouping_expressions [, ...]]

SELECT ステートメントの出力を、一致する値を持つ行に分割します。

ALL と DISTINCT は、重複したグループ化セットごとに個別の出力行を生成するかどうかを決定します。省略すると、ALL が使用されます。

grouping_expressions では、複雑なグループ化オペレーションを実行できます。複雑なグループ化オペレーションを使用して、複数の列セットの集計を必要とする分析を単一のクエリで実行できます。

grouping_expressions 要素には、SUM、AVG、COUNT など、入力列に対して実行される任意の関数を指定できます。

GROUP BY 式は、SELECT ステートメントの出力に表示されない入力列名で出力をグループ化できます。

すべての出力式は、集計関数であるか、GROUP BY 句に存在する列であることが必要です。

単一のクエリを使用して、複数の列セットの集計を必要とする分析を実行できます。

Athena は、GROUPING SETS、CUBE、および ROLLUP を使用する複雑な集計をサポートしています。GROUP BY GROUPING SETS で、グループ化する列の複数のリストを指定します。GROUP BY CUBE で、特定の列のセットに対して、すべての可能なグループ化セットを生成します。GROUP BY ROLLUP で、特定の列のセットに対して、すべての可能な小計を生成します。複雑なグループ化オペレーションでは、入力列で構成される式でのグループ化がサポートされていません。列名のみが許可されます。

通常、UNION ALL を使用しても、これらの GROUP BY オペレーションと同じ結果を達成できます。ただし、GROUP BY を使用するクエリでは、データの読み取りが 1 回で済むという利点があります。UNION ALL は基となるデータを 3 回読み取るため、データソースが変わりやすい場合は、不整合な結果が生成されることがあります。

[ HAVING condition ]

集計関数と GROUP BY 句で使用します。どのグループを選択するかを制御します。condition を満たさないグループは排除されます。このフィルタ処理は、グループや集計の計算後に行われます。

[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] union_query] ]

UNION、INTERSECT、および EXCEPT は、複数の SELECT ステートメントの結果を 1 つのクエリに結合します。ALL または DISTINCT は、最終的な結果セットに含められる行の一意性を制御します。

UNION は、最初のクエリから得られた行と、2 番目のクエリから得られた行を結合します。重複を排除するため、UNION はメモリを消費するハッシュテーブルを構築します。パフォーマンス向上のため、クエリに重複を排除する必要がない場合は UNION ALL の使用を検討してください。複数の UNION 句は左から右に処理されます。ただし、括弧を使用して処理の順序を明示的に定義することもできます。

INTERSECT は、最初のクエリと 2 番目のクエリ両方の結果に存在する行のみを返します。

EXCEPT は、最初のクエリの結果からの行を返し、2 番目のクエリで見つかった行は排除されます。

ALL は、行が同一の場合でも、すべての行が含まれる処理を実行します。

DISTINCT は、統合された結果セットに一意の行のみが含まれるようにします。

[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...]]

結果セットを 1 つ以上の出力 expression でソートします。

句に複数の式が含まれている場合、結果セットは最初の expression に従ってソートされます。次に、最初の式で一致した値がある行に 2 番目の expression が適用されます。以下、同様です。

各 expression では、SELECT の出力列を指定するか、出力列の位置を 1 から始まる序数で指定できます。

ORDER BY は、GROUP BY または HAVING 句の後で、最後のステップとして評価されます。ASC と DESC は、結果のソートを昇順にするか、降順にするかを決定します。

デフォルトの null 順序は、昇順または降順のソート順に関係なしに NULLS LAST です。

[ OFFSET count [ ROW | ROWS ] ]

OFFSET 句を使用して、結果セットの先頭の行をいくつか破棄します。ORDER BY 句が存在する場合、OFFSET 句はソートされた結果セットに対して評価されます。スキップされた行が破棄された後もセットはソートされたままになります。クエリに ORDER BY 句がない場合、破棄される行は任意です。OFFSET で指定したカウントが結果セットのサイズに等しいかそれを超える場合、最終結果は空になります。

LIMIT [ count | ALL ]

結果セットの行数を count に制限します。LIMIT ALL は LIMIT 句を省略した場合と同じです。クエリに ORDER BY 句がない場合は、任意の結果になります。

TABLESAMPLE [BERNOULLI | SYSTEM] (percentage)

サンプリング方法に基づいてテーブルから行を選択する演算子 (オプション) です。

BERNOULLI は、percentage の確率でテーブルサンプルに存在する各行を選択します。テーブルのすべての物理ブロックがスキャンされ、サンプルの percentage とランタイムに計算されるランダム値の比較に基づいて、特定の行がスキップされます。

SYSTEM では、テーブルがデータの論理セグメントに分割され、この詳細度でテーブルがサンプリングされます。

特定のセグメントのすべての行が選択されるか、サンプルの percentage とランタイムに計算されたランダム値の比較に基づいて当該セグメントがスキップされます。SYSTEM サンプリングはコネクタに依存します。この方法では、独立したサンプリング確率は保証されません。

[ UNNEST (array_or_map) [WITH ORDINALITY] ]

配列またはマップをリレーションに展開します。配列は単一の列に展開されます。マップは 2 つの列 (キー、値) に展開されます。

UNNEST に複数の引数を使用できます。これらの引数は、複数の列に展開され、各列の行数は最大の基数引数と同じになります。

その他の列には NULL が埋め込まれます。

WITH ORDINALITY 句は、序数列を末尾に追加します。

通常、UNNEST は JOIN と一緒に使用し、JOIN の左側のリレーションの列を参照できます。

Amazon S3 内にあるソースデータのファイルの場所の取得

テーブル行にあるデータの Amazon S3 ファイルの場所を確認するには、以下の例にあるように、SELECT クエリで "$path" を使用できます。

SELECT "$path" FROM "my_database"."my_table" WHERE year=2019;

このクエリは以下のような結果を返します。

s3://amzn-s3-demo-bucket/datasets_mytable/year=2019/data_file1.json

テーブル内にあるデータについて S3 ファイル名パスのソートされた一意のリストを返すには、以下の例にあるように、SELECT DISTINCT と ORDER BY を使用できます。

SELECT DISTINCT "$path" AS data_source_file
FROM sampledb.elb_logs
ORDER By data_source_file ASC

パスなしでファイル名のみを返すには、以下の例にあるように、"$path" をパラメータとして regexp_extract 関数に渡すことができます。

SELECT DISTINCT regexp_extract("$path", '[^/]+$') AS data_source_file
FROM sampledb.elb_logs
ORDER By data_source_file ASC

特定のファイルからのデータを返すには、以下の例にあるように、WHERE 句でそのファイルを指定します。

SELECT *,"$path" FROM my_database.my_table WHERE "$path" = 's3://amzn-s3-demo-bucket/my_table/my_partition/file-01.csv'

詳細と例については、ナレッジセンターの記事、「Athena テーブルの行の Amazon S3 ソースファイルを表示する方法を教えてください」を参照してください。

注記

Athena では、Hive または Iceberg の非表示のメタデータ列 $bucket、$file_modified_time、$file_size、および $partition は、ビューでサポートされていません。

一重引用符のエスケープ

一重引用符をエスケープするには、以下の例のように、その前に別の一重引用符を付けます。これを二重引用符と混同しないでください。

Select 'O''Reilly'
結果

O'Reilly

追加リソース

Athena で SELECT ステートメントを使用する方法の詳細については、以下のリソースを参照してください。

詳細情報の内容 参照先
Athena でクエリを実行する Amazon Athena で SQL クエリを実行する
SELECT を使用してテーブルを作成する クエリ結果からテーブルを作成する (CTAS)
SELECT クエリからのデータを別のテーブルに挿入する INSERT INTO
SELECT ステートメントで組み込みの関数を使用する Amazon Athena の関数
SELECT ステートメントでユーザー定義の関数を使用する ユーザー定義関数を使用してクエリする
データカタログのメタデータをクエリする AWS Glue Data Catalog をクエリする