Uso de CTAS e INSERT INTO en ETL y análisis de datos - Amazon Athena

Uso de CTAS e INSERT INTO en ETL y análisis de datos

Utilice las instrucciones Create Table as Select (CTAS) e INSERT INTO en Athena para extraer, transformar y cargar (ETL) datos en Amazon S3 para el procesamiento de datos. En este tema se muestra cómo utilizar estas instrucciones para crear particiones y convertir un conjunto de datos al formato de datos en columnas a fin de optimizarlo para el análisis de datos.

Las instrucciones CTAS utilizan consultas SELECT estándar para crear nuevas tablas. Puede utilizar una instrucción CTAS para crear un subconjunto de datos para su análisis. En una instrucción CTAS, puede particionar los datos, especificar su compresión y convertir los datos en un formato de columnas como Apache Parquet o Apache ORC. Cuando ejecuta la consulta CTAS, las tablas y particiones que crea se añaden automáticamente a AWS Glue Data Catalog. Esto hace que las nuevas tablas y particiones que crea estén disponibles inmediatamente para consultas posteriores.

Las instrucciones INSERT INTO insertan nuevas filas en una tabla de destino basándose en una instrucción de consulta SELECT que se ejecuta en una tabla de origen. Puede utilizar instrucciones INSERT INTO para transformar y cargar datos de tabla de origen en formato CSV en datos de tabla de destino utilizando todas las transformaciones compatibles con CTAS.

Overview

En Athena, utilice una instrucción CTAS para realizar una conversión inicial por lotes de los datos. A continuación, utilice varias instrucciones INSERT INTO para realizar actualizaciones incrementales en la tabla creada por la instrucción CTAS.

Paso 1: Crear una tabla basada en el conjunto de datos original

En el ejemplo de este tema se utiliza un subconjunto legible de Amazon S3 del conjunto de datos Diario de la Red Global de Climatología Histórica de NOAA (GHCN-D) disponible de forma pública. Los datos de Amazon S3 tienen las siguientes características.

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

Los datos originales se almacenan en Amazon S3 sin particiones. Los datos están en formato CSV en archivos como el siguiente.

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

Los tamaños de archivo de esta muestra son relativamente pequeños. Al fusionarlos en archivos más grandes, puede reducir el número total de archivos, lo que permite ejecutar mejor las consultas. Puede utilizar instrucciones CTAS e INSERT INTO para mejorar el rendimiento de la consulta.

Para crear una base de datos y una tabla basadas en el conjunto de datos de ejemplo

  1. En la consola de Athena, elija la región de AWS EE. UU. Este (Norte de Virginia). Asegúrese de ejecutar todas las consultas de este tutorial en us-east-1.

  2. En el Editor de consultas de Athena, ejecute el comando CREATE DATABASE para crear una base de datos.

    CREATE DATABASE blogdb
  3. Ejecute la siguiente instrucción para crear una tabla.

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

Paso 2: Usar CTAS para particionar, convertir y comprimir los datos

Después de crear una tabla, puede utilizar una sola instrucción CTAS para convertir los datos al formato Parquet con compresión Snappy y para particionar los datos por año.

La tabla que creó en el paso 1 tiene un campo date con formato de fecha YYYYMMDD (por ejemplo, 20100104). Dado que la nueva tabla se particionará en year, la instrucción de ejemplo del procedimiento siguiente utiliza la función Presto substr("date",1,4) para extraer el valor year del campo date.

