Model Training and Online Inference

Choose a path based on what you already have:

• Existing HTTP model service: use external below, without SQLRec training, export, or inference-container deployment.
• Train and deploy with SQLRec: prepare the service environment, then follow the trained-model lifecycle.
• Hugging Face model: download a Hub snapshot and create a service directly; see backend differences.

Connect an Existing HTTP Model Service

Prepare an inference URL reachable from SQLRec and declare the service's input and output fields:

CREATE MODEL external_rank_model (
  user_id BIGINT,
  item_id BIGINT,
  category VARCHAR,
  price DOUBLE
) WITH (
  'model' = 'external',
  'output_columns' = 'score:FLOAT'
);

CREATE SERVICE external_rank_service
ON MODEL external_rank_model
WITH (
  'url' = 'http://rank-service:8080/predict'
);

In local file mode, save these definitions separately as model/external_rank_model.sql and service/external_rank_service.sql under the complete SQL_SCHEMA_DIR, then restart. See the Docker guide for loading files. In remote metadata mode, execute the definitions through Beeline or JDBC.

Prepare a cached rank_input table containing those fields. Declare the result schema with an empty table, then call the service:

CACHE TABLE external_rank_output AS
SELECT *, CAST(NULL AS FLOAT) AS score FROM rank_input LIMIT 0;

CACHE TABLE result_table AS
CALL call_service('external_rank_service', rank_input)
LIKE external_rank_output;

The result keeps the input columns and appends score. Connecting an existing service does not require a Kubernetes training environment; the inference service must implement the protocol below.

HTTP Inference Protocol (Service Providers)

The service accepts POST requests. A single-table call sends an array of JSON objects containing only Model input fields:

[
  {"user_id": 1, "item_id": 101, "category": "phone", "price": 3999.0},
  {"user_id": 1, "item_id": 102, "category": "tablet", "price": 2999.0}
]

The response maps each output field to an array with the same row count and order as the input:

{"score": [0.85, 0.72]}

For the User-Item call below, the request is column-oriented. User fields are single-element arrays; Item fields follow item row order:

{
  "user_id": [1],
  "item_id": [101, 102],
  "category": ["phone", "tablet"],
  "price": [3999.0, 2999.0]
}

Choose a Backend

Model type	Data source	TRAIN MODEL	EXPORT MODEL	Service checkpoint
tzrec Wide & Deep / DSSM	SQL table	Train	Required	export
LightGBM / XGBoost / CatBoost	SQL table	Train	Required	export
Hugging Face Transformers	Hub snapshot	Download, without an ON table	Unsupported	origin
external	Existing HTTP service	Unsupported	Unsupported	None

See Built-in Models for options.

Core Objects

Object	Purpose
Model	Stores the model type, input fields, and default configuration
Checkpoint	A versioned result produced by training, downloading, or export
Service	Deploys a selected checkpoint as an online endpoint

Checkpoint types are:

• origin: the original training or download result;
• export: an artifact produced by EXPORT MODEL for a tzrec or GBDT service.

Checkpoint names are user-defined version identifiers. Use a traceable value such as a date or release ID, and do not reuse a name for different data or settings.

Lifecycle for a Trained Model

Training, export, and self-hosted serving require the full service environment, including Kubernetes and model storage. The Docker demo does not provide these components. Prepare an accessible training_sample table with fields compatible with the model before running the statements.

1. Create the Model

CREATE MODEL rank_model (
  user_id BIGINT,
  item_id BIGINT,
  category VARCHAR,
  price DOUBLE,
  is_click INT
) WITH (
  'model' = 'tzrec.wide_and_deep',
  'label_columns' = 'is_click'
);

The field list is the model's input contract. Matching columns in training and inference data must have compatible names and types.

2. Train an Origin Checkpoint

TRAIN MODEL rank_model CHECKPOINT = '2026_09_13'
ON training_sample
WHERE dt = '2026-09-13'
WITH (
  'num_epochs' = '1',
  'batch_size' = '8192'
);

TRAIN MODEL submits a Kubernetes training job. Successful submission does not mean training is finished. Check status before exporting:

SHOW CHECKPOINTS rank_model;

DESCRIBE FORMATTED MODEL rank_model
CHECKPOINT = '2026_09_13';

3. Export the Model

EXPORT MODEL rank_model CHECKPOINT = '2026_09_13'
ON training_sample;

The backend determines the export work. A normal single model produces an export checkpoint named <origin_checkpoint>_export. DSSM produces separate user- and item-tower artifacts; use SHOW CHECKPOINTS to get their exact names.

4. Create an Online Service

CREATE SERVICE rank_service
ON MODEL rank_model
CHECKPOINT = '2026_09_13_export'
WITH (
  'replicas' = '1',
  'pod_cpu_cores' = '1',
  'pod_memory' = '2Gi'
);

Inspect the definition and status after creating the service:

SHOW SERVICES;

DESCRIBE FORMATTED SERVICE rank_service;

Call a Model Service

call_service appends model outputs to the input data. Declare input fields in the Model and supply matching fields in the input table.

Row-Oriented Input

CACHE TABLE rank_input AS
SELECT user_id, item_id, category, price FROM candidate_item;

CACHE TABLE rank_output AS
SELECT *, CAST(NULL AS FLOAT) AS probs FROM rank_input LIMIT 0;

CACHE TABLE ranked_item AS
CALL call_service('rank_service', rank_input) LIKE rank_output;

This Wide & Deep example keeps input columns and appends probs. See Built-in Models for other output fields.

User-Item Input

For ranking, pass one user row and multiple item rows separately to avoid repeating user features:

CACHE TABLE item_rank_output AS
SELECT *, CAST(NULL AS FLOAT) AS probs FROM item_candidates LIMIT 0;

CACHE TABLE ranked_item AS
CALL call_service('rank_service', user_features, item_candidates)
LIKE item_rank_output;

• User must contain exactly one row; Item may contain many.
• Duplicate input field names are read from User, and remaining model fields from Item.
• The result keeps Item fields and appends model outputs.
• Empty Item input returns a correctly typed empty table without an HTTP request.

See the HTTP inference protocol for external service request and response formats.

How Hugging Face Differs

For the Hugging Face backend, TRAIN MODEL downloads a selected Hub revision instead of training from a SQL table, so it has no ON data_source clause:

TRAIN MODEL text_embedding_model CHECKPOINT = 'v1' WITH (
  'revision' = 'main'
);

The resulting origin checkpoint can be used directly by a Service, and EXPORT MODEL is not supported. See Built-in Models for the complete task and options.

Troubleshooting

Service creation reports the wrong checkpoint type

Use the type required by the backend: export for tzrec and GBDT, origin for Hugging Face, and no checkpoint for external.

Training fields do not match

Compare DESCRIBE MODEL with the training table. Check field names and types, label columns, and backend-specific user/item feature settings.

call_service returns the wrong row count

Make sure every output array has the same length as the input and that output field names match those defined by the model backend.