Foundry SQL 문법과 함수 참조

Foundry SQL은 ANSI 규칙을 따르는 Spark SQL의 부분집합입니다. Furnace와 Ontology SQL이 공통 파싱 문법을 사용하지만 함수, 자료형과 쓰기 지원은 다릅니다. 객체 모드는 SELECT 조회만 지원하므로 아래 테이블 생성과 변경 문장은 Furnace의 표 데이터에 사용합니다.

문법 표기의 대괄호는 선택 항목, 중괄호 안의 세로줄은 대안, ...는 반복 가능 요소를 나타냅니다. 예시의 경로와 데이터 값은 설명용이며 실제 리소스와 스키마로 바꿉니다. 서로 다른 작업을 설명하는 예제들을 하나의 연속 실행 스크립트로 취급하지 않습니다.

자주 사용하는 문장

테이블 생성, 입력, 조회, 조인, 집계, 윈도 함수와 CTE의 기본 형태입니다.

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

이 목록이 모든 엔진에서 동일하게 지원된다는 뜻은 아닙니다. 아래 호환성 표에서 사용할 엔진의 열을 확인합니다.

테이블 생성

CREATE [ OR REPLACE ] TABLE table_identifier [ ( col_name1 col_type1, ... ) ] [ USING table_format ] [ PARTITIONED BY ( expr [, ...] ) ] [ TBLPROPERTIES ( 'key'='value' [, ...] ) ] [ AS select_statement ]

형식을 생략하면 Iceberg입니다. USING에 iceberg, parquet, avro를 지정할 수 있습니다. PARTITIONED BY는 Iceberg에만 지원하며 TBLPROPERTIES는 Iceberg 속성을 설정합니다.

-- examples
CREATE TABLE `/path/to/table` AS
SELECT *
FROM `/path/to/source`;

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

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

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

CREATE TABLE `/path/to/table` (id INT, ts TIMESTAMP)
    PARTITIONED BY (days(ts));

CREATE TABLE `/path/to/table` (id INT)
    TBLPROPERTIES ('write.format.default'='parquet');

CREATE TABLE `/path/to/table`
    PARTITIONED BY (id)
    AS SELECT * FROM `/path/to/source`;

행 추가, 수정과 삭제

INSERT, UPDATE와 DELETE는 Iceberg 테이블에서 지원합니다. 비Iceberg 테이블에는 이 행 변경 기능이 없습니다.

INSERT

VALUES로 값을 추가하거나 SELECT 결과를 삽입합니다.

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

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

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

UPDATE

WHERE를 생략하면 모든 대상 행을 수정합니다. 제한할 조건을 확인하고 실행합니다.

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

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

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

Iceberg 스키마와 메타데이터

열

열 추가, 삭제, 이름 변경, 자료형 변경, 설명과 null 제약의 설정 및 해제를 지원합니다.

ALTER TABLE table_identifier ADD COLUMN col_name col_type [COMMENT 'comment']
ALTER TABLE table_identifier ADD COLUMNS (col_name col_type [COMMENT 'comment'], ...)
ALTER TABLE table_identifier DROP COLUMN col_name
ALTER TABLE table_identifier DROP COLUMNS (col_name, ...)
ALTER TABLE table_identifier RENAME COLUMN old_name TO new_name
ALTER TABLE table_identifier ALTER COLUMN col_name TYPE new_type
ALTER TABLE table_identifier ALTER COLUMN col_name COMMENT 'comment'
ALTER TABLE table_identifier ALTER COLUMN col_name DROP NOT NULL
ALTER TABLE table_identifier ALTER COLUMN col_name SET NOT NULL

각 문장은 열과 스키마를 변경하는 별도의 예시입니다.

-- examples
ALTER TABLE `/path/to/table` ADD COLUMN new_col STRING;
ALTER TABLE `/path/to/table` ADD COLUMNS (col1 INT, col2 STRING COMMENT 'description');
ALTER TABLE `/path/to/table` DROP COLUMN old_col;
ALTER TABLE `/path/to/table` RENAME COLUMN old_name TO new_name;
ALTER TABLE `/path/to/table` ALTER COLUMN col2 TYPE BIGINT;

파티션 필드

파티션 식을 추가, 삭제하거나 교체하며 선택적으로 별칭을 지정합니다.

