SQL examples

This page provides hands-on SQL examples for common tasks in Foundry, including reading and writing tabular data and querying ontology object types. Examples can be run from SQL Studio, the embedded SQL console, or external SQL clients connected via Arrow Flight SQL. For full syntax reference, see the SQL dialect documentation.

Tabular data

The following examples query and modify datasets and Iceberg tables in data mode, executed by the Furnace engine.

Create and read a dataset

Copied!
1
2
3
4
5
6
7
8
9
-- Create a dataset
CREATE OR REPLACE TABLE `/path/dataset-name` USING parquet AS
SELECT *
FROM `/path/dataset-input`;

-- Query a dataset
SELECT *
FROM `/path/dataset-name`
WHERE column = 'value';

Create and modify an Iceberg table

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Create an empty Iceberg table
CREATE TABLE `/path/table-name` (
    id   INT,
    name STRING
)
USING iceberg;

-- Insert rows
INSERT INTO `/path/table-name`
VALUES (1, 'apple'), (2, 'pear');

-- Update rows
UPDATE `/path/table-name`
SET name = 'clementine'
WHERE id = 2;

-- Delete rows
DELETE FROM `/path/table-name`
WHERE id = 1;

Ontology object types

The following examples query ontology object types in object mode, executed by the Ontology SQL engine. Object types are referenced by their resource identifier (RID).

Query an object type

Copied!
1
2
3
SELECT employeeId, firstName, department
FROM `ri.ontology.main.object-type.<employee-rid>`
WHERE department = 'Engineering';

Query a many-to-many link

Copied!
1
2
3
4
5
6
-- Find all cars driven by a specific person
SELECT c.*
FROM `ri.ontology.main.relation.<relation-rid>` AS linkTable
INNER JOIN `car` AS c
  ON c.`carId` = linkTable.`person_vehicles`
WHERE linkTable.`car_drivers` = 'person-123';

For details on link table column conventions and other object-querying patterns, see the Ontology SQL documentation.

Troubleshooting

If you see an error such as Output folder is not enabled for Iceberg tables. Please use a different format., you may be trying to write an Iceberg table to an unsupported location. This indicates that Iceberg tables are not enabled for your environment or for the specific project location you are writing to. Modify your code to write to a different data format (for example, USING parquet), or contact Palantir Support to enable Foundry Iceberg tables.