Azure Synapse Analytics — PolyBase

PolyBase é uma ferramenta projetada para se beneficiar da natureza distribuída de sistemas para carregar e expor dados de forma mais eficiente possível.
Em termos grosseiros, PolyBase fornece uma interface entre sua plataforma de Data Lake e sua interface Azure Synapse Analytics (ASA).

Os dados em seu Data Lake podem ser expostos à sua instância do ASA como uma tabela externa, o que significa que os dados dentro do arquivo
arquivos podem ser operados como se fossem uma tabela tradicional.

Para usar um PolyBase será necessário seguir três passos.

1. Criar uma credencial com o escopo de banco de banco de dados.
2. Definir uma fonte de dados externa.
3. Especificar um formato de arquivo.

A credencial no escopo de banco de dados é usada para autenticar a instância do ASA em seu Data Lake.
As permissões aqui devem ser rígidamente controladas e bem planejadas.
Várias versões dessas credenciais podem ser necessárias para garantir a granularidade correta.
As partes das informações necessárias são o ID da entidade de serviço, e o Secret/Key que será criado para a entidade de serviço;

A sintaxe para a criação da credencial é:

CREATE DATABASE SCOPED CREDENTIAL DataLakeCredential
   WITH 
        IDENTITY = 'identity_name',
        SECRET = 'secret'

Nota: Ao criar uma tabela externa lembre-se que você não fica restrito em usar um Data Lake para sua fonte de dados externa, ele é apenas uma das opções.

Depois de criarmos nossa credencial, estamos prontos para o item dois.
Ao criar nossa fonte de dados, é importante observar se estamos alocando o caminho raíz dos dados.

CREATE EXTERNAL DATA SOURCE DataLakeSource
 WITH (
        TYPE        =   HADOOP,
        LOCATION    =   N'abfss://...',
        CREDENTIAL  =   DataLakeCredential
    )

Por fim, o passo três: Especificar um formato de arquivo para que nosso ASA entenda como ler os dados encontrados no Lake.
Aqui podemos definir várias opções sobre os arquivos que queremos ler.

Um ponto importante a ter em mente aqui é que o formato do arquivo não pode ser parametrizado, por isso é importante ler a partir de um canal padronizado em seu Data Lake para que você possa reduzir o número de formatos de arquivos necessários.

A sintaxe para criar o formato de arquivo é mostrado em:

CREATE EXTERNAL FILE FORMAT  DataLakeDelimitedTextFormat
 WITH (
        FORMAT_TYPE     =   DELIMITEDTEXT,
        FORMAT_OPTIONS  (
                FIELD_TERMINATOR    =   '|',
                DATE_FORMAT         =   'dd/MM/yyyy',
                DATE_FORMAT         =   '"'
        )
    )

Agora já estamos preparados para ingerir os dados em nosso Warehouse em forma de tabela externa;

Também podemos determinar o que acontece com as linhas que não se encaixam na definição de nossa tabela externa e onde elas devem ser alocadas.
O resource nos permite lidar facilmente com linhas incorretas, sejam elas causadas por violação de tipo de dados ou colunas.

CREATE EXTERNAL TABLE dbo.DataLakeExternalTable
 (
    COL1    TINYINT         NULL,
    COL2    VARCHAR(10)     NULL,
    COL3    NUMERIC(2,2)    NULL
)
WITH
(
    DATA_SOURCE             =   DataLakeSource,
    LOCATION                =   '/READ',
    FILE_FORMAT             =   DataLakeDelimitedTextFormat,
    REJECT_TYPE             =   VALUE,
    REJECT_VALUE            =   100,
    REJECTED_ROW_LOCATION   =   '/REJECTED'
)

O LOCATION “/READ” é uma subpasta da raiz definida na fonte de dados.

Além disso, especificamos que o PolyBase deve falhar na ingestão se 100 ou mais linhas forem inválidas, gravando as linhas inválidas no “/REJECTED” (+uma subpasta)

O argumento REJECT_TYPE pode ser definido como VALUE ou PERCENTAGE.
Seu valor de rejeição significa que o PolyBase falhará nas leituras desta tabela se o número Absoluto de linhas especificado no arquivamento REJECT_VALUE
for excedido.

Como alternativa, se o tipo for definido como porcentagem, a leitura falhará se a porcentagem de linhas definidas for inválida.
Você deve definir o REJECT_SAMPLE_VALUE que informa ao ASA quantas linhas tentar ler como um lote antes de passar para outro lote.

Se o tamanho do lote for definido por 1000 e o valor de rejeição for 10, o ASA lerá nas primeiras 1000 linhas e, se mais de 100 dessas linhas falharem, o lote será reprovado.

Para finalmente persistir esses dado no Warehouse, precisamos usar os dados em uma tabela interna; Uma tabela interna pode ser tratada exatamente da mesma forma que uma tabela normal; No entanto, é claro que os dados são distribuídos em 60 nós.

A forma correta de inserir dados é usando CTAS, ela permite criar uma tabela com saída de uma instrução SELECT.

CREATE TABLE dbo.DataLakeInternalTable
WITH   
(
    DISTRIBUTION    =   ROUND_ROBIN
)
AS
SELECT  COL1,COL2,COL3 
FROM    dbo.DataLakeExternalTable

De forma geral, esse é o método mais apropriado para ingerir dados em massa no warehouse.

Todos os outros métodos como SSIS,ADF e etc, enviam dados por meio de nó de controle, o que causa um gargalo.

Refêrencias:

1. Polybase Revealed: Data Virtualization with SQL Server, Hadoop, Apache Spark, and Beyond
2. Synapse PolyBase
3. Azure SQLDW-Polybase
4. Load confidently with SQL Data Warehouse PolyBase Rejected Row Location
5. https://github.com/franckepeixoto/Azure-Synapse-Analytics-Peixoto