CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

  • Articolo

Si applica a: SQL Server 2022 (16.x) e versioni successive azure Synapse Analytics Analytics Platform System (PDW)

Crea una tabella esterna e quindi esporta, in parallelo, i risultati di un'istruzione Transact-SQL SELECT.

  • Azure Synapse Analytics and Analytics Platform System supporta Hadoop o Archiviazione BLOB di Azure.
  • SQL Server 2022 (16.x) e versioni successive supportano CREATE EXTERNAL TABLE AS SELECT (CETAS) per creare una tabella esterna e quindi esportare, in parallelo, il risultato di un'istruzione Transact-SQL SELECT in Azure Data Lake Storage (ADLS) Gen2, Archiviazione di Azure Account V2 e archiviazione oggetti compatibile con S3.

Nota

Le funzionalità e la sicurezza di CETAS per Istanza gestita di SQL di Azure sono diverse da SQL Server o Azure Synapse Analytics. Per altre informazioni, vedere la versione Istanza gestita di SQL di Azure di CREATE EXTERNAL TABLE AS SELECT.

Nota

Le funzionalità e la sicurezza di CETAS per i pool serverless in Azure Synapse Analytics sono diverse da SQL Server. Per altre informazioni, vedere CETAS con Synapse SQL.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

Argomenti

[ [ database_name . [ schema_name ]. ] | schema_name . ] table_name

Nome da uno a tre parti della tabella da creare nel database. Per una tabella esterna, il database relazionale archivia solo i metadati della tabella.

[ ( column_name [ ,...n ] ) ]

Nome di una colonna di tabella.

LOCATION

Si applica a: Azure Synapse Analytics e Piattaforma di strumenti analitici

'hdfs_folder'**
Specifica la posizione in cui scrivere i risultati dell'istruzione SELECT nell'origine dati esterna. La posizione è un nome di cartella e, se necessario, può includere un percorso relativo alla cartella radice del cluster Hadoop o di Archiviazione BLOB. PolyBase crea il percorso e la cartella, se non esiste già.

I file esterni vengono scritti in hdfs_folder e denominati QueryID_date_time_ID.format, dove ID è un identificatore incrementale ed format è il formato di dati esportato. Un esempio è QID776_20160130_182739_0.orc.

LOCATION deve puntare a una cartella e avere un oggetto finale /, ad esempio : aggregated_data/.

Si applica a: SQL Server 2022 (16.x) e versioni successive

prefix://path[:port] fornisce il protocollo di connettività (prefisso), il percorso e, facoltativamente, la porta, all'origine dati esterna, in cui verrà scritto il risultato dell'istruzione SELECT.

Se la destinazione è compatibile con S3, è necessario prima che esista un bucket, ma PolyBase può creare sottocartelle, se necessario. SQL Server 2022 (16.x) supporta Azure Data Lake Storage Gen2, Archiviazione di Azure Account V2 e Archiviazione oggetti compatibile con S3. I file ORC non sono attualmente supportati.

DATA_SOURCE = external_data_source_name

Specifica il nome dell'oggetto origine dati esterna che contiene il percorso in cui sono o verranno archiviati i dati esterni. Il percorso è un cluster Hadoop o un'istanza di Archiviazione BLOB di Azure. Per creare un'origine dati esterna, usare CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Specifica il nome dell'oggetto formato di file esterno che contiene il formato per il file di dati esterno. Per creare un formato di file esterno, usare CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Opzioni REJECT

