Me pehea te tuku purongo ngawari ki te karetao. Te tuhi karetao ki Python me Google BigQuery

Kei a koe nga mahi e mahi tonu ana i ia ra, ia wiki, ia wiki? Hei tauira, te tuhi purongo. Ka tono raraunga, tātarihia, whakaatahia (hanga kauwhata, tūtohi), ka tukuna atu ki to rangatira. Engari ka pehea mena he mea aunoa enei katoa?

I roto i tenei akoranga ka hangaia e matou he karetao mo Telegram ka awhina i te whakaputa purongo. A ko te mea tino pai ko te kaupapa katoa ka 50 noa nga rarangi waehere! Mena kei te hanga e koe he bot mo Telegram mo te wa tuatahi, me panui ano koe i tenei pou.

Ka tūtohu a Skillbox: Akoranga mahi Kaiwhakawhanake Python mai i te wahanga.

Ka whakamahara matou: mo nga kaipānui katoa o "Habr" - he utu mo te 10 rubles i te wa e whakauru ana ki tetahi akoranga Skillbox ma te whakamahi i te waehere whakatairanga "Habr".

Kia timata tatou

Te whakauru whare pukapuka

Ka whakamahia e matou google-kapua-nui ki te tiki raraunga mai i a Google BigQuery. matplotlib, numpy и porangi ka awhina koe ki te tiro i o raraunga. python-telegram-bot ka tukuna nga raraunga kua oti ki a Telegram.

pip3 tāuta google-cloud-bigquery matplotlib numpy pandas python-telegram-bot

Te hono i te API Google BigQuery

Mena kei te pirangi tatou ki te whakamahi i te ratonga, me hono tatou i te Google BigQuery API. Ki te mahi i tenei ka haere tatou ki Google Console Console me te hanga i tetahi kaupapa hou (me kowhiri ranei i tetahi kaupapa kua puta).

I roto i te paewhiri mana, tohua te WHAKAMAHI APIS ME NGA RATONGA ka rapu mo BigQuery API.

Tīpakohia Whakahohe ki te hono i te API.

Waihangahia he kī pūkete

Me haere ano ki Google Console Console, tīpakohia te ripa Taipitopito, Waihangahia nga tohu me te Kī pūkete Ratonga.

Na - Pūkete ratonga hou, ka tomo i te ingoa i roto i te ingoa pūkete Ratonga mara.

Mai i te rārangi taka-iho Tūnga, tīpako Kaupapa > Kaipupuri, ka Waihanga.

Ko te konae ka tangohia aunoatia ka kiia ko creds.json.

Tautuhia GOOGLE_APPLICATION_CREDENTIALS, whakapūtā te ara ki creds.json i te tauranga.

kaweake GOOGLE_APPLICATION_CREDENTIALS='[PATH_TO_CREDS.JSON]'

Mena i pai nga mea katoa, kua tae ki te wa ki te tuhi i te kaupapa.

Te hanga tono

Mo te akoranga ka whakamahia e matou nga raraunga mai i bigquery-public-data.stackoverflow, mo ta matou ripoata ka tohua e matou te maha o nga whakaputanga o ia ra.

He maamaa noa nga mea katoa.

Uia te ripanga -> Tirohia te raraunga -> Tiaki te tirohanga -> Tukuna te ahua

Me hanga tetahi mahi hei tautuhi i ia miro.

Uiui ki BigQuery

Tuatahi ka kawemai i te whare pukapuka.

mai i google.cloud kawemai bigquery

Ka hangaia e matou he mahi e kiia nei ko query_to_bigquery, ko te tawhā ko te uiui.

def query_to_bigquery(query):
    client = bigquery.Client()
    query_job = client.query(query)
    result = query_job.result()
    dataframe = result.to_dataframe()
    return dataframe

Ma tenei mahi ka whakahoki te tono hei anga raraunga.

Te whakaata i nga raraunga

Hei whakaoti i tenei raruraru, whiriwhiria matplotlib.

kawemai matplotlib.pyplot hei plt

E rima nga tawhā, ko x te raraunga tuaka-x, x_label te taitara mo te tuaka, y te raraunga tuaka-y, y_label te taitara mo te tuaka, ko te taitara te taitara o te tirohanga katoa.

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

Tiakina te whakaahua

Inaianei me whakamahi e rua nga mahi ki te hanga tirohanga me te penapena.

Ka tukuna e matou te maha o nga panui kua whakaputaina ia ra. Tuatahi ka tuhia he tono.

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
        """

Ka awhina te patai ki te kohi raraunga mo nga wiki e rua ka timata i te Hakihea 2, 2018.

Ka whakamahia e matou tenei ra na te mea ko te 2018-12-02 nga raraunga hou kua tuhia ki bigquery-public-data.stackoverflow.post_history, i etahi atu keehi ka taea e koe te whakamahi CURRENT_DATE() ki te tiki i nga raraunga hou.

Waea te mahi query_to_bigquery ki te tiki raraunga.

anga raraunga = query_to_bigquery(uiui)

Na ka whakamahi matou i te pou raraunga ra mo te tuaka-x, me te pou_tapeke pou mo te tuaka-y.

x = anga raraunga['rā'].tolist()
y = anga raraunga['total_posts'].tolist()

Ka whakaatahia ma te whakamahi i te mahi visualize_bar_chart ka tiakina hei ahua.

plt = visualize_bar_chart(x=x, x_label='Rā', y=y, y_label='Tapeke Whakairi', taitara='Whakairinga Ia Ra')
plt.savefig('viz.png')

Ka takai tatou i tenei waehere ki tetahi mahi ko 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')

Tukuna he whakaahua

Hei tuku purongo ki te kaiwhiwhi, me mohio koe ki te tawhā chat_id.

Ka whakamahia e matou userinfobot ka patohia /timata. Ka whakautu te karetao me nga korero e tika ana, kei roto te chat_id i te mara id.

Inaianei me hanga te mahi send_image. Ka whakamahia te mahi get_and_save_image ki te tiki me te tiaki i te ahua. Na ka tukuna e matou nga mea katoa ki te whakapiri tika.

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'))

Kaupapa matua

Ka mutu, ka hangaia e matou tetahi atu mahi, matua, hei whakarewa i te tono. Kaua e wareware ki te huri i YOUR_TOKEN mo te karetao.

Kia mahara: ka tukuna aunoatia e tenei papatono te ahua i te wa e tohua ana e koe. Hei tauira, ka tukuna he ripoata a te iwa i te ata ia ra.

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()

Ko te mutunga, ka penei to maatau tono:

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()

Tiakina te konae ka kiia he main.py.

Ka whakarewahia e matou te tono ma te whakauru i te whakahau ki te tauranga:

python3 matua.py

Kua reri katoa. Inaianei kei a matou he karetao e 50 nga rarangi waehere ka whakaputa purongo me te kore e wawao.

Kia tirohia te karetao mai i koneimā te kōwhiri i te tono /tuku.

Ka taea e koe te tiki i te waehere oti i taku GitHub.

Ka tūtohu a Skillbox:

• E rua tau te akoranga mahi "He kaiwhakawhanake paetukutuku PRO ahau".
• Akoranga aipurangi "C# kaiwhakawhanake mai i 0".
• Akoranga tau mahi "Whakawhanake PHP mai i te 0 ki te PRO".

Source: will.com