Ontology SQL functions（本体 SQL 函数）¶

:::callout{theme="neutral" title="Beta"} Ontology SQL functions are in the beta phase of development and some features may not be available on your environment. Functionality may change during active development. Contact Palantir support to request access to Beta features. :::

Ontology SQL functions allow you to define reusable, parameterized queries over ontology objects using SQL. Once published, an ontology SQL function can be used anywhere in Foundry that accepts ontology functions, including Workshop, Actions, Automate, and the Ontology SDK (OSDK).

Ontology SQL functions are read-only: you cannot modify the ontology from within an ontology SQL function.

:::callout{theme="warning"} Beta ontology SQL functions do not currently support high-concurrency workloads and may need to queue when resource availability is low, or fail under load. For additional query and data constraints, refer to the Ontology SQL limitations and constraints. :::

Define your function with SQL¶

You can write ontology SQL functions in SQL Studio. You must be in Object mode to create a function.

Functions support parameters, which are declared using variables in the SQL dialect. In the example below, animalParam is a parameter that is passed in when the function is called. The default value 'dog' is used when testing the query in the editor.

Parameters can only hold literal values (for example, a string, number, or boolean) of the data types supported by the ontology SQL dialect. A parameter cannot contain a SQL fragment or expression, and can only be used in positions where the SQL dialect expects a literal value, such as the right-hand side of a WHERE clause comparison.

```sql tab="SQL function example" DECLARE @animalParam STRING = 'dog';

SELECT * FROM ri.ontology.main.object-type... WHERE animaltype = @animalParam;

## Publish your function

When you are satisfied with your function, save your worksheet and select the **Publish** button. Your worksheet must be saved to a project before you can publish the function.

<img src="./media/publish-function.png" alt="Publish function button." width=250>

In the **Publish function** dialog, provide a **Name** and **API name** for your function, as with any other function in Foundry.

<img src="./media/publish-function-name.png" alt="Publish function name and API name dialog." width=500>

## Use your function

Ontology SQL functions return a list of structs whose fields match the selected columns, with one struct per row in the result. When only a single column is selected, the function returns a list of that column's type instead. For example, `SELECT animal, animalType, age FROM ...` returns `List<Struct<animal: STRING, animalType: STRING, age: INTEGER>>`, while `SELECT animal FROM ...` returns `List<STRING>`.

After your function is published, it can be [used throughout Foundry](https://palantir.com/docs/foundry/functions/use-functions/) like any other ontology function, including in Workshop, Actions, Automate, and OSDK. Ontology SQL functions can also be called from TypeScript and Python functions. As with other functions in Foundry, Ontology SQL functions support querying on [branches](https://palantir.com/docs/foundry/global-branching/overview/) and [scenarios](https://palantir.com/docs/foundry/workshop/scenarios-overview/).

For details on Ontology SQL more broadly, refer to the [Ontology SQL overview](https://palantir.com/docs/foundry/sql-warehousing/ontology-sql/).


---

# 中文翻译

---

# 本体 SQL 函数

:::callout{theme="neutral" title="Beta"}
本体 SQL 函数（Ontology SQL functions）目前处于 [beta](https://palantir.com/docs/foundry/platform-overview/development-life-cycle/) 开发阶段，部分功能可能未在你的环境中开放。功能在活跃开发期间可能发生变化。如需申请 Beta 功能访问权限，请联系 Palantir 技术支持。
:::

本体 SQL 函数允许你使用 SQL 对[本体](https://palantir.com/docs/foundry/ontology/overview/)对象（ontology objects）定义可复用、带参数的查询。发布后，本体 SQL 函数可在 Foundry 中任何支持本体函数（ontology functions）的位置使用，包括 Workshop、Actions、Automate 以及 Ontology SDK（OSDK）。

本体 SQL 函数为只读函数：你无法在本体 SQL 函数内部修改本体。

:::callout{theme="warning"}
Beta 版本体 SQL 函数目前不支持高并发工作负载，在资源可用性较低时可能需要排队，或在负载过高时失败。有关其他查询和数据限制，请参阅[本体 SQL 限制与约束](https://palantir.com/docs/foundry/sql-warehousing/ontology-sql/#limitations-and-constraints)。
:::

## 使用 SQL 定义函数

你可以在 [SQL Studio](https://palantir.com/docs/foundry/sql-warehousing/sql-studio/) 中编写本体 SQL 函数。创建函数时，必须处于[对象模式](https://palantir.com/docs/foundry/sql-warehousing/sql-studio/#object-mode)（Object mode）。

函数支持参数，这些参数通过 [SQL 方言](https://palantir.com/docs/foundry/sql-warehousing/sql-dialect/)中的[变量](https://palantir.com/docs/foundry/sql-warehousing/ontology-sql/#using-variables)（variables）声明。在以下示例中，`animalParam` 是一个在调用函数时传入的参数。在编辑器中测试查询时，会使用默认值 `'dog'`。

参数只能包含本体 SQL 方言支持的[数据类型](https://palantir.com/docs/foundry/sql-warehousing/sql-dialect/#data-types)（data types）的字面值（例如字符串、数字或布尔值）。参数不能包含 SQL 片段或表达式，且只能在 SQL 方言期望字面值的位置使用，例如 `WHERE` 子句比较的右侧。

```sql tab="SQL 函数示例"
DECLARE @animalParam STRING = 'dog';

SELECT *
FROM `ri.ontology.main.object-type...`
WHERE animaltype = @animalParam;

发布函数¶

当函数满足要求后，保存工作表并选择发布按钮。工作表必须先保存到项目中，然后才能发布函数。

发布函数按钮。

在发布函数对话框中，为函数提供名称和API 名称，这与 Foundry 中其他函数的操作方式相同。

发布函数名称和 API 名称对话框。

使用函数¶

本体 SQL 函数返回一个结构体列表，其字段与所选列匹配，结果中的每一行对应一个结构体。当仅选择单个列时，函数返回该列类型的列表。例如，SELECT animal, animalType, age FROM ... 返回 List<Struct<animal: STRING, animalType: STRING, age: INTEGER>>，而 SELECT animal FROM ... 返回 List<STRING>。

函数发布后，即可像其他本体函数一样在 Foundry 中各处使用，包括 Workshop、Actions、Automate 和 OSDK。本体 SQL 函数也可以从 TypeScript 和 Python 函数中调用。与 Foundry 中的其他函数一样，本体 SQL 函数支持在分支（branches）和场景（scenarios）上进行查询。

有关本体 SQL 的更多详细信息，请参阅本体 SQL 概述。