Le opzioni di rifiuto non si applicano al momento dell'esecuzione dell'istruzione CREATE EXTERNAL TABLE AS SELECT. Vengono invece specificate qui in modo che il database sia in grado di usarle in un secondo tempo quando importa i dati dalla tabella esterna. Successivamente, quando l'istruzione CREATE TABLE AS SELECT seleziona dati dalla tabella esterna, il database usa le opzioni di rifiuto per determinare il numero o la percentuale di righe che può non essere possibile importare prima di interrompere l'importazione.

  • REJECT_VALUE = reject_value

    Specifica il valore o la percentuale di righe che possono non riuscire a eseguire l'importazione prima che il database interrompa l'importazione.

  • REJECT_TYPE = valore | percentuale

    Indica se l'opzione REJECT_VALUE è un valore letterale o una percentuale.

    • value

      Usato se REJECT_VALUE è un valore letterale, non una percentuale. Il database interrompe l'importazione di righe dal file di dati esterno quando il numero di righe non riuscite supera reject_value.

      Ad esempio, se REJECT_VALUE = 5 e REJECT_TYPE = value, il database interrompe l'importazione di righe dopo l'importazione di cinque righe.

    • percentuale

      Utilizzato se REJECT_VALUE è una percentuale, non un valore letterale. Il database interrompe l'importazione di righe dal file di dati esterno quando la percentuale di righe non riuscite supera reject_value. La percentuale di righe con esito negativo viene calcolata a intervalli. Valido solo nei pool SQL dedicati quando TYPE=HADOOP.

  • REJECT_SAMPLE_VALUE = reject_sample_value

    Obbligatorio quando REJECT_TYPE = percentage. Specifica il numero di righe da importare prima che il database ricalcola la percentuale di righe non riuscite.

    Ad esempio, se REJECT_SAMPLE_VALUE = 1000, il database calcola la percentuale di righe con esito negativo dopo che ha tentato di importare 1000 righe dal file di dati esterno. Se la percentuale di righe non riuscite è minore di reject_value, il database tenta di caricare altre 1000 righe. Il database continua a ricalcolare la percentuale di righe con esito negativo dopo aver tentato di importare ognuna delle 1000 righe aggiuntive.

    Nota

    Poiché il database calcola la percentuale di righe con esito negativo a intervalli, la percentuale effettiva di tali righe può superare reject_value.

    Esempio:

    Questo esempio illustra come le tre opzioni REJECT interagiscono tra loro. Ad esempio, se REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100, potrebbe verificarsi lo scenario seguente:

    • Il database tenta di caricare le prime 100 righe, di cui 25 hanno esisto negativo e 75 esito positivo.
    • La percentuale di righe con esito negativo viene calcolata al 25%, che è minore del valore di rifiuto pari al 30%. Non è quindi necessario interrompere il carico.
    • Il database tenta di caricare le 100 righe successive. Questa volta 25 hanno esito positivo e 75 esito negativo.
    • La percentuale di righe con esito negativo viene ricalcolata al 50%. La percentuale di righe con esito negativo ha superato il valore di rifiuto del 30%.
    • Il caricamento non riesce con il 50% delle righe con esito negativo dopo aver tentato di caricare 200 righe, un valore superiore al limite del 30% specificato.

WITH common_table_expression

Indica un set di risultati denominato temporaneo, noto come espressione di tabella comune (CTE). Per altre informazioni, vedere WITH common_table_expression (Transact-SQL)

SELECT <select_criteria>

Popola la nuova tabella con i risultati di un'istruzione SELECT. select_criteria è il corpo dell'istruzione SELECT che determina i dati da copiare nella nuova tabella. Per informazioni sulle istruzioni SELECT, vedere SELECT (Transact-SQL).

Nota

La clausola ORDER BY in SELECT non ha alcun effetto su CETAS.

Opzioni colonna

  • column_name [ ,... n ]

    I nomi di colonna non consentono le opzioni delle colonne menzionate in CREATE TABLE. In alternativa, è possibile specificare un elenco facoltativo di uno o più nomi di colonna per la nuova tabella. Le colonne della nuova tabella usano i nomi specificati. Quando si specificano nomi di colonna, il numero di colonne nell'elenco di colonne deve corrispondere al numero di colonne nei risultati di SELECT. Se non si specificano nomi di colonna, la nuova tabella di destinazione usa i nomi delle colonne nei risultati dell'istruzione select.

    Non è possibile specificare altre opzioni di colonna, ad esempio tipi di dati, regole di confronto o valori Null. Ognuno di questi attributi è derivato dai risultati dell'istruzione SELECT. Tuttavia, è possibile usare l'istruzione SELECT per modificare gli attributi. Per un esempio, vedere Usare CETAS per modificare gli attributi di colonna.

Autorizzazioni

Per eseguire questo comando, l'utente del database deve avere tutte le autorizzazioni o appartenenze seguenti:

  • Autorizzazione ALTER SCHEMA per lo schema locale che conterrà la nuova tabella o appartenenza al ruolo predefinito del database db_ddladmin.
  • Autorizzazione CREATE TABLE o appartenenza al ruolo predefinito del database db_ddladmin.
  • Autorizzazione SELECT per tutti gli oggetti a cui si fa riferimento in select_criteria.

L'account di accesso deve avere tutte le autorizzazioni che seguono:

  • AMMINISTRARE LE OPERAZIONI BULK
  • ALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMAT
  • In generale, è necessario disporre delle autorizzazioni per elencare il contenuto della cartella e scrivere nella cartella LOCATION per CETAS.
  • In Azure Synapse Analytics e nel sistema della piattaforma di analisi l'autorizzazione di scrittura per leggere e scrivere nella cartella esterna nel cluster Hadoop o nell'archiviazione BLOB di Azure.
  • In SQL Server 2022 (16.x), è necessario anche impostare le autorizzazioni appropriate per il percorso esterno. Autorizzazione di scrittura per l'output dei dati nel percorso e autorizzazione lettura per accedervi.
  • Per Archiviazione BLOB di Azure e Azure Data Lake Gen2, al SHARED ACCESS SIGNATURE token devono essere concessi i privilegi seguenti nel contenitore: Lettura, Scrittura, Elenco, Crea.
  • Per Archiviazione blog di Azure, è necessario selezionare la Allowed Servicescasella di controllo : Blob per generare il token di firma di accesso condiviso.
  • Per Azure Data Lake Gen2, è necessario selezionare le Allowed Servicescaselle di controllo : Container e Object per generare il token di firma di accesso condiviso.

