Query APIs
These APIs are in beta
Query APIs are in beta and may have future breaking changes.
The query APIs allow you to interact with workbook queries in Omni.
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.
POST /api/unstable/query/run
curl --location 'https://myorg.omniapp.co/api/unstable/query/run' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer API_TOKEN' \
--data '{
"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"
}
}'
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| query | Object | ✓ | A JSON object representing the query to be run. |
Did you know?
You can retrieve a query's JSON object right from an Omni workbook:
- Open a workbook in Omni.
- Toggle open the Inspector panel. For Mac use
Option + 9; for Windows, useAlt + 9. - Locate the Query structure section.
- Copy the query JSON.
Returns
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.
Example response body
200 OK response
{
"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"
}
Encountering timeouts?
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]