Esempi di query CTAS

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;

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;

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; 

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.

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 ;

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;

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;

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. Per ulteriori informazioni sulla scelta delle colonne per il partizionamento, consulta Usa il partizionamento e il bucketing.

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;

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.

Per ulteriori informazioni sulla scelta delle colonne per il bucketing, consulta Usa il partizionamento e il bucketing.

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;

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 Specificare le proprietà della tabella.

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;

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;