PDF

PDF#

Querona reads PDF documents from a File connection. Every .pdf file in the connection’s folders becomes a row in the built-in builtin.PdfDocuments table, exposing the document’s metadata together with its extracted text and the tables detected inside it (returned as a JSON array). You can also read PDFs ad-hoc — without importing metadata — with OPENROWSET (BULK …) and FORMAT = 'PDF'.

The PdfDocuments table#

Each PDF maps to one row. Alongside the metadata, two columns carry extracted content — Text and Tables — and both are produced only when selected: a query that reads neither runs neither extraction.

Column name	Column type	Description
Id	VarBinary(32) not null	Blake3 hash of the file content; stable row key
Path	NVarChar(4000) null	OS path to a file
CreatedOn	DateTime2 not null	Document creation date
LastModifiedOn	DateTime2 null	Document last modification date
Keywords	NVarChar(1600) null	Document keywords
Subject	NVarChar(300) null	Document subject
Title	NVarChar(300) null	Document title
IsEncrypted	Bit not null	Encryption flag (1=encrypted, 0=not encrypted)
NumberOfPages	Int not null	Number of pages in document
Author	NVarChar(255) null	Document author
Version	Float not null	PDF specification compatibility version
SizeBytes	BigInt not null	File size in bytes
EmbeddedFiles	Int not null	Document-embedded files count
Text	NVarChar(max) null	Extracted document text (see extraction options below)
Tables	NVarChar(max) null	Detected tables as a JSON array (see extraction options below)

Text and Tables are nvarchar(max) — Tables holds a JSON array, so shred it with OPENJSON / JSON_VALUE.

Reading PDFs#

Both ways work on the same File connection:

-- 1) the built-in table (honors the connection's default options)
SELECT Title, Author, NumberOfPages, Text FROM builtin.PdfDocuments;

-- 2) ad-hoc, one or more files, with per-query options
SELECT Path, Text, Tables
  FROM OPENROWSET(BULK '*.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF') AS docs;

DATA_SOURCE names the File connection; a file reference is resolved under its directory, and a reference that escapes it is rejected. See Files and data services for connection setup and folder-to-schema mapping.

Extraction options#

Extraction is steered by options that apply as connection-level defaults to every read (including builtin.PdfDocuments) and can be overridden per query on an OPENROWSET (BULK ...) read via a small TOML options table in the FORMATFILE_DATA_SOURCE parameter.

Set the connection-level defaults on the File connection’s edit form — the PDF extraction and PDF text layout sections — in the Querona portal; a query can then override any of them as shown below.

Table extraction#

engine — how tables are detected: auto (default), lattice, or stream.

lattice finds tables drawn with visible ruling lines (cell borders). Use it when the table has a printed grid: it keys off the lines, so it is precise for bordered tables and ignores borderless ones.
stream infers columns from whitespace alignment, for tables with no borders. Use it when columns are held apart only by spacing; it can mis-split dense text or a table that actually has rules.
auto tries lattice first and falls back to stream when it finds no ruled table. Use it when the layout is unknown or a batch mixes both styles. Its blind spot is a single page holding both a ruled and a borderless table — there, name the engine explicitly or isolate each table with area.

-- a borderless, whitespace-aligned report: force stream detection
SELECT Tables
  FROM OPENROWSET(BULK 'report.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { engine = "stream" }') AS docs;

area — restrict extraction to one rectangle, given as [top, left, bottom, right] in PDF points measured from the top-left corner and applied to every page in range. Use it when a page mixes a table with other content — headers, footers, side notes, or several tables — and you want only the region that holds your table: it cleans the result and stops the detector wandering into non-table text. Omit it to scan the whole page.

-- only the table in the lower half of each page
SELECT Tables
  FROM OPENROWSET(BULK 'invoice.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { engine = "lattice", area = [400, 40, 780, 560] }') AS docs;

Text extraction#

These shape the Text column only; they do not affect Tables.

text_mode — the unit the text is emitted in: blocks (default), words, or letters.

