Get best of the week

Kisah bagaimana kami menjinakkan BigQuery

Tugas


Bahkan, tugas yang ingin saya bicarakan adalah sederhana untuk muram dalam kata-katanya: perlu memvisualisasikan data penjualan departemen e-commerce dengan sedikit darah, yaitu, membaca, hampir tanpa bayaran.


Apa yang dimaksud dengan ini? Keranjang toko kami menghasilkan aliran data yang konstan pada penjualan online di berbagai wilayah di dunia dengan semua konsekuensinya: mata uang yang berbeda, zona waktu, pajak, jenis pelanggan, jenis barang, pesanan, dll. Bahkan, hal yang sama dihasilkan oleh toko online mana pun, hanya saja, mungkin, opsi untuk parameter pesanan sedikit berbeda.


Diperlukan, duduk di arus ini, untuk mengeluarkan dasbor agregat tertentu, di mana bisnis dapat menonton indikator kunci online saat ini dan untuk periode lama online (atau lebih tepatnya, secara teratur). Selain itu, diharapkan bahwa dasbor ini agak berbeda dalam berbagai tingkat detail dan spesialisasi.


Cari solusinya


Secara alami, kami mulai dengan mencari solusi yang sudah jadi. Kami melihat visualisator data seperti, misalnya, Owox, solusi BI dari Microsoft, beberapa solusi perusahaan, dll.
Semua ini luar biasa dilayani oleh iklan. Tapi:


  • tidak murah
  • suatu hal dalam dirinya sendiri (tidak mudah untuk mengontrol mekanisme pemrosesan data, dan kadang-kadang Anda hanya harus bergantung pada kompetensi ilmuwan data dari perusahaan pengembangan)

Pada saat itu (2019), saya sudah memiliki proyeksi visualisasi data saku kecil di Google Datastudio, yang sudah ada selama setidaknya satu tahun dan berkurang menjadi fakta bahwa saya mengunggah laporan secara manual dari 1C, mengunggahnya ke Google Storage Cloud (jangan bingung dengan GoogleDrive), dari sana - ke tabel BigQuery, dan kemudian, di DataStudio, saya melakukan irisan data dan yang lainnya.


— , , , , .


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