github.com/pdaian/flashboys2@v0.0.0-20190718175736-b101c35361f0/get_bq_logs.py (about)

     1  import csv, os
     2  from google.cloud import bigquery
     3  from exchanges import dex_list
     4  
     5  FIELDS_TO_GRAB = 'block_number,transaction_hash,to_address,from_address,address,num_logs,gas,gas_price,receipt_gas_used,input,transaction_index'
     6  
     7  os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "etharbskey.json"
     8  client = bigquery.Client()
     9  
    10  txs = set(['0xe1844a185ced1361f7e695b589f8dc1f9c917e7e85c08825bd192d079e3c4cd6'])
    11  
    12  
    13  query = """SELECT log_index,transaction_hash,logs.transaction_index,address,data,topics,logs.block_timestamp,logs.block_number FROM 
    14    `bigquery-public-data.ethereum_blockchain.logs` AS logs
    15    JOIN `bigquery-public-data.ethereum_blockchain.transactions` AS transactions ON logs.transaction_hash = transactions.hash
    16  WHERE NOT logs.address = transactions.to_address
    17    AND logs.address in UNNEST(@dex_list)
    18    AND NOT transactions.to_address IN UNNEST(@dex_list) ORDER BY block_number ASC, transaction_index ASC"""
    19  
    20  aqp = bigquery.ArrayQueryParameter('dex_list', 'STRING', dex_list)
    21  query_params = [aqp]
    22  job_config = bigquery.QueryJobConfig()
    23  job_config.query_parameters = query_params
    24  query_job = client.query(
    25      query,
    26      # Location must match that of the dataset(s) referenced in the query.
    27      location='US',
    28      job_config=job_config)  # API request - starts the query
    29  
    30  
    31  with open('data/all_logs_bigquery.csv', 'w') as csvfile:
    32      spamwriter = csv.writer(csvfile, delimiter=',',
    33                              quotechar='"', quoting=csv.QUOTE_MINIMAL)
    34  
    35      spamwriter.writerow("log_index,transaction_hash,transaction_index,address,data,topics,block_timestamp,block_number".split(","))
    36      for item in query_job:
    37          spamwriter.writerow(item)
    38  
    39  assert query_job.state == 'DONE'
    40  print("[database fetcher] Wrote all logs")
    41