# Creación de una tabla a partir de los resultados de una consulta (CTAS) Una consulta `CREATE TABLE AS SELECT` (CTAS) crea una nueva tabla en Athena a partir de los resultados de una instrucción `SELECT` de otra consulta. Athena almacena los archivos de datos creados por la instrucción CTAS en una ubicación especificada en Amazon S3. Para ver la sintaxis, consulte [CREATE TABLE AS](create-table-as.md). `CREATE TABLE AS` combina una instrucción DDL `CREATE TABLE` con una instrucción DML `SELECT` y, por lo tanto, técnicamente contiene tanto DDL como DML. Sin embargo, tenga en cuenta que, a efectos de Service Quotas, las consultas CTAS en Athena se tratan como DML. Para obtener información acerca de Service Quotas de Athena, consulte [Service Quotas](service-limits.md). Utilice consultas CTAS para: + Crear tablas a partir de los resultados de una consulta en un solo paso, sin tener que consultar una y otra vez conjuntos de datos sin formato. De ese modo, resulta más sencillo trabajar con conjuntos de datos sin formato. + Transformar los resultados de las consultas y migrar las tablas a otros formatos de tabla, como Apache Iceberg. De esta manera, se mejora el rendimiento de las consultas y se reducen los costos de las consultas en Athena. Para obtener más información, consulta [Creación de tablas de Iceberg](querying-iceberg-creating-tables.md). + Transformar los resultados de las consultas en formatos de almacenamiento, como Parquet y ORC. De esta manera, se mejora el rendimiento de las consultas y se reducen los costos de las consultas en Athena. Para obtener más información, consulta [Uso de formatos de almacenamiento en columnas](columnar-storage.md). + Crear copias de tablas existentes que contengan solo los datos que necesita. **Topics** + [Consideraciones y limitaciones de las consultas CTAS](ctas-considerations-limitations.md) + [Creación de consultas de CTAS](ctas-console.md) + [Ejemplos de CTAS](ctas-examples.md) + [Uso de CTAS e INSERT INTO en ETL](ctas-insert-into-etl.md) + [Solución alternativa para el límite de 100 particiones](ctas-insert-into.md) # Consideraciones y limitaciones de las consultas CTAS En las siguientes secciones se detallan las consideraciones y limitaciones que se deben tener en cuenta cuando se utilicen consultas `CREATE TABLE AS SELECT` (CTAS) en Athena. ## Información sobre la sintaxis de las consultas de CTAS La sintaxis de las consultas CTAS es diferente de la sintaxis de `CREATE [EXTERNAL] TABLE` utilizada para la creación de tablas. Consulte [CREATE TABLE AS](create-table-as.md). ## La diferencia entre las vistas y las consultas de CTAS Las consultas de CTAS escriben nuevos datos en una ubicación especificada en Amazon S3. Las vistas no escriben ningún dato. ## Especificación de una ubicación para los resultados de las consultas de CTAS Si el grupo de trabajo [anula la configuración del lado del cliente](workgroups-settings-override.md) para la ubicación de resultados de consulta, Athena crea la tabla en la ubicación `s3://amzn-s3-demo-bucket/tables//`. Para ver la ubicación de los resultados de la consulta especificada para el grupo de trabajo, [consulte los detalles del grupo de trabajo](viewing-details-workgroups.md). Si el grupo de trabajo no reemplaza la ubicación de los resultados de la consulta, puede utilizar la sintaxis `WITH (external_location ='s3://amzn-s3-demo-bucket/')` de la consulta CTAS para especificar dónde se almacenan los resultados de la consulta CTAS. **nota** La propiedad `external_location` debe especificar una ubicación vacía. Una consulta CTAS comprueba que la ubicación de ruta (prefijo) del bucket esté vacía y nunca sobrescribe los datos si la ubicación ya contiene datos. Para volver a utilizar la misma ubicación, elimine los datos de la ubicación del prefijo de clave en el bucket. Si omite la sintaxis `external_location` y no utiliza la configuración del grupo de trabajo, Athena utiliza la [configuración del lado del cliente](query-results-specify-location-console.md) para la ubicación de resultados de la consulta y crea la tabla en la ubicación `s3://amzn-s3-demo-bucket////tables//`. ## Ubicación de archivos huérfanos Si una instrucción `CTAS` o `INSERT INTO` produce un error, es posible que queden archivos de datos huérfanos en la ubicación de datos de destino de consultas fallidas o canceladas. Debido a que en algunos casos Athena no elimina datos del bucket de desino de su consulta, es posible que se puedan incluir esos datos parciales en consultas posteriores. Para localizar archivos huérfanos para su inspección o eliminación, puede utilizar el archivo de manifiesto de datos que Athena proporciona para realizar un seguimiento de la lista de archivos que se van a escribir. En algunos casos excepcionales en los que una consulta de Athena falló abruptamente, es posible que el archivo de manifiesto no esté presente. Puede inspeccionar manualmente la ubicación de S3 de destino para encontrar los archivos huérfanos. Para obtener más información, consulte [Identificar los archivos de salida de consultas](querying-finding-output-files.md#querying-identifying-output-files) y [DataManifestLocation](https://docs.aws.amazon.com/athena/latest/APIReference/API_QueryExecutionStatistics.html#athena-Type-QueryExecutionStatistics-DataManifestLocation). Recomendamos encarecidamente utilizar Apache Iceberg para lograr transacciones atómicas de tablas. Para obtener más información, consulte [Consulta de tablas de Apache Iceberg](querying-iceberg.md). ## Recordatorio sobre que las cláusulas ORDER BY se ignoran En una consulta CTAS, Athena ignora las cláusulas `ORDER BY` en la parte `SELECT` de la consulta. Según la especificación SQL (ISO 9075, parte 2), el orden de las filas de una tabla especificada por una expresión de consulta solo está garantizado para la expresión de consulta que contenga inmediatamente la cláusula `ORDER BY`. En cualquier caso, las tablas de SQL están intrínsecamente desordenadas y, si se implementa `ORDER BY` en las cláusulas de subconsulta, la consulta tiene un rendimiento deficiente y no se obtiene un resultado ordenado. Por lo tanto, en las consultas CTAS de Athena, no hay garantía de que el orden especificado en la cláusula `ORDER BY` se conserve cuando se escriban los datos. ## Elección de un formato para almacenar los resultados de la consulta Puede almacenar los resultados de las consultas CTAS en `PARQUET`, `ORC`, `AVRO`, `JSON` y `TEXTFILE`. Los delimitadores de múltiples caracteres no son compatibles con el formato `TEXTFILE` CTAS. Si no especifica un formato de almacenamiento de datos, los resultados de las consultas de CTAS se almacenan en Parquet de manera predeterminada. Las consultas CTAS no requieren que se especifique un SerDe para interpretar las trasformaciones de datos. Consulte [Example: Writing query results to a different format](ctas-examples.md#ctas-example-format). ## Consideración de los formatos de compresión `GZIP`La compresión se utiliza para los resultados de las consultas CTAS en los formatos JSON y TEXTFILE. Para Parquet, puede utilizar `GZIP` o `SNAPPY`; el valor predeterminado es `GZIP`. Para Parquet, puede utilizar `LZ4`, `SNAPPY`, `ZLIB` o `ZSTD`; el valor predeterminado es `ZLIB`. Para obtener ejemplos de CTAS que especifican compresión, consulte [Example: Specifying data storage and compression formats](ctas-examples.md#ctas-example-compression). Para obtener más información sobre la compresión en Athena, consulte [Uso de la compresión en Athena](compression-formats.md). ## Partición y agrupación en buckets de los resultados Puede particionar y guardar en buckets los datos de los resultados de una consulta CTAS. Para especificar las propiedades de la tabla de destino, incluya los predicados de particiones y buckets al final de la cláusula `WITH`. Para obtener más información, consulte [Uso de particiones y asignación de buckets](ctas-partitioning-and-bucketing.md) y [Example: Creating bucketed and partitioned tables](ctas-examples.md#ctas-example-bucketed). Al utilizar CTAS para crear una tabla particionada, Athena tiene un límite de escritura de 100 particiones. Para obtener información sobre cómo evitar la limitación de 100 particiones, consulte [Uso de CTAS e INSERT INTO para evitar el límite de 100 particiones](ctas-insert-into.md). ## Cifrado de los resultados Puede cifrar los resultados de las consultas CTAS en Amazon S3, del mismo modo que cifra los resultados de otras consultas en Athena. Para obtener más información, consulte [Cifrado de los resultados de las consultas de Athena en Amazon S3](encrypting-query-results-stored-in-s3.md). ## La configuración de propietario del bucket esperada no se aplica a CTAS Para las instrucciones CTAS, la configuración del propietario del bucket esperado no se aplica a la ubicación de la tabla de destino en Amazon S3. La configuración del propietario esperado del bucket se aplica solo a la ubicación de salida de Amazon S3 especificada para los resultados de las consultas de Athena. Para obtener más información, consulte [Especificación de una ubicación de resultados de consulta mediante la consola de Athena](query-results-specify-location-console.md). ## Los tipos de datos de columna se conservan Los tipos de datos de columna de una consulta CTAS son los mismos que los que se especifican para la consulta original. # Creación de consultas de CTAS en la consola de Athena En la consola de Athena, se puede crear una consulta CTAS a partir de otra consulta. **Para crear una consulta CTAS a partir de otra consulta** 1. Ejecute la consulta en el editor de consultas de la consola de Athena. 1. En la parte inferior del editor de consultas, elija la opción **Create** (Crear) y, a continuación, elija **Table from query** (Tabla a partir de consulta). 1. En el formulario **Create table as select** (Crear tabla basada en la selección), complete los campos como se indica a continuación: 1. En **Table name** (Nombre de la tabla), ingrese el nombre de la nueva tabla. Utilice únicamente minúsculas y caracteres de subrayado, como `my_select_query_parquet`. 1. En **Database configuration** (Configuración de la base de datos), utilice las opciones para elegir una base de datos existente o crear una. 1. (Opcional) En **Result configuration** (Configuración de resultados), para **Location of CTAS query results** (Ubicación de los resultados de la consulta de CTAS), si la configuración de ubicación de los resultados de la consulta de grupo de trabajo no anula esta opción, haga una de las siguientes acciones: + Ingrese la ruta a una ubicación de S3 existente en el cuadro de búsqueda o seleccione **Browse S3** (Examinar S3) para elegir una ubicación de una lista. + Elija **View** (Ver) para abrir la página **Buckets** (Buckets) de la consola de Amazon S3, donde podrá ver más información sobre sus buckets existentes y elegir o crear un bucket con su propia configuración. Debería especificar una ubicación vacía en Amazon S3 en la que se generarán los datos. Si ya existen datos en la ubicación especificada, se produce un error en la consulta. Si el grupo de trabajo anula la configuración del cliente para la ubicación de resultados de consulta, Athena crea la tabla en la ubicación `s3://amzn-s3-demo-bucket/tables/query_id/`. 1. En **Data format** (Formato de datos), especifique el formato en el que se encuentran los datos. + **Table type** (Tipo de tabla): el tipo de tabla predeterminado en Athena es Apache Hive. + **File format** (Formato de archivo): elija entre opciones como CSV, TSV, JSON, Parquet u ORC. Para obtener más información sobre los formatos Parquet y ORC, consulte [Uso de formatos de almacenamiento en columnas](columnar-storage.md). + **Write compression** (Compresión de escritura): (opcional) elija un formato de compresión. Athena admite una variedad de formatos de compresión para leer y escribir datos, como la lectura de una tabla que utiliza varios formatos de compresión. Por ejemplo, Athena puede leer correctamente los datos de una tabla que utiliza el formato de archivo Parquet cuando algunos archivos Parquet se comprimen con Snappy y otros archivos Parquet se comprimen con GZIP. El mismo principio se aplica a los formatos de almacenamiento ORC, archivo de texto y JSON. Para obtener más información, consulte [Uso de la compresión en Athena](compression-formats.md). + **Partitions** (Particiones): (opcional) seleccione las columnas que quiera particionar. La partición de los datos restringe el volumen de datos que explora cada consulta, lo que mejora el rendimiento y reduce los costos. Puede particionar datos por cualquier clave. Para obtener más información, consulte [Partición de datos](partitions.md). + **Buckets** (Buckets): (opcional) seleccione las columnas que quiera agrupar en buckets. La agrupación en buckets es una técnica que agrupa los datos en función de columnas específicas dentro de una sola partición. Estas columnas se conocen como *claves de bucket*. Al agrupar los datos relacionados en un solo bucket (un archivo dentro de una partición), se reduce significativamente la cantidad de datos escaneados por Athena, lo que mejora el rendimiento de las consultas y reduce los costos. Para obtener más información, consulte [Uso de particiones y asignación de buckets](ctas-partitioning-and-bucketing.md). 1. En **Preview table query** (Vista previa de consulta de tablas), revise la consulta. Para ver la sintaxis de la consulta, consulte [CREATE TABLE AS](create-table-as.md). 1. Seleccione **Create table (Creación de tabla)**. La consola de Athena tiene una plantilla SQL que también puede utilizar para crear una consulta de CTAS. **Para crear una consulta CTAS con una plantilla de SQL** Utilice la plantilla `CREATE TABLE AS SELECT` para crear una consulta CTAS en el editor de consultas. 1. En la consola de Athena, junto a **Tables and views** (Tablas y vistas), elija **Create table** (Crear tabla) y, a continuación, elija **CREATE TABLE AS SELECT**. De este modo, se rellena el editor de consultas con una consulta CTAS con valores de marcador de posición. 1. En el editor de consultas, edite la consulta según sea necesario. Para ver la sintaxis de la consulta, consulte [CREATE TABLE AS](create-table-as.md). 1. Seleccione **Ejecutar**. Para ver ejemplos, consulta [Ejemplos de consultas CTAS](ctas-examples.md). # Ejemplos de consultas CTAS Utilice los siguientes ejemplos para crear consultas CTAS. Para obtener información sobre la sintaxis CTAS, consulte [CREATE TABLE AS](create-table-as.md). En esta sección: + [Example: Duplicating a table by selecting all columns](#ctas-example-dupe-table) + [Example: Selecting specific columns from one or more tables](#ctas-example-specify-columns) + [Example: Creating an empty copy of an existing table](#ctas-example-empty-table) + [Example: Specifying data storage and compression formats](#ctas-example-compression) + [Example: Writing query results to a different format](#ctas-example-format) + [Example: Creating unpartitioned tables](#ctas-example-unpartitioned) + [Example: Creating partitioned tables](#ctas-example-partitioned) + [Example: Creating bucketed and partitioned tables](#ctas-example-bucketed) + [Example: Creating an Iceberg table with Parquet data](#ctas-example-iceberg-parquet) + [Example: Creating an Iceberg table with Avro data](#ctas-example-iceberg-avro) + [Example: Creating an S3 table using CTAS](#ctas-example-s3-table) **Example Ejemplo : duplicación de una tabla mediante la selección de todas las columnas** En el siguiente ejemplo se crea una tabla copiando todas las columnas de una tabla: ``` CREATE TABLE new_table AS SELECT * FROM old_table; ``` En la siguiente variante del ejemplo anterior, la instrucción `SELECT` incluye también una cláusula `WHERE`. En este caso, la consulta solo selecciona las filas de la tabla que satisfacen la cláusula `WHERE`: ``` CREATE TABLE new_table AS SELECT * FROM old_table WHERE condition; ``` **Example Ejemplo : selección de columnas específicas de una o más tablas** En el siguiente ejemplo se crea una nueva consulta que se ejecuta en un conjunto de columnas de otra tabla: ``` CREATE TABLE new_table AS SELECT column_1, column_2, ... column_n FROM old_table; ``` Esta variante del mismo ejemplo crea una nueva tabla a partir de columnas específicas de varias tablas: ``` CREATE TABLE new_table AS SELECT column_1, column_2, ... column_n FROM old_table_1, old_table_2, ... old_table_n; ``` **Example Ejemplo : creación de una copia vacía de una tabla existente** En el siguiente ejemplo se utiliza `WITH NO DATA` para crear una nueva tabla, que está vacía y tiene el mismo esquema que la tabla original: ``` CREATE TABLE new_table AS SELECT * FROM old_table WITH NO DATA; ``` **Example Ejemplo : especificación de los formatos de almacenamiento y compresión de datos** Con CTAS, puede utilizar una tabla de origen en un formato de almacenamiento para crear otra tabla en un formato de almacenamiento diferente. Utilice la propiedad `format` para especificar `ORC`, `PARQUET`, `AVRO`, `JSON`o `TEXTFILE` como formato de almacenamiento para la nueva tabla. Para los formatos de almacenamiento `PARQUET`, `ORC`, `TEXTFILE` y `JSON`, utilice la propiedad `write_compression` para especificar el formato de compresión para los datos de la nueva tabla. Para obtener información sobre los formatos de compresión que admite cada formato de archivo, consulte [Uso de la compresión en Athena](compression-formats.md). En el siguiente ejemplo se especifica que los datos de la tabla `new_table` se almacenen en formato Parquet y utilicen la compresión Snappy. La compresión predeterminada de Parquet es `GZIP`. ``` CREATE TABLE new_table WITH ( format = 'Parquet', write_compression = 'SNAPPY') AS SELECT * FROM old_table; ``` En el siguiente ejemplo se especifica que los datos de la tabla `new_table` se almacenen en formato ORC y utilicen la compresión Snappy. La compresión predeterminada de ORC es ZLIB. ``` CREATE TABLE new_table WITH (format = 'ORC', write_compression = 'SNAPPY') AS SELECT * FROM old_table ; ``` En el siguiente ejemplo se especifica que los datos de la tabla `new_table` se almacenen en formato de documento de texto y utilicen la compresión Snappy. La compresión predeterminada de los formatos de documento de texto y JSON es GZIP. ``` CREATE TABLE new_table WITH (format = 'TEXTFILE', write_compression = 'SNAPPY') AS SELECT * FROM old_table ; ``` **Example Ejemplo : escritura de los resultados de la consulta en un formato diferente** La siguiente consulta CTAS selecciona todos los registros de `old_table`, que podrían estar almacenados en CSV u otro formato, y crea una nueva tabla con los datos subyacentes guardados en Amazon S3 en formato ORC: ``` CREATE TABLE my_orc_ctas_table WITH ( external_location = 's3://amzn-s3-demo-bucket/my_orc_stas_table/', format = 'ORC') AS SELECT * FROM old_table; ``` **Example Ejemplo : creación de tablas no particionadas** Los siguientes ejemplos crean tablas que no están particionadas. Los datos de las tablas se almacenan en diferentes formatos. Algunos de estos ejemplos especifican la ubicación externa. En el siguiente ejemplo se crea una consulta CTAS que almacena los resultados como un archivo de texto: ``` CREATE TABLE ctas_csv_unpartitioned WITH ( format = 'TEXTFILE', external_location = 's3://amzn-s3-demo-bucket/ctas_csv_unpartitioned/') AS SELECT key1, name1, address1, comment1 FROM table1; ``` En el siguiente ejemplo, los resultados se almacenan en Parquet y se utiliza la ubicación predeterminada para los resultados: ``` CREATE TABLE ctas_parquet_unpartitioned WITH (format = 'PARQUET') AS SELECT key1, name1, comment1 FROM table1; ``` En la siguiente consulta, la tabla se almacena en JSON y se seleccionan columnas específicas de los resultados de la tabla original: ``` CREATE TABLE ctas_json_unpartitioned WITH ( format = 'JSON', external_location = 's3://amzn-s3-demo-bucket/ctas_json_unpartitioned/') AS SELECT key1, name1, address1, comment1 FROM table1; ``` En el siguiente ejemplo, el formato es ORC: ``` CREATE TABLE ctas_orc_unpartitioned WITH ( format = 'ORC') AS SELECT key1, name1, comment1 FROM table1; ``` En el siguiente ejemplo, el formato es Avro: ``` CREATE TABLE ctas_avro_unpartitioned WITH ( format = 'AVRO', external_location = 's3://amzn-s3-demo-bucket/ctas_avro_unpartitioned/') AS SELECT key1, name1, comment1 FROM table1; ``` **Example Ejemplo : creación de tablas particionadas** Los siguientes ejemplos muestran consultas `CREATE TABLE AS SELECT` para tablas con particiones en diferentes formatos de almacenamiento, que utilizan `partitioned_by` y otras propiedades en la cláusula `WITH`. Para ver la sintaxis, consulte [Propiedades de la tabla CTAS](create-table-as.md#ctas-table-properties). Para obtener más información sobre cómo elegir las columnas para crear particiones, consulte [Uso de particiones y asignación de buckets](ctas-partitioning-and-bucketing.md). Indique las columnas particionadas al final de la lista de columnas en la instrucción `SELECT`. Puede particionar por varias columnas y tener hasta 100 combinaciones únicas de partición y bucket. Por ejemplo, puede tener 100 particiones si no se especifican buckets. ``` CREATE TABLE ctas_csv_partitioned WITH ( format = 'TEXTFILE', external_location = 's3://amzn-s3-demo-bucket/ctas_csv_partitioned/', partitioned_by = ARRAY['key1']) AS SELECT name1, address1, comment1, key1 FROM tables1; ``` ``` CREATE TABLE ctas_json_partitioned WITH ( format = 'JSON', external_location = 's3://amzn-s3-demo-bucket/ctas_json_partitioned/', partitioned_by = ARRAY['key1']) AS select name1, address1, comment1, key1 FROM table1; ``` **Example Ejemplo : creación de tablas particionadas y en buckets** En el siguiente ejemplo se muestra una consulta `CREATE TABLE AS SELECT` que utiliza particiones y buckets para almacenar los resultados en Amazon S3. Los resultados de las tablas están particionados y distribuidos en buckets por columnas diferentes. Athena admite un máximo de 100 combinaciones únicas de partición y bucket. Por ejemplo, si crea una tabla con cinco buckets, se admiten 20 particiones con cinco buckets cada una. Para ver la sintaxis, consulte [Propiedades de la tabla CTAS](create-table-as.md#ctas-table-properties). Para obtener información sobre cómo elegir las columnas para la asignación de buckets, consulte [Uso de particiones y asignación de buckets](ctas-partitioning-and-bucketing.md). ``` CREATE TABLE ctas_avro_bucketed WITH ( format = 'AVRO', external_location = 's3://amzn-s3-demo-bucket/ctas_avro_bucketed/', partitioned_by = ARRAY['nationkey'], bucketed_by = ARRAY['mktsegment'], bucket_count = 3) AS SELECT key1, name1, address1, phone1, acctbal, mktsegment, comment1, nationkey FROM table1; ``` **Example Ejemplo: creación de una tabla de Iceberg con datos de Parquet** En el siguiente ejemplo, se crea una tabla de Iceberg con archivos de datos de Parquet. Los archivos se particionan por mes mediante la columna `dt` de `table1`. El ejemplo actualiza las propiedades de retención de la tabla para que se retengan 10 instantáneas de forma predeterminada en cada ramificación de la tabla. También se retienen las instantáneas de los últimos 7 días. Para obtener más información sobre las propiedades de las tablas de Iceberg, consulte [Especificación de propiedades de tablas](querying-iceberg-creating-tables.md#querying-iceberg-table-properties). ``` CREATE TABLE ctas_iceberg_parquet WITH (table_type = 'ICEBERG', format = 'PARQUET', location = 's3://amzn-s3-demo-bucket/ctas_iceberg_parquet/', is_external = false, partitioning = ARRAY['month(dt)'], vacuum_min_snapshots_to_keep = 10, vacuum_max_snapshot_age_seconds = 604800 ) AS SELECT key1, name1, dt FROM table1; ``` **Example Ejemplo: creación de una tabla de Iceberg con datos de Avro** En el siguiente ejemplo, se crea una tabla de Iceberg con archivos de datos de Avro particionados por `key1`. ``` CREATE TABLE ctas_iceberg_avro WITH ( format = 'AVRO', location = 's3://amzn-s3-demo-bucket/ctas_iceberg_avro/', is_external = false, table_type = 'ICEBERG', partitioning = ARRAY['key1']) AS SELECT key1, name1, date FROM table1; ``` **Example Ejemplo: Creación de una tabla de S3 por medio de CTAS** En el siguiente ejemplo se crea una tabla de S3 por medio de CTAS. Tenga en cuenta que se omite la propiedad de ubicación y que el `table_type` se establece de forma predeterminada en `ICEBERG`: ``` CREATE TABLE "s3tablescatalog/amzn-s3-demo-bucket"."namespace"."s3-table-name" WITH ( format = 'PARQUET' ) AS SELECT * FROM source_table; ``` Puede especificar todas las demás propiedades de la tabla de Iceberg, como la partición y la agrupación en buckets, con la misma sintaxis que las tablas normales de Iceberg. # Uso de CTAS e INSERT INTO en ETL y análisis de datos 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 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 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 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 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 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 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. # Uso de CTAS e INSERT INTO para evitar el límite de 100 particiones Athena tiene un límite de 100 particiones por consulta `CREATE TABLE AS SELECT` ([CTAS](ctas.md)). Del mismo modo, puede añadir un máximo de 100 particiones a una tabla de destino con una instrucción [INSERT INTO](https://docs.aws.amazon.com/athena/latest/ug/insert-into.html). Si supera esta limitación, es posible que reciba el mensaje de error HIVE\$1TOO\$1MANY\$1OPEN\$1PARTITIONS: Exceeded limit of 100 open writers for partitions/buckets (HIVE\$1TOO\$1MANY\$1OPEN\$1PARTITIONS: Se ha superado el límite de 100 autores abiertos para particiones/buckets). Para evitar esta limitación, puede utilizar una instrucción CTAS y una serie de instrucciones `INSERT INTO` que crean o insertan hasta 100 particiones cada una. En el ejemplo de este tema se utiliza una base de datos llamada `tpch100` cuyos datos residen en la ubicación del bucket de Amazon S3 s3://amzn-s3-demo-bucket/. **Para utilizar CTAS e INSERT INTO para crear una tabla de más de 100 particiones** 1. Utilice una instrucción `CREATE EXTERNAL TABLE` para crear una tabla con particiones en el campo que desee. La siguiente instrucción de ejemplo divide los datos mediante la columna `l_shipdate`. La tabla tiene 2525 particiones. ``` CREATE EXTERNAL TABLE `tpch100.lineitem_parq_partitioned`( `l_orderkey` int, `l_partkey` int, `l_suppkey` int, `l_linenumber` int, `l_quantity` double, `l_extendedprice` double, `l_discount` double, `l_tax` double, `l_returnflag` string, `l_linestatus` string, `l_commitdate` string, `l_receiptdate` string, `l_shipinstruct` string, `l_comment` string) PARTITIONED BY ( `l_shipdate` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://amzn-s3-demo-bucket/lineitem/' ``` 1. Ejecute un comando `SHOW PARTITIONS ` como el siguiente para enumerar las particiones. ``` SHOW PARTITIONS lineitem_parq_partitioned ``` Los siguientes son resultados parciales de la muestra. ``` /* l_shipdate=1992-01-02 l_shipdate=1992-01-03 l_shipdate=1992-01-04 l_shipdate=1992-01-05 l_shipdate=1992-01-06 ... l_shipdate=1998-11-24 l_shipdate=1998-11-25 l_shipdate=1998-11-26 l_shipdate=1998-11-27 l_shipdate=1998-11-28 l_shipdate=1998-11-29 l_shipdate=1998-11-30 l_shipdate=1998-12-01 */ ``` 1. Ejecute una consulta CTAS para crear una tabla con particiones. En el siguiente ejemplo se crea una tabla llamada `my_lineitem_parq_partitioned` y se utiliza la cláusula `WHERE ` para restringir la `DATE` a antes de `1992-02-01`. Dado que el conjunto de datos de muestra comienza en enero de 1992, solo se crean particiones para enero de 1992. ``` CREATE table my_lineitem_parq_partitioned WITH (partitioned_by = ARRAY['l_shipdate']) AS SELECT l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_commitdate, l_receiptdate, l_shipinstruct, l_comment, l_shipdate FROM tpch100.lineitem_parq_partitioned WHERE cast(l_shipdate as timestamp) < DATE ('1992-02-01'); ``` 1. Ejecute el comando `SHOW PARTITIONS` para comprobar que la tabla contiene las particiones que desea. ``` SHOW PARTITIONS my_lineitem_parq_partitioned; ``` Las particiones del ejemplo son de enero de 1992. ``` /* l_shipdate=1992-01-02 l_shipdate=1992-01-03 l_shipdate=1992-01-04 l_shipdate=1992-01-05 l_shipdate=1992-01-06 l_shipdate=1992-01-07 l_shipdate=1992-01-08 l_shipdate=1992-01-09 l_shipdate=1992-01-10 l_shipdate=1992-01-11 l_shipdate=1992-01-12 l_shipdate=1992-01-13 l_shipdate=1992-01-14 l_shipdate=1992-01-15 l_shipdate=1992-01-16 l_shipdate=1992-01-17 l_shipdate=1992-01-18 l_shipdate=1992-01-19 l_shipdate=1992-01-20 l_shipdate=1992-01-21 l_shipdate=1992-01-22 l_shipdate=1992-01-23 l_shipdate=1992-01-24 l_shipdate=1992-01-25 l_shipdate=1992-01-26 l_shipdate=1992-01-27 l_shipdate=1992-01-28 l_shipdate=1992-01-29 l_shipdate=1992-01-30 l_shipdate=1992-01-31 */ ``` 1. Utilice una instrucción `INSERT INTO` para añadir particiones a la tabla. En el siguiente ejemplo se añaden particiones para las fechas del mes de febrero de 1992. ``` INSERT INTO my_lineitem_parq_partitioned SELECT l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_commitdate, l_receiptdate, l_shipinstruct, l_comment, l_shipdate FROM tpch100.lineitem_parq_partitioned WHERE cast(l_shipdate as timestamp) >= DATE ('1992-02-01') AND cast(l_shipdate as timestamp) < DATE ('1992-03-01'); ``` 1. Vuelva a ejecutar `SHOW PARTITIONS`. ``` SHOW PARTITIONS my_lineitem_parq_partitioned; ``` La tabla de muestra tiene ahora particiones tanto de enero como de febrero de 1992. ``` /* l_shipdate=1992-01-02 l_shipdate=1992-01-03 l_shipdate=1992-01-04 l_shipdate=1992-01-05 l_shipdate=1992-01-06 ... l_shipdate=1992-02-20 l_shipdate=1992-02-21 l_shipdate=1992-02-22 l_shipdate=1992-02-23 l_shipdate=1992-02-24 l_shipdate=1992-02-25 l_shipdate=1992-02-26 l_shipdate=1992-02-27 l_shipdate=1992-02-28 l_shipdate=1992-02-29 */ ``` 1. Siga utilizando instrucciones `INSERT INTO` que lean y no agreguen más de 100 particiones cada una. Continúe hasta que alcance el número de particiones necesario. **importante** Al establecer la condición `WHERE`, asegúrese de que las consultas no se superpongan. De lo contrario, algunas particiones podrían tener datos duplicados.