CREATE EXTERNAL DATA SOURCE (Transact-SQL)

Panoramica: SQL Server 2016

Applica a: SQL Server 2016 (13.x)

Crea un'origine dati esterna per le query PolyBase. Le origini dati esterne vengono usate per stabilire la connettività e supportano questi casi d'uso principali:

• Virtualizzazione dei dati e caricamento dei dati con PolyBase in SQL Server
• Operazioni di caricamento bulk con BULK INSERT o OPENROWSET

convenzioni di sintassi Transact-SQL

Sintassi per SQL Server 2016

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { HADOOP } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database in SQL Server.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

Percorso:

• <Namenode> = nome del computer, URI del servizio dei nomi o indirizzo IP di Namenode nel cluster Hadoop. PolyBase deve risolvere tutti i nomi DNS usati dal cluster Hadoop.
• port = porta su cui è in ascolto l'origine dati esterna. Per trovare la porta in Hadoop, si usa il parametro di configurazione fs.defaultFS. L'impostazione predefinita è 8020.
• <container> = contenitore dell'account di archiviazione che include i dati. I contenitori radice sono di sola lettura, di conseguenza i dati non possono essere riscritti nel contenitore.
• <storage_account> = nome dell'account di archiviazione della risorsa Azure.
• <server_name> = nome host.
• <instance_name> = nome dell'istanza denominata SQL Server. Usato se è in esecuzione SQL Server servizio Browser nell'istanza di destinazione.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• Il Motore di database di SQL Server non verifica l'esistenza dell'origine dati esterna al momento della creazione dell'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.
• Per garantire una semantica di esecuzione di query coerente, usare la stessa origine dati esterna per tutte le tabelle quando si eseguono query su Hadoop.
• wasbs è facoltativo, ma consigliato in SQL Server 2016 (13.x) per l'accesso a Account di archiviazione di Azure perché i dati verranno inviati usando una connessione TLS/SSL sicura.
• Per garantire la corretta esecuzione delle query di PolyBase durante un failover di Namenode di Hadoop, provare a usare un indirizzo IP virtuale per l'istanza di Namenode del cluster Hadoop. In caso contrario, eseguire ALTER EXTERNAL DATA SOURCE per puntare alla nuova posizione.

CREDENZIALE = credential_name

Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna.

CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.

Per creare credenziali con ambito database, vedere CREATE DATABASE SCOPED CREDENTIAL.

TIPO = * [ HADOOP ] *

Specifica il tipo dell'origine dati esterna da configurare. In SQL Server 2016 questo parametro è sempre obbligatorio e deve essere specificato solo come HADOOP. Supporta le connessioni a Cloudera CDH, Hortonworks HDP o a un account Archiviazione di Azure. Il comportamento di questo parametro è diverso nelle versioni successive di SQL Server.

Per un esempio di utilizzo di TYPE = HADOOP per caricare dati da un account Archiviazione di Azure, vedere Creare l'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia wasb://

RESOURCE_MANAGER_LOCATION = *'ResourceManager_URI[:p ort]'

Configurare questo valore facoltativo quando ci si connette a Cloudera CDH, Hortonworks HDP o solo un account Archiviazione di Azure. Per un elenco completo delle versioni di Hadoop supportate, vedere Configurazione della connettività PolyBase.

RESOURCE_MANAGER_LOCATION Quando viene definito, Query Optimizer prende una decisione basata sui costi per migliorare le prestazioni. È possibile usare un processo MapReduce per eseguire il pushdown del calcolo in Hadoop. Se si specifica RESOURCE_MANAGER_LOCATION, è possibile ridurre significativamente il volume di dati trasferiti tra Hadoop e SQL Server, con conseguente miglioramento delle prestazioni delle query.

Se la Resource Manager non è specificata, il push del calcolo in Hadoop è disabilitato per le query PolyBase. In Creare un'origine dati esterna per fare riferimento a Hadoop con il pushdown abilitato viene fornito un esempio concreto, oltre a ulteriori indicazioni.

Il valore RESOURCE_MANAGER_LOCATION non viene convalidato quando si crea l'origine dati esterna. L'immissione di un valore non corretto potrebbe causare un errore di query in fase di esecuzione ogni volta che si tenta il push-down perché il valore specificato non sarebbe in grado di risolvere.

Affinché PolyBase funzioni correttamente con un'origine dati esterna Hadoop, le porte per i componenti del cluster Hadoop seguenti devono essere aperte:

• Porte HDFS
  • Namenode
  • Datanode
• Resource Manager
  • Invio di processi
• Cronologia dei processi

Se la porta non è specificata, per la scelta del valore predefinito si usa l'impostazione corrente della configurazione 'hadoop connectivity'.

Nella tabella seguente vengono illustrate le porte predefinite per questi componenti. Esiste una dipendenza della versione di Hadoop e la possibilità di una configurazione personalizzata che non usa l'assegnazione di porta predefinita.

Autorizzazioni

Richiede CONTROL autorizzazione per il database in SQL Server.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Sicurezza

PolyBase supporta l'autenticazione basata su proxy per la maggior parte delle origini dati esterne. Creare credenziali con ambito database per creare l'account proxy.

Esempi

R. Creare un'origine dati esterna per fare riferimento a Hadoop

Per creare un'origine dati esterna per fare riferimento al cluster Hortonworks HDP o Cloudera CDH Hadoop, specificare il nome del computer o l'indirizzo IP di Namenode Hadoop e la porta.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8050'
);

B. Creare un'origine dati esterna per fare riferimento a Hadoop con il pushdown abilitato

Specificare l'opzione RESOURCE_MANAGER_LOCATION per abilitare il pushdown del calcolo in Hadoop per le query PolyBase. Dopo l'abilitazione, PolyBase prende una decisione basata sui costi per determinare se eseguire il push del calcolo delle query in Hadoop.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8020',
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Creare un'origine dati esterna per fare riferimento a Hadoop con protezione Kerberos

Per verificare se il cluster Hadoop è protetto tramite Kerberos, controllare il valore della proprietà hadoop.security.authentication nel file core-site.xml di Hadoop. Per fare riferimento a un cluster Hadoop protetto tramite Kerberos, è necessario specificare una credenziale con ambito database che contiene il nome utente e la password di Kerberos. La chiave master del database viene usata per crittografare il segreto della credenziale con ambito database.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>',
     SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Creare un'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia wasb://

In questo esempio l'origine dati esterna è un account di archiviazione Azure V2 denominato logs. Il contenitore di stoccaggio è chiamato daily. Il Archiviazione di Azure'origine dati esterna è solo per il trasferimento dei dati. e non supporta il pushdown dei predicati. Gli spazi dei nomi gerarchici non sono supportati durante l'accesso ai dati tramite l'interfaccia wasb:// .

Questo esempio illustra come creare le credenziali con ambito database per l'autenticazione in un account di archiviazione Azure V2. Specificare la chiave dell'account Archiviazione di Azure nel segreto delle credenziali del database. È possibile specificare qualsiasi stringa nell'identità delle credenziali con ambito database perché non viene usata durante l'autenticazione per Archiviazione di Azure. Quando ci si connette al Archiviazione di Azure tramite wasb o wasbs, l'autenticazione deve essere eseguita con una chiave dell'account di archiviazione, non con una firma di accesso condiviso.

In SQL Server 2016 (13.x), TYPE deve essere impostato su HADOOP anche quando si accede a Archiviazione di Azure.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
     SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential
);

Contenuti correlati

• ALTER EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• configurazione della connettività PolyBase (Transact-SQL)

Panoramica: SQL Server 2017

Applica a: SQL Server 2017 (14.x)

Crea un'origine dati esterna per le query PolyBase. Le origini dati esterne vengono usate per stabilire la connettività e supportano questi casi d'uso principali:

• Virtualizzazione dei dati e caricamento dei dati con PolyBase in SQL Server
• Operazioni di caricamento bulk con BULK INSERT o OPENROWSET

convenzioni di sintassi Transact-SQL

Sintassi per SQL Server 2017

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database in SQL Server.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

Percorso:

• <Namenode> = nome del computer, URI del servizio dei nomi o indirizzo IP di Namenode nel cluster Hadoop. PolyBase deve risolvere tutti i nomi DNS usati dal cluster Hadoop.
• port = porta su cui è in ascolto l'origine dati esterna. Per trovare la porta in Hadoop, si usa il parametro di configurazione fs.defaultFS. L'impostazione predefinita è 8020.
• <container> = contenitore dell'account di archiviazione che include i dati. I contenitori radice sono di sola lettura, di conseguenza i dati non possono essere riscritti nel contenitore.
• <storage_account> = nome dell'account di archiviazione della risorsa Azure.
• <server_name> = nome host.
• <instance_name> = nome dell'istanza denominata SQL Server. Usato se è in esecuzione SQL Server servizio Browser nell'istanza di destinazione.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• Il Motore di database di SQL Server non verifica l'esistenza dell'origine dati esterna al momento della creazione dell'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.
• Per garantire una semantica di esecuzione di query coerente, usare la stessa origine dati esterna per tutte le tabelle quando si eseguono query su Hadoop.
• Per la connessione tramite Driver={<Name of Driver>} specificare ODBC.
• wasbs è facoltativo, ma consigliato in SQL Server 2017 (14.x) per l'accesso a Account di archiviazione di Azure perché i dati verranno inviati usando una connessione TLS/SSL sicura.
• Per garantire la corretta esecuzione delle query di PolyBase durante un failover di Namenode di Hadoop, provare a usare un indirizzo IP virtuale per l'istanza di Namenode del cluster Hadoop. In caso contrario, eseguire ALTER EXTERNAL DATA SOURCE per puntare alla nuova posizione.

CREDENZIALE = credential_name

Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna.

Note aggiuntive e indicazioni utili per la creazione delle credenziali:

• CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.
• Quando TYPE = BLOB_STORAGE, è necessario creare le credenziali usando SHARED ACCESS SIGNATURE come identità.
• TYPE = BLOB_STORAGE è consentito solo per le operazioni bulk; non è possibile creare tabelle esterne per un'origine dati esterna con TYPE = BLOB_STORAGE.
• Quando ci si connette al Archiviazione di Azure tramite wasb o wasbs, l'autenticazione deve essere eseguita con una chiave dell'account di archiviazione, non con una firma di accesso condiviso.
• Quando TYPE = HADOOP è necessario creare le credenziali usando la chiave dell'account di archiviazione come SECRET.

Esistono diversi modi per creare una firma di accesso condiviso:

• È possibile creare un token di firma di accesso condiviso passando al portale Azure -><Your_Storage_Account> -> Firma di accesso condiviso -> Configurare le autorizzazioni -> Generare firma di accesso condiviso e stringa di connessione. Per altre informazioni, vedere Generare una firma di accesso condiviso.

• È possibile creare e configurare una firma di accesso condiviso con Azure Storage Explorer.

• È possibile creare un token di firma di accesso condiviso a livello di codice tramite PowerShell, interfaccia della riga di comando di Azure, .NET e API REST. Per altre informazioni, vedere A accesso limitato alle risorse di Archiviazione di Azure usando firme di accesso condiviso (SAS).

• Il token di firma di accesso condiviso deve essere configurato come segue:

  • Quando viene generato un token di firma di accesso condiviso, include un punto interrogativo ('?') all'inizio del token. Escludere l'elemento iniziale ? quando configurato come SECRET.
  • Usare un periodo di scadenza valido (tutte le date sono espresse in formato UTC).

• Concedere almeno l'autorizzazione di lettura per il file che deve essere caricato ( ad esempio srt=o&sp=r). È possibile creare più firme di accesso condiviso per casi d'uso diversi. Le autorizzazioni devono essere concesse come segue:

  Azione	Autorizzazione
  Lettura di dati da un file	Lettura
  Leggere i dati da più file e sottocartelle	Lettura e Lista

Per un esempio di utilizzo di un CREDENTIAL con SHARED ACCESS SIGNATURE e TYPE = BLOB_STORAGE, vedere Creare un'origine dati esterna per eseguire operazioni bulk e recuperare dati da Archiviazione di Azure nel database SQL

Per creare credenziali con ambito database, vedere CREATE DATABASE SCOPED CREDENTIAL.

TIPO = * [ HADOOP | BLOB_STORAGE ] *

Specifica il tipo dell'origine dati esterna da configurare. Questo parametro non è sempre obbligatorio e deve essere specificato solo quando ci si connette a Cloudera CDH, Hortonworks HDP, un account Archiviazione di Azure o un Azure Data Lake Storage Gen2.

• Usare HADOOP quando l'origine dati esterna è Cloudera CDH, Hortonworks HDP, un account Archiviazione di Azure o un Azure Data Lake Storage Gen2.
• Usare BLOB_STORAGE durante l'esecuzione di operazioni bulk dall'account Archiviazione di Azure usando BULK INSERT o OPENROWSET BULK. Introdotto con SQL Server 2017 (14.x). Usare HADOOP quando si intende CREATE EXTERNAL TABLE con Archiviazione di Azure.

Per un esempio di utilizzo di TYPE = HADOOP per caricare dati da un account Archiviazione di Azure, vedere Creare l'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia wasb://

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:p ort]'

Configurare questo valore facoltativo quando ci si connette a Cloudera CDH, Hortonworks HDP o solo un account Archiviazione di Azure. Per un elenco completo delle versioni di Hadoop supportate, vedere Configurazione della connettività PolyBase.

Dopo aver definito RESOURCE_MANAGER_LOCATION, Query Optimizer prenderà una decisione basata sui costi per migliorare le prestazioni. È possibile usare un processo MapReduce per eseguire il pushdown del calcolo in Hadoop. Se si specifica RESOURCE_MANAGER_LOCATION, è possibile ridurre significativamente il volume di dati trasferiti tra Hadoop e SQL Server, con conseguente miglioramento delle prestazioni delle query.

Se la Resource Manager non è specificata, il push del calcolo in Hadoop è disabilitato per le query PolyBase. In Creare un'origine dati esterna per fare riferimento a Hadoop con il pushdown abilitato viene fornito un esempio concreto, oltre a ulteriori indicazioni.

Il RESOURCE_MANAGER_LOCATION valore non viene convalidato quando si crea l'origine dati esterna. L'immissione di un valore non corretto potrebbe causare un errore di query in fase di esecuzione ogni volta che si tenta il push-down perché il valore specificato non sarebbe in grado di risolvere.

Affinché PolyBase funzioni correttamente con un'origine dati esterna Hadoop, le porte per i componenti del cluster Hadoop seguenti devono essere aperte:

• Porte HDFS
  • Namenode
  • Datanode
• Resource Manager
  • Invio di processi
• Cronologia dei processi

Se la porta non è specificata, per la scelta del valore predefinito si usa l'impostazione corrente della configurazione 'hadoop connectivity'.

Nella tabella seguente vengono illustrate le porte predefinite per questi componenti. Esiste una dipendenza della versione di Hadoop e la possibilità di una configurazione personalizzata che non usa l'assegnazione di porta predefinita.

Autorizzazioni

Richiede CONTROL autorizzazione per il database in SQL Server.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Sicurezza

PolyBase supporta l'autenticazione basata su proxy per la maggior parte delle origini dati esterne. Creare credenziali con ambito database per creare l'account proxy.

Un token di firma di accesso condiviso con tipo HADOOP non è supportato. È supportato solo il tipo BLOB_STORAGE quando viene usata una chiave di accesso dell'account di archiviazione. Il tentativo di creare un'origine dati esterna di tipo HADOOP e le credenziali di firma di accesso condiviso potrebbe non riuscire e potrebbe essere visualizzato l'errore:

Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account aren't valid.: Error [Parameters provided to connect to the Azure storage account aren't valid.] occurred while accessing external file.'

