Port SQL queries into an Omni workbook via API

Requirements

To follow along with this guide, you’ll need:

To install jq, which is a lightweight JSON processor. The script in this guide uses it to build and parse JSON payloads. Refer to the jq documentation for installation instructions.

The following information from your Omni instance:

The subdomain of your Omni URL, for example blobsrus
The ID of the model you want to query

Implementation

The following script uses curl to send a POST request to the /api/v1/documents endpoint. It defines four distinct SQL queries and organizes them into a single workbook with specific visualization types: line charts, bar charts, and tables.

This part of the guide walks through different parts of the script and how they work. Refer to the Run the script section for a full copy-pasteable version of the script.

Script configuration

The first part of the script contains your Omni information, which you’ll need to define before running the script:

OMNI_INSTANCE - The subdomain of your Omni instance
OMNI_API_TOKEN - Your Omni API key
MODEL_ID - The ID of the model you want to query
DOCUMENT_NAME - The name of the document you’ll create
BASE_TABLE - The name of an existing view in your Omni model. This cannot be an arbitrary string.
Each query can point to a different base table. The examples in this guide all reference the same order_items table for simplicity, but one query could point to orders while another points to products.Omni recommends pointing to a view that is contextually related to the query being defined. While the field primarily serves as a reference point, using an unrelated view may produce unexpected behavior.

Script configuration

#!/bin/bash

# =============================================================================
# CONFIGURATION
# =============================================================================

OMNI_INSTANCE="your-instance"
OMNI_API_TOKEN="XXXXX"
MODEL_ID="your-model-uuid"
DOCUMENT_NAME="Multi-Tab SQL Analysis"

# Base table for connection context
BASE_TABLE="order_items"

SQL queries

The SQL queries section defines the actual SQL queries that will be ported into the workbook.

SQL queries

# =============================================================================
# SQL QUERIES
# =============================================================================

SQL_QUERY_1="SELECT DATE(created_at) as order_date, SUM(sale_price) as total_revenue FROM order_items GROUP BY 1"
SQL_QUERY_2="SELECT product_category, SUM(sale_price) as total_sales FROM order_items GROUP BY 1 LIMIT 25"
SQL_QUERY_3="SELECT status, COUNT(*) as order_count FROM order_items GROUP BY 1"
SQL_QUERY_4="SELECT DATE_TRUNC('month', created_at) as month, SUM(sale_price) as revenue FROM order_items GROUP BY 1"

API request

Use the Model ID found in the URL of your model IDE to ensure the workbook targets the correct data schema.

The API request portion of the script uses the information in the Configuration and SQL queries sections to build the curl request.The queryPresentations array defines the individual query tabs (tiles) within the workbook. Each query tab object contains a few parameters that are worth highlighting:

isSql: true - This parameter tells Omni to treat the userEditedSQL string as the primary source of truth for the query
visConfig - This parameter sets the default visualization style for the query tab, such as line, bar, or table

The following snippet highlights these parameters in the script:

API request

# =============================================================================
# API REQUEST
# =============================================================================

API_URL="https://${OMNI_INSTANCE}.omniapp.co/api/v1/documents"

# Build JSON payload using jq
JSON_PAYLOAD=$(jq -n \
  --arg modelId "$MODEL_ID" \
  --arg name "$DOCUMENT_NAME" \
  --arg table "$BASE_TABLE" \
  --arg sql1 "$SQL_QUERY_1" \
  --arg sql2 "$SQL_QUERY_2" \
  --arg sql3 "$SQL_QUERY_3" \
  --arg sql4 "$SQL_QUERY_4" \
  '{
    modelId: $modelId,
    name: $name,
    queryPresentations: [
      {
        name: "Daily Revenue",
        isSql: true,
        query: { table: $table, fields: [], userEditedSQL: $sql1 },
        visConfig: { chartType: "line" }
      },
      {
        name: "Top Products",
        isSql: true,
        query: { table: $table, fields: [], userEditedSQL: $sql2 },
        visConfig: { chartType: "bar" }
      },
      {
        name: "Order Status",
        isSql: true,
        query: { table: $table, fields: [], userEditedSQL: $sql3 },
        visConfig: { chartType: "table" }
      },
      {
        name: "Monthly Trends",
        isSql: true,
        query: { table: $table, fields: [], userEditedSQL: $sql4 },
        visConfig: { chartType: "area" }
      }
    ]
  }')

# Send the request
curl -X POST "$API_URL" \
  -H "Authorization: Bearer ${OMNI_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -d "$JSON_PAYLOAD"

Run the script

The following code example is a full copy-pasteable version of the script. To use it:

Save this version to a convenient location.

Replace the configuration variables with your Omni information.

Use the command line to run the script!

Full copy-pasteable script version

#!/bin/bash

# =============================================================================
# CONFIGURATION
# =============================================================================

OMNI_INSTANCE="your-instance"
OMNI_API_TOKEN="XXXXX"
MODEL_ID="your-model-uuid"
DOCUMENT_NAME="Multi-Tab SQL Analysis"

# Base table for connection context
BASE_TABLE="order_items"

# =============================================================================
# SQL QUERIES
# =============================================================================

SQL_QUERY_1="SELECT DATE(created_at) as order_date, SUM(sale_price) as total_revenue FROM order_items GROUP BY 1"
SQL_QUERY_2="SELECT product_category, SUM(sale_price) as total_sales FROM order_items GROUP BY 1 LIMIT 25"
SQL_QUERY_3="SELECT status, COUNT(*) as order_count FROM order_items GROUP BY 1"
SQL_QUERY_4="SELECT DATE_TRUNC('month', created_at) as month, SUM(sale_price) as revenue FROM order_items GROUP BY 1"

# =============================================================================
# API REQUEST
# =============================================================================

API_URL="https://${OMNI_INSTANCE}.omniapp.co/api/v1/documents"

# Build JSON payload using jq
JSON_PAYLOAD=$(jq -n \
  --arg modelId "$MODEL_ID" \
  --arg name "$DOCUMENT_NAME" \
  --arg table "$BASE_TABLE" \
  --arg sql1 "$SQL_QUERY_1" \
  --arg sql2 "$SQL_QUERY_2" \
  --arg sql3 "$SQL_QUERY_3" \
  --arg sql4 "$SQL_QUERY_4" \
  '{
    modelId: $modelId,
    name: $name,
    queryPresentations: [
      {
        name: "Daily Revenue",
        isSql: true,
        query: { table: $table, fields: [], userEditedSQL: $sql1 },
        visConfig: { chartType: "line" }
      },
      {
        name: "Top Products",
        isSql: true,
        query: { table: $table, fields: [], userEditedSQL: $sql2 },
        visConfig: { chartType: "bar" }
      },
      {
        name: "Order Status",
        isSql: true,
        query: { table: $table, fields: [], userEditedSQL: $sql3 },
        visConfig: { chartType: "table" }
      },
      {
        name: "Monthly Trends",
        isSql: true,
        query: { table: $table, fields: [], userEditedSQL: $sql4 },
        visConfig: { chartType: "area" }
      }
    ]
  }')

# Send the request
curl -X POST "$API_URL" \
  -H "Authorization: Bearer ${OMNI_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -d "$JSON_PAYLOAD"

Requirements

Implementation

Run the script

Next steps