Video functions

Usage notes

In SQL deployments on the platform, video functions are provided by the built-in python module, and no additional dependencies are required in the deployment. Before you use a function provided by the python module, load the module first and then enable the python and core modules:

LOAD MODULE python;
USE MODULES python, core;

Basic invocation

LOAD MODULE python;
USE MODULES python, core;

-- Read the video metadata.
SELECT id, VIDEO_METADATA(uri, 'null') AS metadata
FROM videos;

-- Extract one frame every second and expand the result into one frame per row.
SELECT v.id, f.frame, f.metadata
FROM videos AS v
CROSS JOIN LATERAL TABLE(
  VIDEO_EXPLODE_FRAMES(v.uri, 'sample', 1000)
) AS f(frame, metadata);

Parameters

SQL functions take arguments in the order defined in the signature and support only positional arguments. In a function signature, [ ] indicates an optional parameter. For example, the signature of VIDEO_METADATA is as follows:

VIDEO_METADATA(
  uri
  [, on_error
  [, read_chunk_size
  [, max_cached_blocks
  [, read_ahead_blocks]]]]
)

The following rules apply to invocation:

• Omitted optional parameters are filled with their default values.

• To explicitly set a later optional parameter, you must also explicitly specify all preceding optional parameters.

• NULL is passed as a parameter value, which is not equivalent to the default value used when the parameter is omitted. When a polymorphic data parameter requires NULL, use CAST(NULL AS <exact type>) to specify the type explicitly.

• Except for video_input, VIDEO_METADATA of uri, and VIDEO_SPLIT of metadata, all other parameters are configuration parameters and must use SQL literals, including NULL. Table fields and CAST expressions are not supported.

• VIDEO_SPLIT and VIDEO_EXPLODE_FRAMES is a table-valued function (UDTF) and must be called by using LATERAL TABLE.

Video data forms

VIDEO_METADATA accepts only a video URI. The other three video functions accept both a URI and a video segment reference. Only the first video stream is selected when a video is read.

Video segment reference

ROW<uri STRING, start_time_ms BIGINT, end_time_ms BIGINT>

VIDEO_SPLIT returns this data structure. A video segment uses the closed interval [start_time_ms, end_time_ms]. For example, for [0, 999], the next adjacent segment starts at 1000.

Video metadata

VIDEO_METADATA returns the following structure:

ROW<
  width INT,
  height INT,
  fps DOUBLE,
  duration_ms BIGINT,
  frame_count BIGINT,
  time_base DOUBLE,
  codec_name STRING,
  video_stream_index INT
>

A metadata field may be NULL.

Video frame metadata

VIDEO_EXPLODE_FRAMES returns the following metadata for each output frame:

ROW<
  uri STRING,
  video_stream_index INT,
  frame_index BIGINT,
  pts BIGINT,
  time_ms BIGINT,
  key_frame BOOLEAN,
  start_time_ms BIGINT,
  end_time_ms BIGINT
>

VIDEO_METADATA

Reads basic video information such as resolution, frame rate, duration, and encoding format. This function reads only the container and video stream metadata and does not decode video frames.

VIDEO_METADATA(
  uri
  [, on_error
  [, read_chunk_size
  [, max_cached_blocks
  [, read_ahead_blocks]]]]
)

Return type: video metadata ROW. If the input URI is NULL, the function returns NULL.

WITH source_with_metadata AS (
  SELECT id, VIDEO_METADATA(uri, 'null') AS metadata
  FROM videos
)
SELECT id, metadata.width AS width, metadata.duration_ms AS duration_ms
FROM source_with_metadata;

VIDEO_EXTRACT_FRAMES

Extracts frames from a complete video or a video segment and collects the results of the same input video into an array in a single row. For long videos or a large number of extracted frames, we recommend that you use VIDEO_EXPLODE_FRAMES to avoid holding too many images in a single row.

VIDEO_EXTRACT_FRAMES(
  video_input
  [, frame_selector
  [, sample_interval_ms
  [, max_frames
  [, image_height, image_width
  [, on_error
  [, read_chunk_size
  [, max_cached_blocks
  [, read_ahead_blocks]]]]]]]]]
)

