---
title: "Python Integration"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Python Integration}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = FALSE
)
```

dplyneage has two lineage engines, and only one of them involves Python:

- **dbplyr pipelines** are analyzed by a pure-R engine that walks the pipeline's lazy query tree. No Python is initialized, let alone required.
- **Raw SQL strings** — and the rare pipeline that embeds raw SQL via `dbplyr::sql()` — are analyzed by [sqlglot](https://github.com/tobymao/sqlglot)'s lineage engine, called through the `reticulate` package. reticulate is a Suggests dependency, so install it once with `install.packages("reticulate")` to enable this engine.

So if you only ever pipe dplyr/dbplyr queries into `extract_lineage()`, you can stop reading here: Python never enters the picture, and neither does reticulate. The rest of this vignette covers how the sqlglot dependency is managed when you do analyze raw SQL. It follows the current best practice from the [reticulate package documentation](https://rstudio.github.io/reticulate/articles/package.html): Python dependencies are declared with `reticulate::py_require()` when the package loads, and reticulate provisions them automatically.

## Installation: There Is No Step Two

With reticulate installed, Python setup is automatic. The first time lineage extraction needs sqlglot, reticulate will:

1. Find a suitable Python (downloading a self-contained build via [uv](https://docs.astral.sh/uv/) if none is configured)
2. Provision an ephemeral virtual environment containing `sqlglot`
3. Cache everything, so subsequent sessions start quickly

```{r}
library(dplyneage)

# This just works - no install step required
extract_lineage("SELECT id, name FROM customers") |>
  lineage_flow()
```

You can verify availability at any time:

```{r}
has_sqlglot()
#> [1] TRUE
```

Note: `install_sqlglot()` from earlier development versions is deprecated and does nothing — there is no manual installation step anymore.

## Using Your Own Python Environment

If you manage your own Python environment (a project virtualenv, conda env, or a system Python), reticulate will respect it as usual. Just make sure `sqlglot` is installed there:

```bash
pip install 'sqlglot>=23.0.0'
```

```{r}
# Point reticulate at your environment before loading dplyneage
Sys.setenv(RETICULATE_PYTHON = "/path/to/your/python")

library(dplyneage)
has_sqlglot()
```

See `?reticulate::use_virtualenv` and the [reticulate Python version docs](https://rstudio.github.io/reticulate/articles/versions.html) for other ways to select an environment.

## How It Works

### Architecture

```
dbplyr pipeline ──→ pure-R walk of the lazy query tree
                                  │
raw SQL string ──→ Python (sqlglot.lineage engine)
                                  ↓
              Column Lineage Metadata (per output column)
                                  ↓
                    R (create nodes & edges)
                                  ↓
                 React Flow Visualization
```

Both engines emit the same lineage metadata, so everything downstream is shared. Which one runs is controlled by the `engine` argument of `extract_lineage()`: `"auto"` (the default) picks the R engine for lazy tables and sqlglot for SQL strings, falling back to sqlglot if a pipeline uses something the R engine cannot trace. `metadata$engine` in the result records which one ran.

### Key Components

1. **R/lineage_r_engine.R**: The pure-R engine
   - Walks dbplyr's lazy query tree, reading exact column provenance through selects, mutates, joins, and set operations — no SQL parsing, no Python

2. **R/zzz.R**: Package initialization
   - `.onLoad()`: declares the sqlglot requirement via `py_require()` and imports the bundled Python module with `delay_load` (Python does not start until first use)
   - `has_sqlglot()`: checks availability

3. **inst/python/dplyneage_lineage.py**: The sqlglot engine
   - Built on `sqlglot.lineage.lineage()`, which handles scope resolution, aliases, CTE trace-through, set operations, and star expansion
   - `extract_lineage()`: traces each output column to its source columns
   - `list_tables()`: enumerates base tables (used for schema harvesting)

4. **R/sqlglot_utils.R**: R-side orchestration
   - `extract_lineage()`: main user-facing function, dispatches to an engine
   - `harvest_schema()`: reads table schemas from your database connection so unqualified columns are attributed to the right table
   - `convert_lineage_to_graph()`: creates visualization nodes and edges

## Schemas and Attribution Accuracy

SQL alone does not always say which table an unqualified column belongs to. dbplyr lazy tables sidestep the problem entirely: the R engine reads provenance from the query tree, so no schema is ever needed. (When a lazy table falls back to sqlglot, dplyneage lists the columns of each referenced table from the live connection and hands that schema to sqlglot automatically.)

For raw SQL strings, you can pass a schema yourself:

```{r}
extract_lineage(
  "SELECT c.name, order_date FROM customers c JOIN orders o ON c.id = o.customer_id",
  schema = list(
    customers = c("id", "name"),
    orders = c("customer_id", "order_date")
  )
)
```

Without a schema, fully qualified columns still resolve correctly; unqualified ones may not be traceable, and `SELECT *` cannot be expanded (you'll get a warning).

## SQL Dialects

sqlglot supports many SQL dialects. Specify the dialect when extracting lineage:

```{r}
extract_lineage(query, dialect = "duckdb")     # default
extract_lineage(query, dialect = "postgres")
extract_lineage(query, dialect = "snowflake")
extract_lineage(query, dialect = "bigquery")
extract_lineage(query, dialect = "mysql")
```

The dialect should match your database backend to ensure accurate parsing. See the [sqlglot documentation](https://sqlglot.com/) for the full list of dialects.

## Performance

- **dbplyr pipelines**: no Python startup cost at all — the R engine runs immediately
- **First raw-SQL call**: may take a moment while the Python environment initializes (and, on the very first run, provisions)
- **Subsequent calls**: fast (<100ms for typical queries)
- **Complex queries**: sqlglot handles CTEs, subqueries, window functions, complex joins, and set operations (UNION, INTERSECT, etc.)

## Troubleshooting

### Check Python configuration

```{r}
# See which Python reticulate is using
reticulate::py_config()

# List installed packages
reticulate::py_list_packages()
```

### sqlglot not found in a custom environment

If `has_sqlglot()` returns `FALSE` and you have set `RETICULATE_PYTHON` (or activated an environment), sqlglot is missing from that environment — install it there with `pip install sqlglot`. If you have no custom configuration, reticulate should provision automatically; see `?reticulate::py_require` for details.

## References

- [Reticulate Package Documentation](https://rstudio.github.io/reticulate/)
- [Embedding Python in R Packages](https://rstudio.github.io/reticulate/articles/package.html)
- [sqlglot Documentation](https://sqlglot.com/)
- [sqlglot GitHub](https://github.com/tobymao/sqlglot)