クエリサービスのSQL構文

Adobe Experience Platformクエリサービスは、SELECT文やその他の制限付きコマンドに標準のANSI SQLを使用する機能を提供します。 このドキュメントでは、Query ServiceでサポートされるSQL構文について説明します。

SELECTクエリ

次の構文は、Query ServiceでサポートされるSELECTクエリを定義します。

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT [( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ SNAPSHOT { SINCE start_snapshot_id | AS OF end_snapshot_id | BETWEEN start_snapshot_id AND end_snapshot_id } ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]

ここで、from_itemは次のオプションのいずれかです。

table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

grouping_elementは、次のいずれかのオプションになります。

( )
expression
( expression [, ...] )
ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
CUBE ( { expression | ( expression [, ...] ) } [, ...] )
GROUPING SETS ( grouping_element [, ...] )

with_query は以下になります。

 with_query_name [ ( column_name [, ...] ) ] AS ( select | values )

以下のサブセクションでは、上記の形式に従う場合に、クエリで使用できる追加の句の詳細を示します。

SNAPSHOT句

この句は、スナップショットIDに基づいてテーブル上のデータを増分的に読み取るために使用できます。 スナップショットIDは、データが書き込まれるたびにデータレイクテーブルに適用される、長い型の番号で表されるチェックポイントマーカーです。 SNAPSHOT句は、その隣に使用されるテーブルの関係にそれ自体を付加します。

    [ SNAPSHOT { SINCE start_snapshot_id | AS OF end_snapshot_id | BETWEEN start_snapshot_id AND end_snapshot_id } ]

SELECT * FROM Customers SNAPSHOT SINCE 123;

SELECT * FROM Customers SNAPSHOT AS OF 345;

SELECT * FROM Customers SNAPSHOT BETWEEN 123 AND 345;

SELECT * FROM Customers SNAPSHOT BETWEEN HEAD AND 123;

SELECT * FROM Customers SNAPSHOT BETWEEN 345 AND TAIL;

SELECT * FROM (SELECT id FROM CUSTOMERS BETWEEN 123 AND 345) C 

SELECT * FROM Customers SNAPSHOT SINCE 123 INNER JOIN Inventory AS OF 789 ON Customers.id = Inventory.id;

SNAPSHOT句はテーブルまたはテーブルのエイリアスで使用できますが、サブクエリやビューの上にはありません。 SNAPSHOT句は、テーブルのSELECTクエリを適用できる場所であればどこでも機能します。

また、スナップショット句の特別なオフセット値としてHEADTAILを使用することもできます。 HEADを使用すると最初のスナップショットの前のオフセットを参照し、 TAILは最後のスナップショットの後のオフセットを参照します。

WHERE句

デフォルトでは、SELECTクエリのWHERE句で生成された一致では、大文字と小文字が区別されます。 一致で大文字と小文字を区別しない場合は、LIKEの代わりにキーワードILIKEを使用できます。

    [ WHERE condition { LIKE | ILIKE | NOT LIKE | NOT ILIKE } pattern ]

LIKE句とILIKE句のロジックを次の表に示します。

演算子
WHERE condition LIKE pattern ~~
WHERE condition NOT LIKE pattern !~~
WHERE condition ILIKE pattern ~~*
WHERE condition NOT ILIKE pattern !~~*

SELECT * FROM Customers
WHERE CustomerName ILIKE 'a%';

このクエリでは、名前が「A」または「a」で始まる顧客を返します。

結合

JOINを使用するSELECTクエリの構文は次のとおりです。

SELECT statement
FROM statement
[JOIN | INNER JOIN | LEFT JOIN | LEFT OUTER JOIN | RIGHT JOIN | RIGHT OUTER JOIN | FULL JOIN | FULL OUTER JOIN]
ON join condition

UNION、INTERSECT、EXCEPT

UNIONINTERSECTおよびEXCEPT句は、2つ以上のテーブルから同様の行を結合または除外するために使用します。

SELECT statement 1
[UNION | UNION ALL | UNION DISTINCT | INTERSECT | EXCEPT | MINUS]
SELECT statement 2

CREATE TABLE AS SELECT

次の構文は、CREATE TABLE AS SELECT(CTAS)クエリを定義します。

CREATE TABLE table_name [ WITH (schema='target_schema_title', rowvalidation='false') ] AS (select_query)

パラメーター

  • schema:XDMスキーマのタイトル。この句は、CTASクエリで作成された新しいデータセットに対して既存のXDMスキーマを使用する場合にのみ使用します。
  • rowvalidation:(オプション)新しく作成したデータセットに対して取り込まれるすべての新しいバッチの行レベルの検証を、ユーザーが希望するかどうかを指定します。デフォルト値は true です。
  • select_query:ステー SELECT トメント。SELECTクエリの構文は、SELECTクエリの節に記載されています。

CREATE TABLE Chairs AS (SELECT color, count(*) AS no_of_chairs FROM Inventory i WHERE i.type=="chair" GROUP BY i.color)

CREATE TABLE Chairs WITH (schema='target schema title') AS (SELECT color, count(*) AS no_of_chairs FROM Inventory i WHERE i.type=="chair" GROUP BY i.color)

CREATE TABLE Chairs AS (SELECT color FROM Inventory SNAPSHOT SINCE 123)
メモ

SELECT 文には、COUNTSUMMIN などの集計関数のエイリアスが含まれている必要があります。また、SELECT文は()括弧付きでも付きでも付きも付きません。 ターゲットテーブルに増分デルタを読み込むためのSNAPSHOT句を指定できます。

INSERT INTO

INSERT INTOコマンドは次のように定義されます。

INSERT INTO table_name select_query

パラメーター

  • table_name:クエリを挿入するテーブルの名前。
  • select_query:ステー SELECT トメント。SELECTクエリの構文は、SELECTクエリの節に記載されています。

INSERT INTO Customers SELECT SupplierName, City, Country FROM OnlineCustomers;

INSERT INTO Customers AS (SELECT * from OnlineCustomers SNAPSHOT AS OF 345)
メモ

SELECT文​は()括弧で囲んではいけません。 また、SELECTステートメントの結果のスキーマは、INSERT INTOステートメントで定義されたテーブルの結果に準拠している必要があります。 ターゲットテーブルに増分デルタを読み込むためのSNAPSHOT句を指定できます。

DROP TABLE

DROP TABLEコマンドは、既存のテーブルを削除し、外部テーブルでない場合は、テーブルに関連付けられたディレクトリをファイルシステムから削除します。 テーブルが存在しない場合は、例外が発生します。

DROP TABLE [IF EXISTS] [db_name.]table_name

パラメーター

  • IF EXISTS:これを指定した場合、テーブルがnotexistの場合は例外はスローされ ​ません。

データベースの削除

DROP DATABASEコマンドは、既存のデータベースを削除します。

DROP DATABASE [IF EXISTS] db_name

パラメーター

  • IF EXISTS:これを指定した場合、データベースが存在しない場合は例外がスローさ ​れません。

スキーマを削除

DROP SCHEMAコマンドは既存のスキーマを削除します。

DROP SCHEMA [IF EXISTS] db_name.schema_name [ RESTRICT | CASCADE]

パラメーター

  • IF EXISTS:これを指定した場合、スキーマがnotexistの場合は例外はスローされ ​ません。

  • RESTRICT:モードのデフォルト値。これを指定した場合、スキーマは​テーブルが含まれていない​場合にのみ削除されます。

  • CASCADE:この値を指定すると、スキーマ内に存在するすべてのテーブルと共にスキーマが削除されます。

CREATE VIEW

次の構文は、CREATE VIEWクエリを定義します。

CREATE VIEW view_name AS select_query

パラメーター

  • view_name:作成するビューの名前。
  • select_query:ステー SELECT トメント。SELECTクエリの構文は、SELECTクエリの節に記載されています。

CREATE VIEW V1 AS SELECT color, type FROM Inventory

CREATE OR REPLACE VIEW V1 AS SELECT model, version FROM Inventory

DROP VIEW

次の構文は、DROP VIEWクエリを定義します。

DROP VIEW [IF EXISTS] view_name

パラメーター

  • IF EXISTS:これを指定した場合、ビューがnotexistの場合に例外はスローされ ​ません。
  • view_name:削除するビューの名前。

DROP VIEW v1
DROP VIEW IF EXISTS v1

Spark SQLコマンド

次のサブ節では、クエリサービスでサポートされるSpark SQLコマンドについて説明します。

SET

SETコマンドは、プロパティを設定し、既存のプロパティの値を返すか、既存のプロパティをすべてリストします。 既存プロパティのキーに値が指定された場合、古い値が上書きされます。

SET property_key = property_value

パラメーター

  • property_key:リストまたは変更するプロパティの名前。
  • property_value:プロパティを設定する値です。

設定の値を返すには、property_valueを付けずにSET [property key]を使用します。

PostgreSQL コマンド

以下のサブ節では、クエリサービスでサポートされるPostgreSQLコマンドについて説明します。

BEGIN

BEGINコマンド、またはBEGIN WORKまたはBEGIN TRANSACTIONコマンドを使用して、トランザクションブロックを開始します。 beginコマンドの後に入力された文は、明示的なCOMMITまたはROLLBACKコマンドが指定されるまで、1つのトランザクションで実行されます。 このコマンドはSTART TRANSACTIONと同じです。

BEGIN
BEGIN WORK
BEGIN TRANSACTION

CLOSE

CLOSEコマンドは、開いたカーソルに関連付けられたリソースを解放します。 カーソルを閉じた後の操作は許可されません。不要になったカーソルは閉じる必要があります。

CLOSE name
CLOSE ALL

CLOSE nameを使用した場合、nameは閉じる必要がある開いたカーソルの名前を表します。 CLOSE ALLを使用すると、すべてのオープンカーソルが閉じます。

DEALLOCATE

DEALLOCATEコマンドを使用すると、事前に準備されたSQL文の割り当てを解除できます。 準備された文の割り当てを明示的に解除しない場合は、セッションが終了すると割り当てが解除されます。準備済み文について詳しくは、 PREPAREコマンドの節を参照してください。

DEALLOCATE name
DEALLOCATE ALL

DEALLOCATE nameを使用した場合、nameは、割り当てを解除する必要がある準備済み文の名前を表します。 DEALLOCATE ALLを使用すると、すべての準備済み文の割り当てが解除されます。

DECLARE

DECLAREコマンドを使用すると、カーソルを作成できます。カーソルは、大きなクエリから少数の行を取得するのに使用できます。 カーソルが作成された後、FETCH を使用して行がカーソルから取得されます。

DECLARE name CURSOR FOR query

パラメーター

  • name:作成するカーソルの名前。
  • query:カーソルが返す行を指定する SELECT または VALUES コマンド。

EXECUTE

EXECUTEコマンドは、事前に準備された文を実行するために使用します。 準備済み文はセッション中にのみ存在するので、現在のセッション中に前に実行されたPREPARE文によって作成された必要があります。 準備済み文の使用に関する詳細は、 PREPAREコマンドの節を参照してください。

文を作成したPREPARE文が一部のパラメーターを指定した場合は、互換性のある一連のパラメーターをEXECUTE文に渡す必要があります。 これらのパラメーターが渡されない場合、エラーが発生します。

EXECUTE name [ ( parameter ) ]

パラメーター

  • name:実行する準備済み文の名前。
  • parameter:準備済み文のパラメーターの実際の値。これは、準備済み文の作成時に決定された、このパラメーターのデータ型と互換性のある値を生成する式である必要があります。準備済み文に複数のパラメーターがある場合は、コンマで区切ります。

EXPLAIN

EXPLAINコマンドは、指定された文の実行計画を表示します。 実行計画では、文で参照されるテーブルがスキャンされる方法が示されます。 複数のテーブルが参照されている場合は、各入力テーブルから必要な行を統合するために使用される結合アルゴリズムが表示されます。

EXPLAIN option statement

ここで、optionは次のいずれかになります。

ANALYZE
FORMAT { TEXT | JSON }

パラメーター

  • ANALYZE:にが含まれ option る場 ANALYZE合は、実行時間とその他の統計が表示されます。
  • FORMAT:にが含ま option れる FORMAT場合は、出力形式を指定します(または) TEXTJSONテキスト以外の出力には、テキスト出力形式と同じ情報が含まれますが、プログラムの解析が容易です。このパラメーターのデフォルトは TEXT です。
  • statement:実行計画を表示する SELECTINSERTUPDATEDELETEVALUESEXECUTEDECLARECREATE TABLE ASCREATE MATERIALIZED VIEW AS 文のいずれか。
重要

ANALYZE オプションを使用すると、実際に文が実行されることに注意してください。EXPLAIN は、SELECT が返す出力を破棄しますが、文の他の副作用は通常どおり発生します。

次の例は、単一のinteger列と10000行のテーブルに対する単純なクエリの計画を示しています。

EXPLAIN SELECT * FROM foo;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)

