> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# SQL

Amp runs SQL over [datasets](/get-started/concepts/datasets). Its dialect is the one
[Apache DataFusion](https://datafusion.apache.org/user-guide/sql/index.html), the query engine inside Amp, speaks, which
is close to PostgreSQL's. On top of it, Amp adds a way to name datasets and their versions, columns that track each
row's block, and [functions](/querying/functions) for decoding chain data.

## Prerequisites

* A [connection](/querying/connecting) to a query server. The examples use `ampsql`.
* To run the examples, the [example datasets](/querying/connecting#example-datasets).

## How SQL runs

The same SQL runs in three places, in one of two ways:

| Where                                                    | How it runs                                                         | What it returns                              |
| -------------------------------------------------------- | ------------------------------------------------------------------- | -------------------------------------------- |
| A [batch query](/querying/batch-queries), the default    | Once, over the data synced so far                                   | The complete result                          |
| A [streaming query](/querying/streaming-queries)         | Continuously, over each new range of blocks as it's synced          | New rows, as they come                       |
| A [derived dataset](/datasets/derived-datasets)'s tables | Continuously, like a streaming query, while the dataset is deployed | Nothing: its results become the table's rows |

A streaming query only accepts SQL that Amp can run one block range at a time, without remembering earlier ranges.
That excludes most aggregations, sorting, and limits (see [what streaming queries
support](/querying/streaming-queries#what-streaming-queries-support)). A derived dataset's SQL follows the same rules.

## Queries

A request carries one `SELECT` statement, optionally with `WITH` clauses, or an `EXPLAIN` of one. Queries are
read-only, and Amp rejects anything else:

| You send                                  | Amp responds                                                        |
| ----------------------------------------- | ------------------------------------------------------------------- |
| Two statements, like `SELECT 1; SELECT 2` | `expected a single SQL statement, found 2`                          |
| A write, like `INSERT` or `CREATE TABLE`  | `query plan violates read-only constraints`                         |
| `SHOW TABLES`, or `information_schema`    | `SHOW TABLES is not supported unless information_schema is enabled` |

To list a dataset's tables, run [`ampctl dataset inspect`](/clis/ampctl/dataset). Their columns are in the dataset's
manifest (`ampctl dataset manifest`), and, for raw datasets, in the [table schemas](/datasets/tables/evm-rpc).

## Naming tables

A table's name is its dataset's reference, in double quotes, then the table:

```sql
SELECT COUNT(*) AS blocks
FROM "acme/eth_mainnet@1.0.0".blocks
```

```text [Output]
+--------+
| blocks |
+--------+
| 100    |
+--------+
```

The reference is `namespace/name@revision`. It needs the quotes because it contains `/` and `@`. The revision picks
which version of the dataset the query reads:

| Revision        | Example                               | Reads                                                     |
| --------------- | ------------------------------------- | --------------------------------------------------------- |
| None            | `"acme/eth_mainnet".blocks`           | The latest version, as with `latest`                      |
| A version       | `"acme/eth_mainnet@1.0.0".blocks`     | That version                                              |
| `latest`        | `"acme/eth_mainnet@latest".blocks`    | The highest version                                       |
| `dev`           | `"acme/eth_mainnet@dev".blocks`       | The manifest registered most recently                     |
| A manifest hash | `"acme/eth_mainnet@b9e2ebd9…".blocks` | That exact manifest, whatever the versions point to later |

See [versioning](/datasets/versioning) for how datasets get their versions. A name without quotes, like
`eth_mainnet.blocks`, can't have a namespace, so it means a dataset in the `_` namespace.

To keep versions out of your SQL, write plain names like `eth.blocks` and choose the dataset behind each one when you
send the query, with [dataset aliases](/querying/dataset-aliases).

A reference that matches no dataset, or a table the dataset doesn't have, fails the query:

```text
table 'amp.acme/eth_mainnet@2.0.0.blocks' not found
```

## Columns

### Names

As in PostgreSQL, SQL lowercases names you don't quote, so `Block_Num` means `block_num`. Quote names that are SQL
keywords, like the `from` and `to` columns of EVM transactions:

```sql
SELECT tx_index, "from", "to", value
FROM "acme/eth_mainnet".transactions
WHERE block_num = 24000000
ORDER BY tx_index
LIMIT 2
```

```text [Output]
+----------+------------------------------------------+------------------------------------------+--------+
| tx_index | from                                     | to                                       | value  |
+----------+------------------------------------------+------------------------------------------+--------+
| 0        | 5050f69a9786f081509234f1a7f4684b5e5b76c9 | ff00000000000000000000000000000000008453 | 0      |
| 1        | bd364f802d1b6ec560a9eb8e9f75fad1217d7954 | 5050e08626c499411b5d0e0b5af0e83d3fd82edf | 862996 |
+----------+------------------------------------------+------------------------------------------+--------+
```

### Types

Columns have [Apache Arrow](https://arrow.apache.org/docs/format/Columnar.html) types. Chain data uses a few of them
often. In an [EVM dataset](/datasets/tables/evm-rpc):

| Arrow type                | Holds                                                         | Example columns                      |
| ------------------------- | ------------------------------------------------------------- | ------------------------------------ |
| `UInt64`, `UInt32`        | Counts and positions                                          | `block_num`, `gas_used`, `log_index` |
| `FixedSizeBinary(20)`     | 20-byte addresses                                             | `miner`, `"from"`, `address`         |
| `FixedSizeBinary(32)`     | 32-byte hashes                                                | `hash`, `tx_hash`, `topic0`          |
| `Binary`                  | Bytes of any length                                           | `input`, `data`, `extra_data`        |
| `Decimal128(38, 0)`       | Large whole numbers                                           | `base_fee_per_gas`, `gas_price`      |
| `Utf8`                    | Text, and numbers too large for a decimal, written in base 10 | `value`                              |
| `Timestamp(ns, "+00:00")` | Times, in UTC                                                 | `timestamp`                          |

* `arrow_typeof(column)` returns a column's type, and `arrow_cast(value, 'Type')` converts a value to any Arrow type,
  like `arrow_cast(1000000, 'UInt64')`.
* `ampsql` shows bytes in hexadecimal, without a `0x`. To get `0x` strings, or to compare a column with one, use the
  [hex functions](/querying/functions/evm#hex): `evm_decode_hex(hash)` and `hash = evm_encode_hex('0x…')`. A
  hexadecimal literal like `X'5dca…'` works in comparisons too.

### Block numbers and timestamps

Tables indexed by block have two columns Amp manages: `_block_num`, the block each row comes from, and `_ts`, that
block's timestamp. Amp uses them to know which rows each [block range](/get-started/concepts/reorgs) holds, and they
work like any other column:

```sql
SELECT _block_num, _ts, gas_used
FROM "acme/eth_mainnet".blocks
WHERE _block_num >= 24000098
```

```text [Output]
+------------+----------------------+----------+
| _block_num | _ts                  | gas_used |
+------------+----------------------+----------+
| 24000098   | 2025-12-13T00:25:47Z | 36233422 |
| 24000099   | 2025-12-13T00:25:59Z | 10751338 |
+------------+----------------------+----------+
```

When a query reads several tables, like in a [join](/querying/joins), `_block_num` and `_ts` are ambiguous, and Amp
rejects them. Use `block_num()` and `ts()` instead: they return the block number and timestamp of each result row,
whatever the query does to produce it.

```sql
SELECT block_num(), ts(), hash
FROM "acme/eth_mainnet".blocks
ORDER BY block_num()
LIMIT 2
```

```text [Output]
+-------------+----------------------+------------------------------------------------------------------+
| block_num() | ts()                 | hash                                                             |
+-------------+----------------------+------------------------------------------------------------------+
| 24000000    | 2025-12-13T00:06:11Z | 738cc1716ea1f08adac2d4e2230aedcee2d5cd3f65d66d5d3597e05d710a3d50 |
| 24000001    | 2025-12-13T00:06:23Z | 1353cf08daa372d4f13d8a95721dbd406650c78a602c0bfa1867042275637c3c |
+-------------+----------------------+------------------------------------------------------------------+
```

* `block_num()` returns a `UInt64`, and `ts()` a `Timestamp`. Neither takes arguments.
* In older datasets without a `_ts` column, `ts()` returns their `timestamp` column.
* Names that start with `_` belong to Amp: an alias like `AS _custom` fails with
  `expression contains a column alias starting with '_'`. Choose a name without the underscore.

## Settings

A query can end with a `SETTINGS` clause, which changes how Amp runs it:

```sql
SELECT block_num, hash
FROM "acme/eth_mainnet".blocks
SETTINGS stream = true, microbatch_max_interval = 4
```

| Setting                   | Type                 | Default                           | Description                                                                                                                                       |
| ------------------------- | -------------------- | --------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| `stream`                  | boolean              | `false`                           | Run the query as a [streaming query](/querying/streaming-queries).                                                                                |
| `microbatch_max_interval` | integer, `0` or more | The server's, `100000` by default | The most blocks a streaming query reads at a time (seconds, for [several chains](/querying/streaming-queries#several-chains)). `0` counts as `1`. |

How Amp reads the clause:

* **It ignores settings it doesn't know, whatever their value, without an error.** A typo changes nothing:
  `SETTINGS steam = true` runs a batch query, and `SETTINGS stream = true, microbatch_max_intervl = 40` streams with the
  server's default.
* **It fails on a bad value for a setting it knows**, with gRPC status `INVALID_ARGUMENT`. Which
  [call](/querying/arrow-flight#how-a-query-runs) fails depends on the setting:
  * `stream` only takes `true` or `false`, not `1` or `'true'`. A bad value fails on `GetFlightInfo`. When the
    [`amp-stream` header](/querying/streaming-queries#start-a-stream) is set, Amp doesn't read `stream`, so its value
    isn't checked.
  * `microbatch_max_interval` only takes an unquoted whole number, not `-1`, `1.5`, or `'40'`. A bad value fails on
    `DoGet`, even in a batch query, which otherwise ignores this setting.
* **It only reads the clause of the outermost query.** A `SETTINGS` clause in a subquery does nothing.
* **Names aren't case-sensitive.** `STREAM = TRUE` works.
* **The first of a repeated setting wins.** Amp doesn't check the others.

## Functions

Amp's SQL has DataFusion's functions, such as `date_trunc`, `concat`, `array_agg`, and `row_number`: see DataFusion's
[scalar](https://datafusion.apache.org/user-guide/sql/scalar_functions.html),
[aggregate](https://datafusion.apache.org/user-guide/sql/aggregate_functions.html), and
[window](https://datafusion.apache.org/user-guide/sql/window_functions.html) functions. Amp adds `block_num()` and
`ts()`, functions for decoding chain data, and the [JavaScript functions](/querying/functions/javascript) datasets
define. See [Functions](/querying/functions).

## Errors

A query that fails returns a gRPC status and a message. `ampsql` prints both, along with the causes of the error. The
most common:

| Message contains                                                | Cause                                                                          | Fix                                                                                               |
| --------------------------------------------------------------- | ------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------- |
| `table '…' not found`                                           | No dataset or table matches the name.                                          | Check the [reference](/querying/sql#naming-tables) and its quotes.                                             |
| `No field named …`                                              | No column matches the name.                                                    | Check the name, and quote it if it has capitals or is a keyword.                                  |
| `sql parser error`                                              | The SQL isn't valid.                                                           | Fix the syntax at the line and column given.                                                      |
| `Ambiguous reference to unqualified field _block_num`           | A query over several tables selects `_block_num` or `_ts`.                     | Use `block_num()` or `ts()`.                                                                      |
| `Duplicate field names detected in plan schema`                 | The result has two columns with the same name, as with `SELECT *` over a join. | Select [the columns you need](/querying/joins#column-names).                                      |
| ``selecting `_ts` from a multi-table context``                  | A streaming join selects `*`.                                                  | Select columns by name, and use `block_num()` and `ts()`.                                         |
| `query is not supported in streaming mode`                      | A streaming query does something it can't do incrementally.                    | See [what streaming queries support](/querying/streaming-queries#what-streaming-queries-support). |
| `invalid SETTINGS value`                                        | A setting Amp knows has a value it doesn't take.                               | Use a [value it takes](/querying/sql#settings). The message names the setting.                                 |
| `no common canonical watermark is available for batch snapshot` | The tables have no synced blocks in common yet.                                | Wait for the datasets to sync, then retry.                                                        |

[Limits](/querying/limits#errors) lists the errors for queries that run out of time or memory. [Arrow
Flight](/querying/arrow-flight#mistakes-that-return-internal) lists the mistakes that return gRPC status `INTERNAL`.