Aceder ao armazenamento externo com o conjunto 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 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.
  • Usuário SQL – OPENROWSET acessará o armazenamento com acesso anônimo ou pode ser representado usando token SAS ou identidade gerenciada 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 usuário SQL executar essa função, um usuário avançado com permissão deverá criar uma credencial com ALTER ANY CREDENTIAL escopo de servidor que corresponda à URL na função (usando nome de armazenamento e contêiner) e conceder 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 alguma identidade do Microsoft Entra.

Nota

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

Consultar origens de dados com 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 que especifica a URL da fonte de dados e EXTERNAL DATA SOURCE 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 no âmbito da 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 usuário com permissão CONTROL DATABASE 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 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 EM mesa 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.

Consulta Permissões obrigatórias
OPENROWSET(BULK) sem fonte de dados ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONSou o login SQL deve ter REFERENCES CREDENTIAL::<URL> para armazenamento protegido por SAS
OPENROWSET(BULK) com fonte de dados sem credencial ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) com fonte de dados com credencial REFERENCES DATABASE SCOPED CREDENTIAL e um dos ADMINISTER BULK OPERATIONS ou ADMINISTER DATABASE BULK OPERATIONS
CRIAR UMA ORIGEM DE DADOS EXTERNA ALTER ANY EXTERNAL DATA SOURCE e REFERENCES DATABASE SCOPED CREDENTIAL
CRIAR TABELA EXTERNA CREATE TABLE, , , ALTER ANY SCHEMAALTER ANY EXTERNAL FILE FORMATeALTER 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 SCHEMAALTER 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: