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