Exemplos de consultas CTAS

O exemplo a seguir cria uma tabela copiando todas as colunas de uma tabela:

CREATE TABLE new_table AS 
SELECT * 
FROM old_table;

Na seguinte variação do mesmo exemplo, a instrução SELECT também inclui uma cláusula WHERE. Nesse caso, a consulta seleciona somente as linhas da tabela que satisfazem a cláusula WHERE:

CREATE TABLE new_table AS 
SELECT * 
FROM old_table 
WHERE condition;

O exemplo a seguir cria uma consulta que é executada em um conjunto de colunas de outra tabela:

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

Essa variação do mesmo exemplo cria uma tabela a partir de colunas específicas de várias tabelas:

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

O exemplo a seguir usa WITH NO DATA para criar uma tabela vazia e que tenha o mesmo esquema da tabela original:

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

Com a CTAS, é possível usar a tabela de origem para criar outra tabela em um formato diferente.

Use a propriedade format para especificar ORC, PARQUET, AVRO, JSON ou TEXTFILE como o formato de armazenamento para a nova tabela.

Para os formatos de armazenamento PARQUET, ORC, TEXTFILE e JSON, use a propriedade write_compression para especificar o formato de compactação para os dados da nova tabela. Para obter mais informações sobre os formatos de compactação suportados por cada formato de arquivo, consulte Usar compactação no Athena.

O exemplo a seguir especifica que os dados na tabela new_table são armazenados em formato Parquet e usam compactação Snappy. A compactação padrão para Parquet é GZIP.

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

O exemplo a seguir especifica que os dados na tabela new_table são armazenados em formato ORC e usam compactação Snappy. A compactação padrão para ORC é ZLIB.

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

O exemplo a seguir especifica esses dados na tabela new_table são armazenados em formato textfile usando a compactação Snappy. A compactação padrão para os formatos textfile e JSON é GZIP.

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

A seguinte consulta CTAS seleciona todos os registros de old_table, que poderiam ser armazenados em CSV ou em outro formato, e cria uma nova tabela com os dados subjacentes salvos no Amazon S3 no 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;

Os exemplos a seguir criam tabelas que não são particionadas. Os dados da tabela são armazenados em formatos diferentes. Alguns desses exemplos especificam o local externo.

O exemplo a seguir cria uma consulta CTAS que armazena os resultados como um arquivo 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;

No exemplo a seguir, os resultados são armazenados em Parquet, e os local padrão dos resultados é usado:

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

No a consulta a seguir, a tabela é armazenada em JSON, e colunas específicas são selecionadas entre os resultados da tabela 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;

No exemplo a seguir, o formato é ORC:

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

No exemplo a seguir, o formato é 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;

Os exemplos a seguir mostram consultas CREATE TABLE AS SELECT para tabelas particionadas em diferentes formatos de armazenamento, usando partitioned_by e outras propriedades na cláusula WITH. Para ver a sintaxe, consulte Propriedades da tabela CTAS. Para obter mais informações sobre como escolher as colunas para particionamento, consulte Usar particionamento e bucketing.

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;

O exemplo a seguir mostra uma consulta CREATE TABLE AS SELECT que usa tanto o particionamento quanto o armazenamento em bucket para armazenar os resultados das consultas no Amazon S3. Os resultados da tabela são particionados e armazenados em bucket por diferentes colunas. O Athena permite no máximo 100 combinações únicas de bucket e partição. Por exemplo, se você criar uma tabela com cinco buckets, é possível ter 20 partições com cinco buckets cada. Para ver a sintaxe, consulte Propriedades da tabela CTAS.

Para obter mais informações sobre como escolher as colunas para armazenamento em bucket, consulte Usar particionamento e 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;

O exemplo a seguir cria uma tabela do Iceberg com arquivos de dados do Parquet. Os arquivos são particionados por mês usando a coluna dt na table1. O exemplo atualiza as propriedades de retenção na tabela para que dez snapshots sejam retidos, por padrão, em cada ramificação da tabela. Os snapshots dos últimos 7 dias também são retidos. Para obter mais informações sobre as propriedades de tabelas Iceberg no Athena, consulte Especificar propriedades das tabelas.

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;

O exemplo a seguir cria uma tabela do Iceberg com arquivos de dados do 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;