Beispiele für CTAS Abfragen

Verwenden Sie die folgenden Beispiele, um CTAS Abfragen zu erstellen. Hinweise zur CTAS Syntax finden Sie unterCREATE TABLE AS.

In diesem Abschnitt:

• Example: Duplicating a table by selecting all columns

• Example: Selecting specific columns from one or more tables

• Example: Creating an empty copy of an existing table

• Example: Specifying data storage and compression formats

• Example: Writing query results to a different format

• Example: Creating unpartitioned tables

• Example: Creating partitioned tables

• Example: Creating bucketed and partitioned tables

• Example: Creating an Iceberg table with Parquet data

• Example: Creating an Iceberg table with Avro data

Beispiel

-Beispiel: Duplizieren einer Tabelle durch Auswahl aller Spalten

Das folgende Beispiel erstellt eine Tabelle durch Kopieren aller Spalten aus einer Tabelle:

CREATE TABLE new_table AS 
SELECT * 
FROM old_table;

In der folgenden Variante des gleichen Beispiels enthält Ihre SELECT-Anweisung auch eine WHERE-Klausel. In diesem Fall wählt die Abfrage nur die Zeilen aus der Tabelle aus, die die WHERE-Klausel erfüllen:

CREATE TABLE new_table AS 
SELECT * 
FROM old_table 
WHERE condition;

Beispiel

-Beispiel: Auswählen bestimmter Spalten aus einer oder mehreren Tabellen

Das folgende Beispiel erstellt eine neue Abfrage, die auf einer Reihe von Spalten aus einer anderen Tabelle ausgeführt wird:

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

Diese Variation des gleichen Beispiels erstellt eine neue Tabelle aus bestimmten Spalten aus mehreren Tabellen:

CREATE TABLE new_table AS
SELECT column_1, column_2, ... column_n 
FROM old_table_1, old_table_2, ... old_table_n;

Beispiel

-Beispiel: Erstellen einer leeren Kopie einer vorhandenen Tabelle

Das folgende Beispiel verwendet WITH NO DATA, um eine neue Tabelle zu erstellen, die leer ist und das gleiche Schema wie die ursprüngliche Tabelle aufweist:

CREATE TABLE new_table 
AS SELECT * 
FROM old_table
WITH NO DATA; 

Beispiel

-Beispiel: Angeben von Datenspeicherungs- und Komprimierungsformaten

Mit CTAS können Sie eine Quelltabelle in einem Speicherformat verwenden, um eine weitere Tabelle in einem anderen Speicherformat zu erstellen.

Verwenden Sie die format-Eigenschaft, um ORC PARQUET ,AVRO, JSON oder TEXTFILE als Speicherformat für die neue Tabelle anzugeben.

Verwenden Sie für die Speicherformate PARQUET, ORC, TEXTFILE, und JSON die write_compression-Eigenschaft, um das Komprimierungsformat für die Daten der neuen Tabelle anzugeben. Informationen zu den Komprimierungsformaten, die jedes Dateiformat unterstützt, finden Sie unter Verwenden Sie die Komprimierung in Athena.

Das folgende Beispiel gibt an, dass die Daten in der Tabelle new_table im Parquet-Format gespeichert werden und die Snappy-Komprimierung verwenden. Die Standardkomprimierung für Parquet ist GZIP.

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

Das folgende Beispiel gibt an, dass Daten in der Tabelle in einem ORC Format gespeichert new_table werden, das die Snappy-Komprimierung verwendet. Die Standardkomprimierung für ORC istZLIB.

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

Das folgende Beispiel gibt an, dass die Daten in der Tabelle new_table im Textfile-Format gespeichert werden und die Snappy-Komprimierung verwenden. Die Standardkomprimierung sowohl für die Textdatei als auch für die JSON Formate istGZIP.

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

Beispiel

-Beispiel: Schreiben von Abfrageergebnissen in ein anderes Format

Die folgende CTAS Abfrage wählt alle Datensätze ausold_table, die in einem CSV oder einem anderen Format gespeichert werden könnten, und erstellt eine neue Tabelle mit zugrunde liegenden Daten, die in Amazon S3 im ORC Format gespeichert wurden:

CREATE TABLE my_orc_ctas_table
WITH (
      external_location = 's3://amzn-s3-demo-bucket/my_orc_stas_table/',
      format = 'ORC')
AS SELECT * 
FROM old_table;

Beispiel

-Beispiel: Erstellen von nicht partitionierten Tabellen