Esempi

R. Creare un'origine dati esterna per fare riferimento a Hadoop

Per creare un'origine dati esterna per fare riferimento al cluster Hortonworks HDP o Cloudera CDH Hadoop, specificare il nome del computer o l'indirizzo IP di Namenode Hadoop e la porta.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8050'
);

B. Creare un'origine dati esterna per fare riferimento a Hadoop con il pushdown abilitato

Specificare l'opzione RESOURCE_MANAGER_LOCATION per abilitare il pushdown del calcolo in Hadoop per le query PolyBase. Dopo l'abilitazione, PolyBase prende una decisione basata sui costi per determinare se eseguire il push del calcolo delle query in Hadoop.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8020',
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Creare un'origine dati esterna per fare riferimento a Hadoop con protezione Kerberos

Per verificare se il cluster Hadoop è protetto tramite Kerberos, controllare il valore della proprietà hadoop.security.authentication nel file core-site.xml di Hadoop. Per fare riferimento a un cluster Hadoop protetto tramite Kerberos, è necessario specificare una credenziale con ambito database che contiene il nome utente e la password di Kerberos. La chiave master del database viene usata per crittografare il segreto della credenziale con ambito database.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>',
     SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Creare un'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia wasb://

In questo esempio l'origine dati esterna è un account di archiviazione Azure V2 denominato logs. Il contenitore di stoccaggio è chiamato daily. Il Archiviazione di Azure'origine dati esterna è solo per il trasferimento dei dati. e non supporta il pushdown dei predicati. Gli spazi dei nomi gerarchici non sono supportati durante l'accesso ai dati tramite l'interfaccia wasb:// . Quando ci si connette al Archiviazione di Azure tramite wasb o wasbs, l'autenticazione deve essere eseguita con una chiave dell'account di archiviazione, non con una firma di accesso condiviso.

Questo esempio illustra come creare le credenziali con ambito database per l'autenticazione in un account di archiviazione Azure V2. Specificare la chiave dell'account Archiviazione di Azure nel segreto delle credenziali del database. È possibile specificare qualsiasi stringa nell'identità delle credenziali con ambito database perché non viene usata durante l'autenticazione per Archiviazione di Azure.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
     SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential
);

Esempi: operazioni bulk

E. Creare un'origine dati esterna per operazioni bulk che recuperano dati da Archiviazione di Azure

Applica a: SQL Server 2017 (14.x) e versioni successive.

Utilizzare l'origine dati seguente per le operazioni bulk usando BULK INSERT o OPENROWSET BULK. Le credenziali devono impostare SHARED ACCESS SIGNATURE come identità, non devono includere il carattere ? iniziale nel token di firma di accesso condiviso, devono avere almeno un'autorizzazione di lettura per il file da caricare (ad esempio srt=o&sp=r). Inoltre il periodo di scadenza deve essere valido (tutte le date sono in formato UTC). Per altre informazioni sulle firme di accesso condiviso, vedere Uso delle firme di accesso condiviso.

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

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = AccessAzureInvoices
);

Per un esempio di utilizzo, vedere BULK INSERT.

Contenuti correlati

• ALTER EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso
• configurazione della connettività PolyBase (Transact-SQL)

Panoramica: SQL Server 2019

Applica a: SQL Server 2019 (15.x)

Crea un'origine dati esterna per le query PolyBase. Le origini dati esterne vengono usate per stabilire la connettività e supportano questi casi d'uso principali:

• Virtualizzazione dei dati e caricamento dei dati con PolyBase in SQL Server
• Operazioni di caricamento bulk con BULK INSERT o OPENROWSET

convenzioni di sintassi Transact-SQL

Sintassi per SQL Server 2019

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
    [ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' ]
  )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database in SQL Server.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

Percorso:

• <Namenode> = nome del computer, URI del servizio dei nomi o indirizzo IP di Namenode nel cluster Hadoop. PolyBase deve risolvere tutti i nomi DNS usati dal cluster Hadoop.
• port = porta su cui è in ascolto l'origine dati esterna. Per trovare la porta in Hadoop, si usa il parametro di configurazione fs.defaultFS. L'impostazione predefinita è 8020.
• <container> = contenitore dell'account di archiviazione che include i dati. I contenitori radice sono di sola lettura, di conseguenza i dati non possono essere riscritti nel contenitore.
• <storage_account> = nome dell'account di archiviazione della risorsa Azure.
• <server_name> = nome host.
• <instance_name> = nome dell'istanza denominata SQL Server. Usato se è in esecuzione SQL Server servizio Browser nell'istanza di destinazione.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• Il Motore di database di SQL Server non verifica l'esistenza dell'origine dati esterna al momento della creazione dell'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.
• Per garantire una semantica di esecuzione di query coerente, usare la stessa origine dati esterna per tutte le tabelle quando si eseguono query su Hadoop.
• È possibile usare il connettore sqlserver per connettere SQL Server 2019 (15.x) a un altro SQL Server o a database SQL di Azure.
• Per la connessione tramite Driver={<Name of Driver>} specificare ODBC.
• L'uso di wasbs o abfss è facoltativo, ma consigliato in SQL Server 2019 (15.x) per l'accesso a Account di archiviazione di Azure perché i dati verranno inviati usando una connessione TLS/SSL sicura.
• Le API abfs o abfss sono supportate quando si accede a Account di archiviazione di Azure a partire da SQL Server 2019 (15.x) CU11. Per altre informazioni, vedere the Azure Blob Filesystem Driver (ABFS).
• L'opzione Spazio dei nomi gerarchico per Account di archiviazione di Azure(V2) che usa abfs[s] è supportata tramite Azure Data Lake Storage Gen2 a partire da SQL Server 2019 (15.x) CU11+. Negli altri casi, l'opzione Spazio dei nomi gerarchico non è supportata e deve rimanere disabilitata.
• Per garantire la corretta esecuzione delle query di PolyBase durante un failover di Namenode di Hadoop, provare a usare un indirizzo IP virtuale per l'istanza di Namenode del cluster Hadoop. In caso contrario, eseguire ALTER EXTERNAL DATA SOURCE per puntare alla nuova posizione.
• I tipi sqlhdfs e sqldatapool sono supportati per la connessione tra l'istanza master e il pool di archiviazione di un cluster Big Data. Per Cloudera CDH o Hortonworks HDP, usare hdfs. Per altre informazioni sull'uso di sqlhdfs per l'esecuzione di query sui pool di archiviazione SQL Server cluster Big Data, vedere Query HDFS in SQL Server cluster Big Data 2019.
• SQL Server il supporto per le origini dati esterne HDFS Cloudera (CDP) e Hortonworks (HDP) è stato ritirato e non è incluso in SQL Server 2022 (16.x) e versioni successive. Per altre informazioni, vedere Opzioni datiBig nella piattaforma Microsoft SQL Server.

CONNECTION_OPTIONS = key_value_pair

Specificato per SQL Server 2019 (15.x) e versioni successive. Specifica le opzioni aggiuntive quando si esegue la connessione a un'origine dati esterna tramite ODBC. Per usare più opzioni di connessione, separarle con un punto e virgola.

Si applica alle connessioni generiche ODBC e ai connettori predefiniti ODBC per SQL Server, Oracle, Teradata, MongoDB e API Azure Cosmos DB per MongoDB.

key_value_pair rappresenta la parola chiave e il valore per un'opzione di connessione specifica. Le parole chiave e i valori disponibili dipendono dal tipo di origine dati esterna. Il nome del driver è un requisito minimo, ma sono disponibili altre opzioni, ad esempio APP='<your_application_name>' o ApplicationIntent= ReadOnly|ReadWrite, che può essere utile impostare e possono essere usate per la risoluzione dei problemi.

Le possibili coppie chiave-valore sono specifiche del provider per il fornitore dell'origine dati esterna. Per altre informazioni per ogni provider, vedere CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.

SQL Server 2019 (15.x) Aggiornamento cumulativo 19 e versioni successive introducono parole chiave aggiuntive per supportare i file Oracle TNS:

• La parola chiave TNSNamesFile specifica il percorso file del tnsnames.ora file che si trova nel server Oracle.
• La parola chiave ServerName specifica l'alias usato all'interno di tnsnames.ora che verrà usato per sostituire il nome host e la porta.

Pushdown = ON | SPENTO

Specificato solo per SQL Server 2019 (15,x). Indica se è possibile eseguire il pushdown del calcolo nell'origine dati esterna. È ATTIVATO per impostazione predefinita.

PUSHDOWN è supportato quando ci si connette a SQL Server, Oracle, Teradata, MongoDB, l'API Azure Cosmos DB per MongoDB o ODBC a livello di origine dati esterna.

L'abilitazione o la disabilitazione del push-down a livello di query viene ottenuta tramite l'hint EXTERNALPUSHDOWN.

CREDENZIALE = credential_name

Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna.

Note aggiuntive e indicazioni utili per la creazione delle credenziali:

• CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.
• Quando TYPE = BLOB_STORAGE, è necessario creare le credenziali usando SHARED ACCESS SIGNATURE come identità.
• TYPE = BLOB_STORAGE è consentito solo per le operazioni bulk; non è possibile creare tabelle esterne per un'origine dati esterna con TYPE = BLOB_STORAGE.

Esistono diversi modi per creare una firma di accesso condiviso:

• È possibile creare un token di firma di accesso condiviso passando al portale Azure -><Your_Storage_Account> -> Firma di accesso condiviso -> Configurare le autorizzazioni -> Generare firma di accesso condiviso e stringa di connessione. Per altre informazioni, vedere Generare una firma di accesso condiviso.

• È possibile creare e configurare una firma di accesso condiviso con Azure Storage Explorer.

• È possibile creare un token di firma di accesso condiviso a livello di codice tramite PowerShell, interfaccia della riga di comando di Azure, .NET e API REST. Per altre informazioni, vedere A accesso limitato alle risorse di Archiviazione di Azure usando firme di accesso condiviso (SAS).

• Il token di firma di accesso condiviso deve essere configurato come segue:

  • Quando viene generato un token di firma di accesso condiviso, include un punto interrogativo ('?') all'inizio del token. Escludere l'elemento iniziale ? quando configurato come SECRET.
  • Usare un periodo di scadenza valido (tutte le date sono espresse in formato UTC).

• Concedere almeno l'autorizzazione di lettura per il file che deve essere caricato ( ad esempio srt=o&sp=r). È possibile creare più firme di accesso condiviso per casi d'uso diversi. Le autorizzazioni devono essere concesse come segue:

  Azione	Autorizzazione
  Lettura di dati da un file	Lettura
  Leggere i dati da più file e sottocartelle	Lettura e Lista

Per un esempio di utilizzo di un CREDENTIAL con SHARED ACCESS SIGNATURE e TYPE = BLOB_STORAGE, vedere Creare un'origine dati esterna per eseguire operazioni bulk e recuperare dati da Archiviazione di Azure nel database SQL

Per creare credenziali con ambito database, vedere CREATE DATABASE SCOPED CREDENTIAL.

TIPO = * [ HADOOP | BLOB_STORAGE ] *

Specifica il tipo dell'origine dati esterna da configurare. Questo parametro non è sempre obbligatorio e deve essere specificato solo quando ci si connette a Cloudera CDH, Hortonworks HDP, un account Archiviazione di Azure o un Azure Data Lake Storage Gen2.

• In SQL Server 2019 (15.x) non specificare TYPE a meno che non ci si connetta a Cloudera CDH, Hortonworks HDP, un account Archiviazione di Azure.
• Usare HADOOP quando l'origine dati esterna è Cloudera CDH, Hortonworks HDP, un account Archiviazione di Azure o un Azure Data Lake Storage Gen2.
• Usare BLOB_STORAGE durante l'esecuzione di operazioni bulk dall'account Archiviazione di Azure usando BULK INSERT o OPENROWSET BULK con SQL Server 2017 (14.x). Usare HADOOP quando si intende creare UNA TABELLA ESTERNA su Archiviazione di Azure.
• SQL Server il supporto per le origini dati esterne HDFS Cloudera (CDP) e Hortonworks (HDP) è stato ritirato e non è incluso in SQL Server 2022 (16.x) e versioni successive. Per altre informazioni, vedere Opzioni datiBig nella piattaforma Microsoft SQL Server.

Per un esempio di utilizzo di TYPE = HADOOP per caricare dati da un account Archiviazione di Azure, vedere Creare un'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia wasb://.

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:p ort]'

In SQL Server 2019 (15.x), non specificare RESOURCE_MANAGER_LOCATION a meno che non ci si connetta a Cloudera CDH, Hortonworks HDP, un account Archiviazione di Azure.

Configurare questo valore facoltativo quando ci si connette a Cloudera CDH, Hortonworks HDP o solo un account Archiviazione di Azure. Per un elenco completo delle versioni di Hadoop supportate, vedere Configurazione della connettività PolyBase.

RESOURCE_MANAGER_LOCATION Quando viene definito, Query Optimizer prende una decisione basata sui costi per migliorare le prestazioni. È possibile usare un processo MapReduce per eseguire il pushdown del calcolo in Hadoop. Se si specifica RESOURCE_MANAGER_LOCATION, è possibile ridurre significativamente il volume di dati trasferiti tra Hadoop e SQL Server, con conseguente miglioramento delle prestazioni delle query.

Se la Resource Manager non è specificata, il push del calcolo in Hadoop è disabilitato per le query PolyBase. In Creare un'origine dati esterna per fare riferimento a Hadoop con il pushdown abilitato viene fornito un esempio concreto, oltre a ulteriori indicazioni.

Il valore RESOURCE_MANAGER_LOCATION non viene convalidato quando si crea l'origine dati esterna. L'immissione di un valore non corretto potrebbe causare un errore di query in fase di esecuzione ogni volta che si tenta il push-down perché il valore specificato non sarebbe in grado di risolvere.

Affinché PolyBase funzioni correttamente con un'origine dati esterna Hadoop, le porte per i componenti del cluster Hadoop seguenti devono essere aperte:

• Porte HDFS
  • Namenode
  • Datanode
• Resource Manager
  • Invio di processi
• Cronologia dei processi

Se la porta non è specificata, per la scelta del valore predefinito si usa l'impostazione corrente della configurazione 'hadoop connectivity'.

Nella tabella seguente vengono illustrate le porte predefinite per questi componenti. Esiste una dipendenza della versione di Hadoop e la possibilità di una configurazione personalizzata che non usa l'assegnazione di porta predefinita.

Autorizzazioni

Richiede CONTROL autorizzazione per il database in SQL Server.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Sicurezza

PolyBase supporta l'autenticazione basata su proxy per la maggior parte delle origini dati esterne. Creare credenziali con ambito database per creare l'account proxy.

Quando ci si connette all'archiviazione o al pool di dati in SQL Server cluster Big Data 2019, le credenziali dell'utente vengono passate al sistema back-end. Creare gli account di accesso direttamente nel pool di dati per abilitare l'autenticazione pass-through.

Un token di firma di accesso condiviso con tipo HADOOP non è supportato. È supportato solo il tipo BLOB_STORAGE quando viene usata una chiave di accesso dell'account di archiviazione. Il tentativo di creare un'origine dati esterna di tipo HADOOP e le credenziali di firma di accesso condiviso potrebbe non riuscire e potrebbe essere visualizzato l'errore:

Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account aren't valid.: Error [Parameters provided to connect to the Azure storage account aren't valid.] occurred while accessing external file.'

Esempi

R. Creare un'origine dati esterna in SQL Server 2019 per fare riferimento a Oracle

