Skip to navigation

SQL Table Reference

Look up the columns and types of the core, concept, and video structure tables you can query in the Query Engine.

Which tables appear depends on the dataset’s media type and when it was ingested. Run SHOW TABLES to see the tables for your dataset.

This page lists the columns in each SQL table Coactive creates for a dataset, so you can write queries without running DESCRIBE first. To run queries, see Getting Started with the Query Engine.

Dynamic tag views (dt_*) are documented separately in Dynamic Tag Tables.

Core tables

coactive_table

ColumnDefinitionFormat
coactive_image_idCoactive image identifierstring
coactive_shot_idLegacy. Not populated on current datasets. For shot-level data, use dt_[group]_shot joined to coactive_table_slice.string
coactive_video_idCoactive video identifierstring
created_dtTimestamp of video ingestion (keyframe creation)timestamp
keyframe_indexIndex of the keyframe within the assetstring
keyframe_time_msStart time of the keyframefloat
pathStorage path of the keyframestring
metadataFile-level metadata as key/value pairs (for example, title and genre)map<string, string>

To read a metadata value, see Read metadata values.

coactive_table_adv

An advanced version of coactive_table. It includes every column in coactive_table, plus additional metadata and a pair of columns for each concept:

Additional columnDefinitionFormat
[concept_name]_probScore representing the likelihood that the concept applies to the assetfloat (0.0–1.0)
[concept_name]Binary flag indicating whether the concept applies to the asset, using a default threshold of 0.7 on [concept_name]_probinteger (0 or 1)

For example queries, see Analyzing Concepts with SQL.

coactive_table_video

ColumnDefinitionFormat
coactive_video_idCoactive video identifierstring
pathStorage path of the video assetstring
created_dtTimestamp of video ingestiontimestamp
metadataFile-level metadata as key/value pairs (for example, series_name and tv_rating)map<string, string>

To read a metadata value, see Read metadata values.

coactive_table_keyframe

ColumnDefinitionFormat
idCoactive keyframe identifierstring
org_idOrganization identifierstring
dataset_idDataset identifierstring
video_idCoactive video identifierstring
statusProcessing status of this record (for example, new, processed, error)string
pathStorage path of the keyframestring
frame_time_msTimestamp of the frame within the interval, in millisecondsinteger
start_time_msStart time of the interval the keyframe was extracted from, in millisecondsinteger
end_time_msEnd time of the interval the keyframe was extracted from, in millisecondsinteger

coactive_table_transcription

ColumnDefinitionFormat
org_idOrganization identifierstring
dataset_idDataset identifierstring
idUnique identifier of the transcript segmentstring
video_idAssociated video identifierstring
audio_idAssociated audio track identifierstring
languageLanguage of the transcript segmentstring
granularityLevel of segmentation (for example, sentence)string
textTranscript textstring
start_timeStart time of the segment in seconds (relative to audio start)float
end_timeEnd time of the segment in secondsfloat
confidence_scoreConfidence score of the ASR output for the segmentfloat (0.0–1.0)
created_dtWhen the transcript record was createdtimestamp
audio_file_formatFormat of the audio file (for example, wav)string
audio_file_duration_sDuration of the audio file in secondsfloat
channel_indexChannel index within multi-channel audiointeger (starts from 0)

Concept tables

concept_[concept name]

Each concept gets its own table, for example concept_beach_scenes.

ColumnDefinitionFormat
coactive_image_idCoactive identifier of the image or keyframestring
concept_nameName of the conceptstring
scoreLikelihood the concept applies to the image or keyframefloat (0.0–1.0)

Video structure tables

Video structure tables describe how a video is divided into time ranges. They support native video understanding.

coactive_table_slice

A slice is a time range in a video with a specific property.

ColumnDefinitionFormat
idUnique identifier of the slicestring
composite_idIdentifier of the composite (group of slices) this slice belongs tostring
indexIndex of the slice within the compositeinteger (starts from 0)
start_frame_numFrame index at which the slice startsinteger (starts from 0)
end_frame_numFrame index at which the slice endsinteger
start_time_msStart time of the slice, in millisecondsinteger
end_time_msEnd time of the slice, in millisecondsinteger
video_idCoactive video identifier the slice is associated withstring

coactive_table_composite

A composite, such as a shot or a scene, is made of slices.

ColumnDefinitionFormat
idUnique identifier of the compositestring
video_idCoactive video identifier the composite is associated withstring
typeComposite type (for example, intelligent sampling)string
statusProcessing status (for example, new)string
created_dtWhen the composite was createdtimestamp
updated_dtWhen the composite was last updatedtimestamp

Ad segments are stored in coactive_table_composite as rows where type = 'ad-segment'. For a query that uses them, see Find ad segments.

coactive_table_shot

Legacy. Appears only on older datasets. Use dt_[group]_shot joined to coactive_table_slice instead.