Virtualização de dados (versão prévia)

Aplica-se a:✅banco de dados SQL do Microsoft Fabric

Importante

Esse recurso está na versão prévia.

A virtualização de dados no banco de dados SQL no Fabric permite consultar dados externos armazenados no OneLake usando T-SQL.

Com a sintaxe de virtualização de dados, você pode executar consultas Transact-SQL (T-SQL) em arquivos que armazenam dados em formatos de dados comuns no OneLake. Você pode combinar esses dados com dados relacionais armazenados localmente usando junções. Com a virtualização de dados, você pode acessar dados externos de forma transparente no modo somente leitura, mantendo-os em seu formato e local originais.

Sintaxe

O Banco de Dados SQL do Fabric dá suporte aos seguintes recursos de virtualização de dados:

• CRIAR UMA CREDENCIAL NO ESCOPO DO BANCO DE DADOS
• CRIAR FONTE DE DADOS EXTERNA
• CRIAR FORMATO DE ARQUIVO EXTERNO
• CRIAR TABELA EXTERNA
• OPENROWSET (BULK)
• SELECIONE EM .. A PARTIR DE OPENROWSET
• Funções de metadados: filename(), filepath(), sp_describe_first_result_set()

Authentication

A autenticação no Fabric Lakehouses usa a autenticação de passagem do Microsoft Entra ID.

Acessar arquivos do Fabric OneLake requer que a identidade do usuário tenha permissão tanto para o Lakehouse quanto para o local do arquivo.

Permissions

Os usuários devem ter acesso DE LEITURA ao arquivo ou à pasta no OneLake, garantido por meio da passagem da identidade do Microsoft Entra.

Tipos de arquivo com suporte

• Parquet
• CSV
• O formato de arquivo JSON tem suporte indiretamente especificando o formato de arquivo CSV em que as consultas retornam cada documento como uma linha separada. Você pode analisar linhas ainda mais usando JSON_VALUE e OPENJSON.

Fontes de dados com suporte

Atualmente, apenas o Fabric Lakehouse tem suporte nativo. No entanto, os atalhos do OneLake podem ser usados para estender para várias fontes externas, como Azure Blob Storage, Azure Data Lake Gen2, Dataverse, Amazon S3, Amazon S3 Compatível, Google Cloud Storage, HTTPS público e muito mais.

Para obter mais informações sobre atalhos do Fabric, consulte Unificar fontes de dados com atalhos do OneLake.

Como localizar o local do arquivo ABFSS de uma lakehouse

Para criar uma fonte de dados Fabric Lakehouse, você precisa fornecer o ID do workspace, o locatário e o ID da lakehouse. Para localizar o local do arquivo ABFSS de uma lakehouse:

1. Vá para o portal do Fabric.
2. Navegue até o Lakehouse.
3. Navegue até o local da pasta desejada.
4. Selecione ... e, em seguida, Propriedades.
5. Copie o caminho do ABFS, que é algo assim: abfss://<workspace ID>@<Tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/.

Limitações

• As tabelas externas CSV devem ser consultadas usando nomes qualificados por esquema, por exemplo, dbo.Customer_CSV.
• BULK INSERT atualmente só tem suporte quando usado em combinação com OPENROWSET (BULK).

Exemplos

Os scripts de exemplo a seguir usam um Fabric Lakehouse chamado Cold_Lake que hospeda dados da loja e dos clientes da Contoso em arquivos parquet e csv.

A. Consultar um arquivo Parquet com OPENROWSET

O exemplo a seguir demonstra o uso de OPENROWSET para recuperar dados de exemplo de um arquivo Parquet.

SELECT TOP 100 *  
FROM OPENROWSET(  
    BULK 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/Contoso/customer.parquet',  
    FORMAT = 'parquet'  
) AS customer_dataset;  

B. Consultar um arquivo CSV com OPENROWSET

O exemplo a seguir demonstra o uso de OPENROWSET para recuperar dados de exemplo de um arquivo CSV.

