Criar objetos de banco de dados externos

Concluído

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

Criação de um banco de dados

Você pode criar um banco de dados em um pool de SQL sem servidor da mesma forma que faria em uma instância do SQL Server. Você pode usar a interface gráfica do Synapse Studio ou uma instrução CREATE DATABASE. Uma consideração é definir a ordenação do banco de dados para que ele dê 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 uma ordenação que facilita a importação de dados de texto codificados utf-8 para colunas VARCHAR.

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

Como criar 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, da mesma forma que você pode 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 seguinte código cria uma fonte de dados chamada arquivos 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 você 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 do uso de 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 SAS (assinatura de acesso compartilhado) para se 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

Dica

Além da autenticação SAS, você pode definir credenciais que usam a identidade gerenciada (a identidade do Microsoft Entra usada pelo workspace do Azure Synapse), uma entidade de segurança específica do Microsoft Entra ou a 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 de SQL sem servidor, confira o artigo Controlar o acesso da conta de armazenamento para o pool de SQL sem servidor no Azure Synapse Analytics, na documentação do Azure Synapse Analytics.

Como criar 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 do arquivo que está sendo acessado; que podem incluir várias configurações para arquivos de texto delimitados. Você pode encapsular essas configurações em um formato de arquivo externo, da seguinte maneira:

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.

Como criar uma tabela externa

Quando você precisa executar muitas análises ou relatórios de arquivos no data lake, o uso da função OPENROWSET pode resultar em um código complexo com fontes de dados e caminhos de arquivo. Para simplificar o acesso aos dados, você pode encapsular os arquivos em uma tabela externa; que usuários e aplicativos de relatório podem consultar usando uma instrução SQL SELECT padrão, assim 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 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, tornando mais fácil para muitos analistas de dados e ferramentas de relatório acessar os dados usando semântica de consulta SQL padrão.