Para convertir los datos al formato Parquet con compresión Snappy, realizando el particionado por año

  • Ejecute la siguiente instrucción CTAS, reemplazando your-bucket por la ubicación de su bucket de Amazon S3.

    CREATE table new_parquet WITH (format='PARQUET', parquet_compression='SNAPPY', partitioned_by=array['year'], external_location = 's3://your-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

    En este ejemplo, la tabla que ha creado solo incluye los datos de 2015 a 2019. En el paso 3, añade nuevos datos a esta tabla mediante el comando INSERT INTO.

Cuando finaliza la consulta, se utiliza el siguiente procedimiento para comprobar el resultado en la ubicación de Amazon S3 especificada en la instrucción CTAS.

Para ver las particiones y los archivos de parquet creados por la instrucción CTAS

  1. Para mostrar las particiones creadas, ejecute el siguiente comando en la AWS CLI. Asegúrese de incluir la barra diagonal (/) al final.

    aws s3 ls s3://your-bucket/optimized-data/

    La salida muestra las particiones.

    PRE year=2015/ PRE year=2016/ PRE year=2017/ PRE year=2018/ PRE year=2019/
  2. Para ver los archivos Parquet, ejecute el siguiente comando. Tenga en cuenta que la opción | head -5 ,que restringe la salida a los cinco primeros resultados, no está disponible para Windows.

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

    La salida se parece a la siguiente.

    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

Paso 3: Utilice INSERT INTO para añadir datos

En el paso 2, utilizó CTAS para crear una tabla con particiones para los años 2015 a 2019. Sin embargo, el conjunto de datos original también contiene datos para los años 2010 a 2014. Ahora añada esos datos usando una instrucción INSERT INTO .

Para añadir datos a la tabla mediante una o más instrucciones INSERT INTO

  1. Ejecute el siguiente comando INSERT INTO, especificando los años anteriores a 2015 en la 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. Vuelva a ejecutar el comando aws s3 ls, utilizando la sintaxis siguiente.

    aws s3 ls s3://your-bucket/optimized-data/

    La salida muestra las nuevas particiones.

    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 la reducción en el tamaño del conjunto de datos obtenido mediante compresión y almacenamiento en columnas en formato Parquet, ejecute el siguiente comando.

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

    Los siguientes resultados muestran que el tamaño del conjunto de datos después de Parquet con compresión Snappy es de 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. Si se añaden más datos CSV a la tabla original, puede añadir esos datos a la tabla Parquet mediante instrucciones INSERT INTO. Por ejemplo, si quiere insertar nuevos datos para el año 2020, puede ejecutar la siguiente instrucción INSERT INTO. La instrucción añade los datos y la partición relevante a la tabla 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

    La instrucción INSERT INTO admite escribir un máximo de 100 particiones en la tabla de destino. Sin embargo, para agregar más de 100 particiones, puede ejecutar varias instrucciones INSERT INTO. Para obtener más información, consulte Uso de CTAS e INSERT INTO para crear una tabla con más de 100 particiones.

Paso 4: Medir las diferencias de rendimiento y costo

Después de transformar los datos, puede medir las ganancias de rendimiento y el ahorro de costes ejecutando las mismas consultas en las tablas nuevas y antiguas y comparando los resultados.

nota

Para obtener información sobre los costos por consulta de Athena, consulte Precios de Amazon Athena.

Para medir las ganancias de rendimiento y las diferencias de costos

  1. Ejecute la siguiente consulta en la tabla original. La consulta busca el número de identificadores distintos para cada valor del año.

    SELECT substr("date",1,4) as year, COUNT(DISTINCT id) FROM original_csv GROUP BY 1 ORDER BY 1 DESC
  2. Apunte la hora a la que se ejecutó la consulta y la cantidad de datos analizados.

  3. Ejecute la misma consulta en la nueva tabla, y anote el tiempo de ejecución de la consulta y la cantidad de datos analizados.

    SELECT year, COUNT(DISTINCT id) FROM new_parquet GROUP BY 1 ORDER BY 1 DESC
  4. Compare los resultados y calcule la diferencia de rendimiento y costo. Los siguientes resultados de ejemplo muestran que la consulta de prueba de la nueva tabla era más rápida y barata que la consulta de la tabla anterior.

    Tabla Tiempo de ejecución Datos escaneados
    Original 16,88 segundos 11,35 GB
    New 3,79 segundos 428,05 MB
  5. Ejecute la siguiente consulta de ejemplo en la tabla original. La consulta calcula la temperatura máxima promedio (Celsius), la temperatura mínima promedio (Celsius) y la precipitación media (mm) de la Tierra 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. Apunte la hora a la que se ejecutó la consulta y la cantidad de datos analizados.

  7. Ejecute la misma consulta en la nueva tabla, y anote el tiempo de ejecución de la consulta y la cantidad de datos analizados.

    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 los resultados y calcule la diferencia de rendimiento y costo. Los siguientes resultados de ejemplo muestran que la consulta de prueba de la nueva tabla era más rápida y barata que la consulta de la tabla anterior.

    Tabla Tiempo de ejecución Datos escaneados
    Original 18,65 segundos 11,35 GB
    New 1,92 segundos 68 MB

Summary

En este tema se muestra cómo realizar operaciones ETL mediante instrucciones CTAS e INSERT INTO en Athena. Ha realizado el primer conjunto de transformaciones mediante una instrucción CTAS que ha convertido los datos al formato Parquet con compresión Snappy. La instrucción CTAS también convirtió el conjunto de datos de no particionado a particionado. Esto redujo su tamaño y redujo los costos de ejecución de las consultas. Cuando haya nuevos datos disponibles, puede utilizar una instrucción INSERT INTO para transformar y cargar los datos en la tabla que creó con la instrucción CTAS.