SQL dialect

Foundry SQL is generally a subset of Spark SQL with ANSI compliance.

Quick reference

Common operations

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Create a table
CREATE TABLE `/path/to/table` (id INT, name STRING);

-- Insert data
INSERT INTO `/path/to/table` VALUES (1, 'foo'), (2, 'bar');

-- Basic query
SELECT * FROM `/path/to/table` WHERE id = 1;

-- Join tables
SELECT a.*, b.name
FROM `/path/to/table1` a
LEFT JOIN `/path/to/table2` b ON a.id = b.id;

-- Aggregate
SELECT category, COUNT(*), AVG(price)
FROM `/path/to/products`
GROUP BY category
HAVING COUNT(*) > 10;

-- Window function
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) as rank
FROM `/path/to/employees`;

-- CTE (Common Table Expression)
WITH summary AS (
  SELECT category, SUM(amount) as total
  FROM `/path/to/sales`
  GROUP BY category
)
SELECT * FROM summary WHERE total > 1000;

Commonly used functions

Table references

Copied!
1
2
3
4
5
6
7
8
-- By path
SELECT * FROM `/path/to/table`;

-- By RID
SELECT * FROM `ri.rid.for.table.1234`;

-- With branch
SELECT * FROM `/path/to/table`.branch_master;

Key syntax notes

• Subqueries must be wrapped in parentheses.
• Created tables are Iceberg by default but can be overridden with USING <format> syntax.
• Only Iceberg tables support INSERT, UPDATE, and DELETE.

Table creation

All tables will be created as Iceberg tables.

CREATE [ OR REPLACE ] [ ( col_name1 col_type1, ... ) ] [ USING table_format ] [ AS select_statement ]

If not specified, the table format will be Iceberg. Valid table formats include:

• Iceberg: iceberg
• Parquet: parquet
• Avro: avro

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- examples
CREATE TABLE `/path/to/table` AS
SELECT *
FROM `/path/to/table`;

CREATE TABLE `/path/to/table` USING parquet AS
SELECT *
FROM `/path/to/table`;

CREATE TABLE `/path/to/table`
(
    id   INT,
    name STRING
);

CREATE OR REPLACE TABLE `/path/to/table` (id INT, name STRING);

Table alteration

Iceberg tables support data appends with INSERT, UPDATE, and DELETE statements. Non-Iceberg tables do not currently support table alteration.

Insert

INSERT [ INTO ] table_identifier { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }

Copied!
1
2
3
4
-- examples
INSERT INTO `/path/to/table` VALUES (1, 'foo'), (2, 'bar');

INSERT INTO `/path/to/table` SELECT id, name FROM `/path/to/table2`;

Update

UPDATE table_identifier
SET column_name = value [, ...]
[ WHERE condition ]

Copied!
1
2
3
4
5
6
7
-- examples
UPDATE `/path/to/table`
SET col = 'new value';

UPDATE `/path/to/table`
SET col = 'new value'
WHERE col = 'old value';

Delete

DELETE FROM table_identifier WHERE condition

Copied!
1
DELETE FROM `/path/to/table` WHERE col = 'to delete'

Query composition

[ WITH with_query [ , ... ] ]
select_statement
  [ { UNION | INTERSECT | EXCEPT } select_statement, ... ]
  [ ORDER BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] } ]
  [ WINDOW { window_name AS ( window_spec ) [, ...] } ]
  [ LIMIT { ALL | expression } ]

WITH clause (common table expressions)

Where with_query is:

cte_name [ ( column_name [, ...] ) ] AS ( select_statement )

Copied!
1
2
3
4
5
6
7
-- examples
WITH sales_summary (cust_id, total_sales, order_count) AS (
  SELECT customer_id, SUM(total_amount), COUNT(*)
  FROM `/data/orders`
  GROUP BY customer_id
)
SELECT * FROM sales_summary WHERE total_sales > 10000;

SELECT statement

Where select_statement is:

SELECT [ ALL | DISTINCT ] { [ named_expression | regex_column_names | * ] [, ...] }
  FROM { from_item [, ...] }
  [ WHERE boolean_expression ]
  [ GROUP BY expression [ , ... ] ]
  [ HAVING boolean_expression ]
  [ LIMIT { ALL | expression } [ OFFSET expression ] ]

Named expressions

Where named_expression is one of:

Expression with optional alias:

expression [ AS alias ]

Copied!
1
2
3
4
-- examples
SELECT count(*) AS total_count FROM `/path/to/table`;

SELECT name, salary * 1.1 AS adjusted_salary FROM `/path/to/employees`;

Window function:

window_function() OVER ( window_spec )
window_function() OVER window_name

Where window_spec is:

[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
-- examples
SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM `/path/to/employees`;

SELECT category, product_id,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM `/path/to/products`;

-- Using named window
SELECT customer_id, order_date, total,
       ROW_NUMBER() OVER w as order_sequence
FROM `/path/to/orders`
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);

FROM clause

Where from_item is one of:

Table reference

Tables can be referenced by filesystem path or resource identifier (RID). Both support optional branch specifications for querying specific Iceberg branches. Branch names that contain special characters need to be escaped with backticks.

{ `/path/to/table`[.branch_<branch name>] | `ri.rid.for.table.1234`[.branch_<branch name>] }

Copied!
1
2
3
4
5
6
7
8
9
10
-- examples
SELECT * FROM `/path/to/table`;