Per creare un'origine dati esterna che fa riferimento a Oracle, assicurarsi di disporre di credenziali con ambito database. Facoltativamente, è anche possibile abilitare o disabilitare il push-down del calcolo su questa origine dati.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
WITH IDENTITY = 'oracle_username',
     SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    PUSHDOWN = ON,
    CREDENTIAL = OracleProxyAccount
);

Facoltativamente, l'origine dati esterna a Oracle può usare l'autenticazione proxy per fornire un controllo di accesso con granularità fine. Un utente proxy può essere configurato in modo che abbia accesso limitato rispetto all'utente rappresentato.

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
WITH IDENTITY = 'oracle_username',
     SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]
);

In alternativa, è possibile usare l'autenticazione TNS.

A partire da SQL Server 2019 (15.x) aggiornamento cumulativo 19, CREATE EXTERNAL DATA SOURCE ora supporta l'uso di file TNS durante la connessione a Oracle.

Il CONNECTION_OPTIONS parametro è stato espanso e ora usa TNSNamesFile e ServerName come variabili per esplorare il tnsnames.ora file e stabilire la connessione con il server.

Nell'esempio seguente, durante il runtime SQL Server cercherà il percorso del file tnsnames.ora specificato da TNSNamesFile e cercherà l'host e la porta di rete specificata da ServerName.

CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
    LOCATION = N'oracle://XE',
    CREDENTIAL = [OracleCredentialTest],
    CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);

Per altri esempi relativi a origini dati diverse, come MongoDB, vedere Configurare PolyBase per l'accesso a dati esterni in MongoDB.

B. Creare un'origine dati esterna per fare riferimento a Hadoop

Per creare un'origine dati esterna per fare riferimento al cluster Hortonworks HDP o Cloudera CDH Hadoop, specificare il nome del computer o l'indirizzo IP di Namenode Hadoop e la porta.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8050'
);

C. Creare un'origine dati esterna per fare riferimento a Hadoop con il pushdown abilitato

Specificare l'opzione RESOURCE_MANAGER_LOCATION per abilitare il pushdown del calcolo in Hadoop per le query PolyBase. Dopo l'abilitazione, PolyBase prende una decisione basata sui costi per determinare se eseguire il push del calcolo delle query in Hadoop.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8020',
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Creare un'origine dati esterna per fare riferimento a Hadoop con protezione Kerberos

Per verificare se il cluster Hadoop è protetto tramite Kerberos, controllare il valore della proprietà hadoop.security.authentication nel file core-site.xml di Hadoop. Per fare riferimento a un cluster Hadoop protetto tramite Kerberos, è necessario specificare una credenziale con ambito database che contiene il nome utente e la password di Kerberos. La chiave master del database viene usata per crittografare il segreto della credenziale con ambito database.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>',
     SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

E. Creare un'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia wasb://

In questo esempio l'origine dati esterna è un account di archiviazione Azure V2 denominato logs. Il contenitore di stoccaggio è chiamato daily. Il Archiviazione di Azure'origine dati esterna è solo per il trasferimento dei dati. e non supporta il pushdown dei predicati. Gli spazi dei nomi gerarchici non sono supportati durante l'accesso ai dati tramite l'interfaccia wasb:// . Quando ci si connette al Archiviazione di Azure tramite wasb o wasbs, l'autenticazione deve essere eseguita con una chiave dell'account di archiviazione, non con una firma di accesso condiviso.

Questo esempio illustra come creare le credenziali con ambito database per l'autenticazione in un account di archiviazione Azure V2. Specificare la chiave dell'account Archiviazione di Azure nel segreto delle credenziali del database. È possibile specificare qualsiasi stringa nell'identità delle credenziali con ambito database perché non viene usata durante l'autenticazione per Archiviazione di Azure.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
     SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential
);

F. Creare un'origine dati esterna per fare riferimento a un'istanza denominata di SQL Server tramite la connettività PolyBase

Applica a: SQL Server 2019 (15.x) e versioni successive

Per creare un'origine dati esterna che fa riferimento a un'istanza denominata di SQL Server, usare CONNECTION_OPTIONS per specificare il nome dell'istanza.

Nell'esempio seguente è WINSQL2019 il nome host e SQL2019 è il nome dell'istanza. 'Server=%s\SQL2019' è la coppia chiave-valore.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019',
    CONNECTION_OPTIONS = 'Server=%s\SQL2019',
    CREDENTIAL = SQLServerCredentials
);

In alternativa, è possibile usare una porta per connettersi a un'istanza predefinita di SQL Server.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019:58137',
    CREDENTIAL = SQLServerCredentials
);

G. Creare un'origine dati esterna per fare riferimento a una replica secondaria leggibile del gruppo di disponibilità Always On

Applica a: SQL Server 2019 (15.x) e versioni successive

Per creare un'origine dati esterna che fa riferimento a una replica secondaria leggibile di SQL Server, usare CONNECTION_OPTIONS per specificare l'ApplicationIntent=ReadOnly. È inoltre necessario impostare il database di disponibilità come Database={dbname} in CONNECTION_OPTIONSoppure impostare il database di disponibilità come database predefinito dell'account di accesso usato per le credenziali con ambito database. È necessario eseguire questa operazione in tutte le repliche di disponibilità del gruppo di disponibilità.

Prima di tutto, creare le credenziali con ambito database, archiviandole per un account di accesso con autenticazione SQL. Il connettore ODBC SQL per PolyBase supporta solo l'autenticazione di base. Prima di creare credenziali con ambito database, il database utente deve avere una chiave master per proteggerle. Per altre informazioni, vedere CREATE MASTER KEY. L'esempio seguente crea credenziali con ambito database. È necessario specificare account di accesso e password.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
     SECRET = 'password';

Creare quindi la nuova origine dati esterna.

Indipendentemente dal fatto che sia stato incluso Database=dbname nel CONNECTION_OPTIONS database o impostato come database predefinito per l'account di accesso nella credenziale con ambito database, è comunque necessario specificare il nome del database tramite un nome in tre parti nell'istruzione CREATE EXTERNAL TABLE, all'interno del parametro LOCATION. Per un esempio, vedere CREATE EXTERNAL TABLE.

Nell'esempio seguente, WINSQL2019AGL è il nome del listener del gruppo di disponibilità e dbname è il nome del database come destinazione dell'istruzione CREATE EXTERNAL TABLE.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = SQLServerCredentials
);

È possibile illustrare il comportamento di reindirizzamento del gruppo di disponibilità specificando ApplicationIntent e creando una tabella esterna nella vista di sistema sys.servers. Nello script di esempio seguente vengono create due origini dati esterne e una tabella esterna per ogni origine. Usare le viste per verificare quale server risponde alla connessione. È anche possibile ottenere risultati simili tramite la funzionalità di routing di sola lettura. Per altre informazioni, vedere Configurare il routing di sola lettura per un gruppo di disponibilità Always On.

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
    CREDENTIAL = [SQLServerCredentials]
);
GO

All'interno del database nel gruppo di disponibilità creare una vista per restituire sys.servers e il nome dell'istanza locale, che consente di identificare la replica che risponde alla query. Per altre informazioni, vedere sys.servers.

CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO

Creare quindi una tabella esterna nell'istanza di origine:

CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance

SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO

Esempi: operazioni bulk

H. Creare un'origine dati esterna per operazioni bulk che recuperano dati da Archiviazione di Azure

Applica a: SQL Server 2017 (14.x) e SQL Server 2019 (15.x)

Utilizzare l'origine dati seguente per le operazioni bulk usando BULK INSERT o OPENROWSET BULK. Le credenziali devono impostare SHARED ACCESS SIGNATURE come identità, non devono includere il carattere ? iniziale nel token di firma di accesso condiviso, devono avere almeno un'autorizzazione di lettura per il file da caricare (ad esempio srt=o&sp=r). Inoltre il periodo di scadenza deve essere valido (tutte le date sono in formato UTC). Per altre informazioni sulle firme di accesso condiviso, vedere Uso delle firme di accesso condiviso.

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

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = AccessAzureInvoices
);

Per un esempio di utilizzo, vedere BULK INSERT.

Io. Creare un'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia abfs://

Applica a: SQL Server 2019 (15.x) CU11 e versioni successive

In questo esempio l'origine dati esterna è un account Azure Data Lake Storage Gen2 logs, usando il driver Azure file system BLOB (ABFS). Il contenitore di stoccaggio è chiamato daily. Il Azure Data Lake Storage Gen2'origine dati esterna è solo per il trasferimento dei dati, perché il push del predicato non è supportato.

In questo esempio viene illustrato come creare le credenziali con ambito database per l'autenticazione in un account Azure Data Lake Storage Gen2. Specificare la chiave dell'account Archiviazione di Azure nel segreto delle credenziali del database. È possibile specificare qualsiasi stringa nell'identità delle credenziali con ambito database perché non viene usata durante l'autenticazione per Archiviazione di Azure.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
     SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://daily@logs.dfs.core.windows.net/',
    CREDENTIAL = AzureStorageCredential
);

J. Creare un'origine dati esterna usando ODBC generico in PostgreSQL

Come negli esempi precedenti, creare prima una chiave master del database e credenziali con ambito database. Le credenziali con ambito database verranno usate per l'origine dati esterna. In questo esempio si presuppone anche che nel server sia installato un provider di dati ODBC generico per PostgreSQL.

In questo esempio, il provider di dati ODBC generico viene usato per connettersi a un server di database PostgreSQL nella stessa rete, dove il nome di dominio completo del server PostgreSQL è POSTGRES1, usando la porta predefinita tcp 5432.

CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
    LOCATION = 'odbc://POSTGRES1.domain:5432',
    CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
    CREDENTIAL = postgres_credential
);

Contenuti correlati

• ALTER EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso
• configurazione della connettività PolyBase (Transact-SQL)

Panoramica: SQL Server 2022

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

Crea un'origine dati esterna per le query PolyBase. Le origini dati esterne vengono usate per stabilire la connettività e supportano questi casi d'uso principali:

• Virtualizzazione dei dati e caricamento dei dati con PolyBase in SQL Server
• Operazioni di caricamento bulk con BULK INSERT o OPENROWSET

Sintassi per SQL Server 2022

Sintassi per SQL Server 2022 e versioni successive

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
  )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database in SQL Server.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

* Deve essere una credenziale con ambito database, in cui identity è hardcoded per IDENTITY = 'S3 Access Key' e l'argomento SECRET è nel formato = '<AccessKeyID>:<SecretKeyID>' o usare l'autorizzazione pass-through (STS). Per altre informazioni, vedere Configurare PolyBase per accedere ai dati esterni nell'archiviazione oggetti compatibile con S3.

Percorso:

• port = porta su cui è in ascolto l'origine dati esterna. Facoltativo in molti casi, a seconda della configurazione di rete.
• <container_name> = contenitore dell'account di archiviazione che include i dati. I contenitori radice sono di sola lettura, di conseguenza i dati non possono essere riscritti nel contenitore.
• <storage_account> = nome dell'account di archiviazione della risorsa Azure.
• <server_name> = nome host.
• <instance_name> = nome dell'istanza denominata SQL Server. Usato se è in esecuzione SQL Server servizio Browser nell'istanza di destinazione.
• <ip_address>:<port> = Solo per l'archiviazione oggetti compatibile con S3 (a partire da SQL Server 2022 (16.x)), l'endpoint e la porta usati per connettersi alla risorsa di archiviazione compatibile con S3.
• <bucket_name> = Solo per l'archiviazione di oggetti compatibile con S3 (a partire da SQL Server 2022 (16.x)), specifica della piattaforma di archiviazione.
• <region> = Solo per l'archiviazione di oggetti compatibile con S3 (a partire da SQL Server 2022 (16.x)), specifica della piattaforma di archiviazione.
• <folder> = Parte del percorso di archiviazione all'interno dell'URL di archiviazione.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• Il Motore di database di SQL Server non verifica l'esistenza dell'origine dati esterna al momento della creazione dell'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.
• È possibile usare il connettore sqlserver per connettere SQL Server 2019 (15.x) a un altro SQL Server o a database SQL di Azure.
• Per la connessione tramite Driver={<Name of Driver>} specificare ODBC.
• L'opzione Spazio dei nomi gerarchico per Account di archiviazione di Azure(V2) che usa il prefisso adls è supportata tramite Azure Data Lake Storage Gen2 in SQL Server 2022 (16.x).

• SQL Server supporto per le origini dati esterne HDFS Cloudera (CDP) e Hortonworks (HDP) vengono ritirati e non inclusi in SQL Server 2022 (16.x). Non è necessario usare l'argomento TYPE in SQL Server 2022 (16.x).
• Per altre informazioni sull'archiviazione di oggetti compatibile con S3 e PolyBase a partire da SQL Server 2022 (16.x), vedere Configurare PolyBase per accedere ai dati esterni nell'archiviazione oggetti compatibile con S3. Per un esempio di esecuzione di query su un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3, vedere Virtualizzare un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3 con PolyBase.
• A differenza delle versioni precedenti, in SQL Server 2022 (16.x), il prefisso usato per Archiviazione di Azure Account (v2) è cambiato da wasb[s] a abs.
• Diversa dalle versioni precedenti, in SQL Server 2022 (16.x), il prefisso usato per Azure Data Lake Storage Gen2 è cambiato da abfs[s] a adls.
• Per un esempio di uso di PolyBase per virtualizzare un file CSV in Archiviazione di Azure, vedere Virtualize CSV file with PolyBase.
• Per un esempio che usa PolyBase per virtualizzare una tabella delta in ADLS Gen2, vedere Virtualizzare una tabella delta con PolyBase.
• SQL Server 2022 (16.x) supporta completamente due formati DI URL per Archiviazione di Azure Account v2 (abs) e Azure Data Lake Gen2 (adls).
  • Il percorso LOCATION può usare i formati: <container>@<storage_account_name>.. (scelta consigliata) o <storage_account_name>../<container>. Ad esempio:
    • Archiviazione di Azure Account v2: abs://<container>@<storage_account_name>.blob.core.windows.net (scelta consigliata) o abs://<storage_account_name>.blob.core.windows.net/<container>.
• Azure Data Lake Gen2 supporta: adls://<container>@<storage_account_name>.blob.core.windows.net (scelta consigliata) o adls://<storage_account_name>.dfs.core.windows.net/<container>.

CONNECTION_OPTIONS = key_value_pair

Specificato per SQL Server 2019 (15.x) e versioni successive. Specifica le opzioni aggiuntive quando si esegue la connessione a un'origine dati esterna tramite ODBC. Per usare più opzioni di connessione, separarle con un punto e virgola.

Si applica alle connessioni generiche ODBC e ai connettori predefiniti ODBC per SQL Server, Oracle, Teradata, MongoDB e API Azure Cosmos DB per MongoDB.

key_value_pair rappresenta la parola chiave e il valore per un'opzione di connessione specifica. Le parole chiave e i valori disponibili dipendono dal tipo di origine dati esterna. Il nome del driver è un requisito minimo, ma sono disponibili altre opzioni, ad esempio APP='<your_application_name>' o ApplicationIntent= ReadOnly|ReadWrite, che può essere utile impostare e possono essere usate per la risoluzione dei problemi.

Le possibili coppie chiave-valore sono specifiche del driver. Per altre informazioni per ogni provider, vedere CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.

A partire da SQL Server 2022 (16.x) Aggiornamento cumulativo 2, sono state introdotte parole chiave aggiuntive per supportare i file Oracle TNS:

• La parola chiave TNSNamesFile specifica il percorso file del tnsnames.ora file che si trova nel server Oracle.
• La parola chiave ServerName specifica l'alias usato all'interno di tnsnames.ora che verrà usato per sostituire il nome host e la porta.

PUSHDOWN = ON | SPENTO

Si applica a: SQL Server 2019 (15.x) e versioni successive. Indica se è possibile eseguire il pushdown del calcolo nell'origine dati esterna. È attivata per impostazione predefinita.

