I want to generate a graph, first i will be querying in the snowflake to fetch the data for the credits/resources consumed by warehouse over a year, i want to use this data to generate a line graph to see the trend of how a warehouse has consumed the costs/resources over past one year, for example if i have 5 warehouses, i want to see a line for each of them showing the trend for past one year..
i am new to this in graph thing in python and need help with this.
Regards Vivek
You can perform this using matplotlib, pandas and the snowflake-connector-python Python 3.x modules installed.
You'll need to build a query that aggregates your warehouse metering history in the way you need, using the WAREHOUSE_METERING_HISTORY account usage view or equivalent. The example provided below uses a query that aggregates each month.
With the query results in a Pandas DataFrame, you can then use pivot to format the data such that each warehouse series can appear as a line of its own.
import matplotlib.pyplot as plt
from snowflake import connector
# Establish your connection here
con = connector.connect(…)
q = """
select
warehouse_name as warehouse,
date_trunc('month', end_time)::date in_month,
sum(credits_used) credits
from snowflake.account_usage.warehouse_metering_history
where warehouse_id != 0
group by warehouse, in_month
order by warehouse, in_month;
"""
df = con.cursor().execute(q).fetch_pandas_all()
# Explicitly specify datatypes for all columns so they behave well
df['IN_MONTH'] = pd.to_datetime(df['IN_MONTH'])
tdf = df.astype({'WAREHOUSE': 'string', 'CREDITS': 'float'})
pdf = tdf.pivot(index='IN_MONTH', columns='WAREHOUSE', values='CREDITS')
pdf.plot()
plt.show()
This yields:
P.s. You can alternatively try the native Snowsight features in Snowflake to plot interactive charts right from its SQL editor interface:
