E-MapReduce:StarRocks AI quick start: Autonomous driving training data mining

Background information

Iterating on an autonomous driving model requires large volumes of training data for specific scenarios. The road images collected by vehicles usually accumulate in object storage. You need to mine target scenarios by conditions such as weather, lighting, and road type, and find images that are visually similar to hard samples to strengthen weak areas. Images are unstructured data, so semantic similarity cannot be expressed by equality or range conditions. The images must first be encoded into feature vectors by an embedding model, after which similar objects can be found based on vector distance.

EMR Serverless StarRocks consolidates this process into a single SQL system:

• An Object Table maps the images in OSS and their metadata as a table, so you do not need to download the images to a local device or import them into an internal table.

• AI functions generate scene descriptions, extract structured metadata, and vectorize images inside the database, so you do not need to deploy a separate inference service.

• Hybrid search combines structured filtering with vector similarity calculation in a single SQL statement to select the training set.

• A DLF catalog writes the results to a Paimon lake table. Downstream engines such as Spark and Flink can consume the data directly, without exporting it again.

Prerequisites

• An EMR Serverless StarRocks instance of version 3.5.16-2.2.0 or later is created.

• AI functions are activated. For more information, see AI Function.

• DLF is activated, and you have read and write permissions on the Paimon catalog.

• The road images to process are uploaded to OSS. The examples in this topic use the front camera images in CAM_FRONT 2.zip from the public nuScenes dataset. You can also use your own data.

Workflow overview

The complete process consists of five steps. The following table describes the operation, key capability, and output of each step.

The original images always remain in OSS. The Object Table stores only metadata and a controlled file reference. After you pass file to an AI function, the system generates the signed address automatically, so no AccessKey information appears in the SQL. After the labeling results land in the Paimon lake table, the vectors and structured metadata are stored in the same table, which lets you perform semantic similarity search and exact filtering in a single SQL statement.

Step 1: Map OSS images as an Object Table

Execute the following statements to create and refresh an Object Table. After that, you can query the image list with SQL.

CREATE DATABASE IF NOT EXISTS ad_data_mining;
USE ad_data_mining;

CREATE OBJECT TABLE camfront_objects PROPERTIES (
  "path" = "oss://<BUCKET_NAME>/v1.0-mini/sweeps/CAM_FRONT",
  "aliyun.oss.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
  "aliyun.oss.public_endpoint" = "oss-cn-hangzhou.aliyuncs.com",
  "aliyun.oss.access_key" = "<YOUR_ACCESS_KEY_ID>",
  "aliyun.oss.secret_key" = "<YOUR_ACCESS_KEY_SECRET>",
  "recursive" = "true",
  "file_pattern" = ".*\\.(jpg|jpeg|png)$"
);

REFRESH OBJECT TABLE camfront_objects;

SELECT object_uri, size, content_type, last_modified
FROM camfront_objects ORDER BY last_modified DESC LIMIT 5;

An Object Table contains 8 fixed columns, which are described in the following table.

Step 2: Create a DLF catalog and a Paimon result table

Create a DLF catalog so that StarRocks can read from and write to Paimon lake tables directly.

DROP CATALOG IF EXISTS `dlf_catalog_demo`;
CREATE EXTERNAL CATALOG `dlf_catalog_demo` PROPERTIES (
  "type" = "paimon",
  "uri" = "http://cn-hangzhou-vpc.dlf.aliyuncs.com",
  "paimon.catalog.type" = "rest",
  "paimon.catalog.warehouse" = "<WAREHOUSE_NAME>",
  "token.provider" = "dlf"
);

Create a result table in this catalog. The table uses the lance file format for compact storage of vector data, and sets merge-engine to partial-update so that only the label column is written back later.

CREATE TABLE dlf_catalog_demo.`default`.camfront_ai_enriched (
  id                  STRING,
  object_uri          STRING,
  etag                STRING,
  file_name           STRING,
  caption             STRING,
  metadata            STRING,           -- Structured metadata JSON (weather, lighting, road type, objects, risks)
  vector              ARRAY<FLOAT>,     -- Image feature vector. The dimension depends on the model
  weather             STRING,
  lighting            STRING,
  road_type           STRING,
  is_cloudy_candidate INT,
  is_daytime          INT,
  is_nighttime        INT,
  scene_type          STRING,
  dataset_tag         INT               -- Mining result label: 0 = not selected, 1 = cloudy, 2 = cloudy with pedestrians
) ENGINE = paimon
PROPERTIES (
  "file.format" = "lance",
  "primary-key" = "id",
  "merge-engine" = "partial-update",
  "bucket" = "1",
  "snapshot.time-retained" = "3 d"
);