PUSHDOWN è supportato quando ci si connette a SQL Server, Oracle, Teradata, MongoDB, l'API Azure Cosmos DB per MongoDB o ODBC a livello di origine dati esterna.

L'abilitazione o la disabilitazione del push-down a livello di query viene ottenuta tramite l'hint EXTERNALPUSHDOWN.

CREDENZIALE = credential_name

Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna.

Note aggiuntive e indicazioni utili per la creazione delle credenziali:

• CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.
• Quando si accede a Archiviazione di Azure Account (V2) o Azure Data Lake Storage Gen2, il IDENTITY deve essere SHARED ACCESS SIGNATURE.
• Per un esempio, vedere Creare un'origine dati esterna per eseguire operazioni bulk e recuperare dati da Archiviazione di Azure al database SQL.

Esistono diversi modi per creare una firma di accesso condiviso:

• È possibile creare un token di firma di accesso condiviso passando al portale Azure -><Your_Storage_Account> -> Firma di accesso condiviso -> Configurare le autorizzazioni -> Generare firma di accesso condiviso e stringa di connessione. Per altre informazioni, vedere Generare una firma di accesso condiviso.

• È possibile creare e configurare una firma di accesso condiviso con Azure Storage Explorer.

• È possibile creare un token di firma di accesso condiviso a livello di codice tramite PowerShell, interfaccia della riga di comando di Azure, .NET e API REST. Per altre informazioni, vedere A accesso limitato alle risorse di Archiviazione di Azure usando firme di accesso condiviso (SAS).

• Il token di firma di accesso condiviso deve essere configurato come segue:

  • Quando viene generato un token di firma di accesso condiviso, include un punto interrogativo ('?') all'inizio del token. Escludere l'elemento iniziale ? quando configurato come SECRET.
  • Usare un periodo di scadenza valido (tutte le date sono espresse in formato UTC).

• Concedere almeno l'autorizzazione di lettura per il file che deve essere caricato ( ad esempio srt=o&sp=r). È possibile creare più firme di accesso condiviso per casi d'uso diversi. Le autorizzazioni devono essere concesse come segue:

  Azione	Autorizzazione
  Lettura di dati da un file	Lettura
  Leggere i dati da più file e sottocartelle	Lettura e Lista
  Usare Create External Table as Select (CETAS)	Lettura, creazione, elenco e scrittura

• Per Archiviazione BLOB di Azure e Azure Data Lake Gen 2:

  • Servizi consentiti: Blob deve essere selezionato per generare il token di firma di accesso condiviso

• Tipi di risorse consentiti: Container e Object devono essere selezionati per generare il token di firma di accesso condiviso

Per un esempio d'uso di un oggetto CREDENTIAL con una risorsa di archiviazione di oggetti compatibile con S3 e PolyBase, vedere Configurare PolyBase per l'accesso ai dati esterni in una risorsa di archiviazione di oggetti compatibile con S3.

Per creare credenziali con ambito database, vedere CREATE DATABASE SCOPED CREDENTIAL.

Autorizzazioni

Richiede CONTROL autorizzazione per il database in SQL Server.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Sicurezza

PolyBase supporta l'autenticazione basata su proxy per la maggior parte delle origini dati esterne. Creare credenziali con ambito database per creare l'account proxy.

Eseguire l'aggiornamento a SQL Server 2022

A partire da SQL Server 2022 (16.x), le origini dati esterne hadoop non sono più supportate. È necessario ricreare manualmente le origini dati esterne create in precedenza con TYPE = HADOOPe qualsiasi tabella esterna che usa questa origine dati esterna.

Gli utenti dovranno anche configurare le origini dati esterne per l'uso di nuovi connettori durante la connessione a Archiviazione di Azure.

Esempi

R. Creare un'origine dati esterna in SQL Server per fare riferimento a Oracle

Per creare un'origine dati esterna che fa riferimento a Oracle, assicurarsi di disporre di credenziali con ambito database. Facoltativamente, è anche possibile abilitare o disabilitare il push-down del calcolo su questa origine dati.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
WITH IDENTITY = 'oracle_username',
     SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    PUSHDOWN = ON,
    CREDENTIAL = OracleProxyAccount
);

Facoltativamente, l'origine dati esterna a Oracle può usare l'autenticazione proxy per fornire un controllo di accesso con granularità fine. Un utente proxy può essere configurato in modo che abbia accesso limitato rispetto all'utente rappresentato.

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
WITH IDENTITY = 'oracle_username',
     SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]
);

In alternativa, è possibile eseguire l'autenticazione tramite TNS.

A partire da SQL Server 2022 (16.x) aggiornamento cumulativo 2, CREATE EXTERNAL DATA SOURCE supporta ora l'uso di file TNS durante la connessione a Oracle.

Il CONNECTION_OPTIONS parametro è stato espanso e ora usa TNSNamesFile e ServerName come variabili per esplorare il tnsnames.ora file e stabilire la connessione con il server.

Nell'esempio seguente, durante il runtime SQL Server cercherà il percorso del file tnsnames.ora specificato da TNSNamesFile e cercherà l'host e la porta di rete specificata da ServerName.

CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
    LOCATION = N'oracle://XE',
    CREDENTIAL = [OracleCredentialTest],
    CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);

B. Creare un'origine dati esterna per fare riferimento a un'istanza denominata di SQL Server tramite la connettività PolyBase

Applica a: SQL Server 2019 (15.x) e versioni successive

Per creare un'origine dati esterna che fa riferimento a un'istanza denominata di SQL Server, usare CONNECTION_OPTIONS per specificare il nome dell'istanza.

Prima di tutto, creare le credenziali con ambito database, archiviandole per un account di accesso con autenticazione SQL. Il connettore ODBC SQL per PolyBase supporta solo l'autenticazione di base. Prima di creare credenziali con ambito database, il database utente deve avere una chiave master per proteggerle. Per altre informazioni, vedere CREATE MASTER KEY. L'esempio seguente crea credenziali con ambito database. È necessario specificare account di accesso e password.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
     SECRET = 'password';

Nell'esempio seguente è WINSQL2019 il nome host e SQL2019 è il nome dell'istanza. 'Server=%s\SQL2019' è la coppia chiave-valore.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019',
    CONNECTION_OPTIONS = 'Server=%s\SQL2019',
    CREDENTIAL = SQLServerCredentials
);

In alternativa, è possibile usare una porta per connettersi a un'istanza predefinita di SQL Server.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019:58137',
    CREDENTIAL = SQLServerCredentials
);

C. Creare un'origine dati esterna per fare riferimento a una replica secondaria leggibile del gruppo di disponibilità Always On

Applica a: SQL Server 2019 (15.x) e versioni successive

Per creare un'origine dati esterna che fa riferimento a una replica secondaria leggibile di SQL Server, usare CONNECTION_OPTIONS per specificare l'ApplicationIntent=ReadOnly. È inoltre necessario impostare il database di disponibilità come Database={dbname} in CONNECTION_OPTIONSoppure impostare il database di disponibilità come database predefinito dell'account di accesso usato per le credenziali con ambito database. È necessario eseguire questa operazione in tutte le repliche di disponibilità del gruppo di disponibilità.

Prima di tutto, creare le credenziali con ambito database, archiviandole per un account di accesso con autenticazione SQL. Il connettore ODBC SQL per PolyBase supporta solo l'autenticazione di base. Prima di creare credenziali con ambito database, il database utente deve avere una chiave master per proteggerle. Per altre informazioni, vedere CREATE MASTER KEY. L'esempio seguente crea credenziali con ambito database. È necessario specificare account di accesso e password.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
     SECRET = 'password';

Creare quindi la nuova origine dati esterna.

Indipendentemente dal fatto che sia stato incluso Database=dbname nel CONNECTION_OPTIONS database o impostato come database predefinito per l'account di accesso nella credenziale con ambito database, è comunque necessario specificare il nome del database tramite un nome in tre parti nell'istruzione CREATE EXTERNAL TABLE, all'interno del parametro LOCATION. Per un esempio, vedere CREATE EXTERNAL TABLE.

Nell'esempio seguente, WINSQL2019AGL è il nome del listener del gruppo di disponibilità e dbname è il nome del database come destinazione dell'istruzione CREATE EXTERNAL TABLE.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = SQLServerCredentials
);

È possibile illustrare il comportamento di reindirizzamento del gruppo di disponibilità specificando ApplicationIntent e creando una tabella esterna nella vista di sistema sys.servers. Nello script di esempio seguente vengono create due origini dati esterne e una tabella esterna per ogni origine. Usare le viste per verificare quale server risponde alla connessione. È anche possibile ottenere risultati simili tramite la funzionalità di routing di sola lettura. Per altre informazioni, vedere Configurare il routing di sola lettura per un gruppo di disponibilità Always On.

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
    CREDENTIAL = [SQLServerCredentials]
);
GO

All'interno del database nel gruppo di disponibilità creare una vista per restituire sys.servers e il nome dell'istanza locale, che consente di identificare la replica che risponde alla query. Per altre informazioni, vedere sys.servers.

CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO

Creare quindi una tabella esterna nell'istanza di origine:

CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance

SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO

D. Creare un'origine dati esterna per eseguire query su un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3 tramite PolyBase

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

Lo script di esempio seguente crea un'origine dati esterna s3_ds nel database utente di origine in SQL Server. L'origine dati esterna fa riferimento alle credenziali con ambito database di s3_dc.

CREATE DATABASE SCOPED CREDENTIAL s3_dc
WITH IDENTITY = 'S3 Access Key', -- for S3-compatible object storage the identity must always be S3 Access Key
    SECRET = '<access_key_id>:<secret_key_id>' -- provided by the S3-compatible object storage
GO

CREATE EXTERNAL DATA SOURCE s3_ds
WITH (
    LOCATION = 's3://<ip_address>:<port>/',
    CREDENTIAL = s3_dc
);
GO

Verificare la nuova origine dati esterna con sys.external_data_sources.

SELECT *
FROM sys.external_data_sources;

L'esempio seguente illustra quindi l'uso di T-SQL per eseguire query su un file Parquet archiviato nell'archiviazione oggetti compatibile con S3 tramite la query OPENROWSET. Per altre informazioni, vedere Virtualizzare un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3 con PolyBase.

SELECT *
FROM OPENROWSET (
    BULK '/<bucket>/<parquet_folder>',
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_ds'
) AS [cc];

E. Creare un'origine dati esterna usando ODBC generico in PostgreSQL

Come negli esempi precedenti, creare prima una chiave master del database e credenziali con ambito database. Le credenziali con ambito database verranno usate per l'origine dati esterna. In questo esempio si presuppone anche che nel server sia installato un provider di dati ODBC generico per PostgreSQL.

In questo esempio, il provider di dati ODBC generico viene usato per connettersi a un server di database PostgreSQL nella stessa rete, dove il nome di dominio completo del server PostgreSQL è POSTGRES1, usando la porta predefinita tcp 5432.

CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
    LOCATION = 'odbc://POSTGRES1.domain:5432',
    CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
    CREDENTIAL = postgres_credential
);

Archiviazione di Azure

Creare una firma di accesso condiviso

Sia per Archiviazione BLOB di Azure che per Azure Data Lake Storage (ADLS) Gen2, il metodo di autenticazione supportato è la firma di accesso condiviso (SAS). Un modo semplice per generare un token di firma di accesso condiviso seguire la procedura seguente. Per altre informazioni, vedere CREDENTIAL.

1. Passare al portale di Azure e all'account di archiviazione desiderato.

2. Passare al contenitore desiderato nel menu Archiviazione dati.

3. Selezionare Token di accesso condiviso.

4. Scegliere l'autorizzazione appropriata in base all'azione desiderata:

   Azione	Autorizzazione
   Lettura di dati da un file	Lettura
   Leggere i dati da più file e sottocartelle	Lettura e Lista
   Usare Create External Table as Select (CETAS)	Lettura, creazione e scrittura

5. Scegliere la data di scadenza del token.

6. Generare il token di firma di accesso condiviso e l'URL.

7. Copiare il token di firma di accesso condiviso.

F. Creare un'origine dati esterna per accedere ai dati in Archiviazione BLOB di Azure usando l'interfaccia abs://

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

A partire da SQL Server 2022 (16.x), usare un nuovo prefisso abs per Archiviazione di Azure Account v2. Il abs prefisso supporta l'autenticazione tramite SHARED ACCESS SIGNATURE. Il abs prefisso sostituisce wasb, usato nelle versioni precedenti. HADOOP non è più supportato, non è più necessario usare TYPE = BLOB_STORAGE.

La chiave dell'account di archiviazione Azure non è più necessaria, ma usando il token di firma di accesso condiviso, come illustrato nell'esempio seguente:

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredentialv2
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', -- to use SAS the identity must be fixed as-is
    SECRET = '<Blob_SAS_Token>';
GO

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredentialv2
);

Per un esempio più dettagliato su come accedere ai file CSV archiviati in Archiviazione BLOB di Azure, vedere Virtualize CSV file with PolyBase.

G. Creare un'origine dati esterna per accedere ai dati in Azure Data Lake Gen2

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

A partire da SQL Server 2022 (16.x), usare un nuovo prefisso adls per Azure Data Lake Gen2, sostituendo abfs usato nelle versioni precedenti. Il prefisso supporta anche il adls token di firma di accesso condiviso come metodo di autenticazione, come illustrato in questo esempio:

--Create a database scoped credential using SAS Token
CREATE DATABASE SCOPED CREDENTIAL datalakegen2
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '<DataLakeGen2_SAS_Token>';
GO

CREATE EXTERNAL DATA SOURCE data_lake_gen2_dfs
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = datalakegen2
);

Per un esempio più dettagliato su come accedere ai file delta archiviati in Azure Data Lake Gen2, vedere Virtualize delta table with PolyBase.

Esempi: operazioni bulk

H. Creare un'origine dati esterna per operazioni bulk che recuperano dati da Archiviazione di Azure

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

Utilizzare l'origine dati seguente per le operazioni bulk usando BULK INSERT o OPENROWSET BULK. Le credenziali devono impostare SHARED ACCESS SIGNATURE come identità, non devono includere il carattere ? iniziale nel token di firma di accesso condiviso, devono avere almeno un'autorizzazione di lettura per il file da caricare (ad esempio srt=o&sp=r). Inoltre il periodo di scadenza deve essere valido (tutte le date sono in formato UTC). Per altre informazioni sulle firme di accesso condiviso, vedere Uso delle firme di accesso condiviso.

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

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AccessAzureInvoices,
);

Contenuti correlati

• ALTER EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso
• configurazione della connettività PolyBase (Transact-SQL)

Panoramica: SQL Server 2025

Applica a: SQL Server 2025 (17.x)

Crea un'origine dati esterna per le query PolyBase. Le origini dati esterne vengono usate per stabilire la connettività e supportano questi casi d'uso principali:

• Virtualizzazione dei dati e caricamento dei dati con PolyBase in SQL Server
• Operazioni di caricamento bulk con BULK INSERT o OPENROWSET

Supporta le connessioni di identità gestite per le istanze abilitate da Azure Arc. Per informazioni dettagliate, vedere Connetti a Archiviazione di Azure con identità gestita da PolyBase.

Sintassi per SQL Server 2025 e versioni successive

Per altre informazioni sulle convenzioni di sintassi, vedere convenzioni di sintassi Transact-SQL.

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
  )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database in SQL Server.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

¹ richiede SQL Server 2025 (17.x) istanza abilitata da Azure Arc. Per informazioni dettagliate, vedere Connetti a Archiviazione di Azure con identità gestita da PolyBase.

