Virtualização de dados (pré-visualização)

Aplica-se a:✅Base de dados SQL em Microsoft Fabric

Importante

Este recurso está em pré-visualização.

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

Com a sintaxe de virtualização de dados, pode executar consultas Transact-SQL (T-SQL) em ficheiros 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

A Fabric SQL Database suporta as seguintes capacidades de virtualização de dados:

• CRIAR CREDENCIAL COM ÂMBITO DE BASE DE DADOS
• CRIAR FONTE DE DADOS EXTERNA
• CRIAR FORMATO DE FICHEIRO EXTERNO
• CRIAR TABELA EXTERNA
• OPENROWSET (BULK)
• SELECIONA PARA .. FROM OPENROWSET
• Funções de metadados: nome do ficheiro(), caminho do ficheiro(), sp_describe_first_result_set()

Authentication

A autenticação para Fabric Lakehouses utiliza autenticação passthrough do Microsoft Entra ID.

Aceder a ficheiros a partir do Fabric OneLake requer que a identidade do utilizador tenha permissão tanto para o Lakehouse como para a localização do ficheiro.

Permissions

Os utilizadores devem ter acesso de LEITURA ao ficheiro ou pasta no OneLake, imposto através do Microsoft Entra ID passthrough.

Tipos de ficheiro suportados

• Parquet
• CSV
• O formato de ficheiro JSON é indiretamente suportado pela especificação do formato CSV, onde as consultas retornam cada documento como uma linha separada. Podes analisar linhas mais detalhadamente usando JSON_VALUE e OPENJSON.

Fontes de dados suportadas

Atualmente, apenas o Fabric Lakehouse é suportado nativamente. No entanto, os atalhos OneLake podem ser usados para se estender a várias fontes externas como Azure Blob Storage, Azure Data Lake Gen2, Dataverse, Amazon S3, Amazon S3 Compatible, Google Cloud Storage, HTTPS público e mais.

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

Como encontrar a localização do ficheiro ABFSS de uma casa de lago

Para criar uma fonte de dados Fabric Lakehouse, precisa de fornecer o ID do espaço de trabalho, o locatário, e o ID do lakehouse. Para encontrar a localização do ficheiro ABFSS de uma casa de lago:

1. Aceda ao portal Fabric.
2. Navegue até à sua casa do lago.
3. Navega até à localização da pasta desejada.
4. Selecione ..., depois Propriedades.
5. Copie o caminho ABFS, que se assemelha a isto: abfss://<workspace ID>@<Tenant>.dfs.fabric.microsoft.com/<lakehouse ID>/Files/.

Limitações

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

Examples

Os seguintes scripts de exemplo usam um Fabric Lakehouse chamado Cold_Lake que aloja dados de loja e clientes da Contoso em ficheiros de parquet e csv.

A. Consultar um ficheiro de parquet com OPENROWSET

O exemplo seguinte demonstra a utilização do OPENROWSET para recuperar dados de amostra de um ficheiro 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 ficheiro CSV com OPENROWSET

O exemplo seguinte demonstra a utilização de OPENROWSET para recuperar dados de amostra de um ficheiro 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 seguinte mostra como criar uma fonte de dados externa para simplificar tabelas e comandos externos 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, 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. Crie uma tabela externa para parquet

O exemplo seguinte demonstra como configurar um formato de ficheiro externo e depois criar uma tabela externa especificamente para dados de 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 seguinte demonstra como configurar um formato de ficheiro 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. Ingestão de dados usando OPENROWSET

O exemplo seguinte mostra como OPENROWSET pode ser usado para ingerir dados numa 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 a partir 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 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 externo do ficheiro. Pode identificar o esquema para as instruções CREATE TABLE ou CREATE EXTERNAL TABLE destinadas a uma exploração posterior dos dados.

A sp_describe_first_result_set função utiliza uma amostra dos dados para estimar o esquema. Se a amostra não for representativa, pode fornecer resultados imprecisos. Se o esquema já for conhecido, especifique-o através 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 mais informações, consulte sp_describe_first_result_set().

H. Use funções de metadados - nome do ficheiro() e caminho do ficheiro()

A base de dados Fabric SQL também disponibiliza filename() e filepath() funções para exploração de diretórios e arquivos, e criação dinâmica de consultas, que também podem ser usadas para colunas virtuais combinadas com o OPENROWSET para arquivos de dados em múltiplas subpastas.

O exemplo seguinte lista todos os ficheiros de parquet e as 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 mais informações, veja filename() e filepath().

Conteúdo relacionado

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