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

  • Artigo

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

  • Função OPENROWSET, que habilita consultas ad hoc sobre os arquivos no Armazenamento do Azure.
  • A tabela externa, que é uma estrutura de dados predefinida criada na parte superior do 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 de segurança do Microsoft Entra) e autenticação SAS (padrão para entidades de segurança do SQL).

Consultar arquivos usando OPENROWSET

O OPENROWSET permitirá que os usuários consultem arquivos externos no armazenamento do Azure se tiverem acesso ao armazenamento. Um usuário que está conectado ao pool de 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.
  • Usuário do SQL – OPENROWSET acessará o armazenamento com acesso anônimo ou poderá ser representado usando o token SAS ou a identidade gerenciada do workspace.

As entidades de segurança do SQL também podem usar OPENROWSET para consultar diretamente os arquivos protegidos com tokens SAS ou identidade gerenciada do workspace. Se um usuário do SQL executar essa função, um usuário avançado com a permissão ALTER ANY CREDENTIAL deverá criar uma credencial no escopo do servidor que corresponda à URL na função (usando o nome e o contêiner de armazenamento) 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 do SQL não tiver a permissão de referências para essa credencial, o erro será retornado. As entidades de segurança do SQL não podem representar usando uma identidade do Microsoft Entra.

Observação

Esta versão do OPENROWSET foi projetada para uma exploração de dados rápida e fácil usando a autenticação padrão. Para utilizar a representação ou a identidade gerenciada, use OPENROWSET com a DATA_SOURCE descrita na próxima seção.

Consultar fontes de dados usando OPENROWSET

OPENROWSET permite que o usuário consulte 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 precisa conseguir acessar os arquivos. Os usuários devem ser representados usando o token SAS ou 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 os arquivos na fonte de dados referenciada (no momento, SAS e Identidade Gerenciada). O usuário avançado com permissão de CONTROL DATABASE precisaria criar DATABASE SCOPED CREDENTIAL, que serão usadas para acessar o armazenamento, e EXTERNAL DATA SOURCE, que especifica a URL da fonte de dados e da 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 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 que o logon execute a função OPENROWSET.
    • ADMINISTER DATABASE BULK OPERATIONS permite que o usuário no escopo do banco de dados execute a função OPENROWSET.
  • REFERENCES DATABASE SCOPED CREDENTIAL para a credencial que é referenciada no EXTERNAL DATA SOURCE.

EXTERNAL TABLE

O usuário com as permissões para ler a tabela pode acessar arquivos externos usando uma EXTERNAL TABLE criada na parte superior do conjunto de arquivos e pastas 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ê os dados dessa tabela deve ser capaz de acessar os arquivos. Os usuários devem ser representados usando o token SAS ou Identidade Gerenciada do espaço de trabalho se não puderem acessar diretamente os arquivos usando a Identidade do Microsoft Entra ou acesso anônimo.

DATABASE SCOPED CREDENTIAL especifica como acessar os arquivos na fonte de dados referenciada. O usuário com a permissão CONTROL DATABASE precisaria criar uma DATABASE SCOPED CREDENTIAL para acessar o armazenamento e uma EXTERNAL DATA SOURCE que especificaria a URL da fonte de dados e a credencial que deveriam 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 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 EXTERNAL TABLE

EXTERNAL TABLE permite que você leia dados dos arquivos que são referenciados por meio da 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 os dados:

  • Permissão SELECT na tabela externa
  • Permissão REFERENCES DATABASE SCOPED CREDENTIAL se DATA SOURCE tiver CREDENTIAL

Permissões

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

Consulta Permissões necessárias
OPENROWSET (BULK) sem datasource ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS ou logon do SQL devem ter REFERENCES CREDENTIAL::<URL> para armazenamento protegido por SAS
OPENROWSET(BULK) com datasource sem credencial ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) com datasource com credencial REFERENCES DATABASE SCOPED CREDENTIAL e um de ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS
CREATE EXTERNAL DATA SOURCE ALTER ANY EXTERNAL DATA SOURCE e REFERENCES DATABASE SCOPED CREDENTIAL
CREATE EXTERNAL TABLE CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT e ALTER ANY EXTERNAL DATA SOURCE
SELECT FROM EXTERNAL TABLE 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 CREDENTIAL ou SELECT TABLE para cada tabela/exibição/função na consulta + permissão para leitura e gravação no armazenamento

Próximas etapas

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