Analyzing Base Testnet Activity with the Coherent SQL API and Python
In this tutorial, we will walk through how to connect to Coherent on Snowflake, a cloud data warehouse, run queries, and analyze the data using Python.
- Snowflake account and database - Sign up for a free Snowflake trial account
- Python 3.7+ installed
- Snowflake Connector for Python installed. Run the following command:
pip install snowflake-connector-python
- Pandas installed for data analysis. Run:
pip install pandas
- Matplotlib installed for plotting. Run:
pip install matplotlib
To connect to Snowflake, you need your account name, username, password, database name, schema name, and warehouse name. Assign these to variables and connect to the API:
account = '<your_account_name>'
user = '<your_username>'
password = '<your_password>'
database = '<your_database_name>'
schema = '<your_schema_name>'
warehouse = '<your_warehouse_name>'
conn = snowflake.connector.connect(
account = account,
user = user,
password = password,
database = database,
schema = schema,
warehouse = warehouse
With the connection established, you can run SQL queries on your Snowflake data. Let's run a simple SELECT query:
query = """
FLOOR((block_number - 1) / 1000) * 1000 AS block_group,
COUNT(*) AS tx_count
WHERE to_address = '' AND input LIKE '%a165627a7a72305820%'
FLOOR((block_number - 1) / 1000) * 1000
ORDER BY block_group;
cur = conn.cursor()
You can fetch the results of the query into a Pandas DataFrame with
results = cur.fetch_pandas_all()
df = results
With the data in a DataFrame, you can easily plot it using Matplotlib. Let's plot a line chart:
import matplotlib.pyplot as plt
df.plot(x='block_group', y='tx_count', kind='line')
This will display an interactive line plot of the
metric_valuecolumn over time. Matplotlib has many more chart types and customization options to visualize your Snowflake data.
You now have everything you need to connect to your Snowflake data warehouse, query data with SQL, and analyze and visualize the results with Pandas and Matplotlib!