² Deve essere una credenziale con ambito database, in cui IDENTITY è hardcoded a IDENTITY = 'S3 Access Key' e l'argomento è nel formato SECRET o usare l'autorizzazione = '<AccessKeyID>:<SecretKeyID>' pass-through (STS). Per altre informazioni, vedere Configurare PolyBase per accedere ai dati esterni nell'archiviazione oggetti compatibile con S3.

Percorso:

¹ SQL Server 2022 (16.x) e versioni successive.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• Il Motore di database di SQL Server non verifica l'esistenza dell'origine dati esterna al momento della creazione dell'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.

• È possibile usare il connettore sqlserver per connettere SQL Server 2019 (15.x) a un altro SQL Server o a database SQL di Azure.

• Per la connessione tramite Driver={<Name of Driver>} specificare ODBC.

• L'opzione Spazio dei nomi gerarchico per Account di archiviazione di Azure(V2) che usa il prefisso adls è supportata tramite Azure Data Lake Storage Gen2 in SQL Server 2022 (16.x) e versioni successive.

• SQL Server supporto per le origini dati esterne HDFS Cloudera (CDP) e Hortonworks (HDP) vengono ritirati e non inclusi in SQL Server 2022 (16.x) e versioni successive. Non è necessario usare l'argomento TYPE in SQL Server 2025 (17.x).

• Per altre informazioni sull'archiviazione di oggetti compatibile con S3 e PolyBase in SQL Server 2022 (16.x) e versioni successive, vedere Configurare PolyBase per accedere ai dati esterni nell'archiviazione oggetti compatibile con S3. Per un esempio di esecuzione di query su un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3, vedere Virtualizzare un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3 con PolyBase.

In SQL Server 2022 (16.x) e versioni successive:

• il prefisso usato per Archiviazione di Azure Account (v2) è stato modificato da wasb[s] a abs

• prefisso usato per Azure Data Lake Storage Gen2 modificato da abfs[s] a adls

• Per un esempio di uso di PolyBase per virtualizzare un file CSV in Archiviazione di Azure, vedere Virtualize CSV file with PolyBase.

• Per un esempio che usa PolyBase per virtualizzare una tabella delta in ADLS Gen2, vedere Virtualizzare una tabella delta con PolyBase.

• SQL Server 2022 (16.x) e versioni successive supportano completamente due formati URL sia per Archiviazione di Azure Account v2 (abs) che per Azure Data Lake Gen2 (adls).

  • Il LOCATION percorso può usare i formati: <container>@<storage_account_name>.. (scelta consigliata) o <storage_account_name>../<container>. Per esempio:

    • Archiviazione di Azure Account v2: abs://<container>@<storage_account_name>.blob.core.windows.net (scelta consigliata) o abs://<storage_account_name>.blob.core.windows.net/<container>.

• Azure Data Lake Gen2 supporta: adls://<container>@<storage_account_name>.blob.core.windows.net (scelta consigliata) o adls://<storage_account_name>.dfs.core.windows.net/<container>.

CONNECTION_OPTIONS = key_value_pair

Applica a: SQL Server 2019 (15.x) e versioni successive.

Specifica le opzioni aggiuntive quando si esegue la connessione a un'origine dati esterna tramite ODBC. Per usare più opzioni di connessione, separarle con un punto e virgola.

Si applica alle connessioni generiche ODBC e ai connettori predefiniti ODBC per SQL Server, Oracle, Teradata, MongoDB e API Azure Cosmos DB per MongoDB.

key_value_pair rappresenta la parola chiave e il valore per un'opzione di connessione specifica. Le parole chiave e i valori disponibili dipendono dal tipo di origine dati esterna. Il nome del driver è un requisito minimo, ma sono disponibili altre opzioni, ad esempio APP='<your_application_name>' o ApplicationIntent= ReadOnly|ReadWrite, che può essere utile impostare e possono essere usate per la risoluzione dei problemi.

Le possibili coppie chiave-valore sono specifiche del driver. Per altre informazioni per ogni provider, vedere CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.

A partire da SQL Server 2022 (16.x) Aggiornamento cumulativo 2, sono state introdotte parole chiave aggiuntive per supportare i file Oracle TNS:

• La parola chiave TNSNamesFile specifica il percorso file del tnsnames.ora file che si trova nel server Oracle.
• La parola chiave ServerName specifica l'alias usato all'interno di tnsnames.ora che verrà usato per sostituire il nome host e la porta.

Opzioni di crittografia in SQL Server 2025 (17.x)

A partire da SQL Server 2025 (17.x), quando si usa sqlserver come origine dati, il driver ODBC Microsoft versione 18 per SQL Server è il driver predefinito. L'opzione Encryption è obbligatoria (Yes, Noo Strict) ed TrustServerCertificate è disponibile (Yes o No). Se Encryption non viene specificato, il comportamento predefinito è Encrypt=Yes;TrustServerCertificate=No;e richiede un certificato server.

Per connettersi usando il protocollo TDS 8.0, è stata aggiunta la modalità strict (Encrypt=Strict). In questa modalità è necessario installare un certificato server attendibile e viene sempre verificato (TrustServerCertificate viene ignorato). Una nuova parola chiave, HostnameInCertificate, può essere usata per specificare il nome host previsto trovato nel certificato se è diverso dal server specificato. HostnameInCertificate è utilizzabile in tutte le modalità di crittografia ed è applicabile anche se l'opzione Forza crittografia lato server è abilitata, in modo che il driver verificherà il certificato in modalità Facoltativo o Obbligatorio , a meno che non sia disabilitato tramite TrustServerCertificate.

Per altre informazioni sulle opzioni Encryption, i certificati del server e TrustServerCertificate, vedere Features of the Microsoft ODBC Driver for SQL Server on Windows.

È consigliabile usare sempre il driver più recente. Tuttavia, SQL Server 2025 (17.x) supporta anche Microsoft DRIVER ODBC versione 17 per SQL Server per la compatibilità con le versioni precedenti. Per altre informazioni su come modificare la versione del driver usata da PolyBase, vedere Change la versione del driver SQL Server per PolyBase.

PUSHDOWN = ON | SPENTO

Applica a: SQL Server 2019 (15.x) e versioni successive.

Indica se è possibile eseguire il pushdown del calcolo nell'origine dati esterna. Abilitato per impostazione predefinita.

PUSHDOWN è supportato quando ci si connette a SQL Server, Oracle, Teradata, MongoDB, l'API Azure Cosmos DB per MongoDB o ODBC a livello di origine dati esterna.

Per abilitare o disabilitare il pushdown a livello di query, si usa un hint.

CREDENZIALE = credential_name

Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna.

Note aggiuntive e indicazioni utili per la creazione delle credenziali:

• CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.

• Quando si accede a Archiviazione di Azure Account (V2) o Azure Data Lake Storage Gen2, il IDENTITY deve essere SHARED ACCESS SIGNATURE.

• Per un esempio, vedere Creare un'origine dati esterna per eseguire operazioni bulk e recuperare dati da Archiviazione di Azure al database SQL.

Esistono diversi modi per creare una firma di accesso condiviso:

• È possibile creare un token di firma di accesso condiviso passando al portale Azure><Your_Storage_Account>> di accesso condiviso>Configurare le autorizzazioni>Generate SAS e stringa di connessione. Per altre informazioni, vedere Generare una firma di accesso condiviso.

• È possibile creare e configurare una firma di accesso condiviso con Azure Storage Explorer.

• È possibile creare un token di firma di accesso condiviso a livello di codice tramite PowerShell, interfaccia della riga di comando di Azure, .NET e API REST. Per altre informazioni, vedere A accesso limitato alle risorse di Archiviazione di Azure usando firme di accesso condiviso (SAS).

• Il token di firma di accesso condiviso deve essere configurato come segue:

  • Quando viene generato un token di firma di accesso condiviso, include un punto interrogativo ('?') all'inizio del token. Escludere l'elemento iniziale ? quando configurato come SECRET.

  • Usare un periodo di scadenza valido (tutte le date sono espresse in formato UTC).

• Concedere almeno l'autorizzazione di lettura per il file che deve essere caricato ( ad esempio srt=o&sp=r). È possibile creare più firme di accesso condiviso per casi d'uso diversi. Le autorizzazioni devono essere concesse come segue:

  Azione	Autorizzazione
  Lettura di dati da un file	Lettura
  Leggere i dati da più file e sottocartelle	Lettura e Lista
  Usare Create External Table as Select (CETAS)	Lettura, creazione, elenco e scrittura

• Per Archiviazione BLOB di Azure e Azure Data Lake Gen 2:

  • Servizi consentiti: Blob deve essere selezionato per generare il token di firma di accesso condiviso

• Tipi di risorse consentiti: Container e Object devono essere selezionati per generare il token di firma di accesso condiviso

Per un esempio d'uso di un oggetto CREDENTIAL con una risorsa di archiviazione di oggetti compatibile con S3 e PolyBase, vedere Configurare PolyBase per l'accesso ai dati esterni in una risorsa di archiviazione di oggetti compatibile con S3.

Per creare credenziali con ambito database, vedere CREATE DATABASE SCOPED CREDENTIAL.

Autorizzazioni

Richiede CONTROL autorizzazione per il database in SQL Server.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Sicurezza

PolyBase supporta l'autenticazione basata su proxy per la maggior parte delle origini dati esterne. Creare credenziali con ambito database per creare l'account proxy.

Eseguire l'aggiornamento a SQL Server 2025

In SQL Server 2022 (16.x) e versioni successive le origini dati esterne hadoop non sono supportate. È necessario ricreare manualmente le origini dati esterne create in precedenza con TYPE = HADOOPe qualsiasi tabella esterna che usa questa origine dati esterna.

Gli utenti dovranno anche configurare le origini dati esterne per l'uso di nuovi connettori durante la connessione a Archiviazione di Azure.

Esempi

R. Creare un'origine dati esterna in SQL Server per fare riferimento a Oracle

Per creare un'origine dati esterna che fa riferimento a Oracle, assicurarsi di disporre di credenziali con ambito database. Facoltativamente, è anche possibile abilitare o disabilitare il push-down del calcolo su questa origine dati.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
WITH IDENTITY = 'oracle_username',
     SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    PUSHDOWN = ON,
    CREDENTIAL = OracleProxyAccount
);

Facoltativamente, l'origine dati esterna a Oracle può usare l'autenticazione proxy per fornire un controllo di accesso con granularità fine. Un utente proxy può essere configurato in modo che abbia accesso limitato rispetto all'utente rappresentato.

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
WITH IDENTITY = 'oracle_username',
     SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]
);

In alternativa, è possibile eseguire l'autenticazione tramite TNS.

A partire da SQL Server 2022 (16.x) aggiornamento cumulativo 2, CREATE EXTERNAL DATA SOURCE supporta ora l'uso di file TNS durante la connessione a Oracle.

Il CONNECTION_OPTIONS parametro è stato espanso e ora usa TNSNamesFile e ServerName come variabili per esplorare il tnsnames.ora file e stabilire la connessione con il server.

Nell'esempio seguente, durante il runtime SQL Server cercherà il percorso del file tnsnames.ora specificato da TNSNamesFile e cercherà l'host e la porta di rete specificata da ServerName.

CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
    LOCATION = N'oracle://XE',
    CREDENTIAL = [OracleCredentialTest],
    CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);

B. Creare un'origine dati esterna per fare riferimento a un'istanza denominata di SQL Server tramite la connettività PolyBase

Applica a: SQL Server 2019 (15.x) e versioni successive.

Per creare un'origine dati esterna che fa riferimento a un'istanza denominata di SQL Server, usare CONNECTION_OPTIONS per specificare il nome dell'istanza.

Prima di tutto, creare le credenziali con ambito database, archiviandole per un account di accesso con autenticazione SQL. Il connettore ODBC SQL per PolyBase supporta solo l'autenticazione di base. Prima di creare credenziali con ambito database, il database utente deve avere una chiave master per proteggerle. Per altre informazioni, vedere CREATE MASTER KEY. L'esempio seguente crea credenziali con ambito database. È necessario specificare account di accesso e password.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
     SECRET = 'password';

Nell'esempio seguente è WINSQL2019 il nome host e SQL2019 è il nome dell'istanza. 'Server=%s\SQL2019' è la coppia chiave-valore.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019',
    CONNECTION_OPTIONS = 'Server=%s\SQL2019',
    CREDENTIAL = SQLServerCredentials
);

In alternativa, è possibile usare una porta per connettersi a un'istanza predefinita di SQL Server.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019:58137',
    CREDENTIAL = SQLServerCredentials
);

C. Creare un'origine dati esterna per fare riferimento a una replica secondaria leggibile del gruppo di disponibilità Always On

Applica a: SQL Server 2019 (15.x) e versioni successive.

Per creare un'origine dati esterna che fa riferimento a una replica secondaria leggibile di SQL Server, usare CONNECTION_OPTIONS per specificare l'ApplicationIntent=ReadOnly. Inoltre, sarà necessario impostare il database di disponibilità come Database={dbname} in CONNECTION_OPTIONSoppure impostare il database di disponibilità come database predefinito dell'account di accesso usato per le credenziali con ambito database. È necessario eseguire questa operazione in tutte le repliche di disponibilità del gruppo di disponibilità.

Prima di tutto, creare le credenziali con ambito database, archiviandole per un account di accesso con autenticazione SQL. Il connettore ODBC SQL per PolyBase supporta solo l'autenticazione di base. Prima di creare credenziali con ambito database, il database utente deve avere una chiave master per proteggerle. Per altre informazioni, vedere CREATE MASTER KEY. L'esempio seguente crea credenziali con ambito database. È necessario specificare account di accesso e password.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
     SECRET = 'password';

Creare quindi la nuova origine dati esterna.

Indipendentemente dal fatto che sia stato incluso Database=dbname nel CONNECTION_OPTIONS database o impostato come database predefinito per l'account di accesso nella credenziale con ambito database, è comunque necessario specificare il nome del database tramite un nome in tre parti nell'istruzione CREATE EXTERNAL TABLE, all'interno del parametro LOCATION. Per un esempio, vedere CREATE EXTERNAL TABLE.

Nell'esempio seguente, WINSQL2019AGL è il nome del listener del gruppo di disponibilità e dbname è il nome del database come destinazione dell'istruzione CREATE EXTERNAL TABLE.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = SQLServerCredentials
);

È possibile illustrare il comportamento di reindirizzamento del gruppo di disponibilità specificando ApplicationIntent e creando una tabella esterna nella vista di sistema sys.servers. Nello script di esempio seguente vengono create due origini dati esterne e una tabella esterna per ogni origine. Usare le viste per verificare quale server risponde alla connessione. È anche possibile ottenere risultati simili tramite la funzionalità di routing di sola lettura. Per altre informazioni, vedere Configurare il routing di sola lettura per un gruppo di disponibilità Always On.

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
    CREDENTIAL = [SQLServerCredentials]
);
GO

All'interno del database nel gruppo di disponibilità creare una vista per restituire sys.servers e il nome dell'istanza locale, che consente di identificare la replica che risponde alla query. Per altre informazioni, vedere sys.servers.

CREATE VIEW vw_sys_servers AS
    SELECT [name]
    FROM sys.servers
    WHERE server_id = 0;
GO

Creare quindi una tabella esterna nell'istanza di origine:

CREATE EXTERNAL TABLE vw_sys_servers_ro
(
    name SYSNAME NOT NULL
)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

CREATE EXTERNAL TABLE vw_sys_servers_rw
(
    name SYSNAME NOT NULL
)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

SELECT [name]
FROM dbo.vw_sys_servers_ro;
--should return secondary replica instance

SELECT [name]
FROM dbo.vw_sys_servers_rw;
--should return primary replica instance
GO

