Partilhar via

Tutorial: Criar Data Warehouse lógico com pool SQL sem servidor

  • Artigo

Neste tutorial, você aprenderá a criar um Logical Data Warehouse (LDW) sobre o armazenamento do Azure e o Azure Cosmos DB.

LDW é uma camada relacional criada sobre fontes de dados do Azure, como o armazenamento do Azure Data Lake (ADLS), o armazenamento analítico do Azure Cosmos DB ou o armazenamento de Blob do Azure.

Criar um banco de dados LDW

Você precisa criar um banco de dados personalizado onde armazenará suas tabelas e exibições externas que fazem referência a fontes de dados externas.

CREATE DATABASE Ldw
      COLLATE Latin1_General_100_BIN2_UTF8;

Esse agrupamento fornecerá o desempenho ideal durante a leitura do Parquet e do Azure Cosmos DB. Se você não quiser especificar o agrupamento de banco de dados, certifique-se de especificar esse agrupamento na definição de coluna.

Configurar fontes de dados e formatos

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

Criar fonte de dados

As fontes de dados representam informações de cadeia de conexão que descrevem onde os dados são colocados e como autenticar na fonte de dados.

Um exemplo de definição de fonte de dados que faz referência ao Conjunto de Dados Abertos do Azure ECDC COVID 19 público é mostrado no exemplo a seguir:

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

Um chamador pode acessar a fonte de dados sem credencial se um proprietário da fonte de dados permitiu acesso anônimo ou deu acesso explícito à identidade do chamador do Microsoft Entra.

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

  • Identidade gerenciada do espaço de trabalho Synapse
  • Assinatura de acesso compartilhado do armazenamento do Azure
  • Nome da entidade de serviço personalizada ou identidade do aplicativo do Azure.
  • Chave de conta do Azure Cosmos DB somente leitura que permite ler o armazenamento analítico do Azure Cosmos DB.

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 seguinte fonte de dados externa, o pool Synapse SQL deve usar uma identidade gerenciada do espaço de trabalho para acessar 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ê precisa definir uma credencial contendo uma chave de conta do Azure Cosmos DB somente leitura.

CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';

Qualquer usuário com a função Synapse Administrator 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 privilégios baixos que não tenham a função de Administrador do Sinapse, será necessário conceder a eles uma permissão explícita para fazer referência a essas credenciais com escopo de banco de dados:

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

Encontre mais detalhes na página de permissões conceder DATABASE SCOPED CREDENTIAL.

Definir formatos de arquivo externos

Os formatos de arquivo externos definem a estrutura dos arquivos armazenados na fonte de dados externa. Você pode definir os formatos de arquivo externo 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 Synapse SQL e CREATE EXTERNAL FILE FORMAT para descrever o formato de arquivos CSV ou Parquet.

Explore os seus dados

Depois de configurar suas fontes de dados, você pode usar a OPENROWSET função para explorar seus dados. A função OPENROWSET lê o conteúdo de uma fonte de dados remota (por exemplo, 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 OPENROWSET função fornecerá informações sobre as colunas nos arquivos ou contêineres externos e permitirá que você defina um esquema de suas tabelas e exibições externas.

Criar tabelas externas no armazenamento do Azure

Depois de descobrir o esquema, você pode criar tabelas e exibições externas sobre suas fontes de dados externas. A boa prática é organizar suas tabelas e exibições em esquemas de bancos de dados. Na consulta a seguir, você pode criar um esquema onde colocará todos os objetos que estão acessando o conjunto de dados ECDC COVID no armazenamento do Azure data Lake:

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ê pode criar tabelas externas que estão fazendo referência aos arquivos. A tabela externa a seguir faz referência ao arquivo de parquet COVID do ECDC 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
);

Certifique-se de usar os menores tipos possíveis para colunas de cadeia de caracteres e números para otimizar o desempenho de suas consultas.

Criar vistas no Azure Cosmos DB

Como alternativa às tabelas externas, pode criar vistas sobre os seus dados externos.

Semelhante às tabelas mostradas no exemplo anterior, você deve colocar as exibições em esquemas separados:

create schema ecdc_cosmosdb;

Agora você pode criar um modo de exibição no esquema que está fazendo referência a 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ê deve usar os menores tipos possíveis na definição de WITH esquema.

Nota

Você deve colocar sua chave de conta do Azure Cosmos DB em uma credencial separada e fazer referência a essa credencial da OPENROWSET função. Não guarde a chave da sua conta na definição de vista.

Acesso e permissões

Como etapa final, você deve criar usuários de banco de dados que devem ser capazes de acessar seu LDW e dar-lhes permissões para selecionar dados das tabelas e exibições externas. No script a seguir, 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 do Microsoft Entra, você pode criar entidades SQL que se autenticam com o nome de logon e a 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 orientações genéricas são:

  • Você deve negar ADMINISTER DATABASE BULK OPERATIONS permissão aos novos usuários porque eles devem ser capazes de ler dados somente usando as tabelas e exibições externas que você preparou.
  • Você deve fornecer SELECT permissão apenas para as tabelas que algum usuário deve ser capaz de usar.
  • Se você estiver fornecendo acesso aos dados usando as exibições, deverá conceder REFERENCES permissão à credencial que será usada para acessar a fonte de dados externa.

Este usuário tem permissões mínimas necessárias para consultar dados externos. Se você quiser criar um usuário avançado que possa configurar permissões, tabelas externas e exibições, você pode dar CONTROL permissão ao usuário:

GRANT CONTROL TO [jovan@contoso.com]

Segurança baseada em funções

Em vez de atribuir permissões aos usos individuais, uma boa prática é organizar os usuários em funções e gerenciar permissões no nível da função. O exemplo de código a seguir cria uma nova função representando 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 pertencem 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óximos passos