Usa CTAS e INSERT INTO per ETL e analisi dei dati

Per creare un database e una tabella in base al set di dati di esempio

1. Nella console Athena, scegli la Regione AWSStati Uniti orientali (Virginia settentrionale). Assicurarsi di eseguire tutte le query in questo tutorial in us-east-1.

2. Nell'editor di query Athena, eseguire il comando CREATE DATABASE per creare un database.

   CREATE DATABASE blogdb

3. Eseguire l'istruzione seguente per creare una tabella.

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

Per convertire i dati in formato Parquet con compressione Snappy e partizionamento per anno

• Esegui la seguente istruzione CTAS, sostituendola your-bucket con la posizione del bucket 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

  In questo esempio, la tabella creata include solo i dati dal 2015 al 2019. Nella fase 3, aggiungi nuovi dati a questa tabella utilizzando il comando INSERT INTO.

Per visualizzare le partizioni e i file parquet creati dall'istruzione CTAS

1. Per mostrare le partizioni create, esegui il comando seguente. AWS CLI Assicurarsi di includere la barra finale (/).

   aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/

   L'output mostra le partizioni.

         PRE year=2015/
         PRE year=2016/
         PRE year=2017/
         PRE year=2018/
         PRE year=2019/

2. Per visualizzare i file Parquet, eseguire il comando seguente. L'opzione | head -5 che limita l'output ai primi cinque risultati, non è disponibile in Windows.

   aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable | head -5

   L'output è simile a quello riportato di seguito.

   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

Per aggiungere dati alla tabella utilizzando una o più istruzioni INSERT INTO

1. Eseguire il seguente comando INSERT INTO, specificando gli anni prima del 2015 nella clausola 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. Eseguire nuovamente il comando aws s3 ls, utilizzando la seguente sintassi.

   aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/

   L'output mostra le nuove partizioni.

         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. Per vedere la riduzione delle dimensioni del set di dati ottenuta utilizzando la compressione e lo storage a colonne in formato Parquet, eseguire il comando seguente.

   aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable --summarize

   I seguenti risultati mostrano che la dimensione del set di dati dopo Parquet con compressione 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 più dati CSV vengono aggiunti alla tabella originale, è possibile aggiungere i dati alla tabella Parquet utilizzando le istruzioni INSERT INTO. Ad esempio, se sono disponibili nuovi dati per l'anno 2020, è possibile eseguire la seguente istruzione INSERT INTO. L'istruzione aggiunge i dati e la partizione pertinente alla tabella 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

   L'istruzione INSERT INTO supporta la scrittura di un massimo di 100 partizioni nella tabella di destinazione. Tuttavia, per aggiungere più di 100 partizioni, è possibile eseguire più istruzioni INSERT INTO. Per ulteriori informazioni, consulta Usa CTAS e INSERT INTO per aggirare il limite di 100 partizioni.

Per misurare i miglioramenti delle performance e le differenze dei costi

1. Eseguire la seguente query nella tabella originale. La query trova il numero di distinti IDs per ogni valore dell'anno.

   SELECT substr("date",1,4) as year,
          COUNT(DISTINCT id)
   FROM original_csv
   GROUP BY 1 ORDER BY 1 DESC

2. Annotare il tempo di esecuzione della query e la quantità di dati analizzati.

3. Eseguire la stessa query nella nuova tabella, annotando il tempo di esecuzione della query e la quantità di dati analizzati.

   SELECT year,
     COUNT(DISTINCT id)
   FROM new_parquet
   GROUP BY 1 ORDER BY 1 DESC

4. Confrontare i risultati e calcolare la differenza delle prestazioni e dei costi. I risultati di esempio riportati di seguito mostrano che la query di test nella nuova tabella è stata più veloce e più economica rispetto alla query nella vecchia tabella.

   Tabella	Runtime	Dati scansionati
   Originale	16,88 secondi	11,35 GB
   Novità	3,79 secondi	428,05 MB

5. Eseguire la seguente query di esempio nella tabella originale. La query calcola la temperatura massima media (Celsius), la temperatura minima media (Celsius) e la piovosità media (mm) per la Terra nel 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. Annotare il tempo di esecuzione della query e la quantità di dati analizzati.

7. Eseguire la stessa query nella nuova tabella, annotando il tempo di esecuzione della query e la quantità di dati analizzati.

   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. Confrontare i risultati e calcolare la differenza delle prestazioni e dei costi. I risultati di esempio riportati di seguito mostrano che la query di test nella nuova tabella è stata più veloce e più economica rispetto alla query nella vecchia tabella.

   Tabella	Runtime	Dati scansionati
   Originale	18,65 secondi	11,35 GB
   Novità	1,92 secondi	68 MB