Multimodal function best practices

ai_query

The ai_query function calls a large language model for inference and supports image data as multimodal input. The function signature:

ai_query(prompt [, service_name] [, options][, data][, data_type])

The following table describes the parameters.

Named parameter example:

ai_query(
  'your prompt',
  service_name => 'your_model_service_name',
  options => '{"temperature":0.1}',
  data => image_col,
  data_type => 'binary'
)

Supported named parameters: service_name, options, data, and data_type.

ai_embedding_multimodal

The ai_embedding_multimodal function generates an embedding vector from an image for similarity calculations, retrieval, and other tasks. The function signature:

ai_embedding_multimodal(data [, service_name] [, options] [, data_type])

The following table describes the parameters.

Named parameter example:

ai_embedding_multimodal(
  image_col,
  service_name => 'tongyi-embedding-vision-plus',
  data_type => 'binary'
)

Supported named parameters: service_name, options, and data_type.

Use case 1: Process data by image path

If your table contains only image paths pointing to OSS, HDFS, or another Hadoop-compatible file system, use the uri mode to have the function read file content internally.

Example: Identify license plate numbers from images based on their file paths.

SELECT
  path,
  ai_query(
    'Return the license plate number in the image. IMPORTANT: Reply with only the plate number; if the image is too blurry to recognize, reply ''Unable to recognize''.',
    service_name => 'qwen3.5-plus',
    data => path,
    data_type => 'uri'
  ) AS plate_number
FROM (
  SELECT /*+ REPARTITION(10) */ path
  FROM ccpd_1m_1000
) t;

To explicitly control the thinking configuration, use the options parameter:

SELECT
  path,
  ai_query(
    'Return the license plate number in the image. IMPORTANT: Reply with only the plate number; if the image is too blurry to recognize, reply ''Unable to recognize''.',
    service_name => 'qwen3.5-plus',
    data => path,
    data_type => 'uri',
    options => '{"enable_thinking": false}'
  ) AS plate_number
FROM (
  SELECT /*+ REPARTITION(10) */ path
  FROM ccpd_1m_1000
) t;

Use case 2: Process data with binary content

Use the read_files function to read images in bulk from a directory, then pass the returned content column (BINARY type) as multimodal input.

Example: Read images in bulk from an OSS directory and identify license plate numbers.

SELECT
  path,
  ai_query(
    'Return the license plate number in the image. IMPORTANT: Reply with only the plate number.',
    service_name => 'qwen3.5-plus',
    data => content
  ) AS plate_number
FROM read_files(
  'oss://bucket/path/to/images/',
  suffix => 'jpg,jpeg,png'
);

Use case 3: Generate image embedding vectors

Use the ai_embedding_multimodal function to convert images into embedding vectors for tasks such as similarity search and cluster analysis. Both uri mode and binary mode are supported.

Method 1: Generate an embedding vector from an image path (uri mode).

SELECT
  path,
  ai_embedding_multimodal(
    path,
    service_name => 'tongyi-embedding-vision-plus',
    data_type => 'uri'
  ) AS embedding
FROM image_paths_table;

Method 2: Generate an embedding vector from image binary content (binary mode).

SELECT
  path,
  ai_embedding_multimodal(
    content,
    service_name => 'tongyi-embedding-vision-plus'
  ) AS embedding
FROM read_files(
  'oss://bucket/path/to/images/',
  suffix => 'jpg,png'
);

Control partitions for path-based tables

Place the REPARTITION hint in an inner subquery and run the AI function in the outer query. You can also use a CTE or CREATE TEMPORARY VIEW:

-- Method 1: Subquery
SELECT
  path,
  ai_query('Describe the image content', service_name => 'qwen3.5-plus', data => path, data_type => 'uri') AS result
FROM (
  SELECT /*+ REPARTITION(32) */ path FROM large_image_dataset
) t;

-- Method 2: CTE
WITH car_images AS (
  SELECT /*+ REPARTITION(32) */ path FROM ccpd_1m_1000
)
SELECT
  path,
  ai_query('Describe the image content', service_name => 'qwen3.5-plus', data => path, data_type => 'uri') AS result
FROM car_images;

-- Method 3: Temporary view
CREATE OR REPLACE TEMPORARY VIEW car_images AS
SELECT /*+ REPARTITION(32) */ path FROM ccpd_1m_1000;

SELECT
  path,
  ai_query('Describe the image content', service_name => 'qwen3.5-plus', data => path, data_type => 'uri') AS result
FROM car_images;

Control parallelism for read_files

When read_files scans a directory directly, manual REPARTITION is usually unnecessary. To increase parallelism or reduce partition size, adjust spark.sql.files.maxPartitionBytes instead.

SET spark.sql.files.maxPartitionBytes = 134217728;

Filter by suffix during file reading

Filter file types with the suffix parameter to reduce unnecessary file scans and downstream AI requests. To recursively read subdirectories, add recursive => true:

SELECT path, content
FROM read_files('oss://bucket/images/', suffix => 'jpg,jpeg,png');