CREATE EXTERNAL DATA SOURCE (Transact-SQL)
Crea un origen de datos externo para realizar consultas mediante SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, Analytics Platform System (PDW) o Azure SQL Edge.
En este artículo se proporciona la sintaxis, argumentos, comentarios, permisos y ejemplos para cualquier producto SQL que elija.
Selección de un producto
En la fila siguiente, seleccione el nombre del producto que le interese; de esta manera, solo se mostrará la información de ese producto.
* SQL Server *
Información general (SQL Server 2016)
Se aplica a: SQL Server 2016 (13.x) y versiones posteriores
Crea un origen de datos externo para consultas de PolyBase. Los orígenes de datos externos se usan para establecer la conectividad y admiten estos casos de uso principales:
- Virtualización y carga de datos mediante PolyBase
- Operaciones de carga masiva con
BULK INSERT
oOPENROWSET
Nota
Esta sintaxis varía en las diferentes versiones de SQL Server. Use la lista desplegable del selector de versiones para elegir la versión adecuada.
Para ver las características de SQL Server 2019 (15.x), visite CREATE EXTERNAL DATA SOURCE.
Para ver las características de SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Sintaxis para SQL Server 2016
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = { HADOOP } ]
[ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]
Argumentos
data_source_name
Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único en la base de datos en SQL Server.
LOCATION = '<prefix>://<path[:port]>'
Proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo.
Origen de datos externo | Prefijo de ubicación del conector | Ruta de acceso de ubicación | Ubicaciones admitidas por producto o servicio | Authentication |
---|---|---|---|---|
Cloudera CDH o Hortonworks HDP | hdfs |
<Namenode>[:port] |
SQL Server 2016 (13.x) a SQL Server 2019 (15.x) | Autenticación anónima o básica |
Cuenta de Azure Storage (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
A partir de SQL Server 2016 (13.x) No se admite el espacio de nombres jerárquico |
Clave de cuenta de Azure Storage |
Ruta de acceso de ubicación:
<Namenode>
: nombre de equipo, nombre de URI de servicio o dirección IP deNamenode
en el clúster de Hadoop. PolyBase debe resolver los nombres DNS utilizados por el clúster de Hadoop.port
= el puerto en el cual escucha el origen de datos externo. En Hadoop, el puerto se puede encontrar mediante el parámetro de configuraciónfs.defaultFS
. El valor predeterminado es 8020.<container>
= el contenedor de la cuenta de almacenamiento que contiene los datos. Los contenedores raíz son de solo lectura, no se pueden volver a escribir datos en el contenedor.<storage_account>
= nombre de la cuenta de almacenamiento del recurso de Azure.<server_name>
= el nombre de host.<instance_name>
= el nombre de la instancia con nombre de SQL Server. Se usa si tiene el servicio de SQL Server Browser en ejecución en la instancia de destino.
Instrucciones y notas adicionales cuando se establece la ubicación:
- El motor de base de datos de SQL Server no comprueba la existencia del origen de datos externo cuando se crea el objeto. Para la validación, crea una tabla externa utilizando el origen de datos externo.
- Para garantizar una semántica de consulta coherente, use el mismo origen de datos externo para todas las tablas cuando realice consultas a Hadoop.
- El uso de
wasbs
es opcional, pero se recomienda en SQL Server 2016 (13.x) para acceder a las cuentas de Azure Storage, ya que los datos se enviarán mediante una conexión TLS/SSL segura. - Para garantizar que las consultas de PolyBase son correctas durante una conmutación por error del
Namenode
de Hadoop, considere la posibilidad de usar una dirección IP virtual para elNamenode
del clúster de Hadoop. Si no, ejecute un comando ALTER EXTERNAL DATA SOURCE para que apunte a la nueva ubicación.
CREDENTIAL = credential_name
Especifica una credencial de ámbito de base de datos para la autenticación en el origen de datos externo.
CREDENTIAL
solo se necesita si se han protegido los datos. CREDENTIAL
no es necesario para los conjuntos de datos que permiten el acceso anónimo.
Para crear una credencial con ámbito de base de datos, vea CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
TYPE = [ HADOOP ]
Especifica el tipo de origen de datos externo que se está configurando. En SQL Server 2016, este parámetro siempre es necesario y solo debe especificarse como HADOOP
. Admite conexiones a Cloudera CDH, Hortonworks HDP o a una cuenta de Azure Storage. El comportamiento de este parámetro es diferente en versiones posteriores de SQL Server.
Para obtener un ejemplo de uso TYPE
= HADOOP
para cargar datos desde una cuenta de Azure Storage, consulte Creación de un origen de datos externo para acceder a los datos en Azure Storage mediante la interfaz de wasb://
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
Configure este valor opcional solo al conectarse a Cloudera CDH, Hortonworks HDP o una cuenta de Azure Storage. Para obtener una lista completa de las versiones de Hadoop compatibles, vea Configuración de conectividad de PolyBase (Transact-SQL).
Cuando RESOURCE_MANAGER_LOCATION
está definido, el optimizador de consultas tomará una decisión basada en el costo para mejorar el rendimiento. Se puede usar un trabajo de MapReduce para insertar el cálculo en Hadoop. La especificación de RESOURCE_MANAGER_LOCATION
puede reducir significativamente el volumen de datos transferidos entre Hadoop y SQL Server, lo cual puede suponer una mejora en el rendimiento de las consultas.
Si no se especifica el administrador de recursos, se deshabilita la inserción de cálculo en Hadoop para las consultas de PolyBase. En Creación de un origen de datos externo para hacer referencia a Hadoop con la inserción habilitada se proporciona un ejemplo concreto y más instrucciones.
El valor RESOURCE_MANAGER_LOCATION no se valida cuando se crea el origen de datos externo. Escribir un valor incorrecto puede provocar un error de consulta en tiempo de ejecución cada vez que se intente la inserción, ya que el valor proporcionado no podrá realizar la resolución.
Para que PolyBase funcione correctamente con un origen de datos externo de Hadoop, los puertos de los siguientes componentes del clúster de Hadoop deben estar abiertos:
- Puertos HDFS
- NameNode
- DataNode
- Administrador de recursos
- Envío de trabajo
- Historial de trabajos
Si no se especifica el puerto, el valor predeterminado se elige mediante el valor actual de la configuración "hadoop connectivity".
Conectividad de Hadoop | Puerto predeterminado del administrador de recursos |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
En la tabla siguiente se muestran los puertos predeterminados para estos componentes. Hay dependencias de la versión de Hadoop, así como la posibilidad de una configuración personalizada que no use la asignación de puerto predeterminada.
Componente de clúster de Hadoop | Puerto predeterminado |
---|---|
NameNode | 8020 |
DataNode (transferencia de datos, puerto IPC sin privilegios) | 50010 |
DataNode (transferencia de datos, puerto IPC con privilegios) | 1019 |
Envío de trabajos de Resource Manager (Hortonworks 1.3) | 50300 |
Envío de trabajos de Resource Manager (Cloudera 4.3) | 8021 |
Envío de trabajos de Resource Manager (Hortonworks 2.0 en Windows, Cloudera 5.x en Linux) | 8032 |
Envío de trabajos de Resource Manager (Hortonworks 2.x, 3.0 en Linux, Hortonworks 2.1-3 en Windows) | 8050 |
Historial de trabajos de Resource Manager | 10020 |
Permisos
Requiere el permiso CONTROL
en la base de datos en SQL Server.
Bloqueo
Toma un bloqueo compartido en el objeto EXTERNAL DATA SOURCE
.
Seguridad
PolyBase es compatible con la autenticación basada en proxy para la mayoría de orígenes de datos externos. Cree una credencial con ámbito de base de datos para crear la cuenta de proxy.
Ejemplos
Importante
Para obtener información sobre cómo instalar y habilitar PolyBase, vea Instalación de PolyBase en Windows
A. Creación de un origen de datos externo para hacer referencia a Hadoop
Si quiere crear un origen de datos externo para hacer referencia al clúster de Hadoop de Hortonworks HDP o Cloudera CDH, especifique el nombre de equipo o la dirección IP de Namenode
y puerto de Hadoop.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. Creación de un origen de datos externo para hacer referencia a Hadoop con la inserción habilitada
Especifique la opción RESOURCE_MANAGER_LOCATION
para habilitar la inserción de cálculo en Hadoop para las consultas de PolyBase. Una vez habilitado, PolyBase toma una decisión basada en el coste para determinar si se debe aplicar el cálculo de la consulta en Hadoop.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8020',
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
C. Creación de un origen de datos externo para hacer referencia a Hadoop con protección de Kerberos
Para comprobar si el clúster de Hadoop está protegido con Kerberos, compruebe el valor de la propiedad hadoop.security.authentication
en core-site.xml de Hadoop. Para hacer referencia a un clúster de Hadoop protegido con Kerberos, debe especificar una credencial con ámbito de base de datos que contenga el nombre de usuario y la contraseña de Kerberos. La clave maestra de base de datos se usa para cifrar el secreto de la credencial de ámbito de base de datos.
-- 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 (
LOCATION = 'hdfs://10.10.10.10:8050',
CREDENTIAL = HadoopUser1,
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
D. Creación de un origen de datos externo para acceder a los datos de Azure Storage mediante la interfaz wasb://
En este ejemplo, el origen de datos externo es una cuenta de Azure Storage (V2) denominada logs
. El contenedor de almacenamiento se denomina daily
. El origen de datos externo de Azure Storage es solo para la transferencia de datos. No admite la inserción de predicado. No se admiten espacios de nombres jerárquicos al acceder a los datos mediante la interfaz de wasb://
.
En este ejemplo se muestra cómo crear la credencial de ámbito de base de datos para la autenticación en una cuenta de Azure Storage (V2). Especifique la clave de la cuenta de Azure Storage en el secreto de la credencial de base de datos. Puede especificar cualquier cadena en la identidad de la credencial con ámbito de base de datos, ya que no se usará para la autenticación en Azure Storage. Tenga en cuenta que al conectarse a la instancia de Azure Storage mediante el conector WASB,la autenticación debe realizarse con una clave de cuenta de almacenamiento, no con una firma de acceso compartido (SAS).
En SQL Server 2016 (13.x), TYPE
se debe establecer en HADOOP
incluso al acceder a Azure Storage.
-- 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 (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
Pasos siguientes
Información general (SQL Server 2017)
Se aplica a: SOLO SQL Server 2017 (14.x)
Crea un origen de datos externo para consultas de PolyBase. Los orígenes de datos externos se usan para establecer la conectividad y admiten estos casos de uso principales:
- Virtualización y carga de datos mediante PolyBase
- Operaciones de carga masiva con
BULK INSERT
oOPENROWSET
Nota
Esta sintaxis varía en las diferentes versiones de SQL Server en Linux. Use la lista desplegable del selector de versiones para elegir la versión adecuada.
Para ver las características de SQL Server 2019 (15.x), visite CREATE EXTERNAL DATA SOURCE.
Para ver las características de SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Nota
Esta sintaxis varía en las diferentes versiones de SQL Server. Use la lista desplegable del selector de versiones para elegir la versión adecuada.
Para ver las características de SQL Server 2019 (15.x), visite CREATE EXTERNAL DATA SOURCE.
Para ver las características de SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Sintaxis para SQL Server 2017
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.
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>]' )
[ ; ]
Argumentos
data_source_name
Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único en la base de datos en SQL Server.
LOCATION = '<prefix>://<path[:port]>'
Proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo.
Origen de datos externo | Prefijo de ubicación del conector | Ruta de acceso de ubicación | Ubicaciones admitidas por producto o servicio | Authentication |
---|---|---|---|---|
Cloudera CDH o Hortonworks HDP | hdfs |
<Namenode>[:port] |
SQL Server 2016 (13.x) solo a SQL Server 2019 (15.x) | Autenticación anónima o básica |
Cuenta de Azure Storage (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
A partir de SQL Server 2016 (13.x) No se admite el espacio de nombres jerárquico |
Clave de cuenta de Azure Storage |
Operaciones masivas | https |
<storage_account>.blob.core.windows.net/<container> |
A partir de SQL Server 2017 (14.x) | Firma de acceso compartido (SAS) |
Ruta de acceso de ubicación:
<
NameNode>
: nombre de equipo, nombre de URI de servicio o dirección IP delNamenode
en el clúster de Hadoop. PolyBase debe resolver los nombres DNS utilizados por el clúster de Hadoop.port
= el puerto en el cual escucha el origen de datos externo. En Hadoop, el puerto se puede encontrar mediante el parámetro de configuraciónfs.defaultFS
. El valor predeterminado es 8020.<container>
= el contenedor de la cuenta de almacenamiento que contiene los datos. Los contenedores raíz son de solo lectura, no se pueden volver a escribir datos en el contenedor.<storage_account>
= nombre de la cuenta de almacenamiento del recurso de Azure.<server_name>
= el nombre de host.<instance_name>
= el nombre de la instancia con nombre de SQL Server. Se usa si tiene el servicio de SQL Server Browser en ejecución en la instancia de destino.
Instrucciones y notas adicionales cuando se establece la ubicación:
- El motor de base de datos de SQL Server no comprueba la existencia del origen de datos externo cuando se crea el objeto. Para la validación, crea una tabla externa utilizando el origen de datos externo.
- Para garantizar una semántica de consulta coherente, use el mismo origen de datos externo para todas las tablas cuando realice consultas a Hadoop.
- Especifique
Driver={<Name of Driver>}
al conectarse a través deODBC
. - El uso de
wasbs
es opcional, pero se recomienda en SQL Server 2017 (14.x) para acceder a las cuentas de Azure Storage, ya que los datos se enviarán mediante una conexión TLS/SSL segura. - Para garantizar que las consultas de PolyBase son correctas durante una conmutación por error del
Namenode
de Hadoop, considere la posibilidad de usar una dirección IP virtual para elNamenode
del clúster de Hadoop. Si no, ejecute un comando ALTER EXTERNAL DATA SOURCE para que apunte a la nueva ubicación.
CREDENTIAL = credential_name
Especifica una credencial de ámbito de base de datos para la autenticación en el origen de datos externo.
Instrucciones y notas adicionales cuando se crea una credencial:
CREDENTIAL
solo se necesita si se han protegido los datos.CREDENTIAL
no es necesario para los conjuntos de datos que permiten el acceso anónimo.- Cuando
TYPE
=BLOB_STORAGE
, la credencial debe crearse mediante el uso deSHARED ACCESS SIGNATURE
como identidad. TYPE
=BLOB_STORAGE
solo se permite para operaciones masivas; no se pueden crear tablas externas para un origen de datos externo conTYPE
=BLOB_STORAGE
.- Tenga en cuenta que al conectarse a la instancia de Azure Storage mediante el conector WASB,la autenticación debe realizarse con una clave de cuenta de almacenamiento, no con una firma de acceso compartido (SAS).
- Cuando
TYPE
=HADOOP
, la credencial debe crearse con la clave de la cuenta de almacenamiento comoSECRET
.
Hay varias maneras de crear una firma de acceso compartido:
Puede crear un token de SAS; para ello, vaya a Azure Portal ><Su_cuenta_de_almacenamiento> -> Firma de acceso compartido -> Configurar permisos -> Generar la cadena de conexión y SAS. Para más información, consulte Generación de una firma de acceso compartido.
Puede crear y configurar una SAS con Explorador de Azure Storage.
Puede crear una SAS mediante programación a través de PowerShell, la CLI de Azure, .NET y la API de REST. Para obtener más información, consulte Otorgar acceso limitado a recursos de Azure Storage con firmas de acceso compartido (SAS).
El token de SAS debe configurarse del modo siguiente:
- Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
?
inicial cuando se configura como SECRET. - Usar un período de caducidad válido (todas las fechas se encuentran en hora UTC).
- Conceder al menos permiso de lectura en el archivo que se debe cargar (por ejemplo,
srt=o&sp=r
). Se pueden crear varias firmas de acceso compartido para diferentes casos de uso. Los permisos se deben conceder de la siguiente manera:
Acción Permiso Leer datos desde un archivo Lectura Leer datos de varios archivos y subcarpetas Lectura y enumeración - Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
Para obtener un ejemplo del uso de CREDENTIAL
con SHARED ACCESS SIGNATURE
y TYPE
= BLOB_STORAGE
, vea Creación de un origen de datos externo para ejecutar operaciones masivas y recuperar datos desde Azure Storage en SQL Database.
Para crear una credencial con ámbito de base de datos, vea CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
TYPE = [ HADOOP | BLOB_STORAGE ]
Especifica el tipo de origen de datos externo que se está configurando. Este parámetro no siempre es necesario y solo se debe especificar al conectarse a Cloudera CDH, Hortonworks HDP, una cuenta de Azure Storage o una instancia de Azure Data Lake Storage Gen2.
- Use
HADOOP
cuando el origen de datos externo sea Cloudera CDH, Hortonworks HDP, una cuenta de Azure Storage o una instancia de Azure Data Lake Storage Gen2. - Use
BLOB_STORAGE
al ejecutar operaciones masivas desde una cuenta de Azure Storage mediante BULK INSERT u OPENROWSET. Opción introducida con SQL Server 2017 (14.x). Se usaHADOOP
cuando se pretende crear una tabla externa en Azure Storage.
Nota
TYPE
se debe establecer en HADOOP
incluso al acceder a Azure Storage.
Para obtener un ejemplo de uso TYPE
= HADOOP
para cargar datos desde una cuenta de Azure Storage, consulte Creación de un origen de datos externo para acceder a los datos en Azure Storage mediante la interfaz de wasb://
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
Configure este valor opcional solo al conectarse a Cloudera CDH, Hortonworks HDP o una cuenta de Azure Storage. Para obtener una lista completa de las versiones de Hadoop compatibles, vea Configuración de conectividad de PolyBase (Transact-SQL).
Cuando RESOURCE_MANAGER_LOCATION
está definido, el optimizador de consultas toma una decisión basada en el coste para mejorar el rendimiento. Se puede usar un trabajo de MapReduce para insertar el cálculo en Hadoop. La especificación de RESOURCE_MANAGER_LOCATION
puede reducir significativamente el volumen de datos transferidos entre Hadoop y SQL Server, lo cual puede suponer una mejora en el rendimiento de las consultas.
Si no se especifica el administrador de recursos, se deshabilita la inserción de cálculo en Hadoop para las consultas de PolyBase. En Creación de un origen de datos externo para hacer referencia a Hadoop con la inserción habilitada se proporciona un ejemplo concreto y más instrucciones.
El valor RESOURCE_MANAGER_LOCATION no se valida cuando se crea el origen de datos externo. Escribir un valor incorrecto puede provocar un error de consulta en tiempo de ejecución cada vez que se intente la inserción, ya que el valor proporcionado no podrá realizar la resolución.
Para que PolyBase funcione correctamente con un origen de datos externo de Hadoop, los puertos de los siguientes componentes del clúster de Hadoop deben estar abiertos:
- Puertos HDFS
- NameNode
- DataNode
- Administrador de recursos
- Envío de trabajo
- Historial de trabajos
Si no se especifica el puerto, el valor predeterminado se elige mediante el valor actual de la configuración "hadoop connectivity".
Conectividad de Hadoop | Puerto predeterminado del administrador de recursos |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
En la tabla siguiente se muestran los puertos predeterminados para estos componentes. Tenga en cuenta que hay dependencias de la versión de Hadoop, así como la posibilidad de una configuración personalizada que no use la asignación de puerto predeterminada.
Componente de clúster de Hadoop | Puerto predeterminado |
---|---|
NameNode | 8020 |
DataNode (transferencia de datos, puerto IPC sin privilegios) | 50010 |
DataNode (transferencia de datos, puerto IPC con privilegios) | 1019 |
Envío de trabajos de Resource Manager (Hortonworks 1.3) | 50300 |
Envío de trabajos de Resource Manager (Cloudera 4.3) | 8021 |
Envío de trabajos de Resource Manager (Hortonworks 2.0 en Windows, Cloudera 5.x en Linux) | 8032 |
Envío de trabajos de Resource Manager (Hortonworks 2.x, 3.0 en Linux, Hortonworks 2.1-3 en Windows) | 8050 |
Historial de trabajos de Resource Manager | 10020 |
Permisos
Requiere el permiso CONTROL
en la base de datos en SQL Server.
Bloqueo
Toma un bloqueo compartido en el objeto EXTERNAL DATA SOURCE
.
Seguridad
PolyBase es compatible con la autenticación basada en proxy para la mayoría de orígenes de datos externos. Cree una credencial con ámbito de base de datos para crear la cuenta de proxy.
No se admite un token de SAS con el tipo HADOOP
. Solo se admite con el tipo = BLOB_STORAGE
cuando se usa en su lugar una clave de acceso de la cuenta de almacenamiento. Si se intenta crear un origen de datos externo con el tipo HADOOP
y una credencial SAS, aparece un error como el siguiente:
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 are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'
Ejemplos
Importante
Para obtener información sobre cómo instalar y habilitar PolyBase, vea Instalación de PolyBase en Windows
A. Creación de un origen de datos externo para hacer referencia a Hadoop
Si quiere crear un origen de datos externo para hacer referencia al clúster de Hadoop de Hortonworks HDP o Cloudera CDH, especifique el nombre de equipo o la dirección IP de Namenode
y puerto de Hadoop.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. Creación de un origen de datos externo para hacer referencia a Hadoop con la inserción habilitada
Especifique la opción RESOURCE_MANAGER_LOCATION
para habilitar la inserción de cálculo en Hadoop para las consultas de PolyBase. Una vez habilitado, PolyBase toma una decisión basada en el coste para determinar si se debe aplicar el cálculo de la consulta en Hadoop.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8020',
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
C. Creación de un origen de datos externo para hacer referencia a Hadoop con protección de Kerberos
Para comprobar si el clúster de Hadoop está protegido con Kerberos, compruebe el valor de la propiedad hadoop.security.authentication
en core-site.xml de Hadoop. Para hacer referencia a un clúster de Hadoop protegido con Kerberos, debe especificar una credencial con ámbito de base de datos que contenga el nombre de usuario y la contraseña de Kerberos. La clave maestra de base de datos se usa para cifrar el secreto de la credencial de ámbito de base de datos.
-- 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 (
LOCATION = 'hdfs://10.10.10.10:8050',
CREDENTIAL = HadoopUser1,
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
D. Creación de un origen de datos externo para acceder a los datos de Azure Storage mediante la interfaz wasb://
En este ejemplo, el origen de datos externo es una cuenta de Azure Storage (V2) denominada logs
. El contenedor de almacenamiento se denomina daily
. El origen de datos externo de Azure Storage es solo para la transferencia de datos. No admite la inserción de predicado. No se admiten espacios de nombres jerárquicos al acceder a los datos mediante la interfaz de wasb://
. Tenga en cuenta que al conectarse a la instancia de Azure Storage mediante el conector WASB,la autenticación debe realizarse con una clave de cuenta de almacenamiento, no con una firma de acceso compartido (SAS).
En este ejemplo se muestra cómo crear la credencial de ámbito de base de datos para la autenticación en una cuenta de Azure Storage (V2). Especifique la clave de la cuenta de Azure Storage en el secreto de la credencial de base de datos. Puede especificar cualquier cadena en la identidad de la credencial con ámbito de base de datos, ya que no se usará para la autenticación en Azure Storage.
-- 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 (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
Ejemplos: operaciones masivas
Importante
No coloque un / final, nombre de archivo o parámetros de firma de acceso compartido al final de la dirección URL de LOCATION
al configurar un origen de datos externo para las operaciones masivas.
E. Creación de un origen de datos externo para operaciones masivas de recuperación de datos desde Azure Storage
Se aplica a: SQL Server 2017 (14.x) y versiones posteriores.
Use el origen de datos siguiente para las operaciones masivas con BULK INSERT o OPENROWSET. La credencial debe establecer SHARED ACCESS SIGNATURE
como identidad, no debe tener al inicio ?
en el token de SAS, debe tener al menos permiso de lectura en el archivo que se debe cargar (por ejemplo srt=o&sp=r
), y el período de expiración debe ser válido (todas las fechas se expresan en hora UTC). Para más información sobre las firmas de acceso compartido, vea Uso de Firmas de acceso compartido (SAS).
CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
-- Remove ? from the beginning of the SAS token
SECRET = '<azure_storage_account_key>';
CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
CREDENTIAL = AccessAzureInvoices,
TYPE = BLOB_STORAGE
);
Para ver este ejemplo en uso, vea el ejemplo BULK INSERT.
Pasos siguientes
Información general (SQL Server 2019)
Se aplica a: SQL Server 2019 (15.x) y versiones posteriores
Crea un origen de datos externo para consultas de PolyBase. Los orígenes de datos externos se usan para establecer la conectividad y admiten estos casos de uso principales:
- Virtualización y carga de datos mediante PolyBase
- Operaciones de carga masiva con
BULK INSERT
oOPENROWSET
Nota
Esta sintaxis varía en las diferentes versiones de SQL Server. Use la lista desplegable del selector de versiones para elegir la versión adecuada.
Para ver las características de SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Nota
Esta sintaxis varía en las diferentes versiones de SQL Server. Use la lista desplegable del selector de versiones para elegir la versión adecuada.
Para ver las características de SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Sintaxis para SQL Server 2019
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de 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 } ]
[ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
[ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]
Argumentos
data_source_name
Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único en la base de datos en SQL Server.
LOCATION = '<prefix>://<path[:port]>'
Proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo.
Origen de datos externo | Prefijo de ubicación del conector | Ruta de acceso de ubicación | Ubicaciones admitidas por producto o servicio | Authentication |
---|---|---|---|---|
Cloudera CDH o Hortonworks HDP | hdfs |
<Namenode>[:port] |
SQL Server 2016 (13.x) a SQL Server 2019 (15.x) | Autenticación anónima o básica |
Cuenta de Azure Storage (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
A partir de SQL Server 2016 (13.x) No se admite el espacio de nombres jerárquico |
Clave de cuenta de Azure Storage |
SQL Server | sqlserver |
<server_name>[\<instance_name>][:port] |
A partir de SQL Server 2019 (15.x) | Solo autenticación SQL |
Oracle | oracle |
<server_name>[:port] |
A partir de SQL Server 2019 (15.x) | Solo autenticación básica |
Teradata | teradata |
<server_name>[:port] |
A partir de SQL Server 2019 (15.x) | Solo autenticación básica |
MongoDB o API de Cosmos DB para MongoDB | mongodb |
<server_name>[:port] |
A partir de SQL Server 2019 (15.x) | Solo autenticación básica |
ODBC genérico | odbc |
<server_name>[:port] |
A partir de SQL Server 2019 (15.x): solo Windows | Solo autenticación básica |
Operaciones masivas | https |
<storage_account>.blob.core.windows.net/<container> |
A partir de SQL Server 2017 (14.x) | Firma de acceso compartido (SAS) |
Azure Data Lake Storage Gen2 | abfs[s] |
abfss://<container>@<storage _account>.dfs.core.windows.net |
A partir de SQL Server 2019 (15.x) CU11+. | Clave de acceso de almacenamiento |
Grupo de datos de Clústeres de macrodatos de SQL Server | sqldatapool |
sqldatapool://controller-svc/default |
Solo se admite en Clústeres de macrodatos de SQL Server 2019 | Solo autenticación básica |
Bloque de almacenamiento en Clústeres de macrodatos de SQL Server | sqlhdfs |
sqlhdfs://controller-svc/default |
Solo se admite en Clústeres de macrodatos de SQL Server 2019 | Solo autenticación básica |
Ruta de acceso de ubicación:
<Namenode>
: nombre de equipo, nombre de URI de servicio o dirección IP deNamenode
en el clúster de Hadoop. PolyBase debe resolver los nombres DNS utilizados por el clúster de Hadoop.port
= el puerto en el cual escucha el origen de datos externo. En Hadoop, el puerto se puede encontrar mediante el parámetro de configuraciónfs.defaultFS
. El valor predeterminado es 8020.<container>
= el contenedor de la cuenta de almacenamiento que contiene los datos. Los contenedores raíz son de solo lectura, no se pueden volver a escribir datos en el contenedor.<storage_account>
= nombre de la cuenta de almacenamiento del recurso de Azure.<server_name>
= el nombre de host.<instance_name>
= el nombre de la instancia con nombre de SQL Server. Se usa si tiene el servicio de SQL Server Browser en ejecución en la instancia de destino.
Instrucciones y notas adicionales cuando se establece la ubicación:
- El motor de base de datos de SQL Server no comprueba la existencia del origen de datos externo cuando se crea el objeto. Para la validación, crea una tabla externa utilizando el origen de datos externo.
- Para garantizar una semántica de consulta coherente, use el mismo origen de datos externo para todas las tablas cuando realice consultas a Hadoop.
- Puede usar el conector
sqlserver
para conectar SQL Server 2019 (15.x) a otro SQL Server o a Azure SQL Database. - Especifique
Driver={<Name of Driver>}
al conectarse a través deODBC
. - El uso de
wasbs
oabfss
es opcional, pero se recomienda en SQL Server 2019 (15.x) para acceder a las cuentas de Azure Storage, ya que los datos se enviarán mediante una conexión TLS/SSL segura. - Las API
abfs
oabfss
se admiten al acceder a las cuentas de Azure Storage a partir de SQL Server 2019 (15.x) CU11. Para obtener más información, vea Controlador Azure Blob File System (ABFS). - La opción Espacio de nombres jerárquico para cuentas de Azure Storage (V2) mediante
abfs[s]
se admite a través de Azure Data Lake Storage Gen2 a partir de SQL Server 2019 (15.x) CU11+. De lo contrario, no se admite la opción Espacio de nombres jerárquico y debe permanecer deshabilitada. - Para garantizar que las consultas de PolyBase son correctas durante una conmutación por error del
Namenode
de Hadoop, considere la posibilidad de usar una dirección IP virtual para elNamenode
del clúster de Hadoop. Si no, ejecute un comando ALTER EXTERNAL DATA SOURCE para que apunte a la nueva ubicación. - Se admiten los tipos
sqlhdfs
ysqldatapool
para conectarse entre la instancia maestra y el grupo de almacenamiento de un clúster de macrodatos. Usehdfs
para Cloudera CDH o Hortonworks HDP. Para obtener más información sobre el uso desqlhdfs
para consultar grupos de almacenamiento de clústeres de macrodatos de SQL Server, consulte Tutorial: consultar HDFS en un clúster de macrodatos de SQL Server 2019. - La compatibilidad de SQL Server con los orígenes de datos externos HDFS Cloudera (CDP) y Hortonworks (HDP) se retirará y no se incluirá en SQL Server 2022 (16.x). Para obtener más información, consulte Opciones de macrodatos en la plataforma Microsoft SQL Server.
CONNECTION_OPTIONS = key_value_pair
Se especifica para SQL Server 2019 (15.x) y versiones posteriores. Especifica opciones adicionales al conectarse a través de ODBC
a un origen de datos externo. Para usar varias opciones de conexión, sepárelas mediante punto y coma.
Se aplica a las conexiones ODBC
genéricas, así como a los conectores ODBC
integrados para SQL Server, Oracle, Teradata, MongoDB y la API de Azure Cosmos DB para MongoDB.
key_value_pair
es la palabra clave y el valor de una opción de conexión específica. Las palabras clave y los valores disponibles dependen del tipo de origen de datos externo. Se requiere como mínimo el nombre del controlador, pero existen otras opciones, como APP='<your_application_name>'
o ApplicationIntent= ReadOnly|ReadWrite
, que también resulta útil establecerlas y pueden ayudarle con la solución de problemas.
Los posibles pares clave-valor son específicos del proveedor de origen de datos externo. Para más información sobre cada proveedor, consulte CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.
A partir de la actualización acumulativa 19 de SQL Server 2019 (15.x), se han introducido palabras clave adicionales para admitir archivos TNS de Oracle:
- La palabra clave
TNSNamesFile
especifica la ruta de acceso al archivotnsnames.ora
ubicado en el servidor de Oracle. - La palabra clave
ServerName
especifica el alias usado dentro deltnsnames.ora
que se usará para reemplazar el nombre de host y el puerto.
Delegación = ON | OFF
Se especifica solo para SQL Server 2019 (15.x). Indica si se puede insertar cálculo en el origen de datos externo. El valor predeterminado es ON.
PUSHDOWN
se admite al conectarse a SQL Server, Oracle, Teradata, MongoDB, la API Azure Cosmos DB para MongoDB u ODBC en el nivel de origen de datos externo.
Habilitar o deshabilitar la inserción en el nivel de consulta se logra a través de una sugerencia.
CREDENTIAL = credential_name
Especifica una credencial de ámbito de base de datos para la autenticación en el origen de datos externo.
Instrucciones y notas adicionales cuando se crea una credencial:
CREDENTIAL
solo se necesita si se han protegido los datos.CREDENTIAL
no es necesario para los conjuntos de datos que permiten el acceso anónimo.- Cuando
TYPE
=BLOB_STORAGE
, la credencial debe crearse mediante el uso deSHARED ACCESS SIGNATURE
como identidad.TYPE
=BLOB_STORAGE
solo se permite para operaciones masivas; no se pueden crear tablas externas para un origen de datos externo conTYPE
=BLOB_STORAGE
.
Hay varias maneras de crear una firma de acceso compartido:
Puede crear un token de SAS; para ello, vaya a Azure Portal ><Su_cuenta_de_almacenamiento> -> Firma de acceso compartido -> Configurar permisos -> Generar la cadena de conexión y SAS. Para más información, consulte Generación de una firma de acceso compartido.
Puede crear y configurar una SAS con Explorador de Azure Storage.
Puede crear una SAS mediante programación a través de PowerShell, la CLI de Azure, .NET y la API de REST. Para obtener más información, consulte Otorgar acceso limitado a recursos de Azure Storage con firmas de acceso compartido (SAS).
El token de SAS debe configurarse del modo siguiente:
- Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
?
inicial cuando se configura como SECRET. - Usar un período de caducidad válido (todas las fechas se encuentran en hora UTC).
- Conceder al menos permiso de lectura en el archivo que se debe cargar (por ejemplo,
srt=o&sp=r
). Se pueden crear varias firmas de acceso compartido para diferentes casos de uso. Los permisos se deben conceder de la siguiente manera:
Acción Permiso Leer datos desde un archivo Lectura Leer datos de varios archivos y subcarpetas Lectura y enumeración - Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
Para obtener un ejemplo del uso de CREDENTIAL
con SHARED ACCESS SIGNATURE
y TYPE
= BLOB_STORAGE
, vea Creación de un origen de datos externo para ejecutar operaciones masivas y recuperar datos desde Azure Storage en SQL Database.
Para crear una credencial con ámbito de base de datos, vea CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
TYPE = [ HADOOP | BLOB_STORAGE ]
Especifica el tipo de origen de datos externo que se está configurando. Este parámetro no siempre es necesario y solo se debe especificar al conectarse a Cloudera CDH, Hortonworks HDP, una cuenta de Azure Storage o una instancia de Azure Data Lake Storage Gen2.
- En SQL Server 2019 (15.x), no especifique TYPE a menos que se conecte a Cloudera CDH, Hortonworks HDP o una cuenta de Azure Storage.
- Use
HADOOP
cuando el origen de datos externo sea Cloudera CDH, Hortonworks HDP, una cuenta de Azure Storage o una instancia de Azure Data Lake Storage Gen2. - Use
BLOB_STORAGE
al ejecutar operaciones masivas desde una cuenta de Azure Storage mediante BULK INSERT u OPENROWSET con SQL Server 2017 (14.x). Se usaHADOOP
cuando se pretende crear una tabla externa en Azure Storage. - La compatibilidad de SQL Server con los orígenes de datos externos HDFS Cloudera (CDP) y Hortonworks (HDP) se retirará y no se incluirá en SQL Server 2022 (16.x). Para obtener más información, consulte Opciones de macrodatos en la plataforma Microsoft SQL Server.
Para obtener un ejemplo de uso TYPE
= HADOOP
para cargar datos desde una cuenta de Azure Storage, consulte Creación de un origen de datos externo para acceder a los datos en Azure Storage mediante la interfaz de wasb://
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
En SQL Server 2019 (15.x), no especifique RESOURCE_MANAGER_LOCATION a menos que se conecte a Cloudera CDH, Hortonworks HDP o una cuenta de Azure Storage.
Configure este valor opcional solo al conectarse a Cloudera CDH, Hortonworks HDP o una cuenta de Azure Storage. Para obtener una lista completa de las versiones de Hadoop compatibles, vea Configuración de conectividad de PolyBase (Transact-SQL).
Cuando RESOURCE_MANAGER_LOCATION
está definido, el optimizador de consultas tomará una decisión basada en el costo para mejorar el rendimiento. Se puede usar un trabajo de MapReduce para insertar el cálculo en Hadoop. La especificación de RESOURCE_MANAGER_LOCATION
puede reducir significativamente el volumen de datos transferidos entre Hadoop y SQL Server, lo cual puede suponer una mejora en el rendimiento de las consultas.
Si no se especifica el administrador de recursos, se deshabilita la inserción de cálculo en Hadoop para las consultas de PolyBase. En Creación de un origen de datos externo para hacer referencia a Hadoop con la inserción habilitada se proporciona un ejemplo concreto y más instrucciones.
El valor RESOURCE_MANAGER_LOCATION no se valida cuando se crea el origen de datos externo. Escribir un valor incorrecto puede provocar un error de consulta en tiempo de ejecución cada vez que se intente la inserción, ya que el valor proporcionado no podrá realizar la resolución.
Para que PolyBase funcione correctamente con un origen de datos externo de Hadoop, los puertos de los siguientes componentes del clúster de Hadoop deben estar abiertos:
- Puertos HDFS
- NameNode
- DataNode
- Administrador de recursos
- Envío de trabajo
- Historial de trabajos
Si no se especifica el puerto, el valor predeterminado se elige mediante el valor actual de la configuración "hadoop connectivity".
Conectividad de Hadoop | Puerto predeterminado del administrador de recursos |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
En la tabla siguiente se muestran los puertos predeterminados para estos componentes. Tenga en cuenta que hay dependencias de la versión de Hadoop, así como la posibilidad de una configuración personalizada que no use la asignación de puerto predeterminada.
Componente de clúster de Hadoop | Puerto predeterminado |
---|---|
NameNode | 8020 |
DataNode (transferencia de datos, puerto IPC sin privilegios) | 50010 |
DataNode (transferencia de datos, puerto IPC con privilegios) | 1019 |
Envío de trabajos de Resource Manager (Hortonworks 1.3) | 50300 |
Envío de trabajos de Resource Manager (Cloudera 4.3) | 8021 |
Envío de trabajos de Resource Manager (Hortonworks 2.0 en Windows, Cloudera 5.x en Linux) | 8032 |
Envío de trabajos de Resource Manager (Hortonworks 2.x, 3.0 en Linux, Hortonworks 2.1-3 en Windows) | 8050 |
Historial de trabajos de Resource Manager | 10020 |
Permisos
Requiere el permiso CONTROL
en la base de datos en SQL Server.
Bloqueo
Toma un bloqueo compartido en el objeto EXTERNAL DATA SOURCE
.
Seguridad
PolyBase es compatible con la autenticación basada en proxy para la mayoría de orígenes de datos externos. Cree una credencial con ámbito de base de datos para crear la cuenta de proxy.
Cuando se conecta al almacenamiento o al grupo de datos de un clúster de macrodatos de SQL Server 2019, las credenciales del usuario se pasan al sistema de back-end. Cree inicios de sesión en el propio grupo de datos para habilitar la autenticación de paso a través.
No se admite un token de SAS con el tipo HADOOP
. Solo se admite con el tipo = BLOB_STORAGE
cuando se usa en su lugar una clave de acceso de la cuenta de almacenamiento. Si se intenta crear un origen de datos externo con el tipo HADOOP
y una credencial SAS, aparece un error como el siguiente:
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 are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'
Ejemplos
Importante
Para obtener información sobre cómo instalar y habilitar PolyBase, vea Instalación de PolyBase en Windows
A. Creación de un origen de datos externo en SQL Server 2019 para hacer referencia a Oracle
Para crear un origen de datos externo que haga referencia a Oracle, asegúrese de que tiene una credencial con ámbito de base de datos. También, opcionalmente, puede habilitar o deshabilitar la inserción de cálculo en este origen de datos.
-- 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',
CREDENTIAL = OracleProxyAccount,
PUSHDOWN = ON
);
Opcionalmente, el origen de datos externo a Oracle puede usar la autenticación de proxy para proporcionar un control de acceso más preciso. Un usuario de proxy se puede configurar para tener acceso limitado en comparación con el usuario que se va a suplantar.
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]
);
Como alternativa, puede usar la autenticación de TNS.
A partir de la actualización acumulativa 19 de SQL Server 2019 (15.x), CREATE EXTERNAL DATA SOURCE
admite ahora el uso de archivos TNS al conectarse a Oracle.
El parámetro CONNECTION_OPTIONS
se expandió y ahora usa TNSNamesFile
y ServerName
como variables para examinar el archivo tnsnames.ora
y establecer la conexión con el servidor.
En el ejemplo siguiente, durante el tiempo de ejecución, SQL Server buscará la ubicación del archivo tnsnames.ora
especificada por TNSNamesFile
y buscará el host y el puerto de red especificados por 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'
);
Para obtener ejemplos adicionales de otros orígenes de datos, como MongoDB, vea Configuración de PolyBase para acceder a datos externos en MongoDB.
B. Creación de un origen de datos externo para hacer referencia a Hadoop
Si quiere crear un origen de datos externo para hacer referencia al clúster de Hadoop de Hortonworks HDP o Cloudera CDH, especifique el nombre de equipo o la dirección IP de Namenode
y puerto de Hadoop.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
C. Creación de un origen de datos externo para hacer referencia a Hadoop con la inserción habilitada
Especifique la opción RESOURCE_MANAGER_LOCATION
para habilitar la inserción de cálculo en Hadoop para las consultas de PolyBase. Una vez habilitado, PolyBase toma una decisión basada en el coste para determinar si se debe aplicar el cálculo de la consulta en Hadoop.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8020',
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
D. Creación de un origen de datos externo para hacer referencia a Hadoop con protección de Kerberos
Para comprobar si el clúster de Hadoop está protegido con Kerberos, compruebe el valor de la propiedad hadoop.security.authentication
en core-site.xml de Hadoop. Para hacer referencia a un clúster de Hadoop protegido con Kerberos, debe especificar una credencial con ámbito de base de datos que contenga el nombre de usuario y la contraseña de Kerberos. La clave maestra de base de datos se usa para cifrar el secreto de la credencial de ámbito de base de datos.
-- 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 (
LOCATION = 'hdfs://10.10.10.10:8050',
CREDENTIAL = HadoopUser1,
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
E. Creación de un origen de datos externo para acceder a los datos de Azure Storage mediante la interfaz wasb://
En este ejemplo, el origen de datos externo es una cuenta de Azure Storage (V2) denominada logs
. El contenedor de almacenamiento se denomina daily
. El origen de datos externo de Azure Storage es solo para la transferencia de datos. No admite la inserción de predicado. No se admiten espacios de nombres jerárquicos al acceder a los datos mediante la interfaz de wasb://
. Tenga en cuenta que al conectarse a la instancia de Azure Storage mediante el conector WASB,la autenticación debe realizarse con una clave de cuenta de almacenamiento, no con una firma de acceso compartido (SAS).
En este ejemplo se muestra cómo crear la credencial de ámbito de base de datos para la autenticación en una cuenta de Azure Storage (V2). Especifique la clave de la cuenta de Azure Storage en el secreto de la credencial de base de datos. Puede especificar cualquier cadena en la identidad de la credencial con ámbito de base de datos, ya que no se usará para la autenticación en Azure Storage.
-- 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 (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
F. Creación de un origen de datos externo para hacer referencia a una instancia con nombre de SQL Server mediante la conectividad de PolyBase
Se aplica a: SQL Server 2019 (15.x) y versiones posteriores
Para crear un origen de datos externo que haga referencia a una instancia con nombre de SQL Server, use CONNECTION_OPTIONS
para especificar el nombre de la instancia.
En el ejemplo siguiente, WINSQL2019
es el nombre de host y SQL2019
el de la instancia. 'Server=%s\SQL2019'
es el par de clave-valor.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019',
CONNECTION_OPTIONS = 'Server=%s\SQL2019',
CREDENTIAL = SQLServerCredentials
);
Como alternativa, puede usar un puerto para conectarse a una instancia predeterminada de SQL Server.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019:58137',
CREDENTIAL = SQLServerCredentials
);
G. Creación de un origen de datos externo para hacer referencia a una réplica secundaria legible de un grupo de disponibilidad Always On
Se aplica a: SQL Server 2019 (15.x) y versiones posteriores
Para crear un origen de datos externo que haga referencia a una réplica secundaria legible de SQL Server, use CONNECTION_OPTIONS
para especificar el ApplicationIntent=ReadOnly
.
En primer lugar, cree la credencial con ámbito de base de datos y almacene las credenciales para un inicio de sesión autenticado de SQL. El conector ODBC de SQL para PolyBase solo admite la autenticación básica. Para poder crear una credencial con ámbito de base de datos, la base de datos debe tener una clave maestra para proteger la credencial. Para obtener más información, vea CREATE MASTER KEY. En el ejemplo siguiente se crea una credencial con ámbito de base de datos, y se le proporciona su propio inicio de sesión y contraseña.
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
A continuación, cree el nuevo origen de datos externo.
El parámetro Database
de ODBC no es necesario; proporcione el nombre de la base de datos en su lugar a través de un nombre de tres partes en la instrucción CREATE EXTERNAL TABLE, dentro del parámetro LOCATION. Para obtener un ejemplo, vea CREATE EXTERNAL TABLE.
En el ejemplo siguiente, WINSQL2019AGL
es el nombre del agente de escucha del grupo de disponibilidad y dbname
es el nombre de la base de datos que será el destino de la instrucción CREATE EXTERNAL TABLE.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = SQLServerCredentials
);
Puede demostrar el comportamiento de redireccionamiento del grupo de disponibilidad especificando ApplicationIntent
y creando una tabla externa en la vista del sistema sys.servers
. En el siguiente script de ejemplo, se crean dos orígenes de datos externos y se crea una tabla externa para cada uno. Use las vistas para probar qué servidor responde a la conexión. Se pueden obtener resultados similares con la característica de enrutamiento de solo lectura. Para más información, consulte Configuración del enrutamiento de solo lectura para un grupo de disponibilidad Always On.
CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = [SQLServerCredentials]
);
GO
CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
CREDENTIAL = [SQLServerCredentials]
);
GO
Dentro de la base de datos del grupo de disponibilidad, cree una vista para devolver sys.servers
y el nombre de la instancia local, lo que le ayuda a identificar qué réplica responde a la consulta. Para obtener más información, consulte sys.servers.
CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO
A continuación, cree una tabla externa en la instancia de origen:
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
Ejemplos: operaciones masivas
Importante
No coloque un / final, nombre de archivo o parámetros de firma de acceso compartido al final de la dirección URL de LOCATION
al configurar un origen de datos externo para las operaciones masivas.
H. Creación de un origen de datos externo para operaciones masivas de recuperación de datos desde Azure Storage
Se aplica a: SQL Server 2017 (14.x) y SQL Server 2019 (15.x)
Use el origen de datos siguiente para las operaciones masivas con BULK INSERT o OPENROWSET. La credencial debe establecer SHARED ACCESS SIGNATURE
como identidad, no debe tener al inicio ?
en el token de SAS, debe tener al menos permiso de lectura en el archivo que se debe cargar (por ejemplo srt=o&sp=r
), y el período de expiración debe ser válido (todas las fechas se expresan en hora UTC). Para más información sobre las firmas de acceso compartido, vea Uso de Firmas de acceso compartido (SAS).
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 = 'https://newinvoices.blob.core.windows.net/week3',
CREDENTIAL = AccessAzureInvoices,
TYPE = BLOB_STORAGE
);
Para ver este ejemplo en uso, vea el ejemplo BULK INSERT.
I. Creación de un origen de datos externo para acceder a los datos de Azure Storage mediante la interfaz abfs://
Se aplica a: SQL Server 2019 (15.x) CU11 y versiones posteriores
En este ejemplo, el origen de datos externo es una cuenta de Azure Data Lake Storage Gen2 logs
, con el controlador Azure Blob Filesystem (ABFS). El contenedor de almacenamiento se denomina daily
. El origen de datos externo de Azure Data Lake Storage Gen2 es solo para la transferencia de datos, ya que no se admite la delegación de predicados.
En este ejemplo se muestra cómo crear la credencial de ámbito de base de datos para la autenticación en una cuenta de Azure Data Lake Storage Gen2. Especifique la clave de la cuenta de Azure Storage en el secreto de la credencial de base de datos. Puede especificar cualquier cadena en la identidad de la credencial con ámbito de base de datos, ya que no se usará para la autenticación en Azure Storage.
-- 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 (
LOCATION = 'abfss://daily@logs.dfs.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
J. Creación de un origen de datos externo mediante ODBC genérico en PostgreSQL
Como en ejemplos anteriores, primero cree una clave maestra de base de datos y una credencial con ámbito de base de datos. La credencial con ámbito de base de datos se usará para el origen de datos externo. En este ejemplo también se supone que hay un proveedor de datos ODBC genérico para PostgreSQL instalado en el servidor.
En este ejemplo, el proveedor de datos ODBC genérico se usa para conectarse a un servidor de base de datos de PostgreSQL en la misma red, donde el nombre de dominio completo del servidor de PostgreSQL es POSTGRES1
, mediante el puerto predeterminado de TCP 5432.
CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
LOCATION = 'odbc://POSTGRES1.domain:5432',
CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
CREDENTIAL = postgres_credential
);
Pasos siguientes
Información general (SQL Server 2022)
Se aplica a: SQL Server 2022 (16.x) y versiones posteriores
Crea un origen de datos externo para consultas de PolyBase. Los orígenes de datos externos se usan para establecer la conectividad y admiten estos casos de uso principales:
- Virtualización y carga de datos mediante PolyBase
- Operaciones de carga masiva con
BULK INSERT
oOPENROWSET
Nota
Esta sintaxis varía en las diferentes versiones de SQL Server. Use la lista desplegable del selector de versiones para elegir la versión adecuada. Este contenido se aplica a SQL Server 2022 (16.x) y versiones posteriores.
Sintaxis para SQL Server 2022 y versiones posteriores
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de 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 } ]
)
[ ; ]
Argumentos
data_source_name
Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único en la base de datos en SQL Server.
LOCATION = '<prefix>://<path[:port]>'
Proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo.
Origen de datos externo | Prefijo de ubicación del conector | Ruta de acceso de ubicación | Ubicaciones admitidas por producto o servicio | Authentication |
---|---|---|---|---|
Cuenta de Azure Storage (V2) | abs |
abs://<container_name>@<storage_account_name>.blob.core.windows.net/ o abs://<storage_account_name>.blob.core.windows.net/<container_name> |
A partir de SQL Server 2022 (16.x) Se admite el espacio de nombres jerárquico. |
Firma de acceso compartido (SAS) |
Azure Data Lake Storage Gen2 | adls |
adls://<container_name>@<storage_account_name>.dfs.core.windows.net/ o adls://<storage_account_name>.dfs.core.windows.net/<container_name> |
A partir de SQL Server 2022 (16.x) | Firma de acceso compartido (SAS) |
SQL Server | sqlserver |
<server_name>[\<instance_name>][:port] |
A partir de SQL Server 2019 (15.x) | Solo autenticación SQL |
Oracle | oracle |
<server_name>[:port] |
A partir de SQL Server 2019 (15.x) | Solo autenticación básica |
Teradata | teradata |
<server_name>[:port] |
A partir de SQL Server 2019 (15.x) | Solo autenticación básica |
MongoDB o API de Cosmos DB para MongoDB | mongodb |
<server_name>[:port] |
A partir de SQL Server 2019 (15.x) | Solo autenticación básica |
ODBC genérico | odbc |
<server_name>[:port] |
A partir de SQL Server 2019 (15.x): solo Windows | Solo autenticación básica |
Operaciones masivas | https |
<storage_account>.blob.core.windows.net/<container> |
A partir de SQL Server 2017 (14.x) | Firma de acceso compartido (SAS) |
Almacenamiento de objetos compatible con S3 | s3 |
- Compatible con S3: s3://<server_name>:<port>/ - AWS S3: s3://<bucket_name>.S3.amazonaws.com[:port]/<folder> o s3://s3.amazonaws.com[:port]/<bucket_name>/<folder> |
A partir de SQL Server 2022 (16.x) | Básico o de paso a través (STS) * |
* Debe ser una credencial con ámbito de base de datos, donde identity está codificada de forma rígida y el argumento SECRET tiene el formato = '<AccessKeyID>:<SecretKeyID>'
o usa la autorización de paso a IDENTITY = 'S3 Access Key'
través (STS). Para obtener más información, vea Configuración de PolyBase para acceder a datos externos en el almacenamiento de objetos compatible con S3.
Ruta de acceso de ubicación:
port
= el puerto en el cual escucha el origen de datos externo. Opcional en muchos casos, en función de la configuración de red.<container_name>
= el contenedor de la cuenta de almacenamiento que contiene los datos. Los contenedores raíz son de solo lectura, no se pueden volver a escribir datos en el contenedor.<storage_account>
= nombre de la cuenta de almacenamiento del recurso de Azure.<server_name>
= el nombre de host.<instance_name>
= el nombre de la instancia con nombre de SQL Server. Se usa si tiene el servicio de SQL Server Browser en ejecución en la instancia de destino.<ip_address>:<port>
= Solo para el almacenamiento de objetos compatible con S3 (a partir de SQL Server 2022 (16.x)), el punto de conexión y el puerto usados para conectarse al almacenamiento compatible con S3.<bucket_name>
= Solo para el almacenamiento de objetos compatible con S3 (a partir de SQL Server 2022 (16.x),específico de la plataforma de almacenamiento.<region>
= Solo para el almacenamiento de objetos compatible con S3 (a partir de SQL Server 2022 (16.x),específico de la plataforma de almacenamiento.<folder>
= Parte de la ruta de acceso de almacenamiento dentro de la dirección URL de almacenamiento.
Instrucciones y notas adicionales cuando se establece la ubicación:
- El motor de base de datos de SQL Server no comprueba la existencia del origen de datos externo cuando se crea el objeto. Para la validación, crea una tabla externa utilizando el origen de datos externo.
- Puede usar el conector
sqlserver
para conectar SQL Server 2019 (15.x) a otro SQL Server, a Azure SQL Database o a Azure Synapse Analytics. - Especifique
Driver={<Name of Driver>}
al conectarse a través deODBC
. - La opción Espacio de nombres jerárquico para cuentas de Azure Storage (V2) que usen el prefijo
adls
se admite a través de Azure Data Lake Storage Gen2 en SQL Server 2022 (16.x).
- La compatibilidad de SQL Server con los orígenes de datos externos HDFS Cloudera (CDP) y Hortonworks (HDP) se retira y no se incluye en SQL Server 2022 (16.x). No es necesario usar el argumento TYPE en SQL Server 2022 (16.x).
- Para obtener más información sobre el almacenamiento de objetos compatible con S3 y PolyBase a partir de SQL Server 2022 (16.x), vea Configuración de PolyBase para acceder a datos externos en el almacenamiento de objetos compatible con S3. Para obtener un ejemplo de consulta de un archivo parquet dentro del almacenamiento de objetos compatible con S3, consulte Virtualización del archivo parquet en un almacenamiento de objetos compatible con S3 con PolyBase.
- A diferencia de las versiones anteriores, en SQL Server 2022 (16.x), el prefijo utilizado para la cuenta de Azure Storage (v2) ha cambiado de
wasb[s]
aabs
. - A diferencia de las versiones anteriores, en SQL Server 2022 (16.x), el prefijo utilizado para Azure Data Lake Storage Gen2 ha cambiado de
abfs[s]
aadls
. - Para obtener un ejemplo de uso de PolyBase para virtualizar un archivo CSV en Azure Storage, consulte Virtualización de un archivo CSV con PolyBase.
- Para obtener un ejemplo de uso de PolyBase para virtualizar una tabla delta en ADLS Gen2, consulte Virtualización de una tabla delta con PolyBase.
- SQL Server 2022 (16.x) admite totalmente dos formatos de dirección URL para la cuenta de Azure Storage v2 (
abs
) y Azure Data Lake Gen2 (adls
).- La ruta LOCATION puede usar los formatos:
<container>@<storage_account_name>..
(recomendado) o<storage_account_name>../<container>
. Por ejemplo:- Cuenta de Azure Storage v2:
abs://<container>@<storage_account_name>.blob.core.windows.net
(recomendado) oabs://<storage_account_name>.blob.core.windows.net/<container>
. - Azure Data Lake Gen2 admite:
adls://<container>@<storage_account_name>.blob.core.windows.net
(recomendado) oadls://<storage_account_name>.dfs.core.windows.net/<container>
.
- Cuenta de Azure Storage v2:
- La ruta LOCATION puede usar los formatos:
CONNECTION_OPTIONS = key_value_pair
Se especifica para SQL Server 2019 (15.x) y versiones posteriores. Especifica opciones adicionales al conectarse a través de ODBC
a un origen de datos externo. Para usar varias opciones de conexión, sepárelas mediante punto y coma.
Se aplica a las conexiones ODBC
genéricas, así como a los conectores ODBC
integrados para SQL Server, Oracle, Teradata, MongoDB y la API de Azure Cosmos DB para MongoDB.
key_value_pair
es la palabra clave y el valor de una opción de conexión específica. Las palabras clave y los valores disponibles dependen del tipo de origen de datos externo. Se requiere como mínimo el nombre del controlador, pero existen otras opciones, como APP='<your_application_name>'
o ApplicationIntent= ReadOnly|ReadWrite
, que también resulta útil establecerlas y pueden ayudarle con la solución de problemas.
Los posibles pares clave-valor son específicos del controlador. Para más información sobre cada proveedor, consulte CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.
A partir de Se aplica a: actualización acumulativa 2 de SQL Server 2022 (16.x), se introdujeron palabras clave adicionales para admitir archivos TNS de Oracle:
- La palabra clave
TNSNamesFile
especifica la ruta de acceso al archivotnsnames.ora
ubicado en el servidor de Oracle. - La palabra clave
ServerName
especifica el alias usado dentro deltnsnames.ora
que se usará para reemplazar el nombre de host y el puerto.
PUSHDOWN = ON | OFF
Se aplica a: SQL Server 2019 (15.x) y versiones posteriores. Indica si se puede insertar cálculo en el origen de datos externo. El valor predeterminado es ON.
PUSHDOWN
se admite al conectarse a SQL Server, Oracle, Teradata, MongoDB, la API Azure Cosmos DB para MongoDB u ODBC en el nivel de origen de datos externo.
Habilitar o deshabilitar la inserción en el nivel de consulta se logra a través de una sugerencia.
CREDENTIAL = credential_name
Especifica una credencial de ámbito de base de datos para la autenticación en el origen de datos externo.
Instrucciones y notas adicionales cuando se crea una credencial:
CREDENTIAL
solo se necesita si se han protegido los datos.CREDENTIAL
no es necesario para los conjuntos de datos que permiten el acceso anónimo.- Al acceder a la cuenta de Azure Storage (V2) o Azure Data Lake Storage Gen2, el valor de
IDENTITY
debe serSHARED ACCESS SIGNATURE
.
Hay varias maneras de crear una firma de acceso compartido:
Puede crear un token de SAS; para ello, vaya a Azure Portal ><Su_cuenta_de_almacenamiento> -> Firma de acceso compartido -> Configurar permisos -> Generar la cadena de conexión y SAS. Para más información, consulte Generación de una firma de acceso compartido.
Puede crear y configurar una SAS con Explorador de Azure Storage.
Puede crear una SAS mediante programación a través de PowerShell, la CLI de Azure, .NET y la API de REST. Para obtener más información, consulte Otorgar acceso limitado a recursos de Azure Storage con firmas de acceso compartido (SAS).
El token de SAS debe configurarse del modo siguiente:
- Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
?
inicial cuando se configura como SECRET. - Usar un período de caducidad válido (todas las fechas se encuentran en hora UTC).
- Conceder al menos permiso de lectura en el archivo que se debe cargar (por ejemplo,
srt=o&sp=r
). Se pueden crear varias firmas de acceso compartido para diferentes casos de uso. Los permisos se deben conceder de la siguiente manera:
Acción Permiso Leer datos desde un archivo Lectura Leer datos de varios archivos y subcarpetas Lectura y enumeración Usar Create External Table as Select (CETAS) Lectura, Crear, Enumerar y Escritura - Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
Para Azure Blob Storage y Azure Data Lake Gen 2:
- Servicios permitidos: se debe seleccionar
Blob
para generar el token de SAS. - Tipos de recursos permitidos: se deben seleccionar
Container
yObject
para generar el token de SAS.
- Servicios permitidos: se debe seleccionar
Para obtener un ejemplo del uso de un elemento CREDENTIAL
con el almacenamiento de objetos compatible con S3 y PolyBase, consulte Configuración de PolyBase para acceder a datos externos en el almacenamiento de objetos compatible con S3.
Para crear una credencial con ámbito de base de datos, vea CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
Permisos
Requiere el permiso CONTROL
en la base de datos en SQL Server.
Bloqueo
Toma un bloqueo compartido en el objeto EXTERNAL DATA SOURCE
.
Seguridad
PolyBase es compatible con la autenticación basada en proxy para la mayoría de orígenes de datos externos. Cree una credencial con ámbito de base de datos para crear la cuenta de proxy.
Actualizar a SQL Server 2022
A partir de SQL Server 2022 (16.x) ya no se admiten orígenes de datos externos de Hadoop. Es necesario volver a crear manualmente orígenes de datos externos creados anteriormente con TYPE = HADOOP
y cualquier tabla externa que use este origen de datos externo.
Los usuarios también tendrán que configurar sus orígenes de datos externos para usar nuevos conectores al conectarse a Azure Storage.
Origen de datos externo | De | En |
---|---|---|
Azure Blob Storage | wasb[s] | abs |
ADLS Gen2 | abfs[s] | adls |
Ejemplos
Importante
Para obtener información sobre cómo instalar y habilitar PolyBase, vea Instalación de PolyBase en Windows
A. Creación de un origen de datos externo en SQL Server para hacer referencia a Oracle
Para crear un origen de datos externo que haga referencia a Oracle, asegúrese de que tiene una credencial con ámbito de base de datos. También, opcionalmente, puede habilitar o deshabilitar la inserción de cálculo en este origen de datos.
-- 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',
CREDENTIAL = OracleProxyAccount,
PUSHDOWN = ON
);
Opcionalmente, el origen de datos externo a Oracle puede usar la autenticación de proxy para proporcionar un control de acceso más preciso. Un usuario de proxy se puede configurar para tener acceso limitado en comparación con el usuario que se va a suplantar.
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]
);
Como alternativa, puede autenticarse mediante TNS.
A partir de Se aplica a: la actualización acumulativa 2 CREATE EXTERNAL DATA SOURCE
de SQL Server 2022 (16.x), ahora admite el uso de archivos TNS al conectarse a Oracle.
El parámetro CONNECTION_OPTIONS
se expandió y ahora usa TNSNamesFile
y ServerName
como variables para examinar el archivo tnsnames.ora
y establecer la conexión con el servidor.
En el ejemplo siguiente, durante el tiempo de ejecución, SQL Server buscará la ubicación del archivo tnsnames.ora
especificada por TNSNamesFile
y buscará el host y el puerto de red especificados por 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. Creación de un origen de datos externo para hacer referencia a una instancia con nombre de SQL Server mediante la conectividad de PolyBase
Se aplica a: SQL Server 2019 (15.x) y versiones posteriores
Para crear un origen de datos externo que haga referencia a una instancia con nombre de SQL Server, use CONNECTION_OPTIONS
para especificar el nombre de la instancia.
En primer lugar, cree la credencial con ámbito de base de datos y almacene las credenciales para un inicio de sesión autenticado de SQL. El conector ODBC de SQL para PolyBase solo admite la autenticación básica. Para poder crear una credencial con ámbito de base de datos, la base de datos debe tener una clave maestra para proteger la credencial. Para obtener más información, vea CREATE MASTER KEY. En el ejemplo siguiente se crea una credencial con ámbito de base de datos, y se le proporciona su propio inicio de sesión y contraseña.
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
En el ejemplo siguiente, WINSQL2019
es el nombre de host y SQL2019
el de la instancia. 'Server=%s\SQL2019'
es el par de clave-valor.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019',
CONNECTION_OPTIONS = 'Server=%s\SQL2019',
CREDENTIAL = SQLServerCredentials
);
Como alternativa, puede usar un puerto para conectarse a una instancia predeterminada de SQL Server.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019:58137',
CREDENTIAL = SQLServerCredentials
);
C. Creación de un origen de datos externo para hacer referencia a una réplica secundaria legible de un grupo de disponibilidad Always On
Se aplica a: SQL Server 2019 (15.x) y versiones posteriores
Para crear un origen de datos externo que haga referencia a una réplica secundaria legible de SQL Server, use CONNECTION_OPTIONS
para especificar el ApplicationIntent=ReadOnly
.
En primer lugar, cree la credencial con ámbito de base de datos y almacene las credenciales para un inicio de sesión autenticado de SQL. El conector ODBC de SQL para PolyBase solo admite la autenticación básica. Para poder crear una credencial con ámbito de base de datos, la base de datos debe tener una clave maestra para proteger la credencial. Para obtener más información, vea CREATE MASTER KEY. En el ejemplo siguiente se crea una credencial con ámbito de base de datos, y se le proporciona su propio inicio de sesión y contraseña.
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
A continuación, cree el nuevo origen de datos externo.
El parámetro Database
de ODBC no es necesario; proporcione el nombre de la base de datos en su lugar a través de un nombre de tres partes en la instrucción CREATE EXTERNAL TABLE, dentro del parámetro LOCATION. Para obtener un ejemplo, vea CREATE EXTERNAL TABLE.
En el ejemplo siguiente, WINSQL2019AGL
es el nombre del agente de escucha del grupo de disponibilidad y dbname
es el nombre de la base de datos que será el destino de la instrucción CREATE EXTERNAL TABLE.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = SQLServerCredentials
);
Puede demostrar el comportamiento de redireccionamiento del grupo de disponibilidad especificando ApplicationIntent
y creando una tabla externa en la vista del sistema sys.servers
. En el siguiente script de ejemplo, se crean dos orígenes de datos externos y se crea una tabla externa para cada uno. Use las vistas para probar qué servidor responde a la conexión. Se pueden obtener resultados similares con la característica de enrutamiento de solo lectura. Para más información, consulte Configuración del enrutamiento de solo lectura para un grupo de disponibilidad Always On.
CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = [SQLServerCredentials]
);
GO
CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
CREDENTIAL = [SQLServerCredentials]
);
GO
Dentro de la base de datos del grupo de disponibilidad, cree una vista para devolver sys.servers
y el nombre de la instancia local, lo que le ayuda a identificar qué réplica responde a la consulta. Para obtener más información, consulte sys.servers.
CREATE VIEW vw_sys_servers AS
SELECT [name] FROM sys.servers
WHERE server_id = 0;
GO
A continuación, cree una tabla externa en la instancia de origen:
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. Creación de un origen de datos externo para consultar un archivo parquet en el almacenamiento de objetos compatible con S3 a través de PolyBase
Se aplica a: SQL Server 2022 (16.x) y versiones posteriores.
El siguiente script de ejemplo crea un origen de datos externo s3_ds
en la base de datos de usuario de origen de SQL Server. El origen de datos externo hace referencia a la credencial de ámbito de base de datos 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
Compruebe el nuevo origen de datos externo con sys.external_data_sources.
SELECT * FROM sys.external_data_sources;
A continuación, en el ejemplo siguiente se muestra el uso de T-SQL para consultar un archivo parquet almacenado en el almacenamiento de objetos compatible con S3 mediante la consulta OPENROWSET. Para obtener más información, consulte Virtualización del archivo parquet en un almacenamiento de objetos compatible con S3 con PolyBase.
SELECT *
FROM OPENROWSET (
BULK '/<bucket>/<parquet_folder>',
FORMAT = 'PARQUET',
DATA_SOURCE = 's3_ds'
) AS [cc];
E. Creación de un origen de datos externo mediante ODBC genérico en PostgreSQL
Como en ejemplos anteriores, primero cree una clave maestra de base de datos y una credencial con ámbito de base de datos. La credencial con ámbito de base de datos se usará para el origen de datos externo. En este ejemplo también se supone que hay un proveedor de datos ODBC genérico para PostgreSQL instalado en el servidor.
En este ejemplo, el proveedor de datos ODBC genérico se usa para conectarse a un servidor de base de datos de PostgreSQL en la misma red, donde el nombre de dominio completo del servidor de PostgreSQL es POSTGRES1
, mediante el puerto predeterminado de TCP 5432.
CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
LOCATION = 'odbc://POSTGRES1.domain:5432',
CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
CREDENTIAL = postgres_credential
);
Azure Storage
Creación de una firma de acceso compartido
Tanto en Azure Blob Storage como en Azure Data Lake Gen2, el método de autenticación admitido es la firma de acceso compartido (SAS). Una manera sencilla de generar un token de firma de acceso compartido es mediante los pasos que se describen a continuación. Para más información, consulte CREDENTIAL.
- Vaya a Azure Portal y elija la cuenta de almacenamiento deseada.
- Vaya al contenedor deseado en el menú Almacenamiento de datos.
- Seleccione Tokens de acceso compartido.
- Elija el permiso adecuado en función de la acción deseada. Como referencia, use la tabla siguiente:
Acción | Permiso |
---|---|
Leer datos desde un archivo | Lectura |
Leer datos de varios archivos y subcarpetas | Lectura y enumeración |
Usar Create External Table as Select (CETAS) | Lectura, creación y escritura |
- Elegir la fecha de vencimiento del token.
- Generar URL y token de SAS.
- Copiar el token de SAS.
F. Creación de un origen de datos externo para acceder a los datos de Azure Blob Storage mediante la interfaz abs://
Se aplica a: SQL Server 2022 (16.x) y versiones posteriores
A partir de SQL Server 2022 (16.x), use un nuevo prefijo abs
para la cuenta de Azure Storage v2. El prefijo abs
admite la autenticación con SHARED ACCESS SIGNATURE
. El prefijo abs
reemplaza al wasb
, usado en versiones anteriores. Dado que HADOOP ya no se admite, no es necesario usar TYPE = BLOB_STORAGE
.
La clave de la cuenta de almacenamiento de Azure ya no es necesaria, alternativamente se usa un token de SAS, como se puede ver en el ejemplo siguiente:
-- 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,
);
Para ver un ejemplo más detallado sobre cómo acceder a los archivos CSV almacenados en Azure Blob Storage, consulte Virtualizar archivo CSV con PolyBase.
G. Creación de un origen de datos externo para acceder a los datos en Azure Data Lake Gen2
Se aplica a: SQL Server 2022 (16.x) y versiones posteriores.
A partir de SQL Server 2022 (16.x), utilice un nuevo prefijo adls
para Azure Data Lake Gen2, en sustitución del abfs
utilizado en versiones anteriores. El prefijo adls
también admite el token de SAS como método de autenticación, como se muestra en este ejemplo:
--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
);
Para ver un ejemplo más detallado sobre cómo acceder a los archivos delta almacenados en Azure Data Lake Gen2, consulte Virtualizar tabla delta con PolyBase.
Ejemplos: Operaciones masivas
Importante
No coloque un / final, nombre de archivo o parámetros de firma de acceso compartido al final de la dirección URL de LOCATION
al configurar un origen de datos externo para las operaciones masivas.
H. Creación de un origen de datos externo para operaciones masivas de recuperación de datos desde Azure Storage
Se aplica a: SQL Server 2022 (16.x) y versiones posteriores.
Use el origen de datos siguiente para las operaciones masivas con BULK INSERT o OPENROWSET. La credencial debe establecer SHARED ACCESS SIGNATURE
como identidad, no debe tener al inicio ?
en el token de SAS, debe tener al menos permiso de lectura en el archivo que se debe cargar (por ejemplo srt=o&sp=r
), y el período de expiración debe ser válido (todas las fechas se expresan en hora UTC). Para más información sobre las firmas de acceso compartido, vea Uso de Firmas de acceso compartido (SAS).
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,
);
Pasos siguientes
* SQL Database *
Introducción: Azure SQL Database
Se aplica a: Azure SQL Database
Crea un origen de datos externo para consultas elásticas. Los orígenes de datos externos se usan para establecer la conectividad y admiten estos casos de uso principales:
- Operaciones de carga masiva con
BULK INSERT
oOPENROWSET
- Consultar instancias remotas de SQL Database o Azure Synapse utilizando SQL Database con consulta elástica
- Consultar una instancia de SQL Database con particiones mediante la consulta elástica
Syntax
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.
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>' ] )
[ ; ]
Argumentos
data_source_name
Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único en la base de datos en SQL Database.
LOCATION = '<prefix>://<path[:port]>'
Proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo.
Origen de datos externo | Prefijo de ubicación del conector | Ruta de acceso de ubicación | Disponibilidad |
---|---|---|---|
Operaciones masivas | https |
<storage_account>.blob.core.windows.net/<container> |
|
Consulta elástica (partición) | No se requiere | <shard_map_server_name>.database.windows.net |
|
Consulta elástica (remota) | No se requiere | <remote_server_name>.database.windows.net |
|
EdgeHub | edgehub |
edgehub:// |
Solo está disponible en Azure SQL Edge. EdgeHub siempre es local para la instancia de Azure SQL Edge. Como tal, no es necesario especificar una ruta de acceso o un valor de puerto. |
Kafka | kafka |
kafka://<kafka_bootstrap_server_name_ip>:<port_number> |
Solo está disponible en Azure SQL Edge. |
Ruta de acceso de ubicación:
<shard_map_server_name>
= el nombre del servidor lógico de Azure que hospeda el administrador del mapa de particiones. El argumentoDATABASE_NAME
proporciona la base de datos que se utiliza para hospedar el mapa de particiones ySHARD_MAP_NAME
se utiliza para el propio mapa de particiones.<remote_server_name>
= el nombre de servidor lógico de destino para la consulta elástica. El nombre de la base de datos se especifica utilizando el argumentoDATABASE_NAME
.
Instrucciones y notas adicionales cuando se establece la ubicación:
- El motor de base de datos no comprueba la existencia del origen de datos externo cuando se crea el objeto. Para la validación, crea una tabla externa utilizando el origen de datos externo.
CREDENTIAL = credential_name
Especifica una credencial de ámbito de base de datos para la autenticación en el origen de datos externo.
Instrucciones y notas adicionales cuando se crea una credencial:
- Para cargar datos de Azure Storage en Azure SQL Database, use una firma de acceso compartido (token de SAS).
CREDENTIAL
solo se necesita si se han protegido los datos.CREDENTIAL
no es necesario para los conjuntos de datos que permiten el acceso anónimo.- Cuando
TYPE
=BLOB_STORAGE
, la credencial debe crearse mediante el uso deSHARED ACCESS SIGNATURE
como identidad. - Al conectarse a la instancia de Azure Storage mediante el conector WASB[s], la autenticación debe realizarse con una clave de cuenta de almacenamiento, no con una firma de acceso compartido (SAS).
- Cuando
TYPE
=HADOOP
, la credencial debe crearse con la clave de la cuenta de almacenamiento comoSECRET
. TYPE
=BLOB_STORAGE
solo se permite para operaciones masivas; no se pueden crear tablas externas para un origen de datos externo conTYPE
=BLOB_STORAGE
.
Hay varias maneras de crear una firma de acceso compartido:
Puede crear un token de SAS; para ello, vaya a Azure Portal ><Su_cuenta_de_almacenamiento> -> Firma de acceso compartido -> Configurar permisos -> Generar la cadena de conexión y SAS. Para más información, consulte Generación de una firma de acceso compartido.
Puede crear y configurar una SAS con Explorador de Azure Storage.
Puede crear una SAS mediante programación a través de PowerShell, la CLI de Azure, .NET y la API de REST. Para obtener más información, consulte Otorgar acceso limitado a recursos de Azure Storage con firmas de acceso compartido (SAS).
El token de SAS debe configurarse del modo siguiente:
- Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
?
inicial cuando se configura como SECRET. - Usar un período de caducidad válido (todas las fechas se encuentran en hora UTC).
- Conceder al menos permiso de lectura en el archivo que se debe cargar (por ejemplo,
srt=o&sp=r
). Se pueden crear varias firmas de acceso compartido para diferentes casos de uso. Los permisos se deben conceder de la siguiente manera:
Acción Permiso Leer datos desde un archivo Lectura Leer datos de varios archivos y subcarpetas Lectura y enumeración Usar Create External Table as Select (CETAS) Lectura, creación y escritura - Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
Para obtener un ejemplo del uso de CREDENTIAL
con SHARED ACCESS SIGNATURE
y TYPE
= BLOB_STORAGE
, vea Creación de un origen de datos externo para ejecutar operaciones masivas y recuperar datos desde Azure Storage en SQL Database.
Para crear una credencial con ámbito de base de datos, vea CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
TYPE = [ BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER]
Especifica el tipo de origen de datos externo que se está configurando. Este parámetro no siempre es necesario.
- Use
RDBMS
para consultas entre bases de datos mediante una consulta elástica de SQL Database. - Use
SHARD_MAP_MANAGER
al crear un origen de datos externo al conectarse a una base de datos SQL Database particionada. - Use
BLOB_STORAGE
al ejecutar operaciones masivas con BULK INSERT u OPENROWSET.
Importante
No establezca TYPE
si usa cualquier otro origen de datos externo.
DATABASE_NAME = database_name
Configure este argumento cuando TYPE
se haya establecido en RDBMS
o SHARD_MAP_MANAGER
.
TYPE | Valor de DATABASE_NAME |
---|---|
RDBMS | El nombre de la base de datos remota en el servidor que se proporciona mediante LOCATION |
SHARD_MAP_MANAGER | Nombre de la base de datos que funciona como administrador del mapa de particiones |
Para obtener un ejemplo en el que se muestra cómo crear un origen de datos externo donde TYPE
= RDBMS
vea Creación de un origen de datos externo de RDBMS.
SHARD_MAP_NAME = shard_map_name
Se usa cuando el TYPE
argumento está establecido en SHARD_MAP_MANAGER
únicamente para establecer el nombre del mapa de particiones.
Para obtener un ejemplo en el que se muestra cómo crear un origen de datos externo donde TYPE
= SHARD_MAP_MANAGER
vea Creación de un origen de datos externo de administrador de mapas de partición.
Permisos
Requiere el permiso CONTROL
en la base de datos en Azure SQL Database.
Bloqueo
Toma un bloqueo compartido en el objeto EXTERNAL DATA SOURCE
.
Ejemplos
A. Creación de un origen de datos externo de administrador de mapa de particiones
Si quiere crear un origen de datos externo para hacer referencia a SHARD_MAP_MANAGER
, especifique el nombre del servidor de SQL Database que hospeda el administrador de mapa de particiones en SQL Database o una base de datos de SQL Server en una máquina virtual.
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'
);
Para obtener un tutorial paso a paso, vea Getting started with elastic queries for sharding (horizontal partitioning) [Introducción a las consultas elásticas para particionamiento (creación de particiones horizontales)].
B. Creación de un origen de datos externo de RDBMS
Para crear un origen de datos externo para hacer referencia a RDBMS, especifique el nombre del servidor de SQL Database de la base de datos remota en SQL Database.
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
);
Para obtener un tutorial paso a paso sobre RDBMS, vea Introducción a las consultas entre bases de datos (particiones verticales).
Ejemplos: operaciones masivas
Importante
No coloque un / final, nombre de archivo o parámetros de firma de acceso compartido al final de la dirección URL de LOCATION
al configurar un origen de datos externo para las operaciones masivas.
C. Creación de un origen de datos externo para operaciones masivas de recuperación de datos desde Azure Storage
Use el origen de datos siguiente para las operaciones masivas con BULK INSERT o OPENROWSET. La credencial debe establecer SHARED ACCESS SIGNATURE
como identidad, no debe tener al inicio ?
en el token de SAS, debe tener al menos permiso de lectura en el archivo que se debe cargar (por ejemplo srt=o&sp=r
), y el período de expiración debe ser válido (todas las fechas se expresan en hora UTC). Para más información sobre las firmas de acceso compartido, vea Uso de Firmas de acceso compartido (SAS).
CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
-- Remove ? from the beginning of the SAS token
SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';
CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
CREDENTIAL = AccessAzureInvoices,
TYPE = BLOB_STORAGE
);
Para ver este ejemplo en uso, vea BULK INSERT.
Ejemplos: Azure SQL Edge
Importante
Para obtener información sobre cómo configurar datos externos para Azure SQL Edge, vea Streaming de datos en Azure SQL Edge.
A. Creación de un origen de datos externo para hacer referencia a Kafka
Se aplica a: Azure SQL Edge solo
En este ejemplo, el origen de datos externo es un servidor Kafka que tiene la dirección IP xxx.xxx.xxx.xxx y que escucha en el puerto 1900. El origen de datos externo Kafka es solo para el streaming de datos y no admite la delegación de predicados.
-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyKafkaServer
WITH (LOCATION = 'kafka://xxx.xxx.xxx.xxx:1900');
B. Creación de un origen de datos externo para hacer referencia a EdgeHub
Se aplica a: Azure SQL Edge solo
En este ejemplo, el origen de datos externo es EdgeHub, que se ejecuta en el mismo dispositivo perimetral que Azure SQL Edge. El origen de datos externo edgeHub es solo para el streaming de datos y no admite la inserción de predicados.
-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyEdgeHub
WITH (LOCATION = 'edgehub://');
Pasos siguientes
* Azure Synapse
Analytics *
Introducción: Azure Synapse Analytics
Se aplica a: Azure Synapse Analytics
Crea un origen de datos externo para la virtualización de datos. Los orígenes de datos externos se usan para establecer la conectividad y admiten el caso de uso principal de virtualización y carga de datos desde orígenes de datos externos. Para más información, vea Uso de tablas externas con Synapse SQL.
Importante
Para crear un origen de datos de externo y consultar una instancia de Azure Synapse Analytics mediante Azure SQL Database con consulta elástica, vea SQL Database.
Syntax
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = HADOOP ]
)
[ ; ]
Argumentos
data_source_name
Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único en Azure SQL Database en Azure Synapse Analytics.
LOCATION = '<prefix>://<path>'
Proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo.
Origen de datos externo | Prefijo de ubicación del conector | Ruta de acceso de ubicación |
---|---|---|
Data Lake Storage* Gen1 | adl |
<storage_account>.azuredatalake.net |
Data Lake Storage Gen2 | abfs[s] |
<container>@<storage_account>.dfs.core.windows.net |
Azure Blob Storage | wasbs |
<container>@<storage_account>.blob.core.windows.net |
Azure Blob Storage | https |
<storage_account>.blob.core.windows.net/<container>/subfolders |
Data Lake Storage Gen1 | http[s] |
<storage_account>.azuredatalakestore.net/webhdfs/v1 |
Data Lake Storage Gen2 | http[s] |
<storage_account>.dfs.core.windows.net/<container>/subfolders |
Data Lake Storage Gen2 | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
* Microsoft Azure Data Lake Storage Gen1 tiene compatibilidad limitada, por lo que se recomienda Gen2 para todo el desarrollo nuevo.
Origen de datos externo | Prefijo de ubicación del conector | Grupos de SQL dedicados: PolyBase | Grupos de SQL dedicados: nativos* | Grupos de SQL sin servidor |
---|---|---|---|---|
Data Lake Storage** Gen1 | adl |
No | No | Sí |
Data Lake Storage Gen2 | abfs[s] |
Sí | Sí | Sí |
Azure Blob Storage | wasbs |
Sí | Sí*** | Sí |
Almacenamiento de blobs de Azure | https |
No | Sí | Sí |
Data Lake Storage Gen1 | http[s] |
No | No | Sí |
Data Lake Storage Gen2 | http[s] |
Sí | Sí | Sí |
Data Lake Storage Gen2 | wasb[s] |
Sí | Sí | Sí |
* Los grupos de SQL sin servidor y dedicados de Azure Synapse Analytics usan diferentes bases de código para la virtualización de datos. Los grupos de SQL sin servidor admiten una tecnología nativa de virtualización de datos. Los grupos de SQL dedicados admiten la virtualización de datos nativa y de PolyBase. La virtualización de datos de PolyBase se usa cuando se crea el ORIGEN DE DATOS EXTERNO con TYPE=HADOOP
.
** Microsoft Azure Data Lake Storage Gen1 tiene compatibilidad limitada, por lo que se recomienda Gen2 para todo el desarrollo nuevo.
*** Se recomienda el conector más seguro wasbs
en lugar de wasb
. Solo la virtualización de datos nativa en los grupos de SQL dedicados (donde TYPE no es igual a HADOOP) admite wasb
.
Ruta de acceso de ubicación:
<container>
= el contenedor de la cuenta de almacenamiento que contiene los datos. Los contenedores raíz son de solo lectura, no se pueden volver a escribir datos en el contenedor.<storage_account>
= el nombre de la cuenta de almacenamiento del recurso de Azure.
Instrucciones y notas adicionales cuando se establece la ubicación:
- La opción predeterminada consiste en usar
enable secure SSL connections
al aprovisionar Azure Data Lake Storage Gen2. Si está habilitada, debe usarabfss
al seleccionar una conexión TLS/SSL segura. Tenga en cuenta queabfss
funciona también con conexiones TLS no seguras. Para obtener más información, vea Controlador Azure Blob File System (ABFS). - Azure Synapse no comprueba la existencia del origen de datos externo cuando se crea el objeto. Para la validación, crea una tabla externa utilizando el origen de datos externo.
- Para garantizar una semántica de consulta coherente, use el mismo origen de datos externo para todas las tablas cuando realice consultas a Hadoop.
- El prefijo
https:
le permite usar la subcarpeta en la ruta de acceso.https
no está disponible para todos los métodos de acceso a datos. wasbs
se recomienda, ya que se enviarán los datos mediante una conexión TLS segura.- Los espacios de nombres jerárquicos no se admiten con las cuentas de almacenamiento de Azure V2 al acceder a los datos mediante la interfaz heredada
wasb://
, pero el uso dewasbs://
admite espacios de nombres jerárquicos.
CREDENTIAL = credential_name
Opcional. Especifica una credencial con ámbito de base de datos para autenticarse en el origen de datos externo. El origen de datos externo sin credenciales puede acceder a la cuenta de almacenamiento pública o usar la identidad de Microsoft Entra del autor de la llamada para acceder a los archivos en Azure Storage.
Instrucciones y notas adicionales cuando se crea una credencial:
- Para cargar datos de Azure Storage o Azure Data Lake Store (ADLS) Gen2 en Azure Synapse Analytics, use una clave de Azure Storage.
CREDENTIAL
solo se necesita si se han protegido los datos.CREDENTIAL
no es necesario para los conjuntos de datos que permiten el acceso anónimo.
Para crear una credencial con ámbito de base de datos, vea CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
En el grupo de SQL sin servidor, las credenciales con ámbito de base de datos pueden especificar la identidad administrada del área de trabajo, el nombre de la entidad de seguridad de servicio o el token de firma de acceso compartido (SAS). El acceso a través de una identidad de usuario, también conocido como paso directo de Microsoft Entra, también es posible en la credencial con ámbito de base de datos, ya que es acceso anónimo al almacenamiento disponible públicamente. Para más información, consulte Tipos de autorización de almacenamiento admitidos.
En el grupo de SQL dedicado, las credenciales con ámbito de base de datos pueden especificar el token de firma de acceso compartido (SAS), la clave de acceso de almacenamiento, la entidad de servicio, la identidad administrada del área de trabajo o el acceso directo de Microsoft Entra.
TYPE = HADOOP
Opcional; no se recomienda.
Solo puede especificar TYPE con grupos de SQL dedicados. HADOOP
es el único valor permitido cuando se especifica. Los orígenes de datos externos con TYPE=HADOOP
solo están disponibles en los grupos de SQL dedicados.
Use HADOOP para implementaciones heredadas; de lo contrario, se recomienda usar el acceso a datos nativos más recientes. No especifique el argumento de TIPO para usar el acceso a datos nativos más recientes.
A fin de obtener un ejemplo de cómo usar TYPE = HADOOP
para cargar datos desde Azure Storage, vea Creación de un origen de datos externo para hacer referencia a Azure Data Lake Store Gen1 o 2 mediante una entidad de servicio.
Los grupos de SQL sin servidor y dedicados de Azure Synapse Analytics usan diferentes bases de código para la virtualización de datos. Los grupos de SQL sin servidor admiten una tecnología nativa de virtualización de datos. Los grupos de SQL dedicados admiten la virtualización de datos nativa y de PolyBase. La virtualización de datos de PolyBase se usa cuando se crea el ORIGEN DE DATOS EXTERNO con TYPE=HADOOP
.
Permisos
Debe tener el permiso CONTROL
para la base de datos.
Bloqueo
Toma un bloqueo compartido en el objeto EXTERNAL DATA SOURCE
.
Seguridad
La mayoría de los orígenes de datos externos admiten la autenticación basada en proxy, mediante una credencial con ámbito de base de datos para crear la cuenta de proxy.
Se pueden usar claves de Firma de acceso compartido (SAS) para autenticarse en cuentas de almacenamiento de Azure Data Lake Store Gen 2. Los clientes que quieran autenticarse con una Firma de acceso compartido deben crear una credencial con ámbito de base de datos donde IDENTITY = "Shared Access Signature"
y especificar un token SAS como secreto.
Si crea una credencial con ámbito de base de datos donde IDENTITY = "Shared Access Signature"
y usa un valor de clave de almacenamiento como secreto, recibirá el siguiente mensaje de error:
'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]'
Ejemplos
A. Creación de un origen de datos externo para acceder a los datos de Azure Storage mediante la interfaz wasb://
En este ejemplo, el origen de datos externo es una cuenta de Azure Storage (V2) denominada logs
. El contenedor de almacenamiento se denomina daily
. El origen de datos externo de Azure Storage es solo para la transferencia de datos. No admite la inserción de predicado. No se admiten espacios de nombres jerárquicos al acceder a los datos mediante la interfaz de wasb://
. Tenga en cuenta que al conectarse a la instancia de Azure Storage mediante el conector WASB,la autenticación debe realizarse con una clave de cuenta de almacenamiento, no con una firma de acceso compartido (SAS).
En este ejemplo se usa el método de acceso basado en Java de HADOOP heredado. En el ejemplo siguiente se muestra cómo crear la credencial de ámbito de base de datos para la autenticación en Azure Storage. Especifique la clave de la cuenta de Azure Storage en el secreto de la credencial de base de datos. Puede especificar cualquier cadena en la identidad de la credencial con ámbito de base de datos, ya que no se usará para la autenticación en Azure Storage.
-- 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 (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
B. Creación de un origen de datos externo para hacer referencia a Azure Data Lake Store Gen 1 o 2 mediante una entidad de servicio
La conectividad de Azure Data Lake Store se puede basar en el URI de ADLS y en la entidad de servicio de la aplicación Microsoft Entra. La documentación para crear esta aplicación se puede encontrar en Autenticación de Data Lake Store mediante el identificador de Microsoft Entra.
-- 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 (
LOCATION = 'adl://newyorktaxidataset.azuredatalakestore.net',
CREDENTIAL = ADLS_credential,
TYPE = HADOOP
);
-- 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
LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net',
CREDENTIAL = ADLS_credential,
TYPE = HADOOP
);
C. Creación de un origen de datos externo para hacer referencia a Azure Data Lake Store Gen2 con la clave de cuenta de almacenamiento
-- 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. Creación de un origen de datos externo para Azure Data Lake Store Gen2 mediante abfs://
No es necesario especificar SECRET al conectarse a la cuenta de Azure Data Lake Store Gen2 con el mecanismo de identidad administrada.
-- 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
);
Pasos siguientes
- 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 de firmas de acceso compartido (SAS)
* Analytics
Platform System (PDW) *
Introducción: Sistema de la plataforma de análisis
Se aplica a: Analytics Platform System (PDW)
Crea un origen de datos externo para consultas de PolyBase. Los orígenes de datos externos se usan para establecer la conectividad y admiten el siguiente caso de uso: Virtualización y carga de datos mediante PolyBase.
Syntax
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = HADOOP ]
[ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]
Argumentos
data_source_name
Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único dentro del servidor en Analytics Platform System (PDW).
LOCATION = '<prefix>://<path[:port]>'
Proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo.
Origen de datos externo | Prefijo de ubicación del conector | Ruta de acceso de ubicación |
---|---|---|
Cloudera CDH o Hortonworks HDP | hdfs |
<Namenode>[:port] |
Cuenta de Azure Storage | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
Ruta de acceso de ubicación:
<Namenode>
: nombre de equipo, nombre de URI de servicio o dirección IP deNamenode
en el clúster de Hadoop. PolyBase debe resolver los nombres DNS utilizados por el clúster de Hadoop.port
= el puerto en el cual escucha el origen de datos externo. En Hadoop, el puerto se puede encontrar mediante el parámetro de configuraciónfs.defaultFS
. El valor predeterminado es 8020.<container>
= el contenedor de la cuenta de almacenamiento que contiene los datos. Los contenedores raíz son de solo lectura, no se pueden volver a escribir datos en el contenedor.<storage_account>
= el nombre de la cuenta de almacenamiento del recurso de Azure.
Instrucciones y notas adicionales cuando se establece la ubicación:
- El motor de PDW no comprueba la existencia del origen de datos externo cuando se crea el objeto. Para la validación, crea una tabla externa utilizando el origen de datos externo.
- Para garantizar una semántica de consulta coherente, use el mismo origen de datos externo para todas las tablas cuando realice consultas a Hadoop.
wasbs
se recomienda, ya que se enviarán los datos mediante una conexión TLS segura.- Los espacios de nombres jerárquicos no se admiten cuando se usan con cuentas de Azure Storage a través de wasb://.
- Para garantizar que las consultas de PolyBase son correctas durante una conmutación por error del
Namenode
de Hadoop, considere la posibilidad de usar una dirección IP virtual para elNamenode
del clúster de Hadoop. Si no, ejecute un comando ALTER EXTERNAL DATA SOURCE para que apunte a la nueva ubicación.
CREDENTIAL = credential_name
Especifica una credencial de ámbito de base de datos para la autenticación en el origen de datos externo.
Instrucciones y notas adicionales cuando se crea una credencial:
- Para cargar datos de Azure Storage a Azure Synapse o PDW, use una clave de Azure Storage.
CREDENTIAL
solo se necesita si se han protegido los datos.CREDENTIAL
no es necesario para los conjuntos de datos que permiten el acceso anónimo.
TYPE = [ HADOOP ]
Especifica el tipo de origen de datos externo que se está configurando. Este parámetro no siempre es necesario.
- Use HADOOP cuando el origen de datos externo sea Cloudera CDH, Hortonworks HDP o Azure Storage.
A fin de obtener un ejemplo de cómo usar TYPE
= HADOOP
para cargar datos desde Azure Storage, vea Creación de un origen de datos externo para hacer referencia a Hadoop.
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
En SQL Server 2019 (15.x), no especifique RESOURCE_MANAGER_LOCATION a menos que se conecte a Cloudera CDH, Hortonworks HDP o una cuenta de Azure Storage.
Configure este valor opcional solo al conectarse a Cloudera CDH, Hortonworks HDP o una cuenta de Azure Storage. Para obtener una lista completa de las versiones de Hadoop compatibles, vea Configuración de conectividad de PolyBase (Transact-SQL).
Cuando RESOURCE_MANAGER_LOCATION
está definido, el optimizador de consultas tomará una decisión basada en el costo para mejorar el rendimiento. Se puede usar un trabajo de MapReduce para insertar el cálculo en Hadoop. La especificación de RESOURCE_MANAGER_LOCATION
puede reducir significativamente el volumen de datos transferidos entre Hadoop y SQL, lo cual puede suponer una mejora en el rendimiento de las consultas.
Si no se especifica el administrador de recursos, se deshabilita la inserción de cálculo en Hadoop para las consultas de PolyBase. En Creación de un origen de datos externo para hacer referencia a Hadoop con la inserción habilitada se proporciona un ejemplo concreto y más instrucciones.
El valor RESOURCE_MANAGER_LOCATION no se valida cuando se crea el origen de datos externo. Escribir un valor incorrecto puede provocar un error de consulta en tiempo de ejecución cada vez que se intente la inserción, ya que el valor proporcionado no podrá realizar la resolución.
Para que PolyBase funcione correctamente con un origen de datos externo de Hadoop, los puertos de los siguientes componentes del clúster de Hadoop deben estar abiertos:
- Puertos HDFS
- NameNode
- DataNode
- Administrador de recursos
- Envío de trabajo
- Historial de trabajos
Si no se especifica el puerto, el valor predeterminado se elige mediante el valor actual de la configuración "hadoop connectivity".
Conectividad de Hadoop | Puerto predeterminado del administrador de recursos |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
En la tabla siguiente se muestran los puertos predeterminados para estos componentes. Tenga en cuenta que hay dependencias de la versión de Hadoop, así como la posibilidad de una configuración personalizada que no use la asignación de puerto predeterminada.
Componente de clúster de Hadoop | Puerto predeterminado |
---|---|
NameNode | 8020 |
DataNode (transferencia de datos, puerto IPC sin privilegios) | 50010 |
DataNode (transferencia de datos, puerto IPC con privilegios) | 1019 |
Envío de trabajos de Resource Manager (Hortonworks 1.3) | 50300 |
Envío de trabajos de Resource Manager (Cloudera 4.3) | 8021 |
Envío de trabajos de Resource Manager (Hortonworks 2.0 en Windows, Cloudera 5.x en Linux) | 8032 |
Envío de trabajos de Resource Manager (Hortonworks 2.x, 3.0 en Linux, Hortonworks 2.1-3 en Windows) | 8050 |
Historial de trabajos de Resource Manager | 10020 |
Permisos
Requiere el permiso CONTROL
en la base de datos en Analytics Platform System (PDW).
Nota
En versiones anteriores de PDW, para crear el origen de datos externo se necesitaban permisos ALTER ANY EXTERNAL DATA SOURCE
.
Bloqueo
Toma un bloqueo compartido en el objeto EXTERNAL DATA SOURCE
.
Seguridad
PolyBase es compatible con la autenticación basada en proxy para la mayoría de orígenes de datos externos. Cree una credencial con ámbito de base de datos para crear la cuenta de proxy.
No se admite un token de SAS con el tipo HADOOP
. Solo se admite con el tipo = BLOB_STORAGE
cuando se usa en su lugar una clave de acceso de la cuenta de almacenamiento. Si se intenta crear un origen de datos externo con el tipo HADOOP
y una credencial SAS, aparece un error como el siguiente:
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 are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'
Ejemplos
A. Creación de un origen de datos externo para hacer referencia a Hadoop
Si quiere crear un origen de datos externo para hacer referencia a Hortonworks HDP o Cloudera CDH, especifique el nombre de equipo o la dirección IP de Namenode
y puerto de Hadoop.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. Creación de un origen de datos externo para hacer referencia a Hadoop con la inserción habilitada
Especifique la opción RESOURCE_MANAGER_LOCATION
para habilitar la inserción de cálculo en Hadoop para las consultas de PolyBase. Una vez habilitado, PolyBase toma una decisión basada en el coste para determinar si se debe aplicar el cálculo de la consulta en Hadoop.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8020',
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
C. Creación de un origen de datos externo para hacer referencia a Hadoop con protección de Kerberos
Para comprobar si el clúster de Hadoop está protegido con Kerberos, compruebe el valor de la propiedad hadoop.security.authentication
en core-site.xml de Hadoop. Para hacer referencia a un clúster de Hadoop protegido con Kerberos, debe especificar una credencial con ámbito de base de datos que contenga el nombre de usuario y la contraseña de Kerberos. La clave maestra de base de datos se usa para cifrar el secreto de la credencial de ámbito de base de datos.
-- 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 (
LOCATION = 'hdfs://10.10.10.10:8050',
CREDENTIAL = HadoopUser1,
TYPE = HADOOP,
RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);
D. Creación de un origen de datos externo para acceder a los datos de Azure Storage mediante la interfaz wasb://
En este ejemplo, el origen de datos externo es una cuenta de Azure Storage (V2) denominada logs
. El contenedor de almacenamiento se denomina daily
. El origen de datos externo de Azure Storage es solo para la transferencia de datos. No admite la inserción de predicado. No se admiten espacios de nombres jerárquicos al acceder a los datos mediante la interfaz de wasb://
. Tenga en cuenta que al conectarse a la instancia de Azure Storage mediante el conector WASB,la autenticación debe realizarse con una clave de cuenta de almacenamiento, no con una firma de acceso compartido (SAS).
En este ejemplo se muestra cómo crear la credencial de ámbito de base de datos para la autenticación en Azure Storage. Especifique la clave de la cuenta de Azure Storage en el secreto de la credencial de base de datos. Puede especificar cualquier cadena en la identidad de la credencial con ámbito de base de datos, ya que no se usará para la autenticación en Azure Storage.
-- 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 (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
Pasos siguientes
* SQL Managed Instance *
Introducción: Instancia administrada de Azure SQL
Se aplica a: Azure SQL Instancia administrada
Crea un origen de datos externo en Azure SQL Managed Instance. Para obtener información completa, consulte Virtualización de datos con Azure SQL Managed Instance.
La virtualización de datos de Azure SQL Managed Instance proporciona acceso a datos externos en diversos formatos de archivo a través de la sintaxis T-SQL OPENROWSET o la sintaxis T-SQL CREATE EXTERNAL TABLE.
Syntax
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
)
[ ; ]
Argumentos
data_source_name
Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único en la base de datos.
LOCATION = '<prefix>://<path[:port]>'
Proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo.
Origen de datos externo | Prefijo de ubicación | Ruta de acceso de ubicación |
---|---|---|
Azure Blob Storage | abs |
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name> |
Azure Data Lake Service Gen2 | adls |
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name> |
El motor de base de datos no comprueba la existencia del origen de datos externo cuando se crea el objeto. Para la validación, crea una tabla externa utilizando el origen de datos externo.
No coloque un / final, nombre de archivo o parámetros de firma de acceso compartido al final de la dirección URL de LOCATION
al configurar un origen de datos externo para las operaciones masivas.
CREDENTIAL = credential_name
Especifica una credencial de ámbito de base de datos para la autenticación en el origen de datos externo.
Instrucciones y notas adicionales cuando se crea una credencial:
- Para cargar datos de Azure Storage en Azure SQL Managed Instance, use una firma de acceso compartido (token de SAS).
CREDENTIAL
solo se necesita si se han protegido los datos.CREDENTIAL
no es necesario para los conjuntos de datos que permiten el acceso anónimo.- Si se requiere una credencial, esta se debe crear mediante
Managed Identity
oSHARED ACCESS SIGNATURE
como IDENTITY. Para crear una credencial con ámbito de base de datos, vea CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
Para usar la identidad de servicio administrada para la credencial con ámbito de base de datos:
Especifique
WITH IDENTITY = 'Managed Identity'
.- Use la identidad de servicio administrada asignada por el sistema de Azure SQL Managed Instance, que debe habilitarse si se va a usar con este fin.
Conceda el rol RBAC de Azure Lector para la identidad de servicio administrada asignada por el sistema de Azure SQL Managed Instance a los contenedores de Azure Blob Storage necesarios. Por ejemplo, mediante Azure Portal; consulte Asignación de roles de Azure mediante Azure Portal.
Para crear una firma de acceso compartido (SAS) para la credencial con ámbito de base de datos:
Especifique
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = ...
.Hay varias maneras de crear una firma de acceso compartido:
- Para obtener un token de SAS, vaya a Azure portal ><Su_cuenta_de_almacenamiento> -> Firma de acceso compartido -> Configurar permisos -> Generar la cadena de conexión y SAS. Para más información, consulte Generación de una firma de acceso compartido.
- Puede crear y configurar una SAS con Explorador de Azure Storage.
- Puede crear una SAS mediante programación a través de PowerShell, la CLI de Azure, .NET y la API de REST. Para obtener más información, consulte Otorgar acceso limitado a recursos de Azure Storage con firmas de acceso compartido (SAS).
El token de SAS debe configurarse del modo siguiente:
- Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
?
inicial cuando se configura como SECRET. - Usar un período de caducidad válido (todas las fechas se encuentran en hora UTC).
- Conceder al menos permiso de lectura en el archivo que se debe cargar (por ejemplo,
srt=o&sp=r
). Se pueden crear varias firmas de acceso compartido para diferentes casos de uso. Los permisos se deben conceder de la siguiente manera:
Acción Permiso Leer datos desde un archivo Lectura Leer datos de varios archivos y subcarpetas Lectura y enumeración Usar Create External Table as Select (CETAS) Lectura, creación y escritura - Cuando se genera un token de SAS, este incluye un signo de interrogación ("?") al principio del token. Excluya el
Permisos
Requiere el permiso CONTROL
en la base de datos en Azure SQL Managed Instance.
Bloqueo
Toma un bloqueo compartido en el objeto EXTERNAL DATA SOURCE
.
Ejemplos
Para ver más ejemplos, consulte Virtualización de datos con Azure SQL Managed Instance.
A Consulta de datos externos de Azure SQL Managed Instance con OPENROWSET o una tabla externa
Para ver más ejemplos, consulte Creación de un origen de datos externo o consulte Virtualización de datos con Azure SQL Managed Instance.
Cree la clave maestra de la base de datos, si no existe.
-- Optional: Create MASTER KEY if it doesn't exist in the database: CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong Password>' GO
Cree la credencial con ámbito de base de datos mediante un token de SAS. También puede usar una identidad administrada.
CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<KEY>' ; --Removing leading '?' GO
Cree el origen de datos externo con la credencial.
--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] );
Consulte el archivo de datos de Parquet en el origen de datos externo mediante la sintaxis T-SQL OPENROWSET, basándose en la inferencia de esquemas para explorar rápidamente los datos sin conocer el esquema.
--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;
O bien, consulte los datos mediante OPENROWSET con la cláusula WITH, en lugar de usar la inferencia de esquemas, que puede consultar el costo de ejecución. La inferencia de esquemas no se admite en archivos .csv.
--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;
También puede crear un FORMATO DE ARCHIVO EXTERNO y una TABLA EXTERNA para consultar los datos como una tabla local.
-- 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