SQL dialect and cost

Periplus public SQL is a read-only subset of ClickHouse SQL, with Periplus helpers. Use schema_get for the running catalogue, column types and helper signatures. One SELECT statement is accepted, including CTEs, joins and aggregates. DDL, writes, external sources, system tables and private schemas are unavailable to customers. The engine validates native functions and types; this is not a promise that every ClickHouse construct survives the public compiler. Crawl follow SQL is a separate page-local DuckDB dialect.

Native functions commonly useful here include domain(url), cutToFirstSignificantSubdomain(url), match/extract, JSONExtractString, isValidJSON and parseDateTimeBestEffortOrNull. Use explicit AS aliases, especially for reserved words such as sample. LIMIT n BY key LIMIT m applies a per-key limit then an overall limit; use ORDER BY to define which rows win.

Pass values as positional ? parameters, with one value per placeholder and at most 100 parameter values per request. This is not a capture or result-row limit: IN (?, ?, ?) uses three parameters; selecting captures in SQL needs no parameter per capture. An array value counts as one parameter, for example has(?, toString(capture_id)) with parameters [["<capture-id>", "<another-capture-id>"]]. Query size and resource limits still apply. Do not interpolate untrusted page text. Graph helpers require literal or positional parameter arguments: look up a page_id first, then pass it to the helper. See schema_get for the exact walk/paths signature.

Select captures first

Define the intended capture cohort before extraction. Preserve its full membership when splitting work into smaller queries; do not silently sample or drop captures.

This example extracts every main h2 from two explicitly chosen captures. Pass their IDs as the two parameters. It preserves repeated headings and orders rows by capture ID and tree position; it has no SQL output cap:

WITH selected AS (
  SELECT capture_id, url FROM captures
  WHERE capture_id IN (?, ?)
)
SELECT c.capture_id, c.url, n.node_index,
       node_text(c.capture_id, n.node_index) AS heading
FROM selected c
CROSS JOIN LATERAL query_selector_all(c.capture_id, 'main h2') n
ORDER BY toString(c.capture_id), n.node_index

A CTE names a query; it does not guarantee materialization or caching. Repeated references can repeat work.

Latest-by-URL selection updates as the corpus changes. Define timestamp ordering, NULL handling and a capture-ID tie breaker. Explicit IDs freeze membership and are appropriate when a fixed capture set is intended, including controlled comparisons; they are not a general replacement for dynamic latest selection.

Avoid starting with corpus-wide nodes self-joins. Node text/HTML reconstruction can be expensive. LIMIT bounds returned rows, not necessarily scanned rows or bytes. A small cohort, a join to it or a capture_id IN subquery does not by itself guarantee scan pruning. Inspect read_rows, read_bytes, execution_ms and truncation. The read metrics count native logical reads across helper stages and the final SELECT; they are not physical disk-I/O measurements. Missing metrics mean unknown cost. Preparing a statement validates it but does not provide a reliable scan-cost estimate. Select only required columns and measure each extraction stage without changing the intended cohort. Evaluate scalar helpers in a CTE before grouping when grouping expressions cannot be resolved; scalar helpers are not categorically forbidden in aggregates. Report a minimal reproduction if valid SQL fails.

In an isolated three-capture fixture on ClickHouse 26.8.2.7, the two selected captures shared HTML with repeated, empty and Unicode headings; the third was unselected. At the same publication, this query returned eight rows, while adding LIMIT 1 returned one. Both reported read_rows=154 and read_bytes=12472. The test checks exact values, types, duplicates and ordering, not just counts. These fixture measurements do not predict costs or pruning on a larger corpus.

Extract rows from captured structure

Extract results in SQL rather than reading pages into a conversation and retyping values. The engine runs filters, joins, aggregates and coverage counts over every selected capture. Each row keeps the capture_id and node_index it came from, and a revised definition (another field, a stricter filter, a different unit) runs again over the retained captures without capturing anything again. Reading a few rows or one page's text is useful for choosing the row definition and fields. The results themselves should come from a query you can run again and save.

Use a few contrasting pages to test what one row is and which columns it needs. Show the user a small sample of what is possible and what is missing, and ask before expanding; users need not write SQL. Once they agree, run and save reusable SQL and report the coverage actually reached and where each value came from. The research-collaboration guide describes this practice and the brief stored in project.description.