Die folgenden Beispiele erstellen Tabellen, die nicht partitioniert werden. Die Tabellendaten werden in verschiedenen Formaten gespeichert. Einige dieser Beispiele geben den externen Speicherort an.

Das folgende Beispiel erstellt eine CTAS Abfrage, die die Ergebnisse als Textdatei speichert:

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

Im folgenden Beispiel werden die Ergebnisse in Parquet gespeichert und der standardmäßige Ergebnisspeicherort wird verwendet:

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

In der folgenden Abfrage wird die Tabelle gespeichertJSON, und bestimmte Spalten werden aus den Ergebnissen der Originaltabelle ausgewählt:

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

Im folgenden Beispiel lautet das FormatORC:

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

Im folgenden Beispiel lautet das Format Avro:

CREATE TABLE ctas_avro_unpartitioned 
WITH (
     format = 'AVRO', 
     external_location = 's3://amzn-s3-demo-bucket/ctas_avro_unpartitioned/') 
AS SELECT key1, name1, comment1
FROM table1;

Beispiel

-Beispiel: Erstellen partitionierter Tabellen

Die folgenden Beispiele zeigen CREATE TABLE AS SELECT-Abfragen für partitionierte Tabellen in verschiedenen Datenspeicherformaten unter Verwendung von partitioned_by und anderen Eigenschaften in der WITH-Klausel. Weitere Informationen zur Syntax finden Sie unter CTASEigenschaften von Tabellen. Weitere Informationen zur Auswahl der Spalten für die Partitionierung finden Sie unter Verwenden Sie Partitioning und Bucketing.

Anmerkung

Auflisten von Partitionsspalten am Ende der Liste der Spalten in der SELECT-Anweisung. Sie können in mehrere Spalten partitionieren und über bis zu 100 eindeutige Partitions- und Bucket-Kombinationen verfügen. Sie können beispielsweise 100 Partitionen haben, wenn keine Buckets angegeben sind.

CREATE TABLE ctas_csv_partitioned 
WITH (
     format = 'TEXTFILE',  
     external_location = 's3://amzn-s3-demo-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://amzn-s3-demo-bucket/ctas_json_partitioned/', 
     partitioned_by = ARRAY['key1']) 
AS select name1, address1, comment1, key1 
FROM table1;

Beispiel

-Beispiel: Erstellen von Tabellen mit Bucketing und Partitionierung

Das folgende Beispiel zeigt eine CREATE TABLE AS SELECT-Abfrage, die sowohl die Partitionierung als auch das Bucketing zum Speichern von Abfrageergebnissen in Amazon S3 verwendet. Die Tabellenergebnisse werden partitioniert und nach verschiedenen Spalten gruppiert. Athena unterstützt maximal 100 eindeutige Kombinationen aus Bucket und Partition. Wenn Sie beispielsweise eine Tabelle mit fünf Buckets erstellen, werden 20 Partitionen mit je fünf Buckets unterstützt. Weitere Informationen zur Syntax finden Sie unter CTASEigenschaften von Tabellen.

Weitere Informationen zur Auswahl der Spalten für das Bucketing finden Sie unter Verwenden Sie Partitioning und Bucketing.

CREATE TABLE ctas_avro_bucketed 
WITH (
      format = 'AVRO', 
      external_location = 's3://amzn-s3-demo-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;

Beispiel

-Beispiel: Erstellen einer Iceberg-Tabelle mit Parquet-Daten

Im folgenden Beispiel wird eine Iceberg-Tabelle mit Parquet-Datendateien erstellt. Die Dateien werden mithilfe der dt-Spalte in table1 nach Monaten partitioniert. Das Beispiel aktualisiert die Aufbewahrungseigenschaften für die Tabelle, sodass standardmäßig 10 Snapshots in jeder Verzweigung in der Tabelle aufbewahrt werden. Snapshots innerhalb der letzten 7 Tage werden ebenfalls aufbewahrt. Weitere Informationen zu Iceberg-Tabelleneigenschaften in Athena finden in Geben Sie die Tabelleneigenschaften an.

CREATE TABLE ctas_iceberg_parquet
WITH (table_type = 'ICEBERG',
      format = 'PARQUET', 
      location = 's3://amzn-s3-demo-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;

Beispiel

-Beispiel: Erstellen einer Iceberg-Tabelle mit Avro-Daten

Im folgenden Beispiel wird eine Iceberg-Tabelle mit Avro-Datendateien mit Partitionierung durch key1 erstellt.

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