CTAS クエリの例 - Amazon Athena

CTAS クエリの例

以下の例を使用して CTAS クエリを作成します。CTAS 構文の詳細については、「CREATE TABLE AS」を参照してください。

このセクションの内容:

例: すべての列を選択してテーブルを複製する

次の例では、テーブルからすべての列をコピーしてテーブルを作成します。

CREATE TABLE new_table AS 
SELECT * 
FROM old_table;

同じ例の次のバリエーションで、SELECT ステートメントには WHERE 句も含まれています。この場合、クエリはテーブルから、WHERE 句を満たす行のみを選択します。

CREATE TABLE new_table AS 
SELECT * 
FROM old_table 
WHERE condition;
例: 1 つ、または複数のテーブルから特定の列を選択する

次の例では、別のテーブルからの列のセットで実行される新しいクエリが作成されます。

CREATE TABLE new_table AS 
SELECT column_1, column_2, ... column_n 
FROM old_table;

同じ例のこのバリエーションで、複数のテーブルの特定の列から新しいテーブルを作成します。

CREATE TABLE new_table AS
SELECT column_1, column_2, ... column_n 
FROM old_table_1, old_table_2, ... old_table_n;
例: 既存のテーブルの空のコピーを作成する

以下の例では、WITH NO DATA を使用して、元のテーブルと同じスキーマである、空の新しいテーブルを作成します。

CREATE TABLE new_table 
AS SELECT * 
FROM old_table
WITH NO DATA;
例: データストレージと圧縮形式を指定する

CTAS を使用すると、ソーステーブルをあるストレージ形式で使用して、別のテーブルを異なるストレージ形式で作成することができます。

format プロパティを使用して、新しいテーブルのストレージ形式として ORC、PARQUET、AVRO、JSON、または TEXTFILE を指定します。

PARQUET、ORC、TEXTFILE、および JSON のストレージ形式については、write_compression プロパティを使用して、新しいテーブルのデータに対する圧縮形式を指定します。各ファイル形式がサポートする圧縮形式については、「Athena での圧縮のサポート」を参照してください。

次の例では、テーブル new_table 内のデータは　Snappy 圧縮を使用した Parquet 形式で保存されることを指定しています。Parquet のデフォルトの圧縮は GZIP です。

CREATE TABLE new_table
WITH (
      format = 'Parquet',
      write_compression = 'SNAPPY')
AS SELECT *
FROM old_table;

次の例では、テーブル new_table 内のデータは、Snappy 圧縮を使用した ORC 形式で保存されることを指定しています。ORC のデフォルトの圧縮は ZLIB です。

CREATE TABLE new_table
WITH (format = 'ORC',
      write_compression = 'SNAPPY')
AS SELECT *
FROM old_table ;

次の例では、テーブル new_table 内のデータは、Snappy 圧縮を使用したテキストファイル形式で保存されることを指定しています。テキストファイルと JSON 形式のデフォルトの圧縮は GZIP です。

CREATE TABLE new_table
WITH (format = 'TEXTFILE',
      write_compression = 'SNAPPY')
AS SELECT *
FROM old_table ;
例: クエリ結果を異なる形式で書き込む

以下の CTAS クエリは、CSV 形式、または別の形式で保存されている可能性がある old_table からすべてのレコードを選択し、ORC 形式で Amazon S3 に保存された基盤となるデータを使って新しいテーブルを作成します。

CREATE TABLE my_orc_ctas_table
WITH (
      external_location = 's3://DOC-EXAMPLE-BUCKET/my_orc_stas_table/',
      format = 'ORC')
AS SELECT * 
FROM old_table;
例: パーティションされていないテーブルを作成する

次の例では、パーティションされていないテーブルを作成します。このテーブル データは異なる形式で格納されます。これらの例の一部では、外部の場所を指定します。

次の例では、テキストファイルとして結果を保存する CTAS クエリが作成されます。

CREATE TABLE ctas_csv_unpartitioned 
WITH (
     format = 'TEXTFILE', 
     external_location = 's3://DOC-EXAMPLE-BUCKET/ctas_csv_unpartitioned/') 
AS SELECT key1, name1, address1, comment1
FROM table1;

次の例では、結果は Parquet で保存され、デフォルトの結果の場所が使用されます。

CREATE TABLE ctas_parquet_unpartitioned 
WITH (format = 'PARQUET') 
AS SELECT key1, name1, comment1
FROM table1;

次のクエリでは、テーブルは JSON で格納され、特定の列が元のテーブルの結果から選択されます。

