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

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

1. 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.

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

Para convertir los datos al formato Parquet con compresión Snappy haciendo particiones 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://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

  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.

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

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://amzn-s3-demo-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

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

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://amzn-s3-demo-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 evitar el límite de 100 particiones.

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