FETCH

FETCHコマンドは、以前に作成したカーソルを使用して行を取得します。

FETCH num_of_rows [ IN | FROM ] cursor_name

パラメーター

  • num_of_rows:取得する行の数。
  • cursor_name:情報を取得するカーソルの名前。

PREPARE

PREPAREコマンドを使用すると、準備済み文を作成できます。 準備済み文は、類似のSQL文をテンプレート化するために使用できるサーバー側のオブジェクトです。

準備済み文はパラメーターを取ることができます。パラメーターは、文の実行時に文内で置き換えられる値です。 パラメーターは、準備済み文を使用する場合、$1、$2などを使用して、位置で参照されます。

必要に応じて、パラメータのデータ型のリストを指定できます。 パラメーターのデータ型がリストにない場合は、その型をコンテキストから推論できます。

PREPARE name [ ( data_type [, ...] ) ] AS SELECT

パラメーター

  • name:準備済み文の名前。
  • data_type:準備済み文のパラメーターのデータ型。パラメーターのデータ型がリストにない場合は、その型をコンテキストから推論できます。 複数のデータ型を追加する必要がある場合は、コンマ区切りリストで追加できます。

ROLLBACK

ROLLBACKコマンドは、現在のトランザクションを取り消し、トランザクションによって行われたすべての更新を破棄します。