SELECT * FROM `/path/to/table`.branch_master;

SELECT * FROM `/path/to/table` WHERE id = 1;

SELECT * FROM `ri.rid.for.table.1234`;

SELECT * FROM `ri.rid.for.table`.`branch_abc\xyz`;

Join

relation
  { CROSS | LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ] }
  JOIN relation
  ON boolean_expression

Copied!
1
2
3
4
-- examples
SELECT * FROM `/path/to/table` LEFT JOIN `/path/to/table2` ON id = id2;

SELECT * FROM `/path/to/table` RIGHT OUTER JOIN `/path/to/table2` ON id = id2;

Subquery

( select_statement )

Copied!
1
2
3
4
5
6
7
8
9
-- examples
SELECT * FROM (SELECT * FROM `/path/to/table`);

SELECT * FROM (SELECT * FROM `/path/to/table`) WHERE id = 1;

SELECT * FROM (
  VALUES (9001, 1001, 1, DATE '2023-06-01', 'credit_card', 1329.98, 'completed',
          TIMESTAMP '2023-06-01 11:00:00')
) AS orders(order_id, customer_id, product_id, order_date, payment_method, total, status, created_at);

Complete query examples

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Complex query with joins and filtering
SELECT DISTINCT o.order_id,
                c.name,
                o.total_amount,
                p.category
FROM `/data/orders` o
         LEFT JOIN `/data/customers` c ON o.customer_id = c.id
         CROSS JOIN (SELECT product_id, category
                     FROM `/data/products`
                     WHERE category = 'Electronics') p ON o.product_id = p.product_id
WHERE o.order_date > DATE '2024-01-01'
  AND o.total_amount > 1000.0
ORDER BY o.total_amount DESC
LIMIT 100;

-- Aggregation with HAVING clause
SELECT c.name,
       COUNT(o.order_id) as order_count,
       SUM(o.total_amount) as total_spent
FROM `/data/customers` c
       LEFT JOIN `/data/orders` o ON c.id = o.customer_id
GROUP BY c.name
HAVING COUNT(o.order_id) > 0;

-- UNION query with multiple joins
SELECT emp_id, name, dept, 'Active' AS status
FROM `/hr/employees` e
         LEFT OUTER JOIN `/hr/departments` d ON e.dept_id = d.id
WHERE e.active = true
UNION
SELECT emp_id, name, dept, 'Inactive' AS status
FROM (SELECT *
      FROM `/hr/employees`
      WHERE termination_date < current_timestamp()) e
         RIGHT JOIN `/hr/departments` d ON e.dept_id = d.id
WHERE e.active = false
ORDER BY dept ASC, status DESC NULLS LAST;

Iceberg references

Foundry SQL supports Iceberg references to query metadata. Review the official documentation ↗.

Copied!
1
2
3
4
-- examples
SELECT * FROM `/path/to/table/`.files;

SELECT * FROM `/path/to/table/`.snapshots;

Functions

Functions are available to transforms data in more complex ways. Refer to the Spark SQL documentation ↗ for detailed information on each function below.

Array functions

Map functions

Struct functions

Date and timestamp functions

Mathematical functions

String functions

Conditional functions

Conversion functions

Aggregate functions

Window functions

Window functions require an OVER clause with optional PARTITION BY and ORDER BY:

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- examples
SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) as row_num
FROM `/path/to/table`;

SELECT category, name, price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category
FROM `/path/to/products`;

SELECT order_id, customer_id, order_date, total_amount,
       SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total,
        AVG(total_amount) OVER (PARTITION BY customer_id) as customer_avg,
        COUNT(*) OVER (PARTITION BY customer_id) as customer_order_count
FROM `/path/to/orders`;

SELECT id, amount,
       ROW_NUMBER() OVER w as sequence_num
FROM `/path/to/transactions`
WINDOW w AS (PARTITION BY customer_id ORDER BY transaction_date);

Misc functions

Arithmetic operators

Boolean operators

Other expressions

Case-when statements

Copied!
1
2
3
4
CASE WHEN condition THEN result
     [WHEN condition THEN result]
     [ELSE result]
END

Date/time intervals

Copied!
1
2
3
4
5
6
7
8
9
10
11
INTERVAL { yearMonthIntervalQualifier | dayTimeIntervalQualifier }

yearMonthIntervalQualifier
 { YEAR [TO MONTH] |
   MONTH }

dayTimeIntervalQualifier
 { DAY [TO { HOUR | MINUTE | SECOND } ] |
   HOUR [TO { MINUTE | SECOND } ] |
   MINUTE [TO SECOND] |
   SECOND }

Copied!
1
2
3
4
5
-- examples
INTERVAL '1' YEAR
INTERVAL '6' MONTH
INTERVAL '25' DAY
INTERVAL '6' YEAR TO MONTH

Data types

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `/path/to/table` (
  col1 BOOLEAN,
  col2 SHORT,
  col3 INT,
  col4 LONG,
  col5 FLOAT,
  col6 DOUBLE,
  col7 DATE,
  col8 TIMESTAMP,
  col9 TIMESTAMP_NTZ,
  col10 STRING,
  col11 BINARY,
  col12 DECIMAL(10, 2),
  col13 MAP<STRING, STRING>,
  col14 ARRAY<STRING>,
  col15 STRUCT<col1: STRING, col2: STRING>
);