How can I call SQL on Ontology from Functions?

Ask the Community
1

I’m writing a function that queries the Ontology. I would like to use a SQL syntax to query the Ontology.
It seems that functions don’t have enough permissions to perform this query (missingScope": "api:usage:sql-queries-execute). What is the workaround to perform such queries ?

Context: Some users prefer the syntax in SQL to perform particular operations (joins and aggregation)

2 
import { Function, Double } from "@foundry/functions-api";
import { Objects } from "@foundry/ontology-api";

export class CustomerFunctions {
  @Function()
  public async avgOrderAmount(region: string): Promise<Double> {
    // "JOIN" → traverse the Customer → Orders link instead of joining tables
    const result = Objects.search()
      .customers()
      .filter(c => c.region.exactMatch(region))
      .searchAroundOrders()        // link traversal replaces the JOIN
      .aggregate(agg => agg.amount.avg());   // GROUP BY / AVG via the aggregation API

    return result.amount.avg ?? 0;
  }
}

Use the Ontology SDK (OSDK) instead of raw SQL — this is usually the best path. Rather than SQL syntax, use the OSDK’s object/link traversal APIs.

Functions in Foundry run with a restricted token by default. SQL queries against the Ontology require the api:usage:sql-queries-execute scope, which isn’t granted automatically — hence the missingScope error

3

Calling SQL query from code can be helpful in case if we want to use Object inside our logic.
I tried to hit endpoint - https://mypalantirfoundry.com/object-set-service/api/sql-endpoint/v1/queries/query
POST body - {"querySpec":{"query":"SELECT *\nFROM `ri.ontology.main.object-type.00000000-your ontology rid`\nWHERE latestData IS TRUE","tableProviders":{},"dialect":"SPARK","options":{"options":[{"option":"objectSetContext","value":"{}"}]}},"executionParams":{"defaultBranchIds":[],"resultFormat":"ARROW","resultMode":"AUTO","rowLimit":1000}}

you will have response in ARROW format; you can then convert it as per requirement.

4

Here is the code in Typescript and Python for minimal SQL calls from Functions.

Typescript v2 - SQL calling the Ontology

import { Client } from "@osdk/client";
import { SqlQueries } from "@osdk/foundry";

export default async function doSQLOntologyQuery(
  client: Client, // This parameter gets populated by Foundry at runtime
  prompt: string
): Promise<string> {
  const sqlQuery: SqlQueries.ExecuteOntologySqlQueryRequest = {
    query: "SELECT * FROM `ri.ontology.main.object-type.052d5f87-6ea0-49fe-8bca-bb2fd19e21b1`",
    // parameters: Parameters;
    rowLimit: 100,
    dryRun: false,
  };

  const result: SqlQueries.QueryStatus = await SqlQueries.SqlQueries.execute(
    client,
    sqlQuery
  );
  console.log(result);
  return "success";
}

Typescript v2 - SQL calling datasets

import { Client } from "@osdk/client";
import { SqlQueries } from "@osdk/foundry";

export default async function doSQLOntologyQuery(
  client: Client, // This parameter gets populated by Foundry at runtime
  prompt: string
): Promise<string> {
  const sqlQuery: SqlQueries.ExecuteOntologySqlQueryRequest = {
    query: "SELECT * FROM `ri.dataset....`",
    // parameters: Parameters;
    rowLimit: 100,
    dryRun: false,
  };

  const result: SqlQueries.QueryStatus = await SqlQueries.SqlQueries.execute(
    client,
    sqlQuery
  );
  console.log(result);
  return "success";
}

Python - SQL calling datasets or Ontology (in same snippet)

from foundry_sdk.v2.sql_queries.models import Parameters
from foundry_sdk.v2.sql_queries.sql_query import core
from foundry_sdk.v2.sql_queries.sql_query import SqlQueryClient

from functions.api import function
from foundry_sdk import FoundryClient
import base64
import typing


@function
def test_platform_api() -> str:
    client: FoundryClient = FoundryClient()
    result = client.admin.User.get("e1e95c4a-b6fd-448c-88b5-a3fd40acfaec")
    return result


@function
def sqlQuery() -> str:
    foundry_client: FoundryClient = FoundryClient()

    sqlQueryClient: SqlQueryClient = foundry_client.sql_queries.SqlQuery

    sqlQueryClient.execute(
        query= "SELECT * FROM `ri.dataset.main.object-type...`",
        fallback_branch_ids= ["master"],
        serialization_format= "CSV",
        request_timeout= core.Timeout(100),
    )
    sqlQueryClient.execute_ontology(
        query= "SELECT * FROM `ri.ontology.main.object-type.052d5f87-6ea0-49fe-8bca-bb2fd19e21b1`",
        dry_run= False,
        parameters= Parameters(),
        preview= True,
        row_limit= 100,
        request_timeout= core.Timeout(100),
    )

    return "test"

See https://www.palantir.com/docs/foundry/functions/platform-sdk#use-platform-apis

Unfortunately, those queries currently fail with "missingScope": "api:usage:sql-queries-execute" as this particular endpoints are not enabled to be hit from the function code repository environement.