Utilisation de CTAS et INSERT INTO pour ETL et l'analyse des données - Amazon Athena
PrésentationÉtape 1 : créez une table basée sur le jeu de données d'origineÉtape 2 : utiliser CTAS pour partitionner, convertir et compresser les donnéesÉtape 3 : utilisez INSERT INTO pour ajouter des donnéesÉtape 4 : mesurez les différences de performance et de coûtsRécapitulatif

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Utilisation de CTAS et INSERT INTO pour ETL et l'analyse des données

Vous pouvez utiliser les instructions Create Table as Select (CTAS) et INSERT INTO dans Athena pour extraire, transformer et charger (ETL, extract-transform-load) des données dans Simple Storage Service (Amazon S3) pour leur traitement. Cette rubrique montre comment utiliser ces instructions pour partitionner et convertir un jeu de données au format de données en colonnes, afin de l'optimiser pour l'analyse des données.

Les instructions CTAS utilisent des requêtes SELECT standard pour créer de nouvelles tables. Vous pouvez utiliser une instruction CTAS pour créer un sous-ensemble de vos données à analyser. Dans une instruction CTAS, vous pouvez partitionner les données, spécifier la compression et convertir les données dans un format en colonnes comme Apache Parquet ou Apache ORC. Lorsque vous exécutez la requête CTAS, les tables et partitions qu'elle crée sont automatiquement ajoutées au AWS Glue Data Catalog. Ainsi, les nouvelles tables et partitions créées sont immédiatement disponibles pour les requêtes suivantes.

Les instructions INSERT INTO insèrent de nouvelles lignes dans une table de destination en fonction d'une instruction de requête SELECT exécutée sur une table source. Vous pouvez utiliser les instructions INSERT INTO pour transformer et charger les données de table source au format CSV en données de table de destination à l'aide de toutes les transformations prises en charge par CTAS.

Présentation

Dans Athena, utilisez une instruction CTAS pour effectuer une conversion initiale par lots des données. Utilisez ensuite plusieurs instructions INSERT INTO pour effectuer des mises à jour incrémentielles de la table créée par l'instruction CTAS.

Étapes

Étape 1 : créez une table basée sur le jeu de données d'origine

L'exemple de cette rubrique utilise un sous-ensemble Simple Storage Service (Amazon S3) lisible du jeu de données NOAA Global Historical Climatology Network Daily (GHCN-D) accessible au public. Les données sur Simple Storage Service (Amazon S3) possèdent les caractéristiques suivantes.

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

Les données d'origine sont stockées dans Simple Storage Service (Amazon S3) sans partitions. Les données sont au format CSV dans des fichiers comme ceux ci-dessous.

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

Les tailles de fichier dans cet échantillon sont relativement petites. En les fusionnant dans des fichiers plus grands, vous pouvez réduire le nombre total de fichiers, ce qui permet d'augmenter les performances des requêtes. Vous pouvez utiliser les instructions CTAS et INSERT INTO pour améliorer les performances des requêtes.

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 l'est des États-Unis (Virginie du Nord). Région AWS Assurez-vous d'exécuter toutes les requêtes dans ce didacticiel dans us-east-1.

  2. Dans l'éditeur de requête Athena, exécutez la commande CREATE DATABASE (CRÉER UNE BASE DE DONNÉES) 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/'

Étape 2 : utiliser CTAS pour partitionner, convertir et compresser les données

Après avoir créé une table, vous pouvez utiliser une seule instruction CTAS pour convertir les données au format Parquet avec compression Snappy et pour partitionner les données par année.

Le tableau que vous avez créé à l'Étape 1 comporte un champ date dont la date est au format YYYYMMDD (par exemple, 20100104). Étant donné que la nouvelle table sera partitionnée par year, l'exemple d'instruction de la procédure suivante utilise la fonction Presto substr("date",1,4) pour extraire la valeur year du champ date.

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

  • Exécutez l'instruction CTAS suivante, en remplaçant votre-compartiment par l'emplacement de votre compartiment Simple Storage Service (Amazon S3).

    CREATE table new_parquet
WITH (format='PARQUET',
parquet_compression='SNAPPY',
partitioned_by=array['year'],
external_location = 's3://DOC-EXAMPLE-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 à ce tableau à l'aide de la commande INSERT INTO.

Une fois la requête terminée, utilisez la procédure suivante pour vérifier le résultat à l'emplacement Simple Storage Service (Amazon S3) spécifié dans l'instruction CTAS.

Pour voir les partitions et les fichiers Parquet créés par l'instruction CTAS

  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://DOC-EXAMPLE-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://DOC-EXAMPLE-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

Étape 3 : utilisez INSERT INTO pour ajouter des données

À l'Étape 2, vous avez utilisé CTAS pour créer une table avec des partitions pour les années 2015 à 2019. Toutefois, le jeu de données d'origine contient également des données pour les années 2010 à 2014. Maintenant, vous ajoutez ces données à l'aide d'une instruction INSERT INTO.

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

  1. Exécutez la commande INSERT INTO suivante, en spécifiant les années avant 2015 dans la clause OÙ.

    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://DOC-EXAMPLE-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://DOC-EXAMPLE-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 données CSV sont ajoutées à la table d'origine, vous pouvez ajouter ces données à la table Parquet à l'aide d'instructions INSERT INTO. Par exemple, si vous possédez de nouvelles données pour l'année 2020, vous pouvez exécuter l'instruction INSERT INTO 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'instruction INSERT INTO prend en charge l'écriture de 100 partitions au maximum dans la table de destination. Toutefois, pour ajouter plus de 100 partitions, vous pouvez exécuter plusieurs instructions INSERT INTO. Pour plus d’informations, consultez Utilisation de CTAS et de INSERT INTO pour contourner la limite de 100 partitions.

Étape 4 : mesurez les différences de performance et de coûts

Après avoir transformé les données, vous pouvez mesurer les gains en termes de performance et d'économies en exécutant les mêmes requêtes sur les nouvelles tables et les anciennes, puis en comparant les résultats.

Note

Pour obtenir des informations sur les coûts par requête Athena, consultez la rubrique Tarification Amazon Athena.

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 recherche le nombre d'ID distincts 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

Récapitulatif

Cette rubrique vous a montré comment effectuer des opérations ETL à l'aide des instructions CTAS et INSERT INTO dans Athena. Vous avez effectué le premier ensemble de transformations à l'aide d'une instruction CTAS qui a converti les données au format Parquet avec compression Snappy. L'instruction CTAS a également converti le jeu de données non partitionné en partitionné. Vous avez ainsi réduit sa taille et les coûts d'exécution des requêtes. Lorsque de nouvelles données sont disponibles, vous pouvez utiliser une instruction INSERT INTO pour transformer et charger les données dans la table que vous avez créée avec l'instruction CTAS.