blocks groups words into reading-order paragraphs — the most readable output. Use it for prose you will read, search, or feed to language tooling.
words returns every word, space-separated, in extraction order, with no paragraph reconstruction. Use it when you only need tokens (keyword search, term counts), or when a busy layout makes blocks group text wrongly.
letters concatenates the raw glyph stream with no grouping. Use it as a last resort for unusual layouts where blocks and words both scramble the order, or when you reconstruct the text yourself.

segmenter — how the page is split into blocks; used only by text_mode = blocks: docstrum (default) or xycut.

docstrum groups characters by their spacing to nearest neighbours — robust on mixed or irregular layouts, so it is the general-purpose default.
xycut splits the page along its whitespace gaps — cleaner on regular multi-column or grid-like pages, but it can over- or under-split irregular ones. Reach for it when docstrum merges columns that should stay apart.

reading_order — how the blocks are ordered: unsupervised (default), rendering, or none.

unsupervised infers the natural reading order — the right choice for almost every document.
rendering keeps the order in which the PDF draws its content; some files draw in true reading order, others scramble it. Use it when unsupervised reorders a document you know is drawn top-to-bottom.
none leaves the blocks in detection order — pick it when you order the text yourself downstream.

dedup_letters — remove duplicated overlapping glyphs; true by default. Bold-by-double-printing and drop-shadow effects draw a character twice, slightly offset; with dedup on those extra copies are dropped for clean text. Turn it off only if a document legitimately overlaps distinct characters and you are losing real ones.

margin_left / margin_right / margin_top / margin_bottom — points trimmed from each page edge before extraction; any text whose box falls inside a trimmed band is dropped. Use them to strip fixed page furniture — running headers, footers, page numbers, side notes — that sits in a constant edge band, so it neither pollutes the Text nor leaks into stream tables.

-- words only, and drop a 36-point running header and footer band
SELECT Path, Text
  FROM OPENROWSET(BULK 'report.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { text_mode = "words", margin_top = 36, margin_bottom = 36 }') AS docs;

Page selection#

pages — a 1-based range such as "1-3,5" that restricts reading to the listed pages (for both Text and Tables). Use it to skip covers, contents pages, or appendices, or to target the one page you need; on large documents it also speeds the read by never touching pages you would discard.

-- only pages 2 through 4
SELECT Path, Text
  FROM OPENROWSET(BULK 'manual.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { pages = "2-4" }') AS docs;

Encrypted files#

password — the password that opens an encrypted PDF; required for protected files and ignored for open ones. Supply it per query. Querona does not store PDF passwords: a connection holds no password field, so the value lives only for the duration of the statement that carries it.

Note

The password is part of the SQL text, so treat the statement the way you would treat any credential-bearing query — Querona masks the value in its own statement logs, but whatever composes the query (a script, an application, a client’s query history) sees it in full.

SELECT Path, Text
  FROM OPENROWSET(BULK 'protected.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { password = "s3cret" }') AS docs;

Multi-file reads#

on_error — what a multi-file (glob) read does when a file cannot be opened: fail (default) or skip.

fail aborts the whole read on the first unreadable file. Use it when every file must be processed and a bad one is an error you want surfaced at once.
skip reads what it can and passes over the files it cannot open. Use it for bulk reads across a messy corpus, where a few unreadable or wrongly-encrypted files should not sink the batch.

-- read every PDF under 2024/, skipping any that cannot be opened
SELECT Path, Text
  FROM OPENROWSET(BULK '2024/**/*.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { on_error = "skip" }') AS docs;

Examples#

-- an encrypted document, every PDF under 2024/, skipping any that cannot be read
SELECT Path, Text
  FROM OPENROWSET(BULK '2024/**/*.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { password = "s3cret", on_error = "skip" }') AS docs;

-- shred the detected tables (Tables is a JSON array)
SELECT d.Path, t.[key] AS table_index, t.[value] AS one_table
  FROM OPENROWSET(BULK 'invoice.pdf', DATA_SOURCE = 'files', FORMAT = 'PDF') AS d
       CROSS APPLY OPENJSON(d.Tables) AS t;

PDF

Contents