Metadata-Version: 2.5
Name: bqsqlparse
Version: 0.1.1
Summary: A pure-Python BigQuery (GoogleSQL) parser with column-level lineage extraction: structs, arrays, UNNEST, PIVOT, QUALIFY, windows, MERGE and more.
Project-URL: Homepage, https://github.com/vamsikapa/bqsqlparse
Project-URL: Documentation, https://github.com/vamsikapa/bqsqlparse#readme
Project-URL: Issues, https://github.com/vamsikapa/bqsqlparse/issues
Author: Vamsi Kapa
License: MIT License
        
        Copyright (c) 2026 Vamsi Kapa
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
License-File: LICENSE
Keywords: ast,bigquery,column-lineage,data-lineage,googlesql,lineage,parser,sql
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: SQL
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Compilers
Classifier: Typing :: Typed
Requires-Python: >=3.9
Provides-Extra: dev
Requires-Dist: build; extra == 'dev'
Requires-Dist: pytest>=7.0; extra == 'dev'
Requires-Dist: twine; extra == 'dev'
Description-Content-Type: text/markdown

# bqsqlparse

A **pure-Python BigQuery (GoogleSQL) SQL parser** with built-in
**column (attribute) level lineage** extraction. No runtime dependencies.

- Tokenizer, recursive-descent parser, typed AST, and SQL re-generator for
  the BigQuery dialect
- Column-level lineage: trace every output attribute back to physical
  source table columns — through CTEs, subqueries, joins, set operations,
  `UNNEST`, struct field access, star expansion, `PIVOT`/`UNPIVOT`, and DML
- Usage report: for every source column, which clauses reference it
  (`SELECT`, `JOIN`, `WHERE`, `GROUP_BY`, `HAVING`, `QUALIFY`, `ORDER_BY`, ...)
- Supports Python 3.9+

## Installation

```bash
pip install bqsqlparse
```

## Contents