Step 3: AI labeling and image vectorization

Use ai_complete to generate scene descriptions and structured metadata, use ai_embed_multimodal to generate image feature vectors, and write the results to the Paimon table. The file descriptor carried by each row of the Object Table can be passed directly to an AI function.

INSERT INTO dlf_catalog_demo.`default`.camfront_ai_enriched (
  id, object_uri, etag, file_name, caption, metadata, vector,
  weather, lighting, road_type, is_cloudy_candidate, is_daytime, is_nighttime, scene_type, dataset_tag
)
WITH enriched AS (
  SELECT
    o.object_uri, o.etag,
    regexp_extract(o.object_uri, '.*/(.+)$', 1) AS file_name,
    -- Generate the scene description
    ai_complete('qwen3.6-plus',
      'You are an autonomous driving data analysis assistant. Based on the input road scene image, output 1-2 sentences in English describing the scene, highlighting weather, lighting, road type, key traffic participants and potential risks. Output only the descriptive text.',
      o.file, 'image') AS caption,
    -- Extract structured metadata
    ai_complete('qwen3.6-plus',
      'You are an autonomous driving data analysis assistant. Output a JSON object: {"weather":"sunny/cloudy/rainy/snowy/foggy/other","lighting":"daytime/nighttime/dusk/tunnel/other","road_type":"urban/expressway/rural/intersection/ramp/parking_lot/other","objects":[...],"risks":[...]}. Values must exactly match the enums. Output only valid JSON.',
      o.file, 'image') AS metadata,
    -- Generate the image feature vector
    ai_embed_multimodal(o.file, 'image') AS vector
  FROM camfront_objects o
  -- Filter out processed images. This must run before the AI functions are called
  LEFT ANTI JOIN dlf_catalog_demo.`default`.camfront_ai_enriched e
    ON o.object_uri = e.object_uri AND o.etag = e.etag
  WHERE object_file_is_image(o.file)
)
SELECT
  uuid() AS id,
  object_uri, etag, file_name, caption, metadata, vector,
  get_json_string(metadata, '$.weather')   AS weather,
  get_json_string(metadata, '$.lighting')  AS lighting,
  get_json_string(metadata, '$.road_type') AS road_type,
  IF(get_json_string(metadata, '$.weather')  = 'cloudy',   1, 0) AS is_cloudy_candidate,
  IF(get_json_string(metadata, '$.lighting') = 'daytime',  1, 0) AS is_daytime,
  IF(get_json_string(metadata, '$.lighting') = 'nighttime',1, 0) AS is_nighttime,
  concat_ws('_', get_json_string(metadata, '$.lighting'), get_json_string(metadata, '$.weather')) AS scene_type,
  0 AS dataset_tag
FROM enriched;

After the write operation is complete, execute the following statement to verify that the vectors are not empty and that the dimension is correct.

SELECT count(*) AS total,
       sum(IF(vector IS NOT NULL AND array_length(vector) = 1152, 1, 0)) AS valid_vectors
FROM dlf_catalog_demo.`default`.camfront_ai_enriched;

Step 4: Inspect data quality and select candidates with hybrid search

SELECT weather, COUNT(*) AS cnt,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM dlf_catalog_demo.`default`.camfront_ai_enriched
GROUP BY weather ORDER BY cnt DESC;

-- Strategy 1: Find images in cloudy scenes with similarity greater than 0.6 to the seed image
WITH seed AS (
  SELECT vector AS seed_vec FROM dlf_catalog_demo.`default`.camfront_ai_enriched
  WHERE is_cloudy_candidate = 1 ORDER BY id LIMIT 1
)
SELECT t.object_uri, t.file_name, t.caption, t.weather, t.lighting, t.road_type,
       cosine_similarity(t.vector, seed.seed_vec) AS similarity
FROM dlf_catalog_demo.`default`.camfront_ai_enriched t CROSS JOIN seed
WHERE t.weather = 'cloudy'
  AND cosine_similarity(t.vector, seed.seed_vec) > 0.6
ORDER BY similarity DESC;

-- Strategy 2: Find high-risk cloudy scenes that contain pedestrians, with similarity greater than 0.8
WITH seed AS (
  SELECT vector AS seed_vec FROM dlf_catalog_demo.`default`.camfront_ai_enriched
  WHERE is_cloudy_candidate = 1 ORDER BY id LIMIT 1
)
SELECT t.object_uri, t.file_name, t.caption,
       json_query(t.metadata, '$.objects') AS objects,
       cosine_similarity(t.vector, seed.seed_vec) AS similarity