Importante

L'autorizzazione ALTER ANY EXTERNAL DATA SOURCE concede a qualsiasi entità di sicurezza la possibilità di creare e modificare qualsiasi oggetto origine dati esterna e anche la possibilità di accedere a tutte le credenziali con ambito database nel database. Questa autorizzazione deve essere considerata con privilegi elevati e deve essere concessa solo a entità attendibili nel sistema.

Gestione degli errori

Quando CREATE EXTERNAL TABLE AS SELECT esporta dati in un file di testo delimitato, non esiste alcun file di rifiuto per le righe che non è possibile esportare.

Quando si crea la tabella esterna, il database prova a connettersi alla posizione esterna. Se la connessione non riesce, il comando non riesce e la tabella esterna non viene creata. La conferma dell'esito negativo del comando può richiedere un minuto o più poiché il database ritenta la connessione almeno tre volte.

Se CREATE EXTERNAL TABLE AS SELECT viene annullato o ha esito negativo, il database tenta una sola volta di rimuovere eventuali nuovi file e cartelle già creati nell'origine dati esterna.

In Azure Synapse Analytics and Analytics Platform System il database segnala eventuali errori Java che si verificano nell'origine dati esterna durante l'esportazione dei dati.

Osservazioni:

Dopo l'esecuzione dell'istruzione CREATE EXTERNAL TABLE AS SELECT, è possibile eseguire query Transact-SQL sulla tabella esterna. Queste operazioni importano dati nel database per la durata della query, a meno che non si esegua l'importazione tramite l'istruzione CREATE TABLE AS SELECT.

Il nome e la definizione della tabella esterna vengono archiviati nei metadati del database. I dati vengono archiviati nell'origine dati esterna.

L'istruzione CREATE EXTERNAL TABLE AS SELECT crea sempre una tabella non partizionata, anche se la tabella di origine è partizionata.

Per SQL Server 2022 (16.x), l'opzione allow polybase export deve essere abilitata tramite sp_configure. Per altre informazioni, vedere Impostare l'opzione di configurazione allow polybase export.

Per i piani di query in Azure Synapse Analytics e Piattaforma di strumenti analitici, creati con EXPLAIN, il database usa queste operazioni del piano di query per le tabelle esterne: Spostamento esterno dei dati di riproduzione casuale, Spostamento esterno dei dati trasmessi, Spostamento esterno delle partizioni.

In Piattaforma di strumenti analitici, come prerequisito per la creazione di una tabella esterna, l'amministratore del dispositivo deve configurare la connettività di Hadoop. Per altre informazioni, vedere l'argomento relativo alla configurazione delle connessioni ai dati esterni nella documentazione della piattaforma di strumenti analitici che può essere scaricata dall'Area download Microsoft.

Limitazioni e restrizioni

Poiché i dati delle tabelle esterne si trovano all'esterno del database, le operazioni di backup e ripristino operano solo sui dati archiviati nel database. Di conseguenza, viene eseguito il backup e il ripristino solo dei metadati.

Il database non verifica la connessione all'origine dati esterna quando si ripristina un backup del database che contiene una tabella esterna. Se l'origine originale non è accessibile, il ripristino dei metadati della tabella esterna avrà comunque esito positivo, ma le operazioni SELECT sulla tabella esterna avranno esito negativo.

Il database non garantisce la coerenza dei dati tra il database e i dati esterni. È responsabilità esclusiva del cliente mantenere la coerenza tra i dati esterni e il database.

Le operazioni DML (Data Manipulation Language) non sono supportate per le tabelle esterne. Ad esempio, non è possibile usare le istruzioni Transact-SQL update, insert o delete Transact-SQL per modificare i dati esterni.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW e DROP VIEW sono le uniche operazioni DDL (Data Definition Language) consentite per le tabelle esterne.

Limitazioni e restrizioni per Azure Synapse Analytics

  • Nei pool SQL dedicati di Azure Synapse Analytics e nel sistema della piattaforma di analisi PolyBase possono usare un massimo di 33.000 file per cartella durante l'esecuzione di 32 query PolyBase simultanee. Questo numero massimo include i file e le sottocartelle presenti in ogni cartella HDFS. Se il livello di concorrenza è inferiore a 32, un utente può eseguire le query PolyBase sulle cartelle in HDFS che contengono più di 33.000 file. Per gli utenti di Hadoop e PolyBase, è consigliabile usare percorsi brevi per i file e non più di 30.000 file per ogni cartella HDFS. Quando si fa riferimento a troppi file, si verifica un'eccezione di memoria insufficiente in JVM.

  • Nei pool SQL serverless non è possibile creare tabelle esterne in una posizione in cui sono attualmente presenti dati. Per riutilizzare una posizione che è stata usata per archiviare dati, è necessario eliminare manualmente la posizione in Azure Data Lake Storage. Per altre limitazioni e procedure consigliate, vedere Procedure consigliate per l'ottimizzazione dei filtri.

