Query and ingest JSONL files in Data Warehouse and SQL Analytics Endpoint for Lakehouse (Generally Available)
The OPENROWSET function that can read JSONL format empowers you to easily read and ingest JSONL files – for example log files, social media streams, machine learning datasets, configuration files, and other semi-structured sources. With the versatile OPENROWSET T-SQL function, you can reference and query JSONL files as if they were tables, eliminating the need for manual parsing or complex transformation steps.
Querying JSONL data
The OPENROWSET function allows you to directly read one or multiple JSONL files, representing each JSON object as a row in your query results.
SELECT * FROM OPENROWSET(BULK '/Files/samples/jsonl/farmers-protest-tweets-2021-2-4.jsonl' )
Each property within your JSON objects is automatically mapped to a separate column, simplifying data exploration and manipulation.
Additionally, the WITH clause in OPENROWSET function enables you to define a schema and extract properties at any level—even from nested or complex JSON structures—flattening content on the fly for easier analysis.
You can use either Fabric Query editor, T-SQL Notebook, or tools like SSMS to query JSONL files. In the following picture you can see a Fabric T-SQL Notebook where we are reading the nested JSON documents representing social media data stored in JSONL files:
These capabilities make it straightforward to work with deeply nested JSON data and unlock insights from diverse, semi-structured sources.
Flexible Data Ingestion Workflows
The OPENROWSET function is a powerful tool that also enables various data ingestion scenarios. You can use CREATE TABLE AS SELECT (CTAS) and INSERT SELECT statements to efficiently load JSONL data into your Fabric Data Warehouse tables.
This query loads content of JSONL file into a new Data Warehouse table where you can proceed with further analysis and transformations.
Once you create the table, you can inser additional data using INSERT-SELECT statement:
INSERT INTO OpenRowsetDW.dbo.Tweets SELECT * FROM OPENROWSET(BULK '/Files/jsonl/farmers-protest-2022-12-04.jsonl')
This streamlines the process of populating and refreshing datasets from JSONL sources, supporting both initial loads and ongoing pipeline automation.
Conclusion
JSONL support through OPENROWSET, Fabric Data Warehouse and SQL Analytics Endpoint for Lakehouse deliver a powerful tool for reading and ingesting JSON files at scale. Whether you are working with logs, configuration files, or streaming data, this integration simplifies workflows and accelerates analytics—helping you unlock the full potential of your semi-structured data.
