# Criar uma tabela com base em resultados de consultas (CTAS)
Uma consulta `CREATE TABLE AS SELECT` (CTAS) cria uma tabela no Athena com base nos resultados de uma instrução `SELECT` de outra consulta. O Athena armazena arquivos de dados criados pela instrução CTAS em um local especificado no Amazon S3. Para ver a sintaxe, consulte [CREATE TABLE AS](create-table-as.md).
`CREATE TABLE AS` combina uma instrução DDL `CREATE TABLE` com uma instrução DML `SELECT`, por isso tecnicamente contém tanto DDL quanto DML. No entanto, observe que, para fins de cotas de serviço, as consultas CTAS no Athena são tratadas como DML. Para obter informações sobre as cotas de serviço do Athena, consulte [Service Quotas](service-limits.md).
Use consultas CTAS para:
+ Criar tabelas a partir dos resultados da consulta em uma etapa, sem consultar conjuntos de dados brutos repetidamente. Isso facilita o trabalho com conjuntos de dados brutos.
+ Transformar os resultados da consulta e migrar tabelas para outros formatos de tabela, como o Apache Iceberg. Isso melhora a performance da consulta e reduz seus custos no Athena. Para mais informações, consulte [Criar tabelas do Iceberg](querying-iceberg-creating-tables.md).
+ Transformar os resultados da consulta em formatos de armazenamento, como Parquet e ORC. Isso melhora a performance da consulta e reduz seus custos no Athena. Para mais informações, consulte [Usar formatos de armazenamento colunares](columnar-storage.md).
+ Crie cópias de tabelas existentes que contêm somente os dados necessários.
**Topics**
+ [Considerações e limitações de consultas CTAS](ctas-considerations-limitations.md)
+ [Criar consultas CTAS](ctas-console.md)
+ [Exemplos de CTAS](ctas-examples.md)
+ [Usar CTAS e INSERT INTO para ETL](ctas-insert-into-etl.md)
+ [Contornar o limite de 100 partições](ctas-insert-into.md)
# Considerações e limitações de consultas CTAS
As seções a seguir descrevem considerações e limitações que você deve ter em mente ao usar consultas (CTAS) `CREATE TABLE AS SELECT` no Athena.
## Aprender a sintaxe de consulta CTAS
A sintaxe de consulta CTAS é diferente da sintaxe de `CREATE [EXTERNAL] TABLE` usada para criar tabelas. Consulte [CREATE TABLE AS](create-table-as.md).
## A diferença entre visualizações e consultas CTAS
As consultas CTAS gravam novos dados em um local especificado no Amazon S3. As visualizações não gravam nenhum dado.
## Especificar um local para armazenar os resultados da sua consulta CTAS
Se o seu grupo de trabalho [substituir a configuração do lado do cliente](workgroups-settings-override.md) para o local dos resultados das consultas, o Athena criará a tabela no local `s3://amzn-s3-demo-bucket/tables//`. Para ver o local dos resultados de consultas especificado para o grupo de trabalho, [visualize os detalhes do grupo de trabalho](viewing-details-workgroups.md).
Se seu grupo de trabalho não substituir o local dos resultados de consultas, você poderá usar a sintaxe `WITH (external_location ='s3://amzn-s3-demo-bucket/')` em sua consulta CTAS para especificar onde os resultados da consulta CTAS são armazenados.
**nota**
A propriedade `external_location` deve especificar um local vazio. Uma consulta CTAS verifica se o local do caminho (prefixo) no bucket está vazio e nunca substitui os dados se o local já tiver dados. Para usar o mesmo local novamente, exclua os dados no local do prefixo de chave no bucket.
Se você omitir a sintaxe de `external_location` e não usar a configuração do grupo de trabalho, o Athena usará sua [configuração do lado do cliente](query-results-specify-location-console.md) para o local dos resultados das consultas e criará a tabela no local `s3://amzn-s3-demo-bucket////tables//`.
## Localizar arquivos órfãos
Quando ocorre a falha de uma instrução `CTAS` ou `INSERT INTO`, é possível que arquivos de dados órfãos sejam deixados no local de destino dos dados para as consultas malsucedidas ou canceladas. Como, em alguns casos, o Athena não exclui os dados do bucket de destino da consulta, dados parciais podem ser incluídos nas consultas subsequentes.
Para localizar arquivos órfãos para inspeção ou exclusão, é possível usar o arquivo do manifesto de dados que o Athena oferece para rastrear a lista de arquivos a serem gravados. Em alguns casos raros em que ocorre a falha abrupta de uma consulta do Athena, o arquivo do manifesto pode não estar presente. Você pode encontrar os arquivos órfãos inspecionando manualmente o local de destino do S3. Para obter mais informações, consulte [Identificar arquivos de saída de consultas](querying-finding-output-files.md#querying-identifying-output-files) e [DataManifestLocation](https://docs.aws.amazon.com/athena/latest/APIReference/API_QueryExecutionStatistics.html#athena-Type-QueryExecutionStatistics-DataManifestLocation).
É altamente recomendável usar o Apache Iceberg para realizar transações atômicas de tabelas. Para obter mais informações, consulte [Consultar tabelas do Apache Iceberg](querying-iceberg.md).
## Lembre-se de que as cláusulas ORDER BY são ignoradas
Em uma consulta CTAS, o Athena ignora as cláusulas `ORDER BY` na parte `SELECT` da consulta.
Segundo a especificação SQL (ISO 9075 Parte 2), a ordenação das linhas de uma tabela especificada por uma expressão de consulta é garantida somente para a expressão de consulta que contém imediatamente a cláusula `ORDER BY`. De qualquer forma, as tabelas no SQL são inerentemente desordenadas, e a implementação das cláusulas na subconsulta `ORDER BY` faria com que a consulta tivesse uma performance ruim e não resultaria em uma saída ordenada. Assim, nas consultas CTAS do Athena, não há garantia de que a ordem especificada pela cláusula `ORDER BY` será preservada quando os dados forem gravados.
## Escolher um formato para armazenar os resultados da consulta
É possível armazenar resultados de CTAS em `PARQUET`, `ORC`, `AVRO`, `JSON` e `TEXTFILE`. Não há suporte para delimitadores de vários caracteres no formato CTAS `TEXTFILE`. Se você não especificar um formato de armazenamento de dados, os resultados da consulta CTAS serão armazenados no Parquet por padrão.
As consultas CTAS não exigem a especificação de um SerDe para interpretar transformações de formato. Consulte [Example: Writing query results to a different format](ctas-examples.md#ctas-example-format).
## Considerar os formatos de compactação
`GZIP`A compactação é usada para os resultados da consulta CTAS nos formatos JSON e TEXTFILE. Para Parquet, você pode usar `GZIP` ou `SNAPPY`, e o padrão é `GZIP`. Para ORC, você pode usar `LZ4`, `SNAPPY`, `ZLIB` ou `ZSTD`, e o padrão é `ZLIB`. Para obter exemplos de CTAS que especificam compactação, consulte [Example: Specifying data storage and compression formats](ctas-examples.md#ctas-example-compression). Para obter informações sobre compactação no Athena, consulte [Usar compactação no Athena](compression-formats.md).
## Particionamento e uso de buckets para seus resultados
Você pode particionar e armazenar em buckets os dados resultantes de uma consulta CTAS. Para especificar as propriedades da tabela de destino, inclua predicados de particionamento e bucketing no final da cláusula `WITH`. Para obter mais informações, consulte [Usar particionamento e bucketing](ctas-partitioning-and-bucketing.md) e [Example: Creating bucketed and partitioned tables](ctas-examples.md#ctas-example-bucketed).
Ao usar o CTAS para criar uma tabela particionada, o Athena tem um limite de gravação de 100 partições. Para obter informações sobre como contornar a limitação de 100 partições, consulte [Usar CTAS e INSERT INTO para resolver o limite de 100 partições](ctas-insert-into.md).
## Criptografar seus resultados
É possível criptografar os resultados da consulta CTAS no Amazon S3, semelhante à forma como você criptografa outros resultados de consulta no Athena. Para obter mais informações, consulte [Criptografar os resultados de consultas do Athena armazenados no Amazon S3](encrypting-query-results-stored-in-s3.md).
## A configuração esperada para o proprietário do buckets não se aplica a CTAS
Para instruções CTAS, a configuração esperada do proprietário do bucket não se aplica ao local da tabela de destino no Amazon S3. A configuração esperada do proprietário do bucket se aplica somente ao local de saída do Amazon S3 que você especificar para os resultados da consulta do Athena. Para obter mais informações, consulte [Especificar um local para resultados de consultas com uso do console do Athena](query-results-specify-location-console.md).
## Os tipos de dados de coluna são preservados
Os tipos de dados da coluna para uma consulta CTAS são os mesmos que os tipos especificados para a consulta original.
# Criar consultas CTAS no console do Athena
No console do Athena, você pode criar uma consulta CTAS com base em outra consulta.
**Para criar uma consulta CTAS a partir de outra consulta**
1. Execute a consulta no editor de consultas do console do Athena.
1. Na parte inferior do editor de consultas, escolha a opção **Create** (Criar) e, em seguida, escolha **Table from query** (Tabela a partir de consulta).
1. No formulário **Create table as select** (Criar tabela conforme seleção), preencha os seguintes campos:
1. Em **Table name** (Nome da tabela), especifique o nome para sua nova tabela. Use apenas letras minúsculas e sublinhados, como `my_select_query_parquet`.
1. Em **Database configuration** (Configuração de banco de dados), use as opções para escolher um banco de dados existente ou criar um banco de dados.
1. (Opcional) Em **Result configuration** (Configuração de resultado), para **Location of CTAS query results** (Localização dos resultados da consulta CTAS), se a configuração de localização dos resultados da consulta de grupo de trabalho não substituir essa opção, faça o seguinte:
+ Insira o caminho para uma localização existente do S3 na caixa de pesquisa ou escolha **Browse S3** (Procurar no S3) para escolher uma localização em uma lista.
+ Escolha **View** (Visualizar) para abrir a página **Buckets** do console do Amazon S3, na qual você poderá ver mais informações sobre seus buckets existentes e escolher ou criar um bucket com suas próprias configurações.
Você deverá especificar um local vazio no Amazon S3 no qual os dados serão produzidos. Se os dados já existirem no local especificado, a consulta apresentará erro.
Se a configuração de localização dos resultados de consulta do seu grupo de trabalho substituir a configuração de localização, o Athena criará a tabela no local `s3://amzn-s3-demo-bucket/tables/query_id/`.
1. Em **Data format** (Formato de dados), especifique o formato dos seus dados.
+ **Table type** (Tipo de tabela): o tipo de tabela padrão no Athena é o Apache Hive.
+ **File format** (Formato de arquivo): escolha entre opções como CSV, TSV, JSON, Parquet ou ORC. Para obter mais informações sobre os formatos Parquet e ORC, consulte [Usar formatos de armazenamento colunares](columnar-storage.md).
+ **Write compression** (Compactação de gravação): (opcional) escolha um formato de compactação. O Athena suporta uma variedade de formatos de compactação para leitura e gravação de dados, incluindo a leitura de uma tabela que usa vários formatos de compactação. Por exemplo, o Athena pode ler com sucesso os dados de uma tabela que usa o formato de arquivo Parquet quando alguns arquivos Parquet são compactados com o Snappy e outros arquivos Parquet são compactados com o GZIP. O mesmo princípio se aplica aos formatos de armazenamento ORC, arquivo de texto e JSON. Para obter mais informações, consulte [Usar compactação no Athena](compression-formats.md).
+ **Partitions** (Partições): (opcional) selecione as colunas que você deseja particionar. O particionamento dos dados restringe a quantidade que cada consulta verifica, o que melhora a performance e reduz o custo. Você pode dividir seus dados em partições usando qualquer chave. Para obter mais informações, consulte [Particionar dados](partitions.md).
+ **Buckets** (Compartimentos): (opcional) selecione as colunas que você deseja agrupar. O agrupamento é uma técnica que agrupa dados com base em colunas específicas em uma só partição. Essas colunas são conhecidas como *bucket keys* (chaves de bucket). Ao agrupar dados relacionados em um só bucket (um arquivo dentro de uma partição), você reduz significativamente a quantidade de dados digitalizados pelo Athena, melhorando assim a performance da consulta e reduzindo os custos. Para obter mais informações, consulte [Usar particionamento e bucketing](ctas-partitioning-and-bucketing.md).
1. Em **Preview table query** (Visualizar consulta da tabela), analise sua consulta. Para ver a sintaxe de consulta, acesse [CREATE TABLE AS](create-table-as.md).
1. Escolha **Create table**.
O console do Athena tem um modelo SQL que você também pode usar para criar uma consulta CTAS.
**Para criar uma consulta CTAS usando um modelo SQL**
Use o modelo `CREATE TABLE AS SELECT` para criar uma consulta CTAS no editor de consultas.
1. No console do Athena, ao lado de **Tables and views** (Tabelas e visualizações), escolha **Create table** (Criar tabela) e, em seguida, escolha **CREATE TABLE AS SELECT**. Isso preenche o editor de consultas com uma consulta CTAS com valores de espaço reservado.
1. No editor de consultas, edite a consulta conforme necessário. Para ver a sintaxe de consulta, acesse [CREATE TABLE AS](create-table-as.md).
1. Escolha **Executar**.
Para obter exemplos, consulte [Exemplos de consultas CTAS](ctas-examples.md).
# Exemplos de consultas CTAS
Use os exemplos a seguir para criar consultas CTAS. Para obter informações sobre a sintaxe de CTAS, consulte [CREATE TABLE AS](create-table-as.md).
Nesta seção:
+ [Example: Duplicating a table by selecting all columns](#ctas-example-dupe-table)
+ [Example: Selecting specific columns from one or more tables](#ctas-example-specify-columns)
+ [Example: Creating an empty copy of an existing table](#ctas-example-empty-table)
+ [Example: Specifying data storage and compression formats](#ctas-example-compression)
+ [Example: Writing query results to a different format](#ctas-example-format)
+ [Example: Creating unpartitioned tables](#ctas-example-unpartitioned)
+ [Example: Creating partitioned tables](#ctas-example-partitioned)
+ [Example: Creating bucketed and partitioned tables](#ctas-example-bucketed)
+ [Example: Creating an Iceberg table with Parquet data](#ctas-example-iceberg-parquet)
+ [Example: Creating an Iceberg table with Avro data](#ctas-example-iceberg-avro)
+ [Example: Creating an S3 table using CTAS](#ctas-example-s3-table)
**Example Exemplo: duplicar tabela selecionando todas as colunas**
O exemplo a seguir cria uma tabela copiando todas as colunas de uma tabela:
```
CREATE TABLE new_table AS
SELECT *
FROM old_table;
```
Na variação a seguir 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;
```
**Example Exemplo: selecionar colunas específicas de uma ou mais tabelas**
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 por meio 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;
```
**Example Exemplo: criar uma cópia vazia de uma tabela existente**
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;
```
**Example Exemplo: especificar armazenamento de dados e formatos de compactação**
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](compression-formats.md).
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 ;
```
**Example Exemplo: gravar resultados da consulta em um formato diferente**
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;
```
**Example Exemplo: criar tabelas não particionadas**
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;
```
**Example Exemplo: criar tabelas particionadas**
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](create-table-as.md#ctas-table-properties). Para obter mais informações sobre como escolher as colunas para particionamento, consulte [Usar particionamento e bucketing](ctas-partitioning-and-bucketing.md).
Liste as colunas da partição no final da lista de colunas na instrução `SELECT`. Você pode particionar por mais de uma coluna e ter até 100 combinações únicas de partições e buckets. Por exemplo, você pode ter 100 partições se nenhum bucket for especificado.
```
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;
```
**Example Exemplo: criar tabelas em bucket e particionadas**
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](create-table-as.md#ctas-table-properties).
Para obter mais informações sobre como escolher as colunas para armazenamento em bucket, consulte [Usar particionamento e bucketing](ctas-partitioning-and-bucketing.md).
```
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;
```
**Example Exemplo: criação de uma tabela do Iceberg com dados do Parquet**
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](querying-iceberg-creating-tables.md#querying-iceberg-table-properties).
```
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;
```
**Example Exemplo: criação de uma tabela do Iceberg com dados do Avro**
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;
```
**Example Exemplo: criando uma tabela do S3 usando CTAS**
O exemplo a seguir cria uma nova tabela do S3 usando CTAS. Observe que a propriedade de localização é omitida e o `table_type` assume o padrão `ICEBERG`:
```
CREATE TABLE "s3tablescatalog/amzn-s3-demo-bucket"."namespace"."s3-table-name"
WITH (
format = 'PARQUET'
)
AS SELECT *
FROM source_table;
```
Você pode especificar todas as outras propriedades da tabela do Iceberg, como particionamento e distribuição em buckets com a mesma sintaxe das tabelas normais do Iceberg.
# Usar CTAS e INSERT INTO para ETL e análise de dados
Você pode usar as instruções Create Table as Select ([CTAS](ctas.md)) e [INSERT INTO](insert-into.md) no Athena para extrair, transformar e carregar (ETL) dados no Amazon S3 para processamento de dados. Este tópico mostra como usar essas instruções para particionar e converter um conjunto de dados em um formato de dados colunar para otimizá-lo para análise de dados.
As instruções CTAS usam consultas [SELECT](select.md) padrão para criar novas tabelas. É possível usar uma instrução CTAS para criar um subconjunto de dados para análise. Em uma instrução CTAS, é possível particionar os dados, especificar compactação e converter os dados em um formato colunar, como Apache Parquet ou Apache ORC. Ao executar a consulta CTAS, as tabelas e as partições criadas são adicionadas automaticamente ao [AWS Glue Data Catalog](https://aws.amazon.com/glue). Isso torna as novas tabelas e partições criadas imediatamente disponíveis para consultas subsequentes.
Instruções INSERT INTO inserem novas linhas em uma tabela de destino com base em uma instrução de consulta SELECT que é executada em uma tabela de origem. É possível usar instruções INSERT INTO para transformar e carregar dados da tabela de origem no formato CSV em dados da tabela de destino usando todas as transformações com suporte do CTAS.
## Visão geral
No Athena, use uma instrução CTAS para executar uma conversão inicial em lote dos dados. Depois disso, use várias instruções INSERT INTO para fazer atualizações incrementais para a tabela criada pela instrução CTAS.
**Etapas**
+ [Etapa 1: Criar uma tabela com base no conjunto de dados original](#ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset)
+ [Etapa 2: Usar CTAS para particionar, converter e compactar os dados](#ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data)
+ [Etapa 3: Usar INSERT INTO para adicionar dados](#ctas-insert-into-etl-step-3-use-insert-into-to-add-data)
+ [Etapa 4: Avaliar diferenças de custo e performance](#ctas-insert-into-etl-step-4-measure-performance-and-cost-differences)
## Etapa 1: Criar uma tabela com base no conjunto de dados original
O exemplo neste tópico usa um subconjunto legível pelo Amazon S3 do conjunto de dados [Global Historical Climatology Network Daily (GHCNd) da NOAA](https://registry.opendata.aws/noaa-ghcn/) disponível publicamente. Os dados no Amazon S3 têm as características a seguir.
```
Location: s3://aws-bigdata-blog/artifacts/athena-ctas-insert-into-blog/
Total objects: 41727
Size of CSV dataset: 11.3 GB
Region: us-east-1
```
Os dados originais são armazenados no Amazon S3 sem partições. Os dados estão no formato CSV em arquivos, conforme mostrado a seguir.
```
2019-10-31 13:06:57 413.1 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0000
2019-10-31 13:06:57 412.0 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0001
2019-10-31 13:06:57 34.4 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0002
2019-10-31 13:06:57 412.2 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0100
2019-10-31 13:06:57 412.7 KiB artifacts/athena-ctas-insert-into-blog/2010.csv0101
```
Os tamanhos de arquivo neste exemplo são relativamente pequenos. Ao mesclá-los em arquivos maiores, é possível reduzir o número total de arquivos, o que melhora a performance das consultas. É possível usar instruções CTAS e INSERT INTO para melhorar a performance de consulta.
**Como criar um banco de dados e uma tabela com base no conjunto de dados de exemplo**
1. No console do Athena, escolha a Região da AWS **Leste dos EUA (Norte da Virgínia)**. Execute todas as consultas neste tutorial em `us-east-1`.
1. No editor de consultas do Athena, execute o comando [CREATE DATABASE](create-database.md) para criar um banco de dados.
```
CREATE DATABASE blogdb
```
1. Execute a seguinte instrução para [criar uma tabela](create-table.md).
```
CREATE EXTERNAL TABLE `blogdb`.`original_csv` (
`id` string,
`date` string,
`element` string,
`datavalue` bigint,
`mflag` string,
`qflag` string,
`sflag` string,
`obstime` bigint)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://aws-bigdata-blog/artifacts/athena-ctas-insert-into-blog/'
```
## Etapa 2: Usar CTAS para particionar, converter e compactar os dados
Depois de criar uma tabela, é possível usar uma única instrução [CTAS](ctas.md) para converter os dados para o formato Parquet com compactação Snappy e para particionar os dados por ano.
A tabela criada na Etapa 1 tem um campo `date` com a data formatada como `YYYYMMDD` (por exemplo, `20100104`). Como a nova tabela será particionada em `year`, a instrução de exemplo no procedimento a seguir usa a função Presto `substr("date",1,4)` para extrair o valor `year` do campo `date`.
**Como converter os dados para o formato Parquet com compactação Snappy, particionando por ano**
+ Execute a instrução CTAS a seguir, substituindo *your-bucket* pelo local do seu bucket do Amazon S3.
```
CREATE table new_parquet
WITH (format='PARQUET',
parquet_compression='SNAPPY',
partitioned_by=array['year'],
external_location = 's3://amzn-s3-demo-bucket/optimized-data/')
AS
SELECT id,
date,
element,
datavalue,
mflag,
qflag,
sflag,
obstime,
substr("date",1,4) AS year
FROM original_csv
WHERE cast(substr("date",1,4) AS bigint) >= 2015
AND cast(substr("date",1,4) AS bigint) <= 2019
```
**nota**
Neste exemplo, a tabela criada inclui apenas os dados de 2015 a 2019. Na Etapa 3, adicione novos dados a essa tabela usando o comando INSERT INTO.
Quando a consulta for concluída, siga o procedimento abaixo para verificar a saída no local do Amazon S3 especificado na instrução CTAS.
**Como ver as partições e os arquivos Parquet criados pela instrução CTAS**
1. Para mostrar as partições criadas, execute o seguinte comando da AWS CLI. Certifique-se de incluir a barra final (/).
```
aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/
```
A saída mostra as partições.
```
PRE year=2015/
PRE year=2016/
PRE year=2017/
PRE year=2018/
PRE year=2019/
```
1. Para ver os arquivos Parquet, execute o seguinte comando. Observe que a opção `|` *head -5*, que restringe a saída aos primeiros cinco resultados, não está disponível no Windows.
```
aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable | head -5
```
A saída será semelhante à seguinte.
```
2019-10-31 14:51:05 7.3 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_1be48df2-3154-438b-b61d-8fb23809679d
2019-10-31 14:51:05 7.0 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_2a57f4e2-ffa0-4be3-9c3f-28b16d86ed5a
2019-10-31 14:51:05 9.9 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_34381db1-00ca-4092-bd65-ab04e06dc799
2019-10-31 14:51:05 7.5 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_354a2bc1-345f-4996-9073-096cb863308d
2019-10-31 14:51:05 6.9 MiB optimized-data/year=2015/20191031_215021_00001_3f42d_42da4cfd-6e21-40a1-8152-0b902da385a1
```
## Etapa 3: Usar INSERT INTO para adicionar dados
Na Etapa 2, você usou o CTAS para criar uma tabela com partições para os anos de 2015 a 2019. No entanto, o conjunto de dados original também contém dados para os anos de 2010 a 2014. Agora, adicione esses dados usando uma instrução [INSERT INTO](insert-into.md).
**Como adicionar dados à tabela usando uma ou mais instruções INSERT INTO**
1. Execute o seguinte comando INSERT INTO, especificando os anos antes de 2015 na cláusula WHERE.
```
INSERT INTO new_parquet
SELECT id,
date,
element,
datavalue,
mflag,
qflag,
sflag,
obstime,
substr("date",1,4) AS year
FROM original_csv
WHERE cast(substr("date",1,4) AS bigint) < 2015
```
1. Execute o comando `aws s3 ls` novamente, usando a seguinte sintaxe.
```
aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/
```
A saída mostra as novas partições.
```
PRE year=2010/
PRE year=2011/
PRE year=2012/
PRE year=2013/
PRE year=2014/
PRE year=2015/
PRE year=2016/
PRE year=2017/
PRE year=2018/
PRE year=2019/
```
1. Para ver a redução no tamanho do conjunto de dados obtido por meio do uso da compactação e do armazenamento colunar no formato Parquet, execute o seguinte comando.
```
aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable --summarize
```
Os resultados a seguir mostram que o tamanho do conjunto de dados após o Parquet com compactação Snappy é 1.2 GB.
```
...
2020-01-22 18:12:02 2.8 MiB optimized-data/year=2019/20200122_181132_00003_nja5r_f0182e6c-38f4-4245-afa2-9f5bfa8d6d8f
2020-01-22 18:11:59 3.7 MiB optimized-data/year=2019/20200122_181132_00003_nja5r_fd9906b7-06cf-4055-a05b-f050e139946e
Total Objects: 300
Total Size: 1.2 GiB
```
1. Se mais dados CSV forem adicionados à tabela original, você pode adicionar esses dados à tabela Parquet usando instruções INSERT INTO. Por exemplo, se você tiver novos dados para o ano de 2020, poderá executar a instrução INSERT INTO a seguir. A instrução adiciona os dados e a partição relevante à tabela `new_parquet`.
```
INSERT INTO new_parquet
SELECT id,
date,
element,
datavalue,
mflag,
qflag,
sflag,
obstime,
substr("date",1,4) AS year
FROM original_csv
WHERE cast(substr("date",1,4) AS bigint) = 2020
```
**nota**
A instrução INSERT INTO oferece suporte à gravação de, no máximo, 100 partições na tabela de destino. No entanto, para adicionar mais de 100 partições, você pode executar várias instruções INSERT INTO. Para obter mais informações, consulte [Usar CTAS e INSERT INTO para resolver o limite de 100 partições](ctas-insert-into.md).
## Etapa 4: Avaliar diferenças de custo e performance
Depois de transformar os dados, é possível avaliar os ganhos de performance e a economia de custos executando as mesmas consultas nas tabelas novas e antigas e comparando os resultados.
**nota**
Para obter informações de custo por consulta do Athena, consulte [Definição de preço do Amazon Athena](https://aws.amazon.com/athena/pricing).
**Como avaliar diferenças de custo e ganhos de performance**
1. Execute a seguinte consulta na tabela original. A consulta localiza o número de IDs distintos para cada valor do ano.
```
SELECT substr("date",1,4) as year,
COUNT(DISTINCT id)
FROM original_csv
GROUP BY 1 ORDER BY 1 DESC
```
1. Observe o tempo em que a consulta foi executada e a quantidade de dados verificados.
1. Execute a mesma consulta na nova tabela, observando o tempo de execução da consulta e a quantidade de dados verificados.
```
SELECT year,
COUNT(DISTINCT id)
FROM new_parquet
GROUP BY 1 ORDER BY 1 DESC
```
1. Compare os resultados e calcule a diferença de performance e custo. Os seguintes resultados de exemplo mostram que a consulta de teste na nova tabela foi mais rápida e mais econômica do que a consulta na tabela antiga.
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/athena/latest/ug/ctas-insert-into-etl.html)
1. Execute a seguinte consulta de exemplo na tabela original. A consulta calcula a temperatura máxima média (Celsius), a temperatura mínima média (Celsius) e a precipitação média (mm) da Terra em 2018.
```
SELECT element, round(avg(CAST(datavalue AS real)/10),2) AS value
FROM original_csv
WHERE element IN ('TMIN', 'TMAX', 'PRCP') AND substr("date",1,4) = '2018'
GROUP BY 1
```
1. Observe o tempo em que a consulta foi executada e a quantidade de dados verificados.
1. Execute a mesma consulta na nova tabela, observando o tempo de execução da consulta e a quantidade de dados verificados.
```
SELECT element, round(avg(CAST(datavalue AS real)/10),2) AS value
FROM new_parquet
WHERE element IN ('TMIN', 'TMAX', 'PRCP') and year = '2018'
GROUP BY 1
```
1. Compare os resultados e calcule a diferença de performance e custo. Os seguintes resultados de exemplo mostram que a consulta de teste na nova tabela foi mais rápida e mais econômica do que a consulta na tabela antiga.
****
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/athena/latest/ug/ctas-insert-into-etl.html)
## Resumo
Este tópico mostrou como executar operações ETL usando as instruções CTAS e INSERT INTO no Athena. Você executou o primeiro conjunto de transformações usando uma instrução CTAS que converteu dados para o formato Parquet com compactação Snappy. A instrução CTAS também converteu o conjunto de dados de não particionado em particionado. Isso reduziu seu tamanho e reduziu os custos de execução das consultas. Quando novos dados são disponibilizados, é possível usar uma instrução INSERT INTO para transformar e carregar os dados na tabela criada com a instrução CTAS.
# Usar CTAS e INSERT INTO para resolver o limite de 100 partições
Athena tem um limite de 100 partições por consulta `CREATE TABLE AS SELECT` ([CTAS](ctas.md)). Da mesma forma, é possível adicionar, no máximo, 100 partições a uma tabela de destino com uma instrução [INSERT INTO](https://docs.aws.amazon.com/athena/latest/ug/insert-into.html).
Se exceder essa limitação, você poderá receber a mensagem de erro HIVE\$1TOO\$1MANY\$1OPEN\$1PARTITIONS: Exceeded limit of 100 open writers for partitions/buckets (Limite excedido de 100 gravadores abertos para partições/buckets). Para contornar essa limitação, é possível usar uma instrução CTAS e uma série de instruções `INSERT INTO` que criam ou inserem até 100 partições cada.
O exemplo neste tópico usa um banco de dados chamado `tpch100` cujos dados residem no local do bucket do Amazon S3 s3://amzn-s3-demo-bucket/.
**Como usar CTAS e INSERT INTO para criar uma tabela com mais de 100 partições**
1. Use uma instrução `CREATE EXTERNAL TABLE` para criar uma tabela particionada no campo desejado.
A instrução de exemplo a seguir particiona os dados de acordo com a coluna `l_shipdate`. A tabela tem 2.525 partições.
```
CREATE EXTERNAL TABLE `tpch100.lineitem_parq_partitioned`(
`l_orderkey` int,
`l_partkey` int,
`l_suppkey` int,
`l_linenumber` int,
`l_quantity` double,
`l_extendedprice` double,
`l_discount` double,
`l_tax` double,
`l_returnflag` string,
`l_linestatus` string,
`l_commitdate` string,
`l_receiptdate` string,
`l_shipinstruct` string,
`l_comment` string)
PARTITIONED BY (
`l_shipdate` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://amzn-s3-demo-bucket/lineitem/'
```
1. Execute um comando `SHOW PARTITIONS ` conforme o seguinte para listar as partições.
```
SHOW PARTITIONS lineitem_parq_partitioned
```
Veja a seguir os resultados parciais de exemplo.
```
/*
l_shipdate=1992-01-02
l_shipdate=1992-01-03
l_shipdate=1992-01-04
l_shipdate=1992-01-05
l_shipdate=1992-01-06
...
l_shipdate=1998-11-24
l_shipdate=1998-11-25
l_shipdate=1998-11-26
l_shipdate=1998-11-27
l_shipdate=1998-11-28
l_shipdate=1998-11-29
l_shipdate=1998-11-30
l_shipdate=1998-12-01
*/
```
1. Execute uma consulta CTAS para criar uma tabela particionada.
O exemplo a seguir cria uma tabela chamada `my_lineitem_parq_partitioned` e usa a cláusula `WHERE ` para restringir `DATE` a um valor anterior a `1992-02-01`. Como o conjunto de dados de exemplo começa com janeiro de 1992, somente partições para janeiro de 1992 são criadas.
```
CREATE table my_lineitem_parq_partitioned
WITH (partitioned_by = ARRAY['l_shipdate']) AS
SELECT l_orderkey,
l_partkey,
l_suppkey,
l_linenumber,
l_quantity,
l_extendedprice,
l_discount,
l_tax,
l_returnflag,
l_linestatus,
l_commitdate,
l_receiptdate,
l_shipinstruct,
l_comment,
l_shipdate
FROM tpch100.lineitem_parq_partitioned
WHERE cast(l_shipdate as timestamp) < DATE ('1992-02-01');
```
1. Execute o comando `SHOW PARTITIONS` para verificar se a tabela contém as partições desejadas.
```
SHOW PARTITIONS my_lineitem_parq_partitioned;
```
As partições no exemplo são de janeiro de 1992.
```
/*
l_shipdate=1992-01-02
l_shipdate=1992-01-03
l_shipdate=1992-01-04
l_shipdate=1992-01-05
l_shipdate=1992-01-06
l_shipdate=1992-01-07
l_shipdate=1992-01-08
l_shipdate=1992-01-09
l_shipdate=1992-01-10
l_shipdate=1992-01-11
l_shipdate=1992-01-12
l_shipdate=1992-01-13
l_shipdate=1992-01-14
l_shipdate=1992-01-15
l_shipdate=1992-01-16
l_shipdate=1992-01-17
l_shipdate=1992-01-18
l_shipdate=1992-01-19
l_shipdate=1992-01-20
l_shipdate=1992-01-21
l_shipdate=1992-01-22
l_shipdate=1992-01-23
l_shipdate=1992-01-24
l_shipdate=1992-01-25
l_shipdate=1992-01-26
l_shipdate=1992-01-27
l_shipdate=1992-01-28
l_shipdate=1992-01-29
l_shipdate=1992-01-30
l_shipdate=1992-01-31
*/
```
1. Use uma instrução `INSERT INTO` para adicionar partições à tabela.
O exemplo a seguir adiciona partições para as datas do mês de fevereiro de 1992.
```
INSERT INTO my_lineitem_parq_partitioned
SELECT l_orderkey,
l_partkey,
l_suppkey,
l_linenumber,
l_quantity,
l_extendedprice,
l_discount,
l_tax,
l_returnflag,
l_linestatus,
l_commitdate,
l_receiptdate,
l_shipinstruct,
l_comment,
l_shipdate
FROM tpch100.lineitem_parq_partitioned
WHERE cast(l_shipdate as timestamp) >= DATE ('1992-02-01')
AND cast(l_shipdate as timestamp) < DATE ('1992-03-01');
```
1. Execute `SHOW PARTITIONS` novamente.
```
SHOW PARTITIONS my_lineitem_parq_partitioned;
```
A tabela de exemplo agora tem partições de janeiro e fevereiro de 1992.
```
/*
l_shipdate=1992-01-02
l_shipdate=1992-01-03
l_shipdate=1992-01-04
l_shipdate=1992-01-05
l_shipdate=1992-01-06
...
l_shipdate=1992-02-20
l_shipdate=1992-02-21
l_shipdate=1992-02-22
l_shipdate=1992-02-23
l_shipdate=1992-02-24
l_shipdate=1992-02-25
l_shipdate=1992-02-26
l_shipdate=1992-02-27
l_shipdate=1992-02-28
l_shipdate=1992-02-29
*/
```
1. Continue a usar instruções `INSERT INTO` que leem e adicionam até 100 partições cada. Continue até atingir o número de partições necessárias.
**Importante**
Ao definir a condição `WHERE`, certifique-se de que as consultas não se sobreponham. Caso contrário, algumas partições podem ter dados duplicados.