クエリサービスの SQL 構文

Adobe Experience Platformクエリサービスは、 SELECT ステートメントおよびその他の制限付きコマンド このドキュメントでは、 Query Service.

クエリを選択

次の構文は、 SELECT 次をサポートするクエリ Query Service:

[ 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 句は、テーブルまたはテーブルのエイリアスに対して機能しますが、サブクエリまたはビューの上には機能しません。 A SNAPSHOT 節はどこでも機能する SELECT テーブルのクエリを適用できます。

また、 HEAD および TAIL スナップショット句の特別なオフセット値として。 使用 HEAD は最初のスナップショットの前のオフセットを指し、 TAIL は、最後のスナップショットの後のオフセットを指します。

メモ

2 つのスナップショット ID 間でクエリを実行し、開始スナップショットが期限切れになった場合、オプションのフォールバック動作フラグ (resolve_fallback_snapshot_on_failure) が設定されている場合は次のようになります。

  • オプションのフォールバック動作フラグが設定されている場合、クエリサービスは、利用可能な最も古いスナップショットを選択し、開始スナップショットとして設定し、利用可能な最も古いスナップショットと指定された終了スナップショットの間のデータを返します。 このデータは 包括的 最も古いスナップショットの

  • オプションのフォールバック動作フラグが設定されていない場合は、エラーが返されます。

WHERE 句

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

    [ 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」で始まる顧客を返します。

結合

A 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

この UNION, INTERSECT、および 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:A SELECT 文。 の構文 SELECT クエリは、 「SELECT queries」セクション.

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

パラメーター

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:これを指定した場合、テーブルが not 存在する。

データベースを削除

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

DROP DATABASE [IF EXISTS] db_name

パラメーター

  • IF EXISTS:これを指定した場合、データベースが not 存在する。

スキーマをドロップ

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

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

パラメーター

  • IF EXISTS:これを指定した場合、スキーマが not 存在する。

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

  • CASCADE:これを指定した場合、スキーマは、スキーマ内に存在するすべてのテーブルと共にドロップされます。

CREATE VIEW

次の構文は、 CREATE VIEW クエリ:

CREATE VIEW view_name AS select_query

パラメーター

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:これを指定した場合、ビューが not 存在する。
  • view_name:削除するビューの名前。

DROP VIEW v1
DROP VIEW IF EXISTS v1

匿名ブロック

匿名ブロックは、次の 2 つのセクションで構成されます。実行可能セクションと例外処理セクション 匿名ブロックでは、実行可能セクションは必須です。 ただし、例外処理セクションはオプションです。

次の例は、1 つ以上のステートメントを組み合わせて実行するブロックの作成方法を示しています。

BEGIN
  statementList
[EXCEPTION exceptionHandler]
END

exceptionHandler:
      WHEN OTHER
      THEN statementList

statementList:
    : (statement (';')) +

匿名ブロックの使用例を以下に示します。

$$
BEGIN
   SET @v_snapshot_from = select parent_id  from (select history_meta('email_tracking_experience_event_dataset') ) tab where is_current;
   SET @v_snapshot_to = select snapshot_id from (select history_meta('email_tracking_experience_event_dataset') ) tab where is_current;
   SET @v_log_id = select now();
   CREATE TABLE tracking_email_id_incrementally
     AS SELECT _id AS id FROM email_tracking_experience_event_dataset SNAPSHOT BETWEEN @v_snapshot_from AND @v_snapshot_to;

EXCEPTION
  WHEN OTHER THEN
    DROP TABLE IF EXISTS tracking_email_id_incrementally;
    SELECT 'ERROR';
END$$;

Spark SQL コマンド

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

SET

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

SET property_key = property_value

パラメーター

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

任意の設定の値を返すには、 SET [property key] 無しに property_value.

PostgreSQL コマンド

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

BEGIN

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

BEGIN
BEGIN WORK
BEGIN TRANSACTION

CLOSE

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

CLOSE name
CLOSE ALL

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

DEALLOCATE

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

DEALLOCATE name
DEALLOCATE ALL

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

DECLARE

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

DECLARE name CURSOR FOR query

パラメーター

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

EXECUTE

この EXECUTE コマンドは、事前に準備された文を実行するために使用されます。 準備済み文はセッション中にのみ存在するので、準備済み文は PREPARE 現在のセッションで前に実行された文。 準備済み文の使用に関する詳細については、 PREPARE command 」セクションに入力します。

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

EXECUTE name [ ( parameter ) ]

パラメーター

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

EXPLAIN

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

EXPLAIN option statement

ここで、 option は、次のいずれかに該当します。

ANALYZE
FORMAT { TEXT | JSON }

パラメーター

  • ANALYZE:この option 次を含む ANALYZEに値を指定しない場合は、実行時間やその他の統計情報が表示されます。
  • FORMAT:この option 次を含む FORMATを指定する場合は、出力形式を指定します。 TEXT または JSON. テキスト以外の出力には、テキスト出力形式と同じ情報が含まれますが、プログラムの解析が容易です。このパラメーターのデフォルトは 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_recent テーブルの最近のエントリのみから成る films:

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 次のいずれかを指定できます。 parquet, csvまたは json. デフォルトでは、値は 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"

このページ