Partilhar via

Acessar armazenamento externo usando o pool SQL sem servidor no Azure Synapse Analytics

Este artigo descreve como os usuários podem ler dados dos arquivos armazenados no Armazenamento do Azure no pool SQL sem servidor. Os usuários têm as seguintes opções para acessar o armazenamento:

  • Função OPENROWSET que permite consultas ad-hoc sobre os arquivos no Armazenamento do Azure.
  • Tabela externa que é uma estrutura de dados predefinida construída sobre um conjunto de arquivos externos.

O usuário pode usar diferentes métodos de autenticação , como autenticação de passagem do Microsoft Entra (padrão para entidades do Microsoft Entra) e autenticação SAS (padrão para entidades SQL).

Consultar arquivos usando OPENROWSET

OPENROWSET permite que os usuários consultem arquivos externos no armazenamento do Azure se tiverem acesso ao armazenamento. Um usuário conectado ao pool SQL sem servidor deve usar a seguinte consulta para ler o conteúdo dos arquivos no armazenamento do Azure:

SELECT * FROM
 OPENROWSET(BULK 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/*.parquet', format= 'parquet') as rows

O usuário pode acessar o armazenamento usando as seguintes regras de acesso:

  • Usuário do Microsoft Entra - OPENROWSET usará a identidade do chamador do Microsoft Entra para acessar o Armazenamento do Azure ou acessar o armazenamento com acesso anônimo.
  • Utilizador SQL – OPENROWSET acessará o armazenamento com acesso anónimo ou poderá ser imitado usando um token SAS ou identidade gerida do espaço de trabalho.

As entidades SQL também podem usar OPENROWSET para consultar diretamente arquivos protegidos com tokens SAS ou Identidade Gerenciada do espaço de trabalho. Se um utilizador SQL executar esta função, um utilizador avançado com ALTER ANY CREDENTIAL permissão deverá criar uma credencial ao nível do servidor que corresponda à URL na função (usando o nome de armazenamento e o contêiner) e conceder a permissão REFERENCES para essa credencial ao chamador da função OPENROWSET:

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

Se não houver nenhuma CREDENCIAL no nível do servidor que corresponda à URL ou se o usuário SQL não tiver permissão de referência para essa credencial, o erro será retornado. As entidades de segurança SQL não podem representar usando qualquer identidade do Microsoft Entra.

Observação

Esta versão do OPENROWSET foi projetada para exploração de dados rápida e fácil usando autenticação padrão. Para tirar proveito da representação ou da Identidade Gerida, use OPENROWSET com DATA_SOURCE descrito na próxima seção.

Consultar fontes de dados usando OPENROWSET

OPENROWSET permite ao usuário consultar os arquivos colocados em alguma fonte de dados externa:

SELECT * FROM
 OPENROWSET(BULK 'file/path/*.parquet',
 DATA_SOURCE = MyAzureInvoices,
 FORMAT= 'parquet') as rows

O usuário que executa essa consulta deve ser capaz de acessar os arquivos. Os usuários devem ser representados usando o token SAS ou a Identidade Gerenciada do espaço de trabalho se não puderem acessar diretamente os arquivos usando sua identidade do Microsoft Entra ou acesso anônimo.

DATABASE SCOPED CREDENTIAL especifica como acessar arquivos na fonte de dados referenciada (atualmente SAS e Identidade Gerenciada). Usuário avançado com CONTROL DATABASE permissão precisaria criar DATABASE SCOPED CREDENTIAL que será usado para acessar o armazenamento e EXTERNAL DATA SOURCE que especifica a URL da fonte de dados e a credencial que devem ser usadas:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&amp;sp=rwac&amp;se=2017-02-01T00:55:34Z&amp;st=201********' ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

O chamador deve ter uma das seguintes permissões para executar a função OPENROWSET:

  • Uma das permissões para executar OPENROWSET:
    • ADMINISTER BULK OPERATIONS permite iniciar sessão para executar a função OPENROWSET.
    • ADMINISTER DATABASE BULK OPERATIONS permite que o utilizador abrangido pela base de dados execute a função OPENROWSET.
  • REFERENCES DATABASE SCOPED CREDENTIAL para a credencial referenciada em EXTERNAL DATA SOURCE.

TABELA EXTERNA

O usuário com as permissões para ler a tabela pode acessar arquivos externos usando uma TABELA EXTERNA criada sobre o conjunto de pastas e arquivos do Armazenamento do Azure.

O usuário que tem permissões para criar tabela externa (por exemplo, CREATE TABLE e ALTER ANY CREDENTIAL ou REFERENCES DATABASE SCOPED CREDENTIAL) pode usar o seguinte script para criar uma tabela sobre a fonte de dados do Armazenamento do Azure:

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

O usuário que lê dados desta tabela deve ser capaz de acessar os arquivos. Os usuários devem ser representados usando o token SAS ou a Identidade Gerenciada do espaço de trabalho se não puderem acessar diretamente os arquivos usando sua identidade Microsoft Entra ou acesso anônimo.

DATABASE SCOPED CREDENTIAL especifica como acessar arquivos na fonte de dados referenciada. O utilizador com permissão CONTROL DATABASE precisaria criar uma CREDENCIAL LIMITADA À BASE DE DADOS que será usada para aceder ao armazenamento e uma FONTE DE DADOS EXTERNA que especifica a URL da fonte de dados e a credencial que deve ser usada:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL cred
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

Ler arquivos externos com TABELA EXTERNA

EXTERNAL TABLE permite que você leia dados dos arquivos que são referenciados via fonte de dados usando a instrução SQL SELECT padrão:

SELECT *
FROM dbo.DimProductsExternal

O chamador deve ter as seguintes permissões para ler dados:

  • SELECT permissão ATIVADO em tabela externa
  • REFERENCES DATABASE SCOPED CREDENTIAL permissão se DATA SOURCE tiver CREDENTIAL

Permissões

A tabela a seguir lista as permissões necessárias para as operações listadas acima.

Query Permissões necessárias
OPENROWSET(BULK) sem fonte de dados ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS, ou o login SQL deve ter REFERENCES CREDENTIAL::<URL> para armazenamento protegido por SAS
OPENROWSET(BULK) com origem de dados sem credencial ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) com origem de dados e credencial REFERENCES DATABASE SCOPED CREDENTIAL e um dos ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS
CRIAR FONTE DE DADOS EXTERNA ALTER ANY EXTERNAL DATA SOURCE e REFERENCES DATABASE SCOPED CREDENTIAL
CRIAR TABELA EXTERNA CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMATe ALTER ANY EXTERNAL DATA SOURCE
SELECIONAR A PARTIR DA TABELA EXTERNA SELECT TABLE e REFERENCES DATABASE SCOPED CREDENTIAL
CETAS Para criar tabela - CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCE, e ALTER ANY EXTERNAL FILE FORMAT. Para ler dados: ADMINISTER BULK OPERATIONS ou REFERENCES CREDENTIALSELECT TABLE por cada tabela/visualização/função na consulta + permissão R/W no armazenamento

Próximos passos

Agora você está pronto para continuar com os seguintes artigos de instruções:

  • Last updated on