# Usar CTAS e INSERT INTO para ETL e análise de dados
<a name="ctas-insert-into-etl"></a>

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
<a name="ctas-insert-into-etl-overview"></a>

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
<a name="ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset"></a>

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
<a name="ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data"></a>

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
<a name="ctas-insert-into-etl-step-3-use-insert-into-to-add-data"></a>

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
<a name="ctas-insert-into-etl-step-4-measure-performance-and-cost-differences"></a>

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
<a name="ctas-insert-into-etl-summary"></a>

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.