Query APIs
The query APIs are in beta and may have future breaking changes.
The query APIs allow you to interact with workbook queries in Omni. Use the Retrieve document queries API to retrieve a document's queries.
Run a query
Runs the query specified in the request body. Successful requests will return the data as a base64 encoded Apache Arrow table, allowing you to extract query results from Omni and use them elsewhere. For example, piping data to Google Sheets or leveraging data in a Python notebook.
curl -L -X'https://myorg.omniapp.co/api/unstable/query/run' \
--H 'Content-Type: application/json' \
--H 'Authorization: Bearer <TOKEN>' \
--d '{
"query": {
"limit": 10,
"sorts": [
{
"column_name": "inventory_items.product_department",
"sort_descending": false
},
{
"column_name": "inventory_items.product_category",
"sort_descending": false
}
],
"table": "order_items",
"fields": [
"inventory_items.product_department",
"inventory_items.product_category",
"inventory_items.product_brand",
"inventory_items.count"
],
"modelId": "bcf0cffd-ec1b-44d5-945a-a261ebe407fc",
"version": 5,
"column_limit": 50,
"column_totals": {
"inventory_items.product_category": {
"type": "aggregation"
},
"inventory_items.product_department": {
"type": "aggregation"
}
},
"dimensionIndex": 3,
"join_paths_from_topic_name": "order_items"
},
"userId": "4c34905f-39bb-444b-9f8c-ffaf69b30100", // Optional: Run query as specific user
"cache": "SkipRequery", // Optional: Set cache policy
"resultType": "csv" // Optional: Define export type
}'
Parameters
| Parameter | Type | Required | Default | Description |
|---|---|---|---|---|
| query | object | Yes ✓ | n/a | A JSON object representing the query to be run. You can retrieve a query's JSON object right from an Omni workbook:
|
| userId | string | No X | n/a | Optional UUID to run the query as a specific user. If not provided, uses the user associated with the API token. The |
| cache | string | No X | SkipRequery | Optional cache policy to control how query caching behaves. Accepts one of the following values:
|
| resultType | string | No X | - | Optional parameter to specify the format of query results exported by the API. Accepts one of the following values:
If omitted, the API will return results in a base-64 encoded format. |
Response
200 OK
Successful requests will return a 200 OK status and a response body similar to the following:View full response body
{
"jobs_submitted": {
"6937012a-e9d4-445d-9a60-839566a6de49": "572f5f28-c91f-453a-bb92-a1f89914a5bf"
}
},
{
"job_id": "6937012a-e9d4-445d-9a60-839566a6de49",
"status": "COMPLETE",
"client_result_id": "572f5f28-c91f-453a-bb92-a1f89914a5bf",
"summary": {
"cache_type": "EXACT",
"display_sql": "SELECT \"inventory_items.product_department\",\n \"inventory_items.product_category\",\n \"inventory_items.product_brand\",\n \"inventory_items.count\",\n \"ssort_0\",\n \"ssort_1\",\n \"$omni_group_grouping\",\n \"$omni_column_total_indicator\",\n \"$omni_total_sort\"\nFROM (SELECT \"inventory_items.product_department\",\n \"inventory_items.product_category\",\n \"inventory_items.product_brand\",\n \"inventory_items.count\",\n \"ssort_0\",\n \"ssort_1\",\n \"$omni_group_grouping\",\n \"$omni_column_total_indicator\",\n \"$omni_total_sort\",\n ROW_NUMBER() OVER (PARTITION BY \"$omni_column_total_indicator\" ORDER BY \"inventory_items.product_department\" NULLS FIRST, \"ssort_0\", \"inventory_items.product_category\" NULLS FIRST, \"ssort_1\", \"$omni_total_sort\", \"inventory_items.product_brand\", \"inventory_items.count\", \"$omni_group_grouping\", \"$omni_column_total_indicator\") AS \"$f9\"\n FROM (SELECT \"inventory_items.product_department\",\n \"inventory_items.product_category\",\n \"inventory_items.product_brand\",\n \"inventory_items.count\",\n CASE\n WHEN \"$omni_group_grouping\" = 3 THEN 1\n ELSE 0\n END AS \"ssort_0\",\n CASE\n WHEN \"$omni_group_grouping\" = 1 THEN 1\n ELSE 0\n END AS \"ssort_1\",\n \"$omni_group_grouping\",\n CASE\n WHEN \"$omni_group_grouping\" = 1 THEN 'column_subtotal::inventory_items.product_category'\n WHEN \"$omni_group_grouping\" = 3 THEN 'column_subtotal::inventory_items.product_department'\n WHEN \"$omni_group_grouping\" = 0 THEN NULL\n ELSE NULL\n END AS \"$omni_column_total_indicator\",\n \"$omni_group_grouping\" AS \"$omni_total_sort\"\n FROM (SELECT \"inventory_items\".\"product_department\" AS \"inventory_items.product_department\",\n \"inventory_items\".\"product_category\" AS \"inventory_items.product_category\",\n \"inventory_items\".\"product_brand\" AS \"inventory_items.product_brand\",\n COUNT(\"inventory_items\".\"id\") AS \"inventory_items.count\",\n GROUPING(\"inventory_items\".\"product_department\", \"inventory_items\".\"product_category\", \"inventory_items\".\"product_brand\") AS \"$omni_group_grouping\"\n FROM \"order_items\"\n LEFT JOIN \"inventory_items\" ON \"order_items\".\"inventory_item_id\" = \"inventory_items\".\"id\"\n GROUP BY GROUPING SETS((1, 2, 3), (1, 2), 1)) AS \"t1\"\n ORDER BY \"inventory_items.product_department\" NULLS FIRST, 5, \"inventory_items.product_category\" NULLS FIRST, 6, 9, \"inventory_items.product_brand\", \"inventory_items.count\", \"$omni_group_grouping\", 8\n LIMIT 30) AS \"t3\") AS \"t4\"\nWHERE \"$f9\" <= 10\nORDER BY \"inventory_items.product_department\" NULLS FIRST, \"ssort_0\", \"inventory_items.product_category\" NULLS FIRST, \"ssort_1\", \"$omni_total_sort\"",
"omni_sql": "SELECT \"inventory_items\".\"product_department\" AS \"inventory_items.product_department\",\n \"inventory_items\".\"product_category\" AS \"inventory_items.product_category\",\n \"inventory_items\".\"product_brand\" AS \"inventory_items.product_brand\",\n COUNT(\"inventory_items\".\"id\") AS \"inventory_items.count\"\nFROM \"order_items\"\n LEFT JOIN \"inventory_items\" ON \"order_items\".\"inventory_item_id\" = \"inventory_items\".\"id\"\nGROUP BY GROUPING SETS((1, 2, 3), (1, 2), 1)\nORDER BY 1 NULLS FIRST, 2 NULLS FIRST\nLIMIT 10",
"stage_summaries": [],
"omni_sql_parse_failed": false,
"stats": {
"job_completion_lookup": 14,
"notification_latency": 4,
"plan_queue_latency": 4,
"job_add_latency": 13,
"plan_job_lookup": 13,
"plan_job_execution": 351,
"plan_job_state_update": 20,
"stream_open": 60
},
"plan_stats": {
"model_fetch": 188,
"connection_metadata_fetch": 8,
"topic_permission_check": 1,
"model_detail_build": 1,
"plan_info_schema_lookups": 11,
"semantic_plan_omni_algebra_gen": 19,
"semantic_plan_to_exec": 19,
"semantic_plan": 55,
"cache_check": 74
},
"fields": {
"inventory_items.product_department": {
"field_name": "product_department",
"view_name": "inventory_items",
"data_type": "STRING",
"is_dimension": true,
"fully_qualified_name": "inventory_items.product_department"
},
"inventory_items.product_category": {
"field_name": "product_category",
"view_name": "inventory_items",
"data_type": "STRING",
"is_dimension": true,
"fully_qualified_name": "inventory_items.product_category"
},
"inventory_items.product_brand": {
"field_name": "product_brand",
"view_name": "inventory_items",
"data_type": "STRING",
"is_dimension": true,
"fully_qualified_name": "inventory_items.product_brand"
},
"inventory_items.count": {
"field_name": "count",
"view_name": "inventory_items",
"aggregate_type": "COUNT",
"filters": {},
"data_type": "NUMBER",
"ignored": false,
"label": "Inventory Items Count",
"format": "NUMBER_0",
"display_sql": "COUNT(*)",
"fully_qualified_name": "inventory_items.count"
}
},
"missing_fields": [],
"invalid_calculations": {}
},
"cache_metadata": {
"plan_key": "8ab7613c-7b24-4d78-8370-2c1dd50e9530",
"field_list": [
"inventory_items.product_department",
"inventory_items.product_category",
"inventory_items.product_brand",
"inventory_items.count"
],
"num_rows": 10,
"created_at": 1733246311141,
"data_fresh_at": 1733246311116,
"bytes": 2096,
"ttl": 3600,
"model_id": "bcf0cffd-ec1b-44d5-945a-a261ebe407fc"
},
"query": {
"model_job": {
"model_id": "bcf0cffd-ec1b-44d5-945a-a261ebe407fc",
"table": "order_items",
"fields": [
"inventory_items.product_department",
"inventory_items.product_category",
"inventory_items.product_brand",
"inventory_items.count"
],
"calculations": [],
"filters": {},
"sorts": [
{
"column_name": "inventory_items.product_department",
"sort_descending": false,
"is_column_sort": false,
"null_sort": "OMNI_DEFAULT"
},
{
"column_name": "inventory_items.product_category",
"sort_descending": false,
"is_column_sort": false,
"null_sort": "OMNI_DEFAULT"
}
],
"limit": 10,
"pivots": [],
"fill_fields": [],
"column_totals": {
"inventory_items.product_category": {
"type": "aggregation"
},
"inventory_items.product_department": {
"type": "aggregation"
}
},
"row_totals": {},
"column_limit": 50,
"default_group_by": true,
"join_via_map": {},
"join_paths_from_topic_name": "order_items",
"client_result_id": "572f5f28-c91f-453a-bb92-a1f89914a5bf",
"version": 5,
"period_over_period_computations": [],
"query_references": {},
"metadata": {},
"custom_summary_types": {}
}
},
"result": "/////+gCAAAQAAAAAAAKAA4ABgANAAgACgAAAAAABAAQAAAAAAEKAAwAAAAIAAQACgAAAAgAAAAIAAAAAAAAAAkAAABcAgAA/AEAALABAABcAQAAIAEAAOQAAACYAAAATAAAAAQAAADa/f//FAAAABQAAAAUAAAAAAACARgAAAAAAAAAAAAAALz+//8AAAABIAAAABAAAAAkb21uaV90b3RhbF9zb3J0AAAAAB7+//8UAAAAFAAAABQAAAAAAAUBEAAAAAAAAAAAAAAADP7//xwAAAAkb21uaV9jb2x1bW5fdG90YWxfaW5kaWNhdG9yAAAAAGb+//8UAAAAFAAAABQAAAAAAAIBGAAAAAAAAAAAAAAASP///wAAAAEgAAAAFAAAACRvbW5pX2dyb3VwX2dyb3VwaW5nAAAAAK7+//8UAAAAFAAAABQAAAAAAAIBGAAAAAAAAAAAAAAAkP///wAAAAEgAAAABwAAAHNzb3J0XzEA5v7//xQAAAAUAAAAFAAAAAAAAgEYAAAAAAAAAAAAAADI////AAAAASAAAAAHAAAAc3NvcnRfMAAe////FAAAABQAAAAcAAAAAAACASAAAAAAAAAAAAAAAAgADAAIAAcACAAAAAAAAAFAAAAAFQAAAGludmVudG9yeV9pdGVtcy5jb3VudAAAAG7///8UAAAAFAAAABQAAAAAAAUBEAAAAAAAAAAAAAAAXP///x0AAABpbnZlbnRvcnlfaXRlbXMucHJvZHVjdF9icmFuZAAAALb///8UAAAAFAAAABQAAAAAAAUBEAAAAAAAAAAAAAAApP///yAAAABpbnZlbnRvcnlfaXRlbXMucHJvZHVjdF9jYXRlZ29yeQAAEgAYABQAEwASAAwAAAAIAAQAEgAAABQAAAAUAAAAGAAAAAAABQEUAAAAAAAAAAAAAAAEAAQABAAAACIAAABpbnZlbnRvcnlfaXRlbXMucHJvZHVjdF9kZXBhcnRtZW50AAD/////SAIAABQAAAAAAAAADAAWAA4AFQAQAAQADAAAAOgCAAAAAAAAAAAEABAAAAAAAwoAGAAMAAgABAAKAAAAFAAAAHgBAAAKAAAAAAAAAAAAAAAWAAAAAAAAAAAAAAACAAAAAAAAAAgAAAAAAAAALAAAAAAAAAA4AAAAAAAAAB4AAAAAAAAAWAAAAAAAAAACAAAAAAAAAGAAAAAAAAAALAAAAAAAAACQAAAAAAAAAG4AAAAAAAAAAAEAAAAAAAACAAAAAAAAAAgBAAAAAAAALAAAAAAAAAA4AQAAAAAAAFsAAAAAAAAAmAEAAAAAAAACAAAAAAAAAKABAAAAAAAAUAAAAAAAAADwAQAAAAAAAAIAAAAAAAAA+AEAAAAAAAAoAAAAAAAAACACAAAAAAAAAgAAAAAAAAAoAgAAAAAAACgAAAAAAAAAUAIAAAAAAAACAAAAAAAAAFgCAAAAAAAAKAAAAAAAAACAAgAAAAAAAAIAAAAAAAAAiAIAAAAAAAAsAAAAAAAAALgCAAAAAAAAAAAAAAAAAAC4AgAAAAAAAAIAAAAAAAAAwAIAAAAAAAAoAAAAAAAAAAAAAAAJAAAACgAAAAAAAAAAAAAAAAAAAAoAAAAAAAAAAAAAAAAAAAAKAAAAAAAAAAAAAAAAAAAACgAAAAAAAAAAAAAAAAAAAAoAAAAAAAAAAAAAAAAAAAAKAAAAAAAAAAAAAAAAAAAACgAAAAAAAAAAAAAAAAAAAAoAAAAAAAAACgAAAAAAAAAKAAAAAAAAAAAAAAAAAAAA/wMAAAAAAAAAAAAAAwAAAAYAAAAJAAAADAAAAA8AAAASAAAAFQAAABgAAAAbAAAAHgAAAAAAAABNZW5NZW5NZW5NZW5NZW5NZW5NZW5NZW5NZW5NZW4AAP8DAAAAAAAAAAAAAAsAAAAWAAAAIQAAACwAAAA3AAAAQgAAAE0AAABYAAAAYwAAAG4AAAAAAAAAQWNjZXNzb3JpZXNBY2Nlc3Nvcmllc0FjY2Vzc29yaWVzQWNjZXNzb3JpZXNBY2Nlc3Nvcmllc0FjY2Vzc29yaWVzQWNjZXNzb3JpZXNBY2Nlc3Nvcmllc0FjY2Vzc29yaWVzQWNjZXNzb3JpZXMAAP8DAAAAAAAAAAAAAAQAAAAUAAAAGgAAACYAAAAtAAAANQAAADwAAABBAAAASAAAAFsAAAAAAAAAMTgwc0FsZXhhbmRlciBKdWxpYW5BbGtpJ2lBbHBpbmUgU3dpc3NBbWljYWxlQW5nZWxpbmFBcm5ldHRlQVNJQ1NBdmlhdG9yQTpYIEFybWFuaSBFeGNoYW5nZQAAAAAA/wMAAAAAAAAcAQAAAAAAAAQAAAAAAAAA2QAAAAAAAAB5AAAAAAAAAAUAAAAAAAAADAAAAAAAAAACAAAAAAAAAAkAAAAAAAAACwAAAAAAAAACAAAAAAAAAP8DAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAP8DAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAP8DAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA/wMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA/////wAAAAA=",
"stream_stats": {
"server_stream": 18
}
}
{
"remaining_job_ids": [],
"timed_out": "false"
}
Successful responses will contain the following:
- An object with a
jobs_submittedproperty - An object containing details about the job, such as
job_id,status, and the providedquery - The job details object will also contain a
resultproperty, which contains the query results as a base64 encoded Apache Arrow table. At this point, you can use something like Omni Python SDK to decode and validate the results.
If a request takes too long, the response will include a remaining_job_ids property. When this occurs, poll the GET /api/unstable/query/wait endpoint until the timeout property is false:
curl https://myorg.omniapp.co/api/unstable/query/wait?job_ids=[remaining_job_ids]
429 Too Many Requests
Results from too many requests in a given time frame. Refer to the Rate limiting documentation for more information.