query-onchain-data

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Query Onchain Data on Base

查询Base链上数据

Use the CDP SQL API to query onchain data (events, transactions, blocks, transfers) on Base. Queries are executed via x402 and are charged per query.

使用CDP SQL API查询Base链上的链上数据（事件、交易、区块、转账）。查询通过x402执行，按次收费。

Confirm wallet is initialized and authed

确认钱包已初始化并完成认证

bash

npx awal@latest status

If the wallet is not authenticated, refer to the

authenticate-wallet

skill.

bash

npx awal@latest status

如果钱包未完成认证，请参考

authenticate-wallet

技能。

Executing a Query

执行查询

bash

npx awal@latest x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "<YOUR_QUERY>"}' --json

IMPORTANT: Always single-quote the

-d

JSON string to prevent bash variable expansion.

bash

npx awal@latest x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "<YOUR_QUERY>"}' --json

重要提示：始终对

-d

后的JSON字符串使用单引号，以避免bash变量展开。

CRITICAL: Indexed Fields

关键注意事项：索引字段

Queries against

base.events

MUST filter on indexed fields to avoid full table scans. The indexed fields are:

Indexed Field	Use For
`event_signature`	Filter by event type. Use this instead of `event_name` for performance.
`address`	Filter by contract address.
`block_timestamp`	Filter by time range.

Always include at least one indexed field in your WHERE clause. Combining all three gives the best performance.

针对

base.events

的查询必须过滤索引字段，以避免全表扫描。索引字段如下：

索引字段	用途
`event_signature`	按事件类型过滤。为提升性能，请使用此字段而非 `event_name` 。
`address`	按合约地址过滤。
`block_timestamp`	按时间范围过滤。

请始终在WHERE子句中包含至少一个索引字段。同时使用三个字段可获得最佳性能。

CoinbaseQL Syntax

CoinbaseQL语法

CoinbaseQL is a SQL dialect based on ClickHouse. Supported features:

Clauses: SELECT (DISTINCT), FROM, WHERE, GROUP BY, ORDER BY (ASC/DESC), LIMIT, WITH (CTEs), UNION (ALL/DISTINCT)
Joins: INNER, LEFT, RIGHT, FULL with ON
Operators:
```
=
```
,
```
!=
```
,
```
<>
```
,
```
<
```
,
```
>
```
,
```
<=
```
,
```
>=
```
,
```
+
```
,
```
-
```
,
```
*
```
,
```
/
```
,
```
%
```
, AND, OR, NOT, BETWEEN, IN, IS NULL, LIKE
Expressions: CASE/WHEN/THEN/ELSE, CAST (both
```
CAST()
```
and
```
::
```
syntax), subqueries, array/map indexing with
```
[]
```
, dot notation
Literals: Array
```
[...]
```
, Map
```
{...}
```
, Tuple
```
(...)
```
Functions: Standard SQL functions, lambda functions with
```
->
```
syntax

CoinbaseQL是基于ClickHouse的SQL方言。支持的特性包括：

子句：SELECT（DISTINCT）、FROM、WHERE、GROUP BY、ORDER BY（ASC/DESC）、LIMIT、WITH（CTE）、UNION（ALL/DISTINCT）
连接：INNER、LEFT、RIGHT、FULL JOIN（带ON条件）
运算符：
```
=
```
,
```
!=
```
,
```
<>
```
,
```
<
```
,
```
>
```
,
```
<=
```
,
```
>=
```
,
```
+
```
,
```
-
```
,
```
*
```
,
```
/
```
,
```
%
```
, AND, OR, NOT, BETWEEN, IN, IS NULL, LIKE
表达式：CASE/WHEN/THEN/ELSE、CAST（支持
```
CAST()
```
和
```
::
```
语法）、子查询、使用
```
[]
```
的数组/映射索引、点表示法
字面量：数组
```
[...]
```
、映射
```
{...}
```
、元组
```
(...)
```
函数：标准SQL函数、使用
```
->
```
语法的Lambda函数