D. Creare un'origine dati esterna per eseguire query su un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3 tramite PolyBase

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

Lo script di esempio seguente crea un'origine dati esterna s3_ds nel database utente di origine in SQL Server. L'origine dati esterna fa riferimento alle credenziali con ambito database di s3_dc.

CREATE DATABASE SCOPED CREDENTIAL s3_dc
WITH IDENTITY = 'S3 Access Key', -- for S3-compatible object storage the identity must always be S3 Access Key
    SECRET = '<access_key_id>:<secret_key_id>'; -- provided by the S3-compatible object storage
GO

CREATE EXTERNAL DATA SOURCE s3_ds
WITH (
    LOCATION = 's3://<ip_address>:<port>/',
    CREDENTIAL = s3_dc
);
GO

Verificare la nuova origine dati esterna con sys.external_data_sources.

SELECT *
FROM sys.external_data_sources;

L'esempio seguente illustra quindi l'uso di T-SQL per eseguire query su un file Parquet archiviato nell'archiviazione oggetti compatibile con S3 tramite la query OPENROWSET. Per altre informazioni, vedere Virtualizzare un file Parquet in una risorsa di archiviazione di oggetti compatibile con S3 con PolyBase.

SELECT *
FROM OPENROWSET (
    BULK '/<bucket>/<parquet_folder>',
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_ds'
) AS [cc];

E. Creare un'origine dati esterna usando ODBC generico in PostgreSQL

Come negli esempi precedenti, creare prima una chiave master del database e credenziali con ambito database. Le credenziali con ambito database verranno usate per l'origine dati esterna. In questo esempio si presuppone anche che nel server sia installato un provider di dati ODBC generico per PostgreSQL.

In questo esempio, il provider di dati ODBC generico viene usato per connettersi a un server di database PostgreSQL nella stessa rete, dove il nome di dominio completo del server PostgreSQL è POSTGRES1, usando la porta predefinita tcp 5432.

CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
    LOCATION = 'odbc://POSTGRES1.domain:5432',
    CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
    CREDENTIAL = postgres_credential
);

Archiviazione di Azure

Creare una firma di accesso condiviso

Sia per Archiviazione BLOB di Azure che per Azure Data Lake Gen2, il metodo di autenticazione supportato è la firma di accesso condiviso (SAS). Un modo semplice per generare un token di firma di accesso condiviso seguire la procedura seguente. Per altre informazioni, vedere CREDENTIAL.

1. Passare al portale di Azure e all'account di archiviazione desiderato.
2. Passare al contenitore desiderato nel menu Archiviazione dati.
3. Selezionare Token di accesso condiviso.
4. Scegliere l'autorizzazione appropriata in base all'azione desiderata. Per riferimento, usare la tabella seguente:

1. Scegliere la data di scadenza del token.
2. Generare il token di firma di accesso condiviso e l'URL.
3. Copiare il token di firma di accesso condiviso.

F. Creare un'origine dati esterna per accedere ai dati in Archiviazione BLOB di Azure usando l'interfaccia abs://

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

Usare un nuovo prefisso abs per Archiviazione di Azure Account v2. Il abs prefisso supporta l'autenticazione tramite SHARED ACCESS SIGNATURE. Il abs prefisso sostituisce wasb, usato nelle versioni precedenti. HADOOP non è più supportato, non è più necessario usare TYPE = BLOB_STORAGE.

La chiave dell'account di archiviazione Azure non è più necessaria, ma usando il token di firma di accesso condiviso, come illustrato nell'esempio seguente:

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD= '<password>';
GO

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredentialv2
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', -- to use SAS the identity must be fixed as-is
    SECRET = '<Blob_SAS_Token>';
GO

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredentialv2
);

Per un esempio più dettagliato su come accedere ai file CSV archiviati in Archiviazione BLOB di Azure, vedere Virtualize CSV file with PolyBase.

G. Creare un'origine dati esterna per accedere ai dati in Azure Data Lake Gen2

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

Usare un nuovo prefisso adls per Azure Data Lake Gen2, sostituendo abfs usato nelle versioni precedenti. Il prefisso supporta anche il adls token di firma di accesso condiviso come metodo di autenticazione, come illustrato in questo esempio:

--Create a database scoped credential using SAS Token
CREATE DATABASE SCOPED CREDENTIAL datalakegen2
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '<DataLakeGen2_SAS_Token>';
GO

CREATE EXTERNAL DATA SOURCE data_lake_gen2_dfs
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = datalakegen2
);

Per un esempio più dettagliato su come accedere ai file delta archiviati in Azure Data Lake Gen2, vedere Virtualize delta table with PolyBase.

Esempi: operazioni bulk

H. Creare un'origine dati esterna per operazioni bulk che recuperano dati da Archiviazione di Azure

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

Usare l'origine dati seguente per le operazioni bulk che usano BULK INSERT o OPENROWSET. Le credenziali devono impostare SHARED ACCESS SIGNATURE come identità, non devono includere il carattere ? iniziale nel token di firma di accesso condiviso, devono avere almeno un'autorizzazione di lettura per il file da caricare (ad esempio srt=o&sp=r). Inoltre il periodo di scadenza deve essere valido (tutte le date sono in formato UTC). Per altre informazioni sulle firme di accesso condiviso, vedere Uso delle firme di accesso condiviso.

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

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AccessAzureInvoices,
);

Io. Creare un'origine dati esterna usando TDS 8.0 per connettersi a un altro SQL Server

Applica a: SQL Server 2025 (17.x) e versioni successive.

Quando si usa la versione più recente Microsoft del driver ODBC 18 per SQL Server, è necessario usare l'opzione Encryption in CONNECTION_OPTIONS ed è supportata anche TrustServerCertificate. Se Encryption non viene specificato, il comportamento predefinito è Encrypt=Yes;TrustServerCertificate=No;e è necessario un certificato server.

In questo esempio viene usata l'autenticazione SQL. Per proteggere le credenziali, è necessaria una chiave master del database (DMK). Per altre informazioni, vedere CREATE MASTER KEY. L'esempio seguente crea una credenziale con ambito database con un account di accesso e una password personalizzati.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = '<username>',
     SECRET = '<password>';

Il nome del server di destinazione è WINSQL2022, la porta 58137e si tratta di un'istanza predefinita. Specificando Encrypt=Strict, la connessione usa TDS 8.0 e il certificato del server viene sempre verificato. In questo esempio, l'oggetto HostnameinCertificate usato è WINSQL2022:

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2022:58137',
    CONNECTION_OPTIONS = 'Encrypt=Strict;HostnameInCertificate=WINSQL2022;'
    CREDENTIAL = SQLServerCredentials
);

J. Creare un'origine dati esterna usando la crittografia e l'opzione TrustServerCertificate

Nell'esempio precedente sono riportati due esempi di codice. Il primo frammento di codice contiene Encryption e TrustServerCertificate imposta.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2022:58137',
    CONNECTION_OPTIONS = 'Encrypt=Yes;HostnameInCertificate=WINSQL2022;TrustServerCertificate=Yes;'
    CREDENTIAL = SQLServerCredentials
);

Il frammento di codice seguente non è Encryption abilitato.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2022:58137',
    CONNECTION_OPTIONS = 'Encrypt=no;'
    CREDENTIAL = SQLServerCredentials
);

Contenuti correlati

• ALTER EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso
• configurazione della connettività PolyBase (Transact-SQL)

Panoramica: database SQL di Azure

Applies to: database SQL di Azure

Crea un'origine dati esterna per le query elastiche. Le origini dati esterne vengono usate per stabilire la connettività e supportano questi casi d'uso principali:

• Virtualizzazione dei dati (anteprima)
• Operazioni di caricamento bulk con BULK INSERT o OPENROWSET
• Eseguire query su database SQL remoto o istanze di Azure Synapse usando il database SQL con query elastic
• Esecuzione di query su un database SQL partizionato usando una query elastica

convenzioni di sintassi Transact-SQL

Sintassi

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER } ]
    [ [ , ] DATABASE_NAME = '<database_name>' ]
    [ [ , ] SHARD_MAP_NAME = '<shard_map_manager>' ] )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database nel database SQL.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

Percorso:

• <shard_map_server_name> = Nome del server logico in Azure che ospita la gestione mappe partizioni. L'argomento DATABASE_NAME fornisce il database usato per ospitare la mappa partizioni, mentre SHARD_MAP_NAME viene usato per la mappa partizioni stessa.
• <remote_server_name> = nome del server logico di destinazione per la query elastica. Per specificare il nome del database, si usa l'argomento DATABASE_NAME.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• Il motore di database non verifica l'esistenza dell'origine dati esterna quando viene creato l'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.

CREDENZIALE = credential_name

Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna.

Note aggiuntive e indicazioni utili per la creazione delle credenziali:

• Per caricare dati da Archiviazione di Azure in database SQL di Azure, usare un token di firma di accesso condiviso.
• CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.
• Quando TYPE = BLOB_STORAGE, è necessario creare le credenziali usando SHARED ACCESS SIGNATURE come identità.
• Quando la connessione a Archiviazione di Azure usa il connettore WASB[s], l'autenticazione deve essere eseguita con una chiave dell'account di archiviazione, non con una firma di accesso condiviso .
• Quando TYPE = HADOOP è necessario creare le credenziali usando la chiave dell'account di archiviazione come SECRET.
• TYPE = BLOB_STORAGE è consentito solo per le operazioni bulk; non è possibile creare tabelle esterne per un'origine dati esterna con TYPE = BLOB_STORAGE.

Esistono diversi modi per creare una firma di accesso condiviso:

• È possibile creare un token di firma di accesso condiviso passando al portale Azure -><Your_Storage_Account> -> Firma di accesso condiviso -> Configurare le autorizzazioni -> Generare firma di accesso condiviso e stringa di connessione. Per altre informazioni, vedere Generare una firma di accesso condiviso.

• È possibile creare e configurare una firma di accesso condiviso con Azure Storage Explorer.

• È possibile creare un token di firma di accesso condiviso a livello di codice tramite PowerShell, interfaccia della riga di comando di Azure, .NET e API REST. Per altre informazioni, vedere A accesso limitato alle risorse di Archiviazione di Azure usando firme di accesso condiviso (SAS).

• Il token di firma di accesso condiviso deve essere configurato come segue:

  • Quando viene generato un token di firma di accesso condiviso, include un punto interrogativo ('?') all'inizio del token. Escludere l'elemento iniziale ? quando configurato come SECRET.
  • Usare un periodo di scadenza valido (tutte le date sono espresse in formato UTC).

• Concedere almeno l'autorizzazione di lettura per il file che deve essere caricato ( ad esempio srt=o&sp=r). È possibile creare più firme di accesso condiviso per casi d'uso diversi. Le autorizzazioni devono essere concesse come segue:

  Azione	Autorizzazione
  Lettura di dati da un file	Lettura
  Leggere i dati da più file e sottocartelle	Lettura e Lista
  Usare Create External Table as Select (CETAS)	Lettura, creazione e scrittura

Per un esempio di utilizzo di un CREDENTIAL con SHARED ACCESS SIGNATURE e TYPE = BLOB_STORAGE, vedere Creare un'origine dati esterna per eseguire operazioni bulk e recuperare dati da Archiviazione di Azure nel database SQL

Per creare credenziali con ambito database, vedere CREATE DATABASE SCOPED CREDENTIAL.

TIPO = * [ BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER ] *

Specifica il tipo dell'origine dati esterna da configurare. Questo parametro non è sempre obbligatorio e deve essere fornito solo per determinate origini dati esterne.

• Usare RDBMS per le query tra database usando una query elastica dal database SQL.
• Usare SHARD_MAP_MANAGER durante la creazione di un'origine dati esterna per la connessione a un database SQL partizionato.

• L'uso BLOB_STORAGE è per l'uso solo con il https prefisso . Per abd i prefissi e adls non specificare TYPE.

DATABASE_NAME = database_name

Configurare questo argomento quando TYPE è impostato su RDBMS o SHARD_MAP_MANAGER.

Per un esempio che mostra come creare un'origine dati esterna in cui TYPE = RDBMS, fare riferimento a Creare un'origine dati esterna RDBMS.

SHARD_MAP_NAME = shard_map_name

Usato quando l'argomento TYPE è impostato su SHARD_MAP_MANAGER solo per impostare il nome della mappa partizioni.

Per un esempio relativo alla creazione di un'origine dati esterna in cui TYPE = SHARD_MAP_MANAGER, vedere Creare un'origine dati esterna del gestore mappe partizioni

Autorizzazioni

Richiede CONTROL autorizzazione per il database in database SQL di Azure.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Esempi

R. Creare un'origine dati esterna del gestore mappe partizioni

Per creare un'origine dati esterna per fare riferimento a un SHARD_MAP_MANAGER, specificare il nome del server di database SQL che ospita il gestore mappe partizioni nel database SQL o un database SQL Server in una macchina virtuale.

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

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
WITH IDENTITY = '<username>',
     SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
    TYPE = SHARD_MAP_MANAGER,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = 'ElasticScaleStarterKit_ShardMapManagerDb',
    CREDENTIAL = ElasticDBQueryCred,
    SHARD_MAP_NAME = 'CustomerIDShardMap'
);

Per un'esercitazione dettagliata, vedere Introduzione alle query di database elastico per il partizionamento orizzontale.

B. Creare un'origine dati esterna RDBMS

Per creare un'origine dati esterna per fare riferimento a un RDBMS, specificare il nome del server di database SQL del database remoto nel database SQL.

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

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
WITH IDENTITY = '<username>',
     SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
    TYPE = RDBMS,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = 'Customers',
    CREDENTIAL = SQL_Credential
);

Per un'esercitazione dettagliata su RDBMS, vedere Introduzione alle query tra database (partizionamento verticale).

Esempi: operazioni bulk

C. Creare un'origine dati esterna per operazioni bulk che recuperano dati da Archiviazione di Azure

Utilizzare l'origine dati seguente per le operazioni bulk usando BULK INSERT o OPENROWSET BULK. Le credenziali devono impostare SHARED ACCESS SIGNATURE come identità, non devono includere il carattere ? iniziale nel token di firma di accesso condiviso, devono avere almeno un'autorizzazione di lettura per il file da caricare (ad esempio srt=o&sp=r). Inoltre il periodo di scadenza deve essere valido (tutte le date sono in formato UTC). Per altre informazioni sulle firme di accesso condiviso, vedere Uso delle firme di accesso condiviso.

Creare un'origine dati esterna per Archiviazione BLOB di Azure (ABS) usando l'identità gestita:

CREATE DATABASE SCOPED CREDENTIAL DSC_MI
WITH IDENTITY = 'Managed Identity';

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE PrivateABS
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = [DSC_MI]
);

Creare un'origine dati esterna per Azure Data Lake Gen2 (ADLS) usando l'identità utente:

CREATE DATABASE SCOPED CREDENTIAL DSC_ADLS
WITH IDENTITY = 'User Identity';

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE PrivateADLS
WITH (
    LOCATION = 'adls://<container>@<storage_account_name>.dfs.core.windows.net/',
    CREDENTIAL = [DSC_ADLS]
);

Per un esempio di utilizzo, vedere BULK INSERT.

Esempi: SQL Edge di Azure

R. Creare un'origine dati esterna per fare riferimento a Kafka

Applies to:SQL Edge di Azureonly

In questo esempio l'origine dati esterna è un server Kafka con indirizzo IP xxx.xxx.xxx.xxx e in ascolto sulla porta 1900. L'origine dati esterna Kafka è solo per il flusso di dati e non supporta il push del predicato.

-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyKafkaServer
WITH (
    LOCATION = 'kafka://xxx.xxx.xxx.xxx:1900'
);

B. Creare un'origine dati esterna per fare riferimento a EdgeHub

