ืืื ืืฉ ืื ืืฉืืืืช ืฉืืืืจืืช ืขื ืขืฆืื ืืื ืืืจ ืืื, ืฉืืืข ืืืจ ืฉืืืข? ืืืฉื, ืืชืืืช ืืืืืช. ืืชื ืืืงืฉ ื ืชืื ืื, ืื ืชืืื ืืืชื, ืืืืืื ืื ืืืชื (ืืืฆืจืื ืืจืคืื, ืชืจืฉืืืื), ืืื ืฉืืืืื ืืืชื ืืืืก ืฉืื. ืืื ืื ืื ืื ืื ืืื ืืืืืืื?
ืืืืจืื ืื ื ืืฆืืจ ืืื ืืืืืจื ืฉืืขืืืจ ืืืคืื ืืช ืืืืืื ืืืืืืืื. ืืืืืจ ืืื ืืื ืื ืืื ืฉืืชืืื ืืช ืืืื ืชืืื ืืืจืืืช ื-50 ืฉืืจืืช ืงืื ืืืื! ืื ืืชื ืืืฆืจ ืืื ืขืืืจ ืืืืจื ืืคืขื ืืจืืฉืื ื, ืขืืื ืืงืจืื ืื ืืช ืื
Skillbox ืืืืืฆื: ืงืืจืก ืืขืฉื
ืืคืชื Python ืืืคืก .ืื ื ืืืืืจืื: ืืื ืงืืจืื Habr - ืื ืื ืฉื 10 ืจืืื ืืขืช ืืจืฉืื ืืื ืงืืจืก Skillbox ืืืืฆืขืืช ืงืื ืืืืื ืฉื Habr.
ืืืื ื ืชืืื
ืืชืงื ืช ืกืคืจืืืช
ืื ืื ื ื ืฉืชืืฉ
pip3 ืืืชืงืื ืืช google-cloud-bigquery matplotlib numpy pandas python-telegram-bot
ืืืืืจ Google BigQuery API
ืื ืื ืื ื ืจืืฆืื ืืืฉืชืืฉ ืืฉืืจืืช, ืื ืื ื ืฆืจืืืื ืืืืจ ืืช Google BigQuery API. ืืื ืืขืฉืืช ืืืช ืื ืื ื ืืืืืื ื
ืืืื ืืืงืจื, ืืืจ ENABLE APIS AND SERVICES ืืืคืฉ ืืช BigQuery API.
ืืืจ ืืคืขื ืืื ืืืืจ ืืช ื-API.
ืฆืืจ ืืคืชื ืืฉืืื
ืืื ื ืื ืฉืื
ืืืืจ ืืื - ืืฉืืื ืฉืืจืืช ืืืฉ, ืืืื ืืช ืืฉื ืืฉืื ืฉื ืืฉืืื ืฉืืจืืช.
ืืืจืฉืืื ืื ืคืชืืช ืชืคืงืื, ืืืจ ืคืจืืืงื > ืืขืืื ืืืืืจ ืืื ืฆืืจ.
ืืงืืืฅ ืฉืืืจื ืืืืืืืืช ื ืงืจื creds.json.
ืืืืจ GOOGLE_APPLICATION_CREDENTIALS, ืฆืืื ืื ืชืื ืื creds.json ืืืจืืื ื.
ืืืฆืื GOOGLE_APPLICATION_CREDENTIALS='[PATH_TO_CREDS.JSON]'
ืื ืืื ืืื ืืฉืืจื, ืืืืข ืืืื ืืืชืืื ืืืชืืืช ืืชืืื ืืช.
ืฆืืจ ืืคืืืงืฆืื
ืขืืืจ ืืืืจืื ื ืฉืชืืฉ ืื ืชืื ืื ื-bigquery-public-data.stackoverflow, ืขืืืจ ืืืื ืฉืื ื ื ืืืจ ืืช ืืกืคืจ ืืคืจืกืืืื ืืืืืืื.
ืืื ืื ืคืฉืื.
ืฉืืืืชื ืืืืื -> ืืืืืื ืืช ืื ืชืื ืื -> ืฉืืืจ ืืช ืืืืืื -> ืฉืื ืืช ืืชืืื ื
ืืืื ื ืืฆืืจ ืคืื ืงืฆืื ืืืช ืืื ืืืืืืจ ืื ืฉืจืฉืืจ.
ืฉืืืืชื ื-BigQuery
ืจืืฉืืช ืื ื ืืืืืืื ืืช ืืกืคืจืืื.
ื-google.cloud ืืืืื โโbigquery
ืื ื ืืืฆืจืื ืคืื ืงืฆืื ืืฉื query_to_bigquery, ืืืฉืจ ืืคืจืืืจ ืืื query.
def query_to_bigquery(query):
client = bigquery.Client()
query_job = client.query(query)
result = query_job.result()
dataframe = result.to_dataframe()
return dataframe
ืคืื ืงืฆืื ืื ืชืืืืจ ืืช ืืืงืฉื ืืืกืืจืช ื ืชืื ืื.
ืืืืืืช ื ืชืื ืื
ืืื ืืคืชืืจ ืืขืื ืื, ืืืจ matplotlib.
ืืืื matplotlib.pyplot ื- plt
ืื ืื ื ืฆืจืืืื ืืืืฉื ืคืจืืืจืื, ืืืฉืจ x ืืื ื ืชืื ื ืฆืืจ ื-x, x_label ืืื ืืืืชืจืช ืฉื ืืฆืืจ, y ืืื ื ืชืื ื ืฆืืจ ื-y, y_label ืืื ืืืืชืจืช ืฉื ืืฆืืจ, ืืืืชืจืช ืืื ืืืืชืจืช ืฉื ืืืืืื ืืืื.
def visualize_bar_chart(x, x_label, y, y_label, title):
plt.title(title)
plt.xlabel(x_label)
plt.ylabel(y_label)
index = np.arange(len(x))
plt.xticks(index, x, fontsize=5, rotation=30)
plt.bar(index, y)
return plt
ืฉืืืจ ืืช ืืชืืื ื
ืืขืช ื ืฉืชืืฉ ืืฉืชื ืคืื ืงืฆืืืช ืืื ืืืฆืืจ ืืืืื ืืืฉืืืจ ืืืชื.
ืื ื ื ืฉืื ืืช ืืกืคืจ ืืคืืกืืื ืืืชืคืจืกืืื ืืื ืืื. ืจืืฉืืช ื ืืชืื ืืงืฉื.
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""
ืืฉืืืืชื ืขืืืจืช ืืืืกืืฃ ื ืชืื ืื ืืืฉื ืฉืืืขืืื ืืื ืื-2 ืืืฆืืืจ 2018.
ืื ื ืืฉืชืืฉืื ืืชืืจืื ืื ืืืืืื ืฉ-2018-12-02 ืืื ืื ืชืื ืื ืืขืืื ืืื ืืืืชืจ ืฉืชืืขืื ื-bigquery-public-data.stackoverflow.post_history, ืืืงืจืื ืืืจืื ืืชื ืืืื ืืืฉืชืืฉ ื-CURRENT_DATE() ืืื ืืงืื ืืช ืื ืชืื ืื ืืืืฉืื ืืืืชืจ.
ืืชืงืฉืจ ืืคืื ืงืฆืื query_to_bigquery ืืื ืืงืื ืืช ืื ืชืื ืื.
dataframe = query_to_bigquery(query)
ืืืืจ ืืื ืื ื ืืฉืชืืฉืื ืืขืืืืช ื ืชืื ื ืืชืืจืื ืขืืืจ ืฆืืจ ื-x, ืืืขืืืืช total_posts ืขืืืจ ืฆืืจ ื-y.
x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()
ืื ื ืืืืืื ืื ืืืชื ืืืืฆืขืืช ืืคืื ืงืฆืื visualize_bar_chart ืืฉืืืจืื ืืืชื ืืชืืื ื.
plt = visualize_bar_chart(x=x, x_label='Date', y=y, y_label='Total Posts', title='Daily Posts')
plt.savefig('viz.png')โ
ืื ื ืขืืืคืื ืืช ืืงืื ืืื ืืคืื ืงืฆืื ืฉื ืงืจืืช get_and_save_image.
def get_and_save_image():
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""
dataframe = query_to_bigquery(query)
x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()
plt = visualize_bar_chart(x=x, x_label='Date', y=y, y_label='Total Posts', title='Daily Posts')
plt.savefig('viz.png')
ืฉืื ืชืืื ื
ืขื ืื ืช ืืฉืืื ืืื ืื ืืขื, ืขืืื ืืืขืช ืืช ืืคืจืืืจ chat_id.
ืืฉืชืืฉ
ืขืืฉืื ืืืื ื ืืฆืืจ ืืช ืืคืื ืงืฆืื send_image. ืื ืืฉืชืืฉ ืืคืื ืงืฆืื get_and_save_image ืืื ืืืืืจ ืืืฉืืืจ ืืช ืืชืืื ื. ืืื ืื ืื ื ืฉืืืืื ืืื ืืืืฉ ืืงืฉืจ ืื ืืื.
def send_image(bot, update):
get_and_save_image()
chat_id = 'CHAT_ID_RECEIVER'
bot.send_photo(chat_id=chat_id, photo=open('viz.png','rb'))
ืชืืื ื ืจืืฉืืช
ืืืกืืฃ, ืื ื ืืืฆืจืื ืคืื ืงืฆืื ื ืืกืคืช, ืจืืฉืืช, ืืืคืขืืช ืืืคืืืงืฆืื. ืื ืชืฉืื ืืฉื ืืช ืืช YOUR_TOKEN ืขืืืจ ืืืื.
ืืืืจ: ืชืืื ืืช ืื ืชืฉืื ืืช ืืชืืื ื ืืืืคื ืืืืืืื ืืืื ืฉืชืฆืืื. ืืืฉื, ื ืฉืื ืืื ืื ืืื ืืชืฉืข ืืืืงืจ.
def main():
updater = Updater('YOUR_TOKEN')
updater.job_queue.run_daily(send_image, time=datetime.datetime.strptime('9:00AM', '%I:%M%p').time(), days=(0,1,2,3,4,5,6))
updater.start_polling()
updater.idle()
if __name__ == '__main__':
main()
ืืชืืฆืื ืืื, ืืืืฉืื ืฉืื ื ืืืจืื ืื:
from google.cloud import bigquery
from telegram.ext import Updater
import matplotlib.pyplot as plt
import numpy as np
import datetime
def query_to_bigquery(query):
client = bigquery.Client()
query_job = client.query(query)
result = query_job.result()
dataframe = result.to_dataframe()
return dataframe
def visualize_bar_chart(x, x_label, y, y_label, title):
plt.title(title)
plt.xlabel(x_label)
plt.ylabel(y_label)
index = np.arange(len(x))
plt.xticks(index, x, fontsize=5, rotation=30)
plt.bar(index, y)
return plt
def get_and_save_image():
query = """
SELECT DATE(creation_date) date, COUNT(*) total_posts
FROM `bigquery-public-data.stackoverflow.post_history`
GROUP BY 1
HAVING date > DATE_SUB('2018-12-02', INTERVAL 14 DAY)
ORDER BY 1
"""
dataframe = query_to_bigquery(query)
x = dataframe['date'].tolist()
y = dataframe['total_posts'].tolist()
plt = visualize_bar_chart(x=x, x_label='Date', y=y, y_label='Total Posts', title='Daily Posts')
plt.savefig('viz.png')
def send_image(bot, update):
get_and_save_image()
chat_id = 'CHAT_ID_RECEIVER'
bot.send_photo(chat_id=chat_id, photo=open('viz.png', 'rb'))
def main():
updater = Updater('YOUR_TOKEN')
updater.job_queue.run_daily(send_image, time=datetime.datetime.strptime('9:00AM', '%I:%M%p').time(), days=(0,1,2,3,4,5,6))
updater.start_polling()
updater.idle()
if __name__ == '__main__':
main()
ืฉืืืจ ืืช ืืงืืืฅ ืืงืจื ืื main.py.
ืื ื ืืคืขืืืื ืืช ืืืคืืืงืฆืื ืขื ืืื ืืื ืช ืืคืงืืื ืืืจืืื ื:
python3 main.py
ืืื ืืืื. ืืขืช ืืฉ ืื ื ืจืืืื ืืืืจืื ื-50 ืฉืืจืืช ืงืื ืฉืืคืืง ืืืืืช ืืื ืืชืขืจืืืชื ื.
ืืื ื ืืืืง ืืช ืืืื
ืืชื ืืืื ืืงืื ืืช ืืงืื ืืืืืืจ ื
Skillbox ืืืืืฆื:
- ืงืืจืก ืืขืฉื ืฉื ืชืืื
"ืื ื ืืคืชื ืืชืจืื PRO" .- ืงืืจืก ืืงืืื
"ืืคืชื C# ื-0" .- ืงืืจืก ืืขืฉื ืื ืฉื ื
"ืืคืชื PHP ื-0 ื-PRO" .
ืืงืืจ: www.habr.com