Parquet

Parquet#

Querona reads Apache Parquet files — .parquet and .parq — from a File connection. Parquet files are self-describing: each file carries its own columns and types, so every file maps to its own virtual table with an exact schema taken from the file — nothing is detected or guessed, and there are no parser settings to configure.

SELECT * FROM sales_parquet;

You can also read Parquet files ad-hoc — without importing metadata — with OPENROWSET (BULK …) and FORMAT = 'PARQUET'.

Folder and schema mapping#

Like the other per-file formats, the connection’s folder layout maps to schemas:

  • A file in the connection’s root folder maps to a table in the dbo schema.

  • Each direct subfolder of the root becomes a schema, and its files map to tables in that schema.

For example, a connection rooted at c:\data with sales.parquet in the root and 2024\orders.parquet in a subfolder exposes sales_parquet in dbo and orders_parquet in the 2024 schema.

To have Parquet files picked up during import, add .parquet (and .parq if you use it) to the connection’s list of accepted file extensions.

Key features#

  • exact, self-describing schema — read straight from the file; no schema detection and no parser settings

  • only the columns a query selects are read, and a TOP row limit is pushed into the reader, so large files stay fast

  • both read paths: import as virtual tables, or read ad-hoc with OPENROWSET (BULK ...)

  • several files read as one rowset — list them or match them with a glob

  • local files (files on the Querona server or a reachable network share)

Data types#

Each Parquet column maps to the closest SQL type. Strings and variable-length binary are unbounded (nvarchar(max) and varbinary(max)), so no value is shortened and no character is lost to a code page. Values that exceed what a SQL scalar type can hold are returned as exact text rather than being rounded or truncated, and nested columns are returned as JSON.

Parquet type

SQL type

Notes

BOOLEAN

bit

8- / 16- / 32- / 64-bit signed integer

smallint / smallint / int / bigint

8-bit is signed, so it widens to smallint

unsigned integer

tinyint / int / bigint / decimal(20,0)

widened one step so the full unsigned range fits

FLOAT / DOUBLE

real / float

DECIMAL(p, s)

decimal(p, s)

precision beyond the engine’s maximum is returned as exact text

STRING / ENUM

nvarchar(max)

DATE

date

TIME

time(7)

a time adjusted to UTC is returned as the UTC time of day

TIMESTAMP

datetime2(7)

whatever the file’s time unit; a timestamp adjusted to UTC is returned as its UTC date and time, with no offset and no conversion to the server’s time zone. A nanosecond timestamp is truncated to 100 ns; declared bigint or a character type in WITH (...), it is read as its count of nanoseconds since 1970-01-01

legacy INT96 timestamp

datetime2(7)

read to the microsecond: a seventh fractional digit is not returned

UUID

uniqueidentifier

FIXED_LEN_BYTE_ARRAY(n) (binary)

binary(n)

varbinary(max) when n is above 8000, or when the files of a multi-file read store it with different lengths

BYTE_ARRAY (binary)

varbinary(max)

LIST / MAP / STRUCT

nvarchar(max)

the nested value as a JSON document — shred it with OPENJSON / JSON_VALUE

A column whose values fall outside these ranges — a very large integer, or an interval — is returned as its exact text so nothing is lost.

Note

Earlier versions returned a timestamp adjusted to UTC as datetimeoffset, a time adjusted to UTC as text, and fixed-length binary as varbinary(max). A virtual table imported by an earlier version keeps those types and reads as before; re-import its metadata to switch it to the types above.

Reading Parquet files#

Both ways work on the same File connection:

-- 1) the imported virtual table
SELECT id, customer, amount FROM sales_parquet;

-- 2) ad-hoc, one or more files
SELECT id, customer, amount
  FROM OPENROWSET(BULK 'sales.parquet', DATA_SOURCE = 'files', FORMAT = 'PARQUET') AS p;

DATA_SOURCE names the File connection; a file reference is resolved under its directory, and a reference that escapes it is rejected. Because a Parquet file carries its own schema, the WITH (...) clause is optional — omit it to return every column, or add it to project and order a subset by name:

SELECT amount, id
  FROM OPENROWSET(BULK 'sales.parquet', DATA_SOURCE = 'files', FORMAT = 'PARQUET')
       WITH (amount decimal(18,2), id bigint) AS p;

The WITH clause selects the result columns and their order by name - matched exactly, case included - and each column is read as the type it declares. A column it names that the file does not contain is returned as all-NULL rather than an error. A declared type the file’s own type cannot be read as is refused with an error that names both and suggests a type that works — for example a float declared over a DECIMAL column. Nested-field projection with a '$.path' is not supported yet: declare top-level columns only.

Declaring the type is what recovers a value the Parquet schema cannot describe. A time and a datetimeoffset are written as a timestamp and as text, so without a declared type they come back as those:

SELECT c_time, c_when
  FROM OPENROWSET(BULK 'exported.parquet', DATA_SOURCE = 'files', FORMAT = 'PARQUET')
       WITH (c_time time(7), c_when datetimeoffset(7)) AS p;

A declared type that holds fewer digits than the value truncates it, as an external read does — declaring datetime2(3) over a value carrying seven fractional digits keeps three. A value too long or too large for the declared type is an error, not a silent change: a character or binary column too short to hold the value, a decimal whose integer digits do not fit, an integer out of range.

When the path ends in .parquet or .parq, FORMAT = 'PARQUET' is inferred and can be omitted. Read several files as one rowset by listing them or matching a glob — * within a folder, ** across subfolders:

-- an explicit list
SELECT id FROM OPENROWSET(BULK ('jan.parquet', 'feb.parquet'), DATA_SOURCE = 'files') AS p;

-- every Parquet file under 2024/ at any depth
SELECT id FROM OPENROWSET(BULK '2024/**/*.parquet', DATA_SOURCE = 'files') AS p;

The files matched by a multi-file read must share the same schema.

A folder named key=value is a folder, not data. A partitioned layout such as year=2024/month=01/ adds no year or month column to the result: the columns are the ones the files themselves carry, and a WITH column named after a folder key reads as all-NULL.

Note

A .parquet file that is a Delta Lake or Iceberg data part is read as a plain Parquet file. Querona has no awareness of the table log, so pending deletes, compaction, and time-travel snapshots are ignored and the file’s raw rows are returned as-is — which can include rows a delete has logically removed. Read such tables through their catalog, not as loose .parquet files.

See OPENROWSET for the full OPENROWSET reference.

See also#