ALTER TABLE table_identifier ADD PARTITION FIELD expr [AS alias]
ALTER TABLE table_identifier DROP PARTITION FIELD expr
ALTER TABLE table_identifier REPLACE PARTITION FIELD old_expr WITH new_expr [AS alias]

-- examples
ALTER TABLE `/path/to/table` ADD PARTITION FIELD days(ts);
ALTER TABLE `/path/to/table` ADD PARTITION FIELD bucket(16, id) AS id_bucket;
ALTER TABLE `/path/to/table` DROP PARTITION FIELD days(ts);
ALTER TABLE `/path/to/table` REPLACE PARTITION FIELD days(ts) WITH hours(ts);

쓰기 정렬

전체 정렬, 로컬 정렬, 파티션별 분배와 선택적 로컬 정렬을 지정하거나 정렬을 해제할 수 있습니다.

ALTER TABLE table_identifier WRITE ORDERED BY col1 [ASC|DESC] [, ...]
ALTER TABLE table_identifier WRITE LOCALLY ORDERED BY col1 [ASC|DESC] [, ...]
ALTER TABLE table_identifier WRITE DISTRIBUTED BY PARTITION [LOCALLY ORDERED BY col1 [ASC|DESC] [, ...]]
ALTER TABLE table_identifier WRITE UNORDERED

-- examples
ALTER TABLE `/path/to/table` WRITE ORDERED BY col1, col2 DESC;
ALTER TABLE `/path/to/table` WRITE DISTRIBUTED BY PARTITION LOCALLY ORDERED BY col1;
ALTER TABLE `/path/to/table` WRITE UNORDERED;

식별 필드

테이블의 식별 필드를 지정하거나 해제합니다.

ALTER TABLE table_identifier SET IDENTIFIER FIELDS col1 [, ...]
ALTER TABLE table_identifier DROP IDENTIFIER FIELDS col1 [, ...]

-- examples
ALTER TABLE `/path/to/table` SET IDENTIFIER FIELDS col1, col2;
ALTER TABLE `/path/to/table` DROP IDENTIFIER FIELDS col1, col2;

테이블 속성

SET TBLPROPERTIES로 하나 이상의 속성을 지정하고 UNSET으로 제거합니다. 예시는 기본 파일 형식과 Parquet 압축을 구성합니다.

ALTER TABLE table_identifier SET TBLPROPERTIES ('key1'='value1' [, ...])
ALTER TABLE table_identifier UNSET TBLPROPERTIES ('key1' [, ...])

-- examples
ALTER TABLE `/path/to/table` SET TBLPROPERTIES ('write.format.default'='parquet');
ALTER TABLE `/path/to/table` SET TBLPROPERTIES ('write.format.default'='parquet', 'write.parquet.compression-codec'='zstd');
ALTER TABLE `/path/to/table` UNSET TBLPROPERTIES ('write.format.default');

스냅샷 태그

태그를 생성, 교체하거나 삭제할 수 있습니다. 스냅샷 버전과 보존 일수를 지정할 수 있으며 IF NOT EXISTS와 IF EXISTS 조건을 사용할 수 있습니다.

ALTER TABLE table_identifier CREATE TAG [IF NOT EXISTS] tag_name [AS OF VERSION snapshot_id] [RETAIN n DAYS]
ALTER TABLE table_identifier CREATE OR REPLACE TAG tag_name [AS OF VERSION snapshot_id] [RETAIN n DAYS]
ALTER TABLE table_identifier DROP TAG [IF EXISTS] tag_name
ALTER TABLE table_identifier REPLACE TAG tag_name [AS OF VERSION snapshot_id] [RETAIN n DAYS]

-- examples
ALTER TABLE `/path/to/table` CREATE TAG v1 AS OF VERSION 42;
ALTER TABLE `/path/to/table` CREATE TAG IF NOT EXISTS v1 AS OF VERSION 42 RETAIN 7 DAYS;
ALTER TABLE `/path/to/table` CREATE OR REPLACE TAG v2 AS OF VERSION 100;
ALTER TABLE `/path/to/table` DROP TAG IF EXISTS v1;
ALTER TABLE `/path/to/table` REPLACE TAG v1 AS OF VERSION 50 RETAIN 14 DAYS;

Iceberg 브랜치