Nei pool SQL dedicati di Azure Synapse Analytics e nel sistema della piattaforma di analisi, quando CREATE EXTERNAL TABLE AS SELECT seleziona da un RCFile, i valori di colonna nel file RCFile non devono contenere il carattere pipe (|).

SET ROWCOUNT (Transact-SQL) non ha alcun effetto su CREATE EXTERNAL TABLE AS SELECT. Per ottenere un comportamento simile, usare TOP (Transact-SQL).

Vedere Denominazione e riferimento a contenitori, BLOB e metadati per le limitazioni sui nomi di file.

Errori di carattere

I caratteri seguenti presenti nei dati possono causare errori, inclusi i record rifiutati con CREATE EXTERNAL TABLE AS SELECT in file Parquet.

In Azure Synapse Analytics e Piattaforma di strumenti analitici questo si applica anche ai file ORC.

  • |
  • " (virgolette)
  • \r\n
  • \r
  • \n

Per usare CREATE EXTERNAL TABLE AS SELECT con questi caratteri, è prima di tutto necessario eseguire l'istruzione CREATE EXTERNAL TABLE AS SELECT per esportare i dati in file di testo delimitati, che è poi possibile convertire nel formato Parquet oppure ORC con uno strumento esterno.

Uso di parquet

Quando si utilizzano file parquet, CREATE EXTERNAL TABLE AS SELECT genererà un file Parquet per CPU disponibile, fino al grado massimo di parallelismo configurato (MAXDOP). Ogni file può aumentare fino a 190 GB, dopo che SQL Server genererà più file Parquet in base alle esigenze.

L'hint OPTION (MAXDOP n) per la query influisce solo sulla parte SELECT di CREATE EXTERNAL TABLE AS SELECT, non influisce sulla quantità di file parquet. Viene considerato solo MAXDOP a livello di database e MAXDOP a livello di istanza.

Blocco

Accetta un blocco condiviso per l'oggetto SCHEMARESOLUTION.

Tipi di dati supportati

È possibile usare CETAS per archiviare i set di risultati con i tipi di dati SQL seguenti:

  • binary
  • varbinary
  • char
  • varchar
  • nchar
  • nvarchar
  • smalldate
  • data
  • datetime
  • datetime2
  • datetimeoffset
  • time
  • decimal
  • numeric
  • float
  • real
  • bigint
  • tinyint
  • smallint
  • int
  • bigint
  • bit
  • money
  • smallmoney

Esempi

R. Creare una tabella di Hadoop usando CREATE EXTERNAL TABLE AS SELECT

Si applica a: Azure Synapse Analytics e Piattaforma di strumenti analitici

L'esempio seguente crea una nuova tabella esterna denominata hdfsCustomer che usa le definizioni e i dati delle colonne della tabella di origine dimCustomer.

La definizione della tabella viene archiviata nel database e i risultati dell'istruzione SELECT vengono esportati nel /pdwdata/customer.tbl file nell'origine dati esterna Hadoop customer_ds. Il file viene formattato in base al formato di file esterno customer_ff.

Il nome del file è generato dal database e contiene l'ID di query per facilitare l'allineamento del file con la query che lo ha generato.

Il percorso hdfs://xxx.xxx.xxx.xxx:5000/files/ che precede la directory Customer deve essere già presente. Se la directory Customer non esiste, il database crea la directory.

Nota

In questo esempio viene specificato 5000. Se la porta non è specificata, il database usa 8020 come porta predefinita.

Il percorso e nome file Hadoop risultanti saranno hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt..

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. Usare un hint per la query con CREATE EXTERNAL TABLE AS SELECT

Si applica a: Azure Synapse Analytics e Piattaforma di strumenti analitici

Questa query illustra la sintassi di base per l'uso di un hint di join per la query con l'istruzione CREATE EXTERNAL TABLE AS SELECT. Dopo l'inoltro della query il database usa la strategia hash join per generare il piano di query. Per altre informazioni sugli hint di join e su come usare la clausola OPTION, vedere Clausola OPTION (Transact-SQL).

Nota

In questo esempio viene specificato 5000. Se la porta non è specificata, il database usa 8020 come porta predefinita.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. Usare CETAS per modificare gli attributi di colonna

Si applica a: Azure Synapse Analytics e Piattaforma di strumenti analitici

In questo esempio viene usata un'istruzione CETAS per modificare i tipi di dati, il supporto dei valori Null e le regole di confronto per diverse colonne della tabella FactInternetSales.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. Usare CREATE EXTERNAL TABLE AS SELECT esportando i dati come Parquet

Si applica a:SQL Server 2022 (16.x)

Nell'esempio seguente viene creata una nuova tabella esterna denominata ext_sales che usa i dati della tabella SalesOrderDetail di AdventureWorks2022. L'opzione di configurazione allow polybase export deve essere abilitata.

