In [1]:

    

import ipywidgets as widgets
import random
import pandas as pd
import plotly.graph_objects as go
import sys
import time
from IPython.display import display

import verdict

verdict.set_loglevel('error')
v = verdict.presto('presto')

def to_dow_name(dow_number):
    return {
        0: 'Saturday',
        1: 'Sunday',
        2: 'Monday',
        3: 'Tuesday',
        4: 'Wednesday',
        5: 'Thusday',
        6: 'Friday',
    }[dow_number]

def replace_dow(frame):
    if 'order_dow' not in frame.columns:
        return frame
    frame['order_dow'] = frame['order_dow'].apply(to_dow_name)
    return frame

def hsv_to_rgb(h, s, v):
    # h = [0, 360), s = [0, 1], v = [0, 1]
    c = (1 - abs(2*v - 1)) * s
    x = c * (1 - abs((h/60)%2 - 1))
    m = v - c/2
    h_i = int(h/60)
    r, g, b = {
        0: [c, x, 0],
        1: [x, c, 0],
        2: [0, c, x],
        3: [0, x, c],
        4: [x, 0, c],
        5: [c, 0, x],
    }[h_i]
    r, g, b = int((r+m)*255), int((g+m)*255), int((b+m)*255)
    return (r, g, b)

def rbg_to_str(rgb):
    r, g, b = rgb
    return f"rgb({r}, {g}, {b})"

def gen_colors(num):
    h_begin = 200.0
    h_end = 39.0 + 360.0
    l_begin = 0.3
    l_end = 0.55
    hsl = []
    for i in range(num):
        h = h_begin + (h_end - h_begin)*i/float(num-1)
        h = h%360
        l = l_begin + (l_end - l_begin)*i/float(num-1)
        s = min(abs(h-260)/60.0*0.7+0.3, 1.0)
        hsl.append((h, s, l))
    colors = [rbg_to_str(hsv_to_rgb(a[0], a[1], a[2])) for a in hsl]
    return colors

def new_figure():
    new_figure = go.FigureWidget(data=go.Bar())
    new_figure.update_layout(template='none', height=400)
    return new_figure

def updateFigure(fig, df):
    cols = df.columns
    if len(cols) == 1:
        fig.data[0].x = ['value']
        fig.data[0].y = df[cols[0]]
    else:
        fig.data[0].x = df[cols[0]]
        fig.data[0].y = df[cols[-1]]
#     fig.data[0].marker.color = gen_colors(len(df.index))

def sql(query):
    fig = new_figure()
    display(fig)
    start = time.time()
    result = v.sql(query)
    if len(result.columns) == 1:
        display(result)
    else:
        updateFigure(fig, result)
    print(f"elapsed time: {time.time() - start} seconds.")
    
def presto_sql(query):
    sql("bypass " + query)

def sql_stream(query):
    fig = new_figure()
    display(fig)
    result_itr = v.sql_stream(query)
    for i, result in enumerate(result_itr):
        result = replace_dow(result)
        if i < 5:
            if len(result.columns) == 1:
                display(result)
            else:
                updateFigure(fig, result)
                time.sleep(0.4)
        else:
            break


    

In [13]:

    

sql("""bypass select department, count(*) c from hive.instacart.order_products where add_to_car_order = 25 
       group by department order by department""")


    

    






 
 










    




elapsed time: 30.055830240249634 seconds.

In [15]:

    

sql_stream("""select department, count(*) c from hive.instacart.order_products where add_to_car_order = 25 
              group by department order by department""")


    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]:

    




    

In [ ]: