主流加密貨幣的數據全是公開透明的，我們可以用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，獲取更多原創內容。


推薦閱讀：
相关文章