Il risultato dell'istruzione SELECT verrà salvato nell'archivio oggetti compatibile con S3 configurato in precedenza e denominato s3_edse le credenziali appropriate create come s3_dsc. Il percorso del file Parquet sarà <ip>:<port>/cetas/sales.parquet, dove cetas è il bucket di archiviazione creato in precedenza.

Nota

Il formato Delta è attualmente supportato solo come di sola lettura.

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. Usare CREATE EXTERNAL TABLE AS SELECT dalla tabella Delta a Parquet

Si applica a:SQL Server 2022 (16.x)

Nell'esempio seguente viene creata una nuova tabella esterna denominata Delta_to_Parquet, che usa il tipo di tabella Delta dei dati che si trova in un archivio oggetti compatibile con S3 denominato s3_deltae scrive il risultato in un'altra origine dati denominata s3_parquet come file parquet. Per tale esempio viene usato il comando OPENROWSET. L'opzione di configurazione allow polybase export deve essere abilitata.

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. Usare CREATE EXTERNAL TABLE AS SELECT con una vista come origine

Si applica a: pool SQL serverless di Azure Synapse Analytics e pool SQL dedicati.

In questo esempio è possibile vedere un esempio di codice modello per scrivere CETAS con una vista definita dall'utente come origine, usando l'identità gestita come autenticazione e wasbs:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'wasbs://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

G. Usare CREATE EXTERNAL TABLE AS SELECT con una vista come origine

Si applica a: pool SQL serverless di Azure Synapse Analytics e pool SQL dedicati.

In questo esempio è possibile vedere un esempio di codice modello per scrivere CETAS con una vista definita dall'utente come origine, usando l'identità gestita come autenticazione e https:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

Passaggi successivi

Si applica a: Istanza gestita di SQL di Azure

Crea una tabella esterna e quindi esporta, in parallelo, i risultati di un'istruzione Transact-SQL SELECT.

È possibile usare CREATE EXTERNAL TABLE AS SELECT (CETAS) per completare le attività seguenti:

  • Creare una tabella esterna sopra i file Parquet o CSV in Archiviazione BLOB di Azure o Azure Data Lake Storage (ADLS) Gen2.
  • Esportare, in parallelo, i risultati di un'istruzione T-SQL SELECT nella tabella esterna creata.
  • Per altre funzionalità di virtualizzazione dei dati di Istanza gestita di SQL di Azure, vedere Virtualizzazione dei dati con Istanza gestita di SQL di Azure.

Nota

Questo contenuto si applica solo a Istanza gestita di SQL di Azure. Per altre piattaforme, scegliere la versione appropriata di CREATE EXTERNAL TABLE AS SELECT dal selettore a discesa.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>

Argomenti

[ [ database_name . [ schema_name ]. ] | schema_name . ] table_name

Nome della tabella da creare, composto da una, due o tre parti. Per una tabella esterna, vengono archiviati solo i metadati della tabella. Nessun dato effettivo viene spostato o archiviato.

LOCATION = 'path_to_folder'

Specifica la posizione in cui scrivere i risultati dell'istruzione SELECT nell'origine dati esterna. La cartella radice è la posizione dei dati specificata nell'origine dati esterna. LOCATION deve puntare a una cartella e avere un oggetto finale /. Esempio: aggregated_data/.

La cartella di destinazione per CETAS deve essere vuota. Se il percorso e la cartella non esistono già, vengono creati automaticamente.

DATA_SOURCE = external_data_source_name

Specifica il nome dell'oggetto origine dati esterna che contiene la posizione in cui verranno archiviati i dati esterni. Per creare un'origine dati esterna, usare CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Specifica il nome dell'oggetto formato di file esterno che contiene il formato per il file di dati esterno. Per creare un formato di file esterno, usare CREATE EXTERNAL FILE FORMAT (Transact-SQL). Attualmente sono supportati solo i formati di file esterni con FORMAT_TYPE=PARQUET e FORMAT_TYPE=DELIMITEDTEXT. La compressione GZip per il formato DELIMITEDTEXT non è supportata.

[, PARTITION ( nome colonna [ , ... n ] ) ]

Partiziona i dati di output in più percorsi di file Parquet. Il partizionamento avviene per ogni colonna specificata (column_name), che corrisponde ai caratteri jolly (*) nella rispettiva colonna di partizionamento. Il numero di colonne nella parte PARTITION deve corrispondere al numero di caratteri jolly nella posizione. Deve essere presente almeno una colonna non utilizzata per il partizionamento.

WITH <common_table_expression>

Indica un set di risultati denominato temporaneo, noto come espressione di tabella comune (CTE). Per altre informazioni, vedere WITH common_table_expression (Transact-SQL).

SELECT <select_criteria>

Popola la nuova tabella con i risultati di un'istruzione SELECT. select_criteria è il corpo dell'istruzione SELECT che determina i dati da copiare nella nuova tabella. Per informazioni sulle istruzioni SELECT, vedere SELECT (Transact-SQL).

