OPENROWSET and External Tables for Fabric SQL Databases (Preview)

[hudequei]

Data Virtualization (Preview) for Fabric SQL Databases, enable you to query, analyze, and ingest OneLake data (CSV, Parquet, JSON) without moving or duplicating it.

Data Virtualization brings to Fabric SQL Database the same set of capabilities already available on Azure SQL Database, Azure SQL Managed Instance and SQL Server, customers can now use OPENROWSET and External Tables, with complete parity across SQL flavors, develop once deploy anywhere. Data Virtualization for Fabric SQL Databases directly supports Parquet and delimited text (CSV), but JSON files can also be read using functions like JSON_VALUE and OPENJSON.

Data Virtualization connects to OneLake via EntraID for secure access with minimal configuration. Through Shortcuts, Fabric SQL Database Data Virtualization can access various supported data sources, including Azure Blob Storage, Azure Data Lake Gen2, S3-compatible storage, and SharePoint.

The major advantages of Data Virtualization for Fabric SQL Database are:

• Perform real-time analytics on external data without ingestion or duplication.
• Access both structured and semi-structured data through support for CSV, Parquet, and JSON, including using JSON functions for advanced scenarios.
• Combine OPENROWSET and External Tables with BULK operations for Reverse-ETL ingestion workflows.
• Configure External Tables so your application can access external data, as if it was a regular SQL table, with minimal code change.
• And because External Tables stores the schema information inside Fabric SQL Database, you can seamlessly use leverage Copilot to create quick insights without the need to load any data into SQL.

T-SQL Sample:

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

OPENROWSET_and_External_Tables_for_Fabric_SQL_Databases_Preview

Using OPENROWSET to query a parquet file.

The workspace ID and the Lakehouse ID can be retried through the browser URL or by navigating to the file or folder location and ABFSS path:

OPENROWSET_and_External_Tables_for_Fabric_SQL_Databases_Preview

Finding the ABFS path for folders or files in Lakehouse

You can also use CREATE EXTERNAL DATASOURCE to store this information for ease of use:

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

-- Parse the JSON data
SELECT *
FROM OPENROWSET(
    BULK 'JSON/sample_user_profile.json'
    ,DATA_SOURCE = 'Cold_Lake'
    ,SINGLE_CLOB
) AS JSONData
CROSS APPLY OPENJSON(JSONData.BulkColumn);

OPENROWSET_and_External_Tables_for_Fabric_SQL_Databases_Preview

Using JSON functions with OPENROWSET

Customers can also leverage External Tables to help abstract the location, the schema, and offer applications a similar experience as if it were a regular SQL table, with minimal code change.

T-SQL Sample:

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 [ext_product];

OPENROWSET_and_External_Tables_for_Fabric_SQL_Databases_Preview

Creating and using External Table

For more information about Data Virtualization for Fabric SQL Database, refer to the Data virtualization (preview) documentation.