Return type: ARRAY<IMAGE>.

• image_height and image_width specify the exact output size and do not preserve the original aspect ratio.

• When the input is a video segment reference, the closed interval [start_time_ms, end_time_ms] is used to filter frames whose time_ms can be determined.'all_frames' and 'keyframe' strategy, frames whose time_ms cannot be determined may still be output. The 'sample' strategy skips such frames.

• frame_selector='sample' establishes sampling time points from the start of the segment, and the output time is not earlier than the first frame at each sampling point. Frames whose time_ms cannot be determined are not used as sampling results.

• If the input is NULL or processing fails under the 'null' strategy, the function returns NULL. If processing succeeds but no frame is selected, an empty array is returned.

-- Extract one frame every second, return at most 10 frames per video, and scale them to 224 × 224.
SELECT VIDEO_EXTRACT_FRAMES(
  uri, 'sample', 1000, 10, 224, 224, 'null'
) AS frames
FROM videos;

VIDEO_SPLIT

Splits a complete video or an existing video segment into multiple segment references and expands them into one segment reference per row. This function does not copy video content or read video frames. It only calculates time windows.

VIDEO_SPLIT(
  video_input
  [, metadata
  [, segment_duration_ms
  [, num_segments
  [, video_duration_ms
  [, max_segments
  [, on_error
  [, read_chunk_size
  [, max_cached_blocks
  [, read_ahead_blocks]]]]]]]]]
)

Returned column: segment ROW<uri STRING, start_time_ms BIGINT, end_time_ms BIGINT>, one segment per row.

read_chunk_size, max_cached_blocks and read_ahead_blocks is used only when the function probes the metadata by itself.

• If the input reference already contains end_time_ms, the segment boundaries are used directly.

• If the input has no end_time_ms, the duration in metadata and video_duration_ms are used in sequence. If neither provides a valid value, the function probes the video duration.

• When splitting by fixed duration, each segment is a closed interval. For example, a 2-second video split by 1000 milliseconds produces [0, 999] and [1000, 1999].

• When splitting by the number of segments, the function rounds up the segment duration, so the actual number of segments may be less than num_segments.

-- Use a typed NULL as a placeholder for metadata, and split by 1 second.
SELECT v.id, s.segment
FROM videos AS v
CROSS JOIN LATERAL TABLE(
  VIDEO_SPLIT(v.uri, CAST(NULL AS BIGINT), 1000)
) AS s(segment);

-- Reuse the metadata that has already been read, and split by the target number of segments.
WITH source_with_metadata AS (
  SELECT id, uri, VIDEO_METADATA(uri) AS metadata
  FROM videos
)
SELECT v.id, s.segment
FROM source_with_metadata AS v
CROSS JOIN LATERAL TABLE(
  VIDEO_SPLIT(
    v.uri,
    v.metadata,
    NULL,
    4
  )
) AS s(segment);

VIDEO_EXPLODE_FRAMES

Extracts frames from a complete video or a video segment and expands the results into one frame per row.

VIDEO_EXPLODE_FRAMES(
  video_input
  [, frame_selector
  [, sample_interval_ms
  [, max_frames
  [, image_height, image_width
  [, on_error
  [, read_chunk_size
  [, max_cached_blocks
  [, read_ahead_blocks]]]]]]]]]
)

Returned column: frame IMAGE, metadata ROW<...>, one video frame per row.

The rules for frame selection, scaling, and segment boundaries are the same as those of VIDEO_EXTRACT_FRAMES. When on_error='skip' is used, no rows are output if decoding fails before the first frame is output. If an error occurs after some frames have been output, the rows already output are retained.

SELECT v.id, f.frame, f.metadata.time_ms AS frame_time_ms
FROM videos AS v
CROSS JOIN LATERAL TABLE(
  VIDEO_EXPLODE_FRAMES(
    v.uri, 'sample', 1000, 10, 224, 224, 'skip'
  )
) AS f(frame, metadata);