SQL and functions

The public schema

Queries are read-only and use ClickHouse SQL with Periplus helpers. Use the short table names below or qualify them with public_v1. The SQL explorer and schema endpoint provide the current column names, types, and descriptions.

JSON uses the same captures/documents join and native ClickHouse JSON functions with ARRAY JOIN. See Query JSON documents. visible_text is normalized HTML prose and is empty for JSON.

Periplus helpers

Open the Functions tab in SQL workspace for signatures and examples. Join document structure using capture_id and node_index, not document_id alone.

Inspect one capture

capture_metrics(capture_id) takes one capture UUID string or positional parameter and returns a single row describing that retained capture. Use it before choosing how to explore a page.

SELECT format, parse_status, byte_length, node_count, text_nodes, link_occurrences
FROM capture_metrics('00000000-0000-4000-8000-000000000001');

NULL means unavailable or not applicable, and 0 means counted with none found. JSON documents have NULL visible-text and link counts. An HTML page without HTTP links reports 0. Malformed JSON or XML still returns a row, with parse_status = 'error', the parse_error code and counts for the tree that was published.

parse_status = 'ok' means only that the captured bytes were interpreted. It does not show that the page rendered completely or that dynamic content was captured.

The helper returns zero rows when the capture is unknown, expired, not yet published, failed or has no body. The captures table holds only complete document observations; failed captures keep their capture_id and appear on capture requests and in crawl and schedule capture lists, and URLs not yet attempted are only counted in a crawl's progress. Zero rows from this helper do not mean the capture is absent from the web.

The result has at most one row, so ordering and continuation do not apply. Each call reads this capture's captures, documents, nodes and links rows through capture-keyed indexes. It also reads other rows stored in the same index blocks, but it does not scan unrelated captures. Use at most four calls per statement. All counts and node positions refer to the same publication as the rest of the query. A parser-changing rebuild can change them.

Read capture content

capture_content(capture_id) reads one published HTML capture's stored tree and returns readable text in source order. page_content(page_id) does the same for a page's latest published complete HTML capture. Both take the identity first, followed by named options.

SELECT segment_index, tag, text, text_clipped, cursor
FROM capture_content(?, limit = 50)
ORDER BY segment_index;

page_id is the 64-character lowercase canonical page identity; page_content accepts no URL. Look the identity up with SQL, then read the page's latest capture:

SELECT page_id FROM pages WHERE url = 'https://example.com/pricing';

SELECT capture_id, observed_at, heading_level, text
FROM page_content(?, mode = 'outline')
ORDER BY segment_index;

Columns. Every row has capture_id, document_id, page_id, url and observed_at for the capture that was read, then:

Segmentation. With mode = 'text' (the default), each text node belongs to its nearest enclosing HTML block element, such as p, li, td/th, div, section, pre or h1–h6. A segment is one owner's continuous run of text in source order. Segments never overlap: a block's text excludes the text of blocks nested inside it, and any block boundary ends a run. A container whose text is interrupted by a nested block therefore yields several segments with the same node_index. Inline elements such as a, strong, span and label stay inside their block, so labels stay with their values. br adds a space, and blank segments are omitted. Lists, table cells and bare container text are included. This is a structural rule, not article detection or relevance ranking.

Outlines. mode = 'outline' returns one row per h1–h6 element in source order, including empty headings. A heading's text excludes any headings nested inside it. Text on either side of a nested block, nested heading or br is separated by a space, and inline elements stay joined. role="heading", styling and inferred topics are not treated as headings.

Visibility. visibility = 'visible_text' (the default) excludes the same content as documents.visible_text: head, script, style, template and nav subtrees, comments, role="navigation", hidden and aria-hidden="true". visibility = 'all' keeps every text node. Neither setting evaluates CSS, scripts or layout.

Original evidence. node_text and node_html(capture_id, node_index) return the owning element's original text and HTML, including nested blocks. Node indexes are positions within this capture's current publication. They are not selectors for other pages and can change after a reparse.

Continuation. limit (1–1000, default 200) bounds the rows returned after the cursor. max_chars ranges from 1 to 20000 (default 2000), and limit × max_chars must not exceed 2,000,000. Pass a row's cursor with the same mode and visibility to continue:

SELECT segment_index, text, cursor
FROM capture_content(?, limit = 50, cursor = ?)
ORDER BY segment_index;

A cursor names its capture. page_content(page_id, cursor = ...) keeps reading that capture even after a newer one is published, and returns an error if the cursor's capture belongs to another page. If the current publication no longer matches the cursor's position and text, the call returns an error rather than shifting to different content.