SELECT *  
FROM OPENROWSET(  
    BULK 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/Contoso/customer.csv',  
    FORMAT = 'CSV',  
    FIRST_ROW = 2  
) WITH (  
    CustomerKey INT,  
    GeoAreaKey INT,  
    StartDT DATETIME2,  
    EndDT DATETIME2,  
    Continent NVARCHAR(50),  
    Gender NVARCHAR(10),  
    Title NVARCHAR(10),  
    GivenName NVARCHAR(100),  
    MiddleInitial VARCHAR(2),  
    Surname NVARCHAR(100),  
    StreetAddress NVARCHAR(200),  
    City NVARCHAR(100),  
    State NVARCHAR(100),  
    StateFull NVARCHAR(100),  
    ZipCode NVARCHAR(20),  
    Country_Region NCHAR(2),  
    Country_Region_Full NVARCHAR(100),  
    Birthday DATETIME2,  
    Age INT,  
    Occupation NVARCHAR(100),  
    Company NVARCHAR(100),  
    Vehicle NVARCHAR(100),  
    Latitude DECIMAL(10,6),  
    Longitude DECIMAL(10,6)  
) AS customer_dataset; 

C. Criar fonte de dados externa

O exemplo a seguir mostra como criar uma fonte de dados externa para simplificar tabelas externas e comandos como OPENROWSET:

CREATE EXTERNAL DATA SOURCE [Cold_Lake] 
WITH ( 
LOCATION = 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/'); 

Com uma fonte de dados externa criada, você pode simplificar OPENROWSET, por exemplo:

-- USING DATA SOURCE WITH OPENROWSET 
SELECT TOP 100 * FROM OPENROWSET 
(BULK '/customer.parquet' 
, FORMAT = 'parquet' 
, DATA_SOURCE = 'Cold_Lake' ) 
 AS Customer_dataset; 

-- USING DATA SOURCE WITH OPENROWSET 
SELECT TOP 100 *  
FROM OPENROWSET(  
    BULK '/customer.csv',  
    FORMAT = 'CSV',  
    DATA_SOURCE = 'Cold_Lake', 
    FIRST_ROW = 2  
) WITH (  
    CustomerKey INT,  
    GeoAreaKey INT,  
    StartDT DATETIME2,  
    EndDT DATETIME2,  
    Continent NVARCHAR(50),  
    Gender NVARCHAR(10),  
    Title NVARCHAR(10),  
    GivenName NVARCHAR(100),  
    MiddleInitial VARCHAR(2),  
    Surname NVARCHAR(100),  
    StreetAddress NVARCHAR(200),  
    City NVARCHAR(100),  
    State NVARCHAR(100),  
    StateFull NVARCHAR(100),  
    ZipCode NVARCHAR(20),  
    Country_Region NCHAR(2),  
    Country_Region_Full NVARCHAR(100),  
    Birthday DATETIME2,  
    Age INT,  
    Occupation NVARCHAR(100),  
    Company NVARCHAR(100),  
    Vehicle NVARCHAR(100),  
    Latitude DECIMAL(10,6),  
    Longitude DECIMAL(10,6)  
) AS customer_dataset; 

D. Criar tabela externa para parquet

O exemplo a seguir demonstra como configurar um formato de arquivo externo e, em seguida, criar uma tabela externa especificamente para dados parquet.

CREATE EXTERNAL FILE FORMAT Parquetff WITH (FORMAT_TYPE=PARQUET); 

CREATE EXTERNAL TABLE [ext_product]( 
    [ProductKey] [int] NULL, 
    [ProductCode] [nvarchar](255) NULL, 
    [ProductName] [nvarchar](500) NULL, 
    [Manufacturer] [nvarchar](50) NULL, 
    [Brand] [nvarchar](50) NULL, 
    [Color] [nvarchar](20) NULL, 
    [WeightUnit] [nvarchar](20) NULL, 
    [Weight] DECIMAL(20, 5) NULL, 
    [Cost] DECIMAL(20, 5) NULL, 
    [Price] DECIMAL(20, 5) NULL, 
    [CategoryKey] [int] NULL, 
    [CategoryName] [nvarchar](30) NULL, 
    [SubCategoryKey] [int] NULL, 
    [SubCategoryName] [nvarchar](50) NULL) 
WITH 
(LOCATION = '/product.parquet' 
,DATA_SOURCE = [Cold_Lake] 
,FILE_FORMAT = Parquetff); 
 
SELECT * FROM [dbo].[ext_product] 

E. Criar tabela externa para CSV

O exemplo a seguir demonstra como configurar um formato de arquivo externo e criar uma tabela externa especificamente para dados CSV.

CREATE EXTERNAL FILE FORMAT [CSVFileFormat]  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,  
    FORMAT_OPTIONS (  
        FIELD_TERMINATOR = ',',  
        FIRST_ROW = 2  
    )  
); 
 
