主流加密货币的数据全是公开透明的，我们可以用blockchain.com、etherscan.io之类开放的区块链浏览器方便地查询余额等基本信息。但若要作一些非常规的数据查询就没那么容易了。

比方说，在众多以太坊ERC20代币之中，货币符号重名率最高的是哪些？期货交易所BitMEX余额最多的比特币冷钱包有哪些？回答这些问题通常需要运行全节点同步区块，再写程序遍历资料库。

这两步都颇为麻烦。首先，下载区块需要大量存储空间，如果从零开始同步至少需要几十个小时。比特币的数据相对较小，不计索引大约210GB（2019年三月）；以太坊则早就超过1TB，即使放弃全同步，用geth fast sync也要130GB（参阅《8/ 应对区块链数据大爆炸》）。其次，即使有开源库的辅助，定制程序遍历区块数据也费时费力。

在体验过以上痛苦后再接触BigQuery这件宝物时，我感到格外欣喜，所以写下此文介绍它的基本用法，帮助读者提高数据分析的效率。

BigQuery（cloud.google.com/bigque）是Google Cloud Platform的大数据高性能查询服务。配合Google免费发布的区块链数据集，我们现在只要写SQL语句就能快速查询BTC、BCH、ETH、ETC、LTC、Dash、ZCash、Dogecoin多种加密货币了。

首先，我假定你已掌握科学上网的基本技能或肉身翻墙了。如果你还没有开通Google Cloud账号，请访问cloud.google.com/start/免费申请。登录后访问console.cloud.google.com，如下所示。

BigQuery主界面

展开左侧Resources栏的bigquery-public-data公共数据，找到"crypto_"开头的区块链合集。你还会看到一个名为bitcoin_blockchain的比特币区块合集，它采用的表结构较老，而且没有分块（之后会解释其弊端），所以建议使用新的crypto_bitcoin分析比特币。

常见图标

上图是BigQuery里常见的三个图标。表（table）是普通的关系型资料库表。视图（view）是用SELECT语句构建的虚表。分块表（partitioned table）使用起来与普通表无异，但底层是按录入时间或时间型栏位分块存储的，可以加快查询同时降低成本。

你会注意到，crypto_bitcoin、crypto_bitcoin_cash、crypto_dash、crypto_dogecoin、crypto_litecoin、crypto_zcash这几个数据集的结构相同（毕竟BCH、DASH、DOGE、LTC、ZEC都是BTC的直系血亲）。它们都包含一张区块基本信息表blocks、一张交易详情表transactions、两个基于transactions的视图inputs与outputs。这意味著，同一条SQL语句只要改FROM子句中的表名就立马可以用于另一种加密货币了，极其便捷。

ETH和ETC支持智能合约、不用UTXO架构，所以它们的BigQuery表结构复杂一些，但crypto_ethereum与crypto_ethereum_classic之间SQL仍可以通用。

让我们回到开篇提到的两个实例，尝试用BigQuery解答。

例子1：ERC20代币之中，货币符号重名率最高的是哪些？

ERC20代币协议（参阅《32/ 详解ERC20与ERC721》）允许合约指定一个货币符号，但并没有统一的注册机制，不禁止重名。我们可以从crypto_ethereum.tokens表中很方便地按重名率找出最热门的货币符号：

SELECT symbol, COUNT(*) AS dup
FROM `bigquery-public-data.crypto_ethereum.tokens`
GROUP BY symbol ORDER BY dup DESC

结果前几名都出乎我的意料：

重名率最高的ERC20代币符号

例子2：期货交易所BitMEX余额最多的比特币冷钱包有哪些？

这个例子复杂一些。首先，crypto_bitcoin原始表不直接包含余额。变通方法是分别从inputs视图中提取打款地址和对应的转出金额，从outputs视图中提取收款地址和对应的转入金额，再用UNION ALL合并结果，将所有转入金额减去所有转出金额就能得到余额（以下SQL语句对转入金额和取负后的转出金额求和，效果等价）。其次，我们知道BitMEX（参阅《25/ BitMEX期货交易指南》）的冷钱包采用多重签名（参阅《20/ 详解比特币P2SH地址与multisig》），并且地址以"3BMEX"开头。所以我们过滤地址前缀，最后按余额降序排列（除以一亿是为了以BTC而非satoshi作单位）。

WITH double_entry_book AS (
SELECT array_to_string(inputs.addresses, ",") as address, -inputs.value as value
FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
UNION ALL
SELECT array_to_string(outputs.addresses, ",") as address, outputs.value as value
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
)
SELECT address, sum(value)/100000000 as balance
FROM double_entry_book
WHERE starts_with(address, "3BMEX")
GROUP BY address
ORDER BY balance DESC
LIMIT 100

执行以上语句，不到7秒就能得到结果：

BitMEX冷钱包余额排名

BigQuery接受符合SQL 2011标准的语句，另外内置了支持嵌套和重复栏位的扩展。详细文档请参阅cloud.google.com/bigque。

最后说说BigQuery在区块链分析方面的不足之处。首先，这些数据集每日更新一次，所以不适合对实时性要求高的应用场合。其次，虽然检索区块链等公共数据集无需支付存储费用，但查询本身按数据量计费，每TB$5，有点小贵——即便有分块表优化，运行一条SQL也可能牵扯到近百GB数据。好在每月前1 TB免费，另外Google Cloud新用户有$300的免费额度可用。再次，表结构没有什么冗余信息，导致某些貌似容易的查询变得复杂难写。例子2中的比特币地址余额计算已经比较繁琐了，而以太坊允许通过合约转账，所以余额计算要额外考虑多种情况，令人抓狂。以下是Evgeny Medvedev写的用于计算以太坊余额排名的SQL：

#standardSQL
-- MIT License
-- Copyright (c) 2018 Evgeny Medvedev, [email protected]
with double_entry_book as (
-- debits
select to_address as address, value as value
from `bigquery-public-data.ethereum_blockchain.traces`
where to_address is not null
and status = 1
and (call_type not in (delegatecall, callcode, staticcall) or call_type is null)
union all
-- credits
select from_address as address, -value as value
from `bigquery-public-data.ethereum_blockchain.traces`
where from_address is not null
and status = 1
and (call_type not in (delegatecall, callcode, staticcall) or call_type is null)
union all
-- transaction fees debits
select miner as address, sum(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value
from `bigquery-public-data.ethereum_blockchain.transactions` as transactions
join `bigquery-public-data.ethereum_blockchain.blocks` as blocks on blocks.number = transactions.block_number
group by blocks.miner
union all
-- transaction fees credits
select from_address as address, -(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value
from `bigquery-public-data.ethereum_blockchain.transactions`
)
select address, sum(value) as balance
from double_entry_book
group by address
order by balance desc
limit 10

类似这样的区块链查询不太适合BigQuery，除非以后数据集增加辅助表。

当然，BigQuery瑕不掩瑜。它在一小时内解决了原本可能需要几天开发时间以至于我根本懒得去探究的问题，成为我分析区块链数据的重要工具之一。

欢迎关注微信公众号maobencong，获取更多原创内容。


推荐阅读：
相关文章