CREATE EXTERNAL DATA SOURCE (Transact-SQL)
Cria uma fonte de dados externa para consultar usando SQL Server, Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure, Azure Synapse Analytics, Analytics Platform System (PDW) ou SQL do Azure no Edge.
Este artigo fornece a sintaxe, os argumentos, os comentários, as permissões e os exemplos de qualquer produto SQL que você escolher.
Selecionar um produto
Na linha a seguir, selecione o nome do produto em que você tem interesse e somente as informações do produto serão exibidas.
* SQL Server *
Visão geral: SQL Server 2016
Aplica-se a: SQL Server 2016 (13.x) e versões posteriores
Cria uma fonte de dados externa para consultas do PolyBase. Fontes de dados externas são usadas para estabelecer a conectividade e dar suporte a estes casos de uso principal:
- Virtualização de dados e carregamento dados usando o PolyBase
- Operações de carregamento em massa usando
BULK INSERT
ouOPENROWSET
Observação
Essa sintaxe varia entre as versões do SQL Server. Use a lista suspensa do seletor de versão para escolher a versão apropriada.
Para ver os recursos do SQL Server 2019 (15.x), confira CREATE EXTERNAL DATA SOURCE.
Para exibir os recursos do SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Sintaxe para SQL Server 2016
Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe 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 o nome da fonte de dados definido pelo usuário. O nome deve ser exclusivo no banco de dados no SQL Server.
LOCALIZAÇÃO = '<prefix>://<path[:port]>'
Fornece o protocolo de conectividade e o caminho para a fonte de dados externa.
Fonte de dados externa | Prefixo de localização do conector | Caminho de local | Locais com suporte por produto/serviço | Autenticação |
---|---|---|---|---|
Cloudera CDH ou Hortonworks HDP | hdfs |
<Namenode>[:port] |
SQL Server 2016 (13.x) ao SQL Server 2019 (15.x) | Autenticação anônima ou básica |
Conta de Armazenamento do Azure (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
A partir do SQL Server 2016 (13.x) Não há suporte para o namespace hierárquico |
Chave de conta de Armazenamento do Microsoft Azure |
Caminho de local:
<Namenode>
= o nome do computador, o URI do serviço de nome ou o endereço IP doNamenode
no cluster do Hadoop. O PolyBase deve resolver qualquer nome DNS usado pelo cluster do Hadoop.port
= a porta em que fonte de dados externa está escutando. No Hadoop, a porta pode ser encontrada usando o parâmetro de configuraçãofs.defaultFS
. O padrão é 8020.<container>
= o contêiner da conta de armazenamento que contém os dados. Os contêineres raiz são somente leitura, não é possível gravar dados no contêiner.<storage_account>
= o nome da conta de armazenamento do recurso do Azure.<server_name>
= o nome de host.<instance_name>
= o nome de uma instância nomeada do SQL Server. Usado se você tiver o Serviço SQL Server Browser em execução na instância de destino.
Observações e orientação adicionais ao definir o local:
- O Mecanismo de Banco de Dados do SQL Server não verifica a existência da fonte de dados externa quando o objeto é criado. Para validar, crie uma tabela externa usando a fonte de dados externa.
- Use a mesma fonte de dados externa para todas as tabelas ao consultar o Hadoop para garantir semântica de consulta consistente.
- O uso do
wasbs
é opcional, mas recomendado no SQL Server 2016 (13.x) para acessar as Contas de Armazenamento do Azure, pois os dados serão enviados usando uma conexão TLS/SSL segura. - Para garantir consultas do PolyBase com êxito durante um failover
Namenode
do Hadoop, considere usar um endereço IP virtual para oNamenode
do cluster do Hadoop. Se você não fizer isso, execute um comando ALTER EXTERNAL DATA SOURCE para apontar para o novo local.
CREDENTIAL = credential_name
Especifica uma credencial no escopo do banco de dados para a autenticação na fonte de dados externa.
CREDENTIAL
será necessário apenas se os dados tiverem sido protegidos. CREDENTIAL
não é necessário para conjuntos de dados que permitem acesso anônimo.
Para criar uma credencial no escopo do banco de dados, veja CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
TYPE = [ HADOOP ]
Especifica o tipo de fonte de dados externa que está sendo configurada. No SQL Server 2016, esse parâmetro é sempre necessário e só deve ser especificado como HADOOP
. Dá suporte a conexões com o Cloudera CDH, Hortonworks HDP ou uma conta do Armazenamento do Microsoft Azure. O comportamento desse parâmetro é diferente em versões posteriores do SQL Server.
Para obter um exemplo de como usar TYPE
= HADOOP
para carregar dados de uma conta de Armazenamento do Azure, consulte Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface wasb://
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
Configure esse valor opcional ao se conectar à Cloudera CDH, ao Hortonworks HDP ou a uma conta de Armazenamento do Microsoft Azure. Para ver uma lista completa de versões do Hadoop compatíveis, veja Configuração de conectividade do PolyBase (Transact-SQL).
Quando o RESOURCE_MANAGER_LOCATION
for definido, o otimizador de consulta tomará uma decisão baseada em custo para aprimorar o desempenho. Um trabalho MapReduce pode ser usado para aplicar a computação para o Hadoop. Especificar o RESOURCE_MANAGER_LOCATION
pode reduzir significativamente o volume de dados transferidos entre o Hadoop e o SQL Server, o que pode levar a um desempenho de consultas aprimorado.
Se o Resource Manager não tiver sido especificado, o envio de computação por push para o Hadoop estará desabilitado para consultas do PolyBase. Criar fonte de dados externa para referenciar o Hadoop com aplicação habilitada apresenta um exemplo concreto e diretrizes adicionais.
O valor de RESOURCE_MANAGER_LOCATION e não é validado quando você cria a fonte de dados externa. Inserir um valor incorreto pode causar falha de consulta em tempo de execução sempre que for feita uma tentativa de aplicação, uma vez que o valor fornecido não poderá ser resolvido.
Para que o PolyBase funcione corretamente com uma fonte de dados externa do Hadoop, as portas para os seguintes componentes de cluster do Hadoop devem estar abertas:
- Portas HDFS
- Namenode
- DataNode
- Resource Manager
- Envio do trabalho
- Histórico de trabalho
Se a porta não for especificada, o valor padrão será escolhido usando a definição atual da configuração 'conectividade do Hadoop'.
Conectividade do Hadoop | Porta do Gerenciador de Recursos padrão |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
A tabela a seguir mostra as portas padrão para esses componentes. Há uma dependência de versão do Hadoop e a possibilidade de configuração personalizada que não usa a atribuição de porta padrão.
Componente do cluster do Hadoop | Pipe Padrão |
---|---|
NameNode | 8020 |
DataNode (transferência de dados, porta IPC sem privilégios) | 50010 |
DataNode (transferência de dados, porta IPC com privilégios) | 1019 |
Envio de Trabalho do Resource Manager (Hortonworks 1.3) | 50300 |
Envio de Trabalho do Resource Manager (Cloudera 4.3) | 8021 |
Envio de Trabalho do Resource Manager (Hortonworks 2.0 no Windows, Cloudera 5.x no Linux) | 8032 |
Envio de Trabalho do Resource Manager (Hortonworks 2.x, 3.0 no Linux, Hortonworks 2.1-3 no Windows) | 8050 |
Histórico de Trabalhos do Resource Manager | 10020 |
Permissões
Requer a permissão CONTROL
no banco de dados no SQL Server.
Bloqueio
Usa um bloqueio compartilhado no objeto EXTERNAL DATA SOURCE
.
Segurança
O PolyBase dá suporte para autenticação baseada em proxy para a maioria das fontes de dados externas. Crie uma credencial no escopo do banco de dados para criar a conta proxy.
Exemplos
Importante
Para obter informações sobre como instalar e habilitar o PolyBase, confira Instalar o PolyBase no Windows
a. Criar uma fonte de dados externa para referenciar o Hadoop
Para criar uma fonte de dados externa para referenciar o cluster do Hadoop do Hortonworks HDP ou do Cloudera CDH, especifique o nome do computador ou o endereço IP do Namenode
do Hadoop e a porta.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. Criar uma fonte de dados externa para referenciar o Hadoop com aplicação habilitada
Especifique a opção RESOURCE_MANAGER_LOCATION
para habilitar a computação de aplicação para Hadoop em consultas do PolyBase. Uma vez habilitado, o PolyBase toma uma decisão baseada em custo para determinar se a computação de consulta deve ser enviada por push para o 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. Criar uma fonte de dados externa para referenciar o Hadoop protegido pelo Kerberos
Para verificar se o cluster do Hadoop está protegido pelo Kerberos, verifique o valor da propriedade hadoop.security.authentication
no core-site.xml do Hadoop. Para referenciar um cluster do Hadoop protegido pelo Kerberos, você precisa especificar uma credencial no escopo do banco de dados contendo o nome de usuário e a senha do Kerberos. A chave mestra do banco de dados é usada para criptografar o segredo da credencial no escopo do banco de dados.
-- 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. Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface wasb://
Neste exemplo, a fonte de dados externa é uma conta de Armazenamento do Azure V2 chamada logs
. O contêiner de armazenamento é chamado daily
. A fonte de dados externa do Armazenamento do Azure destina-se somente a transferência de dados. Não dá suporte a aplicação de predicado. Não há suporte para namespaces hierárquicos ao acessar dados por meio da interface wasb://
.
Este exemplo mostra como criar a credencial no escopo do banco de dados para autenticação na conta de Armazenamento do Azure V2. Especifique a chave de conta de Armazenamento do Azure no segredo da credencial do banco de dados. Você pode especificar qualquer cadeia de caracteres na identidade da credencial no escopo do banco de dados, pois ela não será usada durante a autenticação no Armazenamento do Azure. Observe que, ao se conectar ao Armazenamento do Azure por meio do conector WASB[s], a autenticação deve ser feita com uma chave de conta de armazenamento, não com uma SAS (assinatura de acesso compartilhado).
No SQL Server 2016 (13.x), TYPE
deve ser definido como HADOOP
mesmo ao acessar o Armazenamento do Azure.
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
Próximas etapas
Visão geral: SQL Server 2017
Aplica-se a: somente SQL Server 2017 (14.x)
Cria uma fonte de dados externa para consultas do PolyBase. Fontes de dados externas são usadas para estabelecer a conectividade e dar suporte a estes casos de uso principal:
- Virtualização de dados e carregamento dados usando o PolyBase
- Operações de carregamento em massa usando
BULK INSERT
ouOPENROWSET
Observação
Essa sintaxe varia entre as versões do SQL Server em Linux. Use a lista suspensa do seletor de versão para escolher a versão apropriada.
Para ver os recursos do SQL Server 2019 (15.x), confira CREATE EXTERNAL DATA SOURCE.
Para exibir os recursos do SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Observação
Essa sintaxe varia entre as versões do SQL Server. Use a lista suspensa do seletor de versão para escolher a versão apropriada.
Para ver os recursos do SQL Server 2019 (15.x), confira CREATE EXTERNAL DATA SOURCE.
Para exibir os recursos do SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Sintaxe do SQL Server 2017
Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe 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 o nome da fonte de dados definido pelo usuário. O nome deve ser exclusivo no banco de dados no SQL Server.
LOCALIZAÇÃO = '<prefix>://<path[:port]>'
Fornece o protocolo de conectividade e o caminho para a fonte de dados externa.
Fonte de dados externa | Prefixo de localização do conector | Caminho de local | Locais com suporte por produto/serviço | Autenticação |
---|---|---|---|---|
Cloudera CDH ou Hortonworks HDP | hdfs |
<Namenode>[:port] |
SQL Server 2016 (13.x) ao SQL Server 2019 (15.x) somente | Autenticação anônima ou básica |
Conta de Armazenamento do Azure (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
A partir do SQL Server 2016 (13.x) Não há suporte para o namespace hierárquico |
Chave de conta de Armazenamento do Microsoft Azure |
Operações em Massa | https |
<storage_account>.blob.core.windows.net/<container> |
A partir do SQL Server 2017 (14.x) | Assinatura de acesso compartilhado (SAS) |
Caminho de local:
<
Namenode>
= o nome do computador, o URI do serviço de nome ou o endereço IP doNamenode
no cluster do Hadoop. O PolyBase deve resolver qualquer nome DNS usado pelo cluster do Hadoop.port
= a porta em que fonte de dados externa está escutando. No Hadoop, a porta pode ser encontrada usando o parâmetro de configuraçãofs.defaultFS
. O padrão é 8020.<container>
= o contêiner da conta de armazenamento que contém os dados. Os contêineres raiz são somente leitura, não é possível gravar dados no contêiner.<storage_account>
= o nome da conta de armazenamento do recurso do Azure.<server_name>
= o nome de host.<instance_name>
= o nome de uma instância nomeada do SQL Server. Usado se você tiver o Serviço SQL Server Browser em execução na instância de destino.
Observações e orientação adicionais ao definir o local:
- O Mecanismo de Banco de Dados do SQL Server não verifica a existência da fonte de dados externa quando o objeto é criado. Para validar, crie uma tabela externa usando a fonte de dados externa.
- Use a mesma fonte de dados externa para todas as tabelas ao consultar o Hadoop para garantir semântica de consulta consistente.
- Especifique o
Driver={<Name of Driver>}
ao se conectar por meio deODBC
. - O uso do
wasbs
é opcional, mas recomendado no SQL Server 2017 (14.x) para acessar as Contas de Armazenamento do Azure, pois os dados serão enviados usando uma conexão TLS/SSL segura. - Para garantir consultas do PolyBase com êxito durante um failover
Namenode
do Hadoop, considere usar um endereço IP virtual para oNamenode
do cluster do Hadoop. Se você não fizer isso, execute um comando ALTER EXTERNAL DATA SOURCE para apontar para o novo local.
CREDENTIAL = credential_name
Especifica uma credencial no escopo do banco de dados para a autenticação na fonte de dados externa.
Observações e orientações adicionais ao criar uma credencial:
CREDENTIAL
será necessário apenas se os dados tiverem sido protegidos.CREDENTIAL
não é necessário para conjuntos de dados que permitem acesso anônimo.- Quando o
TYPE
=BLOB_STORAGE
, a credencial precisa ser criada usandoSHARED ACCESS SIGNATURE
como a identidade. TYPE
=BLOB_STORAGE
só é permitido para operações em massa; não é possível criar tabelas externas para uma fonte de dados externa comTYPE
=BLOB_STORAGE
.- Observe que, ao se conectar ao Armazenamento do Azure por meio do conector WASB[s], a autenticação deve ser feita com uma chave de conta de armazenamento, não com uma SAS (assinatura de acesso compartilhado).
- Quando
TYPE
=HADOOP
, a credencial deve ser criada usando a chave de conta de armazenamento como oSECRET
.
Há várias maneiras de criar uma assinatura de acesso compartilhado:
Você pode criar um token SAS navegando até o Portal do Azure -><Sua_Conta_de_Armazenamento> –> Assinatura de Acesso Compartilhado –> Configurar permissões –> Gerar SAS e cadeia de conexão. Para obter mais informações, confira Gerar uma assinatura de acesso compartilhado.
Você pode criar e configurar uma SAS com o Gerenciador de Armazenamento do Azure.
Você pode criar uma SAS programaticamente por meio do PowerShell, da CLI do Azure, do .NET e da API REST. Para obter mais informações, confira Conceder acesso limitado a recursos de Armazenamento do Azure usando SAS (assinaturas de acesso compartilhado).
O token SAS deve ser configurado da seguinte maneira:
- Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
?
à esquerda quando configurado como o SEGREDO. - Use um período de término válido (todas as datas estão no horário UTC).
- Conceder pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo
srt=o&sp=r
). Várias assinaturas de acesso compartilhado podem ser criadas para diferentes casos de uso. As permissões devem ser concedidas da seguinte maneira:
Ação Permissão Ler dados de um arquivo Ler Leitura de dados de vários arquivos e subpastas Leitura e listagem - Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
Para obter um exemplo de como usar um CREDENTIAL
com SHARED ACCESS SIGNATURE
e TYPE
= BLOB_STORAGE
, confira Criar uma fonte de dados externa para executar operações em massa e recuperar dados do Armazenamento do Azure no Banco de Dados SQL
Para criar uma credencial no escopo do banco de dados, veja CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
TYPE = [ HADOOP | BLOB_STORAGE ]
Especifica o tipo de fonte de dados externa que está sendo configurada. Esse parâmetro nem sempre é necessário e só deve ser especificado ao se conectar ao Cloudera CDH, ao Hortonworks HDP, a uma conta de Armazenamento do Microsoft Azure ou a um Azure Data Lake Storage Gen2.
- Use
HADOOP
quando a fonte de dados externa for o Cloudera CDH, o Hortonworks HDP, uma conta do de Armazenamento do Microsoft Azure ou um Azure Data Lake Storage Gen2. - Use
BLOB_STORAGE
ao executar operações em massa da conta de Armazenamento do Azure usando BULK INSERT ou OPENROWSET. Introduzido com o SQL Server 2017 (14.x). UseHADOOP
quando pretender criar uma tabela externa no Armazenamento do Azure.
Observação
TYPE
deve ser definido como HADOOP
mesmo ao acessar o Armazenamento do Azure.
Para obter um exemplo de como usar TYPE
= HADOOP
para carregar dados de uma conta de Armazenamento do Azure, consulte Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface wasb://
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
Configure esse valor opcional ao se conectar à Cloudera CDH, ao Hortonworks HDP ou a uma conta de Armazenamento do Microsoft Azure. Para ver uma lista completa de versões do Hadoop compatíveis, veja Configuração de conectividade do PolyBase (Transact-SQL).
Quando o RESOURCE_MANAGER_LOCATION
for definido, o otimizador de consulta tomará uma decisão baseada em custo para melhorar o desempenho. Um trabalho MapReduce pode ser usado para aplicar a computação para o Hadoop. Especificar o RESOURCE_MANAGER_LOCATION
pode reduzir significativamente o volume de dados transferidos entre o Hadoop e o SQL Server, o que pode levar a um desempenho de consultas aprimorado.
Se o Resource Manager não tiver sido especificado, o envio de computação por push para o Hadoop estará desabilitado para consultas do PolyBase. Criar fonte de dados externa para referenciar o Hadoop com aplicação habilitada apresenta um exemplo concreto e diretrizes adicionais.
O valor de RESOURCE_MANAGER_LOCATION e não é validado quando você cria a fonte de dados externa. Inserir um valor incorreto pode causar falha de consulta em tempo de execução sempre que for feita uma tentativa de aplicação, uma vez que o valor fornecido não poderá ser resolvido.
Para que o PolyBase funcione corretamente com uma fonte de dados externa do Hadoop, as portas para os seguintes componentes de cluster do Hadoop devem estar abertas:
- Portas HDFS
- Namenode
- DataNode
- Resource Manager
- Envio do trabalho
- Histórico de trabalho
Se a porta não for especificada, o valor padrão será escolhido usando a definição atual da configuração 'conectividade do Hadoop'.
Conectividade do Hadoop | Porta do Gerenciador de Recursos padrão |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
A tabela a seguir mostra as portas padrão para esses componentes. Existe uma dependência de versão do Hadoop e a possibilidade de configuração personalizada que não usa a atribuição de porta padrão.
Componente do cluster do Hadoop | Pipe Padrão |
---|---|
NameNode | 8020 |
DataNode (transferência de dados, porta IPC sem privilégios) | 50010 |
DataNode (transferência de dados, porta IPC com privilégios) | 1019 |
Envio de Trabalho do Resource Manager (Hortonworks 1.3) | 50300 |
Envio de Trabalho do Resource Manager (Cloudera 4.3) | 8021 |
Envio de Trabalho do Resource Manager (Hortonworks 2.0 no Windows, Cloudera 5.x no Linux) | 8032 |
Envio de Trabalho do Resource Manager (Hortonworks 2.x, 3.0 no Linux, Hortonworks 2.1-3 no Windows) | 8050 |
Histórico de Trabalhos do Resource Manager | 10020 |
Permissões
Requer a permissão CONTROL
no banco de dados no SQL Server.
Bloqueio
Usa um bloqueio compartilhado no objeto EXTERNAL DATA SOURCE
.
Segurança
O PolyBase dá suporte para autenticação baseada em proxy para a maioria das fontes de dados externas. Crie uma credencial no escopo do banco de dados para criar a conta proxy.
No momento, não há suporte para um token SAS com o tipo HADOOP
. Só há suporte para o tipo = BLOB_STORAGE
quando uma chave de acesso da conta de armazenamento é usada. Tentar criar uma fonte de dados externa com o tipo HADOOP
e uma credencial SAS falha com o seguinte erro:
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.'
Exemplos
Importante
Para obter informações sobre como instalar e habilitar o PolyBase, confira Instalar o PolyBase no Windows
a. Criar uma fonte de dados externa para referenciar o Hadoop
Para criar uma fonte de dados externa para referenciar o cluster do Hadoop do Hortonworks HDP ou do Cloudera CDH, especifique o nome do computador ou o endereço IP do Namenode
do Hadoop e a porta.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. Criar uma fonte de dados externa para referenciar o Hadoop com aplicação habilitada
Especifique a opção RESOURCE_MANAGER_LOCATION
para habilitar a computação de aplicação para Hadoop em consultas do PolyBase. Uma vez habilitado, o PolyBase toma uma decisão baseada em custo para determinar se a computação de consulta deve ser enviada por push para o 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. Criar uma fonte de dados externa para referenciar o Hadoop protegido pelo Kerberos
Para verificar se o cluster do Hadoop está protegido pelo Kerberos, verifique o valor da propriedade hadoop.security.authentication
no core-site.xml do Hadoop. Para referenciar um cluster do Hadoop protegido pelo Kerberos, você precisa especificar uma credencial no escopo do banco de dados contendo o nome de usuário e a senha do Kerberos. A chave mestra do banco de dados é usada para criptografar o segredo da credencial no escopo do banco de dados.
-- 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. Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface wasb://
Neste exemplo, a fonte de dados externa é uma conta de Armazenamento do Azure V2 chamada logs
. O contêiner de armazenamento é chamado daily
. A fonte de dados externa do Armazenamento do Azure destina-se somente a transferência de dados. Não dá suporte a aplicação de predicado. Não há suporte para namespaces hierárquicos ao acessar dados por meio da interface wasb://
. Observe que, ao se conectar ao Armazenamento do Azure por meio do conector WASB[s], a autenticação deve ser feita com uma chave de conta de armazenamento, não com uma SAS (assinatura de acesso compartilhado).
Este exemplo mostra como criar a credencial no escopo do banco de dados para autenticação na conta de Armazenamento do Azure V2. Especifique a chave de conta de Armazenamento do Azure no segredo da credencial do banco de dados. Você pode especificar qualquer cadeia de caracteres na identidade da credencial no escopo do banco de dados, pois ela não será usada durante a autenticação no Armazenamento do Azure.
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
Exemplos: operações em massa
Importante
Não adicione parâmetros de assinatura de acesso compartilhado, nome de arquivo ou / à direita no fim da URL LOCATION
ao configurar uma fonte de dados externa para operações em massa.
E. Criar uma fonte de dados externa para operações em massa recuperando dados do Armazenamento do Azure
Aplica-se a: SQL Server 2017 (14.x) e posterior.
Use a seguinte fonte de dados para operações em massa com BULK INSERT ou OPENROWSET. A credencial deve ser definida como SHARED ACCESS SIGNATURE
como a identidade, não deve ter o ?
à esquerda no token SAS, deve ter pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo srt=o&sp=r
), e o período de término deve ser válido (todas as datas estão no horário UTC). Para mais informações sobre assinaturas de acesso compartilhado, consulte Usando SAS (Assinatura de Acesso Compartilhado).
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 esse exemplo em uso, confira o exemplo BULK INSERT.
Próximas etapas
- ALTER EXTERNAL DATA SOURCE (Transact-SQL)
- CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
- CREATE EXTERNAL FILE FORMAT (Transact-SQL)
- CREATE EXTERNAL TABLE (Transact-SQL)
- sys.external_data_sources (Transact-SQL)
- Usando SAS (Assinatura de Acesso Compartilhado)
- Configuração de conectividade do PolyBase
Visão geral: SQL Server 2019
Aplica-se a: SQL Server 2019 (15.x) e posterior
Cria uma fonte de dados externa para consultas do PolyBase. Fontes de dados externas são usadas para estabelecer a conectividade e dar suporte a estes casos de uso principal:
- Virtualização de dados e carregamento dados usando o PolyBase
- Operações de carregamento em massa usando
BULK INSERT
ouOPENROWSET
Observação
Essa sintaxe varia entre as versões do SQL Server. Use a lista suspensa do seletor de versão para escolher a versão apropriada.
Para exibir os recursos do SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Observação
Essa sintaxe varia entre as versões do SQL Server. Use a lista suspensa do seletor de versão para escolher a versão apropriada.
Para exibir os recursos do SQL Server 2022 (16.x), visite CREATE EXTERNAL DATA SOURCE.
Sintaxe do SQL Server 2019
Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe 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 o nome da fonte de dados definido pelo usuário. O nome deve ser exclusivo no banco de dados no SQL Server.
LOCALIZAÇÃO = '<prefix>://<path[:port]>'
Fornece o protocolo de conectividade e o caminho para a fonte de dados externa.
Fonte de dados externa | Prefixo de localização do conector | Caminho de local | Locais com suporte por produto/serviço | Autenticação |
---|---|---|---|---|
Cloudera CDH ou Hortonworks HDP | hdfs |
<Namenode>[:port] |
SQL Server 2016 (13.x) ao SQL Server 2019 (15.x) | Autenticação anônima ou básica |
Conta de Armazenamento do Azure (V2) | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
A partir do SQL Server 2016 (13.x) Não há suporte para o namespace hierárquico |
Chave de conta de Armazenamento do Microsoft Azure |
SQL Server | sqlserver |
<server_name>[\<instance_name>][:port] |
A partir do SQL Server 2019 (15.x) | Somente autenticação SQL |
Oracle | oracle |
<server_name>[:port] |
A partir do SQL Server 2019 (15.x) | Somente autenticação básica |
Teradata | teradata |
<server_name>[:port] |
A partir do SQL Server 2019 (15.x) | Somente autenticação básica |
API do MongoDB ou do Cosmos DB para MongoDB | mongodb |
<server_name>[:port] |
A partir do SQL Server 2019 (15.x) | Somente autenticação básica |
ODBC Genérico | odbc |
<server_name>[:port] |
Começando com o SQL Server 2019 (15.x) – somente Windows | Somente autenticação básica |
Operações em Massa | https |
<storage_account>.blob.core.windows.net/<container> |
A partir do SQL Server 2017 (14.x) | Assinatura de acesso compartilhado (SAS) |
Azure Data Lake Storage Gen2 | abfs[s] |
abfss://<container>@<storage _account>.dfs.core.windows.net |
Começando com o SQL Server 2019 (15.x) CU11+. | Chave de Acesso de Armazenamento |
Pool de dados de Clusters de Big Data do SQL Server | sqldatapool |
sqldatapool://controller-svc/default |
Suporte apenas no Clusters de Big Data do SQL Server 2019 | Somente autenticação básica |
Pool de armazenamento nos Clusters de Big Data do SQL Server | sqlhdfs |
sqlhdfs://controller-svc/default |
Suporte apenas no Clusters de Big Data do SQL Server 2019 | Somente autenticação básica |
Caminho de local:
<Namenode>
= o nome do computador, o URI do serviço de nome ou o endereço IP doNamenode
no cluster do Hadoop. O PolyBase deve resolver qualquer nome DNS usado pelo cluster do Hadoop.port
= a porta em que fonte de dados externa está escutando. No Hadoop, a porta pode ser encontrada usando o parâmetro de configuraçãofs.defaultFS
. O padrão é 8020.<container>
= o contêiner da conta de armazenamento que contém os dados. Os contêineres raiz são somente leitura, não é possível gravar dados no contêiner.<storage_account>
= o nome da conta de armazenamento do recurso do Azure.<server_name>
= o nome de host.<instance_name>
= o nome de uma instância nomeada do SQL Server. Usado se você tiver o Serviço SQL Server Browser em execução na instância de destino.
Observações e orientação adicionais ao definir o local:
- O Mecanismo de Banco de Dados do SQL Server não verifica a existência da fonte de dados externa quando o objeto é criado. Para validar, crie uma tabela externa usando a fonte de dados externa.
- Use a mesma fonte de dados externa para todas as tabelas ao consultar o Hadoop para garantir semântica de consulta consistente.
- Você pode usar o conector
sqlserver
para conectar o Microsoft SQL Server 2019 (15.x) a outro Microsoft SQL Server ou ao Banco de Dados SQL do Microsoft Azure. - Especifique o
Driver={<Name of Driver>}
ao se conectar por meio deODBC
. - O uso do
wasbs
ou doabfss
é opcional, mas recomendado no SQL Server 2019 (15.x) para acessar as Contas de Armazenamento do Microsoft Azure, pois os dados serão enviados usando uma conexão TLS/SSL segura. - As APIs
abfs
ouabfss
têm suporte para o acesso às Contas do Armazenamento do Azure a partir do SQL Server 2019 (15.x) CU11+. Para saber mais, confira O driver ABFS (sistema de arquivos de Blob do Azure). - A opção de Namespace hierárquico para Contas do Armazenamento do Microsoft Azure (V2) usando o
abfs[s]
tem suporte por meio do Azure Data Lake Storage Gen2 começando no SQL Server 2019 (15.x) CU11+. Caso contrário, a opção de namespace hierárquico não tem suporte e ela deve permanecer desabilitada. - Para garantir consultas do PolyBase com êxito durante um failover
Namenode
do Hadoop, considere usar um endereço IP virtual para oNamenode
do cluster do Hadoop. Se você não fizer isso, execute um comando ALTER EXTERNAL DATA SOURCE para apontar para o novo local. - Os tipos
sqlhdfs
esqldatapool
têm suporte para conexão entre a instância mestra e o pool de armazenamento de um cluster de Big Data. Para o Cloudera CDH ou Hortonworks HDP, usehdfs
. Para mais informações sobre como usar osqlhdfs
para consultar pools de armazenamento de Clusters de Big Data do SQL Server, confira Consultar HDFS em um cluster de Big Data do SQL Server 2019. - O suporte do SQL Server a fontes de dados externas do HDFS Cloudera (CDP) e do Hortonworks (HDP) será desativado e não será incluído no SQL Server 2022 (16.x). Para obter mais informações, confira Opções de Big Data na plataforma Microsoft SQL Server.
CONNECTION_OPTIONS = key_value_pair
Especificado para SQL Server 2019 (15.x) e posteriores. Especifica opções adicionais ao se conectar por meio de ODBC
a uma fonte de dados externa. Para usar várias opções de conexão, separe-as com ponto e vírgula.
Aplica-se a conexões ODBC
genéricas, assim como a conectores ODBC
internos para SQL Server, Oracle, Teradata, MongoDB e API do Azure Cosmos DB para MongoDB.
O key_value_pair
é a palavra-chave e o valor de uma opção de conexão específica. As palavras-chave e os valores disponíveis dependem do tipo de fonte de dados externa. O nome do driver é necessário como requisito mínimo, mas há outras opções, como APP='<your_application_name>'
ou ApplicationIntent= ReadOnly|ReadWrite
, que também são úteis de serem definidas e podem ajudar a solucionar problemas.
Os possíveis pares de chave-valor são específicos para o provedor do fornecedor da fonte de dados externa. Para obter mais informações para cada provedor, confira CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.
A partir da atualização cumulativa 19 do SQL Server 2019 (15.x), palavras-chave adicionais foram introduzidas para dar suporte a arquivos TNS do Oracle:
- A palavra-chave
TNSNamesFile
especifica o caminho para o arquivotnsnames.ora
localizado no servidor Oracle. - A palavra-chave
ServerName
especifica o alias usado emtnsnames.ora
que será utilizado para substituir o nome do host e a porta.
Pushdown = ON | OFF
Especificado apenas para SQL Server 2019 (15.x). Informa se é possível realizar a aplicação da computação para a fonte de dados externa. Essa opção é ON por padrão.
Há suporte para PUSHDOWN
ao se conectar ao SQL Server, Oracle, Teradata, MongoDB, à API do Azure Cosmos DB para MongoDB ou ao ODBC no nível da fonte de dados externa.
Habilitar ou desabilitar a aplicação no nível da consulta é feito por meio de uma dica.
CREDENTIAL = credential_name
Especifica uma credencial no escopo do banco de dados para a autenticação na fonte de dados externa.
Observações e orientações adicionais ao criar uma credencial:
CREDENTIAL
será necessário apenas se os dados tiverem sido protegidos.CREDENTIAL
não é necessário para conjuntos de dados que permitem acesso anônimo.- Quando o
TYPE
=BLOB_STORAGE
, a credencial precisa ser criada usandoSHARED ACCESS SIGNATURE
como a identidade.TYPE
=BLOB_STORAGE
só é permitido para operações em massa; não é possível criar tabelas externas para uma fonte de dados externa comTYPE
=BLOB_STORAGE
.
Há várias maneiras de criar uma assinatura de acesso compartilhado:
Você pode criar um token SAS navegando até o Portal do Azure -><Sua_Conta_de_Armazenamento> –> Assinatura de Acesso Compartilhado –> Configurar permissões –> Gerar SAS e cadeia de conexão. Para obter mais informações, confira Gerar uma assinatura de acesso compartilhado.
Você pode criar e configurar uma SAS com o Gerenciador de Armazenamento do Azure.
Você pode criar uma SAS programaticamente por meio do PowerShell, da CLI do Azure, do .NET e da API REST. Para obter mais informações, confira Conceder acesso limitado a recursos de Armazenamento do Azure usando SAS (assinaturas de acesso compartilhado).
O token SAS deve ser configurado da seguinte maneira:
- Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
?
à esquerda quando configurado como o SEGREDO. - Use um período de término válido (todas as datas estão no horário UTC).
- Conceder pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo
srt=o&sp=r
). Várias assinaturas de acesso compartilhado podem ser criadas para diferentes casos de uso. As permissões devem ser concedidas da seguinte maneira:
Ação Permissão Ler dados de um arquivo Ler Leitura de dados de vários arquivos e subpastas Leitura e listagem - Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
Para obter um exemplo de como usar um CREDENTIAL
com SHARED ACCESS SIGNATURE
e TYPE
= BLOB_STORAGE
, confira Criar uma fonte de dados externa para executar operações em massa e recuperar dados do Armazenamento do Azure no Banco de Dados SQL
Para criar uma credencial no escopo do banco de dados, veja CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
TYPE = [ HADOOP | BLOB_STORAGE ]
Especifica o tipo de fonte de dados externa que está sendo configurada. Esse parâmetro nem sempre é necessário e só deve ser especificado ao se conectar ao Cloudera CDH, ao Hortonworks HDP, a uma conta de Armazenamento do Microsoft Azure ou a um Azure Data Lake Storage Gen2.
- No SQL Server 2019 (15.x), não especifique TYPE, a menos que esteja conectando ao Cloudera CDH, Hortonworks HDP, uma conta de Armazenamento Azure.
- Use
HADOOP
quando a fonte de dados externa for o Cloudera CDH, o Hortonworks HDP, uma conta do de Armazenamento do Microsoft Azure ou um Azure Data Lake Storage Gen2. - Use
BLOB_STORAGE
ao executar operações em massa da conta de Armazenamento do Azure usando BULK INSERT ou OPENROWSET com o SQL Server 2017 (14.x). UseHADOOP
quando pretender criar uma tabela externa no Armazenamento do Azure. - O suporte do SQL Server a fontes de dados externas do HDFS Cloudera (CDP) e do Hortonworks (HDP) será desativado e não será incluído no SQL Server 2022 (16.x). Para obter mais informações, confira Opções de Big Data na plataforma Microsoft SQL Server.
Para obter um exemplo de como usar TYPE
= HADOOP
para carregar dados de uma conta de Armazenamento do Azure, consulte Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface wasb://
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
No SQL Server 2019 (15.x), não especifique RESOURCE_MANAGER_LOCATION a menos que se conecte ao Cloudera CDH, Hortonworks HDP, uma conta Armazenamento Azure.
Configure esse valor opcional ao se conectar à Cloudera CDH, ao Hortonworks HDP ou a uma conta de Armazenamento do Microsoft Azure. Para ver uma lista completa de versões do Hadoop compatíveis, veja Configuração de conectividade do PolyBase (Transact-SQL).
Quando o RESOURCE_MANAGER_LOCATION
for definido, o otimizador de consulta tomará uma decisão baseada em custo para aprimorar o desempenho. Um trabalho MapReduce pode ser usado para aplicar a computação para o Hadoop. Especificar o RESOURCE_MANAGER_LOCATION
pode reduzir significativamente o volume de dados transferidos entre o Hadoop e o SQL Server, o que pode levar a um desempenho de consultas aprimorado.
Se o Resource Manager não tiver sido especificado, o envio de computação por push para o Hadoop estará desabilitado para consultas do PolyBase. Criar fonte de dados externa para referenciar o Hadoop com aplicação habilitada apresenta um exemplo concreto e diretrizes adicionais.
O valor de RESOURCE_MANAGER_LOCATION e não é validado quando você cria a fonte de dados externa. Inserir um valor incorreto pode causar falha de consulta em tempo de execução sempre que for feita uma tentativa de aplicação, uma vez que o valor fornecido não poderá ser resolvido.
Para que o PolyBase funcione corretamente com uma fonte de dados externa do Hadoop, as portas para os seguintes componentes de cluster do Hadoop devem estar abertas:
- Portas HDFS
- Namenode
- DataNode
- Resource Manager
- Envio do trabalho
- Histórico de trabalho
Se a porta não for especificada, o valor padrão será escolhido usando a definição atual da configuração 'conectividade do Hadoop'.
Conectividade do Hadoop | Porta do Gerenciador de Recursos padrão |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
8 | 8032 |
A tabela a seguir mostra as portas padrão para esses componentes. Existe uma dependência de versão do Hadoop e a possibilidade de configuração personalizada que não usa a atribuição de porta padrão.
Componente do cluster do Hadoop | Pipe Padrão |
---|---|
NameNode | 8020 |
DataNode (transferência de dados, porta IPC sem privilégios) | 50010 |
DataNode (transferência de dados, porta IPC com privilégios) | 1019 |
Envio de Trabalho do Resource Manager (Hortonworks 1.3) | 50300 |
Envio de Trabalho do Resource Manager (Cloudera 4.3) | 8021 |
Envio de Trabalho do Resource Manager (Hortonworks 2.0 no Windows, Cloudera 5.x no Linux) | 8032 |
Envio de Trabalho do Resource Manager (Hortonworks 2.x, 3.0 no Linux, Hortonworks 2.1-3 no Windows) | 8050 |
Histórico de Trabalhos do Resource Manager | 10020 |
Permissões
Requer a permissão CONTROL
no banco de dados no SQL Server.
Bloqueio
Usa um bloqueio compartilhado no objeto EXTERNAL DATA SOURCE
.
Segurança
O PolyBase dá suporte para autenticação baseada em proxy para a maioria das fontes de dados externas. Crie uma credencial no escopo do banco de dados para criar a conta proxy.
Quando você se conecta ao pool de armazenamento ou de dados em um cluster de Big Data do SQL Server 2019, as credenciais do usuário são passadas para o sistema de back-end. Crie logons no pool de dados propriamente dito para habilitar a autenticação de passagem.
No momento, não há suporte para um token SAS com o tipo HADOOP
. Só há suporte para o tipo = BLOB_STORAGE
quando uma chave de acesso da conta de armazenamento é usada. Tentar criar uma fonte de dados externa com o tipo HADOOP
e uma credencial SAS falha com o seguinte erro:
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.'
Exemplos
Importante
Para obter informações sobre como instalar e habilitar o PolyBase, confira Instalar o PolyBase no Windows
a. Criar fonte de dados externa no SQL Server 2019 para referenciar o Oracle
Para criar uma fonte de dados externa que faça referência ao Oracle, verifique se que você tem uma credencial no escopo do banco de dados. Opcionalmente, você também poderá habilitar ou desabilitar a aplicação de computação em relação a essa fonte de dados.
-- 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, a fonte de dados externa para o Oracle pode usar a autenticação de proxy para fornecer controle de acesso refinado. Um usuário de proxy pode ser configurado para ter acesso limitado em comparação com o usuário que está sendo representado.
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, é possível usar a autenticação TNS.
A partir da atualização cumulativa 19 do SQL Server 2019 (15.x), CREATE EXTERNAL DATA SOURCE
agora dá suporte ao uso de arquivos TNS ao conectar-se ao Oracle.
O parâmetro CONNECTION_OPTIONS
foi ampliado e agora utiliza TNSNamesFile
e ServerName
como variáveis para procurar o arquivo tnsnames.ora
e estabelecer conexão com o servidor.
No exemplo abaixo, durante o runtime, o SQL Server procura o local do arquivo tnsnames.ora
especificado por TNSNamesFile
e o host e a porta de rede 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 ver mais exemplos para outras fontes de dados, como o MongoDB, confira Configurar o PolyBase para acessar dados externos no MongoDB.
B. Criar uma fonte de dados externa para referenciar o Hadoop
Para criar uma fonte de dados externa para referenciar o cluster do Hadoop do Hortonworks HDP ou do Cloudera CDH, especifique o nome do computador ou o endereço IP do Namenode
do Hadoop e a porta.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
C. Criar uma fonte de dados externa para referenciar o Hadoop com aplicação habilitada
Especifique a opção RESOURCE_MANAGER_LOCATION
para habilitar a computação de aplicação para Hadoop em consultas do PolyBase. Uma vez habilitado, o PolyBase toma uma decisão baseada em custo para determinar se a computação de consulta deve ser enviada por push para o 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. Criar uma fonte de dados externa para referenciar o Hadoop protegido pelo Kerberos
Para verificar se o cluster do Hadoop está protegido pelo Kerberos, verifique o valor da propriedade hadoop.security.authentication
no core-site.xml do Hadoop. Para referenciar um cluster do Hadoop protegido pelo Kerberos, você precisa especificar uma credencial no escopo do banco de dados contendo o nome de usuário e a senha do Kerberos. A chave mestra do banco de dados é usada para criptografar o segredo da credencial no escopo do banco de dados.
-- 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. Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface wasb://
Neste exemplo, a fonte de dados externa é uma conta de Armazenamento do Azure V2 chamada logs
. O contêiner de armazenamento é chamado daily
. A fonte de dados externa do Armazenamento do Azure destina-se somente a transferência de dados. Não dá suporte a aplicação de predicado. Não há suporte para namespaces hierárquicos ao acessar dados por meio da interface wasb://
. Observe que, ao se conectar ao Armazenamento do Azure por meio do conector WASB[s], a autenticação deve ser feita com uma chave de conta de armazenamento, não com uma SAS (assinatura de acesso compartilhado).
Este exemplo mostra como criar a credencial no escopo do banco de dados para autenticação na conta de Armazenamento do Azure V2. Especifique a chave de conta de Armazenamento do Azure no segredo da credencial do banco de dados. Você pode especificar qualquer cadeia de caracteres na identidade da credencial no escopo do banco de dados, pois ela não será usada durante a autenticação no Armazenamento do Azure.
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
F. Criar uma fonte de dados externa para referenciar uma instância nomeada do SQL Server por meio da conectividade do PolyBase
Aplica-se a: SQL Server 2019 (15.x) e posterior
Para criar uma fonte de dados externa que referencie uma instância nomeada do SQL Server, use CONNECTION_OPTIONS
para especificar o nome da instância.
No exemplo a seguir, WINSQL2019
é o nome do host e SQL2019
é o nome da instância. 'Server=%s\SQL2019'
é o par chave-valor.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019',
CONNECTION_OPTIONS = 'Server=%s\SQL2019',
CREDENTIAL = SQLServerCredentials
);
Como alternativa, você pode usar uma porta para se conectar a uma instância padrão do SQL Server.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019:58137',
CREDENTIAL = SQLServerCredentials
);
G. Criar fonte de dados externa para referenciar a uma réplica secundária para leitura do grupo de disponibilidade Always On
Aplica-se a: SQL Server 2019 (15.x) e posterior
Para criar uma fonte de dados externa que referencie uma réplica secundária para leitura do SQL Server, use CONNECTION_OPTIONS
para especificar ApplicationIntent=ReadOnly
.
Primeiro, crie a credencial no escopo do banco de dados, armazenando credenciais para um logon autenticado SQL. O Conector ODBC do SQL para PolyBase dá suporte apenas à autenticação básica. Antes de criar uma credencial com escopo do banco de dados, o banco de dados precisa ter uma chave mestra para proteger a credencial. Para obter mais informações, confira CREATE MASTER KEY. O exemplo a seguir cria uma credencial no escopo do banco de dados, fornece seu próprio logon e senha.
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
Em seguida, crie a fonte de dados externa.
O parâmetro Database
ODBC não é necessário. Forneça o nome do banco de dados por meio de um nome de três partes na instrução CREATE EXTERNAL TABLE, dentro do parâmetro LOCATION. Para obter um exemplo, confira CREATE EXTERNAL TABLE.
No exemplo a seguir, WINSQL2019AGL
é o nome do ouvinte do grupo de disponibilidade e dbname
é o nome do banco de dados que será o destino da instrução CREATE EXTERNAL TABLE.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = SQLServerCredentials
);
Você pode demonstrar o comportamento de redirecionamento do grupo de disponibilidade especificando ApplicationIntent
e criando uma tabela externa no modo de exibição do sistema sys.servers
. No script de exemplo a seguir, duas fontes de dados externas são criadas e uma tabela externa é criada para cada uma. Use as exibições para testar qual servidor está respondendo à conexão. Resultados semelhantes também podem ser obtidos por meio do recurso de roteamento somente leitura. Para obter informações, veja Configurar o roteamento somente leitura para um grupo de disponibilidade 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 do banco de dados no grupo de disponibilidade, crie uma exibição para retornar sys.servers
e o nome da instância local, o que ajuda a identificar qual réplica está respondendo à consulta. Para obter mais informações, consulte sys.servers.
CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO
Em seguida, crie uma tabela externa na instância de origem:
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
Exemplos: operações em massa
Importante
Não adicione parâmetros de assinatura de acesso compartilhado, nome de arquivo ou / à direita no fim da URL LOCATION
ao configurar uma fonte de dados externa para operações em massa.
H. Criar uma fonte de dados externa para operações em massa recuperando dados do Armazenamento do Azure
Aplicável ao: SQL Server 2017 (14.x) e SQL Server 2019 (15.x)
Use a seguinte fonte de dados para operações em massa com BULK INSERT ou OPENROWSET. A credencial deve ser definida como SHARED ACCESS SIGNATURE
como a identidade, não deve ter o ?
à esquerda no token SAS, deve ter pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo srt=o&sp=r
), e o período de término deve ser válido (todas as datas estão no horário UTC). Para mais informações sobre assinaturas de acesso compartilhado, consulte Usando SAS (Assinatura de Acesso Compartilhado).
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 esse exemplo em uso, confira o exemplo BULK INSERT.
I. Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface abfs://
Aplica-se a: SQL Server 2019 (15.x) CU11 e posterior
Neste exemplo, a fonte de dados externa é uma conta do Azure Data Lake Storage Gen2, logs
, usando o driver do ABFS (Azure Blob File System). O contêiner de armazenamento é chamado daily
. A fonte de dados externa do Azure Data Lake Storage Gen2 serve apenas para transferência de dados, pois não há suporte para push down de predicado.
Este exemplo mostra como criar a credencial no escopo do banco de dados para autenticação em uma conta do Azure Data Lake Storage Gen2. Especifique a chave de conta de Armazenamento do Azure no segredo da credencial do banco de dados. Você pode especificar qualquer cadeia de caracteres na identidade da credencial no escopo do banco de dados, pois ela não será usada durante a autenticação no Armazenamento do Azure.
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'abfss://daily@logs.dfs.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
J. Criar fonte de dados externa usando o ODBC genérico para o PostgreSQL
Como em exemplos anteriores, primeiro crie uma chave mestra de banco de dados e uma credencial com escopo de banco de dados. A credencial no escopo do banco de dados será usada para a fonte de dados externa. Este exemplo também pressupõe que um provedor de dados ODBC genérico para o PostgreSQL está instalado no servidor.
Neste exemplo, o provedor de dados ODBC genérico é usado para se conectar a um servidor de banco de dados PostgreSQL na mesma rede, em que o nome de domínio totalmente qualificado do servidor PostgreSQL é POSTGRES1
, usando a porta padrão TCP 5432.
CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
LOCATION = 'odbc://POSTGRES1.domain:5432',
CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
CREDENTIAL = postgres_credential
);
Próximas etapas
- ALTER EXTERNAL DATA SOURCE (Transact-SQL)
- CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
- CREATE EXTERNAL FILE FORMAT (Transact-SQL)
- CREATE EXTERNAL TABLE (Transact-SQL)
- sys.external_data_sources (Transact-SQL)
- Usando SAS (Assinatura de Acesso Compartilhado)
- Configuração de conectividade do PolyBase
Visão geral: SQL Server 2022
Aplica-se a: SQL Server 2022 (16.x) e posterior
Cria uma fonte de dados externa para consultas do PolyBase. Fontes de dados externas são usadas para estabelecer a conectividade e dar suporte a estes casos de uso principal:
- Virtualização de dados e carregamento dados usando o PolyBase
- Operações de carregamento em massa usando
BULK INSERT
ouOPENROWSET
Observação
Essa sintaxe varia entre as versões do SQL Server. Use a lista suspensa do seletor de versão para escolher a versão apropriada. Esse conteúdo se aplica ao SQL Server 2022 (16.x) e versões posteriores.
Sintaxe para o SQL Server 2022 e posteriores
Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe 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 o nome da fonte de dados definido pelo usuário. O nome deve ser exclusivo no banco de dados no SQL Server.
LOCALIZAÇÃO = '<prefix>://<path[:port]>'
Fornece o protocolo de conectividade e o caminho para a fonte de dados externa.
Fonte de dados externa | Prefixo de localização do conector | Caminho de local | Locais com suporte por produto/serviço | Autenticação |
---|---|---|---|---|
Conta de Armazenamento do Azure (V2) | abs |
abs://<container_name>@<storage_account_name>.blob.core.windows.net/ or abs://<storage_account_name>.blob.core.windows.net/<container_name> |
A partir do SQL Server 2022 (16.x) Há suporte para o namespace hierárquico. |
Assinatura de acesso compartilhado (SAS) |
Azure Data Lake Storage Gen2 | adls |
adls://<container_name>@<storage_account_name>.dfs.core.windows.net/ or adls://<storage_account_name>.dfs.core.windows.net/<container_name> |
A partir do SQL Server 2022 (16.x) | Assinatura de acesso compartilhado (SAS) |
SQL Server | sqlserver |
<server_name>[\<instance_name>][:port] |
A partir do SQL Server 2019 (15.x) | Somente autenticação SQL |
Oracle | oracle |
<server_name>[:port] |
A partir do SQL Server 2019 (15.x) | Somente autenticação básica |
Teradata | teradata |
<server_name>[:port] |
A partir do SQL Server 2019 (15.x) | Somente autenticação básica |
API do MongoDB ou do Cosmos DB para MongoDB | mongodb |
<server_name>[:port] |
A partir do SQL Server 2019 (15.x) | Somente autenticação básica |
ODBC Genérico | odbc |
<server_name>[:port] |
Começando com o SQL Server 2019 (15.x) – somente Windows | Somente autenticação básica |
Operações em Massa | https |
<storage_account>.blob.core.windows.net/<container> |
A partir do SQL Server 2017 (14.x) | Assinatura de acesso compartilhado (SAS) |
Armazenamento de objetos compatível com o S3 | s3 |
- Compatível com S3: s3://<server_name>:<port>/ - AWS S3: s3://<bucket_name>.S3.amazonaws.com[:port]/<folder> ou s3://s3.amazonaws.com[:port]/<bucket_name>/<folder> |
A partir do SQL Server 2022 (16.x) | Básico ou passagem (STS) * |
* Deve ser uma credencial no escopo do banco de dados, em que a IDENTITY é codificada IDENTITY = 'S3 Access Key'
e o argumento SECRET está no formato = '<AccessKeyID>:<SecretKeyID>'
ou na autorização STS (use passagem). Para obter mais informações, confira Configurar o PolyBase para acessar dados externos no armazenamento de objetos compatível com o S3.
Caminho de local:
port
= a porta em que fonte de dados externa está escutando. Opcional em muitos casos, dependendo da configuração da rede.<container_name>
= o contêiner da conta de armazenamento que contém os dados. Os contêineres raiz são somente leitura, não é possível gravar dados no contêiner.<storage_account>
= o nome da conta de armazenamento do recurso do Azure.<server_name>
= o nome de host.<instance_name>
= o nome de uma instância nomeada do SQL Server. Usado se você tiver o Serviço SQL Server Browser em execução na instância de destino.<ip_address>:<port>
= somente para armazenamento de objetos compatível com o S3 [a partir do SQL Server 2022 (16.x)], o ponto de extremidade e a porta usados para se conectar ao armazenamento compatível com o S3.<bucket_name>
= Somente para armazenamento de objetos compatível com S3 (a partir do SQL Server 2022 (16.x)), específico para a plataforma de armazenamento.<region>
= Somente para armazenamento de objetos compatível com S3 (a partir do SQL Server 2022 (16.x)), específico para a plataforma de armazenamento.<folder>
= Parte do caminho de armazenamento dentro da URL de armazenamento.
Observações e orientação adicionais ao definir o local:
- O Mecanismo de Banco de Dados do SQL Server não verifica a existência da fonte de dados externa quando o objeto é criado. Para validar, crie uma tabela externa usando a fonte de dados externa.
- Você pode usar o conector
sqlserver
para conectar o Microsoft SQL Server 2019 (15.x) a outro Microsoft SQL Server ou ao Banco de Dados SQL do Microsoft Azure. - Especifique o
Driver={<Name of Driver>}
ao se conectar por meio deODBC
. - A opção Namespace Hierárquico para Contas do Armazenamento do Microsoft Azure (V2) usando o prefixo
adls
tem suporte por meio do Azure Data Lake Storage Gen2 no SQL Server 2022 (16.x).
- O suporte do SQL Server a fontes de dados externas do HDFS Cloudera (CDP) e do Hortonworks (HDP) foi desativado e não será incluído no SQL Server 2022 (16.x). Não é necessário usar o argumento TYPE no SQL Server 2022 (16.x).
- Para obter mais informações sobre o armazenamento de objetos compatível com o S3 e o PolyBase a partir do SQL Server 2022 (16.x), consulte Configurar o PolyBase para acessar dados externos no armazenamento de objetos compatível com o S3. Para obter um exemplo de consulta de um arquivo Parquet no armazenamento de objetos compatível com o S3, confira Virtualizar o arquivo Parquet em um armazenamento do objetos compatível com o S3 usando o PolyBase.
- Diferente das versões anteriores, no SQL Server 2022 (16.x), o prefixo usado para a Conta de Armazenamento do Azure (v2) foi alterado de
wasb[s]
paraabs
. - Diferente das versões anteriores, no SQL Server 2022 (16.x), o prefixo usado para a o Azure Data Lake Storage Gen2 foi alterado de
abfs[s]
paraadls
. - Para obter um exemplo usando o PolyBase para virtualizar um arquivo CSV no Armazenamento do Azure, confira Virtualizar arquivo CSV com o PolyBase.
- Para obter um exemplo usando o PolyBase para virtualizar uma tabela delta no ADLS Gen2, confira Virtualizar tabela delta com o PolyBase.
- O SQL Server 2022 (16.x) oferece suporte total a dois formatos de URL para a Conta de Armazenamento do Azure v2 (
abs
) e o Azure Data Lake Gen2 (adls
).- O caminho LOCATION pode usar os formatos:
<container>@<storage_account_name>..
(recomendado) ou<storage_account_name>../<container>
. Por exemplo:- Conta de Armazenamento do Azure v2:
abs://<container>@<storage_account_name>.blob.core.windows.net
(recomendado) ouabs://<storage_account_name>.blob.core.windows.net/<container>
. - O Azure Data Lake Gen2 oferece suporte para:
adls://<container>@<storage_account_name>.blob.core.windows.net
(recomendado) ouadls://<storage_account_name>.dfs.core.windows.net/<container>
.
- Conta de Armazenamento do Azure v2:
- O caminho LOCATION pode usar os formatos:
CONNECTION_OPTIONS = key_value_pair
Especificado para SQL Server 2019 (15.x) e posteriores. Especifica opções adicionais ao se conectar por meio de ODBC
a uma fonte de dados externa. Para usar várias opções de conexão, separe-as com ponto e vírgula.
Aplica-se a conexões ODBC
genéricas, assim como a conectores ODBC
internos para SQL Server, Oracle, Teradata, MongoDB e API do Azure Cosmos DB para MongoDB.
O key_value_pair
é a palavra-chave e o valor de uma opção de conexão específica. As palavras-chave e os valores disponíveis dependem do tipo de fonte de dados externa. O nome do driver é necessário como requisito mínimo, mas há outras opções, como APP='<your_application_name>'
ou ApplicationIntent= ReadOnly|ReadWrite
, que também são úteis de serem definidas e podem ajudar a solucionar problemas.
Os possíveis pares de chave-valor são específicos do driver. Para obter mais informações para cada provedor, confira CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.
A partir de Aplica-se a: SQL Server 2022 (16.x) atualização cumulativa 2, palavras-chave adicionais foram introduzidas para dar suporte a arquivos Oracle TNS:
- A palavra-chave
TNSNamesFile
especifica o caminho para o arquivotnsnames.ora
localizado no servidor Oracle. - A palavra-chave
ServerName
especifica o alias usado emtnsnames.ora
que será utilizado para substituir o nome do host e a porta.
PUSHDOWN = ON | OFF
Aplica-se a: SQL Server 2019 (15.x) e posterior. Informa se é possível realizar a aplicação da computação para a fonte de dados externa. Está ativado por padrão.
Há suporte para PUSHDOWN
ao se conectar ao SQL Server, Oracle, Teradata, MongoDB, à API do Azure Cosmos DB para MongoDB ou ao ODBC no nível da fonte de dados externa.
Habilitar ou desabilitar a aplicação no nível da consulta é feito por meio de uma dica.
CREDENTIAL = credential_name
Especifica uma credencial no escopo do banco de dados para a autenticação na fonte de dados externa.
Observações e orientações adicionais ao criar uma credencial:
CREDENTIAL
será necessário apenas se os dados tiverem sido protegidos.CREDENTIAL
não é necessário para conjuntos de dados que permitem acesso anônimo.- Ao acessar a Conta de Armazenamento do Azure (V2) ou Azure Data Lake Storage Gen2, o
IDENTITY
deve serSHARED ACCESS SIGNATURE
.
Há várias maneiras de criar uma assinatura de acesso compartilhado:
Você pode criar um token SAS navegando até o Portal do Azure -><Sua_Conta_de_Armazenamento> –> Assinatura de Acesso Compartilhado –> Configurar permissões –> Gerar SAS e cadeia de conexão. Para obter mais informações, confira Gerar uma assinatura de acesso compartilhado.
Você pode criar e configurar uma SAS com o Gerenciador de Armazenamento do Azure.
Você pode criar uma SAS programaticamente por meio do PowerShell, da CLI do Azure, do .NET e da API REST. Para obter mais informações, confira Conceder acesso limitado a recursos de Armazenamento do Azure usando SAS (assinaturas de acesso compartilhado).
O token SAS deve ser configurado da seguinte maneira:
- Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
?
à esquerda quando configurado como o SEGREDO. - Use um período de término válido (todas as datas estão no horário UTC).
- Conceder pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo
srt=o&sp=r
). Várias assinaturas de acesso compartilhado podem ser criadas para diferentes casos de uso. As permissões devem ser concedidas da seguinte maneira:
Ação Permissão Ler dados de um arquivo Ler Leitura de dados de vários arquivos e subpastas Leitura e listagem Uso de CETAS (Criar Tabela Externa como Seleção) Leitura, criação, listagem e gravação - Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
Para Armazenamento de Blobs do Azure e Azure Data Lake Gen 2:
- Serviços permitidos:
Blob
deve ser selecionado para gerar o token SAS - Tipos de recursos permitidos:
Container
eObject
devem ser selecionados para gerar o token SAS
- Serviços permitidos:
Para obter um exemplo de como usar um CREDENTIAL
com o armazenamento de objetos compatível com o S3 e o PolyBase, confira Configurar o PolyBase para acessar dados externos no armazenamento de objetos compatível com o S3.
Para criar uma credencial no escopo do banco de dados, veja CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
Permissões
Requer a permissão CONTROL
no banco de dados no SQL Server.
Bloqueio
Usa um bloqueio compartilhado no objeto EXTERNAL DATA SOURCE
.
Segurança
O PolyBase dá suporte para autenticação baseada em proxy para a maioria das fontes de dados externas. Crie uma credencial no escopo do banco de dados para criar a conta proxy.
Atualizar para o SQL Server 2022
A partir do SQL Server 2022 (16.x), não há mais suporte para as fontes de dados externas do Hadoop. É necessário recriar manualmente fontes de dados externas criadas anteriormente com TYPE = HADOOP
e qualquer tabela externa que use essa fonte de dados externa.
Os usuários também precisarão configurar suas fontes de dados externas para usar novos conectores ao se conectar ao Armazenamento do Azure.
Fonte de dados externa | De | Para |
---|---|---|
Armazenamento do Blobs do Azure | wasb[s] | abs |
ADLS Gen2 | abfs[s] | adls |
Exemplos
Importante
Para obter informações sobre como instalar e habilitar o PolyBase, confira Instalar o PolyBase no Windows
a. Criar fonte de dados externa no SQL Server para referenciar o Oracle
Para criar uma fonte de dados externa que faça referência ao Oracle, verifique se que você tem uma credencial no escopo do banco de dados. Opcionalmente, você também poderá habilitar ou desabilitar a aplicação de computação em relação a essa fonte de dados.
-- 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, a fonte de dados externa para o Oracle pode usar a autenticação de proxy para fornecer controle de acesso refinado. Um usuário de proxy pode ser configurado para ter acesso limitado em comparação com o usuário que está sendo representado.
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, é possível autenticar usando o TNS.
A partir de Aplica-se a: SQL Server 2022 (16.x) Atualização Cumulativa 2, CREATE EXTERNAL DATA SOURCE
agora dá suporte ao uso de arquivos TNS ao se conectar ao Oracle.
O parâmetro CONNECTION_OPTIONS
foi ampliado e agora utiliza TNSNamesFile
e ServerName
como variáveis para procurar o arquivo tnsnames.ora
e estabelecer conexão com o servidor.
No exemplo abaixo, durante o runtime, o SQL Server procura o local do arquivo tnsnames.ora
especificado por TNSNamesFile
e o host e a porta de rede 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. Criar uma fonte de dados externa para referenciar uma instância nomeada do SQL Server por meio da conectividade do PolyBase
Aplica-se a: SQL Server 2019 (15.x) e posterior
Para criar uma fonte de dados externa que referencie uma instância nomeada do SQL Server, use CONNECTION_OPTIONS
para especificar o nome da instância.
Primeiro, crie a credencial no escopo do banco de dados, armazenando credenciais para um logon autenticado SQL. O Conector ODBC do SQL para PolyBase dá suporte apenas à autenticação básica. Antes de criar uma credencial com escopo do banco de dados, o banco de dados precisa ter uma chave mestra para proteger a credencial. Para obter mais informações, confira CREATE MASTER KEY. O exemplo a seguir cria uma credencial no escopo do banco de dados, fornece seu próprio logon e senha.
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
No exemplo a seguir, WINSQL2019
é o nome do host e SQL2019
é o nome da instância. 'Server=%s\SQL2019'
é o par chave-valor.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019',
CONNECTION_OPTIONS = 'Server=%s\SQL2019',
CREDENTIAL = SQLServerCredentials
);
Como alternativa, você pode usar uma porta para se conectar a uma instância padrão do SQL Server.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019:58137',
CREDENTIAL = SQLServerCredentials
);
C. Criar fonte de dados externa para referenciar a uma réplica secundária para leitura do grupo de disponibilidade Always On
Aplica-se a: SQL Server 2019 (15.x) e posterior
Para criar uma fonte de dados externa que referencie uma réplica secundária para leitura do SQL Server, use CONNECTION_OPTIONS
para especificar ApplicationIntent=ReadOnly
.
Primeiro, crie a credencial no escopo do banco de dados, armazenando credenciais para um logon autenticado SQL. O Conector ODBC do SQL para PolyBase dá suporte apenas à autenticação básica. Antes de criar uma credencial com escopo do banco de dados, o banco de dados precisa ter uma chave mestra para proteger a credencial. Para obter mais informações, confira CREATE MASTER KEY. O exemplo a seguir cria uma credencial no escopo do banco de dados, fornece seu próprio logon e senha.
CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
WITH IDENTITY = 'username',
SECRET = 'password';
Em seguida, crie a fonte de dados externa.
O parâmetro Database
ODBC não é necessário. Forneça o nome do banco de dados por meio de um nome de três partes na instrução CREATE EXTERNAL TABLE, dentro do parâmetro LOCATION. Para obter um exemplo, confira CREATE EXTERNAL TABLE.
No exemplo a seguir, WINSQL2019AGL
é o nome do ouvinte do grupo de disponibilidade e dbname
é o nome do banco de dados que será o destino da instrução CREATE EXTERNAL TABLE.
CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
LOCATION = 'sqlserver://WINSQL2019AGL',
CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly',
CREDENTIAL = SQLServerCredentials
);
Você pode demonstrar o comportamento de redirecionamento do grupo de disponibilidade especificando ApplicationIntent
e criando uma tabela externa no modo de exibição do sistema sys.servers
. No script de exemplo a seguir, duas fontes de dados externas são criadas e uma tabela externa é criada para cada uma. Use as exibições para testar qual servidor está respondendo à conexão. Resultados semelhantes também podem ser obtidos por meio do recurso de roteamento somente leitura. Para obter informações, veja Configurar o roteamento somente leitura para um grupo de disponibilidade 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 do banco de dados no grupo de disponibilidade, crie uma exibição para retornar sys.servers
e o nome da instância local, o que ajuda a identificar qual réplica está respondendo à consulta. Para obter mais informações, consulte sys.servers.
CREATE VIEW vw_sys_servers AS
SELECT [name] FROM sys.servers
WHERE server_id = 0;
GO
Em seguida, crie uma tabela externa na instância de origem:
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. Criar uma fonte de dados externa para consultar um arquivo parquet no armazenamento de objetos compatível com o S3 por meio do PolyBase
Aplica-se a: SQL Server 2022 (16.x) e versões posteriores
O script de exemplo a seguir cria uma fonte de dados externa s3_ds
no banco de dados do usuário de origem no SQL Server. A fonte de dados externa faz referência à credencial no escopo do banco de dados 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
Verifique a nova fonte de dados externa com sys.external_data_sources.
SELECT * FROM sys.external_data_sources;
O exemplo a seguir demonstra o uso do T-SQL para consultar um arquivo Parquet armazenado em um armazenamento de objetos compatível com S3 por meio da consulta OPENROWSET. Para obter mais informações, confira Virtualizar o arquivo Parquet em um armazenamento de objetos compatível com o S3 usando o PolyBase.
SELECT *
FROM OPENROWSET (
BULK '/<bucket>/<parquet_folder>',
FORMAT = 'PARQUET',
DATA_SOURCE = 's3_ds'
) AS [cc];
E. Criar fonte de dados externa usando o ODBC genérico para o PostgreSQL
Como em exemplos anteriores, primeiro crie uma chave mestra de banco de dados e uma credencial com escopo de banco de dados. A credencial no escopo do banco de dados será usada para a fonte de dados externa. Este exemplo também pressupõe que um provedor de dados ODBC genérico para o PostgreSQL está instalado no servidor.
Neste exemplo, o provedor de dados ODBC genérico é usado para se conectar a um servidor de banco de dados PostgreSQL na mesma rede, em que o nome de domínio totalmente qualificado do servidor PostgreSQL é POSTGRES1
, usando a porta padrão TCP 5432.
CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
LOCATION = 'odbc://POSTGRES1.domain:5432',
CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
CREDENTIAL = postgres_credential
);
Armazenamento do Azure
Criar uma assinatura de acesso compartilhado
No caso do Armazenamento de Blobs do Azure e o Azure Data Lake Gen2, o método de autenticação com suporte é SAS (assinatura de acesso compartilhado). Uma maneira simples de gerar um token de assinatura de acesso compartilhado é seguindo as etapas a seguir. Para obter mais informações, confira CREDENTIAL.
- Navegue até a conta de armazenamento desejada no portal do Azure.
- Navegue até o Contêiner desejado no menu Armazenamento de Dados.
- Selecione Tokens de acesso compartilhado.
- Escolha a permissão apropriada com base na ação desejada, use a tabela abaixo como referência:
Ação | Permissão |
---|---|
Ler dados de um arquivo | Ler |
Leitura de dados de vários arquivos e subpastas | Leitura e listagem |
Uso de CETAS (Criar Tabela Externa como Seleção) | Leitura, criação e gravação |
- Escolha a data de validade do token.
- Gere o token SAS e a URL.
- Copie o token SAS.
F. Crie uma fonte de dados externa para acessar dados no Armazenamento de Blobs do Azure usando a interface abs://
Aplica-se a: SQL Server 2022 (16.x) e versões posteriores
A partir do SQL Server 2022 (16.x), use um novo prefixo abs
para a Conta de Armazenamento do Azure v2. O prefixo abs
dá suporte à autenticação com SHARED ACCESS SIGNATURE
. Esse prefixo abs
substitui wasb
usado em versões anteriores. Não há mais suporte para HADOOP, portanto, não é mais necessário usar TYPE = BLOB_STORAGE
.
A chave da conta de armazenamento do Azure não é mais necessária, é possível usar o Token SAS como podemos ver no exemplo a seguir:
-- 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 obter um exemplo mais detalhado sobre como acessar arquivos CSV salvos no Armazenamento de Blobs do Azure, confira Virtualizar arquivo CSV com PolyBase.
G. Criar fonte de dados externa para acessar dados no Azure Data Lake Gen2
Aplica-se a: SQL Server 2022 (16.x) e versões posteriores
A partir do SQL Server 2022 (16.x), use um novo prefixo adls
para o Azure Data Lake Gen2, substituindo o abfs
usado em versões anteriores. O prefixo adls
também dá suporte ao token SAS como método de autenticação, conforme mostrado no seguinte exemplo:
--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 obter um exemplo mais detalhado sobre como acessar arquivos delta armazenados no Azure Data Lake Gen2, confira\ Virtualizar tabela delta com o PolyBase.
Exemplos: Operações em Massa
Importante
Não adicione parâmetros de assinatura de acesso compartilhado, nome de arquivo ou / à direita no fim da URL LOCATION
ao configurar uma fonte de dados externa para operações em massa.
H. Criar uma fonte de dados externa para operações em massa recuperando dados do Armazenamento do Azure
Aplica-se a: SQL Server 2022 (16.x) e versões posteriores.
Use a seguinte fonte de dados para operações em massa com BULK INSERT ou OPENROWSET. A credencial deve ser definida como SHARED ACCESS SIGNATURE
como a identidade, não deve ter o ?
à esquerda no token SAS, deve ter pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo srt=o&sp=r
), e o período de término deve ser válido (todas as datas estão no horário UTC). Para mais informações sobre assinaturas de acesso compartilhado, consulte Usando SAS (Assinatura de Acesso Compartilhado).
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,
);
Próximas etapas
- ALTER EXTERNAL DATA SOURCE (Transact-SQL)
- CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL)
- CREATE EXTERNAL FILE FORMAT (Transact-SQL)
- CREATE EXTERNAL TABLE (Transact-SQL)
- sys.external_data_sources (Transact-SQL)
- Usando SAS (Assinatura de Acesso Compartilhado)
- Configuração de conectividade do PolyBase
* Banco de Dados SQL *
Visão geral: Banco de Dados SQL do Azure
Aplica-se a: Banco de Dados SQL do Azure
Cria uma fonte de dados externa para consultas elásticas. Fontes de dados externas são usadas para estabelecer a conectividade e dar suporte a estes casos de uso principal:
- Operações de carregamento em massa usando
BULK INSERT
ouOPENROWSET
- Consultar instâncias remotas do Banco de Dados SQL ou do Azure Synapse usando o Banco de Dados SQL com consulta elástica
- Consultar um Banco de Dados SQL fragmentado usando consulta elástica
Sintaxe
Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe 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 o nome da fonte de dados definido pelo usuário. O nome deve ser exclusivo dentro do banco de dados no Banco de Dados SQL.
LOCALIZAÇÃO = '<prefix>://<path[:port]>'
Fornece o protocolo de conectividade e o caminho para a fonte de dados externa.
Fonte de dados externa | Prefixo de localização do conector | Caminho de local | Disponibilidade |
---|---|---|---|
Operações em Massa | https |
<storage_account>.blob.core.windows.net/<container> |
|
Consulta Elástica (fragmento) | Não obrigatório | <shard_map_server_name>.database.windows.net |
|
Consulta Elástica (remota) | Não obrigatório | <remote_server_name>.database.windows.net |
|
EdgeHub | edgehub |
edgehub:// |
Disponível no SQL do Azure no Edgeapenas. O EdgeHub é sempre local para a instância do SQL do Azure no Edge. Como tal, não é necessário especificar um valor de porta ou caminho. |
Kafka | kafka |
kafka://<kafka_bootstrap_server_name_ip>:<port_number> |
Disponível no SQL do Azure no Edgeapenas. |
Caminho de local:
<shard_map_server_name>
= o nome do servidor lógico do Azure que está hospedando o gerenciador de mapa de fragmentos. O argumentoDATABASE_NAME
fornece o banco de dados usado para hospedar o mapa de fragmentos eSHARD_MAP_NAME
é usado para o mapa de fragmentos em si.<remote_server_name>
= o nome do servidor lógico de destino para a consulta elástica. O nome do banco de dados é especificado usando o argumentoDATABASE_NAME
.
Observações e orientação adicionais ao definir o local:
- O Mecanismo de Banco de Dados não verifica a existência da fonte de dados externa quando o objeto é criado. Para validar, crie uma tabela externa usando a fonte de dados externa.
CREDENTIAL = credential_name
Especifica uma credencial no escopo do banco de dados para a autenticação na fonte de dados externa.
Observações e orientações adicionais ao criar uma credencial:
- Para carregar dados do Armazenamento do Azure no Banco de Dados SQL do Azure, use um token SAS (Assinatura de Acesso Compartilhado).
CREDENTIAL
será necessário apenas se os dados tiverem sido protegidos.CREDENTIAL
não é necessário para conjuntos de dados que permitem acesso anônimo.- Quando o
TYPE
=BLOB_STORAGE
, a credencial precisa ser criada usandoSHARED ACCESS SIGNATURE
como a identidade. - Ao se conectar ao Armazenamento do Azure por meio do conector WASB[s], a autenticação deve ser feita com uma chave de conta de armazenamento, não com uma SAS (assinatura de acesso compartilhado).
- Quando
TYPE
=HADOOP
, a credencial deve ser criada usando a chave de conta de armazenamento como oSECRET
. TYPE
=BLOB_STORAGE
só é permitido para operações em massa; não é possível criar tabelas externas para uma fonte de dados externa comTYPE
=BLOB_STORAGE
.
Há várias maneiras de criar uma assinatura de acesso compartilhado:
Você pode criar um token SAS navegando até o Portal do Azure -><Sua_Conta_de_Armazenamento> –> Assinatura de Acesso Compartilhado –> Configurar permissões –> Gerar SAS e cadeia de conexão. Para obter mais informações, confira Gerar uma assinatura de acesso compartilhado.
Você pode criar e configurar uma SAS com o Gerenciador de Armazenamento do Azure.
Você pode criar uma SAS programaticamente por meio do PowerShell, da CLI do Azure, do .NET e da API REST. Para obter mais informações, confira Conceder acesso limitado a recursos de Armazenamento do Azure usando SAS (assinaturas de acesso compartilhado).
O token SAS deve ser configurado da seguinte maneira:
- Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
?
à esquerda quando configurado como o SEGREDO. - Use um período de término válido (todas as datas estão no horário UTC).
- Conceder pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo
srt=o&sp=r
). Várias assinaturas de acesso compartilhado podem ser criadas para diferentes casos de uso. As permissões devem ser concedidas da seguinte maneira:
Ação Permissão Ler dados de um arquivo Ler Leitura de dados de vários arquivos e subpastas Leitura e listagem Uso de CETAS (Criar Tabela Externa como Seleção) Leitura, criação e gravação - Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
Para obter um exemplo de como usar um CREDENTIAL
com SHARED ACCESS SIGNATURE
e TYPE
= BLOB_STORAGE
, confira Criar uma fonte de dados externa para executar operações em massa e recuperar dados do Armazenamento do Azure no Banco de Dados SQL
Para criar uma credencial no escopo do banco de dados, veja CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
TYPE = [ BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER]
Especifica o tipo de fonte de dados externa que está sendo configurada. Esse parâmetro não é sempre necessário.
- Use o
RDBMS
para consultas entre bancos de dados usando a consulta elástica do Banco de Dados SQL. - Use
SHARD_MAP_MANAGER
ao criar uma fonte de dados externa ao se conectar a um Banco de Dados SQL fragmentado. - Use
BLOB_STORAGE
ao executar operações em massa com BULK INSERT ou OPENROWSET.
Importante
Não defina TYPE
se estiver usando qualquer outra fonte de dados externa.
DATABASE_NAME = database_name
Configure esse argumento quando o TYPE
estiver definido como RDBMS
ou SHARD_MAP_MANAGER
.
TYPE | Valor de DATABASE_NAME |
---|---|
RDBMS | O nome do banco de dados remoto no servidor fornecido usando LOCATION |
SHARD_MAP_MANAGER | Nome do banco de dados operacional, como o gerenciador de mapa de fragmentos |
Para obter um exemplo que mostra como criar uma fonte de dados externa em que TYPE
= RDBMS
, veja Criar uma fonte de dados externa do RDBMS
SHARD_MAP_NAME = shard_map_name
Usado quando o argumento TYPE
é definido como SHARD_MAP_MANAGER
apenas para definir o nome do mapa de fragmentos.
Para ver um exemplo que mostra como criar uma fonte de dados externa em que TYPE
= SHARD_MAP_MANAGER
, veja Criar uma fonte de dados externa do gerenciador de mapa de fragmentos
Permissões
Requer a permissão CONTROL
no Banco de Dados SQL do Azure.
Bloqueio
Usa um bloqueio compartilhado no objeto EXTERNAL DATA SOURCE
.
Exemplos
a. Criar uma fonte de dados externa do gerenciador de mapa de fragmentos
Para criar uma fonte de dados externa para referenciar um SHARD_MAP_MANAGER
, especifique o nome do servidor do Banco de Dados SQL que hospeda o gerenciador de mapa de fragmentos no Banco de Dados SQL ou em um banco de dados do SQL Server em uma 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 obter um tutorial passo a passo, confira Introdução a consultas elásticas para fragmentação (particionamento horizontal).
B. Criar uma fonte de dados externa do RDBMS
Para criar uma fonte de dados externa para referenciar um RDBMS, especifica o nome do servidor do Banco de Dados SQL do banco de dados remoto no Banco de Dados SQL.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
WITH IDENTITY = '<username>',
SECRET = '<password>';
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
TYPE = RDBMS,
LOCATION = '<server_name>.database.windows.net',
DATABASE_NAME = 'Customers',
CREDENTIAL = SQL_Credential
);
Para obter um tutorial passo a passo sobre o RDBMS, confira Introdução às consultas entre bancos de dados (particionamento vertical).
Exemplos: operações em massa
Importante
Não adicione parâmetros de assinatura de acesso compartilhado, nome de arquivo ou / à direita no fim da URL LOCATION
ao configurar uma fonte de dados externa para operações em massa.
C. Criar uma fonte de dados externa para operações em massa recuperando dados do Armazenamento do Azure
Use a seguinte fonte de dados para operações em massa com BULK INSERT ou OPENROWSET. A credencial deve ser definida como SHARED ACCESS SIGNATURE
como a identidade, não deve ter o ?
à esquerda no token SAS, deve ter pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo srt=o&sp=r
), e o período de término deve ser válido (todas as datas estão no horário UTC). Para mais informações sobre assinaturas de acesso compartilhado, consulte Usando SAS (Assinatura de Acesso Compartilhado).
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 esse exemplo em uso, confira BULK INSERT.
Exemplos: SQL do Azure no Edge
Importante
Para obter informações sobre como configurar dados externos para o SQL do Azure no Edge, confira Transmissão de dados no SQL do Azure no Edge.
a. Criar uma fonte de dados externa para referenciar o Kafka
Aplica-se ao:SQL do Azure no Edge apenas
Neste exemplo, a fonte de dados externa é um servidor Kafka com o endereço IP xxx.xxx.xxx.xxx e escuta na porta 1900. A fonte de dados externa do Kafka é apenas para streaming de dados e não dá suporte a push de predicado.
-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyKafkaServer
WITH (LOCATION = 'kafka://xxx.xxx.xxx.xxx:1900');
B. Criar uma fonte de dados externa para referenciar o EdgeHub
Aplica-se ao:SQL do Azure no Edge apenas
Neste exemplo, a fonte de dados externa é um EdgeHub em execução no mesmo dispositivo de borda que o SQL do Azure no Edge. A fonte de dados externa do edgeHub é apenas para streaming de dados e não dá suporte a push de predicado.
-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyEdgeHub
WITH (LOCATION = 'edgehub://');
Próximas etapas
* Azure Synapse
Analytics *
Visão geral: Azure Synapse Analytics
Aplica-se a: Azure Synapse Analytics
Cria uma fonte de dados externa para virtualização de dados. Fontes de dados externas são usadas para estabelecer a conectividade e dar suporte ao caso de uso primário da virtualização de dados e carregamento dados a partir das fontes de dados externas. Para obter mais informações, confira Usar tabelas externas com Synapse SQL.
Importante
Para criar uma fonte de dados externa para consultar um recurso do Azure Synapse Analytics usando o Banco de Dados SQL do Azure com a consulta elástica, confira Banco de Dados SQL.
Sintaxe
Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe Transact-SQL.
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
[ [ , ] TYPE = HADOOP ]
)
[ ; ]
Argumentos
data_source_name
Especifica o nome da fonte de dados definido pelo usuário. O nome deve ser exclusivo no Banco de Dados SQL do Azure no Azure Synapse Analytics.
LOCALIZAÇÃO = '<prefix>://<path>'
Fornece o protocolo de conectividade e o caminho para a fonte de dados externa.
Fonte de dados externa | Prefixo de localização do conector | Caminho de local |
---|---|---|
Data Lake Storage* Gen1 | adl |
<storage_account>.azuredatalake.net |
Data Lake Storage Gen2 | abfs[s] |
<container>@<storage_account>.dfs.core.windows.net |
Armazenamento do Blobs do Azure | wasbs |
<container>@<storage_account>.blob.core.windows.net |
Armazenamento do Blobs do Azure | 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 |
* O Microsoft Azure Data Lake Storage Gen1 tem suporte limitado, o Gen2 é sempre recomendado para os novos desenvolvimentos.
Fonte de dados externa | Prefixo de localização do conector | Pools de SQL dedicados: PolyBase | Pools de SQL dedicados: nativo* | Pools de SQL sem servidor |
---|---|---|---|---|
Data Lake Storage** Gen1 | adl |
Não | No | Sim |
Data Lake Storage Gen2 | abfs[s] |
Sim | Sim | Sim |
Armazenamento do Blobs do Azure | wasbs |
Sim | Sim** | Sim |
Armazenamento de Blobs do Azure | https |
Não | Sim | Sim |
Data Lake Storage Gen1 | http[s] |
Não | No | Sim |
Data Lake Storage Gen2 | http[s] |
Sim | Sim | Sim |
Data Lake Storage Gen2 | wasb[s] |
Sim | Sim | Sim |
* Pools de SQL dedicados e sem servidor no Azure Synapse Analytics usam bases de código diferentes para virtualização de dados. Os pools de SQL sem servidor dão suporte à tecnologia de virtualização de dados nativa. Os pools de SQL dedicados dão suporte à virtualização de dados nativa e do PolyBase. A virtualização de dados do PolyBase é usada quando a FONTE DE DADOS EXTERNA é criada com TYPE=HADOOP
.
** O Microsoft Azure Data Lake Storage Gen1 tem suporte limitado, o Gen2 é sempre recomendado para os novos desenvolvimentos.
*** O conector mais seguro wasbs
é recomendado em vez de wasb
. Somente a virtualização de dados nativa em pools de SQL dedicados (em que TYPE não é igual a HADOOP) dá suporte a wasb
.
Caminho de local:
<container>
= o contêiner da conta de armazenamento que contém os dados. Os contêineres raiz são somente leitura, não é possível gravar dados no contêiner.<storage_account>
= o nome da conta de armazenamento do recurso do Azure.
Observações e orientação adicionais ao definir o local:
- A opção padrão é usar
enable secure SSL connections
ao provisionar o Azure Data Lake Storage Gen2. Quando estiver habilitado, você deverá usarabfss
ao selecionar uma conexão TLS/SSL segura. Observe queabfss
também funciona em conexões TSL não seguras. Para saber mais, confira O driver ABFS (sistema de arquivos de Blob do Azure). - O Azure Synapse não verifica a existência da fonte de dados externa quando o objeto é criado. Para validar, crie uma tabela externa usando a fonte de dados externa.
- Use a mesma fonte de dados externa para todas as tabelas ao consultar o Hadoop para garantir semântica de consulta consistente.
- O prefixo
https:
permite que você use subpasta no caminho.https
não está disponível para todos os mecanismos de acesso a dados. wasbs
é recomendado, pois os dados serão enviados usando uma conexão TLS segura.- Os Namespaces Hierárquicos não têm suporte com contas de armazenamento do Azure V2 ao acessar dados usando a interface herdada
wasb://
, mas usarwasbs://
dá suporte a Namespaces Hierárquicos.
CREDENTIAL = credential_name
Opcional. Especifica uma credencial no escopo do banco de dados para autenticação na fonte de dados externa. A fonte de dados externa sem credencial pode acessar a conta de armazenamento público ou usar a identidade do Microsoft Entra do chamador para acessar arquivos no armazenamento do Azure.
Observações e orientações adicionais ao criar uma credencial:
- Para carregar dados do Armazenamento do Azure ou do ADLS (Azure Data Lake Storage) Gen2 no Azure Synapse Analytics, use uma Chave de Armazenamento do Azure.
CREDENTIAL
será necessário apenas se os dados tiverem sido protegidos.CREDENTIAL
não é necessário para conjuntos de dados que permitem acesso anônimo.
Para criar uma credencial no escopo do banco de dados, veja CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
No pool de SQL sem servidor, as credenciais no escopo do banco de dados podem especificar a identidade gerenciada do workspace, o nome da entidade de serviço ou o token SAS (assinatura de acesso compartilhado). O acesso por meio de uma identidade de usuário, também conhecida como passagem do Microsoft Entra, também é possível na credencial com escopo de banco de dados, assim como o acesso anônimo ao armazenamento disponível publicamente. Para obter mais informações, confira Tipos de autorização de armazenamento com suporte.
No pool de SQL dedicado, as credenciais no escopo do banco de dados podem especificar o token SAS (assinatura de acesso compartilhado), a chave de acesso ao armazenamento, a entidade de serviço, a identidade gerenciada do workspace ou a passagem do Microsoft Entra.
TYPE = HADOOP
Opcional, não recomendado.
Você só pode especificar TYPE com pools de SQL dedicados. HADOOP
é o único valor permitido quando especificado. Fontes de dados externas com TYPE=HADOOP
estão disponíveis somente em pools de SQL dedicados.
Use o HADOOP para implementações herdadas, caso contrário, é recomendável usar o acesso a dados nativos mais recente. Não especifique o argumento TYPE para usar o acesso a dados nativos mais recente.
Para obter um exemplo de como usar TYPE = HADOOP
para carregar dados do Armazenamento do Azure, confira Criar uma fonte de dados externa para referenciar o Azure Data Lake Storage Gen 1 ou 2 usando uma entidade de serviço.
Pools de SQL dedicados e sem servidor no Azure Synapse Analytics usam bases de código diferentes para virtualização de dados. Os pools de SQL sem servidor dão suporte à tecnologia de virtualização de dados nativa. Os pools de SQL dedicados dão suporte à virtualização de dados nativa e do PolyBase. A virtualização de dados do PolyBase é usada quando a FONTE DE DADOS EXTERNA é criada com TYPE=HADOOP
.
Permissões
Requer a permissão CONTROL
no banco de dados.
Bloqueio
Usa um bloqueio compartilhado no objeto EXTERNAL DATA SOURCE
.
Segurança
A maioria das fontes de dados externas dá suporte à autenticação baseada em proxy usando uma credencial no escopo do banco de dados para criar a conta proxy.
As chaves SAS (Assinatura de Acesso Compartilhado) têm suporte para autenticação em Contas de Armazenamento do Azure Data Lake Store Gen 2. Os clientes que desejam se autenticar usando uma Assinatura de Acesso Compartilhado devem criar uma credencial no escopo do banco de dados em IDENTITY = "Shared Access Signature"
e inserir um token SAS como o segredo.
Se você criar uma credencial no escopo do banco de dados em IDENTITY = "Shared Access Signature"
e usar um valor de chave de armazenamento como o segredo, e receberá a seguinte mensagem de erro:
'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]'
Exemplos
a. Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface wasb://
Neste exemplo, a fonte de dados externa é uma conta de Armazenamento do Azure V2 chamada logs
. O contêiner de armazenamento é chamado daily
. A fonte de dados externa do Armazenamento do Azure destina-se somente a transferência de dados. Não dá suporte a aplicação de predicado. Não há suporte para namespaces hierárquicos ao acessar dados por meio da interface wasb://
. Observe que, ao se conectar ao Armazenamento do Azure por meio do conector WASB[s], a autenticação deve ser feita com uma chave de conta de armazenamento, não com uma SAS (assinatura de acesso compartilhado).
Este exemplo usa o método de acesso baseado em Java HADOOP herdado. O exemplo a seguir mostra como criar a credencial no escopo do banco de dados para autenticação no Armazenamento do Azure. Especifique a chave de conta de Armazenamento do Azure no segredo da credencial do banco de dados. Você pode especificar qualquer cadeia de caracteres na identidade da credencial no escopo do banco de dados, pois ela não será usada durante a autenticação no Armazenamento do Azure.
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
B. Criar fonte de dados externa para referenciar o Azure Data Lake Storage Gen 1 ou 2 usando uma entidade de serviço
A conectividade do Azure Data Lake Store pode ser baseada no URI do ADLS e na entidade de serviço do aplicativo Microsoft Entra. A documentação para criar esse aplicativo pode ser encontrada em Autenticação do Data lake store usando a ID do 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. Criar fonte de dados externa para referenciar o Azure Data Lake Storage Gen2 usando a chave de conta de armazenamento
-- 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. Criar uma fonte de dados externa para o Azure Data Lake Storage Gen2 usando abfs://
Não há necessidade de especificar SECRET ao se conectar à conta do Azure Data Lake Storage Gen2 com o mecanismo de Identidade Gerenciada.
-- 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
);
Próximas etapas
- 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)
- Usando SAS (Assinatura de Acesso Compartilhado)
* Analytics
Platform System (PDW) *
Visão geral: Sistema de plataforma de análise
Aplica-se a: PDW (Analytics Platform System)
Cria uma fonte de dados externa para consultas do PolyBase. Fontes de dados externas são usadas para estabelecer a conectividade e a compatibilidade com estes casos de uso: Virtualização de dados e carregamento dados usando o PolyBase.
Sintaxe
Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe 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 o nome da fonte de dados definido pelo usuário. O nome precisa ser exclusivo no servidor no Analytics Platform System (PDW).
LOCALIZAÇÃO = '<prefix>://<path[:port]>'
Fornece o protocolo de conectividade e o caminho para a fonte de dados externa.
Fonte de dados externa | Prefixo de localização do conector | Caminho de local |
---|---|---|
Cloudera CDH ou Hortonworks HDP | hdfs |
<Namenode>[:port] |
Conta de Armazenamento do Azure | wasb[s] |
<container>@<storage_account>.blob.core.windows.net |
Caminho de local:
<Namenode>
= o nome do computador, o URI do serviço de nome ou o endereço IP doNamenode
no cluster do Hadoop. O PolyBase deve resolver qualquer nome DNS usado pelo cluster do Hadoop.port
= a porta em que fonte de dados externa está escutando. No Hadoop, a porta pode ser encontrada usando o parâmetro de configuraçãofs.defaultFS
. O padrão é 8020.<container>
= o contêiner da conta de armazenamento que contém os dados. Os contêineres raiz são somente leitura, não é possível gravar dados no contêiner.<storage_account>
= o nome da conta de armazenamento do recurso do Azure.
Observações e orientação adicionais ao definir o local:
- O mecanismo do PDW não verifica a existência da fonte de dados externa quando o objeto é criado. Para validar, crie uma tabela externa usando a fonte de dados externa.
- Use a mesma fonte de dados externa para todas as tabelas ao consultar o Hadoop para garantir semântica de consulta consistente.
wasbs
é recomendado, pois os dados serão enviados usando uma conexão TLS segura.- Não há suporte para Namespaces Hierárquicos quando usados com contas de Armazenamento do Azure em wasb://.
- Para garantir consultas do PolyBase com êxito durante um failover
Namenode
do Hadoop, considere usar um endereço IP virtual para oNamenode
do cluster do Hadoop. Se você não fizer isso, execute um comando ALTER EXTERNAL DATA SOURCE para apontar para o novo local.
CREDENTIAL = credential_name
Especifica uma credencial no escopo do banco de dados para a autenticação na fonte de dados externa.
Observações e orientações adicionais ao criar uma credencial:
- Para carregar dados do Armazenamento do Azure no Azure Synapse, use uma Chave de Armazenamento do Azure.
CREDENTIAL
será necessário apenas se os dados tiverem sido protegidos.CREDENTIAL
não é necessário para conjuntos de dados que permitem acesso anônimo.
TYPE = [ HADOOP ]
Especifica o tipo de fonte de dados externa que está sendo configurada. Esse parâmetro não é sempre necessário.
- Use HADOOP quando a fonte de dados externa for Cloudera CDH, Hortonworks HDP ou Armazenamento do Azure.
Para obter um exemplo de como usar TYPE
= HADOOP
para carregar dados do Armazenamento do Azure, confira Criar uma fonte de dados externa para referenciar o Hadoop.
RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'
No SQL Server 2019 (15.x), não especifique RESOURCE_MANAGER_LOCATION a menos que se conecte ao Cloudera CDH, Hortonworks HDP, uma conta Armazenamento Azure.
Configure esse valor opcional ao se conectar à Cloudera CDH, ao Hortonworks HDP ou a uma conta de Armazenamento do Microsoft Azure. Para ver uma lista completa de versões do Hadoop compatíveis, veja Configuração de conectividade do PolyBase (Transact-SQL).
Quando o RESOURCE_MANAGER_LOCATION
for definido, o otimizador de consulta tomará uma decisão baseada em custo para aprimorar o desempenho. Um trabalho MapReduce pode ser usado para aplicar a computação para o Hadoop. Especificar o RESOURCE_MANAGER_LOCATION
pode reduzir significativamente o volume de dados transferidos entre o Hadoop e o SQL, o que pode levar a um desempenho de consultas aprimorado.
Se o Resource Manager não tiver sido especificado, o envio de computação por push para o Hadoop estará desabilitado para consultas do PolyBase. Criar fonte de dados externa para referenciar o Hadoop com aplicação habilitada apresenta um exemplo concreto e diretrizes adicionais.
O valor de RESOURCE_MANAGER_LOCATION e não é validado quando você cria a fonte de dados externa. Inserir um valor incorreto pode causar falha de consulta em tempo de execução sempre que for feita uma tentativa de aplicação, uma vez que o valor fornecido não poderá ser resolvido.
Para que o PolyBase funcione corretamente com uma fonte de dados externa do Hadoop, as portas para os seguintes componentes de cluster do Hadoop devem estar abertas:
- Portas HDFS
- Namenode
- DataNode
- Resource Manager
- Envio do trabalho
- Histórico de trabalho
Se a porta não for especificada, o valor padrão será escolhido usando a definição atual da configuração 'conectividade do Hadoop'.
Conectividade do Hadoop | Porta do Gerenciador de Recursos padrão |
---|---|
1 | 50300 |
2 | 50300 |
3 | 8021 |
4 | 8032 |
5 | 8050 |
6 | 8032 |
7 | 8050 |
A tabela a seguir mostra as portas padrão para esses componentes. Existe uma dependência de versão do Hadoop e a possibilidade de configuração personalizada que não usa a atribuição de porta padrão.
Componente do cluster do Hadoop | Pipe Padrão |
---|---|
NameNode | 8020 |
DataNode (transferência de dados, porta IPC sem privilégios) | 50010 |
DataNode (transferência de dados, porta IPC com privilégios) | 1019 |
Envio de Trabalho do Resource Manager (Hortonworks 1.3) | 50300 |
Envio de Trabalho do Resource Manager (Cloudera 4.3) | 8021 |
Envio de Trabalho do Resource Manager (Hortonworks 2.0 no Windows, Cloudera 5.x no Linux) | 8032 |
Envio de Trabalho do Resource Manager (Hortonworks 2.x, 3.0 no Linux, Hortonworks 2.1-3 no Windows) | 8050 |
Histórico de Trabalhos do Resource Manager | 10020 |
Permissões
Requer permissão CONTROL
no banco de dados no Analytics Platform System (PDW).
Observação
Nas versões anteriores do PDW, a criação de fonte de dados externa exigia as permissões ALTER ANY EXTERNAL DATA SOURCE
.
Bloqueio
Usa um bloqueio compartilhado no objeto EXTERNAL DATA SOURCE
.
Segurança
O PolyBase dá suporte para autenticação baseada em proxy para a maioria das fontes de dados externas. Crie uma credencial no escopo do banco de dados para criar a conta proxy.
No momento, não há suporte para um token SAS com o tipo HADOOP
. Só há suporte para o tipo = BLOB_STORAGE
quando uma chave de acesso da conta de armazenamento é usada. Tentar criar uma fonte de dados externa com o tipo HADOOP
e uma credencial SAS falha com o seguinte erro:
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.'
Exemplos
a. Criar uma fonte de dados externa para referenciar o Hadoop
Para criar uma fonte de dados externa para referenciar o Hortonworks HDP ou o Cloudera CDH, especifique o nome do computador ou o endereço IP do Namenode
do Hadoop e a porta.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
LOCATION = 'hdfs://10.10.10.10:8050',
TYPE = HADOOP
);
B. Criar uma fonte de dados externa para referenciar o Hadoop com aplicação habilitada
Especifique a opção RESOURCE_MANAGER_LOCATION
para habilitar a computação de aplicação para Hadoop em consultas do PolyBase. Uma vez habilitado, o PolyBase toma uma decisão baseada em custo para determinar se a computação de consulta deve ser enviada por push para o 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. Criar uma fonte de dados externa para referenciar o Hadoop protegido pelo Kerberos
Para verificar se o cluster do Hadoop está protegido pelo Kerberos, verifique o valor da propriedade hadoop.security.authentication
no core-site.xml do Hadoop. Para referenciar um cluster do Hadoop protegido pelo Kerberos, você precisa especificar uma credencial no escopo do banco de dados contendo o nome de usuário e a senha do Kerberos. A chave mestra do banco de dados é usada para criptografar o segredo da credencial no escopo do banco de dados.
-- 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. Criar fonte de dados externa para acessar dados no Armazenamento do Azure usando a interface wasb://
Neste exemplo, a fonte de dados externa é uma conta de Armazenamento do Azure V2 chamada logs
. O contêiner de armazenamento é chamado daily
. A fonte de dados externa do Armazenamento do Azure destina-se somente a transferência de dados. Não dá suporte a aplicação de predicado. Não há suporte para namespaces hierárquicos ao acessar dados por meio da interface wasb://
. Observe que, ao se conectar ao Armazenamento do Azure por meio do conector WASB[s], a autenticação deve ser feita com uma chave de conta de armazenamento, não com uma SAS (assinatura de acesso compartilhado).
Este exemplo mostra como criar a credencial no escopo do banco de dados para autenticação no Armazenamento do Azure. Especifique a chave de conta de Armazenamento do Azure no segredo da credencial do banco de dados. Você pode especificar qualquer cadeia de caracteres na identidade da credencial no escopo do banco de dados, pois ela não será usada durante a autenticação no Armazenamento do Azure.
-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = '<my_account>',
SECRET = '<azure_storage_account_key>';
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
CREDENTIAL = AzureStorageCredential,
TYPE = HADOOP
);
Próximas etapas
* Instância Gerenciada de SQL *
Visão geral: Instância Gerenciada do Azure SQL
Aplica-se a: Instância Gerenciada de SQL do Azure
Cria uma fonte de dados externa na Instância Gerenciada de SQL do Azure. Para obter informações completas, confira Virtualização de dados com a Instância Gerenciada de SQL do Azure.
A virtualização de dados na Instância Gerenciada de SQL do Azure fornece acesso a dados externos em uma variedade de formatos de arquivo por meio da sintaxe T-SQL OPENROWSET ou da sintaxe CREATE EXTERNAL TABLE do T-SQL.
Sintaxe
Para obter mais informações sobre as convenções de sintaxe, confira Convenções de sintaxe Transact-SQL.
CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
[ [ , ] CREDENTIAL = <credential_name> ]
)
[ ; ]
Argumentos
data_source_name
Especifica o nome da fonte de dados definido pelo usuário. O nome deve ser exclusivo no banco de dados.
LOCALIZAÇÃO = '<prefix>://<path[:port]>'
Fornece o protocolo de conectividade e o caminho para a fonte de dados externa.
Fonte de dados externa | Prefixo de local | Caminho de local |
---|---|---|
Armazenamento do Blobs do Azure | abs |
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name> |
Serviço do Azure Data Lake Gen2 | adls |
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name> |
O Mecanismo de Banco de Dados não verifica a existência da fonte de dados externa quando o objeto é criado. Para validar, crie uma tabela externa usando a fonte de dados externa.
Não adicione parâmetros de assinatura de acesso compartilhado, nome de arquivo ou / à direita no fim da URL LOCATION
ao configurar uma fonte de dados externa para operações em massa.
CREDENTIAL = credential_name
Especifica uma credencial no escopo do banco de dados para a autenticação na fonte de dados externa.
Observações e orientações adicionais ao criar uma credencial:
- Para carregar dados do Armazenamento do Azure na Instância Gerenciada de SQL do Azure, use um token SAS (Assinatura de Acesso Compartilhado).
CREDENTIAL
será necessário apenas se os dados tiverem sido protegidos.CREDENTIAL
não é necessário para conjuntos de dados que permitem acesso anônimo.- Se uma credencial for necessária, a credencial deverá ser criada usando
Managed Identity
ouSHARED ACCESS SIGNATURE
como a IDENTIDADE. Para criar uma credencial no escopo do banco de dados, veja CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).
Para usar a identidade do serviço gerenciado com a credencial no escopo do banco de dados:
Especifique
WITH IDENTITY = 'Managed Identity'
- Use a identidade de serviço gerenciada atribuída pelo sistema da Instância Gerenciada de SQL do Azure, que deverá ser habilitada se ela for usada para essa finalidade.
Conceda a função Leitor do RBAC do Azure à identidade de serviço gerenciada atribuída pelo sistema da Instância Gerenciada de SQL do Azure aos contêineres do Armazenamento de Blobs do Azure necessários. Por exemplo, para usar o portal do Azure, confira Atribuir funções do Azure usando o portal do Azure.
Para criar uma SAS (assinatura de acesso compartilhado) para a credencial com escopo do banco de dados:
Especifique
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = ...
Há várias maneiras de criar uma assinatura de acesso compartilhado:
- Você pode obter um token SAS navegando até o Portal do Azure -><Sua_Conta_de_Armazenamento> -> Assinatura de Acesso Compartilhado -> Configurar permissões -> Gerar SAS e cadeia de conexão. Para obter mais informações, confira Gerar uma assinatura de acesso compartilhado.
- Você pode criar e configurar uma SAS com o Gerenciador de Armazenamento do Azure.
- Você pode criar uma SAS programaticamente por meio do PowerShell, da CLI do Azure, do .NET e da API REST. Para obter mais informações, confira Conceder acesso limitado a recursos de Armazenamento do Azure usando SAS (assinaturas de acesso compartilhado).
O token SAS deve ser configurado da seguinte maneira:
- Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
?
à esquerda quando configurado como o SEGREDO. - Use um período de término válido (todas as datas estão no horário UTC).
- Conceder pelo menos permissão de leitura no arquivo que deve ser carregado (por exemplo
srt=o&sp=r
). Várias assinaturas de acesso compartilhado podem ser criadas para diferentes casos de uso. As permissões devem ser concedidas da seguinte maneira:
Ação Permissão Ler dados de um arquivo Ler Leitura de dados de vários arquivos e subpastas Leitura e listagem Uso de CETAS (Criar Tabela Externa como Seleção) Leitura, criação e gravação - Quando um token SAS é gerado, ele inclui um ponto de interrogação ("?") no início do token. Exclua o
Permissões
Requer a permissão CONTROL
no banco de dados na Instância Gerenciada de SQL do Azure.
Bloqueio
Usa um bloqueio compartilhado no objeto EXTERNAL DATA SOURCE
.
Exemplos
Para ver mais exemplos, confira Virtualização de dados com a Instância Gerenciada de SQL do Azure.
a. Consultar dados externos da Instância Gerenciada de SQL do Azure com OPENROWSET ou uma tabela externa
Para obter mais exemplos, confira Criar fonte de dados externa ou veja Virtualização de dados com a Instância Gerenciada de SQL do Azure.
Crie a chave mestra do banco de dados, se ela não existir.
-- Optional: Create MASTER KEY if it doesn't exist in the database: CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong Password>' GO
Crie a credencial com escopo do banco de dados usando um token SAS. Você também pode usar uma identidade gerenciada.
CREATE DATABASE SCOPED CREDENTIAL MyCredential WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<KEY>' ; --Removing leading '?' GO
Crie a fonte de dados externa usando a 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 o arquivo de dados parquet na fonte de dados externa usando a sintaxe do T-SQL OPENROWSET, contando com a inferência de esquema para explorar rapidamente os dados sem conhecer o 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;
Ou consulte os dados usando a cláusula WITH da OPENROWSET, em vez de contar com a inferência de esquema, que pode aumentar o custo de execução da consulta. Em um CSV, não há suporte para a inferência de esquema.
--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;
Ou crie um FORMATO DE ARQUIVO EXTERNO e uma TABELA EXTERNA para consultar os dados como uma tabela 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