ืฆื ืืืจ ืืึธืื ืืึทืกืงืก ืืืึธืก ืืืืขืจืืืจื ืืึธื ื ืึธื ืืึธื, ืืืึธื ื ืึธื ืืืึธื? ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืฉืจืืืื ืจืืคึผืึธืจืฅ. ืืืจ ืืขืื ืืึทืื, ืึทื ืึทืืืื ืขืก, ืืืืืฉืืืึทืืืื ืขืก (ืืึทืื ืืจืึทืคืก, ืืฉืึทืจืฅ) ืืื ืฉืืงื ืขืก ืฆื ืืืื ืืึทืืขืืึธืก. ืืืขืจ ืืืึธืก ืืืื ืึทืืข ืืขื ืืื ืึธืืึทืืืืืื?
ืืื ืืขื ืืืืึธืจืืึทื, ืืืจ ืืืขืื ืฉืึทืคึฟื ืึท ืืึธื ืคึฟืึทืจ ืืขืืขืืจืึทื ืืืึธืก ืืืขื ืืขืืคึฟื ืึธืืึทืืืื ืจืืคึผืึธืจืืื ื. ืืื ืื ืงืืืึทืกื ืืึทื ืืื ืึทื ืื ืืื ืฆืข ืคึผืจืึธืืจืึทื ืืืขื ืฆืื ืืืคืฉืืขืื ืืื ืคืื ืืืืื 50 ืฉืืจืืช ืคืื ืงืึธื! ืืืื ืืืจ ืฉืึทืคึฟื ืึท ืืึธื ืคึฟืึทืจ ืืขืืขืืจืึทื ืคึฟืึทืจ ืื ืขืจืฉืืขืจ ืืึธื, ืืืจ ืืึธื ืืืื ืืืืขื ืขื ืืขื .
Skillbox ืจืขืงืึทืืขื ืื: ืคึผืจืึทืงืืืฉ ืงืืจืก .
ืืืจ ืืขืจืืึธื ืขื ืืืจ: ืคึฟืึทืจ ืึทืืข ืืืืขื ืขืจ ืคืื "ืืืจ" - ืึท ืึทืจืึธืคึผืจืขืืขื ืขื ืคืื 10 ืจืืื ืืืขื ืืืจ ืคืึทืจืฉืจืืึทืื ืืื ืงืืื ืกืงืืืืึธืงืก ืงืืจืก ื ืืฆื ืื ืคึผืขืจืืึธืืฉืึทื ืึทื ืงืึธื "ืืืจ".
ืืขืืื ื ืึทืจืึธืคึผ
ืื ืกืืึธืืื ื ืืืืืจืขืจืื
ืืืจ ืืืขืื ื ืืฆื ืฆื ืืึทืงืืืขื ืืึทืื ืคึฟืื Google BigQuery. , ะธ ืืืขื ืืขืืคึฟื ืืืจ ืืืืืฉืืืึทืืืื ืืืื ืืึทืื. ืืืขื ืฉืืงื ืื ืคืึทืจืืืง ืืึทืื ืฆื ืืขืืขืืจืึทื.
pip3 ืื ืกืืึทืืืจื Google-cloud-bigquery ืืึทืืคึผืืึธืืืื ื ืืคึผื ืคึผืึทื ืืึทืก ืคึผืืืืึธื-ืืขืืขืืจืึทื-ืืึธื
ืงืึทื ืขืงืืื ื Google BigQuery API
ืืืื ืืืจ ืืืืื ืฆื ื ืืฆื ืื ืกืขืจืืืืก, ืืืจ ืืึทืจืคึฟื ืฆื ืคืึทืจืืื ืื ืื Google BigQuery API. ืฆื ืืึธื ืืึธืก ืืืจ ืืืื ืฆื ืืื ืฉืึทืคึฟื ืึท ื ืืึทืข ืคึผืจืืืขืงื (ืึธืืขืจ ืืืืกืงืืืึทืื ืึท ืืืืืกืืื ื ืืืื ืขืจ).
ืืื ืื ืงืึธื ืืจืึธื ืืึทืคืืืข, ืกืขืืขืงืืืจื ืืขืื ืึทืคึผืคึผืก ืืื ืกืขืจืืืืกืขืก ืืื ืงืืง ืคึฟืึทืจ BigQuery API.
ืกืขืืขืงืืืจื ืืขืื ืฆื ืคืึทืจืืื ืื ืื ืึทืคึผื.
ืฉืึทืคึฟื ืึท ืืฉืืื ืฉืืืกื
ืืื ืก ืืืื ืฆื ืืืืืขืจ , ืืืืกืงืืืึทืื ืื ืงืจืึทืืขื ืืฉืึทืื ืงืืืืื, ืฉืึทืคึฟื ืงืจืึทืืขื ืืฉืึทืื ืืื ืกืขืจืืืืก ืืฉืืื ืฉืืืกื.
ืืขืจื ืึธื - ื ืื ืืื ืกื ืืฉืืื, ืืื ืึทืจืืึทื ืื ื ืึธืืขื ืืื ืื ืกืขืจืืืืก ืืฉืืื ื ืึธืืขื ืคืขืื.
ืคืื ืื ืจืึธืืข ืคืึทื-ืึทืจืึธืคึผ ืจืฉืืื, ืืืืกืงืืืึทืื ืคึผืจืึธืืขืงื > ืืึทืืืฆืขืจ, ืืขืืึธืื ืฉืึทืคึฟื.
ืืขืจ ืืขืงืข ืืืึธืก ืืืขื ืืืื ืืืืืึธืืึทืืืฉ ืืึทืื ืืึธืืืื ืืื ืืขืจืืคื creds.json.
ืฉืืขืื GOOGLE_APPLICATION_CREDENTIALS, ืกืคึผืขืฆืืคืืฆืืจื ืืขื ืืจื ืฆื creds.json ืืื ืื ืืืึธืงืืึทื.
ืึทืจืืืกืคืืจื GOOGLE_APPLICATION_CREDENTIALS='[PATH_TO_CREDS.JSON]'
ืืืื ืึทืืฅ ืืื ืืื, ืขืก ืืื ืฆืืื ืฆื ืึธื ืืืืื ืฉืจืืืื ืืขื ืคึผืจืึธืืจืึทื.
ืฉืึทืคึฟื ืึท ืึทืคึผืืึทืงืืืฉืึทื
ืคึฟืึทืจ ืืขื ืืืืึธืจืืึทื, ืืืจ ืืืขืื ื ืืฆื ืืึทืื ืคึฟืื bigquery-public-data.stackoverflow, ืคึฟืึทืจ ืืื ืืืขืจ ืืึทืจืืื ืืืจ ืืืขืื ืืืืกืงืืืึทืื ืื ื ืืืขืจ ืคืื ืืขืืืขื ืืืืกืืืืขืก.
ืขืก ืืื ืฉืืื ืคึผืฉืื.
ืึธื ืคึฟืจืขื ืื ืืืฉ -> ืืืืกืืึทืืืืืจื ืื ืืึทืื -> ืืื ืื ืืืืืฉืืืึทืืึทืืืืฉืึทื -> ืฉืืงื ืื ืืืื
ืืื ืก ืืึทืื ืืืื ืคึฟืื ืงืฆืืข ืฆื ืืขืคืื ืืจื ืืขืืขืจ ืคืึธืืขื.
ืึธื ืคึฟืจืขื ืฆื BigQuery
ืขืจืฉืืขืจ ืืืจ ืึทืจืืึทื ืคืืจ ืื ืืืืืืึธืืขืง.
ืคึฟืื google.cloud ืึทืจืืึทื ืคืืจ ืืืืงืืืขืจื
ืืืจ ืืึทืื ืึท ืคึฟืื ืงืฆืืข ืืขืจืืคึฟื query_to_bigquery, ืืื ืืขืจ ืคึผืึทืจืึทืืขืืขืจ ืืื ืึธื ืคึฟืจืขื.
def query_to_bigquery(query):
client = bigquery.Client()
query_job = client.query(query)
result = query_job.result()
dataframe = result.to_dataframe()
return dataframeืื ืคึฟืื ืงืฆืืข ืืืขื ืฆืืจืืงืงืืืขื ืื ืืขืื ืืื ืึท ืืึทืื ืจืึทื.
ืืืืกืืึทืืืืืื ื ืืึทืื
ืฆื ืกืึธืืืืข ืืขื ืคึผืจืึธืืืขื, ืงืืืึทืื Matplotlib.
ืึทืจืืึทื ืคืืจ ืืึทืืคึผืืึธืืืื.ืคึผืืคึผืืึธื ืืื ืคึผืื
ืืืจ ืืึทืจืคึฟื ืคืื ืฃ ืคึผืึทืจืึทืืขืืขืจืก, ืืื x ืืื ืื X-ืึทืงืก ืืึทืื, x_label ืืื ืืขืจ ืืืื ืคึฟืึทืจ ืื ืึทืงืก, 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 ืคึฟืื ืงืฆืืข ืฆื ืืึทืงืืืขื ืื ืืึทืื.
ืืึทืืึทืคืจืึทืืข = query_to_bigquery (ืึธื ืคึฟืจืขื)
ืืขืจื ืึธื ืืืจ ื ืืฆื ืื ืืึทืืข ืืึทืื ืืืึทื ืคึฟืึทืจ ืื X-ืึทืงืก, ืืื ืื total_posts ืืืึทื ืคึฟืึทืจ ืื ื-ืึทืงืก.
ืจืขื ืืืขื = ืืึทืืึทืคืจืึทืืข ['ืืึทืืข']. ืืึธืืืกื ()
y = ืืึทืืึทืคืจืึทืืข['total_posts'].tolist()
ืืืจ ืืืืืฉืืืึทืืืื ืขืก ืืื ืื visualize_bar_chart ืคืื ืงืฆืืข ืืื ืจืึทืืขืืืขื ืขืก ืืื ืึท ืืืื.
plt = visualize_bar_chart (x=x, x_label='Datum', 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 ืจืขืงืึทืืขื ืื:
- ืฆืืืื-ืืึธืจ ืคึผืจืึทืงืืืฉ ืงืืจืก .
- ืึธื ืืืื ืงืืจืก .
- ืคึผืจืึทืงืืืฉ ืืึธืจ ืงืืจืก .
ืืงืืจ: www.habr.com