调试错误、查看表结构、交易 input,output 数量分布 - 使用 Google Big Query API 处理比特币数据（三）

目录

1. 报错的 SQL
   1. 获取表 schema
   2. 获取一条数据
   3. 原来 bigquery 有两个不同的比特币数据集
   4. 最终代码与可视化

前面两篇文章：
Kaggle 上的比特币链上数据集 - 使用 Google Big Query API 处理比特币数据（一）
总结了 kaggle 上入门 kernal 的代码

用 SQL 获取 bigquery 比特币数据 - 使用 Google Big Query API 处理比特币数据（二）
使用最简单的 SQL 获取原始数据

报错的 SQL

写了一个统计比特币交易输入输出数量的查询

from google.cloud import bigquery

client = bigquery.Client()
query = "SELECT SUM(output_count),  COUNT(1) AS tx_count FROM `bigquery-public-data.bitcoin_blockchain.transactions` as tx GROUP BY output_count"
r = client.query(query)
df = r.to_dataframe()

结果报错：

BadRequest: 400 Unrecognized name: output_count at [1:12]

意思是没有这一列？

我于是回到数据集主页查看，结果发现有这一列。

获取表 schema

参考 notebook

dataset_ref = client.dataset("bitcoin_blockchain", project="bigquery-public-data")
dataset = client.get_dataset(dataset_ref)

# 打印看数据集里所有的表格
for table in client.list_tables(dataset):
    print(table.table_id)

blocks
transactions

看到这里就不对了，这个数据集应该是有 4 张表，这里却只有两个，继续看 transactions 表内容吧

table_ref = dataset_ref.table("transactions")
table = client.get_table(table_ref)
for col in table.schema:
    print(col)
    print()

SchemaField(‘timestamp’, ‘INTEGER’, ‘NULLABLE’, None, ())

SchemaField(‘transaction_id’, ‘STRING’, ‘NULLABLE’, None, ())

SchemaField(‘inputs’, ‘RECORD’, ‘REPEATED’, None, (SchemaField(‘input_script_bytes’, ‘BYTES’, ‘NULLABLE’, None, ()), SchemaField(‘input_script_string’, ‘STRING’, ‘NULLABLE’, None, ()), SchemaField(‘input_script_string_error’, ‘STRING’, ‘NULLABLE’, None, ()), SchemaField(‘input_sequence_number’, ‘INTEGER’, ‘NULLABLE’, None, ()), SchemaField(‘input_pubkey_base58’, ‘STRING’, ‘NULLABLE’, None, ()), SchemaField(‘input_pubkey_base58_error’, ‘STRING’, ‘NULLABLE’, None, ())))

SchemaField(‘outputs’, ‘RECORD’, ‘REPEATED’, None, (SchemaField(‘output_satoshis’, ‘INTEGER’, ‘NULLABLE’, None, ()), SchemaField(‘output_script_bytes’, ‘BYTES’, ‘NULLABLE’, None, ()), SchemaField(‘output_script_string’, ‘STRING’, ‘NULLABLE’, None, ()), SchemaField(‘output_script_string_error’, ‘STRING’, ‘NULLABLE’, None, ()), SchemaField(‘output_pubkey_base58’, ‘STRING’, ‘NULLABLE’, None, ()), SchemaField(‘output_pubkey_base58_error’, ‘STRING’, ‘NULLABLE’, None, ())))

SchemaField(‘block_id’, ‘STRING’, ‘NULLABLE’, None, ())

SchemaField(‘previous_block’, ‘STRING’, ‘NULLABLE’, None, ())

SchemaField(‘merkle_root’, ‘STRING’, ‘NULLABLE’, None, ())

SchemaField(‘nonce’, ‘INTEGER’, ‘NULLABLE’, None, ())

SchemaField(‘version’, ‘INTEGER’, ‘NULLABLE’, None, ())

SchemaField(‘work_terahash’, ‘INTEGER’, ‘NULLABLE’, None, ())

SchemaField(‘work_error’, ‘STRING’, ‘NULLABLE’, None, ())

还真的没有 output_count

获取一条数据

r1 = client('SELECT * FROM `bigquery-public-data.bitcoin_blockchain.transactions` WHERE transaction_id = "4a5e1e4baab89f3a32518a88c31bc87f618f76673e2cc77ab2127b7afdeda33b"')
df = r1.to_dataframe()
df.shape

(1, 11)

返回的数据只有 11 列，这和上一篇文章的结果不同。

原来 bigquery 有两个不同的比特币数据集

上篇文章中用到的是：
bigquery-public-data.crypto_bitcoin.transactions
这个是数据集主页提到的

上面出现错误的代码中用的是
bigquery-public-data.bitcoin_blockchain.transactions
是从数据集主页推荐的官方 kernal 中使用的。

这两个数据集无论是表的数量，还是表的结构都有不同。

所以还是使用第一个比较好

最终代码与可视化

from google.cloud import bigquery

client = bigquery.Client()
query = "SELECT output_count,  COUNT(1) AS tx_count FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx GROUP BY output_count ORDER BY output_count"
r = client.query(query)
df = r.to_dataframe()
r.total_bytes_processed / (1024 ** 3)

3.7562014535069466

df

s = df['tx_count'].sum()
s

504172778

该数据集有 5 亿交易

df['tx_count'][0] / s * 100, df['tx_count'][1] / s * 100 , df['tx_count'][2] / s * 100,

(11.770812822424935, 78.09005467566121, 5.974420340480977)

output_count 为 1、2、3 的交易分别占比 11.77%， 78.09%， 5.97%

数据长尾很明显， 90 以上的交易集中在 1 - 3。所以只画前 10 行的条形图看一下

df[:10].set_index('output_count').plot.bar()