The examples below use a listing whose cards each describe one park. One card is one row. They run unchanged against the public schema; replace the URL prefix, capture ID and selectors with the ones your source uses.

1. Find the repeated structure on one capture. Pass one capture_id:

SELECT signature, member_count, distinct_child_shapes, sample_node_indexes, sample_texts
FROM capture_structure(?, classes = true)
ORDER BY member_count DESC, group_ordinal

A group such as main > article.park with one member per record is a candidate row container. distinct_child_shapes above 1 means members differ, often because a field is absent from some cards. Repetition is syntax, not proof that each member is a record.

2. Measure a selector on that capture. Pass the capture_id and an array of the group's sample_node_indexes:

SELECT selector, strategy, match_count, extra_count, exact_on_capture
FROM selector_candidates(?, ?)
WHERE NOT positional AND matched_requested = requested_nodes
ORDER BY candidate_index

sample_node_indexes holds a few samples (3 by default), not every member, so a correct selector usually also matches members you did not pass. For a group of 100 cards, article.park reports match_count 100, extra_count 97 and exact_on_capture false. Prefer a non-positional selector whose match_count equals the group's member_count from step 1. exact_on_capture is true only when you passed every intended element, which is possible only for groups of up to 16. A match_count above member_count means the selector also matches other elements. Inspect extra_node_indexes, for example with node_context, before choosing it. Selectors are measured on this capture only.

3. Check it on every selected capture. Pass the URL prefix. A capture whose selector matches nothing disappears from a CROSS JOIN, so count against the selection:

WITH selected AS (
  SELECT capture_id, url FROM captures
  WHERE startsWith(url, ?)
  ORDER BY url, capture_id
  LIMIT 100
),
matched AS (
  SELECT s.capture_id AS capture_id, count() AS cards
  FROM selected AS s
  CROSS JOIN LATERAL query_selector_all(s.capture_id, 'article.park') AS m
  GROUP BY s.capture_id
)
SELECT s.capture_id AS capture_id, s.url AS url, if(m.capture_id = s.capture_id, m.cards, 0) AS cards
FROM selected AS s
LEFT JOIN matched AS m ON m.capture_id = s.capture_id
ORDER BY s.url, s.capture_id

Compare the cards with any total the page states. A shortfall is a coverage gap, for example unrequested paging URLs, not absence.

4. Extract one row per container. Pass the URL prefix. Each field comes from its own selector, is assigned to its card with closest(), and is joined on the capture and card:

WITH selected AS (
  SELECT capture_id, url, observed_at FROM captures
  WHERE startsWith(url, ?)
  ORDER BY url, capture_id
  LIMIT 100
),
cards AS (
  SELECT s.capture_id AS capture_id, s.url AS url, s.observed_at AS observed_at, m.node_index AS card_index
  FROM selected AS s
  CROSS JOIN LATERAL query_selector_all(s.capture_id, 'article.park') AS m
),
names AS (
  SELECT s.capture_id AS capture_id, closest(s.capture_id, m.node_index, 'article.park') AS card_index,
         trimBoth(node_text(s.capture_id, m.node_index)) AS name
  FROM selected AS s
  CROSS JOIN LATERAL query_selector_all(s.capture_id, 'article.park h2') AS m
),
visitor_cells AS (
  SELECT s.capture_id AS capture_id, closest(s.capture_id, m.node_index, 'article.park') AS card_index,
         m.node_index AS visitors_node, trimBoth(node_text(s.capture_id, m.node_index)) AS visitors_text
  FROM selected AS s
  CROSS JOIN LATERAL query_selector_all(s.capture_id, 'article.park .visitors') AS m
),
area_cells AS (
  SELECT s.capture_id AS capture_id, closest(s.capture_id, m.node_index, 'article.park') AS card_index,
         trimBoth(node_text(s.capture_id, m.node_index)) AS area_text
  FROM selected AS s
  CROSS JOIN LATERAL query_selector_all(s.capture_id, 'article.park .area') AS m
)
SELECT c.capture_id AS capture_id, c.url AS url, c.observed_at AS observed_at, c.card_index AS card_index,
       n.name AS name,
       if(v.capture_id = c.capture_id, v.visitors_node, NULL) AS visitors_node,
       if(v.capture_id = c.capture_id, v.visitors_text, NULL) AS visitors_text,
       toUInt64OrNull(replaceAll(visitors_text, ',', '')) AS visitors,
       multiIf(visitors_text IS NULL, 'missing', visitors IS NULL, 'unparsed', 'extracted') AS visitors_status,
       if(a.capture_id = c.capture_id, a.area_text, NULL) AS area_text,
       toFloat64OrNull(replaceAll(extract(area_text, '^([0-9,.]+) km'), ',', '')) AS area_km2,
       multiIf(area_text IS NULL, 'missing', area_km2 IS NULL, 'unparsed', 'extracted') AS area_status
