From Data to SQL-Callable Model: An End-to-End Tour of Gnok Notebooks
Gnok Studio now has an in-product Python notebook. You write Python against
governed Gnok data, train a model, and register it so it's callable from SQL
with ML_PREDICT — the whole train→serve loop, in one place, with no data
movement and no separate notebook server to babysit.
This post builds a complete example end to end: read data, explore it, engineer features, train a scikit-learn pipeline, register it, and score new rows from SQL — then let Gnok AI write a cell for us.
The gnok helper
Every notebook kernel comes with a pre-installed gnok helper. You don't
pip install it and you don't pass a host or token — it auto-connects as the
signed-in user, so row-level security, masking, and vended credentials apply
exactly as they do everywhere else in Gnok.
import gnok
from gnok import ml
# A query → pandas
df = gnok.sql("SELECT 1 AS x, 2 AS y").fetch_df()
gnokwraps the standalonepygnokclient and adds agnok.mlnamespace. Full reference: Python Notebooks.
1. Read the data
We'll predict whether a customer will churn. Start by pulling features into a
DataFrame — read_table for a whole table, or gnok.sql(...) for anything
shaped:
customers = gnok.sql("""
SELECT
tenure_months,
monthly_charges,
total_charges,
support_tickets,
churned -- 1 = churned, 0 = retained
FROM analytics.customers
WHERE total_charges IS NOT NULL
""").fetch_df()
print(customers.shape)
customers.head()
2. Explore
It's a normal pandas DataFrame, so all your usual tools work:
customers.describe()
customers.groupby("churned")[["tenure_months", "monthly_charges"]].mean()
import matplotlib.pyplot as plt
customers.boxplot(column="monthly_charges", by="churned")
plt.title("Monthly charges by churn")
plt.show()
Plots render inline in the cell output.
3. Train a model
A scikit-learn pipeline — scale the features, then a gradient-boosting model.
We use a regressor so the model outputs a continuous churn propensity
(0–1), which maps cleanly to a SQL FLOAT:
import numpy as np
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import StandardScaler
from sklearn.ensemble import GradientBoostingRegressor
from sklearn.model_selection import train_test_split
FEATURES = ["tenure_months", "monthly_charges", "total_charges", "support_tickets"]
X = customers[FEATURES].to_numpy("float32")
y = customers["churned"].to_numpy("float32")
X_tr, X_te, y_tr, y_te = train_test_split(X, y, test_size=0.2, random_state=0)
model = Pipeline([
("scale", StandardScaler()),
("gbr", GradientBoostingRegressor(n_estimators=200, max_depth=3, random_state=0)),
]).fit(X_tr, y_tr)
print("R2 (holdout):", round(model.score(X_te, y_te), 3))
4. Register it for SQL
One call exports the fitted pipeline to ONNX, uploads it to an internal stage,
and runs CREATE OR REPLACE MODEL — the model is now a SQL function:
ml.register_model(model, "analytics.imported.churn_propensity_v1", X)
A few things worth knowing (the helper handles the rest):
- Use a fully-qualified name (
catalog.schema.model). The notebook has no default schema, so an unqualified name would land somewhereML_PREDICTcan't find. - The input signature
(FLOAT × 4)is inferred fromX— one float per feature, in column order. register_model(..., returns="FLOAT")is the default; a classifier's label output is an integer, so usereturns="BIGINT"there.
5. Serve it from SQL
Now anyone — a dashboard, a scheduled query, a BI tool — can score rows. Note
the CAST(... AS REAL): bare decimal literals are typed as DECIMAL, which the
ONNX input rejects.
SELECT ML_PREDICT(
'analytics.imported.churn_propensity_v1',
CAST(tenure_months AS REAL),
CAST(monthly_charges AS REAL),
CAST(total_charges AS REAL),
CAST(support_tickets AS REAL)
) AS churn_score
FROM analytics.customers
ORDER BY churn_score DESC
LIMIT 20;
Materialize the scores for the rest of the business:
CREATE OR REPLACE TABLE analytics.churn_scores AS
SELECT
customer_id,
ML_PREDICT('analytics.imported.churn_propensity_v1',
CAST(tenure_months AS REAL), CAST(monthly_charges AS REAL),
CAST(total_charges AS REAL), CAST(support_tickets AS REAL)) AS churn_score
FROM analytics.customers;
…or score back from the notebook and write the result:
scored = gnok.sql("SELECT customer_id, ... FROM analytics.churn_scores").fetch_df()
gnok.write(scored.query("churn_score > 0.7"), "analytics.churn_watchlist")
That's the whole loop: the model trained in Python is now ordinary SQL the rest of the platform can call — no model server, no export/import dance.
6. Let Gnok AI write the cell
Open the ✨ Gnok AI panel and describe what you want:
"Read 1,000 rows of analytics.customers into a DataFrame and plot churn rate by tenure bucket."
It returns a ready-to-run cell — click + Add as cell and run it. The
assistant knows the gnok helper, so it reaches for gnok.sql / read_table
rather than generic boilerplate. When a cell errors, ✨ Fix sends the
traceback back to the model and proposes a corrected cell.
Recap
read (gnok.sql / read_table) → explore (pandas) → train (sklearn)
→ register (gnok.ml.register_model) → serve (ML_PREDICT in SQL)
No pip install, no connection string, no copying data to a separate notebook server. Dig into the
Python Notebooks reference for the full gnok and
gnok.ml API, and ONNX Inference for how
registered models execute.