SQL dialect

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

Table creation

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
17
-- 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 } ]

Where with_query is:

cte_name [ ( column_name [, ...] ) ] AS ( 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 ] ]

Where named_expression is:

• Expression (with 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

  See Window Functions section for available functions.

  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);

Where from_item is:

• Table

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

  Copied!
  1
  2
  3
  4
  5
  6
  7
  8
  -- 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`;

• 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
  -- examples
  SELECT * FROM (SELECT * FROM `/path/to/table`);
  
  SELECT * FROM (SELECT * FROM `/path/to/table`) WHERE id = 1;
  
  VALUES (9001, 1001, 1, DATE '2023-06-01', 'credit_card', 1329.98, 'completed', TIMESTAMP '2023-06-01 11:00:00')

  Note: Subqueries must be wrapped in parentheses - this is not always required in Spark SQL, but is enforced in Foundry.

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
38
39
40
41
-- examples
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;

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;

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_time()) e
         RIGHT JOIN `/hr/departments` d ON e.dept_id = d.id
WHERE e.active = false
ORDER BY dept ASC, status DESC NULLS LAST;

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;

Iceberg references

Foundry SQL supports Iceberg references to query metadata. See 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

Misc 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);

Arithmetic operators

Boolean operators

Other expressions

Case-when statements

CASE WHEN condition THEN result
     [WHEN condition THEN result]
     [ELSE result]
END

Date/time intervals

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>
);