UtilisationCTAS, INSERT INTO pour ETL et analyse des données

Pour créer une base de données et une table à partir de l'exemple de jeu de données

1. Dans la console Athena, choisissez Région AWSUSA Est (Virginie du Nord). Assurez-vous d'exécuter toutes les requêtes dans ce didacticiel dans us-east-1.

2. Dans l'éditeur de requêtes Athena, exécutez la CREATEDATABASEcommande pour créer une base de données.

   CREATE DATABASE blogdb

3. Exécutez l'instruction suivante pour créer une table.

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

Pour convertir les données au format parquet avec compression snappy, partitionnement par année

• Exécutez l'CTASinstruction suivante, en remplaçant your-bucket avec l'emplacement de votre compartiment 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

  Note

  Dans cet exemple, la table que vous créez inclut uniquement les données de 2015 à 2019. À l'étape 3, vous ajoutez de nouvelles données à cette table à l'aide de la INSERT INTO commande.

Pour voir les partitions et les fichiers de parquet créés par la CTAS déclaration

1. Pour afficher les partitions créées, exécutez la AWS CLI commande suivante. Assurez-vous d'inclure la barre oblique finale (/).

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

   Le résultat affiche les partitions.

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

2. Pour afficher les fichiers Parquet, exécutez la commande suivante. Notez que l'option | head -5 qui limite la sortie aux cinq premiers résultats, n'est pas disponible sous Windows.

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

   La sortie se présente comme suit :

   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

Pour ajouter des données à la table à l'aide d'une ou de plusieurs INSERT INTO instructions

1. Exécutez la INSERT INTO commande suivante, en spécifiant les années antérieures à 2015 dans la WHERE clause.

   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. Exécutez à nouveau la commande aws s3 ls en utilisant la syntaxe suivante.

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

   Le résultat affiche les nouvelles partitions.

         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. Pour voir la réduction de la taille du jeu de données obtenue à l'aide de la compression et du stockage en colonnes au format Parquet, exécutez la commande suivante.

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

   Les résultats suivants montrent que la taille du jeu de données après Parquet avec compression Snappy est de 1,2 Go.

   ...
   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. Si d'autres CSV données sont ajoutées à la table d'origine, vous pouvez les ajouter à la table en parquet à l'aide d'INSERTINTOinstructions. Par exemple, si vous disposez de nouvelles données pour l'année 2020, vous pouvez exécuter l'INSERTINTOinstruction suivante. L'instruction ajoute les données et la partition correspondante à la table 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

   Note

   L'INSERTINTOinstruction prend en charge l'écriture d'un maximum de 100 partitions dans la table de destination. Toutefois, pour ajouter plus de 100 partitions, vous pouvez exécuter plusieurs INSERT INTO instructions. Pour de plus amples informations, veuillez consulter Utilisez CTAS et INSERT INTO pour contourner la limite de 100 partitions.

Mesurer les gains de performance et les différences de coûts

1. Exécutez la requête suivante sur la table d'origine. La requête trouve le nombre de caractères distincts IDs pour chaque valeur de l'année.

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

2. Notez la durée d'exécution de la requête et la quantité de données analysées.

3. Exécutez la même requête sur la nouvelle table, en notant le temps d'exécution de la requête et la quantité de données analysées.

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

4. Comparez les résultats et calculez la différence de performance et de coût. Les exemples de résultats suivants montrent que la requête de test sur la nouvelle table a été plus rapide et moins chère que la requête sur l'ancienne table.

   Tableau	Environnement d’exécution	Données analysées
   Original	16,88 secondes	11,35 Go
   New	3,79 secondes	428,05 Mo

5. Exécutez l'exemple de requête suivant sur la table d'origine. La requête calcule la température maximale moyenne (Celsius), la température minimale moyenne (Celsius) et la pluviométrie moyenne (mm) pour la Terre en 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. Notez la durée d'exécution de la requête et la quantité de données analysées.

7. Exécutez la même requête sur la nouvelle table, en notant le temps d'exécution de la requête et la quantité de données analysées.

   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. Comparez les résultats et calculez la différence de performance et de coût. Les exemples de résultats suivants montrent que la requête de test sur la nouvelle table a été plus rapide et moins chère que la requête sur l'ancienne table.

   Tableau	Environnement d’exécution	Données analysées
   Original	18,65 secondes	11,35 Go
   New	1,92 secondes	68 Mo