Available Tables

可用表

base.events

Decoded event logs from smart contract interactions. This is the primary table for most queries.

Column	Type	Description
log_id	String	Unique log identifier
block_number	UInt64	Block number
block_hash	FixedString(66)	Block hash
block_timestamp	DateTime64(3, 'UTC')	Block timestamp (INDEXED)
transaction_hash	FixedString(66)	Transaction hash
transaction_to	FixedString(42)	Transaction recipient
transaction_from	FixedString(42)	Transaction sender
log_index	UInt32	Log index within block
address	FixedString(42)	Contract address (INDEXED)
topics	Array(FixedString(66))	Event topics
event_name	LowCardinality(String)	Decoded event name
event_signature	LowCardinality(String)	Event signature (INDEXED - prefer over event_name)
parameters	Map(String, Variant(Bool, Int256, String, UInt256))	Decoded event parameters
parameter_types	Map(String, String)	ABI types for parameters
action	Enum8('removed' = -1, 'added' = 1)	Added or removed (reorg)

智能合约交互的已解析事件日志。这是大多数查询的主表。

列名	类型	描述
log_id	String	唯一日志标识符
block_number	UInt64	区块编号
block_hash	FixedString(66)	区块哈希
block_timestamp	DateTime64(3, 'UTC')	区块时间戳（已索引）
transaction_hash	FixedString(66)	交易哈希
transaction_to	FixedString(42)	交易接收方
transaction_from	FixedString(42)	交易发送方
log_index	UInt32	区块内的日志索引
address	FixedString(42)	合约地址（已索引）
topics	Array(FixedString(66))	事件主题
event_name	LowCardinality(String)	已解析的事件名称
event_signature	LowCardinality(String)	事件签名（已索引 - 优先于event_name使用）
parameters	Map(String, Variant(Bool, Int256, String, UInt256))	已解析的事件参数
parameter_types	Map(String, String)	参数的ABI类型
action	Enum8('removed' = -1, 'added' = 1)	新增或移除（重组）

base.transactions

Complete transaction data.

Column	Type	Description
block_number	UInt64	Block number
block_hash	String	Block hash
transaction_hash	String	Transaction hash
transaction_index	UInt64	Index in block
from_address	String	Sender address
to_address	String	Recipient address
value	String	Value transferred (wei)
gas	UInt64	Gas limit
gas_price	UInt64	Gas price
input	String	Input data
nonce	UInt64	Sender nonce
type	UInt64	Transaction type
max_fee_per_gas	UInt64	EIP-1559 max fee
max_priority_fee_per_gas	UInt64	EIP-1559 priority fee
chain_id	UInt64	Chain ID
v	String	Signature v
r	String	Signature r
s	String	Signature s
is_system_tx	Bool	System transaction flag
max_fee_per_blob_gas	String	Blob gas fee
blob_versioned_hashes	Array(String)	Blob hashes
timestamp	DateTime	Block timestamp
action	Int8	Added (1) or removed (-1)

完整的交易数据。

列名	类型	描述
block_number	UInt64	区块编号
block_hash	String	区块哈希
transaction_hash	String	交易哈希
transaction_index	UInt64	区块内的索引
from_address	String	发送方地址
to_address	String	接收方地址
value	String	转账金额（wei）
gas	UInt64	燃气限额
gas_price	UInt64	燃气价格
input	String	输入数据
nonce	UInt64	发送方随机数
type	UInt64	交易类型
max_fee_per_gas	UInt64	EIP-1559最高燃气费
max_priority_fee_per_gas	UInt64	EIP-1559优先燃气费
chain_id	UInt64	链ID
v	String	签名v值
r	String	签名r值
s	String	签名s值
is_system_tx	Bool	系统交易标记
max_fee_per_blob_gas	String	Blob燃气费
blob_versioned_hashes	Array(String)	Blob哈希
timestamp	DateTime	区块时间戳
action	Int8	新增（1）或移除（-1）

