Usar CTAS e INSERT INTO para ETL e análise de dados

Você pode usar as instruções Create Table as Select (CTAS) e INSERT INTO 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 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. 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

• Etapa 2: Usar CTAS para particionar, converter e compactar os dados

• Etapa 3: Usar INSERT INTO para adicionar dados

• Etapa 4: Avaliar diferenças de custo e performance

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 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.

2. No editor de consultas do Athena, execute o comando CREATE DATABASE para criar um banco de dados.

   CREATE DATABASE blogdb

3. Execute a seguinte instrução para criar uma tabela.

   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 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/

2. 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.

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

2. 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/

3. 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

4. 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 ter mais informações, consulte Usar CTAS e INSERT INTO para resolver o limite de 100 partições.

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.

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

2. Observe o tempo em que a consulta foi executada e a quantidade de dados verificados.

3. 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

4. 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.

   Tabela	Runtime	Dados examinados
   Original	16,88 segundos	11,35 GB
   Novo	3,79 segundos	428,05 MB

5. 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

6. Observe o tempo em que a consulta foi executada e a quantidade de dados verificados.

7. 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

8. 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.

   Tabela	Runtime	Dados examinados
   Original	18,65 segundos	11,35 GB
   Novo	1,92 segundos	68 MB

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.