Get best of the week

A história de como domamos o BigQuery

Tarefa


De fato, a tarefa sobre a qual quero falar é simples de desanimar: era necessário visualizar os dados de vendas do departamento de comércio eletrônico com pouco sangue, isto é, lido, quase por nada.


O que se entende por isso? As cestas de nossas lojas geram um fluxo constante de dados sobre vendas on-line em diferentes regiões do mundo, com todas as consequências: moedas, fusos horários, impostos, tipos de clientes, tipos de itens, pedidos, etc. De fato, o mesmo é gerado por qualquer loja online, apenas, talvez, as opções para os parâmetros dos pedidos sejam ligeiramente diferentes.


É necessário, sentado nesse fluxo, emitir um determinado painel agregado, no qual a empresa possa assistir a indicadores-chave on-line do momento atual e por longos períodos on-line (ou melhor, regularmente). Além disso, é desejável que esses painéis sejam um pouco diferentes em diferentes graus de detalhe e especialização.


Procure uma solução


Naturalmente, começamos procurando uma solução pronta. Analisamos visualizadores de dados como, por exemplo, Owox, soluções de BI da Microsoft, algumas soluções corporativas etc.
Tudo isso é ótimo servido pela publicidade. Mas:


  • não é barato
  • uma coisa em si (não é tão fácil controlar a mecânica do processamento de dados e, às vezes, você só precisa confiar nas competências dos cientistas de dados da empresa de desenvolvimento)

Naquela época (2019), eu já tinha uma pequena projeção de visualização de dados de bolso no Google Datastudio, que já existia há pelo menos um ano e reduzia ao fato de eu ter carregado manualmente o relatório da 1C, carregado na Google Storage Cloud (para não confundir com GoogleDrive), de lá - para a tabela do BigQuery e, em seguida, no DataStudio, fiz fatias de dados e tudo mais.


— , , , , .


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


, , , :-)

More articles:


All Articles