Beam Calcite SQLクエリ構文

クエリステートメントは、1つ以上のテーブルまたは式をスキャンし、計算された結果行を返します。

一般に、クエリのセマンティクスは標準的です。 Beamの統一されたバッチ/ストリーミングモデルをサポートするための拡張機能については、以下のセクションを参照してください。

• 結合
• ウィンドウ処理とトリガー

Beam SQLの主な機能は、SELECTステートメントです。これは、データをクエリおよび結合する方法です。サポートされている操作は、Apache Calcite SQL のサブセットです。

SQL 構文

query_statement:
    [ WITH with_query_name AS ( query_expr ) [, ...] ]
    query_expr

query_expr:
    { select | ( query_expr ) | query_expr set_op query_expr }
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT  [{ ALL | DISTINCT }]
        { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
            [ REPLACE ( expression [ AS ] column_name [, ...] ) ]
        | expression [ [ AS ] alias ] } [, ...]
    [ FROM from_item  [, ...] ]
    [ WHERE bool_expression ]
    [ GROUP BY { expression [, ...] } ]
    [ HAVING bool_expression ]

set_op:
    UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT

from_item: {
    table_name [ [ AS ] alias ] |
    join |
    ( query_expr ) [ [ AS ] alias ]
    with_query_name [ [ AS ] alias ]
}

join:
    from_item [ join_type ] JOIN from_item
    [ { ON bool_expression | USING ( join_column [, ...] ) } ]

