Die Geschichte, wie wir BigQuery gezähmt haben

Aufgabe

Tatsächlich ist die Aufgabe, über die ich sprechen möchte, in ihrem Wortlaut einfach zu verdunkeln: Es war notwendig, die Verkaufsdaten der E-Commerce-Abteilung mit wenig Blut zu visualisieren, d. H. Fast umsonst zu lesen.

Was ist damit gemeint? Die Körbe unserer Geschäfte generieren einen konstanten Datenstrom über Online-Verkäufe in verschiedenen Regionen der Welt mit allen Konsequenzen: verschiedene Währungen, Zeitzonen, Steuern, Kundentypen, Artikeltypen, Bestellungen usw. In der Tat wird das gleiche von jedem Online-Shop generiert, nur vielleicht unterscheiden sich die Optionen für die Parameter von Bestellungen geringfügig.

In diesem Stream muss ein bestimmtes aggregiertes Dashboard erstellt werden, in dem das Unternehmen Online-Schlüsselindikatoren für den aktuellen Moment und für lange Zeiträume online (oder besser gesagt regelmäßig) anzeigen kann. Darüber hinaus ist es wünschenswert, dass diese Dashboards in unterschiedlichem Detaillierungsgrad und Spezialisierung etwas unterschiedlich waren.

Suche nach einer Lösung

Zunächst suchten wir nach einer vorgefertigten Lösung. Wir haben uns Datenvisualisierer wie beispielsweise Owox, BI-Lösungen von Microsoft, einige Unternehmenslösungen usw. angesehen.
All dies wird durch Werbung großartig bedient. Aber:

• nicht billig
• eine Sache für sich (es ist nicht so einfach, die Mechanismen der Datenverarbeitung zu steuern, und manchmal muss man sich nur auf die Kompetenzen der Datenwissenschaftler des Entwicklungsunternehmens verlassen)

Zu diesem Zeitpunkt (2019) hatte ich bereits eine kleine Projektion zur Visualisierung von Taschendaten in Google Datastudio, die bereits seit mindestens einem Jahr existiert und darauf zurückzuführen ist, dass ich den Bericht manuell von 1C heruntergeladen und in die Google Storage Cloud hochgeladen habe (nicht zu verwechseln) GoogleDrive), von dort - zur BigQuery-Tabelle, und dann habe ich in DataStudio Daten-Slices und alles andere erstellt.

— , , , , .

, , , , . 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.

, , , :-)