Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà. # Creare una tabella dai risultati delle query (CTAS) Una query `CREATE TABLE AS SELECT` (CTAS) crea una nuova tabella in Athena dai risultati di un'istruzione `SELECT` da un'altra query. Athena di dati creati dall'istruzione CTAS in una percorso specifico in Amazon S3. Per la sintassi, consulta [CREATE TABLE AS](create-table-as.md). `CREATE TABLE AS` combina una dichiarazione DDL `CREATE TABLE` con una dichiarazione DML `SELECT`, quindi tecnicamente contiene sia DDL sia DML. Tuttavia, tieni presente che ai fini delle Service Quotas, le query CTAS in Athena vengono trattate come DML. Per informazioni sulle Service Quotas in Athena, consulta [Service Quotas](service-limits.md). Utilizza le query CTAS per: + Creare tabelle dai risultati della query in un'unica operazione, senza eseguire ripetutamente la query del set di dati non elaborati. In questo modo è più semplice lavorare con set di dati non elaborati. + Trasforma i risultati delle query e migra le tabelle in altri formati di tabella come Apache Iceberg. Ciò migliora le prestazioni delle query e riduce i costi delle query in Athena. Per informazioni, consulta [Creazione di una tabella Iceberg](querying-iceberg-creating-tables.md). + Trasforma i risultati delle query in formati di archiviazione come Parquet e ORC. Ciò migliora le prestazioni delle query e riduce i costi delle query in Athena. Per informazioni, consulta [Usa formati di archiviazione colonnare](columnar-storage.md). + Creazione di copie di tabelle esistenti che contengono solo i dati necessari. **Topics** + [Considerazioni e restrizioni per le query CTAS](ctas-considerations-limitations.md) + [Crea query CTAS](ctas-console.md) + [Esempi di CTAS](ctas-examples.md) + [Utilizzare CTAS e INSERT INTO per ETL](ctas-insert-into-etl.md) + [Fare in modo di aggirare il limite di 100 partizioni](ctas-insert-into.md) # Considerazioni e restrizioni per le query CTAS Le sezioni seguenti descrivono le considerazioni e le limitazioni da tenere a mente quando si utilizzano le query `CREATE TABLE AS SELECT` (CTAS) in Athena. ## Impara la sintassi delle query CTAS La sintassi delle query CTAS differisce dalla sintassi di `CREATE [EXTERNAL] TABLE` utilizzato per la creazione di tabelle. Per informazioni, consulta [CREATE TABLE AS](create-table-as.md). ## La differenza tra visualizzazioni e query CTAS Le query CTAS scrivono nuovi dati in una posizione specificata in Amazon S3. Le viste non scrivono alcun dato. ## È possibile specificare il percorso per l’archiviazione dei risultati della query CTAS. Se il gruppo di lavoro [sovrascrive l'impostazione lato client](workgroups-settings-override.md) per la posizione dei risultati della query, Athena crea la tabella nella posizione `s3://amzn-s3-demo-bucket/tables//`. Per visualizzare la posizione dei risultati della query specificata per il gruppo di lavoro, [visualizza i dettagli del gruppo di lavoro](viewing-details-workgroups.md). Se il gruppo di lavoro non sovrascrive la posizione dei risultati della query, puoi utilizzare la sintassi `WITH (external_location ='s3://amzn-s3-demo-bucket/')` nella query CTAS per specificare dove sono memorizzati i risultati della query CTAS. **Nota** La proprietà `external_location` deve specificare una posizione vuota. Una query CTAS verifica che il percorso (prefisso) nel bucket sia vuoto e non sovrascrive mai i dati se il percorso dispone già di dati al suo interno. Per utilizzare nuovamente la stessa posizione, elimina i dati nella posizione del prefisso della chiave nel bucket. Se ometti la sintassi `external_location` e non utilizzi l'impostazione del gruppo di lavoro, Athena usa l'[impostazione lato client](query-results-specify-location-console.md) per la posizione dei risultati della query e crea la tabella nella posizione `s3://amzn-s3-demo-bucket////tables//`. ## Individua i file orfani Se un'istruzione `CTAS` o `INSERT INTO` non riesce, è possibile che i dati orfani vengano lasciati nel percorso dati target per query non riuscite o annullate. Poiché Athena in alcuni casi non elimina i dati dal bucket di destinazione per la tua query, i dati parziali potrebbero essere inclusi nelle query successive. Per individuare i file orfani per l'ispezione o l'eliminazione, è possibile utilizzare il file manifesto dati fornito da Athena per tenere traccia dell'elenco dei file da scrivere. In alcuni rari casi in cui una query Athena fallisce bruscamente, il file manifest potrebbe non essere presente. Puoi ispezionare manualmente la posizione S3 di destinazione per trovare i file orfani. Per ulteriori informazioni, consulta [Identificare i file di output delle query](querying-finding-output-files.md#querying-identifying-output-files) e. [DataManifestLocation](https://docs.aws.amazon.com/athena/latest/APIReference/API_QueryExecutionStatistics.html#athena-Type-QueryExecutionStatistics-DataManifestLocation) Consigliamo vivamente di utilizzare Apache Iceberg per ottenere transazioni atomiche di tabelle. Per ulteriori informazioni, consulta [Tabelle Apache Iceberg](querying-iceberg.md). ## Ricorda che le clausole ORDER BY vengono ignorate In una query CTAS, Athena ignora le clausole `ORDER BY` nella parte `SELECT` della query. Secondo le specifiche SQL (ISO 9075, parte 2), l'ordine delle righe di una tabella specificata da un'espressione di query è garantito solo per l'espressione di query che contiene immediatamente la clausola `ORDER BY`. Le tabelle in SQL sono in ogni caso intrinsecamente non ordinate e l'implementazione delle clausole `ORDER BY` in sottoquery comporterebbe un rendimento scadente della query e non produrrebbe un output ordinato. Pertanto, nelle query Athena CTAS, non vi è alcuna garanzia che l'ordine specificato dalla clausola `ORDER BY` venga preservato al momento della scrittura dei dati. ## Scegli un formato per archiviare i risultati della query È possibile archiviare i risultati CTAS in `PARQUET`, `ORC`, `AVRO`, `JSON` e `TEXTFILE`. I delimitatori multi-carattere non sono supportati per il formato CTAS `TEXTFILE`. Se non specificate un formato di archiviazione dei dati, i risultati delle interrogazioni CTAS vengono memorizzati in Parquet per impostazione predefinita. Le interrogazioni CTAS non richiedono la specifica di a per interpretare le trasformazioni SerDe di formato. Per informazioni, consulta [Example: Writing query results to a different format](ctas-examples.md#ctas-example-format). ## Prendi in considerazione i formati di compressione La compressione `GZIP` viene utilizzata per i risultati delle query CTAS nei formati JSON e TEXTFILE. Per Parquet, puoi usare `GZIP` o `SNAPPY` e il valore predefinito è `GZIP`. Per Parquet, puoi usare `LZ4`, `SNAPPY`, `ZLIB` o `ZSTD` e il valore predefinito è `ZLIB`. Per gli esempi CTAS che specificano la compressione, consulta [Example: Specifying data storage and compression formats](ctas-examples.md#ctas-example-compression). Per ulteriori informazioni sulla compressione in Athena, consulta [Usa la compressione in Athena](compression-formats.md). ## Suddividere e inserire i risultati in bucket È possibile partizionare ed eseguire il bucket dei dati dei risultati di una query CTAS. Per specificare le proprietà della tabella di destinazione, includi i predicati di partizionamento e bucketing alla fine della clausola. `WITH` Per ulteriori informazioni, consultare [Utilizzare partizionamento e bucketing](ctas-partitioning-and-bucketing.md) e [Example: Creating bucketed and partitioned tables](ctas-examples.md#ctas-example-bucketed). Quando si utilizza CTAS per creare una tabella partizionata, Athena ha un limite di scrittura di 100 partizioni. Per informazioni su una soluzione alternativa per la limitazione di 100 partizioni, consulta [Utilizzare CTAS e INSERT INTO per aggirare il limite di 100 partizioni](ctas-insert-into.md). ## Crittografa i tuoi risultati È possibile crittografare i risultati delle query CTAS in Amazon S3, analogamente al modo di crittografare gli altri risultati della query in Athena. Per ulteriori informazioni, consulta [Crittografare i risultati di query Athena archiviati in Amazon S3](encrypting-query-results-stored-in-s3.md). ## L’impostazione prevista del proprietario del bucket non si applica a CTAS Per le istruzioni CTAS, l'impostazione prevista per il proprietario del bucket non si applica alla posizione della tabella di destinazione in Amazon S3. L'impostazione prevista per il proprietario del bucket si applica solo al percorso di output di Amazon S3 specificato per i risultati delle query di Athena. Per ulteriori informazioni, consulta [Specificare una posizione dei risultati delle query utilizzando la console Athena](query-results-specify-location-console.md). ## I tipi di dati delle colonne vengono conservati I tipi di dati di colonna per una query CTAS sono gli stessi specificati per la query originale. # Creare query CTAS nella console di Athena Nella console Athena è possibile creare una query CTAS da un'altra query. **Per creare una query CTAS da un'altra query** 1. Eseguire la query nell'editor di query della console Athena. 1. Nella parte inferiore dell'editor di query, scegli l'opzione **Create** (Crea), quindi scegli **Table from query** (Tabella dalla query). 1. Nel modulo **Create table as select** (Crea tabella come da selezione), completa i campi come segue: 1. Per **Table name** (Nome tabella), immetti il nome per la nuova tabella. Utilizza solo i caratteri minuscoli e i trattini bassi, ad esempio `my_select_query_parquet`. 1. Per **Database configuration** (Configurazione database), utilizza le opzioni per scegliere un database esistente o creane uno nuovo. 1. (Facoltativo) In **Result configuration** (Configurazione risultati), per **Location of CTAS query results** (Posizione dei risultati della query CTAS), se l'impostazione relativa alla posizione dei risultati della query per il gruppo di lavoro non sostituisce questa opzione, effettua una delle seguenti operazioni: + Inserisci il percorso di una posizione S3 esistente nella casella di ricerca o scegli **Browse S3** (Sfoglia S3) per selezionare una posizione da un elenco. + Scegli **View** (Visualizza) per aprire la pagina **Buckets** (Bucket) della console Amazon S3, dove puoi visualizzare ulteriori informazioni sui bucket esistenti oltre a scegliere un bucket o crearne uno nuovo con impostazioni personalizzate. Specifica una posizione vuota in Amazon S3 in cui i dati verranno emessi. Se i dati esistono già nella posizione specificata, la query ha esito negativo con un errore. Se l'impostazione relativa alla posizione dei risultati della query per il gruppo di lavoro sostituisce questa impostazione, Athena crea la tabella nella posizione `s3://amzn-s3-demo-bucket/tables/query_id/` 1. Per **Data format** (Formato dei dati), specifica il formato in cui si trovano i dati. + **Table type** (Tipo di tabella): il tipo di tabella predefinito in Athena è Apache Hive. + **File format** (Formato file): scegli tra opzioni come CSV, TSV, JSON, Parquet o ORC. Per informazioni sui formati Parquet e ORC, consultare [Usa formati di archiviazione colonnare](columnar-storage.md). + **Write compression** (Compressione per la scrittura): (facoltativo) scegli un formato di compressione. Athena supporta diversi formati di compressione per la lettura e la scrittura di dati, inclusa la lettura da una tabella che utilizza più formati di compressione. Ad esempio, Athena può leggere correttamente i dati in una tabella che utilizza il formato file Parquet quando alcuni file Parquet vengono compressi con Snappy e altri file Parquet vengono compressi con GZIP. Lo stesso principio vale per i formati di archiviazione ORC, file di testo e JSON. Per ulteriori informazioni, consulta [Usa la compressione in Athena](compression-formats.md). + **Partitions** (Partizioni): (facoltativo) seleziona le colonne da partizionare. Effettuando il partizionamento dei dati, è possibile limitare la quantità di dati scansionati da ogni query, migliorando così le prestazioni e riducendo i costi. in base a qualsiasi chiave di partizione. Per ulteriori informazioni, consulta [Come partizionare i dati](partitions.md). + **Buckets** (Bucket): (facoltativo) seleziona le colonne che desideri inserire nel bucket. Il bucketing è una tecnica di raggruppamento dei dati in base a colonne specifiche all'interno di un'unica partizione. Queste colonne sono note come *chiavi bucket*. Raggruppando i dati correlati in un unico bucket (un file all'interno di una partizione), si riduce notevolmente la quantità di dati scansionati da Athena, migliorando così le prestazioni delle query e riducendo i costi. Per ulteriori informazioni, consulta [Utilizzare partizionamento e bucketing](ctas-partitioning-and-bucketing.md). 1. Per **Preview table query** (Query di anteprima della tabella), esamina la query. Per la sintassi della query, consulta [CREATE TABLE AS](create-table-as.md). 1. Scegliere **Create table (Crea tabella)**. La console Athena dispone di un modello SQL che è possibile utilizzare anche per creare una query CTAS. **Per creare una query CTAS utilizzando un modello SQL** Utilizzare il modello `CREATE TABLE AS SELECT` per creare una query CTAS nell'editor di query. 1. Nella console Athena, accanto a **Tables and views** (Tabelle e visualizzazioni), scegli **Create table** (Crea tabella) e quindi scegli **CREATE TABLE AS SELECT** (CREA TABELLA COME SELEZIONE). Nell'editor di query viene inserita una query CTAS con valori di segnaposto. 1. Nell'Editor di query, modificare la query se necessario. Per la sintassi della query, consulta [CREATE TABLE AS](create-table-as.md). 1. Scegli **Esegui**. Per alcuni esempi, consulta [Esempi di query CTAS](ctas-examples.md). # Esempi di query CTAS Utilizzare i seguenti esempi per creare query CTAS. Per ulteriori informazioni sulla sintassi CTAS, consulta [CREATE TABLE AS](create-table-as.md). In questa sezione: + [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 Esempio: duplicazione di una tabella selezionando tutte le colonne** L'esempio seguente crea una tabella copiando tutte le colonne di una tabella: ``` CREATE TABLE new_table AS SELECT * FROM old_table; ``` Nella seguente variazione dello stesso esempio, l'istruzione `SELECT` comprende anche una clausola `WHERE`. In questo caso, la query seleziona solo le righe dalla tabella che soddisfano la clausola `WHERE`: ``` CREATE TABLE new_table AS SELECT * FROM old_table WHERE condition; ``` **Example Esempio: selezione di colonne specifiche da una o più tabelle** L'esempio seguente crea una nuova query che viene eseguita su un set di colonne da un'altra tabella: ``` CREATE TABLE new_table AS SELECT column_1, column_2, ... column_n FROM old_table; ``` Questa variazione dello stesso esempio crea una nuova tabella in base a colonne specifiche di più tabelle: ``` CREATE TABLE new_table AS SELECT column_1, column_2, ... column_n FROM old_table_1, old_table_2, ... old_table_n; ``` **Example Esempio: creazione di una copia vuota di una tabella esistente** L'esempio seguente utilizza `WITH NO DATA` per creare una nuova tabella vuota e ha lo stesso schema della tabella originale: ``` CREATE TABLE new_table AS SELECT * FROM old_table WITH NO DATA; ``` **Example Esempio: specifica dei formati di compressione e dell'archiviazione dei dati** Con CTAS, è possibile utilizzare una tabella di origine in un formato di archiviazione per creare un'altra tabella in un formato di archiviazione diverso. Utilizza la proprietà `format` per specificare `ORC`, `PARQUET`, `AVRO`, `JSON` oppure `TEXTFILE` come formato di archiviazione per la nuova tabella. Per i formati di archiviazione `PARQUET`, `ORC`, `TEXTFILE` e `JSON`, utilizzare la proprietà `write_compression` per specificare il formato di compressione per i dati della nuova tabella. Per informazioni sui formati di compressione supportati da ciascun formato di file, consultare [Usa la compressione in Athena](compression-formats.md). L'esempio seguente specifica che i dati nella tabella `new_table` devono essere archiviati in formato Parquet e devono utilizzare la compressione Snappy. La compressione di default per Parquet è `GZIP`. ``` CREATE TABLE new_table WITH ( format = 'Parquet', write_compression = 'SNAPPY') AS SELECT * FROM old_table; ``` L'esempio seguente specifica che i dati nella tabella `new_table` devono essere archiviati in formato ORC tramite la compressione Snappy. La compressione di default per ORC è ZLIB. ``` CREATE TABLE new_table WITH (format = 'ORC', write_compression = 'SNAPPY') AS SELECT * FROM old_table ; ``` L'esempio seguente specifica che i dati nella tabella `new_table` devono essere archiviati in formato file di testo tramite la compressione Snappy. La compressione di default sia per il file di testo che per i formati JSON è GZIP. ``` CREATE TABLE new_table WITH (format = 'TEXTFILE', write_compression = 'SNAPPY') AS SELECT * FROM old_table ; ``` **Example Esempio: scrittura dei risultati della query in un formato diverso** La query CTAS seguente seleziona tutti i record da `old_table`, che possono essere archiviati in CSV o in un altro formato, e crea una nuova tabella con i dati sottostanti salvati in Amazon S3 in 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 Esempio: creazione di tabelle non partizionate** I seguenti esempi creano tabelle non partizionate. I dati della tabella vengono memorizzati in diversi formati. Alcuni di questi esempi specificano il percorso esterno. L'esempio seguente crea una query CTAS che archivia i risultati in un file di testo: ``` 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; ``` Nel seguente esempio, i risultati vengono archiviati in Parquet e viene utilizzato il percorso dei risultati predefiniti. ``` CREATE TABLE ctas_parquet_unpartitioned WITH (format = 'PARQUET') AS SELECT key1, name1, comment1 FROM table1; ``` Nella seguente query, la tabella viene archiviata in formato JSON e vengono selezionate colonne specifiche dai risultati della tabella originale: ``` 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; ``` Nell'esempio seguente, il formato è ORC: ``` CREATE TABLE ctas_orc_unpartitioned WITH ( format = 'ORC') AS SELECT key1, name1, comment1 FROM table1; ``` Nell'esempio seguente, il formato è 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 Esempio: creazione di tabelle partizionate** I seguenti esempi mostrano le query `CREATE TABLE AS SELECT` per tabelle partizionate in diversi formati di storage, che utilizzano `partitioned_by` e altre proprietà nella clausola `WITH`. Per la sintassi, consulta [Proprietà tabella CTAS](create-table-as.md#ctas-table-properties). Per ulteriori informazioni sulla scelta delle colonne per il partizionamento, consulta [Utilizzare partizionamento e bucketing](ctas-partitioning-and-bucketing.md). Elencare le colonne di partizione alla fine dell'elenco di colonne nell'istruzione `SELECT`. È possibile partizionare per più di una colonna e ottenere fino a 100 combinazioni univoche di partizioni e bucket. Ad esempio, è possibile avere 100 partizioni se non vengono specificati bucket. ``` 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 Esempio: creazione di tabelle con bucket e partizionate** L'esempio seguente mostra una query `CREATE TABLE AS SELECT` che utilizza sia il partizionamento sia il bucketing per archiviare i risultati della query in Amazon S3. I risultati vengono partizionati e viene eseguito il bucketing in base a diverse colonne. Athena supporta un massimo di 100 combinazioni uniche di bucket e partizioni. Ad esempio, se si crea una tabella con cinque bucket, sono supportate 20 partizioni con cinque bucket ciascuno. Per la sintassi, consulta [Proprietà tabella CTAS](create-table-as.md#ctas-table-properties). Per ulteriori informazioni sulla scelta delle colonne per il bucketing, consulta [Utilizzare partizionamento e bucketing](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 Esempio: creazione di una tabella Iceberg con dati Parquet** Nell'esempio seguente viene creata una tabella Iceberg con file di dati Parquet. I file vengono partizionati per mese nella colonna `dt` nella `table1`. Nell'esempio vengono aggiornate le proprietà di mantenimento della tabella in modo che per impostazione predefinita vengano conservati 10 snapshot su ogni ramo della tabella. Vengono mantenuti anche gli snapshot degli ultimi 7 giorni. Per ulteriori informazioni sulle proprietà delle tabelle Iceberg in Athena, consulta [Specificazione delle proprietà tabella](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 Esempio: creazione di una tabella Iceberg con dati Avro** Nell'esempio seguente viene creata una tabella Iceberg con file di dati Avro partizionati da `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 Esempio: creazione di una tabella S3 utilizzando CTAS** L'esempio seguente crea una tabella S3 utilizzando CTAS. Notare che la proprietà posizione viene omessa e l’impostazione `table_type`predefinita viene impostata su `ICEBERG`: ``` CREATE TABLE "s3tablescatalog/amzn-s3-demo-bucket"."namespace"."s3-table-name" WITH ( format = 'PARQUET' ) AS SELECT * FROM source_table; ``` È possibile specificare tutte le altre proprietà delle tabelle Iceberg, come il partizionamento e il bucketing, con la stessa sintassi delle normali tabelle Iceberg. # Utilizzare CTAS e INSERT INTO per ETL e analisi dei dati Utilizza le istruzioni Create Table as Select ([CTAS](ctas.md)) e [INSERT INTO](insert-into.md) in Athena per estrarre, trasformare e caricare (ETL) i dati in Amazon S3 per l'elaborazione dei dati. In questo argomento viene illustrato come utilizzare queste istruzioni per partizionare e convertire un set di dati nel formato di dati in colonna per ottimizzarlo per l'analisi dei dati. Le istruzioni CTAS utilizzano le query [SELECT](select.md) standard per creare le nuove tabelle. Puoi utilizzare un'istruzione CTAS per creare un sottoinsieme di dati per l'analisi. In un'istruzione CTAS, puoi partizionare i dati, specificare la compressione e convertire i dati in un formato a colonne come Apache Parquet o Apache ORC. Quando esegui la query CTAS, le tabelle e le partizioni che crea vengono automaticamente aggiunte al [AWS Glue Data Catalog](https://aws.amazon.com/glue). In questo modo le nuove tabelle e partizioni create sono immediatamente disponibili per le query successive. Le istruzioni INSERT INTO inseriscono nuove righe in una tabella di destinazione in base a un'istruzione di query SELECT che viene eseguita per una tabella di origine. Puoi utilizzare le istruzioni INSERT INTO per trasformare e caricare i dati della tabella di origine in formato CSV in dati della tabella di destinazione utilizzando tutte le trasformazioni supportate da CTAS. ## Panoramica di In Athena, utilizza un'istruzione CTAS per eseguire una conversione batch iniziale dei dati. Quindi utilizza più istruzioni INSERT INTO per apportare aggiornamenti incrementali alla tabella creata dall'istruzione CTAS. **Fasi** + [Fase 1: creare una tabella basata sul set di dati originale](#ctas-insert-into-etl-step-1-create-a-table-based-on-the-original-dataset) + [Fase 2: utilizzare CTAS per partizionare, convertire e comprimere i dati](#ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data) + [Fase 3: utilizzare INSERT INTO per aggiungere dati](#ctas-insert-into-etl-step-3-use-insert-into-to-add-data) + [Fase 4: misurare le prestazioni e le differenze dei costi](#ctas-insert-into-etl-step-4-measure-performance-and-cost-differences) ## Fase 1: creare una tabella basata sul set di dati originale Nell'esempio riportato in questo argomento viene utilizzato un sottoinsieme Amazon S3 leggibile del set di dati [NOAA Global Historical Climatology Network Daily (GHCN-D)](https://registry.opendata.aws/noaa-ghcn/) disponibile al pubblico. I dati in Amazon S3 hanno le seguenti caratteristiche. ``` 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 ``` I dati originali vengono archiviati in Amazon S3 senza partizioni. I dati sono in formato CSV in file come il seguente. ``` 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 ``` Le dimensioni dei file in questo esempio sono relativamente piccole. Unendoli in file più grandi, è possibile ridurre il numero totale di file, consentendo prestazioni migliori delle query. Puoi utilizzare le istruzioni CTAS e INSERT INTO per migliorare le prestazioni delle query. **Per creare un database e una tabella in base al set di dati di esempio** 1. Nella console Athena, scegli la Regione AWS**Stati Uniti orientali (Virginia settentrionale)**. Assicurarsi di eseguire tutte le query in questo tutorial in `us-east-1`. 1. Nell'editor di query Athena, eseguire il comando [CREATE DATABASE](create-database.md) per creare un database. ``` CREATE DATABASE blogdb ``` 1. Eseguire l'istruzione seguente per [creare una tabella](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/' ``` ## Fase 2: utilizzare CTAS per partizionare, convertire e comprimere i dati Dopo aver creato una tabella, puoi utilizzare una singola istruzione [CTAS](ctas.md) per convertire i dati in formato Parquet con compressione Snappy e per partizionare i dati per anno. La tabella creata nella fase 1 ha un campo `date` con la data nel formato `YYYYMMDD` (ad esempio, `20100104`). Poiché la nuova tabella verrà partizionata per `year`, l'istruzione di esempio nella procedura seguente utilizza la funzione Presto `substr("date",1,4)` per estrarre il valore `year` dal campo `date`. **Per convertire i dati in formato Parquet con compressione Snappy e partizionamento per anno** + Esegui la seguente istruzione CTAS, sostituendola *your-bucket* con la posizione del bucket 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** In questo esempio, la tabella creata include solo i dati dal 2015 al 2019. Nella fase 3, aggiungi nuovi dati a questa tabella utilizzando il comando INSERT INTO. Al termine della query, utilizza la procedura seguente per verificare l'output nella posizione Amazon S3 specificata nell'istruzione CTAS. **Per visualizzare le partizioni e i file parquet creati dall'istruzione CTAS** 1. Per mostrare le partizioni create, esegui il comando seguente. AWS CLI Assicurarsi di includere la barra finale (/). ``` aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ ``` L'output mostra le partizioni. ``` PRE year=2015/ PRE year=2016/ PRE year=2017/ PRE year=2018/ PRE year=2019/ ``` 1. Per visualizzare i file Parquet, eseguire il comando seguente. L'opzione `|` *head -5* che limita l'output ai primi cinque risultati, non è disponibile in Windows. ``` aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable | head -5 ``` L'output è simile a quello riportato di seguito. ``` 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 ``` ## Fase 3: utilizzare INSERT INTO per aggiungere dati Nella fase 2, hai utilizzato CTAS per creare una tabella con partizioni per gli anni dal 2015 al 2019. Tuttavia, il set di dati originale contiene anche dati relativi agli anni dal 2010 al 2014. Ora aggiungi questi dati utilizzando un'istruzione [INSERT INTO](insert-into.md). **Per aggiungere dati alla tabella utilizzando una o più istruzioni INSERT INTO** 1. Eseguire il seguente comando INSERT INTO, specificando gli anni prima del 2015 nella clausola 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. Eseguire nuovamente il comando `aws s3 ls`, utilizzando la seguente sintassi. ``` aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ ``` L'output mostra le nuove partizioni. ``` 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. Per vedere la riduzione delle dimensioni del set di dati ottenuta utilizzando la compressione e lo storage a colonne in formato Parquet, eseguire il comando seguente. ``` aws s3 ls s3://amzn-s3-demo-bucket/optimized-data/ --recursive --human-readable --summarize ``` I seguenti risultati mostrano che la dimensione del set di dati dopo Parquet con compressione Snappy è 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. Se più dati CSV vengono aggiunti alla tabella originale, è possibile aggiungere i dati alla tabella Parquet utilizzando le istruzioni INSERT INTO. Ad esempio, se sono disponibili nuovi dati per l'anno 2020, è possibile eseguire la seguente istruzione INSERT INTO. L'istruzione aggiunge i dati e la partizione pertinente alla tabella `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** L'istruzione INSERT INTO supporta la scrittura di un massimo di 100 partizioni nella tabella di destinazione. Tuttavia, per aggiungere più di 100 partizioni, è possibile eseguire più istruzioni INSERT INTO. Per ulteriori informazioni, consulta [Utilizzare CTAS e INSERT INTO per aggirare il limite di 100 partizioni](ctas-insert-into.md). ## Fase 4: misurare le prestazioni e le differenze dei costi Dopo aver trasformato i dati, puoi misurare i miglioramenti delle prestazioni e i risparmi sui costi eseguendo le stesse query nelle tabelle nuove e precedenti e confrontando i risultati. **Nota** Per informazioni sui costi per query di Athena, consulta [Prezzi di Amazon Athena](https://aws.amazon.com/athena/pricing). **Per misurare i miglioramenti delle performance e le differenze dei costi** 1. Eseguire la seguente query nella tabella originale. La query trova il numero di distinti IDs per ogni valore dell'anno. ``` SELECT substr("date",1,4) as year, COUNT(DISTINCT id) FROM original_csv GROUP BY 1 ORDER BY 1 DESC ``` 1. Annotare il tempo di esecuzione della query e la quantità di dati analizzati. 1. Eseguire la stessa query nella nuova tabella, annotando il tempo di esecuzione della query e la quantità di dati analizzati. ``` SELECT year, COUNT(DISTINCT id) FROM new_parquet GROUP BY 1 ORDER BY 1 DESC ``` 1. Confrontare i risultati e calcolare la differenza delle prestazioni e dei costi. I risultati di esempio riportati di seguito mostrano che la query di test nella nuova tabella è stata più veloce e più economica rispetto alla query nella vecchia tabella. **** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/athena/latest/ug/ctas-insert-into-etl.html) 1. Eseguire la seguente query di esempio nella tabella originale. La query calcola la temperatura massima media (Celsius), la temperatura minima media (Celsius) e la piovosità media (mm) per la Terra nel 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. Annotare il tempo di esecuzione della query e la quantità di dati analizzati. 1. Eseguire la stessa query nella nuova tabella, annotando il tempo di esecuzione della query e la quantità di dati analizzati. ``` 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. Confrontare i risultati e calcolare la differenza delle prestazioni e dei costi. I risultati di esempio riportati di seguito mostrano che la query di test nella nuova tabella è stata più veloce e più economica rispetto alla query nella vecchia tabella. **** [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/athena/latest/ug/ctas-insert-into-etl.html) ## Riepilogo In questo argomento viene illustrato come eseguire operazioni ETL utilizzando istruzioni CTAS e INSERT INTO in Athena. È stata eseguita la prima serie di trasformazioni utilizzando un'istruzione CTAS che ha convertito i dati nel formato Parquet con compressione Snappy. L'istruzione CTAS ha anche convertito il set di dati da non partizionato a partizionato. Ciò ha ridotto le sue dimensioni e i costi di esecuzione delle query. Quando nuovi dati diventano disponibili, puoi utilizzare un'istruzione INSERT INTO per trasformare e caricare i dati nella tabella creata con l'istruzione CTAS. # Utilizzare CTAS e INSERT INTO per aggirare il limite di 100 partizioni Athena ha un limite di 100 partizioni per query `CREATE TABLE AS SELECT` ([CTAS](ctas.md)). Allo stesso modo, è possibile aggiungere un massimo di 100 partizioni a una tabella di destinazione con un'istruzione [INSERT INTO](https://docs.aws.amazon.com/athena/latest/ug/insert-into.html). Se superi questa limitazione potresti ricevere il messaggio di errore HIVE\$1TOO\$1MANY\$1OPEN\$1PARTITIONS: Exceeded limit of 100 open writers for partitions/buckets (HIVE\$1TOO\$1MANY\$1OPEN\$1PARTITIONS: limite di 100 scrittori aperti per partizioni/bucket superato). Per ovviare questa limitazione, puoi utilizzare un'istruzione CTAS e una serie di istruzioni `INSERT INTO` che creano o inseriscono fino a 100 partizioni ciascuna. Nell’esempio in questo argomento viene utilizzato un database chiamato `tpch100` i cui dati risiedono nella posizione del bucket Amazon S3 s3:///. **Per utilizzare CTAS e INSERT INTO per creare una tabella con più di 100 partizioni** 1. Utilizzare un'istruzione `CREATE EXTERNAL TABLE` per creare una tabella partizionata nel campo desiderato. L'istruzione di esempio seguente partiziona i dati dalla colonna `l_shipdate`. La tabella ha 2525 partizioni. ``` 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. Eseguire un comando `SHOW PARTITIONS ` come il seguente per elencare le partizioni. ``` SHOW PARTITIONS lineitem_parq_partitioned ``` Di seguito sono riportati i risultati parziali di esempio. ``` /* 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. Eseguire una query CTAS per creare una tabella partizionata. L'esempio seguente crea una tabella chiamata `my_lineitem_parq_partitioned` e utilizza la clausola `WHERE ` in modo che `DATE` sia precedente a `1992-02-01`. Poiché il set di dati di esempio inizia con gennaio 1992, vengono create solo le partizioni per gennaio 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. Eseguire il comando `SHOW PARTITIONS` per verificare che la tabella contenga le partizioni desiderate. ``` SHOW PARTITIONS my_lineitem_parq_partitioned; ``` Le partizioni nell'esempio sono per gennaio 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. Utilizzare un'istruzione `INSERT INTO` per aggiungere le partizioni alla tabella. Nell'esempio seguente vengono aggiunte le partizioni per le date del mese di febbraio 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. Eseguire nuovamente `SHOW PARTITIONS`. ``` SHOW PARTITIONS my_lineitem_parq_partitioned; ``` La tabella di esempio ora ha partizioni sia da gennaio che da febbraio 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. Continuare a utilizzare le istruzioni `INSERT INTO` che leggono e aggiungono non più di 100 partizioni ciascuna. Continuare fino a raggiungere il numero di partizioni necessarie. **Importante** Quando imposti la condizione `WHERE`, assicurati che le query non si sovrappongano. In caso contrario, alcune partizioni potrebbero avere dati duplicati.