google-bigquery - Bigquery比特币数据集SQL查询获取时间戳后的交易

Question

我想在时间戳 1572491526 之后创建所有比特币交易的 CSV。所以我尝试了下面的代码。我希望 CSV 有四列 -

transaction_id, timestamp, input, output
1                  1        aaa     bbb
1                  1        abc     cde
2                  2        pqr     xyz

到目前为止我试过这个

from google.cloud import bigquery
client = bigquery.Client()

QUERY =   """
SELECT timestamp, transaction_id, inputs, outputs              
FROM bigquery-public-data.bitcoin_blockchain.transactions
WHERE timestamp > 1572491526
LIMIT 1

"""



# note that max_gb_scanned is set to 24, rather than 1
queryjob = client.query(QUERY)  # API request
rows = queryjob.result() 

row = list(rows)
import pandas as pd
headlines = pd.DataFrame(data=[list(x.values()) for x in row], columns=list(row[0].keys()))
headlines

但我得到的输出不正确。如何解决这个问题

timestamp   transaction_id  inputs  outputs
0   1237254030000   8425ac5096ff2b55e0feefa7c78ba609a245e6f185ecde...   [{'input_script_bytes': b'\x04\xff\xff\x00\x1d...   [{'output_satoshis': 5000000000, 'output_scrip...

标签： google-bigquery

Answer 1

你得到的输出是正确的，我在 Bigquery UI 中测试了你的查询，似乎和你得到的一样。请考虑字段输入和输出是数组，可能存在混淆。

另外，我测试了你的代码，我得到了相同的输出

from google.cloud import bigquery
client = bigquery.Client()
QUERY =   """
SELECT timestamp, transaction_id, inputs, outputs              
FROM bigquery-public-data.bitcoin_blockchain.transactions
WHERE timestamp > 1572491526
LIMIT 1
"""
# note that max_gb_scanned is set to 24, rather than 1
queryjob = client.query(QUERY)  # API request
rows = queryjob.result() 
row = list(rows)
import pandas as pd
headlines = pd.DataFrame(data=[list(x.values()) for x in row], columns=list(row[0].keys()))
headlines.to_csv('output.csv', index=False, header=True)