Publish your first SingleStore DashApp

1
%%sql
2
DROP TABLE IF EXISTS orders;
3

4
CREATE TABLE IF NOT EXISTS orders (
5
    order_id INT PRIMARY KEY,
6
    order_date DATE,
7
    amount DECIMAL(10, 2),
8
    name VARCHAR(50)
9
);

1
%%sql
2
INSERT INTO orders (order_id, order_date, amount, name) VALUES
3
(1, '2024-01-01', 150.00, "Laptop"),
4
(2, '2024-01-01', 20.00, "Speaker"),
5
(3, '2024-01-01', 60.00, "Monitor"),
6
(4, '2024-01-02', 300.00, "Laptop"),
7
(5, '2024-01-02', 100.00, "Laptop"),
8
(6, '2024-01-02', 100.00, "Laptop"),
9
(7, '2024-01-02', 25.00, "Speaker"),
10
(8, '2024-01-02', 20.00, "Speaker"),
11
(9, '2024-01-02', 75.00, "Monitor"),
12
(10, '2024-01-03', 350.00, "Laptop"),
13
(11, '2024-01-03', 150.00, "Laptop"),
14
(12, '2024-01-03', 25.00, "Speaker"),
15
(13, '2024-01-03', 35.00, "Speaker"),
16
(14, '2024-01-03', 55.00, "Monitor"),
17
(15, '2024-01-04', 120.00, "Laptop"),
18
(16, '2024-01-04', 120.00, "Laptop"),
19
(17, '2024-01-04', 30.00, "Speaker"),
20
(18, '2024-01-04', 40.00, "Speaker"),
21
(19, '2024-01-04', 25.00, "Speaker"),
22
(20, '2024-01-04', 50.00, "Monitor"),
23
(21, '2024-01-04', 70.00, "Monitor");

1
from sqlalchemy import create_engine, text
2
import requests
3

4
ca_cert_url = "https://portal.singlestore.com/static/ca/singlestore_bundle.pem"
5
ca_cert_path = "/tmp/singlestore_bundle.pem"
6

7
response = requests.get(ca_cert_url)
8
with open(ca_cert_path, "wb") as f:
9
    f.write(response.content)
10

11
sql_connection_string = connection_url.replace("singlestoredb", "mysql+pymysql")
12
engine = create_engine(
13
    f"{sql_connection_string}?ssl_ca={ca_cert_path}",
14
    pool_size=10,           # Maximum number of connections in the pool is 10
15
    max_overflow=5,         # Allow up to 5 additional connections (temporary overflow)
16
    pool_timeout=30         # Wait up to 30 seconds for a connection from the pool
17
)
18

19
def execute_query(query: str):
20
    with engine.connect() as connection:
21
        return pd.read_sql_query(query, connection)

1
import pandas as pd
2
import plotly.express as px
3
import plotly.graph_objects as go
4

5
def generate_line_chart(type):
6
    if type == 'Count':
7
        df = execute_query("SELECT order_date, name, COUNT(*) as sales from orders group by order_date, name order by order_date")
8
    elif type == 'Total Value':
9
        df = execute_query("SELECT order_date, name, SUM(amount) as sales from orders group by order_date, name order by order_date")
10
    fig = px.line(df, x='order_date', y='sales', color='name', markers=True,
11
              labels={'sales': 'Sales', 'date': 'Order Date'},
12
              title='Sales Over Time')
13
    fig.update_layout(
14
        font_family="Roboto",
15
        font_color="gray",
16
        title_font_family="Roboto",
17
        title_font_color="Black",
18
        legend_title_font_color="gray"
19
    )
20
    return fig
21

22
line_chart = generate_line_chart("Count")
23
line_chart.show()

1
def generate_pie_chart(date):
2
    df = execute_query(f"SELECT name, SUM(amount) as sales from orders where order_date = '{date}' group by name")
3
    fig = px.pie(df,
4
            names='name',
5
            values='sales',
6
            hover_data=['sales'],
7
            labels={'sales': 'Total Sales', 'name': 'Type'},
8
            title='Total Cost by Item Type')
9
    return fig
10

11
pie_chart = generate_pie_chart("2024-01-01")
12
pie_chart.show()

1
from singlestoredb import apps
2
from dash import Dash, callback, html, dcc, Input, Output
3

4
def get_order_dates():
5
    df = execute_query("select distinct order_date from orders order by order_date")
6
    return df['order_date']
7

8
initial_dates = get_order_dates()
9

10
# Create the Dash App
11
app = Dash("Sales Report", requests_pathname_prefix=os.environ['SINGLESTOREDB_APP_BASE_PATH'])
12

13
# Define the Layout of the Dash App. We will be defining
14
# - A line chart depicting a time series of sales
15
# - A dropdown that shows 'Count'/'Total Value' options, which is used to render different line charts
16
# - An interval counter to keep pinging the Dash App server to get the latest dashboard
17
# - A pie chart depicting the total proportion of sales for a day by item type
18
# - A drop down showing the different dates, which is used to render different pie charts
19

20
app.layout = html.Div([
21
    html.P('Sales Dashboard', style={'textAlign':'center', 'marginTop': 50, 'color': '#8800cc', 'fontSize': '32px', 'fontFamily':'Roboto'} ),
22
    html.Div([
23
        dcc.Interval(
24
            id='interval-component',
25
            interval=2 * 5000,  # Update every second
26
            n_intervals=0  # Start at 0
27
        ),
28
        html.Div(
29
            dcc.Dropdown(['Count', 'Total Value'], 'Count', id='category-dropdown', style={'width': '200px', 'marginRight':'32px' }),
30
            style={'display': 'flex', 'justifyContent': 'flex-end'}
31
        ),
32
        dcc.Loading(
33
            id="loading-spinner",
34
            type="circle",  # Type of spinner: 'circle', 'dot', 'cube', etc.
35
            children=[
36
                dcc.Graph(figure = line_chart, id='line-chart'),
37
            ]
38
        ),
39
        html.Div(
40
            dcc.Dropdown(initial_dates, initial_dates[0], id='date-dropdown', style={'width': '200px', 'marginRight':'32px' }),
41
            style={'display': 'flex', 'justifyContent': 'flex-end'}
42
        ),
43
        dcc.Graph(figure = pie_chart, id='pie-chart'),
44
    ], style={'margin': '32px'})
45
])
46

47
# Define a callback to update the bar chart based on the category dropdown selection
48
@app.callback(
49
    Output("line-chart", "figure"),
50
    Input("category-dropdown", "value"))  # Use the stored value
51
def update_bar_chart(type):
52
    return generate_line_chart(type)
53

54
# Define a callback to update the pie chart based on the date dropdown selection
55
@app.callback(
56
    Output("pie-chart", "figure"),
57
    Input("date-dropdown", "value"),
58
    Input('interval-component', 'n_intervals'))
59
def update_pie_chart(date, n_intervals):
60
    return generate_pie_chart(date)
61

62
# Define a callback to update the date dropdown periodically
63
@app.callback(
64
    Output('date-dropdown', 'options'),
65
    Input('interval-component', 'n_intervals'))
66
def update_date_dropdown(n_intervals):
67
    return get_order_dates()

1
connectionInfo = await apps.run_dashboard_app(app)