Nota

La clausola ORDER BY in SELECT non è supportata per CETAS.

Autorizzazioni

Autorizzazioni nell'archiviazione

È necessario disporre delle autorizzazioni per elencare il contenuto della cartella e scrivere nel percorso LOCATION per il corretto funzionamento di CETAS.

I metodi di autenticazione supportati sono un'identità gestita o un token di firma di accesso condiviso.

  • Se si usa l'identità gestita per l'autenticazione, assicurarsi che l'entità servizio dell'istanza gestita di SQL abbia un ruolo di Collaboratore ai dati dei BLOB di archiviazione nel contenitore di destinazione.
  • Se si usa un token di firma di accesso condiviso, sono necessarie le autorizzazioni Lettura, Scrittura ed Elenco .
  • Per Archiviazione blog di Azure, è necessario selezionare la Allowed Servicescasella di controllo : Blob per generare il token di firma di accesso condiviso.
  • Per Azure Data Lake Gen2, è necessario selezionare le Allowed Servicescaselle di controllo : Container e Object per generare il token di firma di accesso condiviso.

Un'identità gestita assegnata dall'utente non è supportata. L'autenticazione pass-through di Microsoft Entra non è supportata. Microsoft Entra ID (in precedenza Azure Active Directory).

Autorizzazioni nell'istanza gestita di SQL

Per eseguire questo comando, l'utente del database deve avere tutte le autorizzazioni o appartenenze seguenti:

  • Autorizzazione ALTER SCHEMA per lo schema locale che conterrà la nuova tabella o appartenenza al ruolo predefinito del database db_ddladmin.
  • Autorizzazione CREATE TABLE o appartenenza al ruolo predefinito del database db_ddladmin.
  • Autorizzazione SELECT per tutti gli oggetti a cui si fa riferimento in select_criteria.

L'account di accesso deve avere tutte le autorizzazioni che seguono:

  • AMMINISTRARE LE OPERAZIONI BULK
  • ALTER ANY EXTERNAL DATA SOURCE
  • ALTER ANY EXTERNAL FILE FORMAT

Importante

L'autorizzazione ALTER ANY EXTERNAL DATA SOURCE concede a qualsiasi entità di sicurezza la possibilità di creare e modificare qualsiasi oggetto origine dati esterna e anche la possibilità di accedere a tutte le credenziali con ambito database nel database. Questa autorizzazione deve essere considerata con privilegi elevati e deve essere concessa solo a entità attendibili nel sistema.

Tipi di dati supportati

CETAS archivia i set di risultati con i tipi di dati SQL seguenti:

  • binary
  • varbinary
  • char
  • varchar
  • nchar
  • nvarchar
  • smalldatetime
  • data
  • datetime
  • datetime2
  • datetimeoffset
  • time
  • decimal
  • numeric
  • float
  • real
  • bigint
  • tinyint
  • smallint
  • int
  • bigint
  • bit
  • money
  • smallmoney

Nota

Non è possibile usare LOB di dimensioni maggiori di 1 MB con CETAS.

Limitazioni e restrizioni

  • CREATE EXTERNAL TABLE AS SELECT (CETAS) per Istanza gestita di SQL di Azure è disabilitato per impostazione predefinita. Per altre informazioni, vedere la sezione successiva Disabilitata per impostazione predefinita.
  • Per altre informazioni sulle limitazioni o sui problemi noti relativi alla virtualizzazione dei dati in Istanza gestita di SQL di Azure, vedere Limitazioni e problemi noti.

Poiché i dati delle tabelle esterne si trovano all'esterno del database, le operazioni di backup e ripristino operano solo sui dati archiviati nel database. Di conseguenza, viene eseguito il backup e il ripristino solo dei metadati.

Il database non verifica la connessione all'origine dati esterna quando si ripristina un backup del database che contiene una tabella esterna. Se l'origine originale non è accessibile, il ripristino dei metadati della tabella esterna ha ancora esito positivo, ma le operazioni SELECT sulla tabella esterna hanno esito negativo.

Il database non garantisce la coerenza dei dati tra il database e i dati esterni. È responsabilità esclusiva del cliente mantenere la coerenza tra i dati esterni e il database.

Le operazioni DML (Data Manipulation Language) non sono supportate per le tabelle esterne. Non è ad esempio possibile usare le istruzioni update, insert o delete di Transact-SQL per modificare i dati esterni.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW e DROP VIEW sono le uniche operazioni DDL (Data Definition Language) consentite per le tabelle esterne.

Non è possibile creare tabelle esterne in una posizione in cui sono attualmente presenti dati. Per riutilizzare una posizione che è stata usata per archiviare dati, è necessario eliminare manualmente la posizione in Azure Data Lake Storage.

SET ROWCOUNT (Transact-SQL) non ha alcun effetto su CREATE EXTERNAL TABLE AS SELECT. Per ottenere un comportamento simile, usare TOP (Transact-SQL).

