ααΎβα’αααβααΆαβαα·α
αα
ααΆαβαααβααααΎβααααβαααβααΈβαα½αβααααβαα
βαα½αβαααααΆα αβα¬βαα? α§ααΆα αααααΆαααααααααΆαααΆαααα α’αααααααΎαα»ααα·αααααα αα·ααΆαααΆ αααααααΎαααΆ (ααααΎααααΆα αα ααααΌαααΆα) α αΎααααααΆααααααααΎααΆαα
α
α
α αααΆαααααα’αααα ααα»ααααα
α»ααααΆαααΆααΎα’αααΈαααΆααα’αααααααααΌαααΆαααααααααααααα·?
αα
αααα»ααααααααα ααΎαααΉααααααΎα bot αααααΆαα Telegram αααααΉααα½αααααΎαααΆαααΆααααααααααααααααααα·α α αΎαα’αααΈααααα·ααααααα»ααααααΊααΆαααααα·ααΈααΆααααΌαααΉαααΆαααΌαααααΉααα 50 αααααΆααααα»ααααα! ααααα·αααΎα’ααααααα»ααααααΎα bot αααααΆαα Telegram ααΆααΎαααααΌαααα α’ααααααα½αααα’αΆαααΆαααα
Skillbox ααααΆαα αααααα·ααααΆααΆααααααα
α’αααα’αα·αααααα Python ααΈααααΌα .ααΎαααααΉαα αααααΆααα’αααα’αΆαααΆααα’αααα "Habr" - ααΆααααα α»αααααα 10 rubles αα αααα α»αααααααααα»ααααααα·ααααΆ Skillbox ααΆαα½ααααααααΎαααααΌαααααααααααΆα "Habr" α
αααβα αΆααααααΎα
ααΆαααα‘αΎααααααΆααα
ααΎαααΉαααααΎ
pip3 ααα‘αΎα google-cloud-bigquery matplotlib numpy pandas python-telegram-bot
αααα»αααααΆαα Google BigQuery API
ααααα·αααΎααΎαα
ααααααΎααααΆααααααΆαααα ααΎαααααΌαααααΆαα Google BigQuery APIα ααΎααααΈααααΎααΌα
αααααΎααα
αα αααα»αααααΆαααααααΆ ααααΎαααΎα ααΎα APIS αα·α 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(αααα½α)
αααααΆααααααΎαααααΎαα½ααααα·ααααααααΆαααα·α αααααααααΆααα’αααα 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='αααααΆααααα
αΆααααα')
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 αααααΆααααααΌαααααααααΎααααΆαααΆααααααααααΆαααΆαα’αααααΆααααααααααΎαα
ααααα·αα·αααααΎα bot
α’αααα’αΆα
ααα½αααΆααααααΌααααααΆααααα
αααα
Skillbox ααααΆαα
- αααααα·ααααΆααΆαααααααααααααααΈαααααΆα
"αααα»αααΆα’ααααααααΎαααα ααααα PRO" .- αααααα·ααααΆααΆαα’ααΈαααΊαα·α
"α’αααα’αα·αααααα C# ααΈ 0" .- αααααα·ααααΆααΆαααααααααααΆα
"α’αααα’αα·αααααα PHP ααΈ 0 ααα PRO" .
ααααα: www.habr.com