Compartilhar via

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

  • Artigo

Neste tutorial, você aprenderá a criar um LDW (data warehouse lógico) no armazenamento do Azure e no 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;

Essa ordenação fornecerá o desempenho ideal 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 cadeia de conexão que descrevem em que local os dados são colocados 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 = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';

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 conceder 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 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=synapselink-cosmosdb-sqlsample;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 logon 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 exibições e as tabelas externas preparadas.
  • Você deverá fornecer a permissão SELECT somente às tabelas que alguns usuários deverão conseguir usar.
  • Se você estiver fornecendo acesso aos dados usando as exibições, conceda a permissão REFERENCES para a 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