FROM cards AS c
LEFT JOIN names AS n ON n.capture_id = c.capture_id AND n.card_index = c.card_index
LEFT JOIN visitor_cells AS v ON v.capture_id = c.capture_id AND v.card_index = c.card_index
LEFT JOIN area_cells AS a ON a.capture_id = c.capture_id AND a.card_index = c.card_index
ORDER BY c.url, c.card_index

• Missingness. Outer joins fill unmatched columns with type defaults (0, ''), not NULL. Test the joined key, as in if(v.capture_id = c.capture_id, ...), so a card without the field reads missing, not 0 or an empty string. A value that is present but does not parse stays visible in its text column as unparsed. A source zero stays 0.
• Provenance. capture_id, url, observed_at and the card and field node indexes identify the evidence for every value. node_text(capture_id, node_index) and node_html return the original, and node_context shows the surroundings.
• Coverage. The selected CTE's LIMIT and filters decide which captures count. A non-truncated result can still omit records they exclude. If truncated is true, page with a stable ORDER BY key instead of rerunning the same SQL.
• Iterate. To add a field or change a rule, edit this SQL and run it again on the same captures. Capture again only for pages that are missing, expired or stale. Validate with sql_execute on the real selection, then save the definition.

A statement may contain at most eight selector helper calls (query_selector, query_selector_all and closest). The limit counts call expressions in the SQL text, not rows: this example has seven, however many captures or cards it reads. For more fields, split extraction into several statements keyed by capture_id and card_index, or use fewer helper calls per statement. Selecting fewer captures does not change the call count. It reduces only the rows, bytes read and run time, which matter for resource limits and truncation.

Curated interpretation is a separate column. Values you type into SQL (literal arrays, VALUES, constants) record your reading of the sources, not an extraction. They are allowed, labelled as curated, beside the extracted columns and their capture IDs. Pass the URL prefix:

WITH extracted AS (
  SELECT c.capture_id AS capture_id, c.url AS url, m.node_index AS name_node,
         trimBoth(node_text(c.capture_id, m.node_index)) AS name
  FROM captures AS c
  CROSS JOIN LATERAL query_selector_all(c.capture_id, 'article.park h2') AS m
  WHERE startsWith(c.url, ?)
),
labels AS (
  SELECT tupleElement(t, 1) AS name, tupleElement(t, 2) AS landscape
  FROM (SELECT arrayJoin([('Alpha', 'coastal'), ('Delta', 'alpine')]) AS t)
)
SELECT e.capture_id AS capture_id, e.url AS url, e.name_node AS name_node, e.name AS name,
       if(l.name = e.name, l.landscape, NULL) AS landscape,
       if(l.name = e.name, 'curated', 'unlabelled') AS landscape_source
FROM extracted AS e
LEFT JOIN labels AS l ON l.name = e.name
ORDER BY e.url, e.name_node

Never type a value the source already states (a name, count, price or date) as a literal and present it as extracted: select it from its node instead. If a typed fact cannot be avoided, keep its capture_id and a node that states it, and check it before saving.

Inspect several positions at once. node_context takes literal or parameter arguments, so pass the capture_id and an array of node indexes:

SELECT target_index, relationship, node_index, name, text
FROM node_context(?, ?, ancestors = 0, siblings = 0, children = 4, elements_only = true)
ORDER BY target_index, node_index

Unsupported: calling node_context per row through LATERAL is rejected with an error. Select the node indexes first, then pass them as an array as above.

-- Rejected: node_context does not accept LATERAL or column arguments.
SELECT m.node_index AS card_index, x.relationship AS relationship, x.text AS text
FROM query_selector_all(?, 'article.park') AS m
CROSS JOIN LATERAL node_context(m.capture_id, m.node_index) AS x

