Understanding the OPENROWSET (BULK..) function

The OPENROWSET(BULK..) function is used to access remote data from a data source (for example, connect to a file stored in Data Lake Gen 2). It can be directly referenced in the FROM clause, similar to calling a table name and pulling data from it as a set of rows.

OPENROWSET(BULK..) can read different types of file structures – PARQUET, DELTA, or delimited text (CSV), and access can be controlled with different login options – Azure AD logins or SQL logins (publicly available files can be accessed by just the web data path).

There is a slight difference in using the OPENROWSET(BULK..) syntax while reading Parquet/Delta files or a CSV file.

Let’s look at the syntaxes used for the OPENROWSET(BULK..) function.

This is OPENROWSET(BULK..) for reading Parquet or Delta files:

--OPENROWSET syntax for Parquet/Delta Lake files
OPENROWSET
( { BULK 'storage path to Parquet file' , [DATA_SOURCE = <data...