Requirement already satisfied: bigquery in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (0.0.41)
Requirement already satisfied: dbstream>=0.1.19 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (0.1.25)
Requirement already satisfied: google-cloud-bigquery>=2.4.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (3.21.0)
Requirement already satisfied: googleauthentication>=0.0.12 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (0.0.17)
Requirement already satisfied: google-cloud-bigquery-storage>=2.1.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (2.24.0)
Requirement already satisfied: pandas==1.3.4 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (1.3.4)
Requirement already satisfied: pyarrow>=2.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from bigquery) (16.0.0)
Requirement already satisfied: python-dateutil>=2.7.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas==1.3.4->bigquery) (2.9.0.post0)
Requirement already satisfied: pytz>=2017.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas==1.3.4->bigquery) (2024.1)
Requirement already satisfied: numpy>=1.21.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas==1.3.4->bigquery) (1.26.4)
Requirement already satisfied: dacktool>=0.0.7 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from dbstream>=0.1.19->bigquery) (0.0.7)
Requirement already satisfied: requests>=2.22.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from dbstream>=0.1.19->bigquery) (2.31.0)
Requirement already satisfied: google-api-core>=2.3.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from dbstream>=0.1.19->bigquery) (2.18.0)
Requirement already satisfied: google-auth<3.0.0dev,>=2.14.1 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery>=2.4.0->bigquery) (2.29.0)
Requirement already satisfied: google-cloud-core<3.0.0dev,>=1.6.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery>=2.4.0->bigquery) (2.4.1)
Requirement already satisfied: google-resumable-media<3.0dev,>=0.6.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery>=2.4.0->bigquery) (2.7.0)
Requirement already satisfied: packaging>=20.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery>=2.4.0->bigquery) (24.0)
Requirement already satisfied: proto-plus<2.0.0dev,>=1.22.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery-storage>=2.1.0->bigquery) (1.23.0)
Requirement already satisfied: protobuf!=3.20.0,!=3.20.1,!=4.21.0,!=4.21.1,!=4.21.2,!=4.21.3,!=4.21.4,!=4.21.5,<5.0.0dev,>=3.19.5 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-bigquery-storage>=2.1.0->bigquery) (4.25.3)
Requirement already satisfied: google-api-python-client==1.7.11 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (1.7.11)
Requirement already satisfied: google-auth-httplib2==0.0.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (0.0.3)
Requirement already satisfied: google-auth-oauthlib>=0.4.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (1.2.0)
Requirement already satisfied: cryptography>=2.7 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (42.0.5)
Requirement already satisfied: google-cloud-secret-manager==2.7.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from googleauthentication>=0.0.12->bigquery) (2.7.2)
Requirement already satisfied: httplib2<1dev,>=0.9.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-python-client==1.7.11->googleauthentication>=0.0.12->bigquery) (0.22.0)
Requirement already satisfied: six<2dev,>=1.6.1 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-python-client==1.7.11->googleauthentication>=0.0.12->bigquery) (1.16.0)
Requirement already satisfied: uritemplate<4dev,>=3.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-python-client==1.7.11->googleauthentication>=0.0.12->bigquery) (3.0.1)
Requirement already satisfied: grpc-google-iam-v1<0.13dev,>=0.12.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-secret-manager==2.7.2->googleauthentication>=0.0.12->bigquery) (0.12.7)
Requirement already satisfied: libcst>=0.2.5 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-cloud-secret-manager==2.7.2->googleauthentication>=0.0.12->bigquery) (1.3.1)
Requirement already satisfied: cffi>=1.12 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from cryptography>=2.7->googleauthentication>=0.0.12->bigquery) (1.16.0)
Requirement already satisfied: googleapis-common-protos<2.0.dev0,>=1.56.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-core>=2.3.2->dbstream>=0.1.19->bigquery) (1.63.0)
Requirement already satisfied: grpcio<2.0dev,>=1.33.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-cloud-bigquery>=2.4.0->bigquery) (1.62.2)
Requirement already satisfied: grpcio-status<2.0.dev0,>=1.33.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-api-core[grpc]!=2.0.*,!=2.1.*,!=2.10.*,!=2.2.*,!=2.3.*,!=2.4.*,!=2.5.*,!=2.6.*,!=2.7.*,!=2.8.*,!=2.9.*,<3.0.0dev,>=1.34.1->google-cloud-bigquery>=2.4.0->bigquery) (1.62.2)
Requirement already satisfied: cachetools<6.0,>=2.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-auth<3.0.0dev,>=2.14.1->google-cloud-bigquery>=2.4.0->bigquery) (5.3.3)
Requirement already satisfied: pyasn1-modules>=0.2.1 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-auth<3.0.0dev,>=2.14.1->google-cloud-bigquery>=2.4.0->bigquery) (0.4.0)
Requirement already satisfied: rsa<5,>=3.1.4 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-auth<3.0.0dev,>=2.14.1->google-cloud-bigquery>=2.4.0->bigquery) (4.9)
Requirement already satisfied: requests-oauthlib>=0.7.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-auth-oauthlib>=0.4.0->googleauthentication>=0.0.12->bigquery) (2.0.0)
Requirement already satisfied: google-crc32c<2.0dev,>=1.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from google-resumable-media<3.0dev,>=0.6.0->google-cloud-bigquery>=2.4.0->bigquery) (1.5.0)
Requirement already satisfied: charset-normalizer<4,>=2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests>=2.22.0->dbstream>=0.1.19->bigquery) (3.3.2)
Requirement already satisfied: idna<4,>=2.5 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests>=2.22.0->dbstream>=0.1.19->bigquery) (3.7)
Requirement already satisfied: urllib3<3,>=1.21.1 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests>=2.22.0->dbstream>=0.1.19->bigquery) (2.2.1)
Requirement already satisfied: certifi>=2017.4.17 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests>=2.22.0->dbstream>=0.1.19->bigquery) (2024.2.2)
Requirement already satisfied: pycparser in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from cffi>=1.12->cryptography>=2.7->googleauthentication>=0.0.12->bigquery) (2.22)
Requirement already satisfied: pyparsing!=3.0.0,!=3.0.1,!=3.0.2,!=3.0.3,<4,>=2.4.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from httplib2<1dev,>=0.9.2->google-api-python-client==1.7.11->googleauthentication>=0.0.12->bigquery) (3.1.2)
Requirement already satisfied: pyyaml>=5.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from libcst>=0.2.5->google-cloud-secret-manager==2.7.2->googleauthentication>=0.0.12->bigquery) (6.0.1)
Requirement already satisfied: pyasn1<0.7.0,>=0.4.6 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pyasn1-modules>=0.2.1->google-auth<3.0.0dev,>=2.14.1->google-cloud-bigquery>=2.4.0->bigquery) (0.6.0)
Requirement already satisfied: oauthlib>=3.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.0->googleauthentication>=0.0.12->bigquery) (3.2.2)
Requirement already satisfied: db-dtypes in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (1.2.0)
Requirement already satisfied: packaging>=17.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from db-dtypes) (24.0)
Requirement already satisfied: pandas>=0.24.2 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from db-dtypes) (1.3.4)
Requirement already satisfied: pyarrow>=3.0.0 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from db-dtypes) (16.0.0)
Requirement already satisfied: numpy>=1.16.6 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from db-dtypes) (1.26.4)
Requirement already satisfied: python-dateutil>=2.7.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas>=0.24.2->db-dtypes) (2.9.0.post0)
Requirement already satisfied: pytz>=2017.3 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from pandas>=0.24.2->db-dtypes) (2024.1)
Requirement already satisfied: six>=1.5 in /Users/masumi/.local/share/mise/installs/python/3.12/lib/python3.12/site-packages (from python-dateutil>=2.7.3->pandas>=0.24.2->db-dtypes) (1.16.0)
#Importing necessary modulesfrom google.cloud import bigqueryfrom google.oauth2 import service_accountimport pandas as pdimport numpy as npimport matplotlib.pyplot as pltimport os%matplotlib inline# Create a "Client" objectkey_path = os.environ['BIGQUERY_SERVICE_ACCOUNT_AUTH_FILE_PATH']credentials = service_account.Credentials.from_service_account_file(key_path, scopes=["https://www.googleapis.com/auth/cloud-platform"])client = bigquery.Client(credentials=credentials, project=credentials.project_id)
# Construct a reference to the "Ethereum Blockchain" dataseteth_ref = client.dataset("crypto_ethereum", project="bigquery-public-data")# API request - fetch the datasetdataset = client.get_dataset(eth_ref)# List all the tables in the "crypto_ethereum" datasettables =list(client.list_tables(dataset))# Print names of all tables in the datasetfor table in tables:print(table.table_id)
Use a function to show amount of data to be scanned for a query not to exceed the limit for a Kaggle user, before actually running the query
def show_amount_of_data_scanned(query):# dry_run lets us see how much data the query uses without running it dry_run_config = bigquery.QueryJobConfig(dry_run=True) query_job = client.query(query, job_config=dry_run_config)print('Data processed: {} GB'.format(round(query_job.total_bytes_processed /10**9, 3)))
The ethereum blockchain is fairly popular with users, the query below will show the top 10 wallet sender addresses with the most number of transactions
transaction_table_ref = eth_ref.table("transactions")transaction_table = client.get_table(transaction_table_ref)most_transaction_query =""" SELECT from_address as wallet_address, COUNT(*) AS transactions FROM `bigquery-public-data.crypto_ethereum.transactions` WHERE EXTRACT(YEAR from block_timestamp) = 2024 GROUP BY wallet_address ORDER BY transactions DESC LIMIT 10 """
The ethereum blockchain is also known for its high gas prices, the query below will show the top 10 highest gas fees of the dataset
highest_gas_query =""" SELECT block_hash, block_timestamp, receipt_cumulative_gas_used FROM `bigquery-public-data.crypto_ethereum.transactions` WHERE EXTRACT(YEAR from block_timestamp) = 2024 ORDER BY receipt_cumulative_gas_used DESC LIMIT 10 """query_job = client.query(highest_gas_query)highest_gas = query_job.to_dataframe()highest_gas
block_hash
block_timestamp
receipt_cumulative_gas_used
0
0xd63e815a03a0b9e7be87d81bad646137473ef7e94c5a...
2024-05-11 01:39:11+00:00
30029206
1
0xad5a9de8a918b61437c2bfc6a65cec33a010afb08482...
2024-05-14 02:48:59+00:00
30029204
2
0x3b3eb42ce1ae1a38e238853cee998ff56dddaefbd102...
2024-04-20 12:46:11+00:00
30029204
3
0xecec1ff6186785bb0f74b4f3a5537a659d440d778bcc...
2024-04-22 03:34:23+00:00
30029192
4
0x3d6fdf73cb6976545182538542ae1bcc5e72990463d0...
2024-03-15 13:10:11+00:00
30029024
5
0xdacc31fe2b70a85722b5aefa90fcc50e44bb23e9dc5e...
2024-04-29 02:20:23+00:00
30028841
6
0x7244d9b8c76835da19c8849e8623f746e9ba0b91dc47...
2024-04-12 02:26:35+00:00
30028833
7
0x6adae77775bb702a110a08a46aaef2811beb0a75e071...
2024-05-18 06:18:23+00:00
30028766
8
0x2f31698831688a5c5cc903a661c2396a6a7dbf4dea89...
2024-05-08 09:27:47+00:00
30028630
9
0x43933205a01a9a6756f296e2b2db7146150046dde6ff...
2024-05-11 12:03:47+00:00
30028599
Let’s see the dates and the number of transactions in year 2024 using an analytic function
transfer_date_query =""" WITH transfer_a_day AS ( SELECT DATE(block_timestamp) AS token_transfer_date, COUNT(*) as num_transfer FROM `bigquery-public-data.crypto_ethereum.token_transfers` WHERE EXTRACT(YEAR from block_timestamp) = 2024 GROUP BY token_transfer_date ) SELECT token_transfer_date, SUM(num_transfer) OVER ( ORDER BY num_transfer DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS sum_transactions FROM transfer_a_day """