Get started with Paimon Global Index using DLF Data Discovery

Understand the sample table

This topic primarily uses the following image sample table:

search_samples.berkeley_deepdrive_100k_images

The following table describes commonly used fields.

The sample table has a B-Tree index on weather and an IVF-PQ vector index on image_embedding.

Preview data with visual exploration

Visual exploration lets you view image content, the table schema, and field distributions without writing SQL.

1. Locate search_samples.berkeley_deepdrive_100k_images in Catalogs and go to the table details page.

2. Open the Data Preview tab and select Visual Exploration mode.

3. Select the fields to display, such as image_id, image, event_time, weather, time_of_day, road_scene, and detected_objects.

4. Configure filters, sorting, and the number of rows to return as needed. Then, select a compute resource and run the query.

The result set directly displays information such as the JPEG images, collection times, weather conditions, road scenes, and object types.

View B-Tree and IVF-PQ indexes

Run the following SQL statement to view the Global Indexes generated for the sample table:

SELECT
    index_type,
    index_field_name,
    row_count
FROM `search_samples`.`berkeley_deepdrive_100k_images$table_indexes`
WHERE index_type IN ('btree', 'ivf-pq')
ORDER BY index_type, index_field_name;

The query results should include the following records:

• btree for weather.

• ivf-pq for image_embedding.

• A row_count value greater than 0, which indicates that the index contains queryable data.

Querying the table_indexes system table confirms that index files have been committed, but does not by itself prove that a query used an index.

Query weather data with a B-Tree index

The following SQL statement queries driving scenes in rainy weather. The B-Tree index on weather can reduce unnecessary data scans.

SELECT
    image_id,
    image,
    event_time,
    weather,
    time_of_day,
    road_scene,
    detected_objects
FROM search_samples.berkeley_deepdrive_100k_images
WHERE weather = 'rainy'
LIMIT 20;

You can change the weather condition to view driving scenes in other weather conditions.

Find similar driving scenes by image

Image search lets you quickly verify image retrieval results without preparing a query vector or building a vector search service. This topic provides an urban road image that contains a motorcycle as the reference image.

Reference image: motorcycle__000f8d37-d4c09a0f.jpg

1. Locate search_samples.berkeley_deepdrive_100k_images in Catalogs and go to the table details page.

2. Open the Data Preview tab and select Image Search mode.

3. Upload the reference image provided in this topic.

4. Set the number of results to return in Top N, select a compute resource, and run the query.

The system calculates the image vector required for the query and uses the IVF-PQ index on image_embedding to return similar driving scenes. The results are displayed in descending order of similarity. You can directly view the images and information such as their collection times, weather conditions, and road scenes.

Query similar images with SQL

The following SQL statement first reads the image_embedding of an existing image from the sample table by image ID. It then uses CROSS JOIN LATERAL to pass the vector directly to vector_search, so you do not need to enter or copy a 768-dimensional vector.

WITH query_image AS (
    SELECT image_id, image_embedding
    FROM search_samples.berkeley_deepdrive_100k_images
    WHERE image_id = 'c6459d6c-2a477f42'
)
SELECT
    q.image_id AS query_image_id,
    r.image_id,
    r.image,
    r.event_time,
    r.weather,
    r.time_of_day,
    r.road_scene,
    r.detected_objects
FROM query_image q
CROSS JOIN LATERAL vector_search(
    'dlf_samples.search_samples.berkeley_deepdrive_100k_images',
    'image_embedding',
    q.image_embedding,
    10
) AS r;

Perform hybrid search with B-Tree and IVF-PQ indexes

Hybrid search also directly uses the image_embedding of an existing image in the sample table. It uses r.weather = 'rainy' to push down the B-Tree condition before vector Top-K and returns the top 10 similar images from driving scenes in rainy weather.

WITH query_image AS (
    SELECT image_id, image_embedding
    FROM search_samples.berkeley_deepdrive_100k_images
    WHERE image_id = 'c6459d6c-2a477f42'
)
SELECT
    q.image_id AS query_image_id,
    r.image_id,
    r.image,
    r.event_time,
    r.weather,
    r.time_of_day,
    r.road_scene,
    r.detected_objects
FROM query_image q
CROSS JOIN LATERAL vector_search(
    'dlf_samples.search_samples.berkeley_deepdrive_100k_images',
    'image_embedding',
    q.image_embedding,
    10
) AS r
WHERE r.weather = 'rainy';

This query uses:

• The B-Tree index on weather to filter for rainy weather.

• The IVF-PQ index on image_embedding to retrieve similar images.

The query applies the weather condition before vector Top-K and returns similar images captured in rainy weather.

Prepare data

Use the table creation and data write statements in Access DLF using PyPaimon to prepare a Paimon table.

The sample table must contain at least the time_of_day field and the detected_objects ARRAY<STRING> field.

Build indexes

Follow the instructions in Build Paimon global indexes to create a Bitmap index on time_of_day and a multivalue index on detected_objects (ARRAY<STRING>). Wait until the indexes are generated and committed.

Then, on the Data Discovery page, select the actual catalog that contains the business table and a compute resource. Replace my_db.my_table in the following SQL statements with the actual table name. Run multivalue filters in SQL mode.

Check the indexes

SELECT index_type, index_field_name, SUM(row_count) AS indexed_rows
FROM `my_db`.`my_table$table_indexes`
WHERE index_type IN ('bitmap', 'multivalue')
GROUP BY index_type, index_field_name;

The results must include bitmap for time_of_day and multivalue for detected_objects. These results confirm that index files have been committed, but do not by themselves prove that a query used the indexes.

Query examples

Filter nighttime images by a low-cardinality field:

SELECT image_id, event_time, time_of_day, detected_objects
FROM my_db.my_table
WHERE time_of_day = 'night'
LIMIT 20;

Filter images whose detected_objects array contains the complete element car:

SELECT image_id, event_time, time_of_day, detected_objects
FROM my_db.my_table
WHERE ARRAY_CONTAINS(detected_objects, 'car')
LIMIT 20;

Combine a scalar condition with an array element condition:

SELECT image_id, event_time, time_of_day, detected_objects
FROM my_db.my_table
WHERE time_of_day = 'night'
  AND ARRAY_CONTAINS(detected_objects, 'car')
LIMIT 20;

Verify the query results

For each result set, verify that time_of_day is night and that detected_objects contains the element car, as applicable. The combined query must satisfy both conditions. ARRAY_CONTAINS matches a complete array element, not a substring of a string.