हम BigQuery नाम कैसे की कहानी

कार्य

वास्तव में, मैं जिस कार्य के बारे में बात करना चाहता हूं, वह इसके शब्दांकन में उदासी से सरल है: ई-कॉमर्स विभाग के बिक्री डेटा की कल्पना करना थोड़ा रक्त के साथ आवश्यक था, अर्थात्, पढ़ें, लगभग कुछ भी नहीं के लिए।

इसका क्या मतलब है? हमारे स्टोरों की टोकरियाँ दुनिया के विभिन्न क्षेत्रों में ऑनलाइन बिक्री पर डेटा की एक निरंतर धारा बनाती हैं, जिसके सभी परिणाम होते हैं: अलग-अलग मुद्राएँ, समय क्षेत्र, कर, ग्राहकों के प्रकार, वस्तुओं के प्रकार, ऑर्डर आदि। वास्तव में, वही किसी भी ऑनलाइन स्टोर द्वारा उत्पन्न होता है, केवल, शायद, ऑर्डर के मापदंडों के लिए विकल्प थोड़ा अलग हैं।

यह आवश्यक है कि इस स्ट्रीम पर बैठे, एक निश्चित एग्रीगेट डैशबोर्ड जारी किया जाए, जिस पर व्यवसाय वर्तमान क्षण के ऑनलाइन प्रमुख संकेतक और लंबे समय तक ऑनलाइन (या बल्कि, नियमित रूप से) देख सके। इसके अलावा, यह वांछनीय है कि ये डैशबोर्ड विस्तार और विशेषज्ञता के अलग-अलग डिग्री में कुछ अलग थे।

समाधान के लिए खोजें

स्वाभाविक रूप से, हमने एक तैयार समाधान की तलाश करके शुरू किया। हमने ऐसे डेटा विज़ुअलाइज़र को देखा, उदाहरण के लिए, Owox, Microsoft से BI समाधान, कुछ एंटरप्राइज़ समाधान, आदि।
यह सब विज्ञापन द्वारा बहुत अच्छा है। परंतु:

• सस्ता नहीं
• अपने आप में एक चीज़ (डेटा प्रोसेसिंग के यांत्रिकी को नियंत्रित करना इतना आसान नहीं है, और कभी-कभी आपको सिर्फ विकास कंपनी के डेटा वैज्ञानिकों की दक्षताओं पर निर्भर रहना पड़ता है)

उस समय (2019) तक, मेरे पास पहले से ही Google Datastudio में एक छोटी पॉकेट डेटा विज़ुअलाइज़ेशन प्रक्षेपण था, जो पहले से ही कम से कम एक वर्ष के लिए मौजूद था और इस तथ्य को कम कर दिया कि मैंने मैन्युअल रूप से 1 सी से रिपोर्ट अपलोड की है, इसे Google संग्रहण क्लाउड पर अपलोड किया है (भ्रमित होने के लिए नहीं) GoogleDrive), वहां से - BigQuery तालिका में, और फिर, DataStudio में, मैंने डेटा स्लाइस और बाकी सब कुछ किया।

— , , , , .

, , , , . Data Studio , BigQuery , , , , .

, , SQL BigQuery, , .