Compose multi-stage queries

Turning captured records into rows often takes several stages: extract fields per record, combine sources, then join and compare. ClickHouse resolves names at each stage, so a later stage can read only the names an earlier one actually exposes.

• Project columns explicitly. SELECT * or t.* across USING joins can expose qualified names such as t.capture_id that a later stage cannot read as capture_id (code 47).
• Name identifiers by role where they are introduced. When several sources carry capture_id or node indexes, alias them (capture_id AS source_id, parent_index AS record_id) instead of carrying the shared name forward.
• Qualify every column once a statement joins sources, including in ORDER BY and GROUP BY, and alias output columns (g.code AS code). An unaliased g.code selected beside another joined code can leave a bare code ambiguous (code 207).
• Extract once, then split. Extract fields for all selected captures in one stage and filter it by source_id for each source role, rather than repeating near-identical extraction stages that must stay in step.
• Check what a stage exposes with SELECT * FROM stage LIMIT 1 before referencing its columns. This is not needed before every query.

This example reads XML or HTML records whose fields are child elements, from one or more captures, and pairs two periods per area. Each period keeps its own capture, record node and observation date, because the two values can come from different captures:

WITH records AS (
  SELECT f.capture_id AS source_id, f.parent_index AS record_id,
         maxIf(f.text, f.name = 'area') AS area,
         maxIf(f.text, f.name = 'period') AS period,
         toFloat64OrNull(maxIf(f.text, f.name = 'value')) AS value
  FROM nodes f
  WHERE f.capture_id IN (?, ?) AND f.kind = 'element'
  GROUP BY f.capture_id, f.parent_index
),
paired AS (
  SELECT r.area AS area,
         countIf(r.period = '2020') AS records_2020,
         countIf(r.period = '2025') AS records_2025,
         if(records_2020 = 1, anyIf(r.value, r.period = '2020'), NULL) AS value_2020,
         if(records_2025 = 1, anyIf(r.value, r.period = '2025'), NULL) AS value_2025,
         if(records_2020 = 1, anyIf(toNullable(r.source_id), r.period = '2020'), NULL) AS capture_2020,
         if(records_2025 = 1, anyIf(toNullable(r.source_id), r.period = '2025'), NULL) AS capture_2025,
         if(records_2020 = 1, anyIf(toNullable(r.record_id), r.period = '2020'), NULL) AS record_2020,
         if(records_2025 = 1, anyIf(toNullable(r.record_id), r.period = '2025'), NULL) AS record_2025
  FROM records r
  WHERE r.area != ''
  GROUP BY r.area
)
SELECT p.area AS area, p.value_2020 AS value_2020, p.value_2025 AS value_2025,
       multiIf(p.records_2020 > 1 OR p.records_2025 > 1, 'conflict',
               p.value_2020 IS NULL OR p.value_2025 IS NULL, 'missing', 'paired') AS evidence,
       p.records_2020 AS records_2020, p.records_2025 AS records_2025,
       p.capture_2020 AS capture_2020, p.record_2020 AS record_2020, c20.observed_at AS observed_2020,
       p.capture_2025 AS capture_2025, p.record_2025 AS record_2025, c25.observed_at AS observed_2025
FROM paired p
LEFT JOIN captures c20 ON c20.capture_id = p.capture_2020
LEFT JOIN captures c25 ON c25.capture_id = p.capture_2025
ORDER BY p.area

A period with no record has a NULL value, capture and record. A record whose value is absent or unparseable keeps its capture and record for inspection, with a NULL value; source zeros stay 0. A period with more than one record is marked conflict: its value and references are NULL rather than an arbitrary pick, and records_2020 or records_2025 shows how many records to inspect. Each value is cited only with the record it came from. Count missing and conflict rows against the expected keys before reporting results. When records are addressed by CSS selectors, query_selector_all, node_text and node_tree can replace a nodes self-join; whether they simplify extraction depends on the source's structure.

Results are live: source_snapshot identifies the layout/publication used, not a saved or retained result. Separate requests can use different publications; record and compare source_snapshot when checking results. Fixed IDs alone do not freeze parser output or retain captures. A saved query stores SQL, not a result snapshot.