L'histoire de la façon dont nous avons apprivoisé BigQuery

Tâche

En fait, la tâche dont je veux parler est simple à obscurcir dans son libellé: il fallait visualiser les données de vente du service e-commerce avec peu de sang, c'est-à-dire lire, presque pour rien.

Qu'entend-on par là? Les paniers de nos magasins génèrent un flux constant de données sur les ventes en ligne dans différentes régions du monde avec toutes les conséquences: différentes devises, fuseaux horaires, taxes, types de clients, types d'articles, commandes, etc. En fait, la même chose est générée par n'importe quelle boutique en ligne, seulement, peut-être, les options pour les paramètres des commandes sont légèrement différentes.

Il est nécessaire, assis sur ce flux, d'émettre un certain tableau de bord agrégé, sur lequel l'entreprise pourrait regarder en ligne les principaux indicateurs du moment actuel et pendant de longues périodes en ligne (ou plutôt régulièrement). De plus, il est souhaitable que ces tableaux de bord soient quelque peu différents à divers degrés de détail et de spécialisation.

Rechercher une solution

Naturellement, nous avons commencé par chercher une solution toute faite. Nous avons examiné des visualiseurs de données tels que, par exemple, Owox, les solutions BI de Microsoft, certaines solutions d'entreprise, etc.
Tout cela est très bien servi par la publicité. Mais:

• pas cher
• une chose en soi (ce n'est pas si facile de contrôler la mécanique du traitement des données, et parfois il suffit de compter sur les compétences des data scientists de la société de développement)

À ce moment-là (2019), j'avais déjà une petite projection de visualisation de données de poche dans Google Datastudio, qui existait déjà depuis au moins un an et réduite au fait que j'ai téléchargé manuellement le rapport à partir de 1C, je l'ai téléchargé sur Google Storage Cloud (à ne pas confondre avec GoogleDrive), de là - à la table BigQuery, puis, dans DataStudio, j'ai fait des tranches de données et tout le reste.

— , , , , .

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

, , , :-)