테이블 브랜치를 생성, 교체하거나 삭제합니다. 시작 스냅샷, 브랜치 보존과 스냅샷 개수 및 기간을 지정할 수 있습니다. 이 테이블 참조는 코드 브랜치와 구분합니다.

ALTER TABLE table_identifier CREATE BRANCH [IF NOT EXISTS] branch_name [AS OF VERSION snapshot_id] [RETAIN n DAYS] [WITH SNAPSHOT RETENTION n SNAPSHOTS [n DAYS]]
ALTER TABLE table_identifier CREATE OR REPLACE BRANCH branch_name [AS OF VERSION snapshot_id] [RETAIN n DAYS] [WITH SNAPSHOT RETENTION n SNAPSHOTS [n DAYS]]
ALTER TABLE table_identifier DROP BRANCH [IF EXISTS] branch_name
ALTER TABLE table_identifier REPLACE BRANCH branch_name [AS OF VERSION snapshot_id] [RETAIN n DAYS]

-- examples
ALTER TABLE `/path/to/table` CREATE BRANCH feature_branch;
ALTER TABLE `/path/to/table` CREATE BRANCH IF NOT EXISTS my_branch AS OF VERSION 42 RETAIN 7 DAYS;
ALTER TABLE `/path/to/table` CREATE OR REPLACE BRANCH my_branch AS OF VERSION 10;
ALTER TABLE `/path/to/table` DROP BRANCH IF EXISTS old_branch;
ALTER TABLE `/path/to/table` REPLACE BRANCH my_branch AS OF VERSION 5 RETAIN 30 DAYS;

트랜잭션 블록

BEGIN과 COMMIT 사이 문장들은 하나의 원자적 실행 작업으로 묶습니다. 중첩할 수 없으며 쓰기 문장을 하나 이상 포함해야 합니다. 짝이 없는 BEGIN이나 COMMIT은 파싱 오류입니다.

BEGIN;
statement1;
statement2;
...
COMMIT;

테이블 생성과 입력을 묶거나 서로 다른 테이블의 입력을 한 실행으로 구성할 수 있습니다.

-- Create and populate a table atomically
BEGIN;
CREATE OR REPLACE TABLE `/path/to/table` (id INT, name STRING);
INSERT INTO `/path/to/table` VALUES (1, 'foo'), (2, 'bar');
COMMIT;

-- Multiple inserts into separate tables as a single job
BEGIN;
INSERT INTO `/path/to/table1` SELECT * FROM `/path/to/source`;
INSERT INTO `/path/to/table2` SELECT * FROM `/path/to/source`;
COMMIT;

쿼리 조합

WITH, 집합 연산 UNION/INTERSECT/EXCEPT, 정렬, 명명한 윈도와 제한을 조합합니다.

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

공통 테이블 식

CTE 이름, 선택적 열 이름과 괄호 안 SELECT를 지정합니다.

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

-- 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와 그룹화

SELECT [ ALL | DISTINCT ] { [ named_expression | regex_column_names | * ] [, ...] }
  FROM { from_item [, ...] }
  [ WHERE boolean_expression ]
  [ GROUP BY { ALL | { expression | ROLLUP(...) | CUBE(...) | GROUPING SETS(...) } [ , ... ] } ]
  [ HAVING boolean_expression ]
  [ LIMIT { ALL | expression } [ OFFSET expression ] ]

Furnace의 GROUP BY ALL은 SELECT에 있는 비집계 식 전체를 그룹화합니다.

SELECT region, product, SUM(revenue)
FROM `/data/sales`
GROUP BY ALL;

선택 식에는 별칭을 붙일 수 있습니다.

expression [ AS alias ]

-- examples
SELECT count(*) AS total_count FROM `/path/to/table`;

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

윈도 정의

OVER에 윈도 식을 직접 넣거나 WINDOW에서 정의한 이름을 참조합니다. PARTITION BY와 ORDER BY는 선택 항목입니다.

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

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

-- 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과 리소스 식별

파일시스템 경로나 RID를 백틱으로 감쌉니다.

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

Iceberg 브랜치를 조회할 때는 .branch_<branch name>을 붙일 수 있습니다. 특수 문자가 있는 브랜치 이름은 백틱으로 감쌉니다.

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

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

시간 이동

VERSION AS OF는 Iceberg의 스냅샷 ID 또는 Foundry 데이터셋의 트랜잭션 RID를 지정합니다.