CREATE TABLE ctas_json_unpartitioned 
WITH (
     format = 'JSON',  
     external_location = 's3://DOC-EXAMPLE-BUCKET/ctas_json_unpartitioned/') 
AS SELECT key1, name1, address1, comment1
FROM table1;

次の例では、形式は ORC です。

CREATE TABLE ctas_orc_unpartitioned 
WITH (
     format = 'ORC') 
AS SELECT key1, name1, comment1 
FROM table1;

次の例では、形式は Avro です。

CREATE TABLE ctas_avro_unpartitioned 
WITH (
     format = 'AVRO', 
     external_location = 's3://DOC-EXAMPLE-BUCKET/ctas_avro_unpartitioned/') 
AS SELECT key1, name1, comment1
FROM table1;
例: パーティションされたテーブルを作成する

次の例では、WITH 句で partitioned_by と他のプロパティを使用した、異なるストレージ形式のパーティション テーブルに対する CREATE TABLE AS SELECT クエリを示します。構文については、「CTAS テーブルのプロパティ」を参照してください。パーティションの列の選択の詳細については、「Athena におけるパーティション化とバケット化」を参照してください。

注記

SELECT ステートメントの列リストの末尾にパーティション列をリストします。複数の列でパーティションでき、最大 100 個の一意のパーティションとバケットの組み合わせを持つことができます。たとえば、バケットが指定されていない場合、100 個のパーティションを持つことができます。

CREATE TABLE ctas_csv_partitioned 
WITH (
     format = 'TEXTFILE',  
     external_location = 's3://DOC-EXAMPLE-BUCKET/ctas_csv_partitioned/', 
     partitioned_by = ARRAY['key1']) 
AS SELECT name1, address1, comment1, key1
FROM tables1;
CREATE TABLE ctas_json_partitioned 
WITH (
     format = 'JSON', 
     external_location = 's3://DOC-EXAMPLE-BUCKET/ctas_json_partitioned/', 
     partitioned_by = ARRAY['key1']) 
AS select name1, address1, comment1, key1 
FROM table1;
例: バケット化およびパーティションされたテーブルを作成する

以下の例は、Amazon S3 へのクエリ結果の保存にパーティションとバケット化の両方を使用する CREATE TABLE AS SELECT クエリを示しています。テーブルの結果は、異なる列でパーティションおよびバケット化されます。Athena は、最大 100 個の一意のバケットとパーティションの組み合わせをサポートします。たとえば、5 個のバケットを持つテーブルを作成する場合、それぞれ 5 個のバケットを持つ 20 個のパーティションがサポートされます。構文については、「CTAS テーブルのプロパティ」を参照してください。

バケット化の列の選択の詳細については、「Athena におけるパーティション化とバケット化」を参照してください。

CREATE TABLE ctas_avro_bucketed 
WITH (
      format = 'AVRO', 
      external_location = 's3://DOC-EXAMPLE-BUCKET/ctas_avro_bucketed/', 
      partitioned_by = ARRAY['nationkey'], 
      bucketed_by = ARRAY['mktsegment'], 
      bucket_count = 3) 
AS SELECT key1, name1, address1, phone1, acctbal, mktsegment, comment1, nationkey 
FROM table1;
例: Parquet データを使った Iceberg テーブルの作成

次の例では、Parquet データファイルを持つ Iceberg テーブルを作成します。ファイルは、table1 の dt 列目を使用して月ごとに分割されます。この例では、テーブルの保存プロパティを更新して、テーブル内のすべてのブランチにデフォルトで 10 個のスナップショットを保持するようにしました。過去 7 日以内のスナップショットも保持されます。Athena の Iceberg テーブル プロパティの詳細については、「テーブルプロパティ」を参照してください。

CREATE TABLE ctas_iceberg_parquet
WITH (table_type = 'ICEBERG',
      format = 'PARQUET', 
      location = 's3://DOC-EXAMPLE-BUCKET/ctas_iceberg_parquet/', 
      is_external = false,
      partitioning = ARRAY['month(dt)'],
      vacuum_min_snapshots_to_keep = 10,
      vacuum_max_snapshot_age_seconds = 604800
   ) 
AS SELECT key1, name1, dt FROM table1;
例: Avro データを含む Iceberg テーブルの作成

次の例では、key1 でパーティションされた Avro データファイルを含む Iceberg テーブルを作成しています。

CREATE TABLE ctas_iceberg_avro
WITH ( format = 'AVRO', 
       location = 's3://DOC-EXAMPLE-BUCKET/ctas_iceberg_avro/', 
       is_external = false,
       table_type = 'ICEBERG',
       partitioning = ARRAY['key1']) 
AS SELECT key1, name1, date FROM table1;