ROLLBACK
ROLLBACK WORK

SELECT INTO

SELECT INTOコマンドは、新しいテーブルを作成し、クエリで計算されたデータで埋めます。 通常のSELECTコマンドと同様に、データはクライアントに返されません。 新しいテーブルの列には、SELECTコマンドの出力列に関連付けられた名前とデータ型が含まれます。

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

パラメーター

標準のSELECTクエリパラメーターの詳細については、SELECTクエリの節を参照してください。 このセクションでは、SELECT INTOコマンド専用のパラメーターのみをリストします。

  • TEMPORARY または TEMP:オプションのパラメーター。指定した場合、作成されるテーブルは一時テーブルになります。
  • UNLOGGED:オプションのパラメーター。指定した場合、として作成されたテーブルはログなしのテーブルになります。 ログなしのテーブルに関する詳細は、PostgreSQLのドキュメントを参照してください。
  • new_table:作成するテーブルの名前。

次のクエリは、テーブルfilmsの最近のエントリのみから成る新しいテーブルfilms_recentを作成します。

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

SHOW

SHOWコマンドは、実行時のパラメータの現在の設定を表示します。 これらの変数は、SET文を使用して、postgresql.conf設定ファイルを編集するか、PGOPTIONS環境変数(libpqまたはlibpqベースのアプリケーションを使用する場合)を使用するか、Postgresサーバーを起動する際にコマンドラインフラグを使用して設定できます。