Applies to:SQL Edge di Azureonly

In questo esempio l'origine dati esterna è un EdgeHub in esecuzione nello stesso dispositivo perimetrale di SQL Edge di Azure. L'origine dati esterna edgeHub è solo per il flusso di dati e non supporta il push del predicato.

-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyEdgeHub
WITH (
    LOCATION = 'edgehub://'
);

Contenuti correlati

• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso
• Introduzione alla query elastica

Panoramica: Azure Synapse Analytics

Applica a: Azure Synapse Analytics

Crea un'origine dati esterna per la virtualizzazione dei dati. Le origini dati esterne vengono usate per stabilire la connettività e supportare il caso d'uso principale della virtualizzazione dei dati e del caricamento dei dati da origini dati esterne. Per altre informazioni, consultare Usare tabelle esterne con Synapse SQL.

convenzioni di sintassi Transact-SQL

Sintassi

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
  [ [ , ] CREDENTIAL = <credential_name> ]
  [ [ , ] TYPE = HADOOP ]
)
[ ; ]

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(   LOCATION = '<prefix>://<path>'
)
[;]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database SQL di Azure in Azure Synapse Analytics.

LOCATION = '<prefisso>://<path>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

* Microsoft Azure Data Lake Storage Gen1 ha un supporto limitato, Gen2 è consigliato per tutti i nuovi sviluppi.

* Pool SQL serverless e dedicati in Azure Synapse Analytics usare codebase diversi per la virtualizzazione dei dati. I pool SQL serverless supportano una tecnologia di virtualizzazione dei dati nativa. I pool SQL dedicati supportano la virtualizzazione dei dati nativa e PolyBase. La virtualizzazione dei dati PolyBase viene usata quando si crea EXTERNAL DATA SOURCE con TYPE=HADOOP.

** Microsoft Azure Data Lake Storage Gen1 ha un supporto limitato, Gen2 è consigliato per tutti i nuovi sviluppi.

Il connettore più sicuro wasbs è consigliato su wasb. Solo la virtualizzazione dei dati nativa nei pool SQL dedicati (in cui TYPE non è uguale a HADOOP) supporta wasb.

Percorso:

• <container> = contenitore dell'account di archiviazione che include i dati. I contenitori radice sono di sola lettura, di conseguenza i dati non possono essere riscritti nel contenitore.
• <storage_account> = nome dell'account di archiviazione della risorsa Azure.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• L'opzione predefinita consiste nell'usare enable secure SSL connections durante il provisioning di Azure Data Lake Storage Gen2. Quando questa opzione è abilitata, è necessario usare abfss quando è selezionata una connessione TLS/SSL sicura, anche se abfss funziona anche per le connessioni TLS non sicure. Per altre informazioni, vedere the Azure Blob Filesystem Driver (ABFS).
• Azure Synapse non verifica l'esistenza dell'origine dati esterna al momento della creazione dell'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.
• Per garantire una semantica di esecuzione di query coerente, usare la stessa origine dati esterna per tutte le tabelle quando si eseguono query su Hadoop.
• Il prefisso https: consente di usare la sottocartella nel percorso. https non è disponibile per tutti i metodi di accesso ai dati.
• wasbs è consigliato quando i dati vengono inviati usando una connessione TLS protetta.
• Gli spazi dei nomi gerarchici non sono supportati con Azure account di archiviazione V2 quando accedono ai dati usando l'interfaccia legacy wasb://, ma l'uso di wasbs:// supporta gli spazi dei nomi gerarchici.

CREDENZIALE = credential_name

Facoltativo. Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna. L'origine dati esterna senza credenziali può accedere all'account di archiviazione pubblico o usare l'identità Microsoft Entra del chiamante per accedere ai file nella risorsa di archiviazione Azure.

Note aggiuntive e indicazioni utili per la creazione delle credenziali:

• Per caricare dati da Archiviazione di Azure o Azure Data Lake Store (ADLS) Gen2 in Azure Synapse Analytics, usare una chiave Archiviazione di Azure.
• CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.

Per creare credenziali con ambito database, vedere CREATE DATABASE SCOPED CREDENTIAL.

• Nel pool SQL serverless le credenziali con ambito database possono specificare l'identità gestita dell'area di lavoro, il nome dell'entità servizio o il token di firma di accesso condiviso.In serverless SQL pool, database-scoped credentials can specify workspace managed identity, service principal name, or shared access signature (SAS). L'accesso tramite un'identità utente, noto anche come Microsoft Entra pass-through, è possibile anche nelle credenziali con ambito database, come l'accesso anonimo all'archiviazione disponibile pubblicamente. Per altre informazioni, vedere Tipi di autorizzazione di archiviazione supportati.

• Nel pool SQL dedicato le credenziali con ambito database possono specificare token di firma di accesso condiviso, chiave di accesso alle risorse di archiviazione, entità servizio, identità gestita dell'area di lavoro o Microsoft Entra pass-through.

TIPO = HADOOP

Facoltativo, non consigliato.

È possibile specificare TYPE solo con pool SQL dedicati. HADOOP è l'unico valore consentito se specificato. Le origini dati esterne con TYPE=HADOOP sono disponibili solo in pool SQL dedicati.

Usare HADOOP per le implementazioni legacy. In caso contrario, è consigliabile usare l'accesso ai dati nativi più recente. Non specificare l'argomento TYPE per usare l'accesso ai dati nativo più recente.

Per un esempio di uso di TYPE = HADOOP per caricare dati da Archiviazione di Azure, vedere Creare un'origine dati esterna per fare riferimento a Azure Data Lake Store Gen 1 o 2 usando un'entità servizio.

I pool SQL serverless e dedicati in Azure Synapse Analytics usano codebase diversi per la virtualizzazione dei dati. I pool SQL serverless supportano una tecnologia di virtualizzazione dei dati nativa. I pool SQL dedicati supportano la virtualizzazione dei dati nativa e PolyBase. La virtualizzazione dei dati PolyBase viene usata quando si crea EXTERNAL DATA SOURCE con TYPE=HADOOP.

Autorizzazioni

È richiesta l'autorizzazione CONTROL per il database.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Sicurezza

La maggior parte delle origini dati esterne supporta l'autenticazione basata su proxy, usando credenziali con ambito database per creare l'account proxy.

Le chiavi di firma di accesso condiviso (SAS) sono supportate per l'autenticazione per Azure Data Lake account di archiviazione di archiviazione di seconda generazione. I clienti che vogliono eseguire l'autenticazione usando una firma di accesso condiviso devono creare credenziali con ambito database in cui IDENTITY = "Shared Access Signature" e immettere un token di firma di accesso condiviso come segreto.

Se si crea una credenziale con ambito database in cui IDENTITY = "Shared Access Signature" e si usa un valore di chiave di archiviazione come segreto, verrà visualizzato il messaggio di errore seguente:

'HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: "Server failed to authenticate the request. Please refer to the information in the www-authenticate header.", 401, HEAD, [Storage path URL]'

Esempi

R. Creare un'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia wasb://

In questo esempio l'origine dati esterna è un account Archiviazione di Azure V2 denominato logs. Il contenitore di stoccaggio è chiamato daily. Il Archiviazione di Azure'origine dati esterna è solo per il trasferimento dei dati. e non supporta il pushdown dei predicati. Gli spazi dei nomi gerarchici non sono supportati durante l'accesso ai dati tramite l'interfaccia wasb:// . Quando ci si connette al Archiviazione di Azure tramite wasb o wasbs, l'autenticazione deve essere eseguita con una chiave dell'account di archiviazione, non con una firma di accesso condiviso.

Questo esempio usa il metodo di accesso basato su HADOOP legacy Java. Nell'esempio seguente viene illustrato come creare le credenziali con ambito database per l'autenticazione da Archiviazione di Azure. Specificare la chiave dell'account Archiviazione di Azure nel segreto delle credenziali del database. È possibile specificare qualsiasi stringa nell'identità delle credenziali con ambito database perché non viene usata durante l'autenticazione per Azure archiviazione.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
     SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential
);

B. Creare un'origine dati esterna per fare riferimento a Azure Data Lake Store Gen 1 o 2 usando un'entità servizio

Azure Data Lake la connettività dello Store può essere basata sull'URI di ADLS e sull'entità servizio dell'applicazione Microsoft Entra. La documentazione per la creazione di questa applicazione è disponibile in Data Lake Store authentication using Microsoft Entra ID.

-- If you do not have a Master Key on your DW you will need to create one.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- These values come from your Microsoft Entra application used to authenticate to ADLS
CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
    -- IDENTITY = '<clientID>@<OAuth2.0TokenEndPoint>' ,
    IDENTITY = '536540b4-4239-45fe-b9a3-629f97591c0c@https://login.microsoftonline.com/42f988bf-85f1-41af-91ab-2d2cd011da47/oauth2/token',
    -- SECRET = '<KEY>'
    SECRET = 'BjdIlmtKp4Fpyh9hIvr8HJlUida/seM5kQ3EpLAmeDI=';

-- For Gen 1 - Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Storage.
-- LOCATION: Provide Data Lake Storage Gen 1 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
    TYPE = HADOOP,
    LOCATION = 'adl://newyorktaxidataset.azuredatalakestore.net',
    CREDENTIAL = ADLS_credential
);

-- For Gen2 - Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Storage.
-- LOCATION: Provide Data Lake Storage Gen2 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
    -- Note the abfss endpoint when your account has secure transfer enabled
    TYPE = HADOOP,
    LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net',
    CREDENTIAL = ADLS_credential
);

C. Creare un'origine dati esterna per fare riferimento a Azure Data Lake Store Gen2 usando la chiave dell'account di archiviazione

-- If you do not have a Master Key on your DW you will need to create one.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
    -- IDENTITY = '<storage_account_name>' ,
    IDENTITY = 'newyorktaxidata',
    -- SECRET = '<storage_account_key>'
    SECRET = 'yz5N4+bxSb89McdiysJAzo+9hgEHcJRJuXbF/uC3mhbezES/oe00vXnZEl14U0lN3vxrFKsphKov16C0w6aiTQ==';

-- Note this example uses a Gen2 secured endpoint (abfss)
CREATE EXTERNAL DATA SOURCE < data_source_name >
WITH (
    LOCATION = 'abfss://2013@newyorktaxidataset.dfs.core.windows.net',
    CREDENTIAL = ADLS_credential,
    TYPE = HADOOP
);

D. Creare un'origine dati esterna per Azure Data Lake Store Gen2 usando abfs://

Non è necessario specificare SECRET durante la connessione all'account Azure Data Lake Store Gen2 con identità managed.

-- If you do not have a Master Key on your DW you will need to create one
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

--Create database scoped credential with **IDENTITY = 'Managed Service Identity'**
CREATE DATABASE SCOPED CREDENTIAL msi_cred
WITH IDENTITY = 'Managed Service Identity';

--Create external data source with abfss:// scheme for connecting to your Azure Data Lake Store Gen2 account
CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://myfile@mystorageaccount.dfs.core.windows.net',
    CREDENTIAL = msi_cred
);

Contenuti correlati

• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT (Azure Synapse Analytics)
• CREATE TABLE AS SELECT (Azure Synapse Analytics)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso

Panoramica: Sistema della piattaforma di analisi

Si applica a: Sistema piattaforma di analisi (PDW)

Crea un'origine dati esterna per le query PolyBase. Le origini dati esterne vengono usate per stabilire la connettività e supportare il caso d'uso seguente: Virtualizzazione dei dati e caricamento dei dati tramite PolyBase in SQL Server.

convenzioni di sintassi Transact-SQL

Sintassi

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = HADOOP ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del server nella piattaforma di strumenti analitici (PDW).

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

Percorso:

• <Namenode> = nome del computer, URI del servizio dei nomi o indirizzo IP di Namenode nel cluster Hadoop. PolyBase deve risolvere tutti i nomi DNS usati dal cluster Hadoop.
• port = porta su cui è in ascolto l'origine dati esterna. Per trovare la porta in Hadoop, si usa il parametro di configurazione fs.defaultFS. L'impostazione predefinita è 8020.
• <container> = contenitore dell'account di archiviazione che include i dati. I contenitori radice sono di sola lettura, di conseguenza i dati non possono essere riscritti nel contenitore.
• <storage_account> = nome dell'account di archiviazione della risorsa Azure.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• Il motore PDW non verifica l'esistenza dell'origine dati esterna quando viene creato l'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.
• Per garantire una semantica di esecuzione di query coerente, usare la stessa origine dati esterna per tutte le tabelle quando si eseguono query su Hadoop.
• wasbs è consigliato quando i dati vengono inviati usando una connessione TLS protetta.
• Gli spazi dei nomi gerarchici non sono supportati quando vengono usati con account Archiviazione di Azure in wasb://.
• Per garantire la corretta esecuzione delle query di PolyBase durante un failover di Namenode di Hadoop, provare a usare un indirizzo IP virtuale per l'istanza di Namenode del cluster Hadoop. In caso contrario, eseguire ALTER EXTERNAL DATA SOURCE per puntare alla nuova posizione.

CREDENZIALE = credential_name

Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna.

Note aggiuntive e indicazioni utili per la creazione delle credenziali:

• Per caricare dati da Archiviazione di Azure in Azure Synapse o PDW, usare una chiave Archiviazione di Azure.
• CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.

TIPO = * [ HADOOP ] *

Specifica il tipo dell'origine dati esterna da configurare. Questo parametro non è sempre obbligatorio.

• Usare HADOOP quando l'origine dati esterna è Cloudera CDH, Hortonworks HDP o Archiviazione di Azure.

Per un esempio di uso di TYPE = HADOOP per caricare dati da Archiviazione di Azure, vedere Creare un'origine dati esterna per fare riferimento a Hadoop.

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:p ort]'

In SQL Server 2019 (15.x), non specificare RESOURCE_MANAGER_LOCATION a meno che non ci si connetta a Cloudera CDH, Hortonworks HDP, un account Archiviazione di Azure.

Configurare questo valore facoltativo quando ci si connette a Cloudera CDH, Hortonworks HDP o solo un account Archiviazione di Azure. Per un elenco completo delle versioni di Hadoop supportate, vedere Configurazione della connettività PolyBase.

RESOURCE_MANAGER_LOCATION Quando viene definito, Query Optimizer prende una decisione basata sui costi per migliorare le prestazioni. È possibile usare un processo MapReduce per eseguire il pushdown del calcolo in Hadoop. Specificando il parametro RESOURCE_MANAGER_LOCATION, è possibile ridurre significativamente il volume dei dati trasferiti tra Hadoop e SQL e quindi migliorare le prestazioni delle query.

Se la Resource Manager non è specificata, il push del calcolo in Hadoop è disabilitato per le query PolyBase. In Creare un'origine dati esterna per fare riferimento a Hadoop con il pushdown abilitato viene fornito un esempio concreto, oltre a ulteriori indicazioni.

Il valore RESOURCE_MANAGER_LOCATION non viene convalidato quando si crea l'origine dati esterna. L'immissione di un valore non corretto potrebbe causare un errore di query in fase di esecuzione ogni volta che si tenta il push-down perché il valore specificato non sarebbe in grado di risolvere.

Affinché PolyBase funzioni correttamente con un'origine dati esterna Hadoop, le porte per i componenti del cluster Hadoop seguenti devono essere aperte:

• Porte HDFS
  • Namenode
  • Datanode
• Resource Manager
  • Invio di processi
• Cronologia dei processi

Se la porta non è specificata, per la scelta del valore predefinito si usa l'impostazione corrente della configurazione 'hadoop connectivity'.

Nella tabella seguente vengono illustrate le porte predefinite per questi componenti. Esiste una dipendenza della versione di Hadoop e la possibilità di una configurazione personalizzata che non usa l'assegnazione di porta predefinita.