Latest capture. page_content picks the page's published complete HTML capture with the newest observed_at, treating undated captures as oldest. A tie is broken by capture_id descending, the same order search uses. JSON and XML captures are not eligible. Each row reports the capture_id and observed_at that were chosen.

Missing and empty input. An unknown, expired, unpublished or non-HTML capture, or a page with no eligible capture, returns an error. Zero rows therefore means a published HTML capture with no text under the chosen mode and visibility. A successful parse does not prove the rendered page was complete.

Named modes. capture_text(capture_id, ...) and capture_outline(capture_id, ...) are capture_content with mode = 'text' and mode = 'outline'. They return the same columns, take the same options except mode, and their cursors are interchangeable with capture_content cursors in that mode. capture_content and its mode option keep working unchanged.

SELECT heading_level, text, node_index
FROM capture_outline(?)
ORDER BY segment_index

Pass the capture ID as a UUID string literal or a ? parameter, not an expression such as toUUID(...).

Processing bounds. A query may make at most two capture_content/capture_text/capture_outline/page_content calls. Each capture is limited to 250,000 nodes and a 48 MiB node read. Exceeding a bound returns helper_limit rather than partial rows. Request deadlines and read budgets still apply. Preparing a query validates arguments without reading content. Results are computed for each request and are not stored.

Search inside one capture

search_capture and search_capture_matches search the text of one capture you already have. A passage is a run of text with no block boundary inside it: inline elements such as a, b, span and code do not split a passage, while div, p, li, td, headings, custom and XML elements do. Text that visible_text excludes (head, script, style, template, nav, navigation roles, hidden and aria-hidden="true") is not searched. Passages split the capture's visible_text without overlap, so a container never repeats its children's text and counts add up.

SELECT rank, score, matched_terms, snippet, node_index
FROM search_capture(?, 'battery recycling')
ORDER BY rank

The query is a string of at most 2,048 characters with at most 32 distinct non-stopword terms; longer queries are rejected. Matching uses the search tokenizer and stopwords: a passage matches when it contains any query term, with no stemming or semantic ranking; phrase = true also requires the whole query as a case-insensitive substring.

The capture never switches to a newer observation. For exhaustive paging, use search_capture_matches with WHERE passage_index > ? ORDER BY passage_index over the same capture, query and options. Ranked search_capture returns only its top matches and is not an exhaustive paging source. Passage positions belong to that capture in the result's source_snapshot and are not cross-page selectors. Unknown, unpublished or expired captures, JSON documents and XML that could not be parsed return an error rather than zero rows. Captures over 1,000,000 nodes return helper_limit. Empty or stopword-only queries return no rows. At most four calls per query.

search_corpus is the explicit name for search across the corpus; search keeps working unchanged.

Node context

node_context(capture_id, node_index) returns the structural neighbourhood of a node position in one capture. node_index is one position or an array of 1–32 positions. The helper reports tree relationships only; it does not decide which neighbour is relevant, group records, or describe rendered layout.

SELECT relationship, distance, node_index, kind, name, text, text_characters, text_clipped
FROM node_context('00000000-0000-4000-8000-000000000001', 377)
ORDER BY target_index, node_index

SELECT target_index, relationship, distance, node_index, name, text, attributes
FROM node_context('00000000-0000-4000-8000-000000000001', [377, 756],
                  ancestors = 2, siblings = 2, children = 4, elements_only = true)
ORDER BY target_index, node_index

Options are named and optional:

By default, sibling and child steps count every node, including whitespace-only text and comments, so nothing is silently dropped. sibling_index always reports that all-node position. Arguments must be literals or positional parameters (an array parameter for several positions); column arguments, LATERAL and more than four calls per query are rejected. To inspect selector matches, select their node_index values first, then pass them as an array.

Columns:

Ordering: ORDER BY target_index, node_index gives document order within each target (ancestors, preceding siblings, self, children, following siblings). Results have no guaranteed order without ORDER BY.

Repeated text: an ancestor's text contains the target's text, so do not count it as independent evidence. Equal text_hash values are a fingerprint that suggests possible duplication (for example a parent whose only text is its preceding text child); compare the text itself before treating rows as identical.

Status: a position absent from an available capture returns one missing_node row; an unknown, unpublished or expired capture returns unavailable_capture rows. Neither is reported as an empty neighbourhood. HTML and XML expose their parsed trees; template contents appear under a document_fragment node, and SVG/MathML names and namespaces are preserved. JSON documents have only a document root and one text child. A parsed tree is the static captured HTML, not proof of complete rendered content. Node positions belong to one capture and the current publication; they are not stable selectors across pages or reparses.