base.blocks

Block-level metadata.

Column	Type	Description
block_number	UInt64	Block number
block_hash	String	Block hash
parent_hash	String	Parent block hash
timestamp	DateTime	Block timestamp
miner	String	Block producer
nonce	UInt64	Block nonce
sha3_uncles	String	Uncles hash
transactions_root	String	Transactions merkle root
state_root	String	State merkle root
receipts_root	String	Receipts merkle root
logs_bloom	String	Bloom filter
gas_limit	UInt64	Block gas limit
gas_used	UInt64	Gas used in block
base_fee_per_gas	UInt64	Base fee per gas
total_difficulty	String	Total chain difficulty
size	UInt64	Block size in bytes
extra_data	String	Extra data field
mix_hash	String	Mix hash
withdrawals_root	String	Withdrawals root
parent_beacon_block_root	String	Beacon chain parent root
blob_gas_used	UInt64	Blob gas used
excess_blob_gas	UInt64	Excess blob gas
transaction_count	UInt64	Number of transactions
action	Int8	Added (1) or removed (-1)

区块级元数据。

列名	类型	描述
block_number	UInt64	区块编号
block_hash	String	区块哈希
parent_hash	String	父区块哈希
timestamp	DateTime	区块时间戳
miner	String	区块生产者
nonce	UInt64	区块随机数
sha3_uncles	String	叔块哈希
transactions_root	String	交易默克尔根
state_root	String	状态默克尔根
receipts_root	String	收据默克尔根
logs_bloom	String	布隆过滤器
gas_limit	UInt64	区块燃气限额
gas_used	UInt64	区块内已使用燃气
base_fee_per_gas	UInt64	基础燃气费
total_difficulty	String	链总难度
size	UInt64	区块大小（字节）
extra_data	String	额外数据字段
mix_hash	String	混合哈希
withdrawals_root	String	提款根
parent_beacon_block_root	String	信标链父根
blob_gas_used	UInt64	已使用Blob燃气
excess_blob_gas	UInt64	剩余Blob燃气
transaction_count	UInt64	交易数量
action	Int8	新增（1）或移除（-1）

Example Queries

查询示例

Get recent USDC Transfer events with decoded parameters

获取最近7天带解析参数的USDC转账事件

sql

SELECT
  parameters['from'] AS sender,
  parameters['to'] AS to,
  parameters['value'] AS amount,
  address AS token_address
FROM base.events
WHERE
  event_signature = 'Transfer(address,address,uint256)'
  AND address = '0x833589fcd6edb6e08f4c7c32d4f71b54bda02913'
  AND block_timestamp >= now() - INTERVAL 7 DAY
LIMIT 10

sql

SELECT
  parameters['from'] AS sender,
  parameters['to'] AS to,
  parameters['value'] AS amount,
  address AS token_address
FROM base.events
WHERE
  event_signature = 'Transfer(address,address,uint256)'
  AND address = '0x833589fcd6edb6e08f4c7c32d4f71b54bda02913'
  AND block_timestamp >= now() - INTERVAL 7 DAY
LIMIT 10

Get transactions from a specific address

获取特定地址的交易记录

bash

npx awal@latest x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT transaction_hash, to_address, value, gas, timestamp FROM base.transactions WHERE from_address = lower('\''0xYOUR_ADDRESS'\'') AND timestamp >= now() - INTERVAL 1 DAY LIMIT 10"}' --json

bash

npx awal@latest x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT transaction_hash, to_address, value, gas, timestamp FROM base.transactions WHERE from_address = lower('\''0xYOUR_ADDRESS'\'') AND timestamp >= now() - INTERVAL 1 DAY LIMIT 10"}' --json

Count events by type for a contract in the last hour

统计最近1小时内某合约的事件类型数量

bash