join_type:
    { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

表記法

• 角括弧「[ ]」はオプションの句を示します。
• 丸括弧「( )」は、リテラルの丸括弧を示します。
• 縦棒「|」は論理ORを示します。
• 中括弧「{ }」は、オプションのセットを囲みます。
• 角括弧内のコンマとそれに続く省略記号「[, … ]」は、先行する項目をコンマ区切りのリストで繰り返すことができることを示します。

SELECT リスト

構文

SELECT  [{ ALL | DISTINCT }]
    { [ expression. ]*
    | expression [ [ AS ] alias ] } [, ...]

SELECTリストは、クエリが返す列を定義します。 SELECTリストの式は、対応するFROM句のいずれかのfrom_itemの列を参照できます。

SELECTリストの各項目は、次のいずれかです。

• *
• 式
• expression.*

SELECT *

select star と呼ばれることが多い SELECT * は、クエリ全体を実行した後に表示される列ごとに1つの出力列を生成します。

SELECT * FROM (SELECT 'apple' AS fruit, 'carrot' AS vegetable);

+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot    |
+-------+-----------+

SELECT expression

SELECTリストの項目は式にすることができます。これらの式は単一の値に評価され、オプションの明示的なaliasを使用して、1つの出力列を生成します。

式に明示的なエイリアスがない場合、可能な場合は、暗黙的なエイリアスの規則に従って暗黙的なエイリアスが与えられます。それ以外の場合、列は匿名であり、クエリ内の他の場所で名前で参照することはできません。

SELECT expression.*

SELECTリストの項目は、expression.*の形式をとることもできます。これにより、expressionの各列またはトップレベルフィールドに対して1つの出力列が生成されます。式は、テーブルエイリアスである必要があります。

次のクエリは、groceriesというテーブル（gというエイリアスが付けられています）の各列に対して1つの出力列を生成します。

WITH groceries AS
  (SELECT 'milk' AS dairy,
   'eggs' AS protein,
   'bread' AS grain)
SELECT g.*
FROM groceries AS g;

+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk  | eggs    | bread |
+-------+---------+-------+

SELECT 修飾子

次のように、SELECTクエリから返される結果を変更できます。

SELECT DISTINCT

SELECT DISTINCTステートメントは、重複する行を破棄し、残りの行のみを返します。SELECT DISTINCTは、次の型の列を返すことはできません。

• STRUCT
• ARRAY

SELECT ALL

SELECT ALLステートメントは、重複する行を含むすべての行を返します。SELECT ALLは、SELECTのデフォルトの動作です。

エイリアス

SELECTリストのエイリアスの構文と可視性の詳細については、エイリアスを参照してください。

FROM 句

FROM句は、行を取得するテーブルを指定し、それらの行を結合して、クエリの残りの部分で処理するために1つの行ストリームを生成する方法を指定します。

構文

from_item: {
    table_name [ [ AS ] alias ] |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    with_query_name [ [ AS ] alias ]
}

table_name

既存のテーブルの名前（オプションで修飾されます）。

SELECT * FROM Roster;
SELECT * FROM beam.Roster;

join

以下のJOIN タイプと結合を参照してください。

select

( select ) [ [ AS ] alias ]はテーブルサブクエリです。

with_query_name

WITH句のクエリ名（WITH 句を参照）は、FROM句内の任意の場所で参照できる一時テーブルの名前のように機能します。以下の例では、subQ1とsubQ2はwith_query_namesです。

例

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;

WITH句は、テーブル名を修飾しない限り（例：beam.Roster）、クエリの実行中に同じ名前の永続的なテーブルを非表示にします。

サブクエリ

サブクエリとは、別のステートメント内に表示され、括弧内に記述されるクエリです。これらは「サブSELECT」または「ネストされたSELECT」とも呼ばれます。サブクエリでは、完全なSELECT構文が有効です。

サブクエリには2つのタイプがあります。

• 式サブクエリ。式が有効な場合は、クエリ内の任意の場所で使用できます。式サブクエリは、単一の値を返します。
• テーブルサブクエリ。FROM句でのみ使用できます。外部クエリは、サブクエリの結果をテーブルとして扱います。

両方のタイプのサブクエリの周りに括弧が必要であることに注意してください。

例

SELECT AVG ( PointsScored )
FROM
( SELECT PointsScored
  FROM Stats
  WHERE SchoolID = 77 )

オプションで、テーブルサブクエリにはエイリアスを付けることができます。

例

SELECT r.LastName
FROM
( SELECT * FROM Roster) AS r;

エイリアス

FROM句のエイリアスの構文と可視性の詳細については、エイリアスを参照してください。

JOIN タイプ

また、結合も参照してください。

構文

join:
    from_item [ join_type ] JOIN from_item
    [ ON bool_expression | USING ( join_column [, ...] ) ]

join_type:
    { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

JOIN句は2つのfrom_itemをマージして、SELECT句が1つのソースとしてそれらをクエリできるようにします。 join_typeとONまたはUSING句（「結合条件」）は、2つのfrom_itemから行を結合および破棄して、単一のソースを形成する方法を指定します。

すべてのJOIN句にはjoin_typeが必要です。

JOIN句には、次のいずれかの条件が当てはまる場合を除き、結合条件が必要です。

• join_typeがCROSSである。
• from_itemの1つ以上がテーブルではない場合（例：array_pathまたはfield_path）。

[INNER] JOIN

INNER JOIN（または単にJOIN）は、2つのfrom_itemのデカルト積を実質的に計算し、結合条件を満たさないすべての行を破棄します。「実質的に」とは、実際にはデカルト積を計算せずにINNER JOINを実装できることを意味します。

CROSS JOIN

CROSS JOINは、一般的にまだサポートされていません。

FULL [OUTER] JOIN

FULL OUTER JOIN（または単にFULL JOIN）は、結合条件を満たす両方のfrom_itemのすべての行のすべてのフィールドを返します。

FULLは、結合条件を満たさない場合でも、両方のfrom_itemからすべての行が返されることを示します。ストリーミングジョブの場合、グローバルウィンドウが適用されていない場合は、同じウィンドウに属し、デフォルトのトリガーに従って遅延していないすべての行が返されます。

OUTERは、一方のfrom_itemの特定の行が他方のfrom_itemのどの行にも結合しない場合、その行は他方のfrom_itemのすべての列に対してNULLで返されることを示します。

また、結合も参照してください。

LEFT [OUTER] JOIN

2つのfrom_itemに対するLEFT OUTER JOIN（または単にLEFT JOIN）の結果は、右側のfrom_itemの行が結合述語を満たさない場合でも、常にJOIN句の左側のfrom_itemのすべての行を保持します。

LEFT は、左側の from_item からのすべての行が返されることを示します。左側の from_item からの特定の行が右側の from_item のどの行にも結合しない場合、その行は右側の from_item のすべての列に対して NULL を返します。左側の from_item のどの行にも結合しない右側の from_item からの行は破棄されます。

RIGHT [OUTER] JOIN

RIGHT OUTER JOIN (または単に RIGHT JOIN) の結果は、LEFT OUTER JOIN の結果と同様に対称的です。

ON 句

ON 句には、bool_expression が含まれます。結合された行（2つの行を結合した結果）は、bool_expression が TRUE を返す場合に結合条件を満たします。

例

SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

USING 句

USING 句は、両方の入力テーブルに存在する1つ以上の列の column_list を必要とします。この列に対して等価比較が実行され、等価比較が TRUE を返す場合に、行は結合条件を満たします。

ほとんどの場合、USING キーワードを使用したステートメントは、ON キーワードを使用するのと同じです。たとえば、以下のステートメントは

SELECT FirstName
FROM Roster INNER JOIN PlayerStats
USING (LastName);

以下と同等です。

SELECT FirstName
FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

SELECT * を使用する場合、USING を使用したクエリの結果は、ON を使用したクエリの結果とは異なります。これを示すために、次のクエリを考えてみましょう。

SELECT * FROM Roster INNER JOIN PlayerStats
USING (LastName);

このステートメントは、Roster.LastName が PlayerStats.LastName と同じである Roster および PlayerStats からの行を返します。結果には単一の LastName 列が含まれます。

対照的に、次のクエリを考えてみましょう。

SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

このステートメントは、Roster.LastName が PlayerStats.LastName と同じである Roster および PlayerStats からの行を返します。結果には、Roster からの 1 つと PlayerStats からの 1 つの、2つの LastName 列が含まれます。

JOIN のシーケンス

FROM 句には、複数の JOIN 句を連続して含めることができます。

例

SELECT * FROM a LEFT JOIN b ON TRUE LEFT JOIN c ON TRUE;

ここで、a、b、および c は、任意の from_item です。JOIN は左から右にバインドされますが、括弧を挿入して異なる順序でグループ化できます。

WHERE 句

構文

WHERE bool_expression

WHERE 句は、各行を bool_expression に対して評価することによって行をフィルタリングし、TRUE を返さないすべての行（つまり、FALSE または NULL を返す行）を破棄します。

例

SELECT * FROM Roster
WHERE SchoolID = 52;

bool_expression には、複数のサブ条件を含めることができます。

例

SELECT * FROM Roster
WHERE LastName LIKE 'Mc%' OR LastName LIKE 'Mac%';

WHERE 句では、SELECT リストの列エイリアスを参照することはできません。

INNER JOIN 内の式は、WHERE 句に同等の式を持ちます。たとえば、INNER JOIN および ON を使用したクエリには、CROSS JOIN および WHERE を使用した同等の式があります。

例 - このクエリ

SELECT * FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

以下と同等です。

SELECT * FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

GROUP BY 句

ウィンドウ処理とトリガー処理も参照してください

構文

GROUP BY { expression [, ...] }

GROUP BY 句は、GROUP BY 句の expression に対して重複しない値を持つテーブル内の行をグループ化します。expression に対して重複しない値を持つソーステーブル内の複数の行に対して、GROUP BY 句は単一の結合された行を生成します。GROUP BY は、集計関数が SELECT リストに存在する場合、または出力の冗長性を排除するために一般的に使用されます。

例

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;

HAVING 句

構文

HAVING bool_expression

HAVING 句は WHERE 句に似ています。bool_expression に対して評価されたときに TRUE を返さない行をフィルタリングします。

WHERE 句と同様に、bool_expression はブール値を返す任意の式にすることができ、複数のサブ条件を含めることができます。

HAVING 句が WHERE 句と異なる点は次のとおりです。

• HAVING 句には、クエリに GROUP BY または集計が存在する必要があります。
• HAVING 句は、GROUP BY および集計の後に発生します。これは、HAVING 句が、結果セット内の集計されたすべての行に対して一度評価されることを意味します。これは、GROUP BY および集計の前に評価される WHERE 句とは異なります。

HAVING 句は、FROM 句を介して利用可能な列と、SELECT リストのエイリアスを参照できます。HAVING 句で参照される式は、GROUP BY 句に現れるか、集計関数の結果である必要があります。

SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

集合演算子

構文

UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT

セット演算子は、2つ以上の入力クエリの結果を単一の結果セットに結合します。ALL または DISTINCT を指定する必要があります。ALL を指定した場合、すべての行が保持されます。DISTINCT が指定された場合、重複する行は破棄されます。

特定の行 R が、最初の入力クエリに正確に m 回、2番目の入力クエリに n 回出現する場合（m >= 0、n >= 0）

• UNION ALL の場合、R は結果に正確に m + n 回出現します。
• UNION DISTINCT の場合、DISTINCT は UNION の計算後に計算されるため、R は正確に1回出現します。
• INTERSECT DISTINCT の場合、DISTINCT は上記の計算結果の後に計算されます。
• EXCEPT DISTINCT の場合、m > 0 かつ n = 0 の場合、行 R は出力に1回出現します。
• 3つ以上の入力クエリがある場合、上記の操作は一般化され、出力は入力が左から右に増分的に結合された場合と同じになります。

次のルールが適用されます。

• UNION ALL 以外のセット演算の場合、すべての列型が等価比較をサポートする必要があります。
• 演算子の両側の入力クエリは、同じ数の列を返す必要があります。
• 演算子は、各入力クエリによって返される列を、それぞれの SELECT リスト内の列の位置に従ってペアにします。つまり、最初の入力クエリの最初の列は、2番目の入力クエリの最初の列とペアになります。
• 結果セットは常に、最初の入力クエリからの列名を使用します。
• 結果セットは常に、対応する列の入力型のスーパータイプを使用するため、ペアになっている列も同じデータ型または共通のスーパータイプを持つ必要があります。
• 異なるセット演算を区切るには括弧を使用する必要があります。この目的のために、UNION ALL や UNION DISTINCT などのセット演算は異なります。ステートメントが同じセット演算を繰り返すだけの場合は、括弧は不要です。

例

query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3

無効

query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3;  // INVALID.

UNION

UNION 演算子は、各クエリの結果セットからの列をペアにして垂直に連結することにより、2つ以上の入力クエリの結果セットを結合します。

INTERSECT

INTERSECT 演算子は、左および右の両方の入力クエリの結果セットにある行を返します。EXCEPT とは異なり、入力クエリの位置（INTERSECT 演算子の左側か右側か）は重要ではありません。

EXCEPT

EXCEPT 演算子は、右側の入力クエリに存在しない左側の入力クエリからの行を返します。

LIMIT 句と OFFSET 句

構文

LIMIT count [ OFFSET skip_rows ]

LIMIT は、INTEGER 型の負でない count を指定し、count 行以下の行が返されます。LIMIT 0 は 0 行を返します。セット演算がある場合、LIMIT はセット演算が評価された後に適用されます。

OFFSET は、INTEGER 型の負でない skip_rows を指定し、テーブル内のそのオフセットからの行のみが考慮されます。

これらの句は、リテラル値またはパラメーター値のみを受け入れます。

LIMIT および OFFSET によって返される行は指定されていません。

WITH 句

WITH 句には、後続の SELECT ステートメントが参照するたびに実行される、1つ以上の名前付きサブクエリが含まれています。任意の句またはサブクエリは、WITH 句で定義したサブクエリを参照できます。これには、UNION などのセット演算子の両側にある任意の SELECT ステートメントが含まれます。

例

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;

エイリアス

エイリアスは、クエリに存在するテーブル、列、または式に与えられた一時的な名前です。SELECT リストまたは FROM 句で明示的なエイリアスを導入することも、Beam が一部の式に対して暗黙的なエイリアスを推測することもできます。明示的なエイリアスも暗黙的なエイリアスも持たない式は匿名であり、クエリは名前でそれらを参照することはできません。

明示的なエイリアス構文

明示的なエイリアスは、FROM 句または SELECT リストのいずれかで導入できます。

FROM 句では、[AS] alias を使用して、テーブル、配列、サブクエリ、UNNEST 句を含む、任意のアイテムに明示的なエイリアスを導入できます。AS キーワードはオプションです。

例

SELECT s.FirstName, s2.SongName
FROM Singers AS s JOIN Songs AS s2 ON s.SingerID = s2.SingerID;

[AS] alias を使用して、SELECT リスト内の任意の式に明示的なエイリアスを導入できます。AS キーワードはオプションです。

例

SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;

明示的なエイリアスの可視性

クエリで明示的なエイリアスを導入した後、クエリの他の場所でそのエイリアスを参照できる場所に制限があります。エイリアスの可視性に関するこれらの制限は、Beam の名前スコープ規則の結果です。

FROM 句のエイリアス

Beam は FROM 句のエイリアスを左から右に処理し、エイリアスは後続の JOIN 句にのみ可視です。

あいまいなエイリアス

Beam は、名前があいまいである場合、つまり、複数の固有のオブジェクトに解決できる場合、エラーを提供します。

例

このクエリには、Singers と Songs の両方に SingerID という名前の列があるため、テーブル間で競合する列名が含まれています

SELECT SingerID
FROM Singers, Songs;

暗黙的なエイリアス

SELECT リストで、明示的なエイリアスを持たない式がある場合、Beam は次の規則に従って暗黙的なエイリアスを割り当てます。SELECT リストに同じエイリアスを持つ複数の列を含めることができます。

• 識別子の場合、エイリアスは識別子です。たとえば、SELECT abc は AS abc を意味します。
• パス式の場合、エイリアスはパス内の最後の識別子です。たとえば、SELECT abc.def.ghi は AS ghi を意味します。
• 「ドット」メンバーフィールドアクセス演算子を使用したフィールドアクセスの場合、エイリアスはフィールド名です。たとえば、SELECT (struct_function()).fname は AS fname を意味します。

他のすべての場合、暗黙的なエイリアスはないため、列は匿名であり、名前で参照することはできません。その列からのデータは引き続き返され、表示されるクエリ結果にはその列の生成されたラベルが表示される場合がありますが、ラベルをエイリアスのように使用することはできません。

FROM 句では、from_item にエイリアスは必須ではありません。次のルールが適用されます。

明示的なエイリアスを持たない式がある場合、Beam はこれらの場合に暗黙的なエイリアスを割り当てます。

• 識別子の場合、エイリアスは識別子です。たとえば、FROM abc は AS abc を意味します。
• パス式の場合、エイリアスはパス内の最後の識別子です。たとえば、FROM abc.def.ghi は AS ghi を意味します

テーブルサブクエリには暗黙的なエイリアスはありません。

FROM UNNEST(x) には暗黙的なエイリアスはありません。

このページの文章の一部は、作成およびGoogle によって共有された作業に基づいて変更されており、クリエイティブコモンズ 3.0 帰属ライセンスに記載されている条件に従って使用されています。