Cost and bounds: each call reads the selected capture's node structure once and reads text and attributes only for returned rows; computing lengths, previews and fingerprints over full text costs proportionally to those rows' text. Work grows with that capture's size times the number of positions, not with unrelated captures. A call returns at most 1 + ancestors + 2 × siblings + children rows per position. LIMIT does not reduce the structural read; normal result limits apply. Use node_text, node_html or the nodes table for complete originals.

Capture links

capture_links(capture_id) lists the link evidence of one retained capture. It reads only that capture; it never fetches, filters or ranks destinations.

SELECT node_index, href, enrichment, target_url, anchor_text,
       context_before, context_after, heading_text, next_after
FROM capture_links(?, limit = 50, context_characters = 120)
ORDER BY node_index

• Input. One capture UUID literal or parameter. Optional named integers: after (continue after this node_index), limit (1–1000, default 100) and context_characters (0–1000, default 200). At most two calls per query.
• Rows. Every HTML a and area element, with or without href, including template content (in_template = true), plus published sitemap loc references. Use ORDER BY node_index for document order. link_ordinal and total_links count all rows for the capture before after/limit. node_index and parent_index are positions within this capture and publication, not stable selectors.
• href and URLs. href is the original attribute value, unresolved and unclipped. has_href = false with href NULL means the attribute is missing; href = '' is an empty attribute. href_scheme is the lexical scheme prefix (mailto, javascript, https, ...), NULL for relative, fragment-only and empty references. resolved_url, target_url, fragment and target_page_id come only from published HTTP(S) link evidence (enrichment = 'published'). Missing, non-HTTP(S), credential-bearing, unparsable and template references are 'not_published' with NULL URL fields; base_href shows the first HTML base href used for resolution.
• Text and context. anchor_text is the link's text; anchor_text_blank is true for empty or whitespace-only text, and image_count/image_alt describe owned img descendants (those whose innermost enclosing anchor is this link) (alt NULL when absent, '' for alt=""). Image-only links therefore keep their image evidence. context_before and context_after are the parent element's text immediately around the link text and never repeat it. The heading is the nearest h1–h6 outside template content that starts before the link in document order: it may contain the link (heading_contains_link) or belong to an earlier section. This is a positional rule, not a relevance judgement.
• Lengths and clipping. Text fields return at most context_characters Unicode characters. anchor_text_characters, parent_text_characters and heading_text_characters are full lengths; the matching *_clipped flags mark shortened values.
• Duplicates. Nothing is deduplicated. href_occurrence/href_occurrences and text_occurrence/text_occurrences number identical hrefs and identical full link texts (including blank text) in document order.
• Continuation. Pass the previous page's next_after as after with the same capture and options; next_after is NULL on the last page. Positions belong to one publication, so compare source_snapshot and document_id between pages.
• Unavailable input. An unknown, unpublished, bodyless or expired capture, a JSON document, or a document whose interpretation failed (for example invalid XML) is an error, never zero rows. HTML replacement-decoding with an available published tree is accepted; that does not establish complete rendering. Zero rows means an accepted published HTML/XML tree with no link elements. Preparation does not check the capture.
• Limits. Each call reads the capture's whole tree, text and link evidence regardless of limit, but no other capture. Nested links repeat their inner text; if the full text of all link elements together exceeds 8,000,000 characters, the query fails with helper_limit instead of returning partial rows. Results describe the stored static HTML/XML tree, not rendered layout, visibility, scripts or link destinations.

Selector candidates

selector_candidates(capture_id, node_indexes) takes one published HTML capture and 1–16 distinct element node indexes from it, for example from query_selector_all or nodes. Node indexes are positions within that capture's current publication, not durable references: a reparse or rebuild can renumber them, so resolve current positions before calling and keep the publication consistent. A session alone does not pin it. It builds CSS selectors from the elements' names, IDs, classes, attributes, parent, nearest ancestor with an ID and :nth-of-type paths. Then it runs every candidate with query_selector_all on that same capture and reports what it matched.

SELECT candidate_index, selector, strategy, match_count, extra_count,
       missing_node_indexes, extra_node_indexes, exact_on_capture
FROM selector_candidates(?, [42, 57])
ORDER BY candidate_index;

SELECT selector
FROM selector_candidates(?, [42, 57])
WHERE exact_on_capture AND NOT positional
ORDER BY candidate_index
LIMIT 1;

Results describe this capture only. A selector that is exact here can match nothing, or different elements, on other pages or on a later capture of the same page, so check it on each capture you use it with. Duplicate IDs and shared classes appear as extra matches. Candidates use only selector features that query_selector_all supports, and none is returned without being run. Unknown or unpublished captures, XML and JSON documents, and node indexes that are not elements (including template contents) are errors, not empty results.