Vedere Denominazione e riferimento a contenitori, BLOB e metadati per le limitazioni sui nomi di file.

Tipi di archiviazione

È possibile archiviare i file in Archiviazione BLOB di Azure, File di Azure o Azure Data Lake Storage Gen2. Per eseguire query sui file, è necessario specificare il percorso in un formato specifico e usare il prefisso del tipo di posizione corrispondente al tipo di origine esterna e endpoint/protocollo, ad esempio gli esempi seguenti:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

Importante

Il prefisso del tipo di posizione specificato viene usato per scegliere il protocollo ottimale per la comunicazione e sfruttare tutte le funzionalità avanzate offerte dal tipo di archiviazione specifico. L'uso del prefisso generico https:// è disabilitato. Usare sempre prefissi specifici dell'endpoint.

Disattivato per impostazione predefinita

CREATE EXTERNAL TABLE AS SELECT (CETAS) consente di esportare dati dall'istanza gestita di SQL in un account di archiviazione esterno, quindi esiste un potenziale rischio di esfiltrazione di dati con queste funzionalità. Pertanto, CETAS è disabilitato per impostazione predefinita per Istanza gestita di SQL di Azure.

Abilitare CETAS

CETAS per Istanza gestita di SQL di Azure può essere abilitato solo tramite un metodo che richiede autorizzazioni di Azure elevate e non può essere abilitato tramite T-SQL. A causa del rischio di esfiltrazione di dati non autorizzati, CETAS non può essere abilitato tramite la sp_configure stored procedure T-SQL, ma richiede invece che l'azione dell'utente all'esterno dell'istanza gestita di SQL.

Autorizzazioni per abilitare CETAS

Per abilitare tramite Azure PowerShell, l'utente che esegue il comando deve avere ruoli Collaboratore o Controllo degli accessi in base al ruolo di Azure di Gestione sicurezza SQL per l'istanza gestita di SQL.

È anche possibile creare un ruolo personalizzato per questa operazione, richiedendo l'azione Lettura e scrittura per l'azione Microsoft.Sql/managedInstances/serverConfigurationOptions .

Metodi per abilitare CETAS

Per richiamare i comandi di PowerShell in un computer, è necessario installare localmente il pacchetto Az versione 9.7.0 o successiva. In alternativa, prendere in considerazione l'uso di Azure Cloud Shell per eseguire Azure PowerShell su shell.azure.com.

Per prima cosa, accedere ad Azure e impostare il contesto appropriato per la sottoscrizione:

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

Per gestire l'opzione di configurazione del server "allowPolybaseExport", modificare gli script di PowerShell seguenti in base alla sottoscrizione e al nome dell'istanza gestita di SQL, quindi eseguire i comandi. Per altre informazioni, vedere Set-AzSqlServerConfigurationOption e Get-AzSqlServerConfigurationOption.

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

Per disabilitare l'opzione di configurazione del server "allowPolybaseExport":

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

Per ottenere il valore corrente dell'opzione di configurazione del server "allowPolybaseExport":

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

Verificare lo stato di CETAS

In qualsiasi momento è possibile controllare lo stato corrente dell'opzione di configurazione CETAS.

Connettersi all'istanza gestita di SQL. Eseguire il T-SQL seguente e osservare la value colonna della risposta. Al termine della modifica della configurazione del server, i risultati di questa query devono corrispondere all'impostazione desiderata.

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

Risoluzione dei problemi

Per altri passaggi per risolvere i problemi di virtualizzazione dei dati in Istanza gestita di SQL di Azure, vedere Risolvere i problemi. La gestione degli errori e i messaggi di errore comuni per CETAS in Istanza gestita di SQL di Azure segue.

Gestione degli errori

Quando CREATE EXTERNAL TABLE AS SELECT esporta dati in un file di testo delimitato, non esiste alcun file di rifiuto per le righe che non è possibile esportare.

Quando si crea la tabella esterna, il database prova a connettersi alla posizione esterna. Se la connessione non riesce, il comando non riesce e la tabella esterna non verrà creata. La conferma dell'esito negativo del comando può richiedere un minuto o più poiché il database ritenta la connessione almeno tre volte.

Messaggi di errore comuni

