SQL 方言

Foundry SQL は、おおむね ANSI に準拠した Spark SQL ↗ のサブセットです。

クイックリファレンス

一般的な操作

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 TABLE `/path/to/table` (id INT, name STRING);

-- データを挿入する
INSERT INTO `/path/to/table` VALUES (1, 'foo'), (2, 'bar');

-- 基本的なクエリ
SELECT * FROM `/path/to/table` WHERE id = 1;

-- テーブルを結合する
SELECT a.*, b.name
FROM `/path/to/table1` a
LEFT JOIN `/path/to/table2` b ON a.id = b.id;

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

-- ウィンドウ関数
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) as rank
FROM `/path/to/employees`;

-- CTE（共通テーブル式）
WITH summary AS (
  SELECT category, SUM(amount) as total
  FROM `/path/to/sales`
  GROUP BY category
)
SELECT * FROM summary WHERE total > 1000;

よく使う関数

テーブル参照

Copied!
1
2
3
4
5
6
7
8
-- パスによる指定
SELECT * FROM `/path/to/table`;

-- RID による指定
SELECT * FROM `ri.rid.for.table.1234`;

-- ブランチを指定
SELECT * FROM `/path/to/table`.branch_master;

構文に関する重要な注意事項

• サブクエリは必ず括弧で囲む必要があります。
• 作成されるテーブルはデフォルトで Iceberg ですが、USING <format> 構文で変更できます。
• INSERT、UPDATE、DELETE に対応しているのは Iceberg テーブルのみです。

テーブルの作成

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

PARTITIONED BY は Iceberg テーブルでのみ利用できます。TBLPROPERTIES は Iceberg テーブルのプロパティ ↗を設定します。

値を指定しない場合は、テーブル形式は Iceberg になります。有効なテーブル形式は次のとおりです。

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

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

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

テーブルの変更

Iceberg テーブルは、INSERT、UPDATE、DELETE 文によるデータの追加に対応しています。Iceberg 以外のテーブルは、現在テーブルの変更に対応していません。

挿入

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

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

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

更新

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

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

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

削除

DELETE FROM table_identifier WHERE condition

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

メタデータを変更する

Iceberg テーブルは、ALTER TABLE 構文によるスキーマとメタデータの変更に対応しています。詳細については、Iceberg Spark DDL ドキュメント ↗を参照してください。

列の操作

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 SET NOT NULL
ALTER TABLE table_identifier ALTER COLUMN col_name DROP NOT NULL

Copied!
1
2
3
4
5
6
-- 例
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]

Copied!
1
2
3
4
5
-- 例
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

Copied!
1
2
3
4
-- 例
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 [, ...]

Copied!
1
2
3
-- 例
ALTER TABLE `/path/to/table` SET IDENTIFIER FIELDS col1, col2;
ALTER TABLE `/path/to/table` DROP IDENTIFIER FIELDS col1, col2;

テーブルのプロパティ

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

Copied!
1
2
3
4
-- 例
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');

タグ

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]

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

ブランチ

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]

Copied!
1
2
3
4
5
6
-- 例
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;
statement1;
statement2;
...
COMMIT;

ルール：

• BEGIN/COMMIT 内のすべての文は、単一のジョブでまとめて実行されます。
• トランザクションブロックはネストできません。
• トランザクションブロックには、書き込み文が1つ以上必要です。
• COMMIT のない BEGIN、または BEGIN のない COMMIT は、パースエラーになります。

Copied!
1
2
3
4
5
6
7
8
9
10
11
-- テーブルの作成とデータの挿入をアトミックに実行する
BEGIN;
CREATE OR REPLACE TABLE `/path/to/table` (id INT, name STRING);
INSERT INTO `/path/to/table` VALUES (1, 'foo'), (2, 'bar');
COMMIT;

-- 別々のテーブルへの複数の挿入を単一のジョブとして実行する
BEGIN;
INSERT INTO `/path/to/table1` SELECT * FROM `/path/to/source`;
INSERT INTO `/path/to/table2` SELECT * FROM `/path/to/source`;
COMMIT;

