jingningzhang1

CREATE TABLE IF NOT EXISTS plotly_iot_dashboard.bronze_sensors
(
Id BIGINT GENERATED BY DEFAULT AS IDENTITY,
device_id INT,
user_id INT,
calories_burnt DECIMAL(10,2), 
miles_walked DECIMAL(10,2), 
num_steps DECIMAL(10,2), 
timestamp TIMESTAMP,
value STRING

jingningzhang1

-- DBTITLE 1,Incrementally Ingest Source Data from Raw Files
COPY INTO plotly_iot_dashboard.bronze_sensors
FROM (SELECT 
id::bigint AS Id,
device_id::integer AS device_id,
user_id::integer AS user_id,
calories_burnt::decimal(10,2) AS calories_burnt, 
miles_walked::decimal(10,2) AS miles_walked, 
num_steps::decimal(10,2) AS num_steps, 
timestamp::timestamp AS timestamp,

jingningzhang1

-- DBTITLE 1,Perform Upserts - Device Data
MERGE INTO plotly_iot_dashboard.silver_sensors AS target
USING (SELECT Id::integer,
              device_id::integer,
              user_id::integer,
              calories_burnt::decimal,
              miles_walked::decimal,
              num_steps::decimal,
              timestamp::timestamp,
              value::string

jingningzhang1

-- COMMAND ----------

SELECT * FROM plotly_iot_dashboard.silver_sensors;

jingningzhang1

import dash
from dash import dcc, html, Input, Output
import dash_mantine_components as dmc
import plotly.express as px
from utils import dbx_utils, chart_utils
from skimage import io

app = dash.Dash(__name__)
app.title = "dash-dbx"
server = app.server  # expose server variable for Procfile

jingningzhang1

DB_NAME = "plotly_iot_dashboard"
USER_TABLE = "silver_users"
DEVICE_TABLE = "silver_sensors"

jingningzhang1

from databricks import sql

jingningzhang1

def get_listofusers():
    connection3 = sql.connect(
        server_hostname=SERVER_HOSTNAME,
        http_path=HTTP_PATH,
        access_token=ACCESS_TOKEN,
    )
    cursor3 = connection3.cursor()
    cursor3.execute(
        f"SELECT DISTINCT userid FROM {DB_NAME}.{USER_TABLE} ORDER BY userid ASC"
    )

jingningzhang1

def get_scatter_data(xaxis, comp):
    """
    Fetches specified columns and an aggregated column from the silver_users table, returns it as a pandas dataframe
    Returns
    -------
    df : pandas dataframe
        basic query of data from Databricks as a pandas dataframe
    """
    connection0 = sql.connect(
        server_hostname=SERVER_HOSTNAME,

jingningzhang1

def generate_scatter(df, xaxis, comp):
    axis_labels = {
        "age": "Age (years)",
        "height": "Height (inches)",
        "weight": "Weight (lbs)",
    }
    fig = px.scatter(
        df,
        x=xaxis,
        y="risk",