Criar objetos de banco de dados externos

Concluído

Você pode usar a função OPENROWSET em consultas SQL executadas no banco de dados mestre padrão do pool SQL interno sem servidor para explorar dados no data lake. No entanto, às vezes você pode querer criar um banco de dados personalizado que contém alguns objetos que facilitam o trabalho com dados externos no data lake que você precisa consultar com freqüência.

Criar uma base de dados

Você pode criar um banco de dados em um pool SQL sem servidor da mesma forma que faria em uma instância do SQL Server. Você pode usar a interface gráfica no Synapse Studio ou uma instrução CREATE DATABASE. Uma consideração é definir o agrupamento do seu banco de dados para que ele ofereça suporte à conversão de dados de texto em arquivos para tipos de dados Transact-SQL apropriados.

O código de exemplo a seguir cria um banco de dados chamado salesDB com um agrupamento que facilita a importação de dados de texto codificados UTF-8 para colunas VARCHAR.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Criando uma fonte de dados externa

Você pode usar a função OPENROWSET com um caminho BULK para consultar dados de arquivo de seu próprio banco de dados, assim como pode fazer no banco de dados mestre , mas se você planeja consultar dados no mesmo local com frequência, é mais eficiente definir uma fonte de dados externa que faça referência a esse local. Por exemplo, o código a seguir cria uma fonte de dados chamada files para a pasta hipotética https://mydatalake.blob.core.windows.net/data/files/ :

CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/'
)

Um benefício de uma fonte de dados externa é que você pode simplificar uma consulta OPENROWSET para usar a combinação da fonte de dados e o caminho relativo para as pastas ou arquivos que deseja consultar:

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

Neste exemplo, o parâmetro BULK é usado para especificar o caminho relativo para todos os arquivos .csv na pasta orders , que é uma subpasta da pasta files referenciada pela fonte de dados.

Outro benefício de usar uma fonte de dados é que você pode atribuir uma credencial para a fonte de dados usar ao acessar o armazenamento subjacente, permitindo que você forneça acesso aos dados por meio do SQL sem permitir que os usuários acessem os dados diretamente na conta de armazenamento. Por exemplo, o código a seguir cria uma credencial que usa uma assinatura de acesso compartilhado (SAS) para autenticar na conta de armazenamento subjacente do Azure que hospeda o data lake.

CREATE DATABASE SCOPED CREDENTIAL sqlcred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';
GO

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = sqlcred
);
GO

Gorjeta

Além da autenticação SAS, você pode definir credenciais que usam identidade gerenciada (a identidade do Microsoft Entra usada pelo espaço de trabalho do Azure Synapse), uma entidade de segurança específica do Microsoft Entra ou autenticação de passagem com base na identidade do usuário que executa a consulta (que é o tipo padrão de autenticação). Para saber mais sobre como usar credenciais em um pool SQL sem servidor, consulte o artigo Controlar o acesso à conta de armazenamento para pool SQL sem servidor no Azure Synapse Analytics na documentação do Azure Synapse Analytics.

Criando um formato de arquivo externo

Embora uma fonte de dados externa simplifique o código necessário para acessar arquivos com a função OPENROWSET, você ainda precisa fornecer detalhes de formato para o arquivo que está sendo acessado; que pode incluir várias configurações para arquivos de texto delimitados. Você pode encapsular essas configurações em um formato de arquivo externo, da seguinte forma:

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

Depois de criar formatos de arquivo para os arquivos de dados específicos com os quais você precisa trabalhar, você pode usar o formato de arquivo para criar tabelas externas, conforme discutido a seguir.

Criando uma tabela externa

Quando você precisa executar muitas análises ou relatórios de arquivos no data lake, usar a função OPENROWSET pode resultar em código complexo que inclui fontes de dados e caminhos de arquivo. Para simplificar o acesso aos dados, você pode encapsular os arquivos em uma tabela externa; quais usuários e aplicativos de relatório podem consultar usando uma instrução SQL SELECT padrão como qualquer outra tabela de banco de dados. Para criar uma tabela externa, use a instrução CREATE EXTERNAL TABLE, especificando o esquema de coluna como para uma tabela padrão e incluindo uma cláusula WITH especificando a fonte de dados externa, o caminho relativo e o formato de arquivo externo para seus dados.

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

Ao criar um banco de dados que contém os objetos externos discutidos nesta unidade, você pode fornecer uma camada de banco de dados relacional sobre arquivos em um data lake, facilitando o acesso de muitos analistas de dados e ferramentas de relatório aos dados usando a semântica de consulta SQL padrão.