FROM dlf_catalog_demo.`default`.camfront_ai_enriched t CROSS JOIN seed
WHERE t.weather = 'cloudy'
  AND t.metadata LIKE '%pedestrian%'
  AND cosine_similarity(t.vector, seed.seed_vec) > 0.8
ORDER BY similarity DESC;

Step 5: Write labels back to the lake table

After you confirm the selection, write the labels to an intermediate table and preview them for verification. Then use Paimon partial update to write back only the dataset_tag column and keep the other columns unchanged.

-- 1. Create the intermediate table
DROP TABLE IF EXISTS dlf_catalog_demo.`default`.dataset_search_tags;
CREATE TABLE dlf_catalog_demo.`default`.dataset_search_tags (
  id           VARCHAR(65533),
  dataset_type INT
);

-- 2. Calculate labels: cloudy with pedestrians (similarity greater than 0.7) is 2, cloudy only (similarity greater than 0.4) is 1, and others are 0
INSERT INTO dlf_catalog_demo.`default`.dataset_search_tags
SELECT id,
  CASE
    WHEN id IN (
      SELECT t.id
      FROM dlf_catalog_demo.`default`.camfront_ai_enriched t,
           (SELECT vector AS seed_vec FROM dlf_catalog_demo.`default`.camfront_ai_enriched
            WHERE is_cloudy_candidate = 1 ORDER BY id LIMIT 1) s
      WHERE t.weather = 'cloudy' AND t.metadata LIKE '%pedestrian%'
        AND cosine_similarity(t.vector, s.seed_vec) > 0.7
    ) THEN 2
    WHEN id IN (
      SELECT t.id
      FROM dlf_catalog_demo.`default`.camfront_ai_enriched t,
           (SELECT vector AS seed_vec FROM dlf_catalog_demo.`default`.camfront_ai_enriched
            WHERE is_cloudy_candidate = 1 ORDER BY id LIMIT 1) s
      WHERE t.weather = 'cloudy'
        AND cosine_similarity(t.vector, s.seed_vec) > 0.4
    ) THEN 1
    ELSE 0
  END AS dataset_type
FROM dlf_catalog_demo.`default`.camfront_ai_enriched;
REFRESH EXTERNAL TABLE dlf_catalog_demo.`default`.dataset_search_tags;

-- 3. Preview the label distribution
SELECT dataset_type, COUNT(*) AS cnt
FROM dlf_catalog_demo.`default`.dataset_search_tags
GROUP BY dataset_type ORDER BY dataset_type;

-- 4. Write back the labels. Only the dataset_tag column is updated
INSERT INTO dlf_catalog_demo.`default`.camfront_ai_enriched (id, dataset_tag)
SELECT id, dataset_type
FROM dlf_catalog_demo.`default`.dataset_search_tags;
REFRESH EXTERNAL TABLE dlf_catalog_demo.`default`.camfront_ai_enriched;

-- 5. View the final label distribution in the source table
SELECT dataset_tag, COUNT(*) AS cnt
FROM dlf_catalog_demo.`default`.camfront_ai_enriched
GROUP BY dataset_tag ORDER BY dataset_tag;

After the write-back is complete, the downstream training pipeline can read the data directly with WHERE dataset_tag = 1. The data stays in the lake table, so no repeated export is needed.

Limits

• An Object Table supports only manual REFRESH to refresh the object list. Automatic periodic refresh is not supported, so you must trigger the refresh through an external scheduling system.

• When you call ai_complete to process a URL or multiple content inputs, you must specify the model name explicitly. Passing the video modality currently returns NULL, so you must extract frames externally and process them as the image modality.

• The vector dimension is determined by the embedding model. The vector column defined when you create the table and the subsequent validation statements must match the actual output dimension of the model.

• If no index is created on the vector column, the search calculates distances row by row. When the data volume is large, the time and resource overhead increase significantly.

• AI function calls are billed by token usage. Incremental filtering must be completed before the AI functions are called. Otherwise, duplicate calls incur extra fees.

• Paimon partial update relies on the table property merge-engine = partial-update and the primary key definition. If they are not configured correctly when you create the table, you cannot update only the specified columns.

What to do next

• Join the result table with structured tables such as collection batches, vehicles, and road sections to integrate scenario mining into your existing closed-loop data scheduling process.

• As the data volume grows, create an HNSW or IVF index on the Paimon vector column to improve search performance.

• The result data stays in the Paimon lake table, where it can be shared for Spark training, Flink real-time computing, and ad hoc queries.