Virtualizar arquivo CSV com o PolyBase

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores

O SQL Server 2022 (16.x) pode consultar dados diretamente de arquivos CSV. Este processo, comumente chamado de virtualização de dados, permite que os dados permaneçam em sua localização original, mas possam ser consultados de uma instância do SQL Server com comandos do T-SQL, como qualquer outra tabela. Este recurso usa conectores do PolyBase e minimiza a necessidade de copiar dados por meio de processos do ETL.

No exemplo a seguir, o arquivo CSV é armazenado no Armazenamento de Blobs do Azure e acessado por meio de OPENROWSET ou de uma tabela externa.

Para mais informações sobre a virtualização de dados, consulte Introdução à virtualização de dados com o PolyBase.

Pré-configuração

1. Habilitar o PolyBase em sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Criar um banco de dados de usuário

Este exercício cria um banco de dados de exemplo com configurações e localização padrão. Você vai usar esse banco de dados de exemplo vazio para trabalhar com os dados e armazenar a credencial no escopo. Neste exemplo, um novo banco de dados vazio chamado CSV_Demo é usado.

CREATE DATABASE [CSV_Demo];

3. Criar uma chave mestra e uma credencial com escopo de banco de dados

A chave mestra do banco de dados no banco de dados de usuário é necessária para criptografar o segredo da credencial com escopo de banco de dados, blob_storage.

USE [CSV_Demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL blob_storage
WITH IDENTITY = '<user_name>', Secret = '<password>';

4. Criar uma fonte de dados externa

Uma credencial com escopo de banco de dados é usada para a fonte de dados externa. Neste exemplo, o arquivo CSV reside no Armazenamento de Blobs do Azure, portanto, use o prefixo abs e o método de identidade SHARED ACCESS SIGNATURE. Para obter mais informações sobre os conectores e prefixos, incluindo novas configurações para o SQL Server 2022 (16.x), consulte CREATE EXTERNAL DATA SOURCE.

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);

Por exemplo, se a sua conta de armazenamento for nomeada s3sampledata e o contêiner for nomeado import, o código será:

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://import@s3sampledata.blob.core.windows.net'
,CREDENTIAL = blob_storage
)

Usar OPENROWSET para acessar os dados

Neste exemplo, o arquivo é chamado call_center.csv e os dados começam na segunda linha.

Uma vez que a fonte de dados externa Blob_CSV é mapeada para um nível de contêiner. O call_center.csv está localizado em uma subpasta chamada 2022 na raiz do contêiner. Para consultar um arquivo em uma estrutura de pastas, forneça um mapeamento de pasta relativo ao parâmetro LOCATION da fonte de dados externa.

SELECT * FROM OPENROWSET
(
    BULK '/2022/call_center.csv',
    FORMAT = 'CSV',
    DATA_SOURCE = 'Blob_CSV',
    FIRSTROW = 2
)
WITH (
    cc_call_center_sk INT,
    cc_call_center_id CHAR(16),
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(40),
    cc_division INT,
    cc_division_name VARCHAR(50),
    cc_company INT,
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(2),
    cc_zip CHAR(10),
    cc_country VARCHAR(20),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
) AS [cc];

Consultar dados com uma tabela externa

CREATE EXTERNAL TABLE também pode ser usado para virtualizar os dados CSV no SQL Server. As colunas devem ser definidas e fortemente tipadas. Embora a criação de tabelas externas exija mais esforço, elas também oferecem benefícios adicionais em relação à consulta de uma fonte de dados externa com OPENROWSET. Você poderá:

  • Fortalecer a definição da digitação de dados para uma determinada coluna
  • Definir nulidade
  • Definir COLLATION
  • Criar estatísticas para uma coluna para otimizar a qualidade do plano de consulta
  • Criar um modelo mais granular no SQL Server para acesso a dados para aprimorar o modelo de segurança

Para obter mais informações, consulte CREATE EXTERNAL TABLE.

Para o exemplo a seguir, a mesma fonte de dados é usada.

1. Criar um formato de arquivo externo

Para definir a formatação do arquivo, é necessário um formato de arquivo externo. Formatos de arquivo externos também são recomendados devido à reutilização.

No exemplo a seguir, os dados começam na segunda linha.

CREATE EXTERNAL FILE FORMAT csv_ff
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2)
);

2. Criar uma tabela externa

LOCATION é a pasta e o caminho do arquivo call_center.csv em relação ao caminho da localização na fonte de dados externa, definida por DATA_SOURCE. Nesse caso, o arquivo fica em uma subpasta chamada 2022. Use FILE_FORMAT para especificar o caminho para o formato de arquivo externo csv_ff no SQL Server.

CREATE EXTERNAL TABLE extCall_Center_csv (
    cc_call_center_sk INT NOT NULL,
    cc_call_center_id CHAR(16) NOT NULL,
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(MAX),
    cc_division VARCHAR(50),
    cc_division_name VARCHAR(50),
    cc_company VARCHAR(60),
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(20),
    cc_zip CHAR(20),
    cc_country VARCHAR(MAX),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
    )
WITH (
    LOCATION = '/2022/call_center.csv',
    DATA_SOURCE = Blob_CSV,
    FILE_FORMAT = csv_ff
);
GO

Conteúdo relacionado