Search
⌃K
Links
🔵

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.

Prerequisites

  • 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

Connecting to the Snowflake API

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:
import snowflake.connector
​
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
)

Running a Query

With the connection established, you can run SQL queries on your Snowflake data. Let's run a simple SELECT query:
query = """
SELECT
FLOOR((block_number - 1) / 1000) * 1000 AS block_group,
COUNT(*) AS tx_count
FROM base_managed.decoded.decoded_testnet_base_transactions
WHERE to_address = '' AND input LIKE '%a165627a7a72305820%'
GROUP BY
FLOOR((block_number - 1) / 1000) * 1000
ORDER BY block_group;
"""
​
cur = conn.cursor()
cur.execute(query)

Retrieving Results in a Pandas Dataframe

You can fetch the results of the query into a Pandas DataFrame with fetch_pandas_all():
results = cur.fetch_pandas_all()
df = results

Plotting with Matplotlib:

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')
plt.show()

Conclusion

This will display an interactive line plot of the metric_value column 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!