Bad Request 400 in query for bitcoin bigquery using python

Question

I want to access the bitcoin bigquery dataset and convert it to dataframe in format.

timestamp, transaction_id, input_addr

when i write the query for it referring to the docs

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()


query1 = '''SELECT
  transaction_id,
  timestamp,
  inputs.input_pubkey_base58
FROM bigquery-public-data.bitcoin_blockchain.transactions
WHERE timestamp < 1588879614 AND timestamp > 1572480000
         '''

dataframe = (
     client.query(query1)
    .result()
    .to_dataframe(bqstorage_client=bqstorageclient)
)
print(dataframe.head())

i get below error. how to resolve it.

---------------------------------------------------------------------------
BadRequest                                Traceback (most recent call last)
<ipython-input-20-2ca06d198e09> in <module>()
      1 dataframe = (
----> 2      client.query(query1)
      3     .result()
      4     .to_dataframe(bqstorage_client=bqstorageclient)
      5 )

BadRequest: 400 Cannot access field input_pubkey_base58 on a value with type ARRAY<STRUCT<input_script_bytes BYTES, input_script_string STRING, input_script_string_error STRING, ...>> at [4:10]

Mikhail Berlyant · Accepted Answer · 2020-05-07 20:06:15Z

1

Cannot access field input_pubkey_base58 on a value with type ARRAY> at [4:10]

You should correct your select statement by unnesting first array(repeated field) as below

SELECT
  transaction_id,
  TIMESTAMP,
  input.input_pubkey_base58
FROM bigquery-public-data.bitcoin_blockchain.transactions,
UNNEST(inputs) input
WHERE TIMESTAMP < 1588879614 AND TIMESTAMP > 1572480000

edited May 7, 2020 at 20:06

answered May 7, 2020 at 19:49

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

pranav nerurkar Over a year ago

ContextualVersionConflict: (google-api-core 1.6.0 (/opt/conda/lib/python3.6/site-packages), Requirement.parse('google-api-core[grpc]<2.0.0dev,>=1.14.0'), {'google-cloud-bigquery-storage'})

pranav nerurkar Over a year ago

Empty DataFrame Columns: [transaction_id, TIMESTAMP, input_pubkey_base58] Index: []

pranav nerurkar Over a year ago

i got an empty dataframe. with column heading but no rows

Mikhail Berlyant Over a year ago

have you tried to run just this exact query in BQ UI? please do

pranav nerurkar Over a year ago

Hi i tried it Query complete (0.5 sec elapsed, 48.1 GB processed) .This query returned no results.

|

Collectives™ on Stack Overflow

Bad Request 400 in query for bitcoin bigquery using python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related