Autorizzazioni

Richiede l'autorizzazione CONTROL per il database nella piattaforma di strumenti analitici (PDW).

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Sicurezza

PolyBase supporta l'autenticazione basata su proxy per la maggior parte delle origini dati esterne. Creare credenziali con ambito database per creare l'account proxy.

Un token di firma di accesso condiviso con tipo HADOOP non è supportato. È supportato solo il tipo BLOB_STORAGE quando viene usata una chiave di accesso dell'account di archiviazione. Il tentativo di creare un'origine dati esterna di tipo HADOOP e le credenziali di firma di accesso condiviso potrebbe non riuscire e potrebbe essere visualizzato l'errore:

Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account aren't valid.: Error [Parameters provided to connect to the Azure storage account aren't valid.] occurred while accessing external file.'

Esempi

R. Creare un'origine dati esterna per fare riferimento a Hadoop

Per creare un'origine dati esterna per fare riferimento a Hortonworks HDP o Cloudera CDH, specificare il nome del computer o l'indirizzo IP di Namenode Hadoop e la porta.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8050'
);

B. Creare un'origine dati esterna per fare riferimento a Hadoop con il pushdown abilitato

Specificare l'opzione RESOURCE_MANAGER_LOCATION per abilitare il pushdown del calcolo in Hadoop per le query PolyBase. Dopo l'abilitazione, PolyBase prende una decisione basata sui costi per determinare se eseguire il push del calcolo delle query in Hadoop.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8020',
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Creare un'origine dati esterna per fare riferimento a Hadoop con protezione Kerberos

Per verificare se il cluster Hadoop è protetto tramite Kerberos, controllare il valore della proprietà hadoop.security.authentication nel file core-site.xml di Hadoop. Per fare riferimento a un cluster Hadoop protetto tramite Kerberos, è necessario specificare una credenziale con ambito database che contiene il nome utente e la password di Kerberos. La chiave master del database viene usata per crittografare il segreto della credenziale con ambito database.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>',
     SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Creare un'origine dati esterna per accedere ai dati in Archiviazione di Azure usando l'interfaccia wasb://

In questo esempio l'origine dati esterna è un account di archiviazione Azure V2 denominato logs. Il contenitore di stoccaggio è chiamato daily. Il Archiviazione di Azure'origine dati esterna è solo per il trasferimento dei dati. e non supporta il pushdown dei predicati. Gli spazi dei nomi gerarchici non sono supportati durante l'accesso ai dati tramite l'interfaccia wasb:// . Quando ci si connette al Archiviazione di Azure tramite wasb o wasbs, l'autenticazione deve essere eseguita con una chiave dell'account di archiviazione, non con una firma di accesso condiviso.

Questo esempio illustra come creare le credenziali con ambito database per l'autenticazione per Azure archiviazione. Specificare la chiave dell'account di archiviazione Azure nel segreto delle credenziali del database. È possibile specificare qualsiasi stringa nell'identità delle credenziali con ambito database perché non viene usata durante l'autenticazione per Azure archiviazione.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
     SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential
);

Contenuti correlati

• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso

Panoramica: Istanza gestita di SQL di Azure

Applica a: Istanza gestita di SQL di Azure

Crea un'origine dati esterna in Istanza gestita di SQL di Azure. Per informazioni complete, vedere Virtualizzazione dei dati con Istanza gestita di SQL di Azure.

La virtualizzazione dei dati in Istanza gestita di SQL di Azure consente di accedere a dati esterni in diversi formati di file tramite OPENROWSET o CREATE EXTERNAL TABLE.

convenzioni di sintassi Transact-SQL

Sintassi

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
  )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

Il motore di database non verifica l'esistenza dell'origine dati esterna quando viene creato l'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.

Non aggiungere parametri finali /, nome file o firma di accesso condiviso alla fine dell'URL durante la configurazione di un'origine LOCATION dati esterna per le operazioni bulk.

CREDENZIALE = credential_name

Specifica una credenziale con ambito database per l'autenticazione nell'origine dati esterna.

Note aggiuntive e indicazioni utili per la creazione delle credenziali:

• Per caricare dati da Archiviazione di Azure in Istanza gestita di SQL di Azure, usare un token di firma di accesso condiviso.
• CREDENTIAL è obbligatorio solo se i dati sono stati protetti. CREDENTIAL non è obbligatorio per i set di dati che consentono l'accesso anonimo.
• Se è necessaria una credenziale, è necessario creare le credenziali usando Managed Identity o SHARED ACCESS SIGNATURE come IDENTITÀ. Per creare credenziali con ambito database, vedere CREATE DATABASE SCOPED CREDENTIAL.

Per usare l'identità del servizio gestito per le credenziali con ambito database:

• Specificare WITH IDENTITY = 'Managed Identity'

• Usare l'identità del servizio gestito assegnata dal sistema del Istanza gestita di SQL di Azure, che deve essere abilitata se deve essere usata a questo scopo.

  • Concedere il ruolo controllo degli accessi in base al ruolo Reader Azure all'identità del servizio gestito assegnata dal sistema del Istanza gestita di SQL di Azure ai contenitori Archiviazione BLOB di Azure necessari. Ad esempio, tramite il portale di Azure, vedere Assign Azure roles using the Azure portal.

Per creare una firma di accesso condiviso per le credenziali con ambito database:

• Specificare WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = ...

• Esistono diversi modi per creare una firma di accesso condiviso:

  • È possibile ottenere un token di firma di accesso condiviso passando al portale Azure -><Your_Storage_Account> -> Firma di accesso condiviso -> Configurare le autorizzazioni -> Generare firma di accesso condiviso e stringa di connessione. Per altre informazioni, vedere Generare una firma di accesso condiviso.
  • È possibile creare e configurare una firma di accesso condiviso con Azure Storage Explorer.
  • È possibile creare un token di firma di accesso condiviso a livello di codice tramite PowerShell, interfaccia della riga di comando di Azure, .NET e API REST. Per altre informazioni, vedere A accesso limitato alle risorse di Archiviazione di Azure usando firme di accesso condiviso (SAS).

• Il token di firma di accesso condiviso deve essere configurato come segue:

  • Quando viene generato un token di firma di accesso condiviso, include un punto interrogativo ('?') all'inizio del token. Escludere l'elemento iniziale ? quando configurato come SECRET.
  • Usare un periodo di scadenza valido (tutte le date sono espresse in formato UTC).

• Concedere almeno l'autorizzazione di lettura per il file che deve essere caricato ( ad esempio srt=o&sp=r). È possibile creare più firme di accesso condiviso per casi d'uso diversi. Le autorizzazioni devono essere concesse come segue:

  Azione	Autorizzazione
  Lettura di dati da un file	Lettura
  Leggere i dati da più file e sottocartelle	Lettura e Lista
  Usare Create External Table as Select (CETAS)	Lettura, creazione e scrittura

Autorizzazioni

Richiede CONTROL autorizzazione per il database in Istanza gestita di SQL di Azure.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Esempi

Per altri esempi, vedere Virtualizzazione dei dati con Istanza gestita di SQL di Azure.

R. Eseguire query su dati esterni da Istanza gestita di SQL di Azure con OPENROWSET o una tabella esterna

Per altri esempi, vedere CREATE EXTERNAL DATA SOURCE o vedere Virtualizzazione dei dati con Istanza gestita di SQL di Azure.

1. Creare la chiave master del database, se non esiste.

   -- Optional: Create MASTER KEY if it doesn't exist in the database:
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong Password>'
   GO
   

2. Creare le credenziali con ambito database usando un token di firma di accesso condiviso. È anche possibile usare un'identità gestita.

   CREATE DATABASE SCOPED CREDENTIAL MyCredential
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
   SECRET = '<KEY>' ; --Removing leading '?'
   GO
   

3. Creare l'origine dati esterna usando le credenziali.

   --Create external data source pointing to the file path, and referencing database-scoped credential:
   CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
   WITH (
       LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest',
       CREDENTIAL = [MyCredential]
   );
   

4. Eseguire query sul file di dati Parquet nell'origine dati esterna usando la sintassi T-SQL OPENROWSET, basandosi sull'inferenza dello schema per esplorare rapidamente i dati senza conoscere lo schema.

   --Query data with OPENROWSET, relying on schema inference.
   SELECT TOP 10 *
   FROM OPENROWSET (
       BULK 'bing_covid-19_data.parquet',
       DATA_SOURCE = 'MyExternalDataSource',
       FORMAT = 'parquet'
   ) AS filerows;
   

5. In alternativa, eseguire query sui dati usando OPENROWSET la clausola WITH, invece di basarsi sull'inferenza dello schema, che potrebbe eseguire query sui costi di esecuzione. In un file CSV, l'inferenza dello schema non è supportata.

   --Or, query data using the WITH clause on a CSV, where schema inference is not supported
   SELECT TOP 10 id,
                 updated,
                 confirmed,
                 confirmed_change
   FROM OPENROWSET (
       BULK 'bing_covid-19_data.csv',
       DATA_SOURCE = 'MyExternalDataSource',
       FORMAT = 'CSV', FIRSTROW = 2
   ) WITH (
       id INT,
       updated DATE,
       confirmed INT,
       confirmed_change INT
   ) AS filerows;
   

6. In alternativa, creare un EXTERNAL FILE FORMAT e un EXTERNAL TABLE per eseguire query sui dati come tabella locale.

   -- Or, create an EXTERNAL FILE FORMAT and an EXTERNAL TABLE
   --Create external file format
   CREATE EXTERNAL FILE FORMAT DemoFileFormat
   WITH (FORMAT_TYPE = PARQUET)
   GO
   
   --Create external table:
   CREATE EXTERNAL TABLE tbl_TaxiRides (
       vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
       tpepPickupDateTime DATETIME2,
       tpepDropoffDateTime DATETIME2,
       passengerCount INT,
       tripDistance FLOAT,
       puLocationId VARCHAR(8000),
       doLocationId VARCHAR(8000),
       startLon FLOAT,
       startLat FLOAT,
       endLon FLOAT,
       endLat FLOAT,
       rateCodeId SMALLINT,
       storeAndFwdFlag VARCHAR(8000),
       paymentType VARCHAR(8000),
       fareAmount FLOAT,
       extra FLOAT,
       mtaTax FLOAT,
       improvementSurcharge VARCHAR(8000),
       tipAmount FLOAT,
       tollsAmount FLOAT,
       totalAmount FLOAT
   )
   WITH (
       LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
       DATA_SOURCE = NYCTaxiExternalDataSource,
       FILE_FORMAT = MyFileFormat\.\./\.\./\.\./azure-sql/
   );
   GO
   
   --Then, query the data via an external table with T-SQL:
   SELECT TOP 10 *
   FROM tbl_TaxiRides;
   GO
   

Contenuti correlati

• Virtualizzazione Data con Istanza gestita di SQL di Azure
• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso

Panoramica: Microsoft Fabric Data Warehouse

Applica a: Fabric Data Warehouse

Crea un'origine dati esterna.

convenzioni di sintassi Transact-SQL

Sintassi

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( LOCATION = '<prefix>://<path>[:<port>]' )
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

Il motore di database non verifica l'esistenza dell'origine dati esterna quando viene creato l'oggetto.

Non aggiungere parametri finali /, nome file o firma di accesso condiviso alla fine dell'URL durante la configurazione di un'origine LOCATION dati esterna per le operazioni bulk.

Autorizzazioni

Se l'account di archiviazione di destinazione è privato, l'entità deve disporre anche dell'autorizzazione per i file di riferimento letti.

• Per Azure Data Lake Storage e Archiviazione BLOB di Azure, l'entità deve avere Storage BLOB Data Reader (o versione successiva) assegnata a livello di contenitore o account di archiviazione.
• Per Fabric One Lake Storage, l'entità deve disporre delle autorizzazioni "ReadAll".

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Esempi

R. Eseguire query su dati esterni con OPENROWSET o una tabella esterna

1. Creare l'origine dati esterna.

   --Create external data source pointing to the file path, and referencing database-scoped credential:
   CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
   WITH (
       LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest'
   );
   

2. Eseguire query sul file di dati Parquet nell'origine dati esterna usando la sintassi T-SQL OPENROWSET, basandosi sull'inferenza dello schema per esplorare rapidamente i dati senza conoscere lo schema.

   --Query data with OPENROWSET, relying on schema inference.
   SELECT TOP 10 *
   FROM OPENROWSET (
       BULK 'bing_covid-19_data.parquet',
       DATA_SOURCE = 'MyPrivateExternalDataSource'
   );
   

3. In alternativa, eseguire query sui dati usando OPENROWSET la clausola WITH, invece di basarsi sull'inferenza dello schema, che potrebbe eseguire query sui costi di esecuzione.

   --Or, query data using the WITH clause on a CSV, where schema inference is not supported
   SELECT TOP 10 id,
       updated,
       confirmed,
       confirmed_change
   FROM OPENROWSET (
       BULK 'bing_covid-19_data.csv', DATA_SOURCE = 'MyPrivateExternalDataSource'
       FIRSTROW = 2
   ) WITH (
       id INT,
       updated DATE,
       confirmed INT,
       confirmed_change INT
   ) AS filerows;
   

Contenuti correlati

• OPENROWSET BULK (Transact-SQL)
• sys.external_data_sources (Transact-SQL)
• Uso delle firme di accesso condiviso

Panoramica: Database SQL in Microsoft Fabric

Applies to: SQL database in Microsoft Fabric

Crea un'origine dati esterna per la virtualizzazione Data nel database SQL in Fabric.

convenzioni di sintassi Transact-SQL

Sintassi

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ ; ]

Argomenti

data_source_name

Specifica il nome definito dall'utente per l'origine dati. Il nome deve essere univoco all'interno del database.

LOCATION = '<prefix>://<path[:p ort]>'

Fornisce il protocollo di connettività e il percorso dell'origine dati esterna.

Fabric database SQL supporta solo OneLake (abfss) come origine dati.

Note aggiuntive e indicazioni utili per l'impostazione della posizione:

• Il motore di database non verifica l'esistenza dell'origine dati esterna quando viene creato l'oggetto. Per eseguire la convalida, creare una tabella esterna usando l'origine dati esterna.

Autorizzazioni

Richiede CONTROL autorizzazione per il database in database SQL di Azure.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL DATA SOURCE.

Esempi

R. Crea una fonte di dati esterna in una cartella di file Lakehouse

Questo esempio prevede il collegamento di una sorgente di dati esterna chiamata MyLakeHouse a una Lakehouse per accedere ai file Parquet e CSV caricati. Questi file in questo esempio si trovano all'interno della Files directory sotto la Contoso cartella.

Per creare un'origine dati Fabric Lakehouse, è necessario specificare l'ID dell'area di lavoro, il tenant e l'ID lakehouse. Per trovare il percorso del file ABFSS di una lakehouse, passare al portale di Fabric. Naviga fino alla tua Lakehouse, vai alla posizione della cartella desiderata, seleziona ..., Proprietà. Copia il percorso ABFS, che appare più o meno così: abfss://<WorkSpaceID>@<Tenant>.dfs.fabric.microsoft.com/<LakehouseID>/Files/Contoso.

Poiché Fabric database SQL supporta solo Microsoft Entra ID'autenticazione pass-through, non è necessario fornire credenziali con ambito database, la connessione userà sempre le credenziali di accesso dell'utente per accedere al percorso.

CREATE EXTERNAL DATA SOURCE MyLakeHouse 
WITH (
 LOCATION = 'abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso'
);

Contenuti correlati

• Virtualizzazione dei dati nel database SQL in Fabric
• CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• sys.external_data_sources (Transact-SQL)