SHOW name
SHOW ALL

パラメーター

  • name:情報が必要なランタイムパラメーターの名前。ランタイムパラメーターに指定できる値は次のとおりです。
    • SERVER_VERSION:このパラメーターは、サーバーのバージョン番号を示します。

    • SERVER_ENCODING:このパラメーターは、サーバー側の文字セットエンコーディングを示します。

    • LC_COLLATE:このパラメータは、照合(テキストの順序付け)のためのデータベースのロケール設定を示します。

    • LC_CTYPE:このパラメータは、文字分類に関するデータベースのロケール設定を示します。

      IS_SUPERUSER:このパラメータは、現在の役割にスーパーユーザー権限があるかどうかを示します。

  • ALL:すべての設定パラメーターの値と説明を表示します。

次のクエリは、パラメータDateStyleの現在の設定を示しています。

SHOW DateStyle;
 DateStyle
-----------
 ISO, MDY
(1 row)

コピー

COPYコマンドは、任意のSELECTクエリの出力を指定された場所にダンプします。 このコマンドを正常に実行するには、ユーザーがこの場所にアクセスできる必要があります。

COPY query
    TO '%scratch_space%/folder_location'
    [  WITH FORMAT 'format_name']

パラメーター

  • query:コピーするクエリ。
  • format_name:クエリをコピーする形式。format_nameは、parquetcsvjsonのいずれかです。 デフォルト値はparquetです。