CREATE EXTERNAL TABLE ext_customer_csv ( 
    CustomerKey INT NOT NULL,  
    GeoAreaKey INT NOT NULL,  
    StartDT DATETIME2 NOT NULL, 
    EndDT DATETIME2 NOT NULL, 
    Continent VARCHAR(50) NOT NULL, 
    Gender VARCHAR(10) NOT NULL, 
    Title VARCHAR(10) NOT NULL,  
    GivenName VARCHAR(100) NOT NULL,  
    MiddleInitial VARCHAR(2) NOT NULL,  
    Surname VARCHAR(100) NOT NULL, 
    StreetAddress VARCHAR(200) NOT NULL, 
    City VARCHAR(100) NOT NULL, 
    State VARCHAR(100) NOT NULL, 
    StateFull VARCHAR(100) NOT NULL, 
    ZipCode VARCHAR(20) NOT NULL,  
    Country_Region CHAR(2) NOT NULL 
    ) 
WITH (  
LOCATION = '/customer.csv' 
, DATA_SOURCE = Cold_Lake 
, FILE_FORMAT = CSVFileFormat 
); 

SELECT * FROM [dbo].[ext_customer_csv]; 

F. Ingerir dados usando OPENROWSET

O exemplo a seguir mostra como OPENROWSET pode ser usado para ingerir dados em uma nova tabela:

SELECT * 
INTO tb_store 
FROM OPENROWSET 
(BULK 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/Contoso/store.parquet' 
, FORMAT = 'parquet' )
 AS STORE;

Para uma tabela existente, INSERT INTO pode ser usado para preencher a tabela de OPENROWSET:

INSERT INTO tb_store  
SELECT TOP 100 * FROM OPENROWSET 
(BULK ' abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/contoso/store.parquet' 
, FORMAT = 'parquet' ) 
 AS STORE; 

G. Usar as funções de metadados – sp_describe_first_result_set

A função sp_describe_first_result_set pode ser usada em combinação com OPENROWSET (BULK) para estimar o esquema de arquivo externo. Você pode identificar o esquema para as instruções CREATE TABLE ou CREATE EXTERNAL TABLE e explorar dados adicionais.

A sp_describe_first_result_set função usa um exemplo dos dados para estimar o esquema. Se o exemplo não for representativo, ele poderá fornecer resultados imprecisos. Se o esquema já for conhecido, especifique-o por meio da WITH cláusula.

EXEC sp_describe_first_result_set N'  
   SELECT * FROM OPENROWSET(  
      BULK ''abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/Contoso/store.parquet'',  
      FORMAT = ''parquet''  
   ) AS DATA'; 

Para obter mais informações, consulte sp_describe_first_result_set().

H. Usar funções de metadados – filename() e filepath()

O Banco de Dados SQL do Fabric também disponibiliza filename() e filepath() funções para exploração de pastas e arquivos, bem como criação de consultas dinâmicas, que também podem ser usadas para colunas virtuais em combinação com OPENROWSET para arquivos de dados distribuídos em várias subpastas.

O exemplo a seguir lista todos os arquivos parquet e suas localizações.

SELECT 
  r.filename() as file_name
, r.filepath() as full_path 
FROM OPENROWSET
   (BULK 'abfss://<workspace ID>@<tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/*/*.parquet',  
    FORMAT = 'parquet'  
   ) AS r 
GROUP BY r.filename(), r.filepath() 
ORDER BY file_name;  

Para obter mais informações, consulte filename() e filepath().

Conteúdo relacionado

• Unificar fontes de dados com atalhos do OneLake
• OPENROWSET (BULK)