-- Examples
SELECT * FROM `/path/to/table` VERSION AS OF `ri.foundry.main.transaction.1234`;

SELECT * FROM `/path/to/table` VERSION AS OF 1234567;

Foundry 데이터셋의 VERSION BETWEEN은 시작 트랜잭션을 제외하고 끝 트랜잭션까지 포함하여 그 사이에 추가된 행을 읽습니다. 두 경계 모두 트랜잭션 RID여야 합니다. Iceberg 스냅샷 ID를 사용할 수 없고 Iceberg 테이블에는 VERSION BETWEEN이 지원되지 않습니다. 이외 시간 이동 문법도 현재 지원하지 않습니다.

-- Rows appended after txn.1234, up to and including txn.5678
SELECT * FROM `/path/to/table`
    VERSION BETWEEN `ri.foundry.main.transaction.1234` AND `ri.foundry.main.transaction.5678`;

조인

다른 관계와 CROSS, LEFT, RIGHT 또는 FULL OUTER 조인을 구성합니다.

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

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

하위 쿼리

Foundry에서는 하위 쿼리를 반드시 괄호로 감싸야 합니다. Spark에서 일부 생략 가능한 경우라도 이 규칙을 적용합니다.

( select_statement )

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

복합 조회 예시

다음은 필터와 조인, HAVING 집계, 여러 조인을 포함한 UNION 예시입니다.

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

힌트와 메타데이터

힌트는 쿼리 엔진에 추가 지시를 제공합니다.

/*+ hint1, hint2(opts...) */

adhoc은 일회성 쓰기를 추가 정의로 남기되 활성 SQL 정의를 바꾸지 않을 때 사용합니다. CREATE OR REPLACE TABLE은 기존 정의를 교체하는 예외이므로 활성 정의를 보존할 보충 작업은 INSERT나 ALTER 같은 적절한 연산으로 구성합니다.

/*+ adhoc */
INSERT INTO `/path/to/table` SELECT * FROM `/path/to/source`;

Iceberg의 파일 및 스냅샷 메타데이터는 다음 참조로 조회할 수 있습니다.

-- examples
SELECT * FROM `/path/to/table/`.files;

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

SHOW FUNCTIONS는 name, kind와 signature를 반환합니다. kind는 scalar, aggregate, window 중 하나입니다. LIKE의 %는 임의 길이 문자열, _는 문자 하나와 일치합니다.

SHOW FUNCTIONS;
SHOW FUNCTIONS LIKE 'abs%';

함수와 연산자 지원

함수 예시의 table과 열은 실제 리소스 및 속성으로 치환합니다. 동일한 계산의 별칭도 엔진별 지원이 다를 수 있으므로 이름을 임의로 바꾸지 않습니다.

배열 함수

맵 함수

구조체 함수

날짜와 타임스탬프 함수

수학 함수

문자열 함수

조건 및 정규표현식 함수

해시 함수

자료형 변환

집계 함수

윈도 함수

윈도 함수는 OVER가 필요하며 선택적으로 파티션과 정렬을 지정합니다. 다음은 행 번호, 그룹별 순위, 누적 합계와 이름 있는 윈도입니다.

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

JSON 함수

기타 함수

공간 함수

지오메트리 열을 직접 반환할 수는 없습니다. 반환할 때는 st_astext()로 WKT 문자열로 변환합니다.

산술 연산자

불리언 연산자

기타 식

Ontology SQL의 조건 분기는 IF 대신 CASE WHEN을 사용합니다.

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

시간 간격

Furnace는 연월 및 일시분초 간격 리터럴을 지원합니다. YEAR TO MONTH는 연도와 월 부분을 함께 표현합니다.

INTERVAL { yearMonthIntervalQualifier | dayTimeIntervalQualifier }

yearMonthIntervalQualifier
 { YEAR [TO MONTH] |
   MONTH }

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

-- examples
INTERVAL '1' YEAR
INTERVAL '6' MONTH
INTERVAL '25' DAY
INTERVAL '6-0' YEAR TO MONTH

자료형

다음 표 생성 예시는 각 자료형의 선언 형태를 보여 줍니다. 특정 형식 및 엔진의 지원 범위는 위 표와 해당 테이블의 조건을 함께 적용합니다.

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