- [Supported syntax](#supported-syntax)
- [Tokenizing](#tokenizing)
- [Parsing](#parsing) — AST inspection, rewriting SQL, multi-statement scripts, error reporting
- [Parsing feature tour](#parsing-feature-tour) — structs, arrays, UNNEST, windows, PIVOT, GROUP BY variants, typed literals, DML, scripting, routines
- [Column-level lineage](#column-level-lineage) — usage report, star expansion, set ops, subqueries, DML, scripts, serialization
- [Function registry](#function-registry)
- [Error types](#error-types)
- [API summary](#api-summary)

## Supported syntax

| Area | Constructs |
| --- | --- |
| Queries | `SELECT` (incl. `AS STRUCT` / `AS VALUE`), `WITH` / `WITH RECURSIVE`, `UNION`/`INTERSECT`/`EXCEPT` `ALL|DISTINCT`, `ORDER BY`, `LIMIT`/`OFFSET`, trailing commas |
| Select list | `* EXCEPT(...)`, `* REPLACE(...)`, `table.*`, implicit + explicit aliases |
| FROM | joins (`INNER`/`LEFT`/`RIGHT`/`FULL`/`CROSS`, `USING`), `UNNEST(...) [WITH OFFSET]`, subqueries, `PIVOT`, `UNPIVOT`, `TABLESAMPLE`, `FOR SYSTEM_TIME AS OF`, table-valued functions, `` `project.dataset.table` `` paths |
| Filtering | `WHERE`, `GROUP BY` (exprs, `ALL`, `ROLLUP`, `CUBE`, `GROUPING SETS`), `HAVING`, `QUALIFY`, named `WINDOW`s |
| Expressions | full operator precedence (`OR`/`AND`/`NOT`, comparisons, `|`, `^`, `&`, `<<`/`>>`, `+`/`-`, `*`/`/`/`||`, unary), `BETWEEN`, `IN` (list / subquery / `UNNEST`), `LIKE [ANY|ALL]`, `IS [NOT] NULL/TRUE/FALSE`, `IS [NOT] DISTINCT FROM`, `CASE`, `EXISTS` |
| Complex types | `STRUCT(...)` / `STRUCT<...>(...)`, `ARRAY[...]` / `ARRAY<T>[...]` / `ARRAY(subquery)`, nested `ARRAY<STRUCT<...>>` types, struct field access `a.b.c`, array subscripts `[OFFSET(i)]` / `[ORDINAL(i)]` / `[SAFE_OFFSET(i)]` / `[SAFE_ORDINAL(i)]` / JSON `j['key']` |
| Functions | any function call incl. dotted paths (`SAFE.`, `NET.`, `HLL_COUNT.`, UDFs), `COUNT(*)`, `DISTINCT`, `IGNORE|RESPECT NULLS`, `ORDER BY ... LIMIT` inside aggregates, `HAVING MAX/MIN`, named arguments (`param => value`), analytic `OVER (...)` with frames, `CAST`/`SAFE_CAST` (+ `FORMAT`), `EXTRACT`, `INTERVAL` |
| Literals | strings (raw / bytes / triple-quoted, escapes), numbers (hex, exponent), typed literals (`DATE '...'`, `TIMESTAMP '...'`, `NUMERIC '...'`, `JSON '...'`), parameters (`@name`, `@@system`, `?`) |
| Identifiers | backtick quoting throughout — reserved keywords as column names (`` SELECT `limit`, t.`from` ``) parse and regenerate correctly; non-reserved words (`value`, `date`, `offset`, `key`, ...) work unquoted |
| Statements | `CREATE [OR REPLACE] [TEMP] TABLE / VIEW / MATERIALIZED VIEW ... AS SELECT` (with `PARTITION BY`, `CLUSTER BY`, `OPTIONS`), `INSERT`, `UPDATE`, `DELETE`, `MERGE` (all `WHEN` variants), `TRUNCATE TABLE`, `DROP` |
| Scripting | `DECLARE`, `SET` (incl. `(a, b) = ...` and `@@system` vars), `BEGIN ... EXCEPTION WHEN ERROR THEN ... END`, `IF/ELSEIF/ELSE`, `LOOP`, `WHILE`, `REPEAT ... UNTIL`, `FOR ... IN (query) DO`, labels, `BREAK`/`LEAVE`/`CONTINUE`/`ITERATE`, `CALL`, `RETURN`, `RAISE [USING MESSAGE]`, `EXECUTE IMMEDIATE ... INTO ... USING`, `ASSERT`, `BEGIN/COMMIT/ROLLBACK TRANSACTION` |
| Routines | `CREATE [OR REPLACE] PROCEDURE` (with `IN`/`OUT`/`INOUT` params), `CREATE [TEMP] [AGGREGATE] FUNCTION` (SQL and `LANGUAGE js` bodies, `ANY TYPE`, `DETERMINISTIC`), `CREATE TABLE FUNCTION ... RETURNS TABLE<...>` |

## Tokenizing

`tokenize()` returns the raw token stream with exact line/column positions —
useful for linters, formatters and syntax highlighters:

```python
from bqsqlparse import tokenize

for tok in tokenize("SELECT `q col` FROM t WHERE x >= @min"):
    print(tok)
# Token(KEYWORD, 'SELECT', L1:C1)
# Token(QIDENT, 'q col', L1:C8)
# Token(KEYWORD, 'FROM', L1:C16)
# Token(IDENT, 't', L1:C21)
# Token(KEYWORD, 'WHERE', L1:C23)
# Token(IDENT, 'x', L1:C29)
# Token(OP, '>=', L1:C31)
# Token(PARAM, '@min', L1:C34)
```

Token types: `KEYWORD` (reserved words, uppercased), `IDENT`, `QIDENT`
(backtick-quoted), `STRING`, `BYTES`, `NUMBER`, `PARAM`, `OP`, `EOF`.
String tokens carry the *decoded* value (escapes processed, unless the
literal used a raw `r'...'` prefix).

## Parsing

```python
import bqsqlparse

ast = bqsqlparse.parse_one("""
    SELECT s.customer.id, ARRAY_AGG(item.sku IGNORE NULLS ORDER BY item.price LIMIT 3) top_skus
    FROM `proj.ds.sales` s, UNNEST(s.line_items) AS item
    GROUP BY 1
    QUALIFY ROW_NUMBER() OVER (PARTITION BY s.customer.id ORDER BY s.ts DESC) = 1
""")

# Typed AST
print(type(ast).__name__)          # Query
print(ast.body.items[1].alias)     # top_skus

# Walk the tree
from bqsqlparse import nodes
for func in ast.find_all(nodes.FuncCall):
    print(func.name_str)

# Regenerate SQL
print(ast.sql())
```

### Inspecting the AST

Every node is a typed dataclass with `children()`, `walk()`, `find_all()`
and `sql()`:

```python
from bqsqlparse import parse_one, nodes

ast = parse_one("SELECT a, SUM(b) AS total FROM ds.t GROUP BY a HAVING SUM(b) > 0")

sel = ast.body                      # nodes.Select
print(sel.items[1].alias)           # total
print(sel.group_by.exprs[0].path)   # ['a']
print(sel.having.sql())             # SUM(b) > 0

# find every table and every function used anywhere in the statement
print([t.full_name for t in ast.find_all(nodes.TableRef)])   # ['ds.t']
print([f.name_str for f in ast.find_all(nodes.FuncCall)])    # ['SUM', 'SUM']
```

### Rewriting SQL programmatically

The AST is mutable — edit nodes, then regenerate. Example: repoint a query
from staging to prod:

```python
ast = parse_one("SELECT id, amount FROM proj.staging.orders WHERE amount > 100")
for tref in ast.find_all(nodes.TableRef):
    tref.path = ["proj", "prod", "orders"]
print(ast.sql())
# SELECT id, amount FROM proj.prod.orders WHERE amount > 100
```

### Multi-statement scripts

```python
from bqsqlparse import parse

stmts = parse("CREATE TEMP TABLE x AS SELECT 1 a; SELECT * FROM x;")
print([type(s).__name__ for s in stmts])
# ['CreateTableAsSelect', 'Query']
```

### Error reporting

All errors carry the offending line and column:

```python
from bqsqlparse import parse_one, ParseError

try:
    parse_one("SELECT a FROM WHERE x = 1")
except ParseError as e:
    print(e)            # Expected identifier (got 'WHERE') at line 1, column 15
    print(e.line, e.col)  # 1 15
```

## Parsing feature tour

### Structs, arrays and nested types

```python
ast = parse_one("""
    SELECT
      STRUCT(1 AS a, 'x' AS b)                          AS s1,
      STRUCT<a INT64, b STRING>(1, 'x')                 AS s2,
      ARRAY[1, 2, 3]                                    AS a1,
      ARRAY<FLOAT64>[1.0, 2.5]                          AS a2,
      [4, 5]                                            AS a3,
      ARRAY(SELECT v FROM ds.vals)                      AS a4,
      CAST(x AS ARRAY<STRUCT<k STRING, v ARRAY<INT64>>>) AS deep
    FROM t
""")
deep = ast.body.items[6].expr.to_type
print(deep.name, deep.element.fields_[0].name)   # ARRAY k
```

### UNNEST, struct access and array subscripts

```python
parse_one("""
    SELECT
      item.sku,
      order_.payload.customer.address.city,
      tags[OFFSET(0)], tags[SAFE_ORDINAL(2)],
      json_col['results'][0]['id']
    FROM ds.orders AS order_
    CROSS JOIN UNNEST(order_.items) AS item WITH OFFSET AS pos
""")
```

### Window functions, QUALIFY and named windows

```python
ast = parse_one("""
    SELECT
      ROW_NUMBER() OVER (PARTITION BY uid ORDER BY ts DESC) AS rn,
      SUM(amt) OVER (ORDER BY ts ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS wk,
      LAG(amt, 1) OVER w AS prev_amt
    FROM ds.txns
    QUALIFY rn = 1
    WINDOW w AS (PARTITION BY uid ORDER BY ts)
""")
frame = ast.body.items[1].expr.over.frame
print(frame.unit, frame.start.kind)   # ROWS PRECEDING
```

### Aggregate modifiers

```python
parse_one("""
    SELECT
      COUNT(*), COUNT(DISTINCT uid),
      ARRAY_AGG(sku IGNORE NULLS ORDER BY price DESC LIMIT 10),
      STRING_AGG(name, ', ' ORDER BY name),
      ANY_VALUE(payload HAVING MAX version)
    FROM t
""")
```

### GROUP BY variants

```python
parse_one("SELECT a, SUM(b) FROM t GROUP BY ALL")
parse_one("SELECT a, b, SUM(c) FROM t GROUP BY ROLLUP(a, b)")
parse_one("SELECT a, b, SUM(c) FROM t GROUP BY CUBE(a, b)")
parse_one("SELECT a, b, SUM(c) FROM t GROUP BY GROUPING SETS ((a, b), a, ())")
```

### PIVOT, UNPIVOT, TABLESAMPLE and time travel

```python
parse_one("""
    SELECT * FROM sales
    PIVOT(SUM(amount) AS total FOR quarter IN ('Q1' AS q1, 'Q2' AS q2)) p
""")
parse_one("SELECT * FROM wide UNPIVOT(value FOR quarter IN (q1, q2, q3))")
parse_one("SELECT * FROM big.t TABLESAMPLE SYSTEM (1 PERCENT)")
parse_one("SELECT * FROM ds.t FOR SYSTEM_TIME AS OF TIMESTAMP '2024-01-01'")
```

### Typed literals, parameters and special expressions

```python
parse_one("""
    SELECT
      DATE '2024-01-01', TIMESTAMP '2024-01-01 00:00:00+00', JSON '{"a": 1}',
      NUMERIC '9.99', b'\\xDE\\xAD', r'raw\\d+', '''multi
      line''',
      ts + INTERVAL 90 MINUTE, INTERVAL '1-2' YEAR TO MONTH,
      SAFE_CAST(v AS BIGNUMERIC), CAST(d AS STRING FORMAT 'YYYY-MM'),
      EXTRACT(WEEK(MONDAY) FROM dt), EXTRACT(HOUR FROM ts AT TIME ZONE 'UTC'),
      x IS DISTINCT FROM y, s LIKE ANY ('a%', 'b%'), v IN UNNEST(arr),
      IF(a > 0, 'pos', 'neg'), fn(mode => 'strict', max_rows => 10)
    FROM t
    WHERE uid = @user_id AND shard = ? AND region = @@region
""")
```

### Reserved keywords as identifiers

```python
ast = parse_one("SELECT `limit`, t.`from`, `order` AS o FROM ds.t AS t")
print(ast.sql())   # SELECT `limit`, t.`from`, `order` AS o FROM ds.t AS t
```

### DDL and DML

```python
parse_one("""
    CREATE OR REPLACE TABLE ds.out
    PARTITION BY DATE(ts) CLUSTER BY region
    OPTIONS(description = 'daily rollup')
    AS SELECT * FROM ds.src
""")
parse_one("INSERT INTO ds.t (a, b) VALUES (1, 'x'), (2, 'y')")
parse_one("UPDATE ds.t SET meta.updated = CURRENT_TIMESTAMP WHERE id = 1")
parse_one("DELETE FROM ds.t WHERE dt < '2020-01-01'")
parse_one("""
    MERGE ds.tgt t USING ds.src s ON t.id = s.id
    WHEN MATCHED AND s.deleted THEN DELETE
    WHEN MATCHED THEN UPDATE SET name = s.name
    WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name)
    WHEN NOT MATCHED BY SOURCE THEN DELETE
""")
```

### Scripts: loops, nested IFs, exception handling

The full BigQuery procedural language parses into typed nodes
(`ScriptBlock`, `IfStmt`, `LoopStmt`, `WhileStmt`, `RepeatStmt`,
`ForInStmt`, ...):

```python
script = """
    DECLARE retries INT64 DEFAULT 0;
    DECLARE done BOOL DEFAULT FALSE;
    outer_loop: LOOP
      BEGIN
        IF retries > 3 THEN
          LEAVE outer_loop;
        ELSEIF done THEN
          BREAK;
        ELSE
          MERGE ds.tgt t USING ds.src s ON t.id = s.id
          WHEN MATCHED THEN UPDATE SET v = s.v;
          SET done = TRUE;
        END IF;
      EXCEPTION WHEN ERROR THEN
        SET retries = retries + 1;
      END;
    END LOOP outer_loop;
"""
stmts = parse(script)
loop = stmts[2]
print(type(loop).__name__, loop.label)     # LoopStmt outer_loop
block = loop.statements[0]
print(block.has_exception_handler)         # True
nested_if = block.statements[0]
print(len(nested_if.branches))             # 2  (IF + ELSEIF)
```

Also covered: `WHILE ... DO ... END WHILE`, `REPEAT ... UNTIL ... END REPEAT`,
`FOR rec IN (SELECT ...) DO ... END FOR`, `CALL`, `RETURN`,
`RAISE USING MESSAGE = ...`, `EXECUTE IMMEDIATE ... INTO ... USING`,
`ASSERT ... AS '...'`, transactions, `TRUNCATE TABLE`, `DROP ...`.

### Routines

```python
proc = parse_one("""
    CREATE OR REPLACE PROCEDURE ds.upsert_users(IN batch_date DATE, OUT rows_added INT64)
    BEGIN
      MERGE ds.users u USING ds.staging s ON u.id = s.id
      WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
      SET rows_added = @@row_count;
    END
""")
print([(p.mode, p.name) for p in proc.params])  # [('IN', 'batch_date'), ('OUT', 'rows_added')]

parse_one("CREATE TEMP FUNCTION add_tax(price FLOAT64) RETURNS FLOAT64 AS (price * 1.1)")
parse_one('CREATE FUNCTION ds.greet(name STRING) RETURNS STRING LANGUAGE js AS "return name;"')
parse_one("CREATE TEMP FUNCTION dbl(x ANY TYPE) AS (x * 2)")
parse_one("""
    CREATE TABLE FUNCTION ds.recent_orders(cutoff DATE)
    RETURNS TABLE<id INT64, amount NUMERIC>
    AS (SELECT id, amount FROM ds.orders WHERE dt >= cutoff)
""")
```

## Column-level lineage

```python
from bqsqlparse import extract_lineage

result = extract_lineage("""
    CREATE OR REPLACE TABLE proj.mart.customer_totals AS
    WITH base AS (
      SELECT o.customer_id, o.amount, c.region
      FROM proj.ds.orders o
      JOIN proj.ds.customers c ON o.customer_id = c.id
    )
    SELECT customer_id, region, SUM(amount) AS total
    FROM base
    GROUP BY 1, 2
""", include_indirect=True)

print(result.target)     # proj.mart.customer_totals
print(result.tables)     # ['proj.ds.customers', 'proj.ds.orders']  (physical tables only)
print(result.ctes)       # ['base']                                 (CTE names, kept separate)

for col in result.columns:
    print(col.name, col.transformation,
          [str(s) for s in col.sources],
          [str(s) for s in col.indirect_sources])
# customer_id IDENTITY    ['proj.ds.orders.customer_id'] [...]
# region      IDENTITY    ['proj.ds.customers.region']   [...]
# total       AGGREGATION ['proj.ds.orders.amount']      [...]

# Graph edges: (source, target) pairs, ready for networkx / OpenLineage
print(result.edges)
# [('proj.ds.orders.customer_id', 'proj.mart.customer_totals.customer_id'), ...]

print(result.to_json())
```

### Clause usage report

`result.usage` tells you *where* each physical source column is referenced,
across every level of the statement (CTEs and subqueries included):

```python
r = extract_lineage("""
    SELECT o.amount, d.region
    FROM ds.orders o JOIN ds.dim d ON o.k = d.k
    WHERE o.status = 'paid'
    GROUP BY d.region, o.amount
    ORDER BY o.ts
""")
for u in r.usage:
    print(f"{u.table}.{u.column}: {u.contexts}")
# ds.dim.k:        ['JOIN']
# ds.dim.region:   ['GROUP_BY', 'SELECT']
# ds.orders.amount:['GROUP_BY', 'SELECT']
# ds.orders.k:     ['JOIN']
# ds.orders.status:['WHERE']
# ds.orders.ts:    ['ORDER_BY']
```

Contexts: `SELECT`, `JOIN`, `WHERE`, `GROUP_BY`, `HAVING`, `QUALIFY`,
`ORDER_BY`, `UNNEST`, `PIVOT`, `TABLE_FUNCTION`, and for DML: `SET`,
`INSERT` (MERGE `ON` reports as `JOIN`, `WHEN ... AND` conditions as
`WHERE`). `ORDER BY` on a select-list alias is resolved back to the
underlying source column.

### Aliases vs. tables vs. CTEs

Table aliases and CTE names are fully resolved during analysis and never
leak into results: `result.tables` contains only physical tables,
`result.ctes` lists the CTE names encountered, and all `SourceColumn.table`
values are physical, fully-qualified names.

### Structs, arrays and UNNEST

Struct field paths are preserved end-to-end:

```python
r = extract_lineage("""
    SELECT item.sku, e.payload.user.id AS uid
    FROM ds.events e, UNNEST(e.items) AS item
""")
# sku <- ds.events.items.sku
# uid <- ds.events.payload.user.id
```

### Star expansion with a schema

Without table schemas, `SELECT *` is reported as a `table.*` pass-through
edge. Provide schemas to fully expand stars and disambiguate unqualified
columns across joins:

```python
r = extract_lineage(
    "SELECT * EXCEPT (pii) FROM ds.users",
    schema={"ds.users": ["id", "email", "pii"]},
)
# columns: id <- ds.users.id, email <- ds.users.email
```

Stars still resolve *through* subqueries and CTEs without a schema:

```python
r = extract_lineage("SELECT x.col1 FROM (SELECT * FROM ds.t) x")
# col1 <- ds.t.col1
```

### Set operations

UNION/INTERSECT/EXCEPT branches are merged positionally — one output column
collects sources from every branch:

```python
r = extract_lineage(
    "SELECT id, amount FROM ds.au_sales UNION ALL SELECT id, amt FROM ds.nz_sales"
)
# id     <- ds.au_sales.id, ds.nz_sales.id
# amount <- ds.au_sales.amount, ds.nz_sales.amt
```

### Correlated and scalar subqueries

```python
r = extract_lineage("""
    SELECT name,
           (SELECT MAX(score) FROM ds.scores s WHERE s.uid = u.id) AS best
    FROM ds.users u
""")
# name <- ds.users.name
# best <- ds.scores.score
```

### Transformation classification

Each output column carries one of: `IDENTITY`, `EXPRESSION`, `AGGREGATION`,
`WINDOW`, `CONSTANT`, `STAR`, `OFFSET`. Classification describes the final
hop that produced the column.

### DML lineage

`INSERT ... SELECT`, `UPDATE ... FROM`, `MERGE` and `CREATE TABLE AS SELECT`
all produce lineage against their target table:

```python
r = extract_lineage("""
    MERGE ds.tgt t USING ds.src s ON t.id = s.id
    WHEN MATCHED THEN UPDATE SET name = s.name
    WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name)
""")
# name <- ds.src.name, id <- ds.src.id
```

`INSERT` column lists rename query outputs positionally:

```python
r = extract_lineage(
    "INSERT INTO ds.tgt (cid, total) SELECT customer_id, SUM(amt) FROM ds.src GROUP BY 1"
)
# cid <- ds.src.customer_id, total <- ds.src.amt (AGGREGATION)
```

`UPDATE ... FROM` resolves assignments against both target and joined
sources, and `DELETE` reports the filter columns via the usage report:

```python
r = extract_lineage(
    "UPDATE ds.users u SET tier = s.tier, updated = CURRENT_TIMESTAMP "
    "FROM ds.scores s WHERE u.id = s.uid"
)
# tier    IDENTITY <- ds.scores.tier
# updated CONSTANT <- (none)
# usage: ds.scores.tier [SET], ds.scores.uid [WHERE], ds.users.id [WHERE]

r = extract_lineage("DELETE FROM ds.events WHERE dt < '2020-01-01'")
# target: ds.events, usage: ds.events.dt [WHERE]
```

### Indirect lineage

With `include_indirect=True`, each output column also lists the columns that
influenced *which rows* it contains (WHERE / JOIN ON / GROUP BY / HAVING /
QUALIFY):

```python
r = extract_lineage(
    "SELECT o.amount FROM ds.orders o JOIN ds.dim d ON o.k = d.k "
    "WHERE d.region = 'AU'",
    include_indirect=True,
)
print([str(s) for s in r.columns[0].indirect_sources])
# ['ds.dim.k', 'ds.dim.region', 'ds.orders.k']
```

### Serialization and graph export

```python
r = extract_lineage("CREATE TABLE ds.out AS SELECT a AS x, b + 1 AS y FROM ds.t")

r.edges          # [('ds.t.a', 'ds.out.x'), ('ds.t.b', 'ds.out.y')]
r.to_dict()      # nested dict: target, tables, ctes, columns, usage
r.to_json()      # pretty JSON of the same

# feed straight into networkx
import networkx as nx
g = nx.DiGraph(r.edges)
```

### Script and procedure lineage

`extract_script_lineage()` walks every control-flow branch (BEGIN blocks,
IF/ELSEIF/ELSE, all loop bodies, exception handlers, procedure bodies) and
returns one `LineageResult` per lineage-bearing statement. Script variables
(`DECLARE`, `FOR` loop vars, `EXECUTE IMMEDIATE ... INTO` targets) are
automatically excluded from column resolution so they are never mistaken
for table columns:

```python
from bqsqlparse import extract_script_lineage

results = extract_script_lineage("""
    DECLARE min_amt INT64 DEFAULT 100;
    IF EXTRACT(DAYOFWEEK FROM CURRENT_DATE) = 1 THEN
      INSERT INTO ds.weekly (uid, total)
      SELECT user_id, SUM(amt) FROM ds.sales WHERE amt > min_amt GROUP BY 1;
    ELSE
      INSERT INTO ds.daily (uid, total)
      SELECT user_id, SUM(amt) FROM ds.sales WHERE amt > min_amt GROUP BY 1;
    END IF;
""")
print([r.target for r in results])   # ['ds.weekly', 'ds.daily']
# min_amt never appears as a source column — it is a script variable
```

Works for stored procedures and table functions too:

```python
(r,) = extract_script_lineage("""
    CREATE PROCEDURE ds.sync()
    BEGIN
      MERGE ds.tgt t USING ds.src s ON t.id = s.id
      WHEN MATCHED THEN UPDATE SET v = s.v;
    END
""")
print(r.target)   # ds.tgt

r = extract_lineage("""
    CREATE TABLE FUNCTION ds.recent(cutoff DATE)
    RETURNS TABLE<id INT64>
    AS (SELECT id FROM ds.orders WHERE dt >= cutoff)
""")
print(r.target)   # ds.recent   (cutoff is treated as a parameter, not a column)
```

`extract_lineage(...)` also accepts a `variables=[...]` list to exclude
known script variables when analyzing a single statement, and works
directly on a script if it contains exactly one lineage-bearing statement.

## Function registry

A registry of ~350 BigQuery built-ins powers transformation classification
and is exposed for your own tooling:

```python
import bqsqlparse

bqsqlparse.is_aggregate("ARRAY_AGG")        # True
bqsqlparse.is_navigation("LAG")             # True  (window-only functions)
bqsqlparse.is_known_function("ST_DISTANCE") # True

sorted(bqsqlparse.SCALAR_FUNCTIONS)
# ['array', 'conditional', 'conversion', 'date_time', 'geography',
#  'hash_crypto', 'interval', 'json', 'math', 'net', 'range', 'search',
#  'string', 'utility']
bqsqlparse.AGGREGATE_FUNCTIONS   # frozenset of aggregate names
bqsqlparse.ALL_FUNCTIONS         # every registered built-in
```

## Error types

All exceptions derive from `BQSQLError` and carry `.line` / `.col`:

| Exception | Raised when |
| --- | --- |
| `LexError` | invalid character, unterminated string/comment |
| `ParseError` | unexpected token / invalid syntax |
| `UnsupportedStatementError` | statement type outside the supported set (e.g. `GRANT`, `DECLARE`) |
| `LineageError` | lineage requested for something without a query (e.g. `CREATE TABLE` with no `AS SELECT`) |

## API summary

| Function | Description |
| --- | --- |
| `parse(sql)` | Parse a script into a list of statement ASTs |
| `parse_one(sql)` | Parse exactly one statement |
| `tokenize(sql)` | Token stream (`Token` objects with line/col) |
| `to_sql(node)` / `node.sql()` | Regenerate SQL from an AST |
| `extract_lineage(sql, schema=None, include_indirect=False, variables=None)` | Column-level lineage (`LineageResult`) |
| `extract_script_lineage(sql, ...)` | One `LineageResult` per DML/query inside a script or procedure |
| `LineageResult.usage` | Per source column: clauses where it is used |
| `LineageResult.ctes` / `.tables` | CTE names vs. physical source tables |
| `is_aggregate / is_navigation / is_known_function` | BigQuery function registry lookups |

## Limitations

- Unquoted dash-separated project names (`my-project.ds.t`) must be
  backtick-quoted (`` `my-project.ds.t` ``)
- A few administrative statements (`GRANT`, `EXPORT DATA`, `LOAD DATA`,
  `ALTER ...`) raise `UnsupportedStatementError`
- `EXECUTE IMMEDIATE` with dynamic SQL strings cannot be traced statically
- Lineage classification reports the final transformation hop per column

## Development

```bash
pip install -e ".[dev]"
pytest
```

See [DEPLOYMENT.md](DEPLOYMENT.md) for publishing to PyPI.

## License

MIT
