Skip to main content

Query APIs

These APIs are in beta

The 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 -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
}'

Parameters

ParameterTypeRequiredDefaultDescription
queryobjectYes n/a

A JSON object representing the query to be run.

You can retrieve a query's JSON object right from an Omni workbook:

  1. Open a workbook in Omni.
  2. Toggle open the Inspector panel. For Mac use Option + 9; for Windows, use Alt + 9.
  3. Locate the Query structure section.
  4. Copy the query JSON.
userIdstringctNo Xn/a

Optional UUID to run the query as a specific user. If not provided, uses the user associated with the API token. The userId must belong to a user in your organization.

Response

200 OK

Successful requests will return a 200 OK status and a response body similar to the following:

View full 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"
}

Successful responses will contain the following:

  • An object with a jobs_submitted property
  • An object containing details about the job, such as job_id, status, and the provided query
  • The job details object will also contain a result property, 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.
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]
429 Too Many Requests

Results from too many requests in a given time frame. Refer to the Rate limiting documentation for more information.