BigQuery (https://console.cloud.google.com/bigquery), , .

, : 300 . , 2 BigQuery, , .

. , , BigQuery , , . , !

, SELECT, , BQ.

, BQ , . , , , .

: SQL ==> Python ==> BigQuery.

, — .

, SELECT csv- GoogleStorage BQ , Google Functions, Python ( ) . , 6 ( ) .

, ?

, , , SQL . , Orders, , , , , , , id, id .. , , .

, , SELECT pandas dataframe. :

import pandas as pd
import MySQLdb
import MySQLdb.cursors

def sql_query(date0, date1):
    connection = MySQLdb.connect(host=”server”, user=”user”, passwd=”pass”, db=”orders”, charset=”cp1251”, cursorclass = MySQLdb.cursors.SSCursor)
    query = “SELECT * FROM orders WHERE date>=’”+date0+”’ and date<’”+date1+”’ ”
    data =  pd.read_sql_query(query, connection=connection)
#  query -   SELECT   ,     date0  date1,   , ,   5 .          -       .          ,      BQ
# , ,  connection      ,        ..       .

#     -  . ,        :

    data.payment_date = data.payment_date.apply(lambda x: x.tz_localize(‘Europe/Moscow’)).astype(str)

#     str -  .  ,      ,       .     BQ         .

# ,     pandas     BQ,           ,      .

    return data

, 5- , (90 , .. ) , sql_query(date) , BQ.

, , pandas dataframe .

: , BQ MERGE . BQ 1-2 . 20 70 , STRING FLOAT. 70000 .

, . BQ, .

from google.cloud import bigquery
from google.oauth2 import service_account

project_id = “MyBQproject”  #    BQ
dataset_id = “MyDataSet”  #     

#       BQ.       - ( ,    ).       BQ.  ,   ,    ‘Service accounts’  Google Cloud Platform.

credentials = service_account.Credentials.from_service_account_file(“acc.json”)
client = bigquery.Client(project_id, credentials=credentials)

def upload_temp_table(df, table_id, schema):
    job_config = bigquery.LoadJobConfig()
    job_config.schema = schema
   # job_config.autodetect = True  #     
    job_config.write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE  #  
    job = client.load_table_from_dataframe(df, table_id, job_config=job_config)
    job.result()   #  

def merge_tables(temp_table, base_table, schema, head, merge_param):
    merge_query = “MERGE into “+base_table+” as T USING “+temp_table+” as S \
         ON T.”+merge_param+” = S.”+merge_param+ \
         “ WHEN MATCHED then UPDATE SET “+ \
         ‘,’.join([‘T.’+item+’=S.’+item for item in head])+ \
         “ WHEN NOT MATCHED then INSERT \
         (“+’,’.join(head)+”) VALUES (“+’,’.join([‘S.’+item for item in head])+”);”

#  ,         merge_param (,  id ,        )

#  -  

    job_config = bigquery.LoadJobConfig()
    job_config.schema = schema
    job = client.query(merge_query, job_config=job_config)
    job.result()

, — .

import datetime

for period in range(18):
    date0 = (datetime.datetime.now() - datetime.timedelta(days = period*5 + 5)).strftime(‘%Y%m%d’)
    date1 = (datetime.datetime.now() - datetime.timedelta(days = period*5)).strftime(‘%Y%m%d’)
    df = sql_query(date0,date1)   #    sql   >=date0, <date1
    upload_temp_table(df=df, table_id=”temp_table”, schema)  #    
    merge_tables(temp_table=’temp_table’, base_table = ‘orders’, schema=schema, head=[,,,], merge_param=’id’)

head — . . SQL , . MERGE- . BQ . - , Python , BQ.

.

import sys

log_file = open(“log.txt”, ‘a’)
old_stdout = sys.stdout
sys.stdout = log_file

, print
:

sys.stdout = old_stdout
log_file.close()

print -. , .

, BigQuery , DataStudio (https://datastudio.google.com/) .

, DataStudio , BigQuery Python. , , , apply.

, DataStudio (, ), , , , Datastudio .

, Datastudio Blended Tables, LEFT JOIN - . , , , BigQuery. , , , SQL-, BigQuery . , , 6 , , 6 . 20 70 3-4 BigQuery. .

, . , . - BigQuery, - Python. , merge_tables — BQ . , .. — . , .

, BigQuery , Google Spreadsheets. - id . :

• Python pandas dataframe
• BQ JOIN BQ (-, ).

, . - , , , .

. - -, . , , ( , F5). : Data Studio Auto-Refresh. : , View, Data Studio Auto-Refresh, , SQL=>BQ.

, , , :-)