Creating from a Template

Step 1: Open the Hamburger Menu in the upper right corner.

Step 2: Select the Dev Panel option from the menu.

Please note, this option is only accessible to users with a developer role or higher.

Step 3: Locate and select the User Defined Notebook API. This is where the list of user-defined functions is housed.

In this environment, no existing or saved user-defined functions are present. It provides instructions on how to access ODBC within the notebook and how to execute it.

Step 4: Download the template file. This file, a Jupyter notebook, includes an example of creating a user-defined function.

This guide explains how to download a Jupyter notebook file, modify it, and upload it as a user-defined function.

Downloading and Accessing the Notebook File

Step 1: Download the notebook file as a template.

Step 2: Open the terminal and start your Jupyter notebook.

Step 3: Navigate to the location of the downloaded file. For this example, the file is in the Downloads folder.

Step 1: After opening the template, you will notice 3 distinct sections. 

Section 1: Initialize Parameters

The first section initializes parameters and contains a parameter tag. These can be modified by passing in new values to the API, such as date parameters or different machines.  In the example below, a, b, and c are all parameters initialized in the first cell.

Section 2: Business Logic

The next section contains the business logic.  This is a simple example and in practice this can take up as many cells as is necessary.

Section 3: API Response

The final cell returns the output via the API response. 

Step 1: Modify the template as needed.

Step 2: Execute the code to see the expected result. In this example, without passing any parameters, the API response should be 2.

Step 3: Save your notebook.

Step 1: Return to the developer page and choose the saved file from the appropriate location.

Step 2: Provide a name for the UDF. This will be the name of the API that's being called.

Step 3: Provide a description for the function.

Step 4: Select "upload". A confirmation message will appear indicating a successful upload.

The new function is now visible, complete with its description and the time it was last saved, and ready to run!

1. After uploading, an api request can be POSTed to {environment_name}.sightmachine.io/v1/udf/task/async with the following body format (parameters are optional):

   
   

   {
       "name": <notebook name>,
       "parameters": {
           <param>: <value>,
           ...
       }
   }

2. You will receive acknowledgement of a successful submission along with a task_id. You can then poll for your results at {environment_name}.sightmachine.io/v1/udf/task/async/{task_id}

Data Quality

The following UDF analyzes a given machine_type to find fields which have recently been all null or only a single static non-null value.

Default Parameters

The first executable cell of the notebook, containing the parameters tag, sets the default parameter values for the API.

API_KEY = ""
API_SECRET = ""
machine_type = ""
days = 5
include_last_non_null = False
schema_name = ""

Imports

Import packages required for your UDF.

import sqlalchemy as sqla
from smsdk import client
from IPython.display import JSON

Validate Parameters

Validate the parameters including machine_type.

tenant = ""
cli = client.Client(tenant)
cli.login('apikey', secret_id=API_SECRET, key_id=API_KEY)
cli.select_db_schema(schema_name=schema_name)
machine_types = cli.get_machine_type_names(clean_strings_out=False)
if machine_type not in machine_types:
    raise RuntimeError(f"machine_type not found: {machine_type}")

Setup ODBC Connection

Connect to the DB using the provided API Key and Secret.

connection_string = f"postgresql+psycopg2://{API_KEY}:{API_SECRET}@pgbouncer:6432/tenant_storage"
engine = sqla.create_engine(connection_string)

Perform Analysis

Run the query and identify columns with null and static fields.

table_name = f"cycle_{machine_type.lower()}"
query = sqla.text(f"""
SELECT column_analysis(:s_name, :t_name, :days) AS column_analysis, count(*) AS total_rows_checked
FROM {engine.dialect.identifier_preparer.quote_schema(schema_name)}.{engine.dialect.identifier_preparer.quote_identifier(table_name)}
WHERE "Cycle End Time" > NOW() - INTERVAL ':days days'
""")
with engine.connect() as conn:
    res = conn.execute(query, s_name=schema_name, t_name=table_name, days=days)
    data = [dict(row) for row in res]

null_fields = []
static_fields = []
column_analysis = data[0]["column_analysis"][0]
for column_name, distinct_count in column_analysis.items():
    if distinct_count == 0:
        null_fields.append(column_name)
    elif distinct_count == 1:
        static_fields.append(column_name)

Format Response

The last executable cell of the notebook contains the response of the UDF.

response = {
    schema_name: {
        machine_type: {
            "days": days,
            "total_rows_checked": data[0]["total_rows_checked"],
            "null_field_count": len(null_fields),
            "null_fields": null_fields,
            "static_field_count": len(static_fields),
            "static_fields": static_fields
        }
    }
}
JSON(response)