Each call returns at most 24 candidates of up to 512 characters. ID, class and attribute values longer than 128 characters are not used. At most two calls are allowed per query. Each candidate costs about as much as one query_selector_all call on the capture, within your normal read budget and deadline. Each internal lookup (the document check, the chosen elements with their ancestors, and each batch of up to eight candidates) may return at most 1 MiB. Exceeding a helper bound returns a helper_limit error.

Important boundaries

CSS helpers query the stored HTML tree, not a live browser. Unsupported selectors fail rather than being approximated. Search is token-based, not semantic search. Graph helpers combine the latest visible observations per page, not a simultaneous snapshot of the web.

Use selective filters and explicit columns. LIMIT bounds returned rows; it does not necessarily bound the work required by sorting, joins, or graph traversal. Result order is only guaranteed when you specify ORDER BY.

Regular expressions (match, extract, extractAll, replaceRegexpAll and similar) use RE2: repetition counts such as {n,m} must be at most 1000, and lookaround and backreferences are not supported. extractAll returns the first capturing group when the pattern has one, so write alternations as (?:a|b) when you want the whole match.

Capture structure

capture_structure groups one capture's HTML or XML elements by repeated syntax and returns one row per group. Use it to see which tags, child shapes or paths repeat before you write selectors or extraction SQL. Repetition is syntax, not a claim that elements are records, and samples are examples, not coverage.

capture_structure(capture_id
  [, mode]               -- 'siblings' (default), 'shape' or 'path'
  [, min_count = 2]      -- groups need at least this many members, 1–200000
  [, classes = false]    -- add class tokens to labels
  [, samples = 3]        -- sample members per group, 0–10
  [, text_chars = 120]   -- sample text characters, 0–500
  [, offset = 0]         -- skip this many groups
  [, limit = 100])       -- groups returned, 1–500

Pass capture_id as a UUID string or positional parameter and mode positionally or as mode = ...; other options are named. Arguments must be literals or parameters.

Labels are local names. SVG and MathML elements use svg: and math:; other namespaces, including XHTML inside XML, show {uri}name. With classes = true, sorted class tokens follow as .token. Special characters in names and tokens are escaped with a backslash, so class="a.b" shows as li.a\.b and class="a b" as li.a.b. Child shapes count element children only. Template contents appear under #fragment. Paths longer than 24 labels are clipped for display. Compare signature_id, not signature: it identifies the exact unclipped key and options, and is equal for the same key under different parents or captures.

Each row includes:

Repeated sibling lists on one capture:

SELECT signature, member_count, distinct_child_shapes, sample_texts
FROM capture_structure(?)
ORDER BY group_ordinal;

Card-like shapes with class names, largest first:

SELECT signature, member_count, common_child_shape, sample_node_indexes
FROM capture_structure(?, 'shape', classes = true, min_count = 5)
ORDER BY member_count DESC;

Full HTML of sampled elements. List the identities in one stage, then extract them in the outer SELECT:

WITH m AS (
  SELECT s.capture_id AS capture_id, s.signature AS signature, i AS node_index
  FROM capture_structure(?, limit = 5) s
  ARRAY JOIN s.sample_node_indexes AS i
)
SELECT signature, node_html(capture_id, node_index) AS html FROM m;

For more groups, repeat the same call with offset = next_offset until next_offset is NULL. Node indexes are positions in this capture and publication, not selectors that work on other pages.

• HTML and XML captures are supported. Unknown, failed, expired or unpublished captures, JSON (use json_values) and invalid XML return an error instead of an empty result.
• Zero rows means no group reached min_count; try min_count = 1.
• Structure comes from the stored static tree, not a live browser, and does not show that a page rendered completely.
• Each call reads only its capture. Documents above 200,000 nodes or 32 MiB of tree data, and results above 8 MiB, return a helper_limit error rather than partial groups.
• A statement can contain at most two capture_structure calls.

Results and selection limits

The SQL workspace can display a limited result. Exports contain displayed rows, not an unlimited rerun. A truncated result is not the full answer.

Pins and schedules run their SQL once, in full, when you create them, independently of the workspace display limit. Your SQL LIMIT still applies. The default ceilings are 100,000 captures per pin and 10,000 URLs per schedule, with a 64 MiB identity-result budget; configured limits and execution deadlines can reject a selection. Partial selections fail instead of creating a pin or schedule.

Explore tables and functions

Related guides

• Query examples
• Python SDK and API
• Saved queries and history