npx awal@latest x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT event_signature, count(*) as cnt FROM base.events WHERE address = lower('\''0xCONTRACT_ADDRESS'\'') AND block_timestamp >= now() - INTERVAL 1 HOUR GROUP BY event_signature ORDER BY cnt DESC LIMIT 20"}' --json

bash

npx awal@latest x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT event_signature, count(*) as cnt FROM base.events WHERE address = lower('\''0xCONTRACT_ADDRESS'\'') AND block_timestamp >= now() - INTERVAL 1 HOUR GROUP BY event_signature ORDER BY cnt DESC LIMIT 20"}' --json

Get latest block info

获取最新区块信息

bash

npx awal@latest x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT block_number, timestamp, transaction_count, gas_used FROM base.blocks ORDER BY block_number DESC LIMIT 1"}' --json

bash

npx awal@latest x402 pay https://x402.cdp.coinbase.com/platform/v2/data/query/run -X POST -d '{"sql": "SELECT block_number, timestamp, transaction_count, gas_used FROM base.blocks ORDER BY block_number DESC LIMIT 1"}' --json

Common Contract Addresses (Base)

Base链常用合约地址

Token	Address
USDC	`0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913`
WETH	`0x4200000000000000000000000000000000000006`

代币	地址
USDC	`0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913`
WETH	`0x4200000000000000000000000000000000000006`

Best Practices

最佳实践

Always filter on indexed fields (

event_signature

address

block_timestamp

) in

base.events

queries.

Never use
SELECT *
- specify only the columns you need.
Always include a
LIMIT
clause to bound result size.
Use
event_signature
instead of
event_name
for filtering - it is indexed and much faster.
Use time-bounded queries with
```
block_timestamp
```
to narrow the scan range.
Always wrap address values in
lower()
- the database stores lowercase addresses but users may provide checksummed (mixed-case) addresses. Use
```
address = lower('0xAbC...')
```
not
```
address = '0xAbC...'
```
.

Common event signatures:

Transfer(address,address,uint256)

Approval(address,address,uint256)

Swap(address,uint256,uint256,uint256,uint256,address)

始终过滤索引字段：在
```
base.events
```
查询中使用
```
event_signature
```
、
```
address
```
、
```
block_timestamp
```
切勿使用
SELECT *
：仅指定你需要的列
始终包含
LIMIT
子句：限制结果集大小
优先使用
event_signature
而非
event_name
过滤：它已被索引，速度更快
使用时间范围查询：通过
```
block_timestamp
```
缩小扫描范围
始终用
lower()
包裹地址值：数据库存储小写地址，但用户可能提供校验和格式（大小写混合）的地址。请使用
```
address = lower('0xAbC...')
```
而非
```
address = '0xAbC...'
```

常见事件签名：

Transfer(address,address,uint256)

、

Approval(address,address,uint256)

、

Swap(address,uint256,uint256,uint256,uint256,address)

Prerequisites

前置条件

Must be authenticated (
```
npx awal@latest status
```
to check, see
```
authenticate-wallet
```
skill)
Wallet must have sufficient USDC balance (
```
npx awal@latest balance
```
to check)
Each query costs $0.10 (100000 USDC atomic units)

必须完成认证（使用
```
npx awal@latest status
```
检查，参考
```
authenticate-wallet
```
技能）
钱包必须有足够的USDC余额（使用
```
npx awal@latest balance
```
检查）
每次查询费用为0.10美元（100000 USDC最小单位）

Error Handling

错误处理

"Not authenticated" - Run
```
awal auth login <email>
```
first, or see
```
authenticate-wallet
```
skill
"Insufficient balance" - Fund wallet with USDC; see
```
fund
```
skill
Query timeout or error - Ensure you are filtering on indexed fields and using a LIMIT

"Not authenticated"（未认证）：先运行
```
awal auth login <email>
```
，或参考
```
authenticate-wallet
```
技能
"Insufficient balance"（余额不足）：为钱包充值USDC；参考
```
fund
```
技能
查询超时或出错：确保你正在过滤索引字段并使用了LIMIT子句