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
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.
Información general
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.
Pasos
Paso 1: Crear una tabla basada en el conjunto de datos original
En el ejemplo de este tema, se utiliza un subconjunto legible de Simple Storage Service (Amazon S3) del conjunto de datos Diario de la Red Global de Climatología Histórica de NOAA (GHCN-D)
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
-
En la consola de Athena, elija US East (N. Virginia) (Este de EE. UU. [Norte de Virginia]) como Región de AWS. Asegúrese de ejecutar todas las consultas de este tutorial en
us-east-1. -
En el Editor de consultas de Athena, ejecute el comando CREATE DATABASE para crear una base de datos.
CREATE DATABASE blogdb -
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: Utilizar 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 haciendo particiones por año
-
Ejecute la siguiente instrucción CTAS, reemplazando
your-bucketpor 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://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) <= 2019nota
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
-
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://amzn-s3-demo-bucket/optimized-data/La salida muestra las particiones.
PRE year=2015/ PRE year=2016/ PRE year=2017/ PRE year=2018/ PRE year=2019/ -
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://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable | head -5La 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: Utilizar INSERT INTO para agregar 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
-
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 -
Vuelva a ejecutar el comando
aws s3 ls, utilizando la sintaxis siguiente.aws s3 ls s3://amzn-s3-demo-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/ -
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://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable --summarizeLos 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 -
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) = 2020nota
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 evitar el límite 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
-
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 -
Apunte la hora a la que se ejecutó la consulta y la cantidad de datos analizados.
-
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 -
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 -
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 -
Apunte la hora a la que se ejecutó la consulta y la cantidad de datos analizados.
-
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 -
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
Resumen
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.