メモ

完全な出力パスはadl://<ADLS_URI>/users/<USER_ID>/acp_foundation_queryService/folder_location/<QUERY_ID>です。

ALTER TABLE

ALTER TABLEコマンドを使用すると、プライマリキーや外部キーの制約を追加または削除したり、テーブルに列を追加したりできます。

制約を追加または削除

次のSQLクエリは、テーブルに制約を追加または削除する例を示しています。

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY ( column_name )

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY ( column_name ) REFERENCES referenced_table_name ( primary_column_name )

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY column_name NAMESPACE namespace

ALTER TABLE table_name DROP CONSTRAINT constraint_name PRIMARY KEY ( column_name )

ALTER TABLE table_name DROP CONSTRAINT constraint_name FOREIGN KEY ( column_name )

パラメーター

  • table_name:編集するテーブルの名前。
  • constraint_name:追加または削除する制約の名前。
  • column_name:制約を追加する列の名前。
  • referenced_table_name:外部キーによって参照されるテーブルの名前。
  • primary_column_name:外部キーによって参照される列の名前。
メモ

テーブルスキーマは一意であり、複数のテーブル間で共有されない必要があります。 また、プライマリキーの制約には名前空間が必須です。

列の追加

次のSQLクエリは、テーブルに列を追加する例を示しています。

ALTER TABLE table_name ADD COLUMN column_name data_type

ALTER TABLE table_name ADD COLUMN column_name_1 data_type1, column_name_2 data_type2 

パラメーター

  • table_name:編集するテーブルの名前。
  • column_name:追加する列の名前。
  • data_type:追加する列のデータ型。次のようなデータタイプがサポートされています。bigint, char,文字列,日付,日時,倍精度,整数, smallint, tinyint, varchar

プライマリキー

SHOW PRIMARY KEYSコマンドは、指定したデータベースの主キー制約をすべてリストします。

SHOW PRIMARY KEYS
    tableName | columnName    | datatype | namespace
------------------+----------------------+----------+-----------
 table_name_1 | column_name1  | text     | "ECID"
 table_name_2 | column_name2  | text     | "AAID"

外部キーの表示

SHOW FOREIGN KEYSコマンドは、指定したデータベースの外部キー制約をすべてリストします。

SHOW FOREIGN KEYS
    tableName   |     columnName      | datatype | referencedTableName | referencedColumnName | namespace 
------------------+---------------------+----------+---------------------+----------------------+-----------
 table_name_1   | column_name1        | text     | table_name_3        | column_name3         |  "ECID"
 table_name_2   | column_name2        | text     | table_name_4        | column_name4         |  "AAID"

このページ