Questi messaggi di errore comuni hanno spiegazioni rapide per CETAS per Istanza gestita di SQL di Azure.

  1. Specifica di una posizione già esistente nella risorsa di archiviazione.

    Soluzione: cancellare il percorso di archiviazione (incluso lo snapshot) o modificare il parametro di posizione nella query.

    Messaggio di errore di esempio: Msg 15842: Cannot create external table. External table location already exists.

  2. Valori di colonna formattati usando oggetti JSON.

    Soluzione: convertire la colonna valore in una singola colonna VARCHAR o NVARCHAR o un set di colonne con tipi definiti in modo esplicito.

    Messaggio di errore di esempio: Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.

  3. Parametro location non valido (ad esempio, più //).

    Soluzione: correzione del parametro location.

    Messaggio di errore di esempio: Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.

  4. Manca una delle opzioni necessarie (DATA_SOURCE, FILE_FORMAT, LOCATION).

    Soluzione: aggiungere il parametro mancante alla query CETAS.

    Messaggio di errore di esempio: Msg 46505: Missing required external DDL option 'FILE_FORMAT'

  5. Problemi di accesso (credenziali non valide, credenziali scadute o credenziali con autorizzazioni insufficienti). La possibilità alternativa è un percorso non valido, in cui l'istanza gestita di SQL ha ricevuto un errore 404 dall'archiviazione.

    Soluzione: verificare la validità e le autorizzazioni delle credenziali. In alternativa, verificare che il percorso sia valido e che esistano risorse di archiviazione. Usare il percorso adls://<container>@<storage_account>.blob.core.windows.net/<path>/URL .

    Messaggio di errore di esempio: Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'

  6. La parte della posizione di DATA_SOURCE contiene caratteri jolly.

    Soluzione: rimuovere i caratteri jolly dalla posizione.

    Messaggio di errore di esempio: Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.

  7. Il numero di caratteri jolly nel parametro LOCATION e il numero di colonne partizionate non corrispondono.

    Soluzione: verificare lo stesso numero di caratteri jolly in LOCATION delle colonne di partizione.

    Messaggio di errore di esempio: Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.

  8. Il nome della colonna nella clausola PARTITION non corrisponde ad alcuna colonna nell'elenco.

    Soluzione: assicurarsi che le colonne in PARTITION siano valide.

    Messaggio di errore di esempio: Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list

  9. Colonna specificata più volte nell'elenco PARTITION.

    Soluzione: assicurarsi che le colonne nella clausola PARTITION siano univoche.

    Messaggio di errore di esempio: Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.

  10. La colonna è stata specificata più di una volta nell'elenco PARTITION oppure non corrisponde a colonne dall'elenco SELECT.

    Soluzione: assicurarsi che non siano presenti duplicati nell'elenco di partizioni e che le colonne di partizione esistano nella parte SELECT.

    Messaggi di errore di esempio: Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter. o Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.

  11. Utilizzo di tutte le colonne nell'elenco PARTITION.

    Soluzione: almeno una delle colonne della parte SELECT non deve essere inclusa nella parte PARTITION della query.

    Messaggio di errore di esempio: Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.

  12. La funzionalità è disabilitata.

    Soluzione: abilitare la funzionalità usando la sezione Disabilitata per impostazione predefinita in questo articolo.

    Messaggio di errore di esempio: Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

Blocco

Accetta un blocco condiviso per l'oggetto SCHEMARESOLUTION.

Esempi

R. Usare CETAS con una vista per creare una tabella esterna usando l'identità gestita

Questo esempio fornisce il codice per la scrittura di CETAS con una vista come origine, usando un'identità gestita dal sistema un'autenticazione.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

B. Usare CETAS con una vista per creare una tabella esterna con l'autenticazione sas

Questo esempio fornisce il codice per la scrittura di CETAS con una vista come origine, usando un token di firma di accesso condiviso come autenticazione.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = '<azure_shared_access_signature>' ;
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

C. Creare una tabella esterna in un singolo file Parquet nella risorsa di archiviazione

I due esempi successivi illustrano come eseguire l'offload di alcuni dati da una tabella locale in una tabella esterna archiviata come file parquet nel contenitore di archiviazione BLOB di Azure. Sono progettati per lavorare con AdventureWorks2022 il database. In questo esempio viene illustrata la creazione di una tabella esterna come un singolo file parquet, in cui l'esempio seguente illustra come creare una tabella esterna e partizionarla in più cartelle con file parquet.

L'esempio seguente usa l'identità gestita per l'autenticazione. Di conseguenza, assicurarsi che l'entità servizio Istanza gestita di SQL di Azure abbia il ruolo Collaboratore ai dati dei BLOB di archiviazione nel contenitore Archiviazione BLOB di Azure. In alternativa, è possibile modificare l'esempio e usare i token sas (Shared Access Secret) per l'autenticazione.

L'esempio seguente consente di creare una tabella esterna in un singolo file Parquet in Archiviazione BLOB di Azure, selezionando dalla SalesOrderHeader tabella per gli ordini precedenti al 1 gennaio 2014:

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );
GO

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

D. Creare una tabella esterna partizionata in più file Parquet archiviati in un albero delle cartelle

Questo esempio si basa sull'esempio precedente per illustrare come creare una tabella esterna e partizionarla in più cartelle con file Parquet. È possibile usare le tabelle partizionate per ottenere vantaggi in termini di prestazioni se il set di dati è di grandi dimensioni.

Creare una tabella esterna dai SalesOrderHeader dati usando i passaggi dell'esempio B, ma partizionare la tabella esterna per OrderDate anno e mese. Quando si eseguono query su tabelle esterne partizionate, è possibile trarre vantaggio dall'eliminazione delle partizioni per le prestazioni.

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

Passaggi successivi