Tutorial: Criar um data warehouse lógico com o pool de SQL sem servidor

Tip

Microsoft Fabric Data Warehouse é um armazém relacional de escala empresarial com base de data lake, arquitetura pronta para o futuro, IA integrada e novos recursos. Se você não estiver familiarizado com o data warehouse, comece com Fabric Data Warehouse. As cargas de trabalho existentes de pools de SQL dedicados podem ser atualizadas para Fabric para acessar novos recursos em ciência de dados, análise em tempo real e relatórios.

Neste tutorial, você aprenderá a criar um data warehouse lógico (LDW) sobre o Azure Storage e o Azure Cosmos DB.

O LDW é uma camada relacional criada nas fontes de dados do Azure, como o ADLS (Azure Data Lake Storage), o armazenamento analítico do Azure Cosmos DB ou o Armazenamento de Blobs do Azure.

Criar um banco de dados do LDW

Você precisará criar um banco de dados personalizado no qual armazenará as exibições e as tabelas externas que referenciam fontes de dados externas.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Esse agrupamento oferece desempenho otimizado na leitura do Parquet e do Azure Cosmos DB. Se você não quiser especificar a ordenação de banco de dados, especifique essa ordenação na definição de coluna.

Configurar fontes de dados e formatos

Como uma primeira etapa, você precisará configurar a fonte de dados e especificar o formato de arquivo dos dados armazenados remotamente.

Criar a fonte de dados

As fontes de dados representam informações de string de conexão que descrevem onde seus dados são armazenados e como se autenticar na fonte de dados.

Um exemplo de definição de fonte de dados que referencia o Conjunto de Dados Abertos do Azure ECDC COVID 19 é mostrado no seguinte exemplo:

CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/'
);

Um chamador poderá acessar a fonte de dados sem uma credencial se um proprietário da fonte de dados permitir o acesso anônimo ou fornecer acesso explícito à identidade do Microsoft Entra do chamador.

Você pode definir explicitamente uma credencial personalizada que será usada ao acessar dados na fonte de dados externa.

Como pré-requisito, você precisará criar uma chave mestra no banco de dados:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Setup you password - you need to create master key only once';

Na fonte de dados externa a seguir, o pool de SQL do Synapse deverá usar uma identidade gerenciada do workspace para acessar os dados no armazenamento.

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity';
GO
CREATE EXTERNAL DATA SOURCE ecdc_cases WITH (
    LOCATION = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/',
    CREDENTIAL = WorkspaceIdentity
);

Para acessar o armazenamento analítico do Azure Cosmos DB, você precisará definir uma credencial que contenha uma chave de conta somente leitura do Azure Cosmos DB.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '<yourcosmosdbaccountkey>';

Qualquer usuário com a função Administrador do Azure Synapse pode usar essas credenciais para acessar o armazenamento do Azure Data Lake ou o armazenamento analítico do Azure Cosmos DB. Se você tiver usuários com poucos privilégios que não têm a função de Administrador do Synapse, será necessário conceder a eles uma permissão explícita para fazer referência a essas credenciais no escopo do banco de dados:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::WorkspaceIdentity TO <user>
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO <user>
GO

Veja mais detalhes na página permissões de credencial no escopo do banco de dados.

Definir formatos de arquivo externos

Os formatos de arquivo externos definem a estrutura dos arquivos armazenados na fonte de dados externa. Você pode definir formatos de arquivo externos Parquet e CSV:

CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT CsvFormat WITH (  FORMAT_TYPE = DELIMITEDTEXT );

Para obter mais informações, consulte Usar tabelas externas com SQL do Synapse e CRIAR FORMATO DE ARQUIVO EXTERNO para descrever o formato de arquivos CSV ou Parquet.

Explorar os seus dados

Depois de configurar as fontes de dados, use a função OPENROWSET para explorar os dados. A função OPENROWSET lê o conteúdo de uma fonte de dados remota (por exemplo, o arquivo) e retorna o conteúdo como um conjunto de linhas.

select top 10  *
from openrowset(bulk 'latest/ecdc_cases.parquet',
                data_source = 'ecdc_cases',
                format='parquet') as a

A função OPENROWSET fornecerá informações sobre as colunas nos arquivos ou nos contêineres externos e permitirá que você defina um esquema das exibições e das tabelas externas.

Criar tabelas externas no Armazenamento do Azure

Depois de descobrir o esquema, crie exibições e tabelas externas com base nas fontes de dados externas. A prática recomendada é organizar as tabelas e as exibições em esquemas de bancos de dados. Na seguinte consulta, você poderá criar um esquema em que colocará todos os objetos que acessam o conjunto de dados ECDC COVID no Azure Data Lake Storage:

create schema ecdc_adls;

Os esquemas de banco de dados são úteis para agrupar os objetos e definir permissões por esquema.

Depois de definir os esquemas, você poderá criar tabelas externas que referenciam os arquivos. A seguinte tabela a seguir referencia o arquivo Parquet ECDC COVID colocado no armazenamento do Azure:

create external table ecdc_adls.cases (
    date_rep                   date,
    day                        smallint,
    month                      smallint,
    year                       smallint,
    cases                      smallint,
    deaths                     smallint,
    countries_and_territories  varchar(256),
    geo_id                     varchar(60),
    country_territory_code     varchar(16),
    pop_data_2018              int,
    continent_exp              varchar(32),
    load_date                  datetime2(7),
    iso_country                varchar(16)
) with (
    data_source= ecdc_cases,
    location = 'latest/ecdc_cases.parquet',
    file_format = ParquetFormat
);

Use os menores tipos possíveis para colunas de cadeia de caracteres e número para otimizar o desempenho das consultas.

Criar exibições no Azure Cosmos DB

Como alternativa a tabelas externas, você pode criar exibições com base nos dados externos.

De modo semelhante às tabelas mostradas no exemplo anterior, você deverá colocar as exibições em esquemas separados:

create schema ecdc_cosmosdb;

Agora você poderá criar uma exibição no esquema que referencia um contêiner do Azure Cosmos DB:

CREATE OR ALTER VIEW ecdc_cosmosdb.Ecdc
AS SELECT *
FROM OPENROWSET(
      PROVIDER = 'CosmosDB',
      CONNECTION = 'Account=yourcosmosdbaccount;Database=covid',
      OBJECT = 'Ecdc',
      CREDENTIAL = 'MyCosmosDbAccountCredential'
    ) WITH
     ( date_rep varchar(20), 
       cases bigint,
       geo_id varchar(6) 
     ) as rows

Para otimizar o desempenho, você deverá usar os menores tipos possíveis na definição de esquema WITH.

Observação

Coloque a chave de conta do Azure Cosmos DB em uma credencial separada e referencie essa credencial na função OPENROWSET. Não mantenha a chave de conta na definição de exibição.

Acesso e permissões

Como uma etapa final, você deverá criar usuários de banco de dados que devem conseguir acessar o LDW e conceder permissões a eles para selecionar dados das exibições e das tabelas externas. No seguinte script, você pode ver como adicionar um novo usuário que será autenticado usando a identidade do Microsoft Entra:

CREATE USER [jovan@contoso.com] FROM EXTERNAL PROVIDER;
GO

Em vez de entidades de segurança do Microsoft Entra, você pode criar entidades de segurança do SQL que se autenticam com o nome de usuário e senha.

CREATE LOGIN [jovan] WITH PASSWORD = 'My Very strong Password ! 1234';
CREATE USER [jovan] FROM LOGIN [jovan];

Em ambos os casos, você pode atribuir permissões aos usuários.

DENY ADMINISTER DATABASE BULK OPERATIONS TO [jovan@contoso.com]
GO
GRANT SELECT ON SCHEMA::ecdc_adls TO [jovan@contoso.com]
GO
GRANT SELECT ON OBJECT::ecdc_cosmosDB.cases TO [jovan@contoso.com]
GO
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::MyCosmosDbAccountCredential TO [jovan@contoso.com]
GO

As regras de segurança dependem das suas políticas de segurança. Algumas diretrizes genéricas são:

  • Você deverá negar a permissão ADMINISTER DATABASE BULK OPERATIONS para os novos usuários, porque eles devem conseguir ler os dados somente usando as tabelas externas e as exibições que você preparou.
  • Você deverá fornecer a permissão SELECT somente às tabelas que alguns usuários deverão conseguir usar.
  • Se você estiver fornecendo acesso a dados usando as visualizações, conceda a permissão REFERENCES à credencial que será usada para acessar a fonte de dados externa.

Esse usuário tem permissões mínimas necessárias para consultar dados externos. Caso você deseje criar um usuário avançado que possa configurar permissões, tabelas externas e exibições, conceda a permissão CONTROL ao usuário:

GRANT CONTROL TO [jovan@contoso.com]

Segurança baseada em função

Em vez de atribuir permissões a usuários individuais, uma melhor prática é organizar os usuários em funções e gerenciar a permissão para cada função. O exemplo de código abaixo cria uma função que representa as pessoas que podem analisar casos de COVID-19 e adiciona três usuários a essa função:

CREATE ROLE CovidAnalyst;

ALTER ROLE CovidAnalyst ADD MEMBER [jovan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [milan@contoso.com];
ALTER ROLE CovidAnalyst ADD MEMBER [petar@contoso.com];

Você pode atribuir as permissões a todos os usuários que pertençam ao grupo:

GRANT SELECT ON SCHEMA::ecdc_cosmosdb TO [CovidAnalyst];
GO
DENY SELECT ON SCHEMA::ecdc_adls TO [CovidAnalyst];
GO
DENY ADMINISTER DATABASE BULK OPERATIONS TO [CovidAnalyst];

Esse controle de acesso de segurança baseado em função pode simplificar o gerenciamento de suas regras de segurança.

Próximas etapas

  • Last updated on