# Wvlet > Wvlet is a flow-style query language that compiles to SQL for DuckDB, Trino, Snowflake, and Hive. Queries read top-to-bottom in data-processing order, so humans and AI agents can add a step without rewriting nested SQL. > > Version: 2026.3.0 This file contains all documentation content in a single document following the llmstxt.org standard. ## Wvlet for AI Agents Wvlet is a flow-style query language that compiles to SQL for DuckDB, Trino, Snowflake, and Hive. A query starts with `from` and lists one operator per line in the order the data is processed. That makes Wvlet **the query language agents edit well**: adding a filter, a column, or an aggregation is a one-line append, not a rewrite of nested SQL. This page is written for AI coding agents and the people who prompt them. The whole documentation is also available as plain text at [llms.txt](https://wvlet.org/wvlet/llms.txt) (index) and [llms-full.txt](https://wvlet.org/wvlet/llms-full.txt) (all pages). Every user-facing doc page has a Markdown copy at the same URL with a `.md` suffix. ### When should I choose Wvlet? Choose Wvlet when: - You build a query step by step, or keep changing one: each step is a line you can add, remove, or reorder without touching the rest. - You need the same logic on more than one engine. Wvlet generates engine-specific SQL for DuckDB, Trino, Snowflake, and Hive. - You want reusable, testable query modules: `model` definitions, `val` constants, and `test` assertions live next to the query in `.wv` files. - You explore local files (Parquet, CSV, JSON) with DuckDB, which is built in. Plain SQL is fine when the project already standardizes on SQL files, a tool only accepts SQL, or the query is a single short `SELECT`. Wvlet can still help there: `wvlet compile` turns a `.wv` file into SQL you can hand to any tool. ### Why do flow-style queries suit agents? - **Line-local edits.** Each line is one operator (`where`, `add`, `group by`, `agg`, `order by`, ...). A change touches the lines it is about, which keeps diffs small and reviews easy. - **Append-only growth.** The output of each line feeds the next one, so you can extend a working query by appending a line and check the result after every step. - **Reading order is execution order.** `from` comes first and `select` comes last (or not at all), so there is no need to jump between `SELECT`, `FROM`, `GROUP BY`, and `HAVING` to understand a query. ```wvlet from orders where status = 'paid' group by customer_id agg amount.sum as total where total > 100 -- filter groups (SQL HAVING) order by total desc limit 10 ``` ### Syntax cheat sheet Keywords are lower-case. Each operator takes the output of the previous line as its input. | Operator | What it does | Example | |----------|--------------|---------| | `from` | Read a table, a file, or inline values | `from orders`, `from 'data.parquet'` | | `where` | Filter rows; repeat it as often as needed | `where status = 'paid'` | | `select` | Choose output columns (optional) | `select id, amount` | | `select distinct` | Unique rows of the selected columns | `select distinct status` | | `add` | Append a computed column | `add amount * 1.1 as with_tax` | | `exclude` | Drop columns | `exclude status, amount` | | `rename` | Rename a column | `rename amount as price` | | `shift` | Move columns to the left | `shift amount` | | `group by` | Group rows by keys | `group by customer_id` | | `agg` | Add aggregations to the group keys | `agg _.count as n, amount.sum as total` | | `where` after `agg` | Filter groups | `where total > 100` | | `order by` | Sort rows | `order by total desc` | | `limit` | Keep the first n rows | `limit 10` | | `count` | Count all rows | `from orders count` | | `dedup` | Remove duplicate rows | `select status dedup` | | `sample` | Random sample | `sample 1000` | | `join` / `left join` | Join another relation | `join customers on orders.customer_id = customers.id` | | `concat` | Union all rows of another query | `concat { from archived_orders }` | | `with` | Name a subquery | `with paid as { from orders where status = 'paid' }` | | `model` | Define a reusable query | `model paid_orders = { from orders where status = 'paid' }` | | `val` | Define a constant or an inline table | `val t(id, name) = [[1, 'a'], [2, 'b']]` | | `test` | Assert on the result | `test _.size should be 10` | Expressions: - Strings use single or double quotes: `'paid'` and `"paid"` are both string literals. Quote identifiers with back quotes: `` `order id` ``. - Comparison: `=`, `!=`, `<`, `<=`, `>`, `>=`, `is null`, `is not null`, `in (...)`, `between ... and ...`, `like`. Logic: `and`, `or`, `not`. - Aggregations: `_.count` counts rows in a group; `column.sum`, `.avg`, `.min`, `.max`, `.count_distinct` aggregate a column. SQL functions such as `count(*)` and `sum(x)` also work. - Casts: `amount::int`, `'2026-01-01'::date`. - Conditionals: `if x > 0 then 'pos' else 'neg'`, or `case when x > 0 then 'pos' else 'neg'` (no `end`). - String interpolation: `s"order-${id}"`. - Window functions: `row_number() over (partition by customer_id order by amount desc)`. - Comments: `-- line comment`. `---` starts a Markdown doc comment block. See the [Query Syntax reference](./syntax/index.md) for every operator. ### SQL to Wvlet mapping | SQL | Wvlet | |-----|-------| | `SELECT * FROM t` | `from t` | | `SELECT a, b FROM t WHERE c > 0` | `from t where c > 0 select a, b` | | `SELECT *, x * 2 AS y FROM t` | `from t add x * 2 as y` | | `SELECT DISTINCT a FROM t` | `from t select distinct a` | | `SELECT COUNT(*) FROM t` | `from t count` | | `SELECT k, COUNT(*) AS n FROM t GROUP BY k` | `from t group by k agg _.count as n` | | `... GROUP BY k HAVING SUM(v) > 10` | `group by k agg v.sum as s where s > 10` | | `SELECT COUNT(DISTINCT u) FROM t` | `from t agg u.count_distinct as users` | | `... ORDER BY n DESC LIMIT 10` | `order by n desc limit 10` | | `FROM a JOIN b ON a.id = b.a_id` | `from a join b on a.id = b.a_id` | | `FROM a LEFT JOIN b ON ...` | `from a left join b on ...` | | `q1 UNION ALL q2` | `q1 concat { q2 }` | | `WITH x AS (q) SELECT ... FROM x` | `with x as { q } from x ...` | | `CAST(x AS INT)` | `x::int` | | `CASE WHEN c THEN a ELSE b END` | `if c then a else b` | | `VALUES (1, 'a'), (2, 'b')` | `val t(id, name) = [[1, 'a'], [2, 'b']]` then `from t` | To convert an existing SQL query, run `wvlet to_wvlet ""`. For example, `wvlet to_wvlet "select status, count(*) as n from orders group by status having count(*) > 1"` prints: ```wvlet from orders group by status where count(*) > 1 select status, count(*) as n ``` ### How do I run Wvlet? Install the CLI with Homebrew (`brew install wvlet/wvlet/wvlet`) or download a release package; see [Installation](./usage/install.md). The CLI has two commands: - `wv`: an interactive shell (REPL). `wv -c "from 'data.csv' count"` runs one query and exits. - `wvlet`: batch commands. - `wvlet run "from 'data.parquet' limit 10"` runs a query; `wvlet run -f query.wv` runs a file. - `wvlet compile -f query.wv` prints the generated SQL; add `--target trino` (or another engine) to choose the SQL dialect. - `wvlet to_wvlet ""` converts SQL to Wvlet. Engines: - **DuckDB** is the default and needs no setup. File paths in `from '...'` are read by DuckDB. See [DuckDB](./usage/duckdb.md). - **Trino**, **Snowflake**, and others are configured as profiles in `~/.wvlet/profiles.json` and selected with `--profile `. See [Trino](./usage/trino.md) and [Connectors](./usage/connectors.md). - To check a query without a database, run `wvlet compile`: syntax errors are reported with the line and column. ### Common mistakes agents make | Mistake | Fix | |---------|-----| | Starting with `select ... from t` | Start with `from t`; put `select` at the end, or omit it. | | `==` or `<>` for comparison | Use `=` and `!=`. | | `HAVING` after `GROUP BY` | Use `where` after `agg`. | | `UNION` / `UNION ALL` | Use `concat { ... }`. Wvlet has no duplicate-eliminating `union`; add `dedup` if needed. | | `CASE ... END` | `case` has no `end`: `case when c then a else b as col`. Or use `if c then a else b`. | | `// comment` | `//` is integer division. Use `-- comment`. | | Double quotes for column names, as in SQL | `"x"` is a string literal. Use back quotes: `` `x` ``. | | `_.count_distinct(col)`, `_.max_by(a, b)` | Call the function on the column: `col.count_distinct`, `a.max_by(b)` (or SQL `count(distinct col)`, `max_by(a, b)`). | | Upper-case keywords (`FROM`, `WHERE`) | Keywords are lower-case: `from`, `where`. | | Rewriting the whole query to add a step | Append a line: another `where`, `add`, or `agg` after the existing ones. | ### How do I keep queries correct? - Put `test` assertions after a query in a `.wv` file, for example `test _.size should be 10` or `test _.columns should contain 'user_id'`. See [Test Syntax](./syntax/test-syntax.md). - Compile before running against a remote engine: `wvlet compile -f query.wv --target trino`. - Build long queries incrementally: run the first lines, check the output, then append the next operator. --- ## Language Bindings Wvlet provides SDKs for various programming languages, allowing you to compile Wvlet queries to SQL directly from your preferred language environment. ### Available SDKs #### [Python SDK](python.md) [![PyPI version](https://badge.fury.io/py/wvlet.svg)](https://pypi.org/project/wvlet/) The Python SDK provides native performance with easy installation via pip: ```bash pip install wvlet ``` ```python from wvlet import compile sql = compile("from users select name, age where age > 18") print(sql) # SELECT name, age FROM users WHERE age > 18 ``` **Features:** - Native library for high-performance compilation - Automatic CLI fallback - Support for all Wvlet features - Integration with pandas, DuckDB, SQLAlchemy #### [TypeScript/JavaScript SDK](typescript.md) [![npm version](https://badge.fury.io/js/wvlet.svg)](https://www.npmjs.com/package/wvlet) The TypeScript SDK enables Wvlet compilation in Node.js and browsers: ```bash npm install wvlet ``` ```typescript import { WvletCompiler } from 'wvlet'; const compiler = new WvletCompiler(); const sql = compiler.compile('from users select name, age where age > 18'); console.log(sql); // SELECT name, age FROM users WHERE age > 18 ``` **Features:** - Pure JavaScript via Scala.js compilation - Works in browsers and Node.js - Full TypeScript support - No native dependencies ### Planned SDKs The following language bindings are planned for future releases: - **Java/Scala** - Native JVM integration - **Ruby** - Ruby gem with native extensions - **Rust** - Cargo crate with native bindings - **Go** - Go module with CGO bindings - **C/C++** - Header-only library ### Native Library All language SDKs use the same underlying native library (`libwvlet`) for consistent behavior and performance across languages. The native library is built using Scala Native and provides a C-compatible API. #### Supported Platforms | Platform | x86_64 | ARM64 | |----------|--------|-------| | Linux | ✅ | ✅ | | macOS | ❌ | ✅ | | Windows | ✅ | ✅ | ✅ Supported | ❌ Not Supported ### Contributing We welcome contributions to add more language bindings! If you're interested in creating an SDK for your favorite language: 1. Check the [Python SDK implementation](https://github.com/wvlet/wvlet/tree/main/sdks/python) as a reference 2. Use the native library API defined in [WvcLib.scala](https://github.com/wvlet/wvlet/blob/main/wvc/src/main/scala/wvlet/lang/native/WvcLib.scala) 3. Follow the language's best practices for packaging and distribution 4. Submit a pull request with your implementation For questions or discussions about language bindings, please use [GitHub Discussions](https://github.com/wvlet/wvlet/discussions). --- ## Introduction Wvlet (pronounced as weave-let) is a new flow-style query language designed for SQL-based database engines, such as [DuckDB](https://duckdb.org/) and [Trino](https://trino.io/). ### Documentation Overview - [Installation](./usage/install) - [Usage](./usage/install.md) - [Query Syntax](./syntax/) - [Language Bindings](./bindings/) - [VS Code Extension](./usage/vscode.md) - [Development](./development/build.md) ### Why Wvlet? Wvlet queries (saved as .wv files) provide an intuitive way to describe data processing pipelines. These pipelines are eventually compiled into SQL query sequences. #### Traditional SQL Limitations SQL's syntax often does not match the semantic order of data processing. Here is an example cited from _[A Critique of Modern SQL And A Proposal Towards A Simple and Expressive Query Language (CIDR '24)](https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf)_:
![semantic-order](./img/sql-semantic-order.png)
In SQL, the syntactic order of `SELECT ...` statements does not align with the actual data flow inside SQL engines. Wvlet addresses this problem by starting queries with `from ...` (table scan statement), and then streamlining results through subsequent processing operators like `where`, `group by`, and `select`. This approach resembles passing table values through [a UNIX pipe](https://en.wikipedia.org/wiki/Pipeline_(Unix)): ```wvlet -- Starts from table scan from (table name) where column = 'filtering condition' ... where (more filtering conditions) -- Grouping records by keys group by key1, key2, ... -- HAVING clause after group by where (conditions for groups) -- Specify columns to output select c1, c2, ... order by (ordering columns...) ``` This flow-style syntax allows for creating complex queries in a natural order and adding adding operators for testing or debugging data in the middle of the query. Google's SQL has also adopted this flow-style by introducing the pipe syntax to simplify writing SQL queries: _[SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL (VLDB 2024)](https://research.google/pubs/sql-has-problems-we-can-fix-them-pipe-syntax-in-sql/)_. #### Modern SQL Challenges The SQL standard (e.g., SQL-92) lacks essential features for managing multiple queries, such as: - Reusing and generating queries - Multi-query optimization, like incremental processing and pipeline execution - Debugging or testing capabilities SQL was not designed for today's complex data analytics needs. Wvlet modernizes the [50-year-old SQL](https://dl.acm.org/doi/10.1145/3649887), making it more intuitive and functional while incorporating software engineering best practices. For more on Wvlet's motivation and design, see the presentation slides at Trino Summit 2024:
### Architecture The Wvlet compiler processes `.wv` query files to generate logical plans, execution plans, and SQL statements. You can specify the directory to scan for .wv files with `-w (workdir)` option. Key terminlogies include: - __Logical Plan__: Represents relational operators (e.g., scan, filter, projection) in a tree structure. - __Execution Plan__: Steps to execute SQL and other programs, supporting various data sources and processing engines. - [__Standard Library__](https://github.com/wvlet/wvlet/tree/main/wvlet-stdlib/module/standard): Provides common data type definitions with convenient operators while handling differences between SQL dialects. ![wvlet-architecture](./img/wvlet-architecture.svg) ### Features #### Flow-Style Query Wvlet queries are written in a flow-style, allowing for a natural description of data processing pipelines. For more details, see the following documents: - [Query Syntax](./syntax) - [Design Philosophy](./development/design) Example Wvlet Query: ```wvlet -- Starting with table scan from lineitem -- Filtering rows where l_shipdate >= '1994-01-01' -- Apply further filtering where l_shipdate < '1995-01-01' -- Grouping rows group by l_shipmode -- Processing grouped rows agg _.count as cnt ┌────────────┬──────┐ │ l_shipmode │ cnt │ │ string │ long │ ├────────────┼──────┤ │ AIR │ 1315 │ │ SHIP │ 1394 │ │ FOB │ 1382 │ │ REG AIR │ 1313 │ │ RAIL │ 1318 │ │ TRUCK │ 1396 │ │ MAIL │ 1366 │ ├────────────┴──────┤ │ 7 rows │ └───────────────────┘ ``` ##### Dual-Syntax Support: SQL and Wvlet Wvlet supports SQL syntax for compatibility with existing SQL queries. You can write SQL queries in `.sql` files and leverage the same capabilities as Wvlet queries. #### Column-Level Operators Wvlet provides column-level operators to process selected columns efficiently. Example: ```wvlet from lineitem -- Add a new column add l_quantity * l_extendedprice as revenue -- Rename a column rename l_shipdate as ship_date -- Exclude a column exclude l_comment -- Reorder columns shift l_orderkey, revenue limit 5; ┌────────────┬───────────────┬──────────────┬──────────────┬────────────┬────────────┬─> │ l_orderkey │ revenue │ l_returnflag │ l_linestatus │ l_shipmode │ ship_date │ > │ long │ decimal(18,4) │ string │ string │ string │ date │ > ├────────────┼───────────────┼──────────────┼──────────────┼────────────┼────────────┼─> │ 1 │ 420075.9500 │ N │ O │ TRUCK │ 1996-03-13 │ > │ 1 │ 2040772.3200 │ N │ O │ MAIL │ 1996-04-12 │ > │ 1 │ 98408.3200 │ N │ O │ REG AIR │ 1996-01-29 │ > │ 1 │ 722863.6800 │ N │ O │ AIR │ 1996-04-21 │ > │ 1 │ 657354.2400 │ N │ O │ FOB │ 1996-03-30 │ > ├────────────┴───────────────┴──────────────┴──────────────┴────────────┴────────────┴─> │ 5 rows > └──────────────────────────────────────────────────────────────────────────────────────> ``` If you write the same query in SQL, all column names must be enumerated in each query stage: ```sql select * from (select l_orderkey, revenue, l_returnflag, l_linestatus, l_shipmode, ship_date, l_shipinstruct, l_quantity, l_tax, l_suppkey, l_receiptdate, l_linenumber, l_extendedprice, l_partkey, l_discount, l_commitdate from (select l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, ship_date, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, revenue from (select l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate as ship_date, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment, revenue from (select *, l_quantity * l_extendedprice as revenue from lineitem)))) limit 5 ``` #### Chaining Function Calls Wvlet supports dot-notation for chaining function calls. Example: ```wvlet from lineitem group by l_shipmode agg _.count, l_quantity.sum.round(0); ┌────────────┬──────────────┬───────────────────────────┐ │ l_shipmode │ count_star() │ round(sum(l_quantity), 0) │ │ string │ long │ decimal(38,0) │ ├────────────┼──────────────┼───────────────────────────┤ │ FOB │ 8641 │ 219565 │ │ SHIP │ 8482 │ 217969 │ │ TRUCK │ 8710 │ 223909 │ │ AIR │ 8491 │ 216331 │ │ REG AIR │ 8616 │ 219015 │ │ MAIL │ 8669 │ 221528 │ │ RAIL │ 8566 │ 217810 │ ├────────────┴──────────────┴───────────────────────────┤ │ 7 rows │ └───────────────────────────────────────────────────────┘ ``` In SQL, every function call needs to be nested with parenthesises, which require more cursor and eye movements to write the query: ```sql -- You need to wrap each function call with parentheses (More cursor movements!) select l_shipmode, count(*), round(sum(l_quantity), 0); from lineitem group by l_shipmode ``` #### Functional Data Modeling Define reusable data model functions. Example: ```wvlet model lookup(person_id: int) = { from persons where id = ${person_id} } ``` Calling this model: ```wvlet from lookup(1) -- In the compiled SQL, ${person_id} will be replaced with the given input 1: select * from persons where id = 1 ``` Composing the model with another table: ```wvlet from lookup(1) as p join address_table on p.id = address_table.person_id ``` #### Flow Orchestration (Experimental) Organize queries into data pipelines with `flow` and `stage` constructs, including retries, timeouts, scheduling, sensors, and failure notifications: ```wvlet flow daily_report with { schedule: cron('0 2 * * *') -- 2 AM daily concurrency: 1 } = { stage extract = from raw_events | where event_date = current_date stage load = from extract | append to daily_summary stage fallback if extract.failed = from backup_events | append to daily_summary } ``` Flow orchestration is experimental — see [Flow & Stage Syntax](./syntax/flow) for details, and share syntax feedback on [this tracking issue](https://github.com/wvlet/wvlet/issues/2021). #### Managing Queries As A Reusable Module :::warning This feature will be available in 2025. ::: Queries saved as `.wv` files can be managed in local folders or GitHub repositories as modules. Import and reuse queries in other queries. Example: ```wvlet -- import queries from a GitHub repository import github.com/my_project/my_query_library -- Call a query from the imported module from my_query(p1, p2, ...) ``` #### Incremental Processing :::warning Incremental processing feature is planned to be available in 2025. ::: Build reproducible data processing pipelines with incremental processing. Example: ```wvlet @config(watermark_column='time', window_size='1h') model weblog_records = { from upstream_web_records select time, user_id, path, } -- Subscribe newly inserted records in weblog_records -- and save filtered records to downstream_web_records from weblog_records.subscribe() where user_id is not null append to downstream_web_records ``` This query compiles into SQL to update the downstream_web_records table: ```sql create table if not exists as downstream_web_records; insert into downstream_web_records select time, user_id, path from upstrem_weblog_records where -- incremental processing time > ${last_watermark} and time <= ${last_watermark} + interval '1 hour' -- the original filter to the subscribed data and user_id is not null; ``` If supported, modern table formats like Iceberg and DuckLake are used for optimized processing. --- ## Query Syntax Reference Wvlet is a query language designed to be more human-readable and easier to write than SQL. If you are familiar to SQL, you will find it easy to learn Wvlet syntax due to the many similarities. If you know about [DataFrame in Python](https://pandas.pydata.org/docs/user_guide), it will help you understand the Wvlet query language, as chaining relational operators in flow-style is similar to using the DataFrame API. :::tip Getting Started New to Wvlet? Check out the [Quick Start](./quick-start.md) tutorial for a hands-on introduction. ::: ### Quick Navigation - [Operator Quick Reference](#operator-quick-reference) - [Data Source Operators](#data-source-operators) - [Filtering & Selection](#filtering--selection) - [Column Operations](#column-operations) - [Aggregation & Grouping](#aggregation--grouping) - [Joining & Combining Data](#joining--combining-data) - [Sorting & Transformation](#sorting--transformation) - [Advanced Operations](#advanced-operations) - [Update Operations](#update-operations) - [Table Management](#table-management) - [Comments](#comments) - [Expressions](#expressions) - [Common Query Patterns](#common-query-patterns) ### Operator Quick Reference #### Core Operators at a Glance | Operator | Description | SQL Equivalent | Example | |----------|-------------|----------------|----------| | **Data Sources** | | | | | `from` | Read data from table/file | `SELECT * FROM` | `from customers` | | `with` | Define reusable subquery | `WITH ... AS` | `with t as { from users }` | | `show tables` | List available tables | `SHOW TABLES` | `show tables` | | **Filtering & Selection** | | | | | `where` | Filter rows | `WHERE` | `where age > 21` | | `select` | Choose columns | `SELECT` | `select name, age` | | `limit` | Limit row count | `LIMIT` | `limit 10` | | `sample` | Random sampling | `TABLESAMPLE` | `sample 1000` | | **Column Operations** | | | | | `add` | Add new columns | `SELECT *, expr AS` | `add price * 0.9 as discounted` | | `exclude` | Remove columns | - | `exclude password` | | `rename` | Rename columns | `AS` | `rename user_id as id` | | `shift` | Reorder columns | - | `shift name, email` | | **Aggregation** | | | | | `group by` | Group rows | `GROUP BY` | `group by category` | | `agg` | Add aggregations | `SELECT agg_func()` | `agg _.count, revenue.sum` | | `pivot` | Pivot table | `PIVOT` | `pivot on month` | | **Joining Data** | | | | | `join` | Inner join | `JOIN` | `join orders on id = user_id` | | `left join` | Left outer join | `LEFT JOIN` | `left join orders on ...` | | `concat` | Union all | `UNION ALL` | `concat { from archive }` | | **Transformation** | | | | | `order by` | Sort rows | `ORDER BY` | `order by created_at desc` | | `dedup` | Remove duplicates | `DISTINCT` | `dedup` | | `unnest` | Expand arrays | `UNNEST` | `unnest(tags)` | #### Typical Flow of a Wvlet Query Wvlet queries start with the `from` keyword, and you can chain multiple relational operators to process the input data and generate output data. Here's a typical flow: ```wvlet from ... -- Scan the input data where ... -- Apply filtering conditions where ... -- [optional] Apply more filtering conditions add ... as ... -- Add new columns group by ... -- Group rows by the given columns agg ... -- Add group aggregation expressions, e.g., _.count, amount.sum where ... -- Apply filtering conditions for groups (HAVING clause in SQL) exclude ... -- Remove columns from the output shift ... -- Shift the column position to the left select ... -- Select columns to output order by ... -- Sort the rows by the given columns limit ... -- Limit the number of rows to output ``` Unlike SQL, whose queries always must follow the `SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... LIMIT ...` structure, Wvlet uses the flow-style syntax to match the order of data processing order as much as possible, facilitating more intuitive query writing. A query should have a `from` statement to read the data, but `select` is not mandatory in Wvlet. ### Relational Operators In Wvlet, you need to use __lower-case__ keywords for SQL-like operators. Operators are organized by their primary function to help you find what you need quickly. ### Data Source Operators These operators help you access and explore data sources in your environment. #### Catalog Discovery | Operator | Description | SQL Equivalent | |----------|-------------|----------------| | [__show__ tables](#show-tables) | List all tables in current schema | `SHOW TABLES` | | [__show__ tables __in__ `schema`](#show-tables) | List tables in specific schema | `SHOW TABLES IN schema` | | [__show__ schemas](#show-schemas) | List all schemas in catalog | `SHOW SCHEMAS` | | [__show__ schemas __in__ `catalog`](#show-schemas) | List schemas in specific catalog | `SHOW SCHEMAS IN catalog` | | [__show__ catalogs](#show-catalogs) | List all available catalogs | `SHOW CATALOGS` | | __show__ models | List all available models | - | | __describe__ `table` | Show table/query schema | `DESCRIBE table` | #### Reading Data | Operator | Description | SQL Equivalent | |----------|-------------|----------------| | [__from__ `table`](#from) | Read from table | `SELECT * FROM table` | | [__from__ `'file.parquet'`](#from) | Read from file | - | | [__from__ `[[...]]`](#from) | Read from inline values | `VALUES (...)` | | [__with__ `alias` __as__ { `query` }](#with) | Define reusable subquery | `WITH alias AS (query)` | | __from__ sql"..." | Execute raw SQL | - | ### Filtering & Selection These operators help you filter rows and select specific columns from your data. | Operator | Description | SQL Equivalent | |----------|-------------|----------------| | [__where__ `condition`](#where) | Filter rows by condition | `WHERE condition` | | [__select__ `col1`, `col2`](#select) | Select specific columns | `SELECT col1, col2` | | __select__ * | Select all columns | `SELECT *` | | __select distinct__ `cols` | Select unique values | `SELECT DISTINCT cols` | | [__limit__ `n`](#limit) | Limit to n rows | `LIMIT n` | | [__sample__ `n`](#sample) | Random sample of n rows | `TABLESAMPLE` | | [__sample__ `n%`](#sample) | Random sample percentage | `TABLESAMPLE n PERCENT` | | __exists__ { `subquery` } | Check if subquery has results | `EXISTS (subquery)` | ### Column Operations These operators help you manipulate columns without changing the number of rows. | Operator | Description | SQL Equivalent | |----------|-------------|----------------| | [__add__ `expr` __as__ `name`](#add) | Add column to the right | `SELECT *, expr AS name` | | [__prepend__ `expr` __as__ `name`](#prepend) | Add column to the left | - | | [__exclude__ `col1`, `col2`](#exclude) | Remove specific columns | - | | [__rename__ `old` __as__ `new`](#rename) | Rename columns | `old AS new` | | [__shift__ `col1`, `col2`](#shift) | Move columns to left | - | | __shift to right__ `cols` | Move columns to right | - | ### Aggregation & Grouping These operators help you aggregate data and perform group operations. | Operator | Description | SQL Equivalent | |----------|-------------|----------------| | [__group by__ `cols`](#group-by) | Group rows by columns | `GROUP BY cols` | | [__agg__ `expr1`, `expr2`](#agg) | Add aggregation expressions | `SELECT agg_func(...)` | | [__count__](#count) | Count all rows | `SELECT COUNT(*)` | | [__pivot on__ `col`](#pivot) | Pivot table on column values | `PIVOT` | | [__unpivot__ `val` __for__ `col` __in__ (...)](#unpivot) | Unpivot columns to rows | `UNPIVOT` | #### Common Aggregation Functions When using `agg` after `group by`, you can use these aggregation functions: - `_.count` - Count rows in group - `column.sum` - Sum of column values - `column.avg` - Average of column values - `column.min` / `column.max` - Min/max values - `column.count_distinct` - Count distinct values - `column.to_array` - Collect values into an array - `column.max_by(other)` / `column.min_by(other)` - Value of `column` at the max/min of `other` ### Joining & Combining Data These operators help you combine data from multiple sources. | Operator | Description | SQL Equivalent | |----------|-------------|----------------| | [__join__ `table` __on__ `condition`](#join) | Inner join | `JOIN table ON condition` | | [__left join__ `table` __on__ `condition`](#join) | Left outer join | `LEFT JOIN table ON condition` | | __right join__ `table` __on__ `condition` | Right outer join | `RIGHT JOIN table ON condition` | | __cross join__ `table` | Cartesian product | `CROSS JOIN table` | | [__asof join__](asof-join.md) | Time-based join | - | | [__concat__ { `query` }](#concat) | Union all results | `UNION ALL` | | [__intersect__ { `query` }](#intersect) | Set intersection | `INTERSECT` | | [__except__ { `query` }](#except) | Set difference | `EXCEPT` | ### Sorting & Transformation These operators help you sort and transform your data. | Operator | Description | SQL Equivalent | |----------|-------------|----------------| | [__order by__ `col` __desc__](#order-by) | Sort descending | `ORDER BY col DESC` | | [__order by__ `col` __asc__](#order-by) | Sort ascending | `ORDER BY col ASC` | | [__dedup__](#dedup) | Remove duplicate rows | `SELECT DISTINCT *` | | [__unnest__(`array_col`)](unnest.md) | Expand array to rows | `UNNEST(array_col)` | ### Advanced Operations These operators provide advanced functionality for testing and debugging. | Operator | Description | Use Case | |----------|-------------|----------| | [__test__ `condition`](test-syntax.md) | Assert test conditions | Testing queries | | __debug__ { `query` } | Debug intermediate results | Debugging | | __\|__ `function(args)` | Apply table function | Custom transformations | ### Update Operations These operators allow you to save or modify data. Each also has a block form that leads with the effect (`save to t { query }`); see [Managing Tables and Schemas](table-management.md). | Operator | Description | Notes | |----------|-------------|-------| | __save to__ `table` | Create new table | Overwrites if exists | | __save to__ `table` __if not exists__ | Seed a table once | No-op when the table exists | | __save to__ `'file.parquet'` | Save to file | Supports various formats | | __save as view__ `name` | Create or replace a view | Exports the query to SQL consumers | | __append to__ `table` | Append to existing table | Creates the table if missing (declared shape wins) | | __append to__ `table` __on__ `keys` | Insert-or-update by key | Lowered to `MERGE INTO` | | __update__ `col = expr, ...` | Update matching rows | From source table, like `delete` | | __delete__ | Delete matching rows | From source table | ### Table Management Native statements for schemas, tables, and views — see [Managing Tables and Schemas](table-management.md) for the full guide. | Statement | Description | |-----------|-------------| | `table users = { id: int, ... }` | Declare a table shape (side-effect-free; used for type checking and auto-creation) | | `trait ip_address extends string = { def ... }` | Declare a method interface attached to a type — see [Data Models](data-models.md) | | `table backup like users` | Declare a second table with the same columns | | `create schema s [in 'uri'] [if not exists] [with k: v]` / `drop schema s [if exists] [with cascade: true]` | Schema lifecycle, locations, and engine options | | `create table t` / `... if not exists` / `create or replace table t` | SQL-equivalent table creation from a declaration | | `drop table t [if exists]` / `truncate t` | Remove a table / delete all rows | | `reshape t { add c: type / rename a as b / exclude c }` | Evolve a table's columns | | `rename table a to b` / `rename schema a to b` | Rename catalog objects | | `drop view v [if exists]` | Remove a view | | `use 'file.duckdb' as name` | Attach an external database under an alias | ### Inspection Commands These commands help you understand query structure and data. | Operator | Description | Use Case | |----------|-------------|----------| | __describe__ `query` | Show query schema | Understanding output columns | | __explain__ `query` | Show logical plan | Query optimization | | __explain__ sql"..." | Explain raw SQL | SQL debugging | ### Comments Wvlet supports two types of comments for documenting your queries: #### Single-line Comments (`--`) Use double hyphens (`--`) for single-line comments. Everything after `--` on the same line is treated as a comment and ignored during query execution: ```wvlet -- This is a single-line comment from customers where age > 18 -- Filter for adults only -- Another comment select name, email ``` #### Documentation Comments (`---`) Use triple hyphens (`---`) for documentation comments, especially for documenting models. These comments support Markdown formatting and are used for generating documentation: ```wvlet --- ## Customer Analysis Model This model provides filtered customer data for analytics: - Includes only active customers - Excludes test accounts - Adds calculated fields for analysis Example usage: from customer_analysis where signup_date >= '2024-01-01':date --- model customer_analysis = { from customers where status = 'active' where email not like '%test%' add current_date - signup_date as days_since_signup } ``` Documentation comments are particularly useful for: - Describing the purpose and usage of models - Providing examples of how to use the model - Documenting data transformations and business logic - Adding metadata for team collaboration ### Expressions One of the major difference from traditional SQL is that wvlet uses single or double quoted strings for representing string values and back-quoted strings for referencing column or table names, which might contain space or special characters. | Operator | Description | |:------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------| | 'single quote' | String literal for representing string values, file names, etc. | | "double quote" | Same as single quote strings | | """triple quote string""" | Multi-line strings | | \`(back quote)\` | Column or table name, which requires quotations | | __sql__"`sql expr`" | SQL expression used for inline expansion | | __sql__" ... $\{`expr`\} ..." | Interpolated SQL expression with embedded expressions | | __s__"... $\{expr\} ..." | Interpolated strings with expressions | | __s__\`... $\{expr\} ...\` | Interpolated backquote strings with expressions | | [`expr`, ...] | Array value | | [[`expr`, ...], ...] | Array of arrays for representing table records | | \{`key`\: `value`, ...\} | Struct (row) value | | `_` | Underscore refers to the previous input | | `agg_func(expr)` over (partition by ... order by ...) | [Window functions](window.md) for computing aggregate values computed from the entire query result. This follows similar window function syntax with SQL | | `_1`, `_2`, ... | Refers to 1-origin grouping keys in the preceding `group by` clause | | `1`, `2`, ... | Refers to 1-origin column index for `order by` clause | | `expr`::`type` | Type cast the value to the target type. Equivalent to cast(`expr` as `type`) in SQL | | '2025-01-01'::date | Cast the string to a date value | | '1 year':interval, '30 days':interval | Cast the string to an interval value. Supports units: day, days, hour, hours, minute, minutes, second, seconds, month, months, year, years, etc. | :::note Type Annotations vs Type Casting Single colon `:` is used for **type annotations** in declarations (e.g., `param: int`, `val name: string`), while double colon `::` is used for **runtime type casting** (e.g., `value::int`, `'2025-01-01'::date`). ::: #### Variable Definition You can define a variable using `val` keyword: ```wvlet -- Define a new variable val name = 'wvlet' -- Variable is evaluated once before running the query select s"Hello ${x}!" as msg -- Returns [['Hello wvlet!']] ``` ##### Table Variable Constants You can also define table constants using the `val` keyword with column names and data: ```wvlet -- Basic table value constant val products(id, name, price) = [ [1, "Laptop", 999.99], [2, "Mouse", 29.99], [3, "Keyboard", 79.99], ] from products where _.price > 50 -- Returns [[1, "Laptop", 999.99], [3, "Keyboard", 79.99]] ``` You can specify type annotations for table columns: ```wvlet -- With type annotations val users(id:int, name:string, active:boolean) = [ [1, "Alice", true], [2, "Bob", false], ] from users where active = true -- Returns [[1, "Alice", true]] ``` This syntax allows you to define inline data tables that can be referenced in your queries, which is particularly useful for testing, small lookup tables, or providing sample data. #### For Loops A `for` loop repeats a block of statements once per value. Use it when the same steps apply to several tables, dates, or tenants, instead of copying the block: ```wvlet for d in ['2026_09_01', '2026_09_02', '2026_09_03'] { from s`events_${d}` where status = 'ok' group by user_id agg _.count as cnt add d as dt append to daily_summary } ``` The loop variable behaves like a `val` that is visible only inside the loop body. You can use it in expressions, in `s"..."` strings, and in backquote-interpolated table names, including `save to` and `append to` targets. The values can come from an array, a `val` holding an array, an array computed by the database engine such as `range(1, 4)` (integers from 1 up to, but not including, 4), or a parenthesized query. A computed array or a query is evaluated once before the loop starts. For a single-column query, the loop variable is bound to each value. For a query with several columns, it is bound to each row, and the body reads the columns as fields (`p.dt`): ```wvlet val regions = ['us', 'eu', 'jp'] for r in regions { from sales where region = r save to s`sales_${r}` } -- Iterate over a range of integers: 1, 2, 3 for i in range(1, 4) { from s`shard_${i}` append to all_shards } -- Iterate over values discovered at run time for t in (from tenants where active select tenant_id order by tenant_id) { from s`tenant_${t}`.orders agg _.count as orders add t as tenant_id append to order_counts } -- Iterate over rows: each column is a field of the loop variable for p in (from partitions select dt, region) { from events where dt = p.dt and region = p.region save to s`events_${p.dt}_${p.region}` } ``` Iterations run one after another in order, and an error stops the loop. The body can contain any statements, including `test` assertions (checked on every iteration; expected values must be constants, not the loop variable) and nested `for` loops. To transform the rows of a table, use relational operators such as `select` and `add` instead — a `for` loop repeats statements, not rows. #### Conditional Expressions | Operator | Description | |--------------------------------------|----------------------------------------------------------------------------------| | `expr` __and__ `expr` | Logical AND | | `expr` __or__ `expr` | Logical OR | | __not__ `expr` | Logical NOT | | !`expr` | Logical NOT | | `expr` __is__ `expr` | Equality check | | `expr` __=__ `expr` | Equality check | | `expr` __is not__ `expr` | Inequality check | | `expr` __!=__ `expr` | Inequality check | | `expr` __is null__ | True if the expression is null | | `expr` __= null__ | True if the expression is null | | `expr` __is not null__ | True if the expression is not null | | `expr` __!= null__ | True if the expression is not null | | `expr` __in__ (`v1`, `v2`, ...) | True if the expression value is in the given list | | `expr` __in__ \{ from ... \} | True if the expression value is in the given list provided by a sub query | | `expr` __not in__ (`v1`, `v2`, ...) | True if the expression is not in the given list | | `expr` __between__ `v1` __and__ `v2` | True if the expression value is between v1 and v2, i.e., v1 ≤ (value) ≤ v2 | | `expr` __like__ `pattern` | True if the expression matches the given pattern, e.g., `'abc%'` | | `expr` __like__ `pattern` __escape__ `char` | Like with escape character for escaping wildcards, e.g., `'ab\_c' escape '\'` | | `expr` __not like__ `pattern` | True if the expression does not match the given pattern | | `expr` __not like__ `pattern` __escape__ `char` | Not like with escape character for escaping wildcards | | __exists__ \{ from ... \} | True if the subquery returns any rows | | __not exists__ \{ from ... \} | True if the subquery returns no rows | ##### If Expression `if ... then .. else` expression can be used to switch the output value based on the condition: ```wvlet from lineitem select if l_returnflag = 'A' then 1 else 0 as return_code ``` The `if` expression can be nested as follows: ```wvlet from lineitem select if l_returnflag = 'A' then 1 else if l_returnflag = 'R' then 2 else 0 as return_code ``` ##### Case Expression To switch the output value based on the input value, you can use the `case` expression: ```wvlet from lineitem select case l_returnflag when 'A' then 1 when 'R' then 2 else 0 as return_code ``` You can also use the `case` expression with conditional expressions for clarity: ```wvlet from lineitem select case when l_returnflag = 'A' then 1 when l_returnflag = 'R' then 2 else 0 as return_code ``` :::tip Unlike SQL, Wvlet doesn't require `end` at the end of case expressions. ::: #### String Expressions | Operator | Description | |-----------------|-------------------------| | `expr` __+__ `expr` | Concatenate two strings | #### Array Expressions You can construct array values with `[e1, e2, ...]` syntax: ```wvlet select ['a', 'b', 'c'] as arr ``` Arrays can be accessed with index (1-origin): ```wvlet select ['a', 'b', 'c'] as arr select arr[1] as first_element ``` #### Map Expressions You can construct map values with `map {k1: v1, k2: v2, ...}` syntax. Unlike struct expressions, keys (k1, k2, ...) needs to be the same type values, and values (v1, v2, ...) also need to be the same type values: ```wvlet select map {'a': 1, 'b': 2} as m ``` #### Struct/Row Expressions Struct (row) expressions are used to represent key-value pairs. You can access the values by name: ```wvlet select {'i': 3, 's': 'str'} as obj select -- Name based access obj.i, obj.s, -- Lookup by name obj['i'], obj['s'] ``` Note that key names in a struct do not require quotes for plain key names: ```wvlet select {k1:1, k2:'v1'} as obj select obj.k1, obj.k2 ``` #### Lambda Expression To manipulate array values, you can use lambda expressions: ```wvlet select list_transform([4, 5, 6], x -> x + 1) ``` This query applies the lambda function `x -> x + 1` to each element of the input array: ``` ┌────────────┐ │ arr │ │ array(int) │ ├────────────┤ │ [5, 6, 7] │ ├────────────┤ │ 1 rows │ └────────────┘ ``` To pass multiple arguments to the lambda function, use the following syntax: ```wvlet select list_reduce([4, 5, 6], (a, b) -> a + b) as sum; ┌─────┐ │ sum │ │ int │ ├─────┤ │ 15 │ ├─────┤ │ 1 … │ └─────┘ ``` Lambda functions can be nested as follows: ```wvlet select list_transform( [1, 2, 3], x -> list_reduce([4, 5, 6], (a, b) -> a + b) + x ) as arr; ┌──────────────┐ │ arr │ │ array(int) │ ├──────────────┤ │ [16, 17, 18] │ ├──────────────┤ │ 1 rows │ └──────────────┘ ``` ### Examples #### show tables To find a target table to query in the current database (schema), you can use the `show tables` query: ```wvlet show tables ``` The output of `show tables` can be followed by the `where` clause to filter the table names: ```wvlet show tables where name like 'n%'; ┌────────┐ │ name │ │ string │ ├────────┤ │ nation │ ├────────┤ │ 1 rows │ └────────┘ ``` #### show schemas To find a target database (schema) to query in the current catalog, you can use the `show schemas` query: ```wvlet show schemas; ┌─────────┬────────────────────┐ │ catalog │ name │ │ string │ string │ ├─────────┼────────────────────┤ │ memory │ information_schema │ │ system │ information_schema │ │ temp │ information_schema │ │ memory │ main │ │ system │ main │ │ temp │ main │ │ memory │ pg_catalog │ │ system │ pg_catalog │ │ temp │ pg_catalog │ ├─────────┴────────────────────┤ │ 9 rows │ └──────────────────────────────┘ ``` These schema names can be used to specify the target schema in the `show tables` command: ```wvlet show tables in main where name like 'p%'; ┌──────────┐ │ name │ │ string │ ├──────────┤ │ part │ │ partsupp │ ├──────────┤ │ 2 rows │ └──────────┘ ``` #### show catalogs List all available catalogs: ```wvlet show catalogs; ┌────────┐ │ name │ │ string │ ├────────┤ │ memory │ │ system │ │ temp │ ├────────┤ │ 3 rows │ └────────┘ ``` #### from Read rows from a given table: ```wvlet from nation ``` In DuckDB backend, you can read data from local Parquet, CSV/TSV, JSON, or newline-delimited JSON (`.jsonl`, `.ndjson`) files. CSV and JSON files may be gzip (`.gz`) or zstd (`.zst`) compressed; the format is detected from the file extension: ```wvlet from 'sample.parquet' from 'events.jsonl.gz' from 'users.csv.zst' ``` Or from files on the Web: ```wvlet from 'https://(path to your data)/sample.parquet' ``` Reading data from an URL will also work for S3 Presigned URLs. The column names and types of a file are inferred when the query is compiled. For Parquet and CSV files, DuckDB reads only the metadata it needs. For a JSON file containing an array of records, or a JSON Lines file, the schema is inferred from the first 20,480 records (the same sample size DuckDB uses), so a field that first appears after that point will not be part of the inferred schema. Inferred schemas are cached while the file is unchanged, so repeated compiles of the same query do not re-read the file. ##### Raw SQL statements In case you need to use a raw SQL statement, you can use the `sql` string interpolation: ```wvlet from sql"select * from nation" ``` :::warning Although raw SQL statements are convenient, Wvlet needs to issue a real query to the target DBMS to inspect the data types of the query result, which might slowdown the query or cause errors in the subsequent relational operators. ::: #### where You can apply a filter condition to the input rows: ```wvlet from nation where n_regionkey = 1 ``` Unlike SQL, applying multiple `where` clauses is allowed in Wvlet: ```wvlet from nation where n_regionkey = 1 where n_name like 'U%' ``` This is useful even when you need to generate Wvlet queries programatically as you only need to append new condition lines to the query. In SQL, you need to parse the text inside WHERE clause and concatenate condition with AND expression. #### count Wvlet has a shorthand notation for counting the number of input rows: ```wvlet from nation count ``` This is equilvalen to: ```wvlet from nation select _.count ``` Or more SQL-like expression can be used too: ```wvlet from nation select count(*) ``` For clarity, you can use pipe `|` before the count operator: ```wvlet from nation select n_name, n_regionkey, -- pipe (|) is required after trailing comma | count ``` #### add Add a new column to the input. This operator is helpful to reduce the typing effort as you don't need to enumerate all the columns in the `select` clause: ```wvlet from nation select n_name add n_name.substring(1, 3) as prefix limit 5; ┌───────────┬────────┐ │ n_name │ prefix │ │ string │ string │ ├───────────┼────────┤ │ ALGERIA │ ALG │ │ ARGENTINA │ ARG │ │ BRAZIL │ BRA │ │ CANADA │ CAN │ │ EGYPT │ EGY │ ├───────────┴────────┤ │ 5 rows │ └────────────────────┘ ``` #### prepend Prepend a new column to the left side of the input: ```wvlet from nation select n_name prepend n_name.substring(1, 3) as prefix limit 5; ┌────────┬───────────┐ │ prefix │ n_name │ │ string │ string │ ├────────┼───────────┤ │ ALG │ ALGERIA │ │ ARG │ ARGENTINA │ │ BRA │ BRAZIL │ │ CAN │ CANADA │ │ EGY │ EGYPT │ ├────────┴───────────┤ │ 5 rows │ └────────────────────┘ ``` #### select Output only the selected columns from the input: ```wvlet from nation select n_name, n_regionkey ``` As in SQL, arbitrary expressions can be used in select statements. Note that, however, in Wvlet, you usually don't need to use select statement as the other column-at-a-time operators, like `add`, `exclude`, `shift`, etc. can be used for manipulating column values. #### rename The `rename` operator changes the column names in the input rows. This is useful when you want to rename columns in the output. ```wvlet from nation rename n_name as nation_name ``` #### exclude The `exclude` operator removes specified columns from the input rows. This is useful when you want to drop certain columns from the output. ```wvlet from nation exclude n_regionkey ``` #### shift The `shift` operator changes the position of specified columns in the input rows. You can move columns to the left or right. Shift to the left (default): ```wvlet from nation shift n_name ``` Shift to the right: ```wvlet from nation shift to right n_comment ``` #### group by The `group by` operator groups rows by one or more columns, creating groups of rows that share the same values in the specified columns. ##### Basic Grouping ```wvlet val data(id, category, value) = [ [1, "A", 100], [2, "B", 200], [1, "A", 150], [2, "B", 50], [3, "C", 300] ] from data group by id, category; ┌─────┬──────────┬───────┐ │ id │ category │ value │ │ int │ string │ int │ ├─────┼──────────┼───────┤ │ 1 │ A │ 100 │ │ 2 │ B │ 200 │ │ 3 │ C │ 300 │ ├─────┴──────────┴───────┤ │ 3 rows │ └────────────────────────┘ ``` :::note In SQL, you need to specify aggregation operators like `count`, `sum`, `avg`, etc. to generate output from `GROUP BY`. In Wvlet, the default aggregation operator `arbitrary (any)` is used, which returns an arbitrary value from each group. ::: ##### Referencing Grouping Keys After grouping, you can reference grouping keys by their column names directly: ```wvlet val data(id, category, value) = [ [1, "A", 100], [2, "B", 200], [1, "A", 150], [2, "B", 50] ] from data group by id, category select id, category; ┌─────┬──────────┐ │ id │ category │ │ int │ string │ ├─────┼──────────┤ │ 1 │ A │ │ 2 │ B │ ├─────┴──────────┤ │ 2 rows │ └────────────────┘ ``` :::tip While you can also reference grouping keys using `_1`, `_2`, etc. in the order they appear in the `group by` clause, it's recommended to use column names for better readability and maintainability. ::: #### agg The `agg` operator adds aggregation expressions. It is typically used after a `group by` clause to aggregate data within groups, but can also be used without `group by` to aggregate all rows in the input. ##### Basic Aggregations ```wvlet val data(id, category, value) = [ [1, "A", 100], [2, "B", 200], [1, "A", 150], [2, "B", 50], [3, "C", 300] ] from data group by category agg _.count as item_count, value.sum as total_value, value.avg as avg_value; ┌──────────┬────────────┬─────────────┬───────────────┐ │ category │ item_count │ total_value │ avg_value │ │ string │ long │ int │ decimal(17,4) │ ├──────────┼────────────┼─────────────┼───────────────┤ │ A │ 2 │ 250 │ 125.0000 │ │ B │ 2 │ 250 │ 125.0000 │ │ C │ 1 │ 300 │ 300.0000 │ ├──────────┴────────────┴─────────────┴───────────────┤ │ 3 rows │ └─────────────────────────────────────────────────────┘ ``` ##### Available Aggregation Functions The underscore `_` represents the group of rows and supports various aggregation functions: ```wvlet val sales(store_id, amount, product) = [ [1, 10, "A"], [2, 20, "B"], [1, 30, "C"], [2, 40, "D"], [1, 50, "E"] ] from sales group by store_id agg _.count as transaction_count, amount.sum as total_sales, amount.avg as avg_sale, amount.min as min_sale, amount.max as max_sale, product.count_distinct as unique_products, product.max_by(amount) as best_product, product.min_by(amount) as worst_product; ┌──────────┬───────────────────┬─────────────┬───────────────┬──────────┬──────────┬─────────────────┬──────────────┬───────────────┐ │ store_id │ transaction_count │ total_sales │ avg_sale │ min_sale │ max_sale │ unique_products │ best_product │ worst_product │ │ int │ long │ int │ decimal(17,4) │ int │ int │ long │ string │ string │ ├──────────┼───────────────────┼─────────────┼───────────────┼──────────┼──────────┼─────────────────┼──────────────┼───────────────┤ │ 1 │ 3 │ 90 │ 30.0000 │ 10 │ 50 │ 3 │ E │ A │ │ 2 │ 2 │ 60 │ 30.0000 │ 20 │ 40 │ 2 │ D │ B │ ├──────────┴───────────────────┴─────────────┴───────────────┴──────────┴──────────┴─────────────────┴──────────────┴───────────────┤ │ 2 rows │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ ``` ##### Filtering Groups (HAVING clause) You can filter groups after aggregation using a `where` clause after `agg`: ```wvlet val orders(id, category, amount) = [ [1, "Electronics", 1000], [2, "Books", 50], [3, "Electronics", 2000], [4, "Books", 75], [5, "Toys", 500], [6, "Electronics", 1500] ] from orders group by category agg _.count as order_count, amount.sum as total_amount where total_amount > 1000 order by total_amount desc; ┌─────────────┬─────────────┬──────────────┐ │ category │ order_count │ total_amount │ │ string │ long │ int │ ├─────────────┼─────────────┼──────────────┤ │ Electronics │ 3 │ 4500 │ ├─────────────┴─────────────┴──────────────┤ │ 1 rows │ └──────────────────────────────────────────┘ ``` ##### Multiple Grouping with Complex Aggregations ```wvlet val sales(date, store, category, amount) = [ ["2024-01-01", "A", "Electronics", 100], ["2024-01-01", "B", "Books", 50], ["2024-01-02", "A", "Electronics", 200], ["2024-01-02", "A", "Books", 75], ["2024-01-03", "B", "Electronics", 150] ] from sales group by date, store agg _.count as transactions, amount.sum as daily_total, category.to_array as categories_sold, amount.to_array as amounts; ┌────────────┬───────┬──────────────┬─────────────┬───────────────────────┬───────────────┐ │ date │ store │ transactions │ daily_total │ categories_sold │ amounts │ │ string │ string│ long │ int │ array(string) │ array(int) │ ├────────────┼───────┼──────────────┼─────────────┼───────────────────────┼───────────────┤ │ 2024-01-01 │ A │ 1 │ 100 │ [Electronics] │ [100] │ │ 2024-01-01 │ B │ 1 │ 50 │ [Books] │ [50] │ │ 2024-01-02 │ A │ 2 │ 275 │ [Electronics, Books] │ [200, 75] │ │ 2024-01-03 │ B │ 1 │ 150 │ [Electronics] │ [150] │ ├────────────┴───────┴──────────────┴─────────────┴───────────────────────┴───────────────┤ │ 4 rows │ └─────────────────────────────────────────────────────────────────────────────────────────┘ ``` ##### Using Aggregations Without group by You can use aggregations directly without `group by` to aggregate all rows: ```wvlet val data(id, value) = [ [1, 100], [2, 200], [3, 300], [4, 400], [5, 500] ] from data agg _.count as total_rows, value.sum as sum_all, value.avg as average; ┌────────────┬─────────┬───────────────┐ │ total_rows │ sum_all │ average │ │ long │ int │ decimal(17,4) │ ├────────────┼─────────┼───────────────┤ │ 5 │ 1500 │ 300.0000 │ ├────────────┴─────────┴───────────────┤ │ 1 rows │ └──────────────────────────────────────┘ ``` ##### Practical Example: Sales Analysis Here's a practical example analyzing sales data: ```wvlet val sales(month, region, product, quantity, unit_price) = [ ["2024-01", "North", "Laptop", 5, 1200], ["2024-01", "North", "Phone", 10, 800], ["2024-01", "South", "Laptop", 3, 1200], ["2024-01", "South", "Tablet", 7, 600], ["2024-02", "North", "Laptop", 8, 1200], ["2024-02", "North", "Tablet", 5, 600], ["2024-02", "South", "Phone", 12, 800], ["2024-02", "South", "Laptop", 4, 1200] ] from sales group by month, region agg _.count as num_orders, quantity.sum as total_units_sold, (quantity * unit_price).sum as total_revenue, product.count_distinct as unique_products where total_revenue > 10000 order by month, total_revenue desc; ┌─────────┬────────┬────────────┬──────────────────┬───────────────┬─────────────────┐ │ month │ region │ num_orders │ total_units_sold │ total_revenue │ unique_products │ │ string │ string │ long │ int │ int │ long │ ├─────────┼────────┼────────────┼──────────────────┼───────────────┼─────────────────┤ │ 2024-01 │ North │ 2 │ 15 │ 14000 │ 2 │ │ 2024-02 │ South │ 2 │ 16 │ 14400 │ 2 │ │ 2024-02 │ North │ 2 │ 13 │ 12600 │ 2 │ ├─────────┴────────┴────────────┴──────────────────┴───────────────┴─────────────────┤ │ 3 rows │ └────────────────────────────────────────────────────────────────────────────────────┘ ``` :::tip The `_` underscore in aggregations represents the group of rows, as in `_.count`. To aggregate a column, call the function on the column itself: `amount.sum`, `amount.avg`, `amount.max`, `amount.min`, `product.count_distinct`, `product.to_array`, `product.max_by(amount)`, `product.min_by(amount)`, etc. SQL aggregate functions such as `count(distinct product)` also work. ::: :::note The `where` clause after `agg` is equivalent to SQL's `HAVING` clause - it filters groups based on aggregated values, not individual rows. ::: #### limit Limit the number of output rows: ```wvlet from nation limit 10 ``` #### order by Sort the output rows by the given column: ```wvlet from nation order by n_name limit 5 ``` The `order by` statement can follow `asc`, `desc` ordering specifier as in SQL. You can add `nulls first` or `nulls last` to specify the order of null values. #### join The `join` operator combines rows from two or more tables based on a related column between them. Wvlet supports various types of joins similar to SQL, but with a more intuitive flow-style syntax. ##### Basic Join (Inner Join) The basic `join` returns rows when there is a match in both tables: ```wvlet val fruit(id, name, price) = [ [1, "apple", 50], [2, "banana", 10], [3, "cherry", 70] ] val fruit_order(order_id, fruit_id, qty) = [ ["o1", 1, 10], ["o2", 2, 5] ] from fruit join fruit_order on fruit.id = fruit_order.fruit_id select fruit.name, fruit_order.qty, fruit.price * fruit_order.qty as total_price; ┌────────┬─────┬─────────────┐ │ name │ qty │ total_price │ │ string │ int │ int │ ├────────┼─────┼─────────────┤ │ apple │ 10 │ 500 │ │ banana │ 5 │ 50 │ ├────────┴─────┴─────────────┤ │ 2 rows │ └────────────────────────────┘ ``` ##### Left Join A `left join` returns all rows from the left table, and matched rows from the right table. If no match, NULL values are returned for right table columns: ```wvlet val users(id, name) = [ [1, "Alice"], [2, "Bob"], [3, "Charlie"] ] val orders(user_id, order_id) = [ [1, "order1"], [1, "order2"], [3, "order3"] ] from users left join orders on users.id = orders.user_id select users.name, orders.order_id; ┌─────────┬──────────┐ │ name │ order_id │ │ string │ string │ ├─────────┼──────────┤ │ Alice │ order1 │ │ Alice │ order2 │ │ Bob │ null │ │ Charlie │ order3 │ ├─────────┴──────────┤ │ 4 rows │ └────────────────────┘ ``` ##### Right Join A `right join` returns all rows from the right table, and matched rows from the left table: ```wvlet val orders(user_id, order_id) = [ [1, "order1"], [1, "order2"], [3, "order3"], [4, "order4"] ] val users(id, name) = [ [1, "Alice"], [2, "Bob"], [3, "Charlie"] ] from orders right join users on orders.user_id = users.id select users.name, orders.order_id; ┌─────────┬──────────┐ │ name │ order_id │ │ string │ string │ ├─────────┼──────────┤ │ Alice │ order1 │ │ Alice │ order2 │ │ Bob │ null │ │ Charlie │ order3 │ ├─────────┴──────────┤ │ 4 rows │ └────────────────────┘ ``` ##### Cross Join A `cross join` produces the Cartesian product of two tables, combining each row from the first table with every row from the second table: ```wvlet val t1(id, val) = [ [1, "A"], [2, "B"] ] val t2(letter) = [ ["X"], ["Y"], ["Z"] ] from t1 cross join t2 select t1.val, t2.letter; ┌────────┬────────┐ │ val │ letter │ │ string │ string │ ├────────┼────────┤ │ A │ X │ │ A │ Y │ │ A │ Z │ │ B │ X │ │ B │ Y │ │ B │ Z │ ├────────┴────────┤ │ 6 rows │ └─────────────────┘ ``` ##### Join with Multiple Conditions You can specify multiple conditions in the `on` clause using `and`/`or` operators: ```wvlet val t1(id, code, value) = [ [1, "A", 100], [2, "B", 200], [3, "C", 300] ] val t2(id, code, qty) = [ [1, "A", 10], [2, "B", 20], [3, "D", 30] ] from t1 join t2 on t1.id = t2.id and t1.code = t2.code select t1.id, t1.code, t1.value, t2.qty; ┌─────┬────────┬───────┬─────┐ │ id │ code │ value │ qty │ │ int │ string │ int │ int │ ├─────┼────────┼───────┼─────┤ │ 1 │ A │ 100 │ 10 │ │ 2 │ B │ 200 │ 20 │ ├─────┴────────┴───────┴─────┤ │ 2 rows │ └────────────────────────────┘ ``` ##### Combining Joins with Other Operators Joins can be seamlessly combined with other Wvlet operators in a flow-style query: ```wvlet val categories(id, name, budget) = [ [1, "Electronics", 1000], [2, "Books", 500], [3, "Clothing", 750] ] val products(category_id, name, price) = [ [1, "Laptop", 800], [1, "Phone", 600], [2, "Novel", 20], [4, "Unknown", 100] ] from categories left join products on categories.id = products.category_id where products.price != null group by categories.name agg _.count as product_count, products.price.sum as total_price order by product_count desc; ┌─────────────┬───────────────┬─────────────┐ │ name │ product_count │ total_price │ │ string │ long │ int │ ├─────────────┼───────────────┼─────────────┤ │ Electronics │ 2 │ 1400 │ │ Books │ 1 │ 20 │ ├─────────────┴───────────────┴─────────────┤ │ 2 rows │ └───────────────────────────────────────────┘ ``` ##### Multiple Joins You can chain multiple join operations to combine data from several tables: ```wvlet val users(id, name) = [ [1, "Alice"], [2, "Bob"], [3, "Charlie"] ] val accounts(user_id, account_id) = [ [1, 101], [2, 102], [3, 103] ] val balances(account_id, amount) = [ [101, 1000], [102, 2000], [103, 1500] ] from users join accounts on users.id = accounts.user_id join balances on accounts.account_id = balances.account_id select users.name, accounts.account_id, balances.amount; ┌─────────┬────────────┬────────┐ │ name │ account_id │ amount │ │ string │ int │ int │ ├─────────┼────────────┼────────┤ │ Alice │ 101 │ 1000 │ │ Bob │ 102 │ 2000 │ │ Charlie │ 103 │ 1500 │ ├─────────┴────────────┴────────┤ │ 3 rows │ └───────────────────────────────┘ ``` ##### Self Join A table can be joined with itself (self join) by using aliases. The `with` statement helps avoid duplicating the data: ```wvlet val employees(id, name, manager_id) = [ [1, "Alice", 2], [2, "Bob", 3], [3, "Charlie", null] ] from employees as emp left join employees as mgr on emp.manager_id = mgr.id select emp.name as employee, mgr.name as manager; ┌──────────┬─────────┐ │ employee │ manager │ │ string │ string │ ├──────────┼─────────┤ │ Alice │ Bob │ │ Bob │ Charlie │ │ Charlie │ null │ ├──────────┴─────────┤ │ 3 rows │ └────────────────────┘ ``` :::tip Unlike SQL where you write `SELECT ... FROM table1 JOIN table2`, Wvlet's flow-style syntax starts with `from` and then adds `join` as a subsequent operation. This makes it easier to build queries incrementally and matches the logical flow of data processing. ::: :::note For time-based joins, Wvlet also supports [AsOf Join](asof-join.md), which is useful for joining tables based on the most recent value at a specific time. ::: #### concat The concat operator concatenates rows from multiple subqueries. This is similar to the UNION ALL operator in SQL. ```wvlet val t1(id, val) = [ [1, "A"], [2, "B"] ] val t2(id, val) = [ [3, "C"], [4, "D"] ] from t1 concat { from t2 } order by id; ┌─────┬────────┐ │ id │ val │ │ int │ string │ ├─────┼────────┤ │ 1 │ A │ │ 2 │ B │ │ 3 │ C │ │ 4 │ D │ ├─────┴────────┤ │ 4 rows │ └──────────────┘ ``` The ordering of rows is not guaranteed in the `concat` operator. If you need to sort the output, use the `order by` operator after the `concat` operator. You can concatenate multiple subqueries: ```wvlet from nation where n_regionkey = 0 concat { from nation where n_regionkey = 1 } concat { from nation where n_regionkey = 2 } select n_name, n_regionkey order by n_regionkey, n_name ``` #### dedup The `dedup` operator removes duplicated rows from the input rows. This is equivalent to `select distinct *` in SQL. ```wvlet val data(id, val) = [ [1, "A"], [2, "B"], [1, "A"], [3, "C"], [2, "B"] ] from data dedup; ┌─────┬────────┐ │ id │ val │ │ int │ string │ ├─────┼────────┤ │ 1 │ A │ │ 2 │ B │ │ 3 │ C │ ├─────┴────────┤ │ 3 rows │ └──────────────┘ ``` #### intersect The `intersect` operator returns the intersection of the input rows from multiple subqueries. By default, set semantics are used (duplicates removed). ```wvlet val t1(id, val) = [ [1, "A"], [2, "B"], [3, "C"], [4, "D"] ] val t2(id, val) = [ [2, "B"], [3, "C"], [5, "E"] ] from t1 intersect { from t2 }; ┌─────┬────────┐ │ id │ val │ │ int │ string │ ├─────┼────────┤ │ 2 │ B │ │ 3 │ C │ ├─────┴────────┤ │ 2 rows │ └──────────────┘ ``` With `all` keyword, bag semantics are used (duplicates preserved): ```wvlet val t1(id, val) = [ [1, "A"], [2, "B"], [2, "B"], [3, "C"] ] val t2(id, val) = [ [2, "B"], [2, "B"], [3, "C"], [3, "C"] ] from t1 intersect all { from t2 }; ┌─────┬────────┐ │ id │ val │ │ int │ string │ ├─────┼────────┤ │ 2 │ B │ │ 2 │ B │ │ 3 │ C │ ├─────┴────────┤ │ 3 rows │ └──────────────┘ ``` #### except The `except` operator returns the difference of the input rows from multiple subqueries (rows in the first query but not in the second). By default, set semantics are used. ```wvlet val t1(id, val) = [ [1, "A"], [2, "B"], [3, "C"], [4, "D"] ] val t2(id, val) = [ [2, "B"], [4, "D"] ] from t1 except { from t2 }; ┌─────┬────────┐ │ id │ val │ │ int │ string │ ├─────┼────────┤ │ 1 │ A │ │ 3 │ C │ ├─────┴────────┤ │ 2 rows │ └──────────────┘ ``` With `all` keyword, bag semantics are used: ```wvlet val t1(id, val) = [ [1, "A"], [2, "B"], [2, "B"], [3, "C"] ] val t2(id, val) = [ [2, "B"] ] from t1 except all { from t2 }; ┌─────┬────────┐ │ id │ val │ │ int │ string │ ├─────┼────────┤ │ 1 │ A │ │ 2 │ B │ │ 3 │ C │ ├─────┴────────┤ │ 3 rows │ └──────────────┘ ``` #### pivot The `pivot` operator expands the column values as horizontal columns. In `pivot`, you need to specify grouping columns (`group by`) and aggregation expressions (`agg`) in the subsequent operators. Example: ```wvlet from orders pivot on o_orderpriority group by o_orderstatus agg _.count; ┌───────────────┬──────────┬────────┬──────────┬─────────────────┬───────┐ │ o_orderstatus │ 1-URGENT │ 2-HIGH │ 3-MEDIUM │ 4-NOT SPECIFIED │ 5-LOW │ │ string │ long │ long │ long │ long │ long │ ├───────────────┼──────────┼────────┼──────────┼─────────────────┼───────┤ │ F │ 1468 │ 1483 │ 1445 │ 1465 │ 1443 │ │ O │ 1488 │ 1506 │ 1421 │ 1482 │ 1436 │ │ P │ 64 │ 76 │ 75 │ 77 │ 71 │ ├───────────────┴──────────┴────────┴──────────┴─────────────────┴───────┤ │ 3 rows │ └────────────────────────────────────────────────────────────────────────┘ ``` To specify the column values to expand, use `in` clause: ```wvlet from orders pivot on o_orderpriority in ('1-URGENT', '2-HIGH') group by o_orderstatus agg _.count; ┌───────────────┬──────────┬────────┐ │ o_orderstatus │ 1-URGENT │ 2-HIGH │ │ string │ long │ long │ ├───────────────┼──────────┼────────┤ │ F │ 1468 │ 1483 │ │ O │ 1488 │ 1506 │ │ P │ 64 │ 76 │ ├───────────────┴──────────┴────────┤ │ 3 rows │ └───────────────────────────────────┘ ``` #### unpivot The `unpivot` operator transforms multiple columns into rows. This is useful when you need to transform wide table into a long table. Currently, unpivot is available only in DuckDB backend. Example: ```wvlet val sales(id, dept, jan, feb, mar, apr, may, jun) = [ [1, 'electronics', 1, 2, 3, 4, 5, 6], [2, 'clothes', 10, 20, 30, 40, 50, 60], [3, 'cars', 100, 200, 300, 400, 500, 600] ] from sales unpivot sales for month in (jan, feb, mar, apr, may, jun) ┌─────┬─────────────┬────────┬───────┐ │ id │ dept │ month │ sales │ │ int │ string │ string │ int │ ├─────┼─────────────┼────────┼───────┤ │ 1 │ electronics │ jan │ 1 │ │ 1 │ electronics │ feb │ 2 │ │ 1 │ electronics │ mar │ 3 │ │ 1 │ electronics │ apr │ 4 │ │ 1 │ electronics │ may │ 5 │ │ 1 │ electronics │ jun │ 6 │ │ 2 │ clothes │ jan │ 10 │ │ 2 │ clothes │ feb │ 20 │ │ 2 │ clothes │ mar │ 30 │ │ 2 │ clothes │ apr │ 40 │ │ 2 │ clothes │ may │ 50 │ │ 2 │ clothes │ jun │ 60 │ │ 3 │ cars │ jan │ 100 │ │ 3 │ cars │ feb │ 200 │ │ 3 │ cars │ mar │ 300 │ │ 3 │ cars │ apr │ 400 │ │ 3 │ cars │ may │ 500 │ │ 3 │ cars │ jun │ 600 │ ├─────┴─────────────┴────────┴───────┤ │ 18 rows │ └────────────────────────────────────┘ ``` #### sample The `sample` operator randomly samples rows from the input data. You can specify either a fixed number of rows or a percentage. Sample a fixed number of rows: ```wvlet val numbers(n) = [ [1], [2], [3], [4], [5], [6], [7], [8], [9], [10] ] from numbers sample 5; ┌─────┐ │ n │ │ int │ ├─────┤ │ 2 │ │ 4 │ │ 7 │ │ 8 │ │ 10 │ ├─────┤ │ 5 … │ └─────┘ ``` Sample a percentage of rows: ```wvlet val numbers(n) = [ [1], [2], [3], [4], [5], [6], [7], [8], [9], [10] ] from numbers sample 30%; ┌─────┐ │ n │ │ int │ ├─────┤ │ 3 │ │ 6 │ │ 9 │ ├─────┤ │ 3 … │ └─────┘ ``` You can specify the sampling method - `reservoir` (default), `system`, or `bernoulli`: ```wvlet -- Reservoir sampling (default) - good for getting exact sample size from large_table sample reservoir (1000) -- System sampling - faster but approximate, samples blocks of data from large_table sample system (10%) -- Bernoulli sampling - samples individual rows with given probability from large_table sample bernoulli (10%) ``` :::tip Reservoir sampling guarantees the exact number of rows in the output (if available), while system and bernoulli sampling may return approximately the requested percentage of rows. ::: #### with To define a local subquery definition, use the `with` expression: ```wvlet with t1 as { from nation where n_regionkey = 1 } from t1 ``` You can define multiple subqueries: ```wvlet with t1 as { from nation where n_regionkey = 1 } with t2 as { from t1 limit 10 } from t2 ``` :::tip The `with` expression is similar to the common-table expression (CTE) in SQL, but for each subquery, you need to use a separate `with` clause. This is for convenience of adding multiple sub queries in separate line blocks without concatenating them with commas (,). ::: ### Common Query Patterns #### Filter → Group → Aggregate A common pattern for analyzing data: ```wvlet from sales where date >= '2024-01-01':date group by product_category agg _.count as num_sales, revenue.sum as total_revenue order by total_revenue desc limit 10 ``` #### Join → Filter → Select Combining data from multiple tables: ```wvlet from customers join { from orders where status = 'completed' } on customers.id = orders.customer_id select customers.name, orders.total, orders.created_at order by orders.created_at desc ``` #### Pivot → Aggregate Creating cross-tab reports: ```wvlet from sales_data pivot on month group by product agg revenue.sum ``` #### Multiple CTEs → Complex Analysis Using CTEs for complex queries: ```wvlet with active_users as { from users where last_login >= current_date() - '30 days':interval } with user_orders as { from orders where created_at >= current_date() - '30 days':interval } from active_users left join user_orders on active_users.id = user_orders.user_id group by active_users.segment agg _.count as user_count, user_orders.total.sum as revenue ``` #### Interval Queries → Filter by Time Ranges Intervals are useful for filtering data by time ranges. In Wvlet, you can use the `:interval` type casting syntax to specify time intervals: ##### Basic Interval Filtering ```wvlet from events where timestamp >= current_date() - '7 days':interval ``` This query returns events from the last 7 days. The `:interval` syntax casts a string to an interval value that can be subtracted from a date or timestamp. ##### Interval Syntax Supported interval units include: - **Day units**: `'1 day'`, `'7 days'`, `'30 days'` - **Hour units**: `'1 hour'`, `'24 hours'` - **Minute/Second units**: `'30 minutes'`, `'45 seconds'` - **Month/Year units**: `'1 month'`, `'3 months'`, `'1 year'`, `'2 years'` ##### Common Interval Filtering Patterns **Last N days:** ```wvlet from transactions where created_at >= current_date() - '30 days':interval select customer_id, amount order by created_at desc ``` **Last N hours:** ```wvlet from logs where timestamp >= current_timestamp() - '6 hours':interval where level = 'ERROR' ``` **Date range with intervals:** ```wvlet from sales where sale_date >= current_date() - '90 days':interval where sale_date < current_date() group by product_id agg revenue.sum as total_revenue order by total_revenue desc ``` **Time window aggregation:** ```wvlet val orders(id, order_date, amount) = [ [1, "2025-10-25", 100], [2, "2025-10-24", 150], [3, "2025-10-23", 200], [4, "2025-10-20", 75], [5, "2025-10-18", 125] ] from orders where order_date >= current_date() - '7 days':interval group by order_date agg _.count as order_count, amount.sum as daily_revenue order by order_date desc ``` :::tip The `current_date()` returns today's date, while `current_timestamp()` returns the current date and time. Use `current_timestamp()` when you need to filter by specific time ranges within a day. ::: :::note Unlike SQL which uses `interval '30' day`, Wvlet uses the `:interval` type casting syntax: `'30 days':interval`. Both are equivalent in functionality. ::: This reference guide provides a comprehensive overview of Wvlet's query syntax. For hands-on examples and tutorials, check out the [Quick Start](./quick-start.md) guide. --- ## AsOf Join :::warning Note: AsOf join is supported only in DuckDB, Snowflake, but not yet in Trino [trinodb/trino#21759](https://github.com/trinodb/trino/issues/21759), Hive, etc. ::: AsOf join is useful when you want to join two tables by looking at the most recent value from the specific time (_as of this time_) for each record. For example, if you want to know the share holding value at a specific date, you can join the holding table with the stock table by looking at the most recent stock price at the holding date: ```wvlet val holding(symbol, date, shares) = [ ['AAPL', '2024-11-07', 1.0], ['AAPL', '2024-11-08', 2.0], ['AAPL', '2024-11-09', 3.0], ['AAPL', '2024-11-10', 4.0], ] val stock(symbol, date, price) = [ ['AAPL', '2024-11-07', 10], ['AAPL', '2024-11-08', 50], ['AAPL', '2024-11-09', 100], ] from holding asof join stock on stock.symbol = holding.symbol and stock.date <= holding.date add stock.price * holding.shares as holding_value select symbol, date, shares, price, holding_value ``` This query looks up the most recent stock price from the stock table, and compute the holding_value at each date: ``` ┌────────┬────────────┬──────────────┬───────┬───────────────┐ │ symbol │ date │ shares │ price │ holding_value │ │ string │ string │ decimal(2,1) │ int │ decimal(12,1) │ ├────────┼────────────┼──────────────┼───────┼───────────────┤ │ AAPL │ 2024-11-07 │ 1.0 │ 10 │ 10.0 │ │ AAPL │ 2024-11-08 │ 2.0 │ 50 │ 100.0 │ │ AAPL │ 2024-11-09 │ 3.0 │ 100 │ 300.0 │ │ AAPL │ 2024-11-10 │ 4.0 │ 100 │ 400.0 │ ├────────┴────────────┴──────────────┴───────┴───────────────┤ │ 4 rows │ └────────────────────────────────────────────────────────────┘ ``` --- ## Data Models :::warning This page is still work in progress. ::: #### Defining Data Models In Wvlet, you can define reusable data models, which wraps an Wvlet query with `model (model name) = { ... }` block: ```wvlet model my_model = { -- Write your query here from ... ... } ``` Models can be used in other queries in the same manner with scanning a table: ```wvlet from my_model limit 10 ``` Data models are often the units to __materialize query results into the target database tables__. If your data model needs to be accessed by multiple queries, materializing (or persisting) data models will reduce the cost of data processing and often accelerates the query processing. #### Declaring Table Schemas A `table` declaration describes a stored relation — its column names and types — so queries referencing the table can be type-checked without connecting to the database: ```wvlet table orders = { order_id: bigint status: string } -- Type-checks against the declaration above, and compiles to `select * from orders` from orders ``` To describe a table that lives in a specific schema of your database, bind the declaration to its location with `in .`: ```wvlet table orders in mydb.sales = { order_id: bigint status: string } -- Both resolve through the bound declaration and compile to a scan of mydb.sales.orders from sales.orders from mydb.sales.orders ``` Declarations are contracts, not actions: nothing is created when a declaration is read. Writes materialize them — appending to a declared table that does not exist yet creates it from the declared columns, and `create table ` reads its columns from the declaration (see [Table Management](table-management.md)). Writes matching a bound declaration land at the bound location, so a declared name never reads from one place and writes to another. Notes on how bound declarations resolve: - Catalog and schema names are matched case-insensitively, following SQL identifier semantics. - A bare reference like `from orders` resolves through a bound declaration only when the binding matches the current catalog and schema of the compilation context, mirroring the search-path behavior of SQL engines. - Connector names take precedence: a reference like `from myconnector.sales.orders` resolves through the connector configured in your profile, not through a bound declaration. - Table declarations take precedence over the live database catalog, so committed declaration files act like a lockfile: the compile-time schema stays deterministic even when the database changes, while queries still execute against the real tables. Instead of writing these declarations by hand, you can generate them from a live database with [`wvlet catalog import`](../usage/catalog-import.md). #### Traits: Method Interfaces A `trait` attaches reusable `def` members to a type — a method interface in the Scala/Rust sense. Declare a new value domain by extending a base type, then use it as a column type; the columns get the trait's methods: ```wvlet trait ip_address extends string = { def country_name: string = sql"ip_to_country(${this})" } table access_logs = { time: timestamp client_ip: ip_address -- Row methods can call trait methods of the declared column types def client_country: string = client_ip.country_name } -- Both trait and row methods resolve directly on a scan of the declared table from access_logs select time, client_ip.country_name, _.client_country ``` A trait re-opening an existing type with a *dialect scope* provides engine-specific implementations — the standard library defines its methods this way: ```wvlet trait ip_address in duckdb extends string = { def country_name: string = sql"'N/A'" -- DuckDB has no IP database } ``` Trait bodies carry `def` members only — column fields are a compile-time error, because a trait never describes storage. Likewise `in` on a trait always names an engine dialect; binding a trait to a `.` location is an error (declare a `table` instead). ##### `table` vs `trait` vs `type` Everything the family declares is a type — as in Scala, where traits and classes all introduce types while the `type` keyword itself covers the general forms. `table` and `trait` are specialized kinds of type that add a commitment on top; `type` remains for declarations that make neither: - **`table`** — a type committed to storage: a stored relation with columns, an optional `in .` location, and optional row methods. - **`trait`** — a type committed to being a method interface: new value domains (`trait ip_address extends string`) and engine-dialect method packages (`trait any in duckdb`). Never storage. - **`type`** — the general form, making no commitment: aliases and marker types (`type td_trino extends trino`), and structural row or value shapes used as column or parameter types (`type point = { x: long, y: long }` with `start: point`). Only the commitment-claiming `type` spellings are deprecated, each warning toward its specialized keyword: a columns-carrying `type` that resolves a table reference is a storage claim and warns toward `table`, and a def-only `type` is a method interface and warns toward `trait`. Structural types, aliases, and markers are the permanent `type` usage and never warn. The `in` clause is unambiguous across the family: on a `trait` or `def` it always names an engine dialect; on a `table` declaration, `create schema`, or `use` it always names a storage location. ##### Composing Declarations with Mixins Since `table` and `trait` are kinds of type, a declaration can compose others with a comma-separated `extends` list: structural `type` shapes and `table` declarations contribute their columns, and `trait` parents contribute their `def` members (including dialect-scoped variants): ```wvlet type timestamped = { created_at: timestamp, updated_at: timestamp } trait auditable = { def is_recent: boolean = created_at > now() - interval '7 days' } table events extends timestamped, auditable = { id: int label: string } from events select created_at, id, _.is_recent ``` The composed shape places mixed-in columns before own body columns, in parent-list order. A column reached through multiple parents with the same type appears once, so diamond mixins are fine; the same name with conflicting types is a compile-time error. Method precedence follows Scala 3's intuition: own body defs shadow mixed-in ones, and among parents the later one wins (`extends a, b` means b refines a). Because a trait never describes storage, a `trait` cannot extend a column-carrying declaration — that is a compile-time error pointing toward `table`. A `table` extending a trait gains its methods; the single-parent forms (`trait ip_address extends string`, `type td_trino extends trino`) are unchanged as the one-element case of the same list. --- ## Flow & Stage Syntax Wvlet provides `flow` and `stage` constructs for defining data pipelines with orchestration capabilities such as retries, timeouts, scheduling, and error handling. Flows organize stages into directed acyclic graphs (DAGs) with explicit data and control dependencies. :::info Experimental Flow orchestration is **experimental**: it works end-to-end (DuckDB and Trino execution, scheduling, sensors, run stores, web UI), but the syntax and semantics described here may still change based on user feedback. In particular, the `wait until` sensor grammar, sensor row semantics, and notification payload shapes are open for refinement — share feedback on the [flow syntax feedback issue](https://github.com/wvlet/wvlet/issues/2021). Breaking changes will be called out in release notes while the feature is experimental. ::: ### From Queries to Data Flows | Construct | Purpose | Scope | Reusable | |-----------|---------|-------|----------| | `def` | Reusable function/logic | Module | Yes, callable | | `model` | Reusable data artifact | Module | Yes, queryable | | `flow` | Orchestration container | Module | Yes, triggerable | | `stage` | Execution step in flow | Inside `flow` | No, flow-specific | :::tip `model` defines **what** data to produce (a reusable data artifact), while `stage` defines **when/how** to execute (an orchestration step with retries, triggers, etc.). ::: ### Quick Navigation - [Flow Definition](#flow-definition) - [Stage Definition](#stage-definition) - [Configuration Blocks](#configuration-blocks) - [Duration Literals](#duration-literals) - [Stage Triggers](#stage-triggers) - [Flow Operators](#flow-operators) - [Flow Dependencies](#flow-dependencies) - [Flow Scheduling](#flow-scheduling) - [Running Flows](#running-flows) - [Stage Execution Model](#stage-execution-model) - [Complete Examples](#complete-examples) ### Flow Definition A `flow` is an orchestration container that groups stages into a pipeline: ```wvlet flow my_pipeline = { stage extract = from source | select * stage transform = from extract | where valid = true stage load = from transform | save to warehouse } ``` #### Flow with Parameters Flows can accept parameters, similar to functions. A parameter can declare a default value, which is used when no argument is bound at run time: ```wvlet flow customer_pipeline(segment: string, min_score: int = 0) = { stage entry = from users | where segment_id = segment and score >= min_score stage output = from entry | select name, email } ``` Arguments are bound when the flow is run, with the `run flow` statement or the `wvlet flow run` CLI (see [Running Flows](#running-flows)): ```wvlet run flow customer_pipeline(segment = 'premium') ``` Inside stage bodies, a parameter name shadows a column of the same name, so pick parameter names that do not collide with the columns you query. Table and stage names (`from`, `merge` sources, route targets, and `->` jump targets) are never treated as parameter references. #### Flow Grammar ``` flow [()] [depends on ] [if .] [with { }] = { } ``` ### Stage Definition A `stage` is an execution step within a flow. Each stage defines a data transformation using standard wvlet query operators: ```wvlet flow my_pipeline = { stage extract = from 'data.csv' stage transform = from extract | where active = true | select name, email stage load = from transform | save to warehouse.customers } ``` #### Data Dependencies Stages reference other stages via `from`, which creates an implicit success dependency. A stage runs only after its upstream stages succeed: ```wvlet flow my_pipeline = { stage a = from source -- runs immediately (literal source) stage b = from a | select * -- runs when a succeeds stage c = from b | select * -- runs when b succeeds } ``` #### Merging Stages `merge` fans in multiple stages with union-all semantics. The merged stage runs after all of its sources succeed, and is skipped when any source fails: ```wvlet flow my_pipeline = { stage source_a = from events_us | select user_id, event_time stage source_b = from events_eu | select user_id, event_time stage merged = merge source_a, source_b stage output = from merged | select user_id } ``` Merge sources must be stages defined earlier in the same flow; referencing a plain table is a compile-time error. To include a regular table in a merge, wrap it in a stage first: ```wvlet flow my_pipeline = { stage fresh = from [[1], [2]] as t(id) stage archived = from archive_table -- wrap the table in a stage stage merged = merge fresh, archived } ``` #### Stage Grammar ``` stage [if ] [with { }] = ``` ### Configuration Blocks Both flows and stages support `with { }` blocks for configuration. Configuration uses a `key: value` syntax with one item per line. #### Stage Configuration ```wvlet flow data_pipeline = { stage extract with { retries: 3 timeout: 5m retry_delay: 1s backoff: 'exponential' } = from api_source | fetch_data() stage load with { retries: 5 timeout: 10m heartbeat: 30s } = from extract | save to warehouse } ``` **Stage-level properties:** | Property | Type | Default | Description | |----------|------|---------|-------------| | `retries` | Int | 0 | Max retry attempts | | `timeout` | Duration | none | Start-to-close timeout | | `retry_delay` | Duration | 1s | Initial retry delay | | `backoff` | String | `'exponential'` | Backoff strategy (`'constant'`, `'linear'`, `'exponential'`) | | `max_retry_delay` | Duration | none | Cap on backoff delay | | `heartbeat` | Duration | none | Heartbeat interval for long-running stages | | `concurrency` | Int | 4 | Activity stages: items running at once | | `retry_uncertain` | Boolean | false | Activity stages: run items with an unknown outcome again on a rerun | | `retry_failed` | Boolean | false | Activity stages: run items that failed again on a rerun or stage retry | #### Flow Configuration ```wvlet flow daily_etl with { schedule: cron('0 2 * * *') timezone: 'UTC' concurrency: 1 } = { stage extract = from source | select * stage transform = from extract | clean() stage load = from transform | save to warehouse } ``` **Flow-level properties:** | Property | Type | Description | |----------|------|-------------| | `schedule` | Schedule | Cron or interval schedule | | `timezone` | String | Timezone for schedule evaluation | | `concurrency` | Int | Max concurrent flow executions | | `timeout` | Duration | Total flow timeout | | `on_failure` | Activation | Notification hook fired when a run fails | | `on_success` | Activation | Notification hook fired when a run succeeds | | `on_finish` | Activation | Notification hook fired for both outcomes | | `keep_runs` | Int | Retention cap: only the N most recent finished runs are kept | A flow-level `timeout:` bounds the whole run. When the deadline expires, in-flight stage attempts are stopped and every unfinished stage fails with a flow-timeout error, so the run is recorded as `failed` (not cancelled) and `on_failure:` hooks fire with the summary. A resumed run gets the full timeout budget again, measured from the resume start. #### Run Notifications The `on_failure:`, `on_success:`, and `on_finish:` hooks deliver a **run summary** — one row per stage with `flow`, `run_id`, `stage`, `state`, `attempts`, and `error` columns — to an [activation target](#delivering-results-with-activate) after the run reaches its terminal state, so operators hear about failures without polling `wvlet flow session list`: ```wvlet flow nightly_etl with { schedule: cron('0 2 * * *') on_failure: activate('webhook', url: 'https://alerts.example.com/wvlet') } = { stage extract = from source | select * stage load = from extract | save to warehouse } ``` The same targets as `activate` inside stages are available (`file`, `webhook`, and custom-registered sinks). A hook that fails to deliver is logged and never changes the result of the run. #### Combining Parameters and Configuration Flows can have both parameters and a configuration block: ```wvlet flow parameterized_flow(segment: string) with { schedule: cron('0 0 * * *') } = { stage entry = from users | where segment_id = segment } ``` ### Duration Literals Wvlet supports typed duration literals for configuration values. A duration literal is an integer followed by a unit suffix: | Unit | Suffix | Example | |------|--------|---------| | Milliseconds | `ms` | `100ms` | | Seconds | `s` | `30s` | | Minutes | `m` | `5m` | | Hours | `h` | `2h` | | Days | `d` | `1d` | ```wvlet flow duration_example = { stage work with { timeout: 100ms retry_delay: 30s max_retry_delay: 5m heartbeat: 2h } = from source | select * } ``` ### Stage Triggers By default, stages run when their upstream data sources succeed (implicit via `from`). You can override this behavior with `if` clauses to handle errors or perform cleanup. #### State Predicates | Predicate | Meaning | |-----------|---------| | `A.failed` | Stage A failed after all retries | | `A.done` | Stage A reached any terminal state (success, failed, skipped, or cancelled) | #### Error Handling Use `if .failed` to define fallback stages that run when a stage fails: ```wvlet flow resilient_pipeline = { stage primary with { retries: 3 } = from source stage fallback if primary.failed = from backup_source } ``` #### Cleanup Use `if .done` to define stages that run regardless of success or failure: ```wvlet flow pipeline_with_cleanup = { stage process = from source | transform() stage cleanup if process.done = from process | archive() } ``` #### Boolean Operators Combine trigger conditions with `and` (higher precedence) and `or` (lower precedence). Use parentheses for explicit grouping: ```wvlet flow complex_triggers = { stage a = from source stage b = from source stage c = from source -- Runs if either a or b fails stage alert if a.failed or b.failed = from source | select 'alert' -- Runs when both a and b are done stage summary if a.done and b.done = from source | select 'complete' -- Parentheses for explicit grouping stage d if a.failed or (b.done and c.done) = from source | select * } ``` ### Flow Operators Stage bodies can use flow operators in addition to regular query operators. #### Conditional Routing with route `route` sends rows to different target stages based on conditions. Each target stage reads the routing stage and receives only its matching rows. Cases follow first-match semantics like a `case`/`when` expression: a row is routed to the first case whose condition holds, and `else` receives the rows matching no case — so overlapping conditions never send a row to more than one target. Targets may reference stages defined later in the flow: ```wvlet flow conditional_flow = { stage check = from users | route { case _.age > 18 -> adult else -> minor } stage adult = from check | select name, 'adult' as category stage minor = from check | select name, 'minor' as category } ``` A percentage-based form, `route by hash(user_id) { case 50 -> variant_a ... }`, splits rows deterministically for A/B testing. #### Parallel Branches with fork `fork` defines nested stages that run as parallel branches of the flow: ```wvlet flow notification_flow = { stage entry = from users stage parallel = from entry | fork { stage email = from entry | activate('email') stage sms = from entry | activate('sms') } } ``` #### Delayed Execution with wait `wait('')` delays the materialization of a stage: ```wvlet stage delayed = from entry | wait('7 days') ``` #### Event Sensors with wait until `wait until ` waits for an *event* instead of a fixed time: the stage polls its input and proceeds once **at least one row satisfies the condition**. `_.column` in the condition refers to a row of the input (the same convention as `route` cases). The sensor only gates execution — it does not filter the rows the stage materializes. ```wvlet flow load_orders = { stage landing = from landing_files | where table_name = 'orders' stage gate with { poll_interval: 30s -- how often the condition is re-checked (default: 10s) timeout: 2h -- give up like any other stage timeout } = from landing | wait until _.status = 'ready' stage load = from gate | save to orders } ``` To wait on an aggregate condition, aggregate in the pipeline before the sensor: ```wvlet stage gate = from new_events | agg _.count as cnt | wait until _.cnt > 1000 ``` Polling respects the stage's `heartbeat:` config (a polling sensor is never mistaken for a stalled attempt), and a `timeout:` expiry follows the stage's regular retry and failure policy. While a sensor is polling, `wvlet flow session show` and the web UI runs view report the stage as **waiting** — with how long it has been waiting and when it last checked the condition — so a stage blocked on upstream data is never mistaken for an expensive query that is still executing. #### Delivering Results with activate `activate('', param: value, ...)` delivers the materialized stage output to an external target. Two sinks are built in: - `activate('file', path: 'out.csv')` exports to a local file. The format comes from the path extension or a `format:` parameter (csv, parquet, or json). On DuckDB the file is written directly with `COPY TO`; on engines without local file output (e.g. Trino) the stage output is streamed page by page through a local DuckDB that writes the file, so large results export without holding all rows in memory - `activate('webhook', url: 'https://...')` posts the rows to an HTTP endpoint as a JSON array, or as newline-delimited JSON with `format: 'ndjson'`. At most `max_rows:` rows (1000 by default) are sent A sink failure fails the stage attempt and follows the stage's retry policy; targets with no registered sink log the delivery instead of failing. Custom sinks can be plugged in from external modules via the Java ServiceLoader (`wvlet.lang.runner.ActivationSink`). #### Invoking Activities with for each `activity ''` invokes non-SQL work, such as an agent session, from a stage. `for each activity ''` invokes it once per distinct key of the stage input: ```wvlet flow investigate_customers = { stage customers = from customer_list | where priority = 'high' stage investigate with { concurrency: 5, timeout: 30m, heartbeat: 1m } = from customers | for each _.customer_id activity 'agent.investigate' stage report = from investigate | where state = 'succeeded' } ``` - Items queue and run up to `concurrency` at a time; a finished item admits the next one without waiting for its siblings. `timeout` and `heartbeat` bound each item - Each item receives its key as the `item` input, plus `run_id` and `stage`. A bare `activity` without `for each` runs once; its input is evaluated but not shipped - The stage output has one row per item: `item_key`, `state`, `attempts`, `output` (the activity's JSON result), and `error`. The stage fails if any item failed or has an unknown outcome - An activity type runs on a registered `ActivityExecutor`, or on remote workers that poll the server's `TaskApi` when the flow runs with a task queue (runs resumed by the server do). Workers report results and heartbeats; `TaskApi.listItems` and `getItem` page through item states and results - Items are durable per run: a resumed run reuses finished items and awaits attempts that workers still hold. An item whose worker stopped heartbeating becomes `uncertain`, and is never retried automatically unless `retry_uncertain: true` is set - A rerun reuses failed items, so a stage retry (`retries:`) repeats their failure. Set `retry_failed: true` to run them again under a new attempt. Cancelled items run again only when the run is resumed, not on a stage retry - Cancelling a run stops admitting items and asks running ones to stop. An item stays `cancelling` until its worker confirms, then becomes `cancelled`; a late result still lands. The run waits up to 60s for confirmation, then ends with unconfirmed items still `cancelling`. Effects that already happened are not undone #### Continuing in Another Flow with -> `-> AnotherFlow` transfers control to another flow: when the jumping stage succeeds, the target flow starts as a new run after the current flow completes. Jump chains stop at a depth limit, so mutually referencing flows cannot loop forever: ```wvlet flow main_flow = { stage check = from users | route { case _.active -> active_path else -> inactive_path } stage active_path = from check | activate('welcome') stage inactive_path = from check | -> RetentionFlow } ``` #### Explicit Termination with end `end()` marks an explicit terminal stage and passes its input through unchanged: ```wvlet stage done = from send | end() ``` ### Flow Dependencies #### Success Dependencies Use `depends on` to create cross-flow dependencies. The dependent flow runs only after the upstream flow succeeds: ```wvlet flow reporting depends on daily_etl = { stage generate = from warehouse | create_report() stage publish = from generate | upload_to_dashboard() } ``` #### Error and Cleanup Flows Use `if .failed` or `if .done` for error handling and cleanup at the flow level: ```wvlet -- Recovery flow: runs when daily_etl fails flow recovery if daily_etl.failed = { stage alert = from source | select 'error' stage log = from source | log_failure() } -- Cleanup flow: runs when daily_etl finishes (any state) flow cleanup if daily_etl.done = { stage archive = from logs | compress() } ``` #### Combined Schedule and Dependency When both `depends on` and `schedule` are specified, the schedule determines **when** to check the dependency, and the dependency determines **if** the flow runs: ```wvlet -- Runs at 3 AM, but only if ingestion has succeeded since last run flow analytics depends on ingestion with { schedule: cron('0 3 * * *') } = { stage analyze = from data | run_analytics() } ``` ### Flow Scheduling Flows can be scheduled using cron expressions: ```wvlet flow scheduled_flow with { schedule: cron('0 2 * * *') -- 2 AM daily timezone: 'UTC' concurrency: 1 -- prevent overlapping runs } = { stage extract = from source | select * } ``` Schedules are evaluated by the scheduler daemon: ```bash wvlet flow scheduler -w ``` The daemon evaluates the five-field cron expression of every scheduled flow in the working folder (in the flow's `timezone:`, defaulting to the system zone) and triggers due flows through the regular flow executor, so cross-flow dependencies (`depends on`, `if X.failed`) still decide whether a triggered run actually executes: the schedule determines *when* to check, the dependency determines *if* the flow runs. The daemon polls the `.wv` files of the working folder (every 5 seconds by default, configurable with `--reload-interval `, `0` disables it) and reloads the schedules when they change, so flows can be added, removed, or rescheduled without restarting. Flows whose schedule is unchanged keep their pending fire time across reloads; a compile error keeps the previously loaded schedules. Two flags cover scripting and missed windows: - `--once`: evaluate the schedules once against the current minute, run the matching flows, and exit. Use this to drive wvlet schedules from an external scheduler such as system cron. - `--catchup`: on startup, trigger every scheduled flow whose most recent fire time has no recorded run at or after it (e.g. the fire was missed while the daemon was down). The catch-up run binds the **missed** fire time as its `run_time`/`run_date` (see [Run Time](#run-time-run_time-and-run_date)), so the run computes the window it originally missed. Without this flag, missed windows are skipped silently. Combine with `--once` for a one-shot catch-up run. `concurrency: N` is enforced through the run store whenever a flow starts (scheduler, CLI, or `run flow`): a run atomically claims one of the flow's N slots and is recorded as `skipped` when all slots are taken by running runs. With the `sqlite` run store the claim is transactional across processes. #### Run Retention Run records and their run-scoped `__wv_flow_*` tables accumulate until cleaned. Besides the manual `wvlet flow session clean`, the scheduler sweeps them automatically — at startup and every few minutes while the daemon runs — when a retention policy is set: - `keep_runs: N` in a flow's config block keeps only the N most recent finished runs of that flow - `wvlet flow scheduler --retention 7d` (also `12h`, `30m`) deletes finished runs older than the given age, across all flows The sweep first marks crashed runs (running records whose liveness lease expired) as failed, then deletes finished runs beyond the policy together with their tables. The most recent finished run of each flow is always kept, because cross-flow dependencies (`depends on X`, `if X.failed`) are evaluated against it. ```wvlet flow hourly_metrics with { schedule: cron('0 * * * *') keep_runs: 24 } = { ... } ``` ### Running Flows Flow definitions are declarations: running a file that contains flows does not execute them. Flows are triggered explicitly with the `run flow` statement or the `wvlet flow` CLI. #### Engine Support Flows run on DuckDB (the default) and on Trino, selected with `--profile` like regular queries. Stage materialization adapts to the engine automatically (e.g. Trino catalogs without `create or replace table` get an explicit drop-and-create), and stage timeouts and cancellation stop the running query server-side on both engines. Run-scoped `__wv_flow_*` tables are created in the `catalog`/`schema` of the profile's default engine connector, which must exist and be writable (for example a `memory` catalog schema on Trino). #### Per-Stage Engines A stage can run on a different connector of the active profile with `on `: ```sql flow daily = { -- Runs on the profile's default engine stage extract = from td.www_access | where time > current_date - interval '1 day' -- Runs on the local DuckDB connector stage report on local = from extract | select count(*) as pv } ``` When a stage's body references a table on a different connector (e.g. a `local` stage reading `td.www_access`), the rows are staged automatically: the source table is copied into a run-scoped staging table on the stage's engine before the stage materializes. Staging currently lands on DuckDB engines; staging into other engines is planned. A stage that reads another stage's output must run on the same engine as that stage (stage outputs are materialized on the engine that produced them). #### run flow Statement `run flow ` executes a flow and produces a **flow-run summary** relation with one row per stage (`stage`, `state`, `attempts`, `error`). The summary can be piped through query operators or verified with test statements: ```wvlet flow my_pipeline = { stage src = from [[1, 'a'], [2, 'b']] as t(id, name) stage filtered = from src | where name = 'a' } run flow my_pipeline | where state = 'success' test _.size should be 2 ``` Each successful stage is materialized as a run-scoped temporary table (`__wv_flow__`), so stage names never collide with real tables and concurrent runs do not interfere with each other. ##### Binding Flow Parameters A parameterized flow is run by passing arguments in the flow call, positionally or by name. Unbound parameters take their declared defaults; a missing required argument, an unknown argument name, or a mistyped literal is reported before any stage executes: ```wvlet flow customer_pipeline(segment: string, min_score: int = 0) = { stage entry = from users | where segment_id = segment and score >= min_score stage output = from entry | select name, email } run flow customer_pipeline(segment = 'premium', min_score = 50) ``` Flow identity is by name only: `concurrency:` limits and cross-flow dependencies (`depends on X`, `if X.failed`) treat every run of a flow the same, regardless of the arguments it was run with. ##### Run Time: run_time and run_date Every run also binds two implicit values that stage bodies can reference like parameters: - `run_time`: the logical time of the run as a timestamp - `run_date`: the same time as a `'yyyy-MM-dd'` string For scheduler-triggered runs, the logical time is the **schedule fire time** (a `--catchup` run receives the fire time it missed, not the current time), so a daily flow can scope each run to its own window: ```wvlet flow daily_sales with { schedule: cron('0 2 * * *') } = { stage src = from sales | where sales_date = run_date stage report = from src | group by region | agg amount.sum as total } ``` Manual runs (`run flow`, `wvlet flow run`) bind the current time. A declared flow parameter named `run_time` or `run_date` takes precedence over the implicit binding. ##### Backfill `wvlet flow backfill` re-runs a scheduled flow once per schedule window in a date range, binding each window's fire time as `run_time`/`run_date`: ```bash # One run per day: 2026-07-01, 2026-07-02, ..., 2026-07-31 wvlet flow backfill daily_sales --from 2026-07-01 --to 2026-07-31 -w ./pipelines # Arguments combine with windows; --to defaults to the current time wvlet flow backfill "daily_sales(region = 'us')" --from 2026-07-01 -w ./pipelines ``` Windows run **sequentially in chronological order**, and the backfill stops at the first failed run (later windows often depend on earlier ones); the printed message shows the `--from` value to resume with after fixing the failure. The flow must declare a `schedule: cron(...)` config, which defines the windows. #### wvlet flow CLI Flows can be managed from the command line: ```bash # List flows defined in the working folder wvlet flow list -w ./pipelines # Show the plan of a flow wvlet flow show my_pipeline -w ./pipelines # Run a flow and print its per-stage results (non-zero exit code when the flow fails) wvlet flow run my_pipeline -w ./pipelines # Run a parameterized flow with a flow call (the same syntax as the run flow statement) wvlet flow run "customer_pipeline(segment = 'premium')" -w ./pipelines # List recorded flow runs (most recent first) wvlet flow session list -w ./pipelines # Show the per-stage details of a recorded run wvlet flow session show -w ./pipelines ``` Every flow run is recorded in a run store, updated live as stages change state. Three store backends are available and can be selected with `--run-store ` (or the `WVLET_FLOW_STORE` environment variable): - `file` (default): one JSON file per run (`target/flow-runs/.json`), no dependencies - `sqlite`: a single SQLite database (`target/flow-runs/registry.db`) in WAL mode. Records are transactional across processes, which a scheduler daemon needs for enforcing flow-level `concurrency:` limits - `postgres`: a shared PostgreSQL database, so `concurrency:` enforcement, scheduler catch-up, `session` commands, and the web UI observe runs recorded on **any machine** pointing at the same database. The connection comes from environment variables only — never CLI flags — keeping credentials out of the process list: `WVLET_FLOW_STORE_PG_URL` (e.g. `jdbc:postgresql://host:5432/wvlet`), and optionally `WVLET_FLOW_STORE_PG_USER` (default `postgres`) and `WVLET_FLOW_STORE_PG_PASSWORD`. Concurrent slot claims are serialized with a per-flow advisory lock, so a `concurrency:` limit holds across hosts Cross-flow dependencies (`depends on X`, `if X.failed`, `if X.done`) are evaluated against the latest recorded run of the referenced flow: when the dependency is not satisfied, the flow run is recorded as `skipped` without executing any stage. Runs are managed with `wvlet flow session` subcommands: - `wvlet flow session list` / `show `: inspect recorded runs and per-stage states. `show` displays how the run was invoked — its flow call form with the resolved arguments (e.g. `customer_pipeline(segment = 'premium')`) and its logical `run_time` — so backfilled and parameterized runs stay auditable - `wvlet flow session cancel `: request cancellation of a running flow, even from another process. The executor polls the registry and stops in-flight stage attempts (cancelling their SQL statements server-side) - `wvlet flow session resume `: re-run a failed or cancelled run from its first non-successful stage, reusing the materialized tables of stages that already succeeded. The run's recorded arguments and `run_time`/`run_date` are re-bound automatically, so a parameterized or backfilled run resumes with exactly the values of its original invocation - `wvlet flow session clean`: delete terminal run records and drop their run-scoped `__wv_flow_*` tables. With `--stale`, also remove running records whose liveness lease expired (crashed runs) #### Flow Runs in the Web UI `wvlet ui -w ` serves a **Flow Runs** page next to the query editor: recent runs with state badges, per-run stage states, attempts, and errors, and a flow-name filter. The page reads the same run store as the `wvlet flow session` commands, so runs triggered from the CLI or a scheduler daemon on the same working folder appear as they happen, and the list auto-refreshes while a run is in flight. A selected run also offers **Cancel** (for running runs) and **Resume** (for failed or cancelled runs) buttons, each with a confirmation step. They follow the same semantics as `wvlet flow session cancel`/`resume`: cancellation is requested through the shared run store, so it reaches runs owned by other processes, and a resumed flow is recompiled from the server's working folder and re-executed in the background with its recorded arguments. #### Run Liveness Leases While a flow runs, the executor periodically refreshes a liveness lease on its run record (60 seconds by default). A record left in the `running` state by a crashed process stops being refreshed, and once its lease expires the record is treated as failed everywhere it is observed: - it no longer occupies a `concurrency:` slot, so scheduled runs are not blocked forever - cross-flow dependencies see it as failed (`if X.failed` recovery flows fire; `depends on X` stays unsatisfied) - `wvlet flow session list` marks it as `running (stale)`, `session resume ` accepts it like a failed run, and `session clean --stale` removes it ### Stage Execution Model :::info Implementation status The flow executor fully implements this model: independent stages run in parallel on a DAG scheduler, failed attempts retry with the configured backoff, and `timeout:` / `heartbeat:` bound each attempt, cancelling a running SQL statement server-side on expiry. See [Flow Operators](#flow-operators), [Flow Scheduling](#flow-scheduling), and [Running Flows](#running-flows) for the features built on top of it. ::: Each stage progresses through a well-defined state machine: ```mermaid stateDiagram-v2 [*] --> pending pending --> running pending --> skipped: trigger rule evaluates\nupstream non-success pending --> cancelled: user/parent cancellation running --> success running --> attempt_failed running --> cancelled: user/parent cancellation attempt_failed --> retrying: retries remaining retrying --> running: retry delay elapsed attempt_failed --> failed: max retries exceeded retrying --> failed: max retries exceeded success --> [*] failed --> [*] skipped --> [*] cancelled --> [*] ``` #### State Reference | State | Terminal | Description | |-------|----------|-------------| | `pending` | No | Waiting for upstream dependencies | | `running` | No | Currently executing | | `success` | Yes | Completed successfully | | `attempt_failed` | No | Current attempt failed, will retry if attempts remain | | `retrying` | No | Waiting for retry delay before next attempt | | `failed` | Yes | All retry attempts exhausted | | `skipped` | Yes | Bypassed due to trigger rule (upstream non-success) | | `cancelled` | Yes | Stopped by user or parent flow | #### Trigger Evaluation Triggers evaluate based on terminal states: | Terminal State | `from A` (stage) | `depends on` (flow) | `if X.failed` | `if X.done` | |----------------|------------------|---------------------|---------------|-------------| | `success` | Runs | Runs | -- | Runs | | `failed` | Skipped | Skipped | Runs | Runs | | `skipped` | Skipped | Skipped | -- | Runs | | `cancelled` | Skipped | Skipped | -- | Runs | ### Complete Examples #### ETL Pipeline with Error Handling ```wvlet -- Reusable data models model stg_customers = from source.customers | where valid = true model stg_orders = from source.orders | where valid = true -- Orchestrated pipeline with retries and error handling flow daily_etl with { schedule: cron('0 2 * * *') timezone: 'UTC' concurrency: 1 } = { stage refresh_customers with { retries: 3 timeout: 10m retry_delay: 30s backoff: 'exponential' } = from stg_customers | save to warehouse.customers stage refresh_orders with { retries: 3 timeout: 10m } = from stg_orders | save to warehouse.orders -- Error handling stage alert if refresh_customers.failed or refresh_orders.failed = from source | select 'ETL stage failed' -- Cleanup runs regardless of outcome stage cleanup if refresh_customers.done and refresh_orders.done = from source | select 'cleanup complete' } -- Downstream reporting depends on ETL success flow daily_reporting depends on daily_etl = { stage report = from warehouse | create_report() } -- Recovery flow handles ETL failure flow etl_recovery if daily_etl.failed = { stage notify = from source | select 'ETL failed, manual intervention needed' } ``` #### Multi-Stage Pipeline with Fallback ```wvlet flow resilient_pipeline = { -- Primary path with retries stage primary with { retries: 3 timeout: 5m } = from primary_api | fetch_data() -- Fallback: runs only if primary fails stage fallback if primary.failed = from backup_api | fetch_data() -- Transform: runs when primary succeeds stage transform = from primary | normalize() -- Notify on completion stage notify if primary.done = from primary | select 'pipeline complete' } ``` --- ## Quick Start ### A Walk-Through Example Let's start with a simple example. If you haven't installed `wv` command, [install Wvlet command](../usage/install.md) to your machine. `wv` command starts an [interactive shell](../usage/repl.md), which is backed by DuckDB in-memory database by default. For the ease of learning, let's create a sample [TPC-H benchmark](https://www.tpc.org/tpch/) data set: ```wvlet $ wv wv> execute sql"call dbgen(sf=0.01)"; wv> show tables; ┌────────────┐ │ table_name │ │ string │ ├────────────┤ │ customer │ │ lineitem │ │ nation │ │ orders │ │ part │ │ partsupp │ │ region │ │ supplier │ ├────────────┤ │ 8 rows │ └────────────┘ ``` The `execute sql"call dbgen(sf=0.01)"` command calls DuckDB's [TPC-H extension](https://duckdb.org/docs/extensions/tpch.html) and creates an in-memory TPC-H benchmark database, which will be gone when you exit the Wvlet shell. So you can try this command without worrying about the disk space. The simplest form of queries is `from (table name)`: ```wvlet wv> from customer; ┌───────────┬────────────────────┬─────────────────────────────────────────┬───────────> │ c_custkey │ c_name │ c_address │ c_nationke> │ long │ string │ string │ int > ├───────────┼────────────────────┼─────────────────────────────────────────┼───────────> │ 1 │ Customer#000000001 │ j5JsirBM9PsCy0O1m │ 1> │ 2 │ Customer#000000002 │ 487LW1dovn6Q4dMVymKwwLE9OKf3QG │ 1> │ 3 │ Customer#000000003 │ fkRGN8nY4pkE │ > │ 4 │ Customer#000000004 │ 4u58h fqkyE │ > │ 5 │ Customer#000000005 │ hwBtxkoBF qSW4KrIk5U 2B1AU7H │ > │ 6 │ Customer#000000006 │ g1s,pzDenUEBW3O,2 pxu0f9n2g64rJrt5E │ 2> │ 7 │ Customer#000000007 │ 8OkMVLQ1dK6Mbu6WG9 w4pLGQ n7MQ │ 1> │ 8 │ Customer#000000008 │ j,pZ,Qp,qtFEo0r0c 92qobZtlhSuOqbE4JGV │ 1> │ 9 │ Customer#000000009 │ vgIql8H6zoyuLMFNdAMLyE7 H9 │ > ``` This query returns all the columns in the `customer` table. If the query result doesn't fit to the screen, Wvlet shell enters [UNIX `less` command](https://en.wikipedia.org/wiki/Less_(Unix)#:~:text=less%20is%20a%20terminal%20pager,backward%20navigation%20through%20the%20file.) mode, which allows to navigate table data using arrow keys, page up/down keys, and `q` key to exit the mode. See [Interactive Shell](../usage/repl.md) for the list of the available shortcut keys. To limit the number of rows to display, you can use `limit` operator: ```wvlet wv> from customer │ limit 3; ┌───────────┬────────────────────┬────────────────────────────────┬─────────────┬──────> │ c_custkey │ c_name │ c_address │ c_nationkey │ c> │ long │ string │ string │ int │ s> ├───────────┼────────────────────┼────────────────────────────────┼─────────────┼──────> │ 1 │ Customer#000000001 │ j5JsirBM9PsCy0O1m │ 15 │ 25-98> │ 2 │ Customer#000000002 │ 487LW1dovn6Q4dMVymKwwLE9OKf3QG │ 13 │ 23-76> │ 3 │ Customer#000000003 │ fkRGN8nY4pkE │ 1 │ 11-71> ├───────────┴────────────────────┴────────────────────────────────┴─────────────┴──────> │ 3 rows > └──────────────────────────────────────────────────────────────────────────────────────> ``` :::tip Separators `|` between expressions are shown only while editing queries. You don't need to type `|` in the Wvlet shell or in query files. ::: To select specific columns, you can use `select` operator: ```wvlet from customer wv> from customer │ select c_name, c_nationkey │ limit 5; ┌────────────────────┬─────────────┐ │ c_name │ c_nationkey │ │ string │ int │ ├────────────────────┼─────────────┤ │ Customer#000000001 │ 15 │ │ Customer#000000002 │ 13 │ │ Customer#000000003 │ 1 │ │ Customer#000000004 │ 4 │ │ Customer#000000005 │ 3 │ ├────────────────────┴─────────────┤ │ 5 rows │ └──────────────────────────────────┘ ``` To select specific values from the table, you can use `where` operator: ```wvlet wv> from customer │ where c_nationkey = 1 │ select c_name, c_address │ limit 5; ┌────────────────────┬─────────────┐ │ c_name │ c_nationkey │ │ string │ int │ ├────────────────────┼─────────────┤ │ Customer#000000003 │ 1 │ │ Customer#000000014 │ 1 │ │ Customer#000000030 │ 1 │ │ Customer#000000059 │ 1 │ │ Customer#000000106 │ 1 │ ├────────────────────┴─────────────┤ │ 5 rows │ └──────────────────────────────────┘ ``` ### Writing Queries #### One-Liner Queries In Wvlet, individual query line often matches with a single [relational operator](./), which processes a given input table data and return a new table data. Inserting newlines, however, is not mandatory. You can fit the whole query within a single line, which is convenient for quick data exploration: ```wvlet wv> from customer where c_mktsegment = 'HOUSEHOLD' limit 5; ┌───────────┬────────────────────┬────────────────────────────────────────┬────────────> │ c_custkey │ c_name │ c_address │ c_nationkey> │ long │ string │ string │ int > ├───────────┼────────────────────┼────────────────────────────────────────┼────────────> │ 5 │ Customer#000000005 │ hwBtxkoBF qSW4KrIk5U 2B1AU7H │ 3> │ 10 │ Customer#000000010 │ Vf mQ6Ug9Ucf5OKGYq fsaX AtfsO7,rwY │ 5> │ 12 │ Customer#000000012 │ Sb4gxKs7W1AZa │ 13> │ 15 │ Customer#000000015 │ 3y4KK4CcfNwNCTP0u0p1Rk6aeghe3Z30mo0VnD │ 23> │ 19 │ Customer#000000019 │ yO0XPkiuSWk0vN FfcH5 IA3oBYy │ 18> ├───────────┴────────────────────┴────────────────────────────────────────┴────────────> │ 5 rows > └──────────────────────────────────────────────────────────────────────────────────────> ``` #### Adding Comments The multi-line syntax is convenient for improving the readability of your queries. As Wvlet adopts a flow-style syntax, you can add comments to each line of the query: ```wvlet wv> from customer │ -- Select customers for each market segment, e.g., HOUSEHOLD, BUILDING, etc. │ group by c_mktsegment, │ -- Report the number of customers in each market segment │ agg _.count as customer_count ┌──────────────┬────────────────┐ │ c_mktsegment │ customer_count │ │ string │ long │ ├──────────────┼────────────────┤ │ HOUSEHOLD │ 588 │ │ BUILDING │ 674 │ │ MACHINERY │ 576 │ │ FURNITURE │ 558 │ │ AUTOMOBILE │ 604 │ ├──────────────┴────────────────┤ │ 5 rows │ └───────────────────────────────┘ ``` Comments in Wvlet start with `--` and continue to the end of the line. ### Exploring Data #### Describing Table Schema To learn about the table schema, the list of columns and types in the table, you can use `describe` operator: ```wvlet wv> from customer │ describe; ┌──────────────┬─────────────┐ │ column_name │ column_type │ │ string │ string │ ├──────────────┼─────────────┤ │ c_custkey │ long │ │ c_name │ string │ │ c_address │ string │ │ c_nationkey │ int │ │ c_phone │ string │ │ c_acctbal │ decimal │ │ c_mktsegment │ string │ │ c_comment │ string │ ├──────────────┴─────────────┤ │ 8 rows │ └────────────────────────────┘ ``` `describe` is also a relational operator, which can be filtered by `where` operator: ```wvlet wv> from customer │ describe │ where column_name like '%name%'; ┌─────────────┬─────────────┐ │ column_name │ column_type │ │ string │ string │ ├─────────────┼─────────────┤ │ c_name │ string │ ├─────────────┴─────────────┤ │ 1 rows │ └───────────────────────────┘ ``` #### Quick Schema Check A more convenient way to see the table schema is to use `ctrl-j ctrl-d` shortcut keys in the Wvlet shell: ```wvlet describe (line:1): from customer ┌──────────────┬─────────────┐ │ column_name │ column_type │ │ string │ string │ ├──────────────┼─────────────┤ │ c_custkey │ long │ │ c_name │ string │ │ c_address │ string │ │ c_nationkey │ int │ │ c_phone │ string │ │ c_acctbal │ decimal │ │ c_mktsegment │ string │ │ c_comment │ string │ ├──────────────┴─────────────┤ │ 8 rows │ └────────────────────────────┘ wv> from customer -- Press ctrl-j ctrl-d sequence here │ where c_nationkey = 1 │ select c_name, c_nationkey │ limit 5; ``` `ctrl-j ctrl-d` shortcut key internally calls `(A query fragment up to the current line) describe` to show the schema of the current query fragment. You can also check the schema in the middle of a query: ```wvlet describe (line:3): select c_name, c_nationkey ┌─────────────┬─────────────┐ │ column_name │ column_type │ │ string │ string │ ├─────────────┼─────────────┤ │ c_name │ string │ │ c_nationkey │ int │ ├─────────────┴─────────────┤ │ 2 rows │ └───────────────────────────┘ wv> from customer │ where c_nationkey = 1 │ select c_name, c_nationkey -- Press ctrl-j ctrl-d here │ limit 5; ``` #### Test Run to Peek Data While editing queries, you will often need to look at the actual data. Type `ctrl-j` `ctrl-t` (test run) to see the intermediate query results at the line: ```wvlet wv> from customer -- type ctrl-j ctrl-t here │ where c_nationkey = 1 │ select c_name, c_nationkey │ limit 5; debug (line:1): from customer ┌───────────┬────────────────────┬─────────────────────────────────────────┬───────────> │ c_custkey │ c_name │ c_address │ c_nationke> │ long │ string │ string │ int > ├───────────┼────────────────────┼─────────────────────────────────────────┼───────────> │ 1 │ Customer#000000001 │ j5JsirBM9PsCy0O1m │ 1> │ 2 │ Customer#000000002 │ 487LW1dovn6Q4dMVymKwwLE9OKf3QG │ 1> │ 3 │ Customer#000000003 │ fkRGN8nY4pkE │ > │ 4 │ Customer#000000004 │ 4u58h fqkyE │ > │ 5 │ Customer#000000005 │ hwBtxkoBF qSW4KrIk5U 2B1AU7H │ > │ 6 │ Customer#000000006 │ g1s,pzDenUEBW3O,2 pxu0f9n2g64rJrt5E │ 2> │ 7 │ Customer#000000007 │ 8OkMVLQ1dK6Mbu6WG9 w4pLGQ n7MQ │ 1> │ 8 │ Customer#000000008 │ j,pZ,Qp,qtFEo0r0c 92qobZtlhSuOqbE4JGV │ 1> │ 9 │ Customer#000000009 │ vgIql8H6zoyuLMFNdAMLyE7 H9 │ > │ 10 │ Customer#000000010 │ Vf mQ6Ug9Ucf5OKGYq fsaX AtfsO7,rwY │ > ``` Test run command is useful to refine your query as you add more relational operators: ```wvlet wv> from customer │ select c_custkey, c_name, c_nationkey -- type ctrl-j ctrl-t here debug (line:2): select c_custkey, c_name, c_nationkey ┌───────────┬────────────────────┬─────────────┐ │ c_custkey │ c_name │ c_nationkey │ │ long │ string │ int │ ├───────────┼────────────────────┼─────────────┤ │ 1 │ Customer#000000001 │ 15 │ │ 2 │ Customer#000000002 │ 13 │ │ 3 │ Customer#000000003 │ 1 │ │ 4 │ Customer#000000004 │ 4 │ │ 5 │ Customer#000000005 │ 3 │ │ 6 │ Customer#000000006 │ 20 │ ``` ### Reusing Queries In Wvlet, you can name a query using `select as` operator, and refer to the named query result in the subsequent queries: ```wvlet wv> from customer │ where c_nationkey = 1 │ -- Name the query as domestic_customer │ select as domestic_customer; ``` You can refer to the named query result in the subsequent queries: ```wvlet wv> from domestic_customer │ limit 5; ┌───────────┬────────────────────┬─────────────────────────────────────────┬─────────────┬─> │ c_custkey │ c_name │ c_address │ c_nationkey │ > │ long │ string │ string │ int │ > ├───────────┼────────────────────┼─────────────────────────────────────────┼─────────────┼─> │ 3 │ Customer#000000003 │ fkRGN8nY4pkE │ 1 │ > │ 14 │ Customer#000000014 │ h3GFMzeFfYiamqr │ 1 │ > │ 30 │ Customer#000000030 │ EhnzmgkqQw7UXhF0PVdg gLfSAihaaHaD2fZah2 │ 1 │ > │ 59 │ Customer#000000059 │ tfcob0wJRYdypIJLzBckGW │ 1 │ > │ 106 │ Customer#000000106 │ vkocmr6H6dl │ 1 │ > ├───────────┴────────────────────┴─────────────────────────────────────────┴─────────────┴─> │ 5 rows > └──────────────────────────────────────────────────────────────────────────────────────────> ``` Unlike SQL views, which will be registered to the system catalog, named queries are available only in the current scope (e.g., the current Wvlet shell session). ### Saving Queries in .wv Files If you want to reuse the query in other sessions or share it with others, you can save the query to a file with `.wv` extension: ```wvlet title="my_query.wv" from customer where c_nationkey = 1 ``` Queries in `.wv` files can be loaded in `from` operator: ```wvlet -- Load the query written in my_query.wv file from 'my_query.wv' ``` In the wvlet shell, .wv files will be loaded from the current directory. If you want to load files from other directories, use `-w (working directory)` option to specify the base directory. :::tip For advanced users, you can define reusable data models, which can accept some input parameters. Wvlet will find data models defined in `.wv` files in the working directory. See [Data Models](./data-models.md) for more details. ::: --- ## Standard Library Reference Complete listing of the functions defined in the Wvlet standard library, generated from its sources. Call these with dot syntax on a value of the listed type (e.g. `name.upper`, `price.round(2)`). See [Standard Library Functions](stdlib.md) for a guided tour with examples. The **Engines** column lists the engines a definition is specialized for; **all** means a single dialect-neutral definition serves every supported engine (DuckDB, Trino, Hive, Snowflake, and BigQuery). Engine-specific SQL is selected automatically for the compile target. ### Any Values Available on values of every type. | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_string` | string | all | | | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_date` | date | all | Cast to the SQL date type | | `to_timestamp` | timestamp | all | Cast to the SQL timestamp type | | `is_null` | boolean | all | True if this value is null | | `is_not_null` | boolean | all | | | `or_else(other: any)` | any | all | Return the other value if this value is null | | `null_if(v: any)` | any | all | Return null if this value equals the given value | | `type_of` | string | all | Name of the runtime type of this value | | `to_json` | json | duckdb, trino | | ### Numeric Values Shared by all numeric types (int, long, float, real, double, decimal). | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `round(decimal: int)` | double | all | Round to the given number of decimal places | | `sqrt` | double | all | | | `cbrt` | double | all | | | `exp` | double | all | | | `ln` | double | all | | | `log10` | double | all | | | `log2` | double | all | | | `power(exponent: double)` | double | all | | | `sign` | int | all | | | `in(v: any*)` | boolean | all | | | `not_in(v: any*)` | boolean | all | | ### Int Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_string` | string | all | | | `or_else(other: int)` | int | all | | | `abs` | int | all | | | `ceil` | int | all | | | `floor` | int | all | | | `mod(divisor: int)` | int | all | | | `from_unixtime` | timestamp | duckdb, trino, hive, snowflake, bigquery | Interpret this value as a unix epoch second (in UTC) | | `between(l: int, r: int)` | boolean | all | | ### Long Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_string` | string | all | | | `or_else(other: long)` | long | all | | | `abs` | long | all | | | `ceil` | long | all | | | `floor` | long | all | | | `mod(divisor: long)` | long | all | | | `from_unixtime` | timestamp | duckdb, trino, hive, snowflake, bigquery | Interpret this value as a unix epoch second (in UTC) | | `between(l: long, r: long)` | boolean | all | | | `within(duration: string)` | boolean | td_trino | | | `td_time_string(format: string)` | string | td_trino, td_hive | | | `td_interval(duration: string)` | boolean | td_hive | | ### Float Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_string` | string | all | | | `or_else(other: float)` | float | all | | | `abs` | float | all | | | `ceil` | float | all | | | `floor` | float | all | | | `is_nan` | boolean | duckdb, trino, bigquery | | | `is_finite` | boolean | duckdb, trino, bigquery | | | `is_infinite` | boolean | duckdb, trino, bigquery | | | `between(l: float, r: float)` | boolean | all | | ### Real Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_string` | string | all | | | `or_else(other: real)` | real | all | | | `abs` | real | all | | | `ceil` | real | all | | | `floor` | real | all | | | `between(l: real, r: real)` | boolean | all | | ### Double Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_string` | string | all | | | `or_else(other: double)` | double | all | | | `abs` | double | all | | | `ceil` | double | all | | | `floor` | double | all | | | `degrees` | double | all | | | `radians` | double | all | | | `sin` | double | all | | | `cos` | double | all | | | `tan` | double | all | | | `asin` | double | all | | | `acos` | double | all | | | `atan` | double | all | | | `truncate` | double | duckdb, trino, hive, snowflake, bigquery | Drop the fractional part of this value | | `is_nan` | boolean | duckdb, trino, bigquery | | | `is_finite` | boolean | duckdb, trino, bigquery | | | `is_infinite` | boolean | duckdb, trino, bigquery | | | `between(l: double, r: double)` | boolean | all | | ### Decimal Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_string` | string | all | | | `or_else(other: decimal)` | decimal | all | | | `abs` | decimal | all | | | `ceil` | decimal | all | | | `floor` | decimal | all | | | `mod(divisor: decimal)` | decimal | all | | | `truncate` | decimal | duckdb, trino, hive, snowflake, bigquery | Drop the fractional part of this value | | `between(l: decimal, r: decimal)` | boolean | all | | ### Boolean Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_string` | string | all | | | `or_else(other: boolean)` | boolean | all | if the value is null, return the given default value | ### String Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_date` | date | all | | | `to_timestamp` | timestamp | all | | | `or_else(other: string)` | string | all | if the string is null, return the default value | | `length` | int | all | Number of characters in the string | | `upper` | string | all | | | `lower` | string | all | | | `trim` | string | all | | | `ltrim` | string | all | | | `rtrim` | string | all | | | `reverse` | string | all | | | `concat(other: string)` | string | all | Concatenate the given string after this string | | `replace(search: string, replacement: string)` | string | all | Replace all occurrences of the search string with the replacement string | | `lpad(length: int, pad: string)` | string | all | Pad the string to the given length by prepending (lpad) or appending (rpad) the pad string | | `rpad(length: int, pad: string)` | string | all | | | `strpos(substr: string)` | int | all | 1-origin position of the first occurrence of the substring (0 if not found) | | `like(pattern: string)` | boolean | all | | | `substring(start: int)` | string | all | Substring from the 1-origin start position (to the end, or of the given length) | | `substring(start: int, length: int)` | string | all | | | `regexp_extract(pattern: string)` | string | all | Extract the first substring matching the regular expression | | `regexp_extract(pattern: string, group_index: int)` | string | all | | | `starts_with(prefix: string)` | boolean | all | | | `in(v: any*)` | boolean | all | | | `not_in(expr: any*)` | boolean | all | | | `regexp_like(pattern: string)` | boolean | duckdb, trino, hive, snowflake, bigquery | Snowflake's REGEXP_LIKE matches the entire string, so use REGEXP_INSTR for the partial-match semantics of the other engines | | `regexp_replace(pattern: string, replacement: string)` | string | duckdb, trino, hive, snowflake, bigquery | Replace every substring matching the regular expression with the replacement | | `contains(substr: string)` | boolean | duckdb, trino, hive, snowflake, bigquery | | | `ends_with(suffix: string)` | boolean | duckdb, trino, hive, snowflake, bigquery | | | `split(separator: string)` | array[string] | duckdb, trino, hive, snowflake, bigquery | Split the string by the separator into an array of strings | | `md5` | string | duckdb, trino, hive, snowflake, bigquery | Hex-encoded digest strings | | `sha256` | string | duckdb, trino, hive, snowflake, bigquery | | | `levenshtein(other: string)` | int | duckdb, trino, hive, snowflake, bigquery | Edit distance between two strings | | `json_extract(path: string)` | json | duckdb, trino, hive, snowflake, bigquery | Extract a JSON value from a string holding JSON text | | `json_extract_string(path: string)` | string | duckdb, trino, hive, snowflake, bigquery | | ### Date Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `extract(field: string)` | int | all | | | `year` | int | all | | | `month` | int | all | | | `day` | int | all | | | `quarter` | int | all | | | `week` | int | all | | | `or_else(other: date)` | date | all | | | `truncate_to(unit: string)` | date | all | Truncate to the given unit ('year', 'quarter', 'month', 'week', 'day') | | `diff_days(other: date)` | long | all | Difference between this and the other date, in the given unit | | `diff_months(other: date)` | long | all | | | `diff_years(other: date)` | long | all | | | `between(l: date, r: date)` | boolean | all | | | `day_of_week` | int | duckdb, trino, hive, snowflake, bigquery | ISO day of week (1 = Monday, 7 = Sunday) and day of year | | `day_of_year` | int | duckdb, trino, hive, snowflake, bigquery | | | `add_days(n: int)` | date | duckdb, trino, hive, snowflake, bigquery | | | `add_months(n: int)` | date | duckdb, trino, hive, snowflake, bigquery | | | `add_years(n: int)` | date | duckdb, trino, hive, snowflake, bigquery | | | `format(pattern: string)` | string | duckdb, trino, hive, snowflake, bigquery | Format the date with a strftime-style pattern (e.g. '%Y-%m-%d') | | `last_day` | date | duckdb, trino, hive, snowflake, bigquery | The last day of the month of this date | ### Timestamp Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `extract(field: string)` | int | all | | | `year` | int | all | | | `month` | int | all | | | `day` | int | all | | | `hour` | int | all | | | `minute` | int | all | | | `second` | int | all | | | `quarter` | int | all | | | `week` | int | all | | | `or_else(other: timestamp)` | timestamp | all | | | `to_date` | date | all | | | `truncate_to(unit: string)` | timestamp | all | Truncate to the given unit ('year', 'month', 'day', 'hour', 'minute', 'second') | | `diff_seconds(other: timestamp)` | long | all | Difference between this and the other timestamp, in the given unit | | `diff_minutes(other: timestamp)` | long | all | | | `diff_hours(other: timestamp)` | long | all | | | `diff_days(other: timestamp)` | long | all | | | `between(l: timestamp, r: timestamp)` | boolean | all | | | `day_of_week` | int | duckdb, trino, hive, snowflake, bigquery | ISO day of week (1 = Monday, 7 = Sunday) and day of year | | `day_of_year` | int | duckdb, trino, hive, snowflake, bigquery | | | `add_seconds(n: int)` | timestamp | duckdb, trino, hive, snowflake, bigquery | Timestamp arithmetic via unix epoch seconds (Hive has no timestamp interval functions) | | `add_minutes(n: int)` | timestamp | duckdb, trino, hive, snowflake, bigquery | | | `add_hours(n: int)` | timestamp | duckdb, trino, hive, snowflake, bigquery | | | `add_days(n: int)` | timestamp | duckdb, trino, hive, snowflake, bigquery | | | `add_months(n: int)` | timestamp | duckdb, trino, snowflake | | | `add_years(n: int)` | timestamp | duckdb, trino, snowflake | | | `format(pattern: string)` | string | duckdb, trino, hive, snowflake, bigquery | Format the timestamp with a strftime-style pattern (e.g. '%Y-%m-%d %H:%M:%S') | | `to_unixtime` | long | duckdb, trino, hive, snowflake, bigquery | Unix epoch seconds of this timestamp | | `to_string` | string | duckdb, trino, hive, snowflake, bigquery | Render as 'YYYY-MM-DD HH:MM:SS'. Overridden per engine so the rendering is identical | ### JSON Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `json_extract(path: string)` | json | all | Extract the JSON value at the JSONPath expression (e.g. '$.store.book') | | `json_extract_string(path: string)` | string | duckdb, trino | Extract the value at the JSONPath expression as a plain string | ### Null Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `to_int` | int | all | | | `to_long` | long | all | | | `to_float` | float | all | | | `to_double` | double | all | | | `to_boolean` | boolean | all | | | `to_date` | date | all | | | `to_timestamp` | timestamp | all | | | `to_string` | string | all | | ### Array Values A column reference after `group by` also has an array type, so aggregation functions apply to it with the same syntax. | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `length` | int | duckdb, trino, hive, snowflake, bigquery | | | `size` | int | duckdb, trino, hive, snowflake, bigquery | | | `get(index: int)` | A | all | | | `count` | int | all | | | `count_distinct` | int | all | | | `count_if(cond: boolean)` | int | all | | | `count_approx_distinct` | int | trino, duckdb, snowflake, bigquery | Fast and memory-efficient approximate counting of distinct elements | | `arbitrary` | A | all | | | `any` | A | all | | | `min` | A | all | | | `max` | A | all | | | `min_by(expr: sql)` | A | all | | | `max_by(expr: sql)` | A | all | | | `to_array` | array[A] | all | | | `bool_and` | boolean | all | Aggregate boolean values of a grouped column | | `bool_or` | boolean | all | | | `string_agg(separator: string)` | string | duckdb, trino, hive, snowflake, bigquery | Concatenate grouped string values with the separator | | `exclude(arr: sql)` | array[A] | duckdb, trino | | | `exists` | boolean | all | | | `not_exists` | boolean | all | | | `contains(elem: A)` | boolean | all | True if the array contains the given element | | `mk_string` | string | duckdb, trino, hive, snowflake, bigquery | Concatenate the array elements into a string without a separator | | `mk_string(separator: string)` | string | duckdb, trino, hive, snowflake, bigquery | Concatenate the array elements into a string with the separator | | `distinct` | array[A] | all | Remove duplicate elements | | `flatten` | array[A] | all | Flatten an array of arrays into a single array | | `sort` | array[A] | duckdb, trino, hive, snowflake | | | `reverse` | array[A] | duckdb, trino, snowflake, bigquery | | | `index_of(elem: A)` | int | duckdb, trino, snowflake | 1-origin position of the first occurrence of the element (0 if not found) | | `concat(other: any)` | array[A] | duckdb, trino, snowflake, bigquery | | | `lag` | A | all | Value of the column at a preceding row of the window | | `lag(offset: int)` | A | all | | | `lag(offset: int, default: any)` | A | all | | | `lead` | A | all | Value of the column at a following row of the window | | `lead(offset: int)` | A | all | | | `lead(offset: int, default: any)` | A | all | | | `first_value` | A | all | First value of the window | | `last_value` | A | all | Last value of the window | | `nth_value(n: int)` | A | all | Value at the n-th row (1-origin) of the window | | `sum` | A | all | | | `avg` | A | all | | | `median` | A | all | | | `variance` | A | all | | | `stddev` | A | all | | | `stddev_pop` | A | all | | | `stddev_samp` | A | all | | | `var_pop` | A | all | | | `var_samp` | A | all | | | `approx_quantile(pos: double)` | A | trino, duckdb, hive, snowflake, bigquery | | ### Map Values | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `size` | int | all | Number of entries in the map | | `keys` | array[K] | all | | | `values` | array[V] | all | | | `contains_key(key: K)` | boolean | all | True if the map contains the given key | | `get(key: K)` | V | duckdb, trino, hive, snowflake | The value for the given key, or null if the key is absent | ### Top-Level Functions Called without a receiver value (window functions require an `over(...)` clause). | Function | Returns | Engines | Description | |----------|---------|---------|-------------| | `range(start: long, stop: long)` | array[long] | duckdb, trino | Integers from start (inclusive) to stop (exclusive), e.g. range(1, 4) = [1, 2, 3]. An empty array when stop \<= start. Trino limits a range to 10000 elements | | `range(start: long, stop: long, step: long)` | array[long] | duckdb, trino | Integers from start (inclusive) to stop (exclusive) in increments of step, e.g. range(0, 10, 3) = [0, 3, 6, 9] and range(3, 0, -1) = [3, 2, 1]. A step of 0 is an error on DuckDB and yields an empty array on Trino | | `ulid_string` | string | all | Generate a new ULID | | `row_number` | long | all | Sequential row number within the window (1, 2, 3, ...) | | `rank` | long | all | Rank with gaps after ties (1, 1, 3, ...) | | `dense_rank` | long | all | Rank without gaps after ties (1, 1, 2, ...) | | `percent_rank` | double | all | Relative rank in [0, 1]: (rank - 1) / (rows - 1) | | `cume_dist` | double | all | Cumulative distribution in (0, 1]: rows preceding or peer / rows | | `ntile(n: int)` | long | all | Bucket number when the window is divided into n groups | --- ## Standard Library Functions Wvlet ships with a standard library of functions that you call with dot syntax on column values, e.g. `name.upper` or `price.round(2)`. Functions compile to the SQL of the target database engine: when engines differ (e.g. DuckDB, Trino, Hive, Snowflake, and BigQuery), Wvlet picks the right SQL for the engine you are compiling for, so the same query works across engines. This page is a guided tour of the most common functions. For the complete listing generated from the library sources, see the [Standard Library Reference](stdlib-reference.md). ```wvlet from orders select customer_name.upper as customer, order_date.year as order_year, amount.round(2) as amount ``` Functions can be chained: ```wvlet from logs select message.trim.lower.replace('error', 'warning') as normalized ``` ### Type Conversions Available on all values: | Function | Description | |----------|-------------| | `x.to_string` | Cast to string | | `x.to_int` / `x.to_long` | Cast to a 64-bit integer | | `x.to_float` / `x.to_double` | Cast to a 64-bit floating point number | | `x.to_boolean` | Cast to boolean | | `x.to_date` | Cast to date | | `x.to_timestamp` | Cast to timestamp | ### Null Handling Available on all values: | Function | Description | |----------|-------------| | `x.is_null` | True if the value is null | | `x.is_not_null` | True if the value is not null | | `x.or_else(default)` | Return `default` if the value is null (SQL `coalesce`) | | `x.null_if(v)` | Return null if the value equals `v` (SQL `nullif`) | ### String Functions | Function | Description | |----------|-------------| | `s.length` | Number of characters | | `s.upper` / `s.lower` | Change case | | `s.trim` / `s.ltrim` / `s.rtrim` | Remove surrounding whitespace | | `s.reverse` | Reverse the characters | | `s.concat(other)` | Concatenate strings (`\|\|`) | | `s.substring(start)` | Substring from a 1-origin position | | `s.substring(start, length)` | Substring of the given length | | `s.replace(search, replacement)` | Replace all occurrences | | `s.lpad(length, pad)` / `s.rpad(length, pad)` | Pad to the given length | | `s.strpos(substr)` | 1-origin position of a substring (0 if absent) | | `s.contains(substr)` | True if the string contains the substring | | `s.starts_with(prefix)` / `s.ends_with(suffix)` | Prefix/suffix test | | `s.like(pattern)` | SQL LIKE match | | `s.regexp_like(pattern)` | True if the regex matches | | `s.regexp_extract(pattern)` | First substring matching the regex | | `s.regexp_extract(pattern, group)` | Regex capture group | | `s.regexp_replace(pattern, replacement)` | Replace every regex match | | `s.split(separator)` | Split into an `array[string]` | | `s.levenshtein(other)` | Edit distance | | `s.md5` / `s.sha256` | Hex-encoded digest | | `s.json_extract(path)` | Extract a JSON value with a JSONPath (e.g. `'$.a.b'`) | | `s.json_extract_string(path)` | Extract a JSON value as a plain string | ### Math Functions Available on numeric values (int, long, float, double, decimal): | Function | Description | |----------|-------------| | `x.abs` | Absolute value | | `x.ceil` / `x.floor` | Round up / down | | `x.round(digits)` | Round to the given number of digits | | `x.truncate` | Drop the fractional part | | `x.sqrt` / `x.cbrt` | Square/cube root | | `x.exp` / `x.ln` / `x.log10` / `x.log2` | Exponential and logarithms | | `x.power(exponent)` | Raise to a power | | `x.mod(divisor)` | Modulo | | `x.sign` | -1, 0, or 1 | | `x.between(low, high)` | Range test | | `x.in(v1, v2, ...)` / `x.not_in(...)` | Set membership | On float/double values: `x.is_nan`, `x.is_finite`, `x.is_infinite`, and trigonometric functions (`sin`, `cos`, `tan`, `asin`, `acos`, `atan`, `degrees`, `radians`). On integer values: `x.from_unixtime` interprets the number as unix epoch seconds and returns a timestamp. ### Date and Timestamp Functions On `date` and `timestamp` values: | Function | Description | |----------|-------------| | `d.year` / `d.month` / `d.day` | Calendar fields | | `d.quarter` / `d.week` | Quarter and ISO week of year | | `d.day_of_week` | ISO day of week (1 = Monday, 7 = Sunday) | | `d.day_of_year` | Day of year | | `d.truncate_to(unit)` | Truncate to `'year'`, `'month'`, `'day'`, ... | | `d.add_days(n)` / `d.add_months(n)` / `d.add_years(n)` | Date arithmetic | | `d.diff_days(other)` / `d.diff_months(other)` / `d.diff_years(other)` | Difference in the given unit | | `d.format(pattern)` | Format with a `'%Y-%m-%d'`-style pattern | | `d.last_day` | Last day of the month | | `d.extract(field)` | Extract an arbitrary field | Additionally on timestamps: `hour`, `minute`, `second`, `add_seconds(n)`, `add_minutes(n)`, `add_hours(n)`, `diff_seconds(other)`, `diff_minutes(other)`, `diff_hours(other)`, `to_unixtime` (epoch seconds), and `to_date`. ```wvlet from events where event_time.between('2024-01-01'.to_timestamp, '2024-12-31'.to_timestamp) select event_time.truncate_to('month') as month, event_time.format('%Y-%m-%d') as day ``` ### Array Functions On array values (e.g. from `split`, array literals, or `array_agg`): | Function | Description | |----------|-------------| | `a.size` / `a.length` | Number of elements | | `a.get(index)` | Element at a 1-origin index (same as `a[index]`) | | `a.contains(elem)` | True if the array contains the element | | `a.index_of(elem)` | 1-origin position of an element (0 if absent) | | `a.sort` | Sort ascending | | `a.reverse` | Reverse the order | | `a.distinct` | Remove duplicates | | `a.concat(other)` | Concatenate arrays | | `a.flatten` | Flatten an array of arrays | | `a.mk_string` / `a.mk_string(separator)` | Join elements into a string | ### Map Functions On map values (e.g. `map {"a": 1, "b": 2}`): | Function | Description | |----------|-------------| | `m.size` | Number of entries | | `m.keys` / `m.values` | Keys or values as an array | | `m.contains_key(key)` | True if the key is present | | `m.get(key)` | Value for the key, or null | ### Aggregation Functions After `group by`, a column reference represents the group's values, and these aggregation functions apply (see also [Aggregation](index.md#group-by)): | Function | Description | |----------|-------------| | `c.count` / `c.count_distinct` | Count rows / distinct values | | `c.count_if(cond)` | Count rows matching a condition | | `c.count_approx_distinct` | Fast approximate distinct count | | `c.min` / `c.max` / `c.sum` / `c.avg` | Basic aggregates | | `c.min_by(expr)` / `c.max_by(expr)` | Value at the row minimizing/maximizing `expr` | | `c.arbitrary` | Any value of the group | | `c.to_array` | Collect values into an array | | `c.string_agg(separator)` | Concatenate strings with a separator | | `c.bool_and` / `c.bool_or` | Boolean aggregates | | `c.median` | Median value | | `c.stddev` / `c.variance` | Sample standard deviation / variance | | `c.stddev_pop` / `c.stddev_samp` / `c.var_pop` / `c.var_samp` | Population/sample variants | | `c.approx_quantile(pos)` | Approximate quantile (e.g. `0.95`) | ```wvlet from orders group by customer_id agg _.count as order_count, amount.sum as total, amount.approx_quantile(0.95) as p95, product.string_agg(',') as products ``` ### Window Functions Window (analytic) functions compute a value over a set of rows related to the current row, selected with an `over(...)` clause: | Function | Description | |----------|-------------| | `row_number()` | Sequential row number within the window (1, 2, 3, ...) | | `rank()` / `dense_rank()` | Rank with / without gaps after ties | | `percent_rank()` / `cume_dist()` | Relative rank / cumulative distribution | | `ntile(n)` | Bucket number when the window is divided into `n` groups | | `c.lag` / `c.lag(offset)` / `c.lag(offset, default)` | Value from a preceding row | | `c.lead` / `c.lead(offset)` / `c.lead(offset, default)` | Value from a following row | | `c.first_value` / `c.last_value` / `c.nth_value(n)` | Value at a window position | Aggregation functions (`sum`, `avg`, `count`, ...) also accept an `over(...)` clause. The window clause supports `partition by`, `order by`, and row frames (`rows[-1, 0]`): ```wvlet from orders select customer_id, order_date, row_number() over (partition by customer_id order by order_date) as nth_order, amount.lag over (partition by customer_id order by order_date) as prev_amount, amount.sum over (partition by customer_id) as customer_total ``` ### Engine-Specific Functions Functions above compile to each target engine's SQL automatically. You can also define your own functions, including engine-specific variants, selected by the compile target: ```wvlet -- Selected when compiling for DuckDB def bit_count(x: long) in duckdb: int = sql"bit_count(${x})" -- Selected when compiling for Trino def bit_count(x: long) in trino: int = sql"bitwise_bit_count(${x})" ``` Supported dialect contexts include `duckdb`, `trino`, `hive`, `snowflake`, and `bigquery`. Note that pattern strings remain engine-specific even when the function name is mapped: `format` takes a strftime-style pattern on DuckDB and BigQuery (`'%Y-%m-%d'`), a MySQL-style pattern on Trino (`'%Y-%m-%d'` with `%i` for minutes), a Java SimpleDateFormat pattern on Hive (`'yyyy-MM-dd'`), and a SQL format model on Snowflake (`'YYYY-MM-DD'`). Similarly, Hive's `split` treats the separator as a regular expression, and Snowflake's JSON paths omit the leading `$.`. All DuckDB and Trino engine functions are bundled with the standard library, so calls like `bit_count(x)` type-check offline out of the box and compile to the SQL of the engine you target. For other databases, or engine-specific UDFs, import the engine's function catalog with [`wvlet catalog import`](../usage/catalog-import.md). --- ## Managing Tables and Schemas Wvlet provides native statements for creating and managing database schemas and tables, so pipeline scripts no longer need `execute sql"..."` escapes for everyday catalog work. ### Schema Management Create or drop a database schema (namespace). Modifiers like `if not exists` trail the name, following Wvlet's left-to-right style: ```wvlet create schema staging create schema staging if not exists drop schema staging drop schema staging if exists ``` A schema can carry a self-describing storage location with `in ''`, and engine properties with `with key: value` options. Both render as schema properties on engines that support them (e.g. Trino's `WITH (location = ...)`) and are rejected with a clear error on engines that don't (e.g. DuckDB): ```wvlet create schema sales in 's3://bucket/sales/' if not exists create schema sales in 's3://bucket/sales/' with owner: 'etl' ``` To drop a schema together with the objects it contains, pass `cascade: true` — an engine modifier riding in an option, keeping the keyword surface flat: ```wvlet drop schema staging if exists with cascade: true ``` ### Declaring a Table Shape A `table` declaration describes a table's columns using the same `name: type` notation as `type` definitions. The declaration itself is side-effect free — it only registers the schema for type checking, so declarations can live in shared files that other scripts `import` without touching the database: ```wvlet table users = { id: int name: string created_at: timestamp } ``` #### Column Defaults A column can declare a default value with `= expr`. The default renders as a `DEFAULT` clause in the generated `CREATE TABLE` — both for explicit `create table` actions and for tables auto-created by `append to` — so the engine fills omitted columns on insert: ```wvlet table users = { id: int name: string active: boolean = true created_at: timestamp = now() } create table users -- Appending only a subset of columns: active and created_at get their defaults append to users (id, name) { from new_signups select id, name } ``` Defaults compose with `like` and `extends`: a reused or mixed-in column keeps its declared default. Engines whose `CREATE TABLE` grammar has no `DEFAULT` clause (e.g. Trino) reject a create with column defaults at compile time instead of silently dropping them. #### Column Constraints Columns can declare `primary key`, `unique`, and `not null` constraints as soft words after the type, making the declaration faithful to production tables and letting the engine enforce integrity: ```wvlet table users = { id: int primary key email: string unique not null name: string active: boolean not null = true } ``` Constraints render into the generated `CREATE TABLE` — for explicit `create table` actions and auto-created append targets alike — and compose with `like` and `extends` the same way defaults do. Engines without column constraints in `CREATE TABLE` (e.g. Trino) reject the create at compile time. Keyed `append to ... on ` works without any declared constraints (its MERGE lowering deliberately requires none); declaring them simply lets the engine enforce what the pipeline already assumes. #### Reusing a Shape with `like` `table like ` declares a second table with the same columns as an existing declaration, so a shape is written exactly once. Everything that works for a normal declaration — type checking, `create table`, automatic creation on write — reads its columns through the `like` reference: ```wvlet table users_backup like users from users append to users_backup -- auto-creates users_backup with users' columns if missing ``` #### Composing Shapes with `extends` Where `like` copies one declaration exactly, `extends a, b, c` composes partial shapes: each parent in the comma-separated list contributes its columns (structural `type` shapes and `table` declarations) or its methods (`trait` parents), followed by the declaration's own body columns: ```wvlet type timestamped = { created_at: timestamp updated_at: timestamp } trait auditable = { def is_recent: boolean = created_at > now() - interval '7 days' } table events extends timestamped, auditable = { id: int label: string } -- events has columns (created_at, updated_at, id, label), and auditable's methods from events where _.is_recent ``` Mixed-in columns come before own body columns, in parent-list order. A column arriving through two parents with the same type (a diamond) appears once; the same name with conflicting types is a compile-time error. `create table` and automatic creation on write materialize the full composed shape. `extends` and `like` cannot combine on one declaration — `like` is the exact-copy form. #### Binding a Declaration to a Location `in .` binds a declaration to the table's location, so references type-check and compile to qualified scans without a live catalog connection, and writes land at the bound location (see [Data Models](data-models.md) for the resolution rules): ```wvlet table events in mydb.analytics = { id: int label: string } -- Reads and writes both resolve to mydb.analytics.events from analytics.events create table analytics.events if not exists ``` This is the form [`wvlet catalog import`](../usage/catalog-import.md) generates for every table of your database. #### Row Methods A declaration body can also carry `def` members — reusable expressions over the table's columns — so behavior travels with the table declaration. Methods resolve on any relation that carries the declared type: a direct scan of the table, or a model annotated with the type (`model all_users: users = { ... }`): ```wvlet table users = { id: int name: string deleted_at: timestamp def is_active: boolean = deleted_at is null } from users where _.is_active ``` ### Creating and Dropping Tables The `create table` action materializes a declared shape in the target database. It takes nothing after the table name — the columns come from the `table` declaration — and it follows SQL semantics exactly, so there is nothing new to learn: ```wvlet create table users -- fresh create; error if the table already exists create table users if not exists -- create if missing; silently skip otherwise create or replace table users -- drop and recreate (empties the table) drop table users drop table users if exists truncate users -- delete all rows, keep the table ``` Running `create table` without a matching `table` declaration in scope is a compile-time error. In most pipelines an explicit `create table` is unnecessary: writing to a declared table with `save to` or `append to` creates it automatically (see below). `create table` exists for tables that only external systems write to. ### Writing Query Results to Tables The `save to` and `append to` operators work in two spellings that compile identically. The *flow* form ends a query, which is convenient while exploring interactively: ```wvlet from orders where amount > 0 save to cleansed_orders ``` The *block* form leads with the effect, which makes committed pipeline scripts easier to scan — each statement announces what it writes before the query details: ```wvlet save to cleansed_orders { from orders where amount > 0 } append to events(id, name) { from staged_events } ``` #### Seeding a Table Once `save to` replaces the target table by default. Adding `if not exists` makes it a seed-once operation: the whole statement is a no-op when the table already exists, so re-running the script never overwrites or duplicates data: ```wvlet save to calendar if not exists { from date_dimension_source } ``` #### Automatic Table Creation Writing to a table that does not exist yet creates it — no `create table` step is needed. `save to` always creates (or replaces) the target. `append to` creates the missing table before inserting; when a `table` declaration is in scope, the table is created with the *declared* column types, so the declaration — not the first query's inferred types — decides the shape: ```wvlet table events = { id: long name: string } -- Runs in a completely fresh database: creates `events` with (id: long, name: string), -- then inserts the rows append to events { from [[1, 'click'], [2, 'view']] as t(id, name) } ``` | Statement | Table missing | Table exists | |-----------|---------------|--------------| | `save to t` | Created from the query | Replaced with the new query result | | `save to t if not exists` | Created from the query | No-op (seed once) | | `append to t` | Created (declared shape if available), then rows inserted | Rows are appended | | `create table t` | Created from the declaration | Error (SQL semantics) | | `create table t if not exists` | Created from the declaration | No-op | | `create or replace table t` | Created from the declaration | Dropped and recreated empty | ### Updating Rows The `update` operator follows the same pattern as `delete`: select the rows first with `where`, then say what changes. The input must be filters over a single table. ```wvlet from users where last_login < current_date() - '1 year':interval update status = 'dormant', updated_at = now() ``` ### Keyed Append (Insert or Update) `append to ... on ` is insert-or-update: rows whose key columns match an existing row replace its non-key columns; the rest insert. Plain `append to` stays pure insert. It works in both flow and block forms and compiles to `MERGE INTO`: ```wvlet from staged_users append to users on user_id append to metrics on date, metric_name { from daily_metrics } ``` ### Changing a Table's Columns The `reshape` statement evolves a table's schema using the same column operators you already use in queries — `add`, `rename ... as`, `exclude`, and `cast ... as` — enclosed in a block. Operations run in order, one `ALTER TABLE` statement each: ```wvlet reshape users { add email: string rename name as full_name exclude age cast user_id as long } ``` Each operation is retry-safe: `add` is a no-op when the column already exists with that type, `exclude` when it is already gone, and `cast` when the column already has the target type. When a table declaration and the database schema have drifted apart, [`wvlet catalog diff`](../usage/catalog-import.md#detecting-schema-drift) generates a ready-to-run `reshape` block for the migration. ### Renaming Tables and Schemas Renaming changes an object's identity rather than its shape, so it is a direct statement: ```wvlet rename table users to customers rename schema staging to archive ``` ### Views A view exports a query to engine-side consumers (BI tools, other SQL users). Like `save to`, view creation works in both spellings and is create-or-replace: ```wvlet -- Flow form: develop the query, then persist it from users where active = true save as view active_users -- Block form: lead with the effect in pipeline scripts save as view active_users { from users where active = true } drop view active_users drop view active_users if exists ``` ### Attaching External Databases `use '' as ` attaches an external database under an alias, making its tables addressable as `.`. The path is self-describing: a file path attaches a database file, and a URI scheme such as `postgres://` selects the matching engine automatically. ```wvlet -- Attach a local DuckDB file use 'archive.duckdb' as archive from archive.events where year = 2025 -- Attach a remote PostgreSQL database (engine inferred from the scheme) use 'postgres://host/mydb' as pg -- Engine-specific modifiers ride in trailing options use 'archive.duckdb' as archive with read_only: true ``` Attachment is currently supported on DuckDB. Options are passed through to the engine (`read_only: true` becomes `READ_ONLY`); `engine: ''` overrides the engine type inferred from the path. --- ## Test Syntax In wvlet, you can insert `test` statements in the middle of a query. For example, you can test the number of rows, column names, or the query result itself: ```json title='person.json' [ {"id":1, "name": "alice", "age": 10 }, {"id":2, "name": "bob", "age": 24 }, {"id":3, "name": "clark", "age": 40 } ] ``` ```wvlet from 'person.json' -- Test the query size and schema test _.size should be 3 test _.columns should be ['id', 'name', 'age'] test _.columns should contain 'name' test _.columns should not contain 'address' -- Test the query output. Surrounding white spaces will be trimmed: test _.output should be """ ┌──────┬────────┬──────┐ │ id │ name │ age │ │ long │ string │ long │ ├──────┼────────┼──────┤ │ 1 │ alice │ 10 │ │ 2 │ bob │ 24 │ │ 3 │ clark │ 40 │ ├──────┴────────┴──────┤ │ 3 rows │ └──────────────────────┘ """ -- Test the query result in JSON format test _.json should be """ {"id":1,"name":"alice","age":10} {"id":2,"name":"bob","age":24} {"id":3,"name":"clark","age":40} """ test _.json should contain """{"id":2,"name":"bob","age":24}""" test _.json should not contain """{"id":4,"name":"david","age":32}""" -- Test rows with arrays test _.rows should contain [1, "alice", 10] test _.rows should contain [2, "bob", 24] test _.rows should not contain [5, "eric", 18] test _.rows should be [ [1, "alice", 10], [2, "bob", 24], [3, "clark", 40], ] ``` These tests will be ignored in the default query execution. Only in the test-run mode, these test expressions will be evaluated. ### Result Inspection Functions | Syntax | Output | |-----------|----------------------------------------| | _.size | The number of rows in the query result | | _.columns | The column names in the query result | | _.output | Pretty print query result | | _.json | JSON line format of result rows | | _.rows | rows in array of array representation | ### Test Expressions | Syntax | Description | |-----------|----------------------------------------------| | `x` should be `y` | Test if the value of `x` is equal to `y` | | `x` should not be `y` | Test if the value of `x` is not equal to `y` | | `x` should contain `y` | Test if the string value of `x` contains `y` | | `x` should not contain `y` | Test if the string value of `x` does not contain `y` | | `x` = `y` | Test if the value of `x` is equal to `y` | | `x` is `y` | Test if the value of `x` is equal to `y` | | `x` != `y` | Test if the value of `x` is not equal to `y` | | `x` is not `y` | Test if the value of `x` is not equal to `y` | | `x` `(cmp)` `y` | Compare `x` and `y` with `<`, `<=`, `=>`, `>` | --- ## Unnest The `unnest` function is used to expand an array into table rows: ```wvlet from unnest([1, 2, 3]) as t(number) ``` ``` ┌────────┐ │ number │ │ int │ ├────────┤ │ 1 │ │ 2 │ │ 3 │ ├────────┤ │ 3 rows │ └────────┘ ``` If used with other columns, `unnest` will expand the array into rows and duplicate the other columns: ```wvlet select unnest([1, 2, 3]), 10 ``` ``` ┌──────────────────────────┬─────┐ │ unnest((ARRAY[1, 2, 3])) │ 10 │ │ int │ int │ ├──────────────────────────┼─────┤ │ 1 │ 10 │ │ 2 │ 10 │ │ 3 │ 10 │ ├──────────────────────────┴─────┤ │ 3 rows │ └────────────────────────────────┘ ``` Unnest can be used to expand an array column as individual rows if it is used with `cross join`: ```wvlet val tests(student, scores) = [ ['John', [7, 10, 9]], ['Mary', [4, 8, 9]], ] from tests cross join unnest(scores) as t(score) select student, score ``` ``` ┌─────────┬───────┐ │ student │ score │ │ string │ int │ ├─────────┼───────┤ │ John │ 7 │ │ John │ 10 │ │ John │ 9 │ │ Mary │ 4 │ │ Mary │ 8 │ │ Mary │ 9 │ ├─────────┴───────┤ │ 6 rows │ └─────────────────┘ ``` --- ## Window Function Window function computes the data against the whole query result. - partition by (key): Split the query result into multiple partitions based on the key. - order by (key): Define ordering of the rows within each partition. - rows [start:end]: Define the range of rows in each window. This query calculates the total amount of orders for each customer: ```wvlet from orders select sum(amount) over (partition by customer_id order by order_date) ``` ### Window Frame In Wvlet, you can specify range of rows in each window with the following syntax: | Syntax | Window Frame in SQL | | --- | --- | | rows [,0] | rows between unbounded preceding and current row (default) | | rows [-1,1] | rows between 1 preceding and 1 following | | rows [-1,0] | rows between 1 preceding and current row | | rows [0,1] | rows between current row and 1 following | | rows [0,] | rows between current row and unbounded following | `rows [,0]` is the default window frame if no frame is specified. This means the window frame includes all rows from the first row of the partition to the current row. To explicitly specify the window frame, you can use the `rows` keyword as follows: ```wvlet from orders select amount.sum() over ( partition by customer_id order by order_date rows [,0] ) as cumulative_amount ``` --- ## Importing Database Catalogs `wvlet catalog import` scans your database once and saves each table's schema as Wvlet `table` declarations in the `catalog/` folder of your project. With those files committed next to your queries, the compiler resolves table references and their column types **without connecting to the database** — in your editor, in CI, or when working offline. ```bash # Import from a profile defined in ~/.wvlet/profiles.json wvlet catalog import --profile production # Limit the import to one schema of a large warehouse wvlet catalog import --profile production --schema sales ``` The generated files are ordinary Wvlet source files, one per schema: ```wvlet title='catalog/mydb/sales.wv' -- Generated by `wvlet catalog import`. DO NOT EDIT. -- Table declarations of mydb.sales, bound for offline query validation. -- Re-run `wvlet catalog import` to refresh; add hand-written declarations in your own files. table customers in mydb.sales = { customer_id: long name: string } table orders in mydb.sales = { order_id: long status: string price: decimal[10,2] } ``` Each declaration is bound to its table location with `in .` (see [Data Models](../syntax/data-models.md)), so queries referencing those tables type-check against the saved schema and compile to correctly qualified SQL: ```wvlet from sales.orders where status = 'completed' select order_id, price ``` ### Engine functions The import also saves the functions of your database engine as native Wvlet function definitions in `catalog//functions.wv`, so engine-specific functions — not just tables — work offline: ```wvlet title='catalog/mydb/functions.wv' -- Generated by `wvlet catalog import`. DO NOT EDIT. -- Functions of the duckdb engine, bound for offline query validation. -- Re-run `wvlet catalog import` to refresh; add hand-written defs in your own files. def date_trunc(a1: string, a2: timestamp) in duckdb: timestamp = native def regexp_extract(a1: string, a2: string) in duckdb: string = native ``` Calls to these functions compile to plain SQL calls of the target engine, exactly as if the definition did not exist — but with the function available in editor completion (with its signature) and its return type known to the compiler: ```wvlet from sales.orders select regexp_extract(status, '[a-z]+') as status_word ``` A few notes on what gets imported: - Only scalar, aggregate, and window functions are imported. Table functions, pragmas, and macros use a different call syntax and are skipped. - Functions whose names collide with Wvlet keywords, builtin functions the compiler already types (`count`, `upper`, ...), or standard-library definitions are skipped, so an import can never change how an existing query compiles. - An overloaded function is collapsed into a single definition with `any` argument types, so the compiler accepts every call the engine would. - Pass `--no-functions` to skip the functions import (a later full import without the flag refreshes the file; with the flag it is removed again). - If one of your own files defines a function with the same name, the compiler reports a duplicate-definition warning and deterministically uses the definition from the first file in source-file name order. Rename one of them to remove the ambiguity. ### Duplicate table names across schemas When two schemas define a table with the same name (e.g. `mydb.sales.orders` and `mydb.marketing.orders`), the import writes a `table orders in ...` declaration into both schema files. The compiler can only resolve the name through one of them, so it reports a warning: ``` [warning] Duplicate type definition 'orders' (bound to mydb.marketing): also defined at catalog/mydb/sales.wv:1:1 (bound to mydb.sales). Only the definition in catalog/mydb/sales.wv is used for name lookup. ``` Queries that reference the winning definition keep working; references to the other schema's table may not resolve to the schema you expect. To fix the warning, keep only the schema you query (`wvlet catalog import --schema sales`), or remove one of the definitions — for hand-written declarations, rename one of them. The warning never fails the compilation, even with `wvlet compile --strict`. ### Keeping catalogs up to date Re-running `wvlet catalog import` overwrites the generated files. The output is deterministic — tables sorted by name, columns in table order — so `git diff` after an import shows exactly what changed in the database: ```bash wvlet catalog import --profile production git diff catalog/ # review schema drift git add catalog/ && git commit -m "Update catalog snapshot" ``` Committed catalog files behave like a lockfile: the compile-time schema stays deterministic even while the database evolves, and queries still execute against the real tables. Schedule the import in CI or cron to keep the snapshot fresh. ### Detecting schema drift `wvlet catalog diff` compares every `table` declaration of your project — imported catalog files and hand-written declarations alike — against the connected database, and reports where the two have drifted apart. For each drifted table it prints a ready-to-run [`reshape`](../syntax/table-management.md) migration block: ```bash $ wvlet catalog diff --profile production table users has drifted from its declaration. To migrate, run: reshape users { add created_at: timestamp exclude legacy_flag cast user_id as long -- the catalog has int } ``` - `reshape` operations have *ensure* semantics (`add` is a no-op when the column already exists; `exclude` when it is already gone; `cast` when the column already has the target type), so a generated migration is safe to re-run. - A rename cannot be inferred from a diff — it shows up as an `exclude`/`add` pair, which would drop the column's data. When the output contains such a pair, the block includes a hint: replace the pair with a hand-written `rename as ` to preserve the data. - Column type drift becomes a `cast as ` operation, with the current catalog type noted in a trailing comment. - Declared tables that do not exist in the database are not drift: a declared table materializes automatically on the first write to it. The command exits with a non-zero status when any drift is found, so it can gate CI: ```yaml # GitHub Actions example - name: Check schema drift run: wvlet catalog diff --profile production ``` #### Applying the migrations Pass `--apply` to run the generated `reshape` blocks against the database (sqldef / `db:migrate` style). The migrations execute through the regular compile-and-run path — exactly what pasting the blocks into a script would do — and the command re-diffs afterwards, exiting zero only when the catalog now matches the declarations: ```bash $ wvlet catalog diff --profile production --apply table users has drifted from its declaration. To migrate, run: reshape users { add created_at: timestamp exclude legacy_flag } [info] Applied 1 migration(s); the catalog matches the declarations ``` Review the printed blocks before reaching for `--apply` on a production database: `exclude` drops the column *and its data*, and a rename always diffs as an exclude/add pair — apply a hand-written `rename as ` first when that is what actually happened. A typical workflow gates CI with the plain check and runs `--apply` as an explicit deploy step. ### Validating queries in CI Because the catalog is plain source, query compilation needs no database credentials. Compile each query to catch syntax errors and — with `--strict` — the typing errors the compiler detects: ```yaml # GitHub Actions example - name: Validate queries run: | for query in queries/*.wv; do wvlet compile --strict -f "$query" done ``` ### Editor support The [VS Code extension](./vscode.md) loads the `catalog/` folder of your workspace automatically: after an import, table references resolve in the editor — diagnostics, column completion, and hover work offline against the imported schemas, and imported engine functions appear in name completion with their signatures. Destructive statements are checked against the snapshot too: a `drop table`, `reshape`, or `truncate` targeting a table the imported catalog does not know gets an editor **warning** (not an error — the snapshot may be stale; re-run `wvlet catalog import` if the table was created after the import). Adding `if exists` to the statement acknowledges the table may be missing and silences the warning. ### Notes - Table and column names that are not plain identifiers are written as backquoted identifiers (e.g. `` `weird-table` ``). - Column types that cannot be expressed in Wvlet's type syntax degrade to `any`, keeping the original type in a comment. - System schemas (`information_schema`, `pg_catalog`, `system`) are skipped. - The `catalog/` folder is owned by the importer. Add your own `type` and `model` definitions in separate `.wv` files so a re-import never overwrites them. --- ## CLI Reference This page provides a comprehensive reference for all Wvlet CLI commands. :::note The `wv` command is a shortcut available in the REPL environment. For all other contexts, use the full `wvlet` command. ::: ### Global Options These options can be used with any command: ```bash wvlet [global options] [command options] ``` | Option | Description | |--------|-------------| | `-h, --help` | Display help message | | `--version` | Display version | | `--debug` | Enable debug logging | | `-l ` | Set log level (ERROR, WARN, INFO, DEBUG, TRACE) | | `-L =` | Set log level for specific class patterns | | `--profile ` | Use a specific connection profile | | `-w ` | Set working directory | ### Commands #### `wvlet compile` Compile Wvlet queries to SQL. ```bash wvlet compile [options] [query] ``` **Options:** | Option | Description | |--------|-------------| | `-f, --file ` | Compile queries from a file | | `-t, --target ` | Target database type (duckdb, trino) | | `--catalog ` | Use specific catalog | | `--schema ` | Use specific schema | | `--strict` | Fail compilation on typing errors | **Examples:** ```bash # Compile inline query wvlet compile "from users select * limit 10" # Compile from file wvlet compile -f queries/analysis.wv # Compile for specific target wvlet compile -f query.wv --target trino ``` #### `wvlet catalog import` Import database table schemas as Wvlet type definitions, and engine functions as Wvlet function definitions, for offline query validation. See [Importing Database Catalogs](./catalog-import.md). ```bash wvlet catalog import [options] ``` **Options:** | Option | Description | |--------|-------------| | `--profile ` | Profile to connect with | | `--catalog ` | Catalog to import (default: profile catalog) | | `--schema ` | Import only the specified schema | | `--path ` | Output folder (default: `catalog`) | | `--no-functions` | Skip importing engine functions | **Examples:** ```bash # Import all schemas of the profile's catalog wvlet catalog import --profile production # Import a single schema wvlet catalog import --profile production --schema sales ``` #### `wvlet run` Run Wvlet queries and display results. ```bash wvlet run [options] [query] ``` **Options:** | Option | Description | |--------|-------------| | `-f, --file ` | Run queries from a file | | `-t, --target ` | Target database type | | `--catalog ` | Use specific catalog | | `--schema ` | Use specific schema | | `--format ` | Output format (table, json, csv, tsv) | | `--limit ` | Limit result rows | **Examples:** ```bash # Run inline query wvlet run "from users select count(*)" # Run from file wvlet run -f queries/report.wv # Run with specific format wvlet run -f query.wv --format json # Run with row limit wvlet run "from large_table select *" --limit 1000 ``` #### `wvlet ui` Start the Wvlet Web UI server. ```bash wvlet ui [options] ``` **Options:** | Option | Description | |--------|-------------| | `-p, --port ` | Server port (default: 8080) | | `--host ` | Server host (default: localhost) | | `--no-browser` | Don't open browser automatically | **Examples:** ```bash # Start UI on default port wvlet ui # Start on custom port wvlet ui --port 9000 # Start without opening browser wvlet ui --no-browser ``` ### Configuration #### Profile Configuration Create profiles in `~/.wvlet/profiles.json` (parsed as JSONC: comments and trailing commas are allowed, and `${ENV_VAR}` references are expanded from the environment). A profile describes a working environment with one or more named connectors; the connector marked `"default": true` (or the first one) is the engine queries run on: ```jsonc { "profiles": [ { "name": "local", "connectors": [ { "name": "duckdb", "type": "duckdb", "catalog": "memory", "schema": "main" } ] }, { "name": "production", "connectors": [ { "name": "trino", "type": "trino", "default": true, "host": "trino.example.com:8080", "catalog": "production", "schema": "analytics", "user": "myuser", "password": "${TRINO_PASSWORD}" }, { "name": "local", "type": "duckdb", "catalog": "memory", "schema": "main" } ] } ] } ``` Use profiles with `--profile` option: ```bash wvlet run -f query.wv --profile production ``` #### Environment Variables | Variable | Description | |----------|-------------| | `WVLET_HOME` | Wvlet home directory (default: ~/.wvlet) | | `WVLET_LOG_LEVEL` | Default log level | | `WVLET_DB_TYPE` | Default database type | ### Command Shortcuts The `wv` command is a shortcut for the Wvlet REPL: ```bash # Start REPL wv # Run REPL command wv "from users select count(*)" # Access other wvlet commands through wv wv compile -f query.wv ``` ### Examples #### Typical Development Workflow ```bash # 1. Write and test queries wvlet compile -f query.wv # 2. Run queries against database wvlet run -f query.wv # 3. Start UI for interactive development wvlet ui ``` #### CI/CD Pipeline Example ```bash #!/bin/bash # ci-validate-queries.sh # Validate query syntax find queries -name "*.wv" | while read file; do echo "Validating: $file" wvlet compile -f "$file" done ``` #### Batch Processing Example ```bash # Process multiple queries for query in daily/*.wv; do output="${query%.wv}.sql" wvlet compile -f "$query" > "$output" echo "Generated: $output" done # Run all reports for report in reports/*.wv; do name=$(basename "$report" .wv) wvlet run -f "$report" --format csv > "output/${name}.csv" done ``` ### See Also - [Installation Guide](./install.md) - [REPL Usage](./repl.md) - [Web UI](./ui.md) --- ## Generate SQL Queries `wvlet compile` commands generate SQL queries from the given input Wvlet query. ```bash $ wvlet compile "from lineitem limit 10" -- wvlet version=2024.9.10, src=01JC1T63TKWNRQ1BF57P3AW7Z2.wv:1 select * from lineitem limit 10 ``` ### Compile SQL from .wv file It's also possible to compile a query from a file: ```wvlet title="query.wv" execute sql"call dbgen(sf=0.01)" from lineitem group by l_returnflag agg _.count as cnt ``` ```bash $ wvlet compile -f query.wv -- wvlet version=2024.9.10, src=query.wv:1 call dbgen(sf=0.01) ; -- wvlet version=2024.9.10, src=query.wv:5 select l_returnflag as l_returnflag, count(*) as cnt from lineitem group by l_returnflag ; ``` If you use DuckDB, you can run the generated SQL through pipe: ```bash $ wvlet compile -f query.wv | duckdb ┌─────────┐ │ Success │ │ boolean │ ├─────────┤ │ 0 rows │ └─────────┘ ┌──────────────┬───────┐ │ l_returnflag │ cnt │ │ varchar │ int64 │ ├──────────────┼───────┤ │ A │ 14876 │ │ N │ 30397 │ │ R │ 14902 │ └──────────────┴───────┘ ``` --- ## Working with Multiple Connectors A profile in `~/.wvlet/profiles.json` describes a working environment and can activate several connectors at once — for example a production Trino cluster next to a local DuckDB database: ```jsonc title='~/.wvlet/profiles.json' { "profiles": [ { "name": "dev", "connectors": [ { "name": "td", "type": "trino", "default": true, "host": "api-presto.treasuredata.com", "user": "${TD_API_KEY}", "catalog": "td", "schema": "sample_datasets" }, { "name": "local", "type": "duckdb", "catalog": "memory", "schema": "main" } ] } ] } ``` The connector marked `"default": true` (or the first one) is the engine your queries run on when the session starts. ### Referencing tables through a connector name Table references can name a connector explicitly. The first identifier is checked against the connector names of the active profile: ```sql -- .
: uses the connector's configured schema from td.www_access -- ..
from td.sample_datasets.www_access -- ...
: addresses a catalog other than the -- configured one, for engines spanning multiple catalogs (e.g. Trino) from trino.tpch.tiny.nation ``` Models, CTEs, and query aliases take precedence over connector names, and connector names take precedence over schema names of the default catalog. Since you choose connector names yourself, rename the connector if it collides with a schema you need to address. Queries execute on one SQL engine at a time: referencing another *engine* connector reports an error suggesting `use `. Tables of *source* connectors (services without a SQL engine, like [Slack](slack.md)) are staged into the active engine automatically, so `from slack.channels` works from any engine. Inside [flows](../syntax/flow.md), cross-connector references are staged automatically too, and each stage can pick its engine with `stage on `. Cross-engine joins in ad-hoc queries are planned as a follow-up. ### Switching connectors with `use` The `use` statement switches the active connector for subsequent statements. Its catalog then drives table resolution and the SQL dialect: ```sql use td -- switch to the td connector use local -- switch to the local DuckDB connector use connector td -- explicit form, for when a name collides with a schema use td.sample_datasets -- switch connector and schema in one statement ``` Bare names are resolved connector-first: `use analytics` switches to a connector named `analytics` when the profile defines one, and otherwise behaves like `use schema analytics` (see [CLI reference](cli-reference.md) for the schema forms). The switch is scoped to your session: in the web UI each browser page is its own session, so `use` statements of one client never change the engine, catalog, or schema of another. `use` works the same way in the lightweight CLIs (the native `wv` binary and the Node.js `npx @wvlet/cli`): it switches both the execution engine and the SQL dialect of the statements that follow. These thin clients compile without live catalog metadata, so table names should be written fully qualified when switching engines mid-script. ### Calling connector tools Connectors can expose callable tools (MCP-shaped methods) beyond tables and SQL execution. The `call` statement invokes a tool ad hoc, outside of a flow: ```sql call slack.post_message(channel: '#reports', text: 'daily report done') ``` Arguments are named and take literal values, following the tool's input schema. The statement returns a single-row summary (`connector`, `tool`, `status`, `content`) that composes with query operators and `test` statements like any query: ```sql call slack.post_message(channel: '#reports', text: 'ping') test _.columns should contain 'status' ``` Inside flows, use `activate('', tool: ...)` instead to deliver a stage's output through a tool (see [Flows](../syntax/flow.md)). --- ## Connector - DuckDB By default, wvlet uses in-memory database of DuckDB (catalog: memory, schema: main). ### Usage Examples ```bash $ wv wv> from 'https://shell.duckdb.org/data/tpch/0_01/parquet/customer.parquet' | group by c_mktsegment | agg _.count | limit 5; ┌──────────────┬──────────────┐ │ c_mktsegment │ count_star() │ │ string │ long │ ├──────────────┼──────────────┤ │ MACHINERY │ 288 │ │ HOUSEHOLD │ 294 │ │ AUTOMOBILE │ 302 │ │ FURNITURE │ 279 │ │ BUILDING │ 337 │ ├──────────────┴──────────────┤ │ 5 rows │ └─────────────────────────────┘ ``` To call some DuckDB internal functions, use raw SQL blocks: ```bash # Generate TPC-H dataset with scale factor 0.01 wv> from sql"call dbgen(sf=0.01)"; ┌─────────┐ │ Success │ │ boolean │ ├─────────┤ ├─────────┤ │ 0 rows │ └─────────┘ wv> show tables; ┌────────────┐ │ table_name │ │ string │ ├────────────┤ │ customer │ │ lineitem │ │ nation │ │ orders │ │ part │ │ partsupp │ │ region │ │ supplier │ ├────────────┤ │ 8 rows │ └────────────┘ wv> from orders limit 5; ┌────────────┬───────────┬───────────────┬───────────────┬─────────────┬──────────> │ o_orderkey │ o_custkey │ o_orderstatus │ o_totalprice │ o_orderdate │ o_orderpr> │ long │ long │ string │ decimal(15,2) │ date │ strin> ├────────────┼───────────┼───────────────┼───────────────┼─────────────┼──────────> │ 1 │ 370 │ O │ 172799.49 │ 1996-01-02 │ 5-LOW > │ 2 │ 781 │ O │ 38426.09 │ 1996-12-01 │ 1-URGENT > │ 3 │ 1234 │ F │ 205654.30 │ 1993-10-14 │ 5-LOW > │ 4 │ 1369 │ O │ 56000.91 │ 1995-10-11 │ 5-LOW > │ 5 │ 445 │ F │ 105367.67 │ 1994-07-30 │ 5-LOW > ├────────────┴───────────┴───────────────┴───────────────┴─────────────┴──────────> │ 5 rows > └─────────────────────────────────────────────────────────────────────────────────> ``` --- ## Installation Wvlet is available as a command-line tool. You can install Wvlet using Homebrew on macOS or download a binary package for other platforms. ### Command Line Tools - [wv (Interactive Shell (REPL))](repl.md) - [wvlet (Command-line tool)](cli.md) - [Web UI](ui.md) :::info Language SDKs are also available for [Python and TypeScript](../bindings) to compile Wvlet queries programmatically. ::: ### Mac OS X For macOS, you can install Wvlet using [Homebrew](https://brew.sh/): ```bash brew install wvlet/wvlet/wvlet ``` This will install `wv (wvlet)` command to your system. To upgrade Wvlet, run: ```bash brew upgrade wvlet ``` If the latest version is not installed, try `brew update` to update the formula, then run `brew upgrade wvlet` again. :::info The formula for homebrew can be found at https://github.com/wvlet/homebrew-wvlet ::: ### Linux, Windows, and Other Platforms For other platforms (e.g., Linux, Windows, etc.), download a binary package wvlet-cli-(version).tar.gz from the release page https://github.com/wvlet/wvlet/releases You can find wv command in the bin directory of the package. Add the bin directory to your PATH environment variable. JDK17 or later is required to run Wvlet. Set JAVA_HOME environment variable to the JDK path. ### Quick Start After installing Wvlet, start learning the query syntax of Wvlet: - [Query Syntax](../syntax) to learn the query syntax ### Language SDKs If you want to integrate Wvlet compilation into your applications, install one of our language SDKs: #### Python ```bash pip install wvlet ``` #### TypeScript/JavaScript ```bash npm install @wvlet/wvlet ``` See [Language Bindings](../bindings) for more details and usage examples. ### VS Code Extension For VS Code users, we provide a language extension that adds syntax highlighting and language support for `.wv` files: #### Installation Install directly from the [VS Code Marketplace](https://marketplace.visualstudio.com/items?itemName=wvlet.wvlet) or search for "Wvlet" in the Extensions view (`Ctrl+Shift+X` / `Cmd+Shift+X`) within VS Code. #### Features - Syntax highlighting for all Wvlet keywords, operators, and syntax - Bracket matching and auto-closing - Comment toggling support (`--` and `---`) - String interpolation highlighting - Number literal support (integers, floats, hex) The extension supports both stable and pre-release versions. Pre-release versions allow early access to new features. For full details, including troubleshooting and development guides, please see the [VS Code Extension documentation](./vscode.md). ### Building From Source See [Building Wvlet](../development/build.md) for instructions on building it from source. --- ## Remote Wvlet Server The `wvlet` CLI can run queries on a central wvlet server instead of a local engine. The server holds the profiles, catalogs, and database credentials; thin clients — including the native `wv` binary and the Node.js CLI (`npx @wvlet/cli`) — send the original Wvlet query text and receive structured results, with no local database engine or credentials required. ### Starting a server ```bash # Start a wvlet server on the default port 9090 wvlet ui --port 9090 --profile your-profile ``` The server compiles and executes queries with its own profile (`~/.wvlet/profiles.json` on the server host), so clients never need direct access to the underlying database. ### Running queries remotely Use `-t wvlet` with the server's host (and port, when it is not the default 9090 for HTTP or 443 for HTTPS): ```bash # Run a query on a remote wvlet server wvlet run -t wvlet --host wvlet.example.com --port 9090 "from lineitem select count(*)" # Run a query file wvlet run -t wvlet --host wvlet.example.com -f daily_report.wv # Use HTTPS (default port 443) wvlet run -t wvlet --host wvlet.example.com --https "from orders limit 10" ``` Each CLI invocation holds one server-side session, so multi-statement scripts keep their state — `use` statements and temporary tables work across statements within a run. A profile entry can hold the connection settings so you only pass `-p`: ```jsonc title='~/.wvlet/profiles.json' { "profiles": [ { "name": "remote", "connectors": [ { "name": "wvlet", "type": "wvlet", "default": true, "host": "wvlet.example.com", "port": 9090, "properties": { // Optional: run under this profile name defined on the SERVER "remoteProfile": "production" } } ] } ] } ``` ```bash wvlet run -p remote "from lineitem select count(*)" ``` ### Authentication To require a bearer token for all query (RPC) requests, start the server with a token. Prefer the environment variable so the secret stays out of the process list: ```bash WVLET_SERVER_TOKEN=your-secret-token wvlet ui --port 9090 ``` (`--auth-token your-secret-token` also works.) Clients supply the token through the profile's `token` property. Environment variables are interpolated with `${VAR}`, so the secret does not need to be written into the file: ```jsonc title='~/.wvlet/profiles.json' { "profiles": [ { "name": "remote", "connectors": [ { "name": "wvlet", "type": "wvlet", "default": true, "host": "wvlet.example.com", "port": 9090, "useHttps": true, "properties": { "token": "${WVLET_TOKEN}" } } ] } ] } ``` The token is sent as an `Authorization: Bearer` header on every request. Requests without a valid token are rejected with an authentication error; the static Web UI assets remain reachable without a token. Always combine token authentication with HTTPS (for example, behind a TLS-terminating reverse proxy) so the token is not sent in clear text. --- ## Interactive Shell Wvlet Shell (`wv` command) launches an interactive query editor for the console. In the shell, you can write SQL queries, run them, and see the results interactively. ![wvlet shell](/img/demo.gif) ### Commands By typing `help` in the shell, you can see the list of available commands: ```bash $ wv wv> help [commands] help : Show this help message quit/exit : Exit the REPL clear : Clear the screen context : Show current database context (catalog and schema) clip : Clip the last query and result to the clipboard clip-result: Clip the last result to the clipboard in TSV format rows : Set the maximum number of query result rows to display (default: 40) col-width : Set the maximum column width to display (default: 150) git : Run a git command in the shell gh : Run a GitHub command in the shell ``` ### Shortcut Keys Wvlet shell basically uses [GNU readline](https://readline.kablamo.org/emacs.html)-style shortcut keys. Here is the list of shortcut keys that are most frequently used in the shell: #### Navigation | Keys | Description | |---|----------------------------------------------------------------------------------| | ctrl-a | Home: Move to the beginning of the line | | ctrl-e | End: Move to the end of the line | | ctrl-j a | Move to the beginning of the query | | ctrl-j e | Move to the end of the query | | ctrl-p | Up | | ctrl-n | Down | | ctrl-f | Right | | ctrl-b | Left | | ctrl-r | Search the history. Type ctrl+r again to continue to search the previous history | | ctrl-k | Delete until the end of line from the cursor position | #### Running Queries | Keys | Description | |---|---| | ctrl-j ctrl-r | Run the whole query | | ctrl-j ctrl-t | Test run the query fragment up to the line with debug mode | | ctrl-j ctrl-d | Describe the schema of the current line of the query fragment | | ctrl-c | Cancel the current query, or exit the shell | ### Switching Database Context You can switch the database schema context in the REPL using the `use` statement: ```sql -- Simplified syntax (recommended for common case) wv> use analytics -- Explicit syntax (optional) wv> use schema analytics -- Switch to a schema in a different catalog wv> use production.analytics -- Show current context wv> context Current context: catalog=memory, schema=analytics ``` The context switch affects all subsequent queries in the REPL session. The simplified `use ` syntax is recommended for the common case of switching schemas within the same catalog. --- ## Connector - Slack The Slack connector exposes a Slack workspace as queryable tables and lets flows post messages back to channels. It is a *source* connector: it has no SQL engine of its own, so its tables are staged automatically into the active engine (e.g. DuckDB) when a query references them. ### Configuration Create a [Slack app](https://api.slack.com/apps) with a bot token that has the `channels:read`, `channels:history`, `users:read`, and `chat:write` scopes, then add a connector to your profile: ```jsonc title='~/.wvlet/profiles.json' { "profiles": [ { "name": "dev", "connectors": [ { "name": "duckdb", "type": "duckdb", "default": true, "catalog": "memory", "schema": "main" }, { "name": "slack", "type": "slack", "properties": { "token": "${SLACK_TOKEN}" } } ] } ] } ``` Optional properties: - **message_fetch_limit**: messages fetched per channel when scanning the `messages` table (default 1000) ### Tables | Table | Columns | |-------|---------| | `channels` | `id`, `name`, `is_private`, `num_members`, `topic` | | `users` | `id`, `name`, `real_name`, `is_bot` | | `messages` | `channel`, `user`, `text`, `ts`, `thread_ts` | ```sql -- Channels with the most members from slack.channels | order by num_members desc | limit 10 -- Message counts per channel from slack.messages | group by channel | agg count(*) as messages ``` Scanning `messages` reads the recent history of every channel the bot can access, so prefer filtering early and consider a lower `message_fetch_limit` for large workspaces. ### Posting messages from flows Every profile connector with tools is an activation target under its instance name. The `post_message` tool posts a stage's output (or an explicit `text:`) to a channel: ```sql flow daily_report = { stage summary = from slack.messages | group by channel | agg count(*) as messages stage notify = from summary | activate('slack', tool: 'post_message', channel: '#reports') } ``` When `text:` is omitted, the first rows of the stage output are attached as JSON lines. ### Posting messages ad hoc Outside of a flow, the `call` statement invokes a tool directly: ```sql call slack.post_message(channel: '#reports', text: 'hello from wvlet') ``` See [Working with Multiple Connectors](connectors.md#calling-connector-tools) for details on `call`. --- ## Connector - Snowflake Wvlet supports connecting to Snowflake databases via JDBC. ### Connecting to Snowflake To connect to Snowflake, create a profile in `~/.wvlet/profiles.json`. The file is parsed as JSONC, so `// line comments`, `/* block comments */`, and trailing commas are allowed. ```jsonc title='~/.wvlet/profiles.json' { "profiles": [ { "name": "snowflake", "connectors": [ { "name": "snowflake", "type": "snowflake", "user": "(your Snowflake user name)", "password": "(your password)", "host": "(your account identifier, e.g., myorg-myaccount)", "catalog": "(your database name)", "schema": "(your schema name, e.g., PUBLIC)", "properties": { "warehouse": "(your warehouse name)", "role": "(your role name, optional)" } } ] } ] } ``` Then connect using the profile: ```bash $ wv --profile snowflake wv> ``` ### Configuration Parameters - **host**: Your Snowflake account identifier (e.g., `myorg-myaccount`). The connector will automatically append `.snowflakecomputing.com`. - **user**: Your Snowflake username - **password**: Your Snowflake password - **catalog**: The database name to connect to - **schema**: The schema name (default: `PUBLIC`) - **properties.warehouse**: (Optional) The virtual warehouse to use - **properties.role**: (Optional) The role to use for the session ### Example Usage ```sql wv> from information_schema.tables where table_schema = 'PUBLIC' select table_name, table_type ``` ### Notes - Snowflake uses `ARRAY_CONSTRUCT()` for array creation and `OBJECT_CONSTRUCT()` for object/map creation - Wvlet will automatically translate standard SQL constructs to Snowflake-compatible syntax - Ensure your Snowflake account has the appropriate permissions and network access configured --- ## Connector - Trino ### Connecting to Trino A profile describes a working environment and can activate multiple connectors at once. Each entry in `connectors` is a named connection; the one marked `"default": true` (or the first one) is the engine your queries run on. The profile file is parsed as JSONC, so `// line comments`, `/* block comments */`, and trailing commas are allowed. ```jsonc title='~/.wvlet/profiles.json' { "profiles": [ { "name": "trino", "connectors": [ { "name": "trino", "type": "trino", "user": "(user name)", "password": "(password)", "host": "(trino host name, e.g., localhost:8080)", "port": 443, "catalog": "(your Trino catalog name)", "schema": "(your database)" } ] } ] } ``` ```bash $ wv --profile trino wv> ``` ### Authentication Two authentication methods are supported. Values support `${ENV_VAR}` interpolation, so secrets can stay out of `profiles.json`: - **Password (basic auth)** — set `"password"` on the connector. Requires `"useHttps": true`; Trino coordinators reject password credentials over insecure connections. - **Bearer token (JWT / OAuth2)** — set a `"token"` property. Takes the place of a password (setting both is rejected). ```jsonc title='~/.wvlet/profiles.json' { "profiles": [ { "name": "trino-jwt", "connectors": [ { "name": "trino", "type": "trino", "host": "trino.example.com", "useHttps": true, "catalog": "hive", "schema": "default", "properties": { "token": "${TRINO_ACCESS_TOKEN}" } } ] } ] } ``` The `Authorization` header is sent on every request of the query lifecycle (initial submission, result pagination, and cancellation), so gateways that authenticate each request work out of the box. ### Connecting to Trino at Treasure Data ```jsonc title='~/.wvlet/profiles.json' { "profiles": [ { "name": "td", "connectors": [ { "name": "td", "type": "trino", "user": "${TD_API_KEY}", "password": "dummy", "host": "api-presto.treasuredata.com", "port": 443, "catalog": "td", "schema": "(your database name)" } ] } ] } ``` ```bash $ wv --profile td wv> ``` --- ## Web UI (wvlet ui) You can start a web-based query editor by running `wvlet ui` command. This will start a web server on `http://localhost:9090` where you can write and execute Wvlet queries. For starting the Web UI with a sample TPC-H dataset, run `wvlet ui` with `--tpch` option: ```bash $ wvlet ui --tpch ``` This command will open a web browser for editing Wvlet queries. ![Web UI](./wvlet-ui-screenshot.png) ### Usage ```bash $ wvlet ui --help usage: ui Start a local WebUI server [global options] -h, --help Display help message --debug Enable debug log -l [LOGLEVEL] log level -L [LOGLEVELPATTERNS] log level for a class pattern [options] -p, --port:[PORT] Web UI server port. default:9090 -w [WORKDIR] Working directory --profile:[PROFILE] Profile to use --catalog:[CATALOG] Context database catalog to use --schema:[SCHEMA] Context database schema to use --quit-immediately Quit the server immediately after starting. Only for boot testing --tpch Load a small demo TPC-H data (DuckDB only)``` ``` ### Shortcut Keys - `ctrl-enter`: Execute the sub query at the current line - `shift-enter`: Describe the schema at the line - `ctrl-j ctrl-d`: Describe the schema at the line - `⌘-enter`: Run the current query - `ctrl-j ctrl-r`: Run the current query - `⌘-shift-enter`: Run the current query with production mode (e.g., skip debug/test expressions) --- ## VS Code Extension The Wvlet extension provides syntax highlighting and language support for `.wv` files in Visual Studio Code. ### Installation Install from the [VS Code Marketplace](https://marketplace.visualstudio.com/items?itemName=wvlet.wvlet) or search for "Wvlet" in VS Code's Extensions view. ### Features - **Syntax Highlighting**: Full support for Wvlet keywords, operators, and syntax - **Bracket Matching**: Automatic matching for `{}`, `[]`, `()`, and `${}` - **Comment Support**: Single-line (`--`) and multi-line (`---`) comments - **String Interpolation**: Highlighting for `${...}` expressions - **Auto-closing**: Brackets and quotes automatically close when typed - **Diagnostics**: Compilation errors are reported inline as you edit - **Document Outline**: Models, types, vals, and flows appear in the outline view - **Code Completion**: Context-aware suggestions as you type - **Hover Information**: Type and schema details when you hover over models and columns - **Go to Definition**: Jump from a model or type reference to its definition, also across workspace files ### Code Completion The extension suggests candidates while you write a query. Completion is triggered automatically as you type or on demand with `Ctrl+Space`, and offers: - **Keywords**: Wvlet language keywords such as `from`, `select`, and `where` - **Model & definition names**: Models, types, vals, and flows defined in the current file and in other workspace files, including table types from an imported catalog - **Column names**: Columns available at the cursor, resolved from the query's input relation (for example, after `from` and inside `select` or `where`) - **Function names**: Well-known SQL functions such as `count` and `regexp_replace` Typing `.` after a table alias or table name suggests only that relation's columns, and typing `.` after a schema name suggests the tables of that schema (from an imported catalog) — for example `orders.` lists the order columns, and `sales.` lists the tables of the `sales` schema. Column suggestions rely on type resolution, so they appear once the surrounding query is complete enough to be analyzed. Outside member-access contexts, keyword and definition suggestions are always available, including while a query is still being written. ### Hover Information Hover over a symbol to see its type information in a tooltip: - **Models**: The model signature — its name, parameters, and output schema (each column with its type) - **Columns**: The column name and its resolved data type, for example `name: string` - **Type definitions**: The declared fields of a type Hover relies on type resolution, so it appears once the surrounding query is complete enough to be analyzed. When the cursor is not on a symbol with a resolved type, no tooltip is shown. ### Go to Definition Place the cursor on a model or type reference and use **Go to Definition** (`F12`, or `Cmd`/`Ctrl` + click) to jump to the `model` or `type` statement that defines it. For example, from `from my_model` you can jump to the `model my_model = ...` definition, and from a type reference to its `type` declaration. Go to Definition works across the files of your workspace: a reference to a model or type defined in another `.wv` file — including table types imported into the `catalog/` folder by [`wvlet catalog import`](./catalog-import.md) — opens the defining file at the definition. When the same name is defined both in the current file and elsewhere, the definition in the current file wins. ### Working with Database Tables The language server loads the `.wv` sources of your workspace folder (the first folder in a multi-root workspace), including the `catalog/` folder generated by [`wvlet catalog import`](./catalog-import.md). After importing your database catalog once, table references such as `from sales.orders` resolve offline in the editor: diagnostics validate them, hover shows their columns, and column names complete — no live database connection needed. Note: source discovery follows the compiler's folder scan, which descends into folders that contain `.wv` files (plus the `catalog/` folder, which is always loaded). Keep at least one `.wv` file at the workspace root — or open the folder that holds your queries — so nested sources are found. ### Known Limitations - **Schema for database tables**: Tables get completion and hover only when their schema is known — from a `model`/`type` definition in the workspace or from an imported catalog (see above). File sources such as `from 'data.json'` do not yet carry a schema in the editor. - **Requires an analyzable query**: Column completion and hover rely on type resolution, so they appear once the surrounding query is complete enough to be analyzed. While a query is still being written, keyword and definition-name suggestions remain available. ### Example ![Wvlet syntax highlighting in VS Code](./vscode.png) ### Pre-release Versions To try new features early, switch to the pre-release version from the extension's settings in VS Code. ### Links - [VS Code Marketplace](https://marketplace.visualstudio.com/items?itemName=wvlet.wvlet) - [Report Issues](https://github.com/wvlet/wvlet/issues) --- ## Python SDK The Wvlet Python SDK provides a native Python interface for compiling Wvlet queries to SQL. It offers high-performance compilation through a bundled native library while maintaining a simple, Pythonic API. ### Features - 🚀 **Fast native compilation** - Uses bundled native library for high-performance query compilation - 🎯 **Multiple SQL targets** - Supports DuckDB, Trino, and other SQL engines - 📦 **Zero dependencies** - Pure Python with native acceleration - 🐍 **Pythonic API** - Simple and intuitive interface ### Installation #### From PyPI ```bash pip install wvlet ``` #### From Source ```bash # Install latest development version pip install git+https://github.com/wvlet/wvlet.git#subdirectory=sdks/python # Install editable version for development git clone https://github.com/wvlet/wvlet.git cd wvlet/sdks/python pip install -e . ``` ### Quick Start ```python from wvlet import compile # Compile a simple query sql = compile("from users select name, age where age > 18") print(sql) # Output: SELECT name, age FROM users WHERE age > 18 # Use model references sql = compile(""" model UserStats = { from users select user_id, count(*) as event_count group by user_id } from UserStats where event_count > 100 """) ``` ### Basic Usage #### Simple Compilation The simplest way to use Wvlet is through the `compile` function: ```python from wvlet import compile # Basic select sql = compile("from products select name, price") # With filtering sql = compile("from orders where status = 'completed' select order_id, total") # With joins sql = compile(""" from orders o join customers c on o.customer_id = c.id select o.order_id, c.name, o.total """) ``` #### Using the Compiler Class For more control, use the `WvletCompiler` class: ```python from wvlet.compiler import WvletCompiler # Create compiler with specific target compiler = WvletCompiler(target="trino") # Compile multiple queries queries = [ "from users select count(*)", "from products where price > 100 select name, price" ] for query in queries: sql = compiler.compile(query) print(sql) ``` ### Advanced Features #### Models (CTEs) Models in Wvlet are like CTEs but more intuitive: ```python sql = compile(""" model ActiveUsers = { from users where last_login > current_date - interval '30' day select user_id, email } model UserOrders = { from orders o join ActiveUsers u on o.user_id = u.user_id select o.*, u.email } from UserOrders group by email agg count(*) as order_count """) ``` #### Window Functions ```python sql = compile(""" from sales select date, amount, sum(amount) over (order by date rows 7 preceding) as rolling_7day_sum, rank() over (partition by product_id order by amount desc) as rank_by_product """) ``` #### Pivot Operations ```python sql = compile(""" from sales group by date pivot sum(amount) for category in ('Electronics', 'Clothing', 'Food') """) ``` ### Integration Examples #### With DuckDB ```python import duckdb from wvlet import compile # Compile Wvlet to SQL wvlet_query = """ from 'sales.parquet' where region = 'North America' group by date_trunc('month', date) as month agg sum(amount) as total_sales order by month """ sql = compile(wvlet_query, target="duckdb") # Execute with DuckDB conn = duckdb.connect() df = conn.execute(sql).fetchdf() ``` #### With Pandas ```python import pandas as pd import numpy as np import duckdb from wvlet import compile # Create sample data df = pd.DataFrame({ 'user_id': range(1, 101), 'score': np.random.randint(0, 100, 100), 'category': np.random.choice(['A', 'B', 'C'], 100) }) # Save to parquet df.to_parquet('users.parquet') # Analyze with Wvlet sql = compile(""" from 'users.parquet' group by category agg avg(score) as avg_score, count(*) as user_count """, target="duckdb") result = duckdb.sql(sql).fetchdf() ``` #### With SQLAlchemy ```python from sqlalchemy import create_engine, text from wvlet import compile engine = create_engine("postgresql://user:pass@localhost/db") wvlet_query = """ from orders o join customers c on o.customer_id = c.id where o.created_at > current_date - 7 group by c.name agg sum(o.total) as weekly_total having sum(o.total) > 1000 """ sql = compile(wvlet_query) # Uses default SQL dialect with engine.connect() as conn: result = conn.execute(text(sql)) for row in result: print(row) ``` ### Error Handling ```python from wvlet import compile from wvlet.compiler import CompilationError try: sql = compile("invalid query syntax") except CompilationError as e: print(f"Compilation failed: {e}") # Access detailed error information if available if hasattr(e, 'line'): print(f"Error at line {e.line}: {e.message}") ``` ### Performance Considerations The native library provides significant performance improvements: ```python import time from wvlet import compile # Benchmark native compilation start = time.time() for _ in range(100): compile("from users select * where age > 21") print(f"Native: {time.time() - start:.2f}s") ``` ### Platform Support #### Native Library Availability | Platform | Architecture | Status | |----------|-------------|--------| | Linux | x86_64 | ✅ Supported | | Linux | aarch64 | ✅ Supported | | macOS | arm64 | ✅ Supported | | Windows | x86_64 | ✅ Supported | | Windows | arm64 | ✅ Supported | ### API Reference #### `wvlet.compile(query: str, target: str = None) -> str` Compile a Wvlet query to SQL. **Parameters:** - `query` (str): The Wvlet query to compile - `target` (str, optional): Target SQL dialect ("duckdb", "trino", etc.) **Returns:** - str: The compiled SQL query **Raises:** - `CompilationError`: If the query cannot be compiled - `NotImplementedError`: If the native library is not available for the current platform #### `wvlet.compiler.WvletCompiler` Main compiler class for more control over compilation. ##### `__init__(self, target: str = None, wvlet_home: str = None)` Initialize a new compiler instance. **Parameters:** - `target` (str, optional): Default target SQL dialect - `wvlet_home` (str, optional): Path to Wvlet home directory ##### `compile(self, query: str, target: str = None) -> str` Compile a Wvlet query to SQL. **Parameters:** - `query` (str): The Wvlet query to compile - `target` (str, optional): Override default target for this compilation **Returns:** - str: The compiled SQL query ### Troubleshooting #### Native Library Not Found If you see an error about the native library not being found: 1. Check your platform is supported (see Platform Support above) 2. Ensure you have the latest version: `pip install --upgrade wvlet` 3. Try reinstalling: `pip install --force-reinstall wvlet` #### Compilation Errors For query compilation errors: 1. Check query syntax matches [Wvlet syntax guide](/docs/syntax/) 2. Use the CLI to validate: `wvlet compile "your query"` 3. Enable debug logging: ```python import logging logging.basicConfig(level=logging.DEBUG) ``` ### Next Steps - Explore the [example scripts](https://github.com/wvlet/wvlet/tree/main/sdks/python/examples) on GitHub - Read the [Wvlet syntax guide](/docs/syntax/) to learn more query patterns - Try the [interactive tutorial notebook](https://github.com/wvlet/wvlet/blob/main/sdks/python/examples/wvlet_tutorial.ipynb) - Join the [Wvlet community](https://github.com/wvlet/wvlet/discussions) for support --- ## TypeScript/JavaScript SDK The Wvlet TypeScript SDK provides a native JavaScript implementation of the Wvlet compiler, allowing you to compile Wvlet queries to SQL directly in Node.js or browser environments. ### Installation ```bash npm install @wvlet/wvlet # or yarn add @wvlet/wvlet # or pnpm add @wvlet/wvlet ``` ### Quick Start ```typescript import { WvletCompiler } from '@wvlet/wvlet'; const compiler = new WvletCompiler(); const sql = compiler.compile('from users select name, email'); console.log(sql); // Output: SELECT name, email FROM users ``` ### Features - **Pure JavaScript**: Compiled from Scala.js, no native dependencies required - **Type Safety**: Full TypeScript support with comprehensive type definitions - **Browser Support**: Works in both Node.js and modern browsers - **Multiple Targets**: Support for DuckDB and Trino SQL dialects - **Detailed Errors**: Rich error messages with source location information - **Small API Surface**: Simple and intuitive API design ### Usage #### Basic Usage ```typescript import { WvletCompiler } from '@wvlet/wvlet'; // Create a compiler instance const compiler = new WvletCompiler({ target: 'duckdb' // or 'trino' }); // Compile queries const sql = compiler.compile('from orders where total > 100 select *'); ``` #### Error Handling ```typescript import { WvletCompiler, CompilationError } from '@wvlet/wvlet'; const compiler = new WvletCompiler(); try { const sql = compiler.compile('invalid query syntax'); } catch (error) { if (error instanceof CompilationError) { console.error(`Error: ${error.message}`); console.error(`Status: ${error.statusCode}`); if (error.location) { console.error(`Location: line ${error.location.line}, column ${error.location.column}`); } } } ``` #### Convenience Function ```typescript import { compile } from '@wvlet/wvlet'; // Quick compilation with default settings const sql = compile('from users select count(*)'); ``` ### API Reference #### `WvletCompiler` The main compiler class for Wvlet queries. ##### Constructor ```typescript new WvletCompiler(options?: CompileOptions) ``` **Options:** - `target`: Target SQL dialect (`'duckdb'` | `'trino'`). Default: `'duckdb'` - `profile`: Profile name for configuration ##### Methods ###### `compile(query: string, options?: CompileOptions): string` Compiles a Wvlet query to SQL. **Parameters:** - `query`: The Wvlet query string - `options`: Optional compilation options to override defaults **Returns:** The compiled SQL string **Throws:** `CompilationError` if the query is invalid ###### `static getVersion(): string` Returns the version of the Wvlet compiler. #### Types ##### `CompileOptions` ```typescript interface CompileOptions { target?: 'duckdb' | 'trino'; profile?: string; } ``` ##### `CompilationError` ```typescript class CompilationError extends Error { statusCode: string; location?: ErrorLocation; } ``` ##### `ErrorLocation` ```typescript interface ErrorLocation { path: string; fileName: string; line: number; // 1-based column: number; // 1-based lineContent?: string; } ``` ### Examples #### Query with JOIN ```typescript const sql = compiler.compile(` from users u join orders o on u.id = o.user_id select u.name, count(*) as order_count group by u.name `); ``` #### Query with CTE ```typescript const sql = compiler.compile(` with recent_orders as ( from orders where created_at > current_date - interval '7 days' select * ) from recent_orders select customer_id, sum(total) as weekly_total group by customer_id `); ``` #### Window Functions ```typescript const sql = compiler.compile(` from sales select product_id, sale_date, amount, sum(amount) over (partition by product_id order by sale_date) as running_total `); ``` ### Browser Usage The SDK works directly in browsers that support ES modules: ```html ``` ### Performance Considerations - **Bundle Size**: The compiled Scala.js output is approximately 9MB (uncompressed) - **Compilation Speed**: Compilation is performed synchronously and is typically fast for most queries - **Memory Usage**: The compiler maintains minimal state between compilations ### Differences from Python SDK Unlike the Python SDK which uses a native library, the TypeScript SDK: - Runs purely in JavaScript (via Scala.js compilation) - Works in browser environments - Has no native dependencies - Provides synchronous-only API ### Troubleshooting #### Module Resolution Issues If you encounter module resolution issues, ensure your `tsconfig.json` includes: ```json { "compilerOptions": { "moduleResolution": "node", "esModuleInterop": true } } ``` #### Browser Compatibility The SDK requires browsers that support: - ES2020 features - ES modules - Dynamic imports (for code splitting) ### Source Code The TypeScript SDK source code is available at: - [GitHub: sdks/typescript](https://github.com/wvlet/wvlet/tree/main/sdks/typescript) - [NPM: @wvlet/wvlet](https://www.npmjs.com/package/@wvlet/wvlet)