# Uso de CTAS e INSERT INTO en ETL y análisis de datos
<a name="ctas-insert-into-etl"></a>

Utilice las instrucciones Create Table as Select ([CTAS](ctas.md)) e [INSERT INTO](insert-into.md) 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](select.md) 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](https://aws.amazon.com/glue). 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.

## Descripción general
<a name="ctas-insert-into-etl-overview"></a>

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.

**Steps**
+ [Paso 1: Crear una tabla basada en el conjunto de datos original](#ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset)
+  [Paso 2: Utilizar CTAS para particionar, convertir y comprimir los datos](#ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data) 
+  [Paso 3: Utilizar INSERT INTO para agregar datos](#ctas-insert-into-etl-step-3-use-insert-into-to-add-data) 
+  [Paso 4: Medir las diferencias de rendimiento y costo](#ctas-insert-into-etl-step-4-measure-performance-and-cost-differences) 

## Paso 1: Crear una tabla basada en el conjunto de datos original
<a name="ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset"></a>

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)](https://registry.opendata.aws/noaa-ghcn/) 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 **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`.

1. En el Editor de consultas de Athena, ejecute el comando [CREATE DATABASE](create-database.md) para crear una base de datos. 

   ```
   CREATE DATABASE blogdb
   ```

1. Ejecute la siguiente instrucción para [crear una tabla](create-table.md).

   ```
   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
<a name="ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data"></a>

Después de crear una tabla, puede utilizar una sola instrucción [CTAS](ctas.md) 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-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.

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

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

## Paso 3: Utilizar INSERT INTO para agregar datos
<a name="ctas-insert-into-etl-step-3-use-insert-into-to-add-data"></a>

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](insert-into.md) .

**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
   ```

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

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

1. 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](ctas-insert-into.md).

## Paso 4: Medir las diferencias de rendimiento y costo
<a name="ctas-insert-into-etl-step-4-measure-performance-and-cost-differences"></a>

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](https://aws.amazon.com/athena/pricing).

**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
   ```

1. Apunte la hora a la que se ejecutó la consulta y la cantidad de datos analizados.

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

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.  
****    
[See the AWS documentation website for more details](http://docs.aws.amazon.com/es_es/athena/latest/ug/ctas-insert-into-etl.html)

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

1. Apunte la hora a la que se ejecutó la consulta y la cantidad de datos analizados.

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

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.  
****    
[See the AWS documentation website for more details](http://docs.aws.amazon.com/es_es/athena/latest/ug/ctas-insert-into-etl.html)

## Resumen
<a name="ctas-insert-into-etl-summary"></a>

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.