Virtualizar ficheiro CSV com PolyBase

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

O SQL Server 2022 (16.x) pode consultar dados diretamente a partir de ficheiros CSV. Este conceito, vulgarmente referido como virtualização de dados, permite que os dados permaneçam na sua localização original, mas pode ser consultado a partir de uma instância SQL Server com comandos T-SQL, tal como qualquer outra tabela. Esta funcionalidade utiliza conectores PolyBase e minimiza a necessidade de copiar dados através de processos ETL.

No exemplo seguinte, o ficheiro CSV é armazenado no Azure Blob Storage e acedido via OPENROWSET ou uma tabela externa.

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

Pré-configuração

1. Ativar o PolyBase em `sp_configure`

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

RECONFIGURE;

2. Criar uma base de dados de utilizadores

Este exercício cria uma base de dados de amostra com configurações e localização padrão. Utiliza-se esta base de dados de amostras vazia para trabalhar com os dados e armazenar a credencial com âmbito definido. Neste exemplo, é usada uma nova base de dados vazia nomeada CSV_Demo .

CREATE DATABASE [CSV_Demo];

3. Criar uma chave mestra e uma credencial ajustada ao âmbito da base de dados

A chave mestra na base de dados do utilizador é necessária para encriptar o segredo das credenciais com abrangência da base 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 fonte de dados externa

Uma credencial com âmbito de base de dados é usada para a fonte de dados externa. Neste exemplo, o ficheiro CSV reside no Azure Blob Storage, por isso use o prefixo abs e o SHARED ACCESS SIGNATURE método identidade. Para mais informações sobre os conectores e prefixos, incluindo novas definições para o SQL Server 2022 (16.x), consulte CRIAR FONTE DE DADOS EXTERNA.

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 contentor for nomeado import, o código seria:

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

Use o OPENROWSET para aceder aos dados

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

Dado que a fonte de dados externa Blob_CSV está mapeada ao nível do contentor. O call_center.csv está localizado numa subpasta chamada 2022 na raiz do contentor. Para consultar um ficheiro numa estrutura de pastas, forneça um mapeamento de pastas 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

O 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 as tabelas externas exijam mais esforço para serem criadas, também oferecem benefícios adicionais em relação à consulta a uma fonte de dados externa com o OPENROWSET. É possível:

Reforçar a definição da tipagem de dados para uma dada coluna
Defina nulidade
Defina COLLATION
Crie estatísticas para uma coluna para otimizar a qualidade do plano de consulta
Crie um modelo mais granular dentro do SQL Server para acesso a dados e melhore o seu modelo de segurança

Para mais informações, consulte CRIAR TABELA EXTERNA.

Para o exemplo seguinte, utiliza-se a mesma fonte de dados.

1. Criar formato de ficheiro externo

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

No exemplo seguinte, 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 tabela externa

LOCALIZAÇÃO é o caminho da pasta e do caminho do ficheiro call_center.csv relativamente ao caminho de localização na fonte de dados externa, definido por DATA_SOURCE. Neste caso, o ficheiro encontra-se numa subpasta chamada 2022. Use FILE_FORMAT para especificar o caminho até ao csv_ff formato de ficheiro externo 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

Comentários

Esta página foi útil?

Last updated on 2026-03-19