> **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. # Derived Datasets A derived dataset's tables are SQL queries over other datasets: filter a raw dataset down to one contract's events, decode them into readable columns, or combine several datasets into one table. Amp runs each query as new blocks arrive and stores its results, so querying a derived table is as fast as querying a raw one, and other datasets can build on it in turn. A derived dataset declares the datasets it reads as `dependencies`, and each of its tables as a SQL query over them. Its `kind` is `manifest`. ## Prerequisites * A running Amp you can manage, like one in [solo mode](/running-amp/solo-mode). * [`ampctl`](/clis/ampctl) and [`ampsql`](/clis/ampsql), which install with Amp, and `curl`. * A dataset to build on. The example uses `acme/eth_mainnet@1.0.0`, the Ethereum mainnet dataset that the [Quickstart](/get-started/quickstart) deploys, with blocks 24,000,000 to 24,000,099. ## Create a derived dataset This dataset keeps the transfers of USDC, a token whose contract is at `0xa0b8…eb48`, from Ethereum mainnet's logs, and decodes each into its sender, recipient, and amount. ::::steps ### Write the table's query The query reads the `logs` table of the dependency it calls `eth`, and uses [EVM functions](/querying/functions/evm) to pick out USDC's `Transfer` events and decode them: ```sql SELECT block_num, timestamp, tx_hash, log_index, evm_decode_log(topic1, topic2, topic3, data, 'Transfer(address indexed from, address indexed to, uint256 value)') AS transfer FROM eth.logs WHERE address = evm_encode_hex('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)') ``` ### Get the table's schema The manifest lists each table's columns and their types. Rather than writing them, ask the Admin API for the schema of your queries: it plans them against the dependencies, and returns each table's columns, or the first problem it finds. :::terminal ```bash curl -s -X POST http://localhost:1610/schema \ -H 'content-type: application/json' \ -d '{ "dependencies": { "eth": "acme/eth_mainnet@1.0.0" }, "tables": { "usdc_transfers": "SELECT block_num, timestamp, tx_hash, log_index, evm_decode_log(topic1, topic2, topic3, data, '\''Transfer(address indexed from, address indexed to, uint256 value)'\'') AS transfer FROM eth.logs WHERE address = evm_encode_hex('\''0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'\'') AND topic0 = evm_topic('\''Transfer(address indexed from, address indexed to, uint256 value)'\'')" } }' ``` ```json {"schemas":{"usdc_transfers":{"arrow":{"fields":[{"name":"_block_num","type":"UInt64","nullable":false},{"name":"_ts","type":{"Timestamp":["Nanosecond","+00:00"]},"nullable":false},{"name":"block_num","type":"UInt64","nullable":false},…]}}}} ``` ::: The schema starts with the [reserved columns](/datasets/manifests#reserved-columns) `_block_num` and `_ts`, which the table carries over from `eth.logs`. Keep them. ### Write the manifest Put the query and its schema in the manifest: ```json [usdc_transfers.json] { "kind": "manifest", "dependencies": { "eth": "acme/eth_mainnet@1.0.0" }, "tables": { "usdc_transfers": { "input": { "sql": "SELECT block_num, timestamp, tx_hash, log_index, evm_decode_log(topic1, topic2, topic3, data, 'Transfer(address indexed from, address indexed to, uint256 value)') AS transfer FROM eth.logs WHERE address = evm_encode_hex('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')" }, "schema": { "arrow": { "fields": [ { "name": "_block_num", "type": "UInt64", "nullable": false }, { "name": "_ts", "type": { "Timestamp": ["Nanosecond", "+00:00"] }, "nullable": false }, { "name": "block_num", "type": "UInt64", "nullable": false }, { "name": "timestamp", "type": { "Timestamp": ["Nanosecond", "+00:00"] }, "nullable": false }, { "name": "tx_hash", "type": { "FixedSizeBinary": 32 }, "nullable": false }, { "name": "log_index", "type": "UInt32", "nullable": false }, { "name": "transfer", "type": { "Struct": [ { "name": "from", "data_type": { "FixedSizeBinary": 20 }, "nullable": true, "dict_id": 0, "dict_is_ordered": false, "metadata": {} }, { "name": "to", "data_type": { "FixedSizeBinary": 20 }, "nullable": true, "dict_id": 0, "dict_is_ordered": false, "metadata": {} }, { "name": "value", "data_type": "Utf8", "nullable": true, "dict_id": 0, "dict_is_ordered": false, "metadata": {} } ] }, "nullable": false } ] } } } } } ``` ### Register and deploy it `ampctl dataset register` checks each table's query again, and refuses the manifest if one fails. ```bash ampctl dataset register acme/usdc_transfers ./usdc_transfers.json --tag 1.0.0 ampctl dataset deploy acme/usdc_transfers@1.0.0 ``` The job runs the query over the blocks its dependency has, then over each new one as it arrives. ### Query it :::terminal ```bash ampsql 'SELECT block_num, evm_decode_hex(transfer.to) AS recipient, transfer.value AS amount FROM "acme/usdc_transfers".usdc_transfers ORDER BY block_num, log_index LIMIT 3' ``` ``` +-----------+--------------------------------------------+-------------+ | block_num | recipient | amount | +-----------+--------------------------------------------+-------------+ | 24000000 | 0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640 | 13587372645 | | 24000000 | 0x52aa899454998be5b000ad077a46bbe360f4e497 | 13462155827 | | 24000000 | 0x33a3a62bda3c338c1ec9cf69e4e2fff908dcf20b | 89118973142 | +-----------+--------------------------------------------+-------------+ ``` ::: :::: ## The ERC-20 transfers dataset Pages like [`ampsql`](/clis/ampsql) and [`ampctl dataset`](/clis/ampctl/dataset) use this dataset in their examples: `acme/eth_transfers@1.0.0`. Its `transfers` table has a row for each `Transfer` event in the Quickstart's blocks: the event that token contracts following Ethereum's ERC-20 standard emit when tokens move. `token` is the address of the contract that emitted it. ::::steps ### Write the manifest Create the file in the directory you ran the [Quickstart](/get-started/quickstart) in. The schema is what the Admin API returns for the query, [as above](/datasets/derived-datasets#get-the-tables-schema). ```json [eth_transfers.json] { "kind": "manifest", "dependencies": { "eth": "acme/eth_mainnet@1.0.0" }, "tables": { "transfers": { "input": { "sql": "SELECT block_num, timestamp, tx_hash, log_index, address AS token FROM eth.logs WHERE topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')" }, "schema": { "arrow": { "fields": [ { "name": "_block_num", "type": "UInt64", "nullable": false }, { "name": "_ts", "type": { "Timestamp": ["Nanosecond", "+00:00"] }, "nullable": false }, { "name": "block_num", "type": "UInt64", "nullable": false }, { "name": "timestamp", "type": { "Timestamp": ["Nanosecond", "+00:00"] }, "nullable": false }, { "name": "tx_hash", "type": { "FixedSizeBinary": 32 }, "nullable": false }, { "name": "log_index", "type": "UInt32", "nullable": false }, { "name": "token", "type": { "FixedSizeBinary": 20 }, "nullable": false } ] } } } } } ``` ### Register and deploy it :::terminal ```bash ampctl dataset register acme/eth_transfers ./eth_transfers.json --tag 1.0.0 ``` ``` ✓ Dataset registered successfully → Namespace: acme → Name: eth_transfers → Version: 1.0.0 → Hash: fceb18fb7f3d2be31ea324a0d8760ff3e3440c212a7bea0a0b65016ff613af26 → Kind: manifest → Start block: 0 → Finalized only: false → Tables: transfers ``` ::: The Quickstart's dataset stops at block 24,000,099, so deploy this one with the same end block, for its job to complete once it has read them all: :::terminal ```bash ampctl dataset deploy acme/eth_transfers@1.0.0 --end-block 24000099 ``` ``` ✓ Dataset deployed successfully → Job ID: 4 ``` ::: ### Query it :::terminal ```bash ampsql '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 3' ``` ``` +--------------------------------------------+-----------+ | token | transfers | +--------------------------------------------+-----------+ | 0x0d4a11d5eeaac28ec3f61d100daf4d40471f1852 | 43168 | | 0x609e0f0cb16e53878ba5e959a22fc7fcd81b124a | 42151 | | 0xdac17f958d2ee523a2206206994597c13d831ec7 | 5459 | +--------------------------------------------+-----------+ ``` ::: :::: ## Dependencies `dependencies` maps an alias of your choice to each dataset the tables read. The SQL refers to a dependency's tables through its alias, like `eth.logs`. An alias starts with a letter, followed by letters, digits, and underscores, and is at most 63 characters. Don't use `self`: `self.` always refers to the dataset's own tables. A dependency names a version or a manifest hash, like `acme/eth_mainnet@1.0.0` or `acme/eth_mainnet@b9e2ebd9…`. `latest` and `dev` aren't allowed: they move to other manifests as new ones are registered, which would change your dataset's data without changing the dataset. A version moves too if someone registers another manifest with it, and a job reads the manifest the version points to when the job starts: to be sure a dependency never changes, name it by its hash. See [Versioning](/datasets/versioning). A dependency must be registered before you register a dataset that reads it, and deployed for your dataset to have data: a derived table only has the blocks its dependencies have. ## Tables that read each other (Unstable) A table can read another table of the same dataset through the `self.` prefix: ```json { "tables": { "usdc_transfers": { "input": { "sql": "SELECT block_num, tx_hash, log_index, topic1, data FROM eth.logs WHERE address = evm_encode_hex('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')" }, "schema": { "arrow": { "fields": [] } } }, "usdc_mints": { "input": { "sql": "SELECT block_num, tx_hash, log_index FROM self.usdc_transfers WHERE topic1 = evm_encode_hex('0x0000000000000000000000000000000000000000000000000000000000000000')" }, "schema": { "arrow": { "fields": [] } } } } } ``` `usdc_mints` keeps the transfers from the zero address, which is how USDC records newly issued tokens. (The schemas are left empty for brevity: get them from the Admin API as above, which resolves `self.` too.) * Amp works out the order tables depend on each other in, and fills a table after those it reads. * A table only exposes the columns its own schema lists, so a table that reads it can only select those. * Use `self.`, not the dataset's own name: the dataset has no name yet when Amp checks the manifest. * Tables can't depend on each other in a loop, and a table can't read itself. ## Functions The tables' SQL can call Amp's built-in functions, like the [EVM functions](/querying/functions/evm) above, and [JavaScript functions](/querying/functions/javascript) the manifest defines in `functions`. It can also read contracts' state with [`eth_call`](/querying/functions/eth-call#in-streaming-queries-and-derived-datasets), which calls the chain's RPC endpoint as the job processes each range of blocks. A table calls its own dataset's functions with `self.`, like `self.toHex(tx_index)`, and a dependency's with its alias. ## Where a table starts A new derived table starts from its dependencies' first block. When its query filters on the block number, Amp skips the blocks the filter rules out instead of running the query over them: ```sql SELECT block_num, tx_hash, log_index FROM eth.logs WHERE block_num >= 24000050 ``` Amp does this only when it's sure the filter applies to every row of the result, for a dataset deployed without an end block, and reading a single network. Otherwise, the table starts from the first block. A table resumed after a stop continues from where it left off. ## What a table's query can do Amp keeps a derived table up to date by running its query on each new range of blocks, so the query must work block by block: * **No aggregates or sorting**, like `GROUP BY`, `count(*)`, or `ORDER BY`: their result for one range of blocks would change when the next arrives. Aggregate and sort when you query the table instead. * **Joins**: between tables with a common watermark column (`_block_num`, or `_ts` across networks). A query can have any number of block-aligned joins, but at most one general join on any chain of nested joins, and no outer or anti joins. See [Joins](/querying/joins#streaming-joins). * **One network or several**: a table that reads several networks orders their data by `_ts`, so its schema has `_ts`, and `_block_num` only if its query selects it. It can only be deployed to keep up with the chains: with `--end-block`, the job fails. * **No static datasets**: a derived table can't read a [static dataset](/datasets/static-datasets). ## Errors `ampctl dataset register` and the Admin API's schema endpoint check each table's query before you deploy. Both stop at the first table that fails. `ampctl dataset register` fails with `MANIFEST_VALIDATION_ERROR`, and a message that names the table and the problem, like `Table 'usdc_transfers' contains non-incremental SQL`. The schema endpoint answers with a code for the problem: | Code | Cause | Fix | | ---------------------------------- | ----------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------- | | `NON_INCREMENTAL_QUERY` | The query aggregates, sorts, nests a join in another, or does something else that can't run block by block. | Aggregate when you query the table instead. Split nested joins into tables that each join once, reading each other with `self.`. | | `INVALID_PLAN` | The query doesn't plan, like a function called with the wrong types. | Read the message's cause, like `No function matches the given name and argument types`. | | `INVALID_TABLE_SQL` | The SQL doesn't parse. | Fix the syntax the message points to. | | `MISSING_BLOCK_NUM` | The tables a single-network query reads have no `_block_num` column. | Read tables with a `_block_num` column. | | `MISSING_TS` | The tables a query across networks reads have no `_ts` column, or a `timestamp` column in its place. | Read tables with a `_ts` column. | | `STATIC_SOURCE_NOT_MATERIALIZABLE` | The query reads a static dataset. | Join the static dataset when you query the table instead. | | `CYCLIC_DEPENDENCY` | Tables read each other in a loop. | Break the loop. | | `SELF_REFERENCING_TABLE` | A table reads itself. | Read another table. | | `SELF_REF_TABLE_NOT_FOUND` | A `self.` reference names a table the dataset doesn't have. | Fix the table's name. | | `NO_TABLE_REFERENCES` | The query reads no table, like `SELECT 1`. | Read at least one table. | | `CATALOG_QUALIFIED_TABLE` | The query names a table in three parts, like `a.b.c`. | Use `alias.table` or `self.table`. | A dependency that names `latest` or `dev` fails with `Symbolic reference 'latest' not allowed (use explicit version or hash)`. `ampctl dataset register` doesn't check a table's [reserved columns](/datasets/manifests#reserved-columns) against its query. A schema with the wrong ones registers, then the job that fills the table fails, or retries until you stop it. `ampctl dataset status` shows the error. To fix it, use the schema the Admin API returns: | The table reads | Its schema has | The job | Error | | ---------------- | -------------------------------------------------- | ------- | -------------------------------------------------------------------------------------------- | | One network | `_ts`, no `_block_num` | Fails | `block is missing required timestamp for watermark computation` | | One network | Neither | Fails | `all source tables must have at least one watermark column (_block_num or _ts)` | | Several networks | No `_ts` | Fails | `all source tables must have at least one watermark column (_block_num or _ts)` | | Several networks | `_ts`, and a `_block_num` the query doesn't select | Retries | `Arrow: record batch has 6 columns but the writer schema has 7`, with the table's own counts | A table with no `_ts` can't be read across networks, whatever the reading table's schema. The schema endpoint answers `MISSING_TS`, unless that table has a `timestamp` column: then it returns a schema, and the job retries with `Schema error: No field named _ts`. Deploy a new version of that table, with `_ts`, and read that.