48/ 使用 BigQuery 分析区块链数据入门指南

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

例如，在众多的以太坊ERC20代币中，哪些货币符号的重复率最高？ 期货交易所BitMEX中余额最大的比特币冷钱包有哪些？ 回答这些问题通常需要运行全节点同步区块，然后编写程序遍历数据库。

这两步比较麻烦。 首先，下载区块需要大量的存储空间，从头开始同步至少需要几十个小时。 比特币的数据比较小，没有索引大约210GB（2019年3月）； 以太坊已经超过1TB，即使放弃全同步，用geth fast sync也需要130GB（见）。 其次，即使有开源库的辅助，自定义程序遍历区块数据也是费时费力的。

在经历了以上痛苦之后，接触到BigQuery这件宝物让我感到非常高兴，所以写这篇文章来介绍它的基本用法，帮助读者提高数据分析的效率。

BigQuery() 是谷歌云平台的大数据高性能查询服务。 配合谷歌免费发布的区块链数据集，我们现在只要写SQL语句就可以快速查询BTC、BCH、ETH、ETC、LTC、Dash、ZCash、Dogecoin。

首先，我假设你已经掌握了科学或物理翻墙上网的基本技巧。 如果您还没有开通谷歌云账号，请访问免费申请。 登录后访问如下图。

BigQuery 主界面

展开左侧Resources栏中的bigquery-public-data公开数据，找到以“crypto_”开头的区块链集合。 你还会看到一个名为bitcoin_blockchain的比特币区块集合，它使用较旧的表结构并且没有区块（缺点将在后面解释），因此建议使用新的crypto_bitcoin来分析比特币。

常用图标

上图显示了 BigQuery 中的三个常用图标。 表是普通的关系数据库表。 视图（view）是用SELECT语句建立的虚拟表。 分区表的使用方式与普通表相同，只是底层按照入账时间或时间类型字段分块存储，可以加快查询速度，降低成本。

你会注意到数据集 crypto_bitcoin、crypto_bitcoin_cash、crypto_dash、crypto_dogecoin、crypto_litecoin 和 crypto_zcash 具有相同的结构（毕竟 BCH、DASH、DOGE、LTC 和 ZEC 都是 BTC 的直系血亲）。 它们都包括一个区块基本信息表区块，一个交易明细表交易，两个基于交易输入和输出的视图。 这意味着通过更改 FROM 子句中的表名，可以立即将相同的 SQL 语句用于另一种加密货币，非常方便。

ETH和ETC支持智能合约，不使用UTXO架构，所以它们的BigQuery表结构比较复杂，但是crypto_ethereum和crypto_ethereum_classic之间的SQL还是很常见的。

我们回到开头提到的两个例子，尝试用BigQuery来解决。

例1：在ERC20代币中，哪些货币符号的重复率最高？

ERC20代币协议（见）允许合约指定一个货币符号btc图标，但没有统一的注册机制，也不禁止重名。 从 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的冷钱包（见）采用多重签名（见），地址以“3BMEX”开头。 所以我们过滤地址前缀，最后按余额降序排序（除以1亿使用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 DESCLIMIT 100

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

BitMEX 冷钱包余额排名
BigQuery 接受符合 SQL 2011 的语句，并具有支持嵌套和重复字段的内置扩展。 请参考详细文档。
最后说说BigQuery在区块链分析方面的不足。 首先，这些数据集每天更新一次，不适合实时性要求高的应用。 其次，虽然检索区块链等公共数据集不需要支付存储费用，但查询本身是根据数据量收费的，这有点贵，每TB 5美元——即使有块表优化，运行一个单个 SQL 可能涉及近百 GB 的数据。 幸运的是，每月前 1 TB 是免费的，谷歌云的新用户有 300 美元的免费配额可用。 再次，表结构中没有冗余信息，这使得一些看似简单的查询变得复杂且难以编写。 例2中比特币地址的余额计算比较繁琐btc图标，而以太坊允许通过合约转账，所以余额的计算需要额外考虑各种情况，令人抓狂。 以下是 Evgeny Medvedev 编写的用于计算以太坊余额排名的 SQL：
#standardSQL-- MIT License-- Copyright (c) 2018 Evgeny Medvedev, evge.medvedev@gmail.comwith 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 desclimit 10

像这样的区块链查询不适合 BigQuery，除非将来在数据集中添加辅助表。
当然，BigQuery 有其缺陷。 它在一个小时内解决了可能需要花费几天开发时间的问题，让我什至懒得去探索它，成为我分析区块链数据的重要工具之一。