Ejemplos de consultas CTAS

En el siguiente ejemplo se crea una tabla copiando todas las columnas de una tabla:

CREATE TABLE new_table AS 
SELECT * 
FROM old_table;

En la siguiente variante del ejemplo anterior, la instrucción SELECT incluye también una cláusula WHERE. En este caso, la consulta solo selecciona las filas de la tabla que satisfacen la cláusula WHERE:

CREATE TABLE new_table AS 
SELECT * 
FROM old_table 
WHERE condition;

En el siguiente ejemplo se crea una nueva consulta que se ejecuta en un conjunto de columnas de otra tabla:

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

Esta variante del mismo ejemplo crea una nueva tabla a partir de columnas específicas de varias tablas:

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

En el siguiente ejemplo se utiliza WITH NO DATA para crear una nueva tabla, que está vacía y tiene el mismo esquema que la tabla original:

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

Con CTAS, puede utilizar una tabla de origen en un formato de almacenamiento para crear otra tabla en un formato de almacenamiento diferente.

Utilice la propiedad format para especificar ORC, PARQUET, AVRO, JSONo TEXTFILE como formato de almacenamiento para la nueva tabla.

Para los formatos de almacenamiento PARQUET, ORC, TEXTFILE y JSON, utilice la propiedad write_compression para especificar el formato de compresión para los datos de la nueva tabla. Para obtener información sobre los formatos de compresión que admite cada formato de archivo, consulte Uso de la compresión en Athena.

En el siguiente ejemplo se especifica que los datos de la tabla new_table se almacenen en formato Parquet y utilicen la compresión Snappy. La compresión predeterminada de Parquet es GZIP.

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

En el siguiente ejemplo se especifica que los datos de la tabla new_table se almacenen en formato ORC y utilicen la compresión Snappy. La compresión predeterminada de ORC es ZLIB.

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

En el siguiente ejemplo se especifica que los datos de la tabla new_table se almacenen en formato de documento de texto y utilicen la compresión Snappy. La compresión predeterminada de los formatos de documento de texto y JSON es GZIP.

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

La siguiente consulta CTAS selecciona todos los registros de old_table, que podrían estar almacenados en CSV u otro formato, y crea una nueva tabla con los datos subyacentes guardados en Amazon S3 en formato ORC:

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;

Los siguientes ejemplos crean tablas que no están particionadas. Los datos de las tablas se almacenan en diferentes formatos. Algunos de estos ejemplos especifican la ubicación externa.

En el siguiente ejemplo se crea una consulta CTAS que almacena los resultados como un archivo de texto:

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;

En el siguiente ejemplo, los resultados se almacenan en Parquet y se utiliza la ubicación predeterminada para los resultados:

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

En la siguiente consulta, la tabla se almacena en JSON y se seleccionan columnas específicas de los resultados de la tabla original:

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;

En el siguiente ejemplo, el formato es ORC:

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

En el siguiente ejemplo, el formato es 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;

Los siguientes ejemplos muestran consultas CREATE TABLE AS SELECT para tablas con particiones en diferentes formatos de almacenamiento, que utilizan partitioned_by y otras propiedades en la cláusula WITH. Para ver la sintaxis, consulte Propiedades de la tabla CTAS. Para obtener más información sobre cómo elegir las columnas para crear particiones, consulte Uso de particiones y asignación de buckets.

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;

En el siguiente ejemplo se muestra una consulta CREATE TABLE AS SELECT que utiliza particiones y buckets para almacenar los resultados en Amazon S3. Los resultados de las tablas están particionados y distribuidos en buckets por columnas diferentes. Athena admite un máximo de 100 combinaciones únicas de partición y bucket. Por ejemplo, si crea una tabla con cinco buckets, se admiten 20 particiones con cinco buckets cada una. Para ver la sintaxis, consulte Propiedades de la tabla CTAS.

Para obtener información sobre cómo elegir las columnas para la asignación de buckets, consulte Uso de particiones y asignación de buckets.

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;

En el siguiente ejemplo, se crea una tabla de Iceberg con archivos de datos de Parquet. Los archivos se particionan por mes mediante la columna dt de table1. El ejemplo actualiza las propiedades de retención de la tabla para que se retengan 10 instantáneas de forma predeterminada en cada ramificación de la tabla. También se retienen las instantáneas de los últimos 7 días. Para obtener más información sobre las propiedades de las tablas de Iceberg, consulte Especificación de propiedades de tablas.

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;

En el siguiente ejemplo, se crea una tabla de Iceberg con archivos de datos de Avro particionados por key1.

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;