Often, we need to monitor that crucial graph for every day stats so that business is not loosing money. Often, business doesn't have patience to login to reporting tool or follow up on emails on the fix. So why not get that graph directly into a slack channel for everyone to see and take action?
So, Let’s get that data from Big Query first,
pip install google-cloud-bigqueryfrom google.cloud import bigquery#setting up authourization
client = bigquery.Client.from_service_account_json("your_keys.json")#query you are interested in my_query = """
select * from my_table
where my_condition = 'my_condition';
"""#making the API request and get the data into dataframedf = client.query(my_query).to_dataframe()if df.empty:
print('no results, please check query or if table is populated')
else:
print("Fetch data success,", len(df))
Now, let’s prepare the graph from the data
ax = df.plot(kind='bar', figsize=(20,16), color="green", fontsize=13)
ax.set_alpha(0.8)
ax.set_title("Stats_Today", fontsize=22)
ax.set_ylabel("Record Count", fontsize=15)
ax.set_xlabel("Date", fontsize=15)
fig = ax.get_figure()
#saving figure at folder, saving at airflow folder
fig.savefig(airflow_home+'Stats_Today.png', bbox_inches = "tight")At the last, let’s connect to slack channel and upload this image.
#reading variables from the file, these are coming from airflowkpi_variables = Variable.get("kpi_variables_secret", deserialize_json=True)
url = kpi_variables["url"]def upload_graph():
#uploading to slack
querystring = {"token":access_token}
payload = {"channels":"my_channel"} #your channel
file_upload = {"file": (airflow_home+"Stats_Today.png", open(airflow_home+"Stats_Today.png", 'rb'), 'image/png')}
headers = {"Content-Type": "multipart/form-data",}
response = requests.post(url, data=payload, params=querystring, files=file_upload)
after scheduling it for everyday using airflow the results looks like-
