> **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.

# Querying

You query Amp with SQL. The Python SDK sends your query to Amp's query server, and gives you the results as an
[Apache Arrow](https://arrow.apache.org) table, the columnar format Amp sends them in, or as a Polars or pandas
DataFrame. For results too large to hold in memory, you read them in batches instead.

This page covers queries that return the data that's there when you run them. To get new rows as blocks arrive, see
[Streaming](/sdks/python/streaming). [SQL](/querying/sql) covers what you can write in a query.

## Prerequisites

* The Python SDK, [installed](/sdks/python#installation).
* The Amp and the `acme/eth_mainnet` dataset from the [Quickstart](/get-started/quickstart), running on your machine.

## Run a query

`client.sql()` takes a SQL query and returns a query builder, a `QueryBuilder`. Nothing is sent to Amp yet: the query
runs when you ask the builder for its results.

```python [arrow.py]
from amp import Client

client = Client('grpc://localhost:1602')

table = client.sql(
    'SELECT block_num, hash, gas_used FROM "acme/eth_mainnet".blocks ORDER BY block_num DESC LIMIT 3'
).to_arrow()
print(table.schema)
print(table.num_rows)

row = table.to_pylist()[0]
print(row['block_num'], '0x' + row['hash'].hex(), row['gas_used'])
```

:::terminal
```bash
python arrow.py
```

```
block_num: uint64 not null
hash: fixed_size_binary[32] not null
gas_used: uint64 not null
3
24000099 0x6ed4b3511195052eea47925fe5035f5e3fc528440eed62649ba82058a1b70e40 10751338
```
:::

In SQL, a table is its dataset's name in double quotes, then the table's name: `"acme/eth_mainnet".blocks`. The quotes
are needed because of the `/`. A dataset in the `_` namespace can go without its namespace or quotes, like
`eth_firehose.blocks`.

## Get the results

A query builder gives you the results in one of these forms. Each runs the query once:

| Method                   | Returns                                        | Needs                            |
| ------------------------ | ---------------------------------------------- | -------------------------------- |
| `to_arrow()`             | A `pyarrow.Table` with all the results         | Nothing more                     |
| `to_polars()`            | A `polars.DataFrame` with all the results      | Nothing more                     |
| `to_arrow().to_pandas()` | A `pandas.DataFrame` with all the results      | `pandas` (`pip install pandas`)  |
| `stream()`               | An iterator of `pyarrow.RecordBatch`, in order | Nothing more                     |
| `load()`                 | Load results into another system               | A [loader](/sdks/python/loaders) |

`to_arrow()` keeps its results: calling it again on the same builder returns the same table without running the query
again, and `to_polars()` reuses it too. To run a query again, for example to see new data, call `client.sql()` again.

`to_polars()` converts the Arrow table without copying the data. For pandas, install it first, then convert the Arrow
table:

```python [pandas_example.py]
from amp import Client

client = Client('grpc://localhost:1602')

df = client.sql(
    'SELECT block_num, gas_used, gas_limit FROM "acme/eth_mainnet".blocks ORDER BY block_num LIMIT 5'
).to_arrow().to_pandas()
print(df)
```

:::terminal
```bash
python pandas_example.py
```

```
   block_num  gas_used  gas_limit
0   24000000  57860235   59999886
1   24000001  27660983   60000000
2   24000002  17602635   60000000
3   24000003  17842064   60000000
4   24000004  25049659   60000000
```
:::

### Read large results in batches

`to_arrow()` holds all the results in memory at once. For large results, `stream()` gives them to you in batches, Arrow
record batches, as Amp sends them, so you only hold one at a time:

```python [batches.py]
from amp import Client

client = Client('grpc://localhost:1602')

total = 0
for batch in client.sql('SELECT block_num, tx_hash, gas_used FROM "acme/eth_mainnet".transactions').stream():
    print(f'{batch.num_rows} rows')
    total += batch.num_rows
print(f'{total} rows in all')
```

:::terminal
```bash
python batches.py
```

```
8192 rows
8192 rows
769 rows
0 rows
17153 rows in all
```
:::

Amp decides the batches' sizes, and ends with an empty batch. Despite its name, `stream()` doesn't wait for new
blocks: it reads the results there are when the query runs, then stops. To keep reading as blocks arrive, see
[Streaming](/sdks/python/streaming).

## Column types

Results keep Amp's column types, which Arrow represents exactly. In Python, `to_pylist()` and `as_py()` turn them into
Python values:

| Amp column                                      | Arrow type                      | Python value                                   |
| ----------------------------------------------- | ------------------------------- | ---------------------------------------------- |
| Block numbers, gas amounts                      | `uint64`                        | `int`                                          |
| Timestamps                                      | `timestamp[ns, tz=+00:00]`      | `datetime` in UTC                              |
| Hashes (32 bytes) and addresses (20 bytes)      | `fixed_size_binary[32]`, `[20]` | `bytes`: format them with `'0x' + value.hex()` |
| Amounts that fit in 38 digits, like `gas_price` | `decimal128(38, 0)`             | `Decimal`                                      |
| Larger amounts, like a transaction's `value`    | `string`, of decimal digits     | `str`: convert it with `int(value)`            |

[Tables](/datasets/tables/evm-rpc) lists each table's columns and their types. To get hexadecimal strings from Amp
instead of bytes, use SQL [functions](/querying/functions/evm), like `evm_decode_hex(address)`.

## See a query's plan

A query plan shows how Amp runs a query: which tables it reads, how it filters and combines them. It helps you find
out why a query is slow. `explain()` returns the plan without running the query:

```python [explain.py]
from amp import Client

client = Client('grpc://localhost:1602')

plan = client.sql('SELECT count(*) FROM "acme/eth_mainnet".blocks WHERE gas_used > 30000000').explain()
print(plan)
```

:::terminal
```bash
python explain.py
```

```
Projection
  Aggregate: groupBy=[[]], aggr=[[count(Int64(1))]]
    Projection
      Filter
        TableScan: acme/eth_mainnet.blocks projection=[gas_used], partial_filters=[_block_num >= UInt64(24000000) AND _block_num <= UInt64(24000099)]
```
:::

Each line is a step of the plan, indented under the step that uses its output. `explain(analyze=True)` runs the query,
and adds what each step did: how many rows it produced, and how long it took. `to_dataframe()` puts those metrics in a
Polars DataFrame, one row per step, with durations in seconds:

```python [analyze.py]
from amp import Client

client = Client('grpc://localhost:1602')

plan = client.sql('SELECT count(*) FROM "acme/eth_mainnet".blocks WHERE gas_used > 30000000').explain(analyze=True)
print(plan.to_dataframe().select('node', 'depth', 'output_rows', 'elapsed_compute_secs'))
```

:::terminal
```bash
python analyze.py
```

```
shape: (7, 4)
┌────────────────────────┬───────┬─────────────┬──────────────────────┐
│ node                   ┆ depth ┆ output_rows ┆ elapsed_compute_secs │
│ ---                    ┆ ---   ┆ ---         ┆ ---                  │
│ str                    ┆ i64   ┆ i64         ┆ f64                  │
╞════════════════════════╪═══════╪═════════════╪══════════════════════╡
│ ProjectionExec         ┆ 0     ┆ 1           ┆ 4.1700e-7            │
│ AggregateExec          ┆ 1     ┆ 1           ┆ 0.000008             │
│ CoalescePartitionsExec ┆ 2     ┆ 12          ┆ 0.000018             │
│ AggregateExec          ┆ 3     ┆ 12          ┆ 0.000047             │
│ SnapshotTableExec      ┆ 4     ┆ 51          ┆ 0.000008             │
│ RepartitionExec        ┆ 5     ┆ 51          ┆ 0.000008             │
│ DataSourceExec         ┆ 6     ┆ 51          ┆ 9.1800e-7            │
└────────────────────────┴───────┴─────────────┴──────────────────────┘
```
:::

Your durations differ, and so may the number of partitions, which depends on the machine running Amp. `print(plan)`
shows every metric under its step. In a Jupyter or marimo notebook, a plan shows as a formatted tree, with its metrics
color-coded; `to_html()` returns the same HTML. [`ExplainResult`](/sdks/python/reference/client#explainresult) lists
everything a plan has.

## Dataset aliases

A dataset alias is a short name for a dataset, like `eth` for `acme/eth_mainnet@1.0.0`, that you can use in your SQL
instead of the dataset's full name. It saves typing quotes and versions, and lets you switch every query to another
version of a dataset in one place.

```python [aliases.py]
from amp import Client

client = Client(query_url='grpc://localhost:1602', admin_url='http://localhost:1610')

client.aliases.register('eth', 'acme/eth_mainnet@1.0.0')

df = client.sql('SELECT block_num, gas_used FROM eth.blocks ORDER BY block_num LIMIT 3').to_polars()
print(df)
```

:::terminal
```bash
python aliases.py
```

```
Registered alias 'eth' → acme/eth_mainnet@1.0.0
shape: (3, 2)
┌───────────┬──────────┐
│ block_num ┆ gas_used │
│ ---       ┆ ---      │
│ u64       ┆ u64      │
╞═══════════╪══════════╡
│ 24000000  ┆ 57860235 │
│ 24000001  ┆ 27660983 │
│ 24000002  ┆ 17602635 │
└───────────┴──────────┘
```
:::

* **Where they live.** The SDK saves aliases in `~/.amp/cache/amp_dataset_aliases`, on your machine, so every script and
  notebook you run there can use them. Run the example again, and `register()` fails, because `eth` already exists:
  pass `force=True` to replace it.
* **How they work.** Before sending a query, the SDK replaces each alias used as a dataset name with the dataset's full
  name. It reads the file for every query, so a change made elsewhere applies to the next query.
* **Versions.** A dataset without a version, like `client.aliases.register('eth', 'acme/eth_mainnet')`, means its
  latest version, which can change. When you [generate a manifest](/sdks/python/admin) from a query that uses such an
  alias, the SDK pins it to the version that's latest at that moment, so the derived dataset doesn't change under you.
* **Names.** An alias can't be a SQL keyword, like `select`, or `_`. With an `admin_url`, `register()` also refuses a
  name that's already a namespace on that Amp, like `acme`, since the alias would hide it.

`client.aliases` also has `list()`, `get(alias)`, `resolve(alias)`, and `remove(alias)`: see
[Client Reference](/sdks/python/reference/client#datasetaliasmanager).

:::note
These aliases are the SDK's own. Amp can also bind names to dataset versions itself, for each query, through
[a request header](/querying/dataset-aliases). The SDK doesn't use that header: it rewrites your SQL instead.
:::

## Troubleshooting

### `no common canonical watermark is available for batch snapshot`

The full message starts with `FlightUnavailableError: Flight returned unavailable error, with message: failed to resolve
batch query snapshot`. The dataset you query has no data yet: its first extraction hasn't finished a block. Check its
progress with `ampctl dataset status acme/eth_mainnet@1.0.0`, and query it again once it has extracted some blocks.

### `table '…' not found`

The message reads like `ArrowInvalid: Flight returned invalid argument error, with message: failed to infer SQL output
schema | Caused by: Error during planning: table 'amp.acme/eth_mainnet.nope' not found`. The dataset or the table
doesn't exist, or isn't deployed. Check the names with `ampctl dataset list`, or
[inspect the dataset](/sdks/python/inspecting-datasets).

### `SQL parse error` … `found: /`

The dataset's name isn't in double quotes: write `"acme/eth_mainnet".blocks`, not `acme/eth_mainnet.blocks`. In a
Python string, use single quotes around the query, as the examples do, so the double quotes need no escaping.

### Malformed `authorization` header

The message reads
``ArrowInvalid: gRPC returned precondition failed error, with message: malformed `authorization` header``. You passed
a token that isn't one, for example with `auth_token` or in the `AMP_AUTH_TOKEN` environment variable. An Amp that
doesn't require authentication, like solo mode, still rejects one that's malformed. Remove it, or see
[Authentication](/sdks/python/authentication).

### `certificate verify failed: unable to get local issuer certificate`

The message reads like `FlightUnavailableError: Flight returned unavailable error, with message: failed to connect to
all addresses; … Ssl handshake failed (TSI_PROTOCOL_FAILURE): … certificate verify failed: unable to get local issuer
certificate`. The query server's certificate, on a `grpc+tls://` URL, isn't signed by an authority gRPC trusts, like a
self-signed one. `Client` takes no certificate, so give gRPC the certificate of the authority that signed it, in PEM
format, with the `GRPC_DEFAULT_SSL_ROOTS_FILE_PATH` environment variable:

```bash
GRPC_DEFAULT_SSL_ROOTS_FILE_PATH=ca.pem python query.py
```

It replaces the certificate authorities gRPC trusts by default, so set it only for programs that query that server.

### `DatasetAliasAlreadyExistsError: alias 'eth' is already registered`

The alias exists from an earlier run. Pass `force=True` to `register()` to replace it, or remove it with
`client.aliases.remove('eth')`.