クエリの構成

[ 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 句（共通テーブル式）

with_query は次のとおりです。

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

Copied!
1
2
3
4
5
6
7
-- 例
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_statement は次のとおりです。

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 リスト内のすべての非集計式でグループ化できます。

Copied!
1
2
3
SELECT region, product, SUM(revenue)
FROM `/data/sales`
GROUP BY ALL;

名前付き式

named_expression は次のいずれかです。

任意のエイリアスを持つ式：

expression [ AS alias ]

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

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

ウィンドウ関数：

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

window_spec は次のとおりです。

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

-- 名前付きウィンドウを使用する
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 句

from_item は次のいずれかです。

テーブル参照

テーブルは、ファイルシステムのパスまたはリソース識別子 (RID) で参照できます。どちらも任意でブランチを指定でき、特定の Iceberg ブランチをクエリできます。特殊文字を含むブランチ名は、バッククォートでエスケープする必要があります。

{ `/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
-- 例
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 を使用します。

Copied!
1
2
3
4
-- 例
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 ... AND ... を使用して、トランザクションの範囲内で追加された行を読み取れます。このクエリは、開始トランザクションより後から終了トランザクションまでに追加された行を返します。開始境界のトランザクションは含まれず、終了境界のトランザクションは含まれます。両方の境界には、スナップショットIDではなくトランザクション RID を指定する必要があります。

Copied!
1
2
3
-- txn.1234 より後から txn.5678 まで（txn.5678 を含む）に追加された行
SELECT * FROM `/path/to/table`
    VERSION BETWEEN `ri.foundry.main.transaction.1234` AND `ri.foundry.main.transaction.5678`;

Iceberg テーブルでは VERSION BETWEEN を利用できません。ほかのタイムトラベル構文には、現在対応していません。

結合

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

Copied!
1
2
3
4
-- 例
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;

サブクエリ

( select_statement )

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

完全なクエリの例

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
-- 結合と絞り込みを含む複雑なクエリ
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;

-- HAVING 句を使用した集計
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 クエリ
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;

SQL ヒント

SQL ヒントは、クエリエンジンに追加の指示を与えます。ヒントは次の構文で指定します。

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

現在、次のヒントに対応しています。

• adhoc：1回限りの書き込みを実行し、アクティブな定義を変更せずに、追加の SQL 定義として保持します。

Copied!
1
2
/*+ adhoc */
CREATE OR REPLACE TABLE `/path/to/table` AS SELECT * FROM `/path/to/source`;

Iceberg 参照

Foundry SQL は、メタデータをクエリするための Iceberg 参照に対応しています。公式ドキュメント ↗を確認してください。

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

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

イントロスペクション

SHOW FUNCTIONS を使用すると、Foundry SQL が対応している関数を一覧表示できます。この文は、name、kind（scalar、aggregate、window のいずれか）、signature の3列を返します。任意の LIKE パターンを指定すると、関数名で絞り込めます。% は任意の文字列に一致し、_ は1文字に一致します。

Copied!
1
2
SHOW FUNCTIONS;
SHOW FUNCTIONS LIKE 'abs%';

関数

関数を使用すると、より複雑な方法でデータを加工できます。以下の各関数の詳細については、Spark SQL ドキュメント ↗を参照してください。

配列関数

マップ関数

構造体関数

日付とタイムスタンプの関数

数学関数

文字列関数

条件関数

ハッシュ関数

変換関数

集計関数

ウィンドウ関数

ウィンドウ関数には OVER 句が必須で、PARTITION BY と ORDER BY は任意です。

Copied!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 例
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 関数

その他の関数

地理空間関数

算術演算子

ブール演算子

その他の式

Case-when 文

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

日付/時刻の間隔

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
-- 例
INTERVAL '1' YEAR
INTERVAL '6' MONTH
INTERVAL '25' DAY
INTERVAL '6' YEAR TO MONTH

データ型

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