> **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. # ampsql Status: Experimental. `ampsql` runs a SQL query against Amp and prints the result, like `psql` does for PostgreSQL. It sends the query to Amp's [query server](/running-amp/query-server) over [Arrow Flight](/querying/arrow-flight), prints the rows as a table, JSON, or CSV, and exits. Use it to look at a dataset's data, to try a query before you put it in a [derived dataset](/datasets/derived-datasets) or an app, or to export a result to a file from a script. `ampsql` runs [batch queries](/querying/batch-queries): each one reads the blocks Amp has when it starts, returns its rows, and ends. It doesn't run [streaming queries](/querying/streaming-queries), and it has no interactive mode: each run sends one query. To learn the SQL itself, like how to name a dataset's tables and which functions there are, see [SQL](/querying/sql). ## Prerequisites * A running Amp with a dataset to query, like the one the [Quickstart](/get-started/quickstart) starts with [solo mode](/running-amp/solo-mode). The examples query its `acme/eth_mainnet@1.0.0` dataset, and [`acme/eth_transfers@1.0.0`](/datasets/derived-datasets#the-erc-20-transfers-dataset), a derived dataset you create on top of it. * `ampsql`, [installed](/clis/ampsql#install). ## Install [`ampup`](/clis/ampup) installs `ampsql` with `ampd` and `ampctl`, and keeps it at the same version as them. Check that it's on your `PATH`: :::terminal ```bash ampsql --version ``` ``` ampsql v0.0.42 ``` ::: To build it from source instead, see [Installation](/running-amp/installation). ## Usage ``` ampsql [OPTIONS] [QUERY] ``` | Argument / flag | Environment variable | Default | Description | | --------------------------- | -------------------- | ----------------------- | ------------------------------------------------------------------------------------------------------------------------------- | | `QUERY` | none | none | The query to run, like `'SELECT 1 AS x'`. See [Run a query](/clis/ampsql#run-a-query). | | `-c`, `--command ` | none | none | The query to run, like `QUERY`, for scripts written for `psql`: `-c 'SELECT 1 AS x'`. | | `-f`, `--file ` | none | none | A file holding the query to run, like `-f top_tokens.sql`. | | `--format ` | none | `table` | How to print the rows: `table`, `json`, `jsonl`, or `csv`, like `--format csv`. See [Output formats](/clis/ampsql#output-formats). | | `-o`, `--output ` | none | standard output | The file to write the rows to, like `-o top_tokens.csv`. See [Write to a file](/clis/ampsql#write-to-a-file). | | `-e`, `--echo-queries` | none | off | Prints the query to standard error before running it. | | `--flight-url ` | `AMP_QUERY_URL` | `http://localhost:1602` | The query server to send the query to, like `--flight-url https://amp.example.com:1602`. See [Connect to Amp](/clis/ampsql#connect-to-amp). | | `--auth-token ` | `AMP_AUTH_TOKEN` | none | The token to send, for a gateway that requires one. See [Authentication](/clis/ampsql#authentication). | | `-h`, `--help` | none | none | Prints a summary of the flags. `--help` prints the full help, with examples. | | `-V`, `--version` | none | none | Prints `ampsql`'s version. | A flag takes precedence over its environment variable. `QUERY`, `-c`, and `-f` are the three ways to pass the query: use exactly one. ## Connect to Amp `ampsql` sends queries to `http://localhost:1602` unless you name another. That's where solo mode's query server, and a [query server](/running-amp/query-server) with its default configuration, listen, so on the machine that runs Amp, queries work as they are. To query another Amp, set `AMP_QUERY_URL` to its query server, once for every command in your shell: ```bash export AMP_QUERY_URL=https://amp.example.com:1602 ``` Or pass `--flight-url` to one command: ```bash ampsql 'SELECT 1 AS x' --flight-url http://amp-query.internal:1602 ``` The URL needs its scheme. With `https://`, `ampsql` connects over TLS and trusts the certificate authorities your system does. Any other scheme connects without TLS, even `grpc+tls://`, which other Arrow Flight clients use for TLS. Its port is the query server's `flight_addr` ([Configuration Reference](/running-amp/configuration-reference)). When your system doesn't trust the query server's certificate, like a self-signed one, set `SSL_CERT_FILE` to the certificate of the authority that signed it, in PEM format, like `ca.pem`. It replaces your system's certificate authorities rather than adding to them, so set it only for the commands that query that server: ```bash SSL_CERT_FILE=ca.pem ampsql 'SELECT 1 AS x' --flight-url https://amp.internal:1602 ``` `SSL_CERT_DIR` works the same way, with a directory of PEM files. Without either, the query fails with `invalid peer certificate: UnknownIssuer` ([Errors](/clis/ampsql#errors)). When nothing answers at that address, the query fails with exit code 1: ``` 2026-10-30T21:15:13.356218Z ERROR run{flight_url=http://localhost:1602/}:connect_client: ampsql::cmd::root: Failed to connect to Arrow Flight endpoint error=failed to connect to Arrow Flight endpoint error_source=["gRPC transport error", "transport error", "tcp connect error", "tcp connect error", "Connection refused (os error 61)"] ✗ failed to build Arrow Flight client → failed to connect to Arrow Flight endpoint → gRPC transport error → transport error → tcp connect error → tcp connect error → Connection refused (os error 61) ``` Check that Amp is running, and that the URL's port is its query server's. A URL without its scheme, like `localhost:1602`, fails the same way, with `invalid URL, scheme is missing` at the end. ## Authentication Amp's query server doesn't check who sends a query. A deployment that needs to, like a hosted Amp, puts a gateway in front of it that checks a token and decides which [namespaces](/get-started/glossary) it can read. To query one, set `AMP_AUTH_TOKEN` to the token you were given, or pass it with `--auth-token`. `ampsql` sends it in each request's `Authorization: Bearer` header. ```bash export AMP_AUTH_TOKEN=… ``` `ampctl` reads the same variable ([Authentication](/clis/ampctl#authentication)), so one token serves both. Prefer the variable to the flag: other users of the machine can read a command's flags in its process list. The query server reads the token as a JWT, and takes the user it names to apply their [limits](/querying/limits). A token that isn't a JWT fails, even with solo mode, which needs none: ``` ✗ query failed → Arrow Flight error → Tonic error: code: 'The system is not in a state required for the operation's execution', message: "malformed `authorization` header" → code: 'The system is not in a state required for the operation's execution', message: "malformed `authorization` header" ``` A dataset in a namespace your token wasn't granted answers like one that doesn't exist: `table '…' not found` ([Errors](/clis/ampsql#errors)). ## Run a query Pass the query as an argument. Datasets are named in double quotes, like `"acme/eth_mainnet@1.0.0"`, so put the query in single quotes, which keep the shell from reading them: :::terminal ```bash ampsql 'SELECT block_num, hash, gas_used FROM "acme/eth_mainnet@1.0.0".blocks ORDER BY block_num LIMIT 3' ``` ``` +-----------+------------------------------------------------------------------+----------+ | block_num | hash | gas_used | +-----------+------------------------------------------------------------------+----------+ | 24000000 | 738cc1716ea1f08adac2d4e2230aedcee2d5cd3f65d66d5d3597e05d710a3d50 | 57860235 | | 24000001 | 1353cf08daa372d4f13d8a95721dbd406650c78a602c0bfa1867042275637c3c | 27660983 | | 24000002 | b184e0e5844fe75b8cbf74b05a5afa1662c2b67bff13c597f2d56f8e40c19f9a | 17602635 | +-----------+------------------------------------------------------------------+----------+ ``` ::: `-c` does the same, for scripts written for `psql`: ```bash ampsql -c 'SELECT block_num, hash, gas_used FROM "acme/eth_mainnet@1.0.0".blocks ORDER BY block_num LIMIT 3' ``` A longer query is easier to keep in a file, which can span several lines and hold `--` comments: ```sql [top_tokens.sql] -- The five tokens with the most transfers SELECT evm_decode_hex(token) AS token, count(*) AS transfers FROM "acme/eth_transfers@1.0.0".transfers GROUP BY token ORDER BY transfers DESC LIMIT 5 ``` :::terminal ```bash ampsql -f top_tokens.sql ``` ``` +--------------------------------------------+-----------+ | token | transfers | +--------------------------------------------+-----------+ | 0x0d4a11d5eeaac28ec3f61d100daf4d40471f1852 | 43168 | | 0x609e0f0cb16e53878ba5e959a22fc7fcd81b124a | 42151 | | 0xdac17f958d2ee523a2206206994597c13d831ec7 | 5459 | | 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 | 4158 | | 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 | 4135 | +--------------------------------------------+-----------+ ``` ::: `ampsql` doesn't read the query from standard input, nor from `-f -`. On Linux and macOS, pass `/dev/stdin` as the file instead, to pipe a query from another command: ```bash cat top_tokens.sql | ampsql -f /dev/stdin ``` Each run sends one query, a single SQL statement. To run several, run `ampsql` once for each. With `-e`, `ampsql` prints the query to standard error, after `→`, before it runs it. That shows which query a script ran, without mixing it into the results: :::terminal ```bash ampsql -f top_tokens.sql -e --format csv ``` ``` → -- The five tokens with the most transfers SELECT evm_decode_hex(token) AS token, count(*) AS transfers FROM "acme/eth_transfers@1.0.0".transfers GROUP BY token ORDER BY transfers DESC LIMIT 5 token,transfers 0x0d4a11d5eeaac28ec3f61d100daf4d40471f1852,43168 0x609e0f0cb16e53878ba5e959a22fc7fcd81b124a,42151 0xdac17f958d2ee523a2206206994597c13d831ec7,5459 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48,4158 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2,4135 ``` ::: ### Streaming queries A query with `SETTINGS stream = true` is a [streaming query](/querying/streaming-queries), which never ends: it keeps returning rows as Amp adds blocks. `ampsql` doesn't stop it, so it never exits. With `--format table`, it prints nothing, since it waits for the last row to size the columns; with the other formats, it prints rows in chunks, and keeps the last ones until a chunk fills. Press Ctrl+C to stop it. [Streaming Queries](/querying/streaming-queries) explains how to receive a stream's rows. ## Output formats `--format` sets how `ampsql` prints the rows: | Format | Prints | | ------- | ------------------------------------------------------ | | `table` | A table to read, with a header row. The default. | | `json` | A JSON array, with an object for each row. | | `jsonl` | A JSON object for each row, one per line (JSON Lines). | | `csv` | Comma-separated values, with a header row. | Here's one query's output in each format. Add `--format json`, `--format jsonl`, or `--format csv` to the command to get the others: ```bash ampsql 'SELECT block_num, timestamp, tx_hash, token FROM "acme/eth_transfers@1.0.0".transfers ORDER BY block_num, log_index LIMIT 2' ``` :::code-group ```txt [table] +-----------+----------------------+------------------------------------------------------------------+------------------------------------------+ | block_num | timestamp | tx_hash | token | +-----------+----------------------+------------------------------------------------------------------+------------------------------------------+ | 24000000 | 2025-12-13T00:06:11Z | 1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048 | c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 | | 24000000 | 2025-12-13T00:06:11Z | 1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048 | a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 | +-----------+----------------------+------------------------------------------------------------------+------------------------------------------+ ``` ```txt [json] [{"block_num":24000000,"timestamp":"2025-12-13T00:06:11Z","tx_hash":"1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048","token":"c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2"},{"block_num":24000000,"timestamp":"2025-12-13T00:06:11Z","tx_hash":"1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048","token":"a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48"}] ``` ```txt [jsonl] {"block_num":24000000,"timestamp":"2025-12-13T00:06:11Z","tx_hash":"1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048","token":"c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2"} {"block_num":24000000,"timestamp":"2025-12-13T00:06:11Z","tx_hash":"1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048","token":"a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48"} ``` ```txt [csv] block_num,timestamp,tx_hash,token 24000000,2025-12-13T00:06:11Z,1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048,c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 24000000,2025-12-13T00:06:11Z,1636fe170a5e662ce65a0f0c7805dbc5aa61646d3df57a8cfe1f8953767df048,a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48 ``` ::: In every format: * **Binary values**, like hashes and addresses, print as hexadecimal without `0x`. To get `0x` hex, select them with `evm_decode_hex`, like `evm_decode_hex(token) AS token` ([EVM functions](/querying/functions/evm)). * **Timestamps** print in ISO 8601, in UTC. * **Null values** print as an empty cell in `table` and `csv`. `json` and `jsonl` leave the field out of the row's object. A query that returns no rows prints an empty table with `table`, and `[]` with `json`. With `jsonl` and `csv`, it prints nothing, not even the CSV header row. `json`, `jsonl`, and `csv` write rows as they arrive from the query server, so a large result doesn't have to fit in memory. `table` waits for every row, to size its columns, so for a large result, use another format or a `LIMIT`. ### Write to a file `-o` writes the rows to a file instead of standard output, and replaces the file if it exists. Messages, like the query `-e` prints and errors, still go to the terminal: ```bash ampsql -f top_tokens.sql --format csv -o top_tokens.csv ``` `-o` opens the file only once the query server has accepted the query. So when `ampsql` can't connect, or the query fails to plan, an existing file keeps its contents, and a missing one isn't created. Once the server has accepted the query, the file is emptied, so a query that fails while its rows arrive leaves it with part of the result, or none. Redirecting standard output with `>` writes the same rows, since `ampsql` prints only rows there, but doesn't protect the file: your shell empties it before `ampsql` starts, so a query that fails leaves it empty. Either way, to keep an existing file until a new export has succeeded, write to another file, then rename it: ```bash ampsql -f top_tokens.sql --format csv -o top_tokens.csv.tmp \ && mv top_tokens.csv.tmp top_tokens.csv ``` ## Logging `ampsql` logs to standard error. `AMP_LOG` sets how much: `error`, `warn`, `info` (the default), `debug`, or `trace`. A query that succeeds logs nothing at the default level. A query that fails logs its error, then prints the `✗` message: `AMP_LOG=off` keeps only the message. `AMP_LOG=debug` shows each step: :::terminal ```bash AMP_LOG=debug ampsql 'SELECT 1 AS x' ``` ``` 2026-10-30T21:15:27.781215Z DEBUG run{flight_url=http://localhost:1602/}: ampsql::cmd::root: Connecting to Arrow Flight endpoint 2026-10-30T21:15:27.783270Z DEBUG run{flight_url=http://localhost:1602/}: ampsql::cmd::root: Executing batch SQL query format=Table +---+ | x | +---+ | 1 | +---+ ``` ::: `AMP_LOG` only sets the level of Amp's own logs. The libraries `ampsql` uses log errors only, unless you set `RUST_LOG`, like `RUST_LOG=h2=debug` for the HTTP/2 connection to the query server. ## Exit codes | Code | Meaning | | ---- | ------------------------------------------------------------------------------------------------------------------------------------------- | | `0` | The query succeeded, even with no rows. | | `1` | The query failed: `ampsql` couldn't read the query or write the rows, couldn't reach Amp, or Amp returned an error. A `✗` message says why. | | `2` | The command line is invalid, like an unknown flag, a `--format` it doesn't know, or two ways to pass the query. | ## Errors Errors print a `✗` message, then the errors that caused it, from the closest to the deepest. Amp's own error is usually the last line, after `message:`. * `` ✗ no query provided: pass SQL as a positional argument, `-c `, or `-f ` ``: you ran `ampsql` without a query. Piping one in doesn't count; use `-f /dev/stdin` ([Run a query](/clis/ampsql#run-a-query)). * `error: the argument '[QUERY]' cannot be used with '--command '`: you passed the query two ways. Keep one. This exits with code 2, like `error: invalid value 'yaml' for '--format '`. * `✗ failed to read query file -`, then `No such file or directory (os error 2)`: `ampsql` found no file at the path `-f` names. For standard input, use `-f /dev/stdin`. * `✗ failed to open output file nodir/out.csv`, then `No such file or directory (os error 2)`: the directory `-o` names doesn't exist. Create it first. * `✗ failed to build Arrow Flight client`: `ampsql` couldn't reach the query server. See [Connect to Amp](/clis/ampsql#connect-to-amp). The last lines say why: * `Connection refused`: nothing listens at that address. * `invalid peer certificate: UnknownIssuer`: the query server's certificate isn't signed by an authority `ampsql` trusts. Set `SSL_CERT_FILE` to the authority's certificate ([Connect to Amp](/clis/ampsql#connect-to-amp)). * `received corrupt message of type InvalidContentType`: the URL is `https://`, but the query server doesn't use TLS. Use `http://`. * `connection error detected: frame with invalid size`, after `✗ query failed`: the query server expects TLS, but the URL isn't `https://`. Use `https://`. * `ampsql` prints nothing and never exits: v0.0.42's `ampsql` hangs when an `OTEL_EXPORTER_OTLP_*` variable that [turns telemetry exports on](/running-amp/telemetry#environment-variables) has a value in its environment, like `OTEL_EXPORTER_OTLP_ENDPOINT` or `OTEL_EXPORTER_OTLP_TRACES_HEADERS`. Unset them all for `ampsql`: ```bash env $(env | grep -oE '^OTEL_EXPORTER_OTLP_[A-Z_]+' | sed 's/^/-u /') ampsql 'SELECT 1 AS x' ``` * `SQL parse error | Caused by: invalid SQL syntax -> … sql parser error: Expected: an SQL statement, found: SELEC at Line: 1, Column: 1`: the query isn't valid SQL. The message names the line and column where it stopped making sense. * `SQL parse error | Caused by: expected a single SQL statement, found 2`: the query holds several statements. Run them one at a time. * `Error during planning: table 'amp.acme/eth_mainet@1.0.0.blocks' not found`: no dataset version or table has that name, or your token can't read its namespace. Check the dataset's name and version, and the table's name, with [`ampctl dataset list`](/clis/ampctl/dataset). * `no common canonical watermark is available for batch snapshot`: the dataset is deployed, but has no blocks yet, because its job hasn't extracted any. Wait for it, and check its progress with [`ampctl dataset status`](/clis/ampctl/dataset). The message arrives as `✗ query failed`, then `gRPC status error`, then `code: 'The service is currently unavailable'`. * `failed to pin plan source-table revisions | Caused by: table 'acme/eth_mainnet@1.0.0.blocks' has no synced data`: the dataset is registered, but was never deployed, so it has no data. [Deploy it](/clis/ampctl/dataset#ampctl-dataset-deploy). * `decoded message length too large`: the query's SQL is about 4 MiB or more. See [Query size](/querying/limits#query-size). * ``malformed `authorization` header``: the token isn't a JWT. See [Authentication](/clis/ampsql#authentication). For the errors queries can hit while they run, like running out of time or memory, see [Limits](/querying/limits).