Parsing, validation and lineage

The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

The AST

sql_parse() returns the full abstract syntax tree as nested R lists, following the upstream JSON AST format:

ast <- sql_parse("SELECT a, SUM(b) AS total FROM t GROUP BY a")
ast
#> <polyglot_ast> 1 statement (generic)
#> [1] select
str(ast$statements[[1]], max.level = 3, list.len = 4)
#> List of 1
#>  $ select:List of 26
#>   ..$ cluster_by      : NULL
#>   ..$ connect         : NULL
#>   ..$ distinct        : logi FALSE
#>   ..$ distinct_on     : NULL
#>   .. [list output truncated]

The AST round-trips: sql_generate() renders it back to SQL in any dialect.

Tokens

For lower-level tooling (syntax highlighting, linters), sql_tokenize() exposes the token stream with exact positions:

sql_tokenize("SELECT a FROM t WHERE x = 'hé'")
#> <polyglot_tokens> 8 tokens
#>     type   text line column start end
#> 1 Select SELECT    1      7     0   6
#> 2    Var      a    1      9     7   8
#> 3   From   FROM    1     14     9  13
#> 4    Var      t    1     16    14  15
#> 5  Where  WHERE    1     22    16  21
#> 6    Var      x    1     24    22  23
#> 7     Eq      =    1     26    24  25
#> 8 String     hé    1     31    26  30

Validation

Three layers of checking are available:

# 1. Syntax only (default)
sql_validate("SELECT FROM WHERE")
#> <polyglot_validation> invalid (generic)
#> [E003] Expected table name or subquery, got Where at 1:18

# 2. Strict syntax + semantic lint warnings
sql_validate("SELECT name, FROM employees", strict_syntax = TRUE)
#> <polyglot_validation> invalid (generic)
#> [E005] Trailing comma before FROM is not allowed in strict syntax mode at 1:13
sql_validate("SELECT *, category FROM products LIMIT 10", semantic = TRUE)
#> <polyglot_validation> valid (generic)
#> [W001] SELECT * is discouraged; specify columns explicitly for better
#> performance and maintainability
#> [W004] LIMIT without ORDER BY produces non-deterministic results

The third layer is schema-aware validation. Describe your tables as a named list — names are tables, values are (optionally named) column vectors:

schema <- list(
  orders = c(o_id = "INT", o_user = "INT", o_total = "DECIMAL(10,2)"),
  users  = c(id = "INT", name = "TEXT")
)

sql_validate("SELECT o_missing FROM orders", schema = schema)
#> <polyglot_validation> invalid (generic)
#> [E201] Unknown column 'o_missing' in table 'orders'

Use error = TRUE to turn an invalid result into a polyglot_validation_error condition — convenient in pipelines.

Source tables

sql_source_tables(
  "WITH cte AS (SELECT id FROM base)
   SELECT * FROM cte JOIN other USING (id)"
)
#> [1] "other" "base"

Note the CTE itself is not listed — only physical sources are.

Column-level lineage

sql_lineage() traces every output column through CTEs, subqueries and expressions down to source tables:

lin <- sql_lineage(
  "WITH base AS (SELECT id, amount FROM payments)
   SELECT id, amount * 2 AS doubled FROM base"
)
lin
#> <polyglot_lineage> 2 columns (generic)
#> id ← payments
#> doubled ← payments

Each entry carries the full lineage tree:

str(lin$columns[[2]]$tree, max.level = 2)
#> List of 5
#>  $ downstream :List of 1
#>   ..$ :List of 5
#>  $ expression : chr "amount * 2 AS doubled"
#>  $ name       : chr "doubled"
#>  $ source_kind: chr "Root"
#>  $ source_name: chr ""

A schema improves resolution of unqualified or ambiguous columns, and column = restricts lineage to one output column.

Structural analysis

sql_analyze() condenses a query into facts — shape, projections, relations, CTEs, set operations:

a <- sql_analyze(
  "WITH x AS (SELECT id FROM t)
   SELECT x.id, UPPER(name) AS shout FROM x JOIN u ON x.id = u.id"
)
a
#> <polyglot_analysis> shape: select (generic)
#> projections: id, shout
#> relations: u, x
#> ctes: x
vapply(a$projections, function(p) p$transformKind, character(1))
#> [1] "direct"     "expression"

OpenLineage export

For data catalogs that speak OpenLineage, sql_openlineage() emits a columnLineage facet with inferred input/output datasets:

ol <- sql_openlineage(
  "INSERT INTO reports SELECT id, total FROM sales",
  namespace = "warehouse"
)
names(ol)
#> [1] "facet"    "inputs"   "outputs"  "warnings"

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.