Query Serviceでのクエリ実行の一般的なガイダンス
このドキュメントでは、Adobe Experience Platform Query Serviceでクエリを作成する際に知っておくべき重要な詳細について説明します。
Query Serviceで使用されるSQL構文について詳しくは、SQL構文ドキュメント を参照してください。
クエリ実行モデル
Adobe Experience Platform Query Serviceには、インタラクティブと非インタラクティブの2つのクエリ実行モデルがあります。 インタラクティブな実行は、ビジネスインテリジェンスツールでのクエリの開発とレポートの生成に使用され、非インタラクティブな実行は、データ処理ワークフローの一部として大規模なジョブや運用クエリに使用されます。
インタラクティブクエリの実行
クエリは、Query Service UIを通じて送信するか、接続されたクライアント を通じて送信することで、インタラクティブに実行できます。 接続されたクライアントを通じてQuery Serviceを実行すると、送信されたクエリが返されるかタイムアウトするまで、アクティブなセッションがクライアントとQuery Serviceの間で実行されます。
インタラクティブクエリの実行には、次の制限があります。
LIMIT 0を含めます。 10 分のクエリタイムアウトは引き続き適用されます。デフォルトでは、インタラクティブクエリの結果はクライアントに返され、永続 化されません。結果をExperience Platformのデータセットとして保持するには、クエリでCREATE TABLE AS SELECT構文を使用する必要があります。
非インタラクティブクエリの実行
Query Service APIを介して送信されたクエリは、非インタラクティブに実行されます。 非インタラクティブ実行とは、Query ServiceがAPI呼び出しを受け取り、受け取った順序でクエリを実行することを意味します。 非インタラクティブなクエリは、常にExperience Platformに新しいデータセットを生成して結果を受け取るか、既存のデータセットに新しい行を挿入します。
オブジェクト内の特定のフィールドへのアクセス
クエリ内のオブジェクト内のフィールドにアクセスするには、ドット表記（.）または角括弧表記（[]）を使用します。次の SQL 文は、ドット表記を使用し、endUserIds オブジェクトを下に移動して mcid オブジェクトを表示します。
SELECT endUserIds._experience.mcid
FROM {ANALYTICS_TABLE_NAME}
WHERE endUserIds._experience.mcid IS NOT NULL
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 1
{ANALYTICS_TABLE_NAME}次の SQL 文は、括弧表記を使用し、endUserIds オブジェクトを下に移動して mcid オブジェクトを表示します。
SELECT endUserIds['_experience']['mcid']
FROM {ANALYTICS_TABLE_NAME}
WHERE endUserIds._experience.mcid IS NOT NULL
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 1
{ANALYTICS_TABLE_NAME}上記の例のクエリはどちらも、単一の値ではなく、フラット化されたオブジェクトを返します。
endUserIds._experience.mcid
|--------------------------------------------------------
(48168239533518554367684086979667672499,"(ECID)",true)
(1 row)
返される endUserIds._experience.mcid オブジェクトには、次のパラメーターに対応する値が含まれます。
idnamespaceprimary
列がオブジェクトまでしか宣言されていない場合、オブジェクト全体を文字列として返します。ID のみを表示するには、次を使用します。
SELECT endUserIds._experience.mcid.id
FROM {ANALYTICS_TABLE_NAME}
WHERE endUserIds._experience.mcid IS NOT NULL
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 1
endUserIds._experience.mcid.id
|----------------------------------------
48168239533518554367684086979667672499
(1 row)
見積もり
一重引用符、二重引用符、および逆引用符は、クエリサービスクエリ内で異なる用途があります。
一重引用符
一重引用符（'）は、テキスト文字列の作成に使用されます。例えば、この変数を SELECT ステートメント内で使用して、結果に静的なテキスト値を返し、列の内容を評価する WHERE 句内で使用することができます。
次のクエリは、列の静的テキスト値（'datasetA'）を宣言します。
SELECT
'datasetA',
timestamp,
web.webPageDetails.name
FROM {ANALYTICS_TABLE_NAME}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10
次のクエリでは、WHERE 句において一重引用符で囲まれた文字列（'homepage'）を使用して、特定のページのイベントを返します。
SELECT
timestamp,
endUserIds._experience.mcid.id
FROM {ANALYTICS_TABLE_NAME}
WHERE web.webPageDetails.name = 'homepage'
AND TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10
二重引用符
二重引用符（"）は、識別子をスペースで宣言するために使用されます。
次のクエリでは、二重引用符を使用して、1 つの列で識別子にスペースが含まれている場合に、指定した列の値を返します。
SELECT
no_space_column,
"space column"
FROM
( SELECT
'column1' as no_space_column,
'column2' as "space column"
)
逆引用符
逆引用符 ` は、ドット表記の構文を使用する場合に のみ 予約列名をエスケープするために使用します。例えば、order は SQL では予約語なので、逆引用符をしようしてフィールド commerce.order にアクセスする必要があります。
SELECT
commerce.`order`
FROM {ANALYTICS_TABLE_NAME}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10
逆引用符は、数字で開始するフィールドにアクセスするためにも使用されます。例えば、フィールド 30_day_value にアクセスするには、逆引用符表記を使用する必要があります。
SELECT
commerce.`30_day_value`
FROM {ANALYTICS_TABLE_NAME}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10
角括弧表記を使用する場合は、逆引用符は 不要 です。
SELECT
commerce['order']
FROM {ANALYTICS_TABLE_NAME}
WHERE TIMESTAMP = to_timestamp('{TARGET_YEAR}-{TARGET_MONTH}-{TARGET_DAY}')
LIMIT 10
テーブル情報の表示
クエリ サービスに接続すると、\dまたはSHOW TABLES コマンドを使用して、Experience Platform上のすべての使用可能なテーブルを表示できます。
標準テーブルビュー
\d コマンドは、テーブルの一覧表示に使用する標準のPostgreSQL ビューを表示します。 このコマンドの出力例を以下に示します。
List of relations
Schema | Name | Type | Owner
|--------+-----------------+-------+----------
public | luma_midvalues | table | postgres
public | luma_postvalues | table | postgres
(2 rows)
詳細テーブルビュー
SHOW TABLES コマンドは、テーブルに関するより詳細な情報を提供するカスタムコマンドです。 このコマンドの出力例を以下に示します。
name | dataSetId | dataSet | description | resolved
|-----------------+--------------------------+----------------+-------------+----------
luma_midvalues | 5bac030c29bb8d12fa992e58 | Luma midValues | | false
luma_postvalues | 5c86b896b3c162151785b43c | Luma midValues | | false
(2 rows)
スキーマ情報
テーブル内のスキーマに関する詳細な情報を表示するには、\d {TABLE_NAME} コマンドを使用します。ここで、{TABLE_NAME}は、スキーマ情報を表示するテーブルの名前です。
次の例は、luma_midvaluesを使用して表示される\d luma_midvalues テーブルのスキーマ情報を示しています。
Table "public.luma_midvalues"
Column | Type | Collation | Nullable | Default
|-------------------+-----------------------------+-----------+----------+---------
timestamp | timestamp | | |
_id | text | | |
productlistitems | anyarray | | |
commerce | luma_midvalues_commerce | | |
receivedtimestamp | timestamp | | |
enduserids | luma_midvalues_enduserids | | |
datasource | datasource | | |
web | luma_midvalues_web | | |
placecontext | luma_midvalues_placecontext | | |
identitymap | anymap | | |
marketing | marketing | | |
environment | luma_midvalues_environment | | |
_experience | luma_midvalues__experience | | |
device | device | | |
search | search | | |
さらに、列の名前をテーブル名に追加することで、特定の列に関する詳細な情報を取得できます。 これは\d {TABLE_NAME}_{COLUMN}形式で書き込まれます。
次の例は、web列の追加情報を示しており、次のコマンドを使用して呼び出されます：\d luma_midvalues_web:
Composite type "public.luma_midvalues_web"
Column | Type | Collation | Nullable | Default
|----------------+-----------------------------------+-----------+----------+---------
webpagedetails | luma_midvalues_web_webpagedetails | | |
webreferrer | web_webreferrer | | |
データセットの結合
複数のデータセットを結合して、クエリに他のデータセットのデータを含めることができます。
次の例では、次の2つのデータセット（your_analytics_tableとcustom_operating_system_lookup）を結合し、ページビュー数で上位50 オペレーティングシステムのSELECT ステートメントを作成します。
クエリ
SELECT
b.operatingsystem AS OperatingSystem,
SUM(a.web.webPageDetails.pageviews.value) AS PageViews
FROM your_analytics_table a
JOIN custom_operating_system_lookup b
ON a._experience.analytics.environment.operatingsystemID = b.operatingsystemid
WHERE TIMESTAMP >= TO_TIMESTAMP('2018-01-01') AND TIMESTAMP <= TO_TIMESTAMP('2018-12-31')
GROUP BY OperatingSystem
ORDER BY PageViews DESC
LIMIT 50;
結果
重複の除外
Query Serviceは、データの重複排除、またはデータから重複する行の削除をサポートしています。 重複排除について詳しくは、 クエリサービス重複排除ガイド を参照してください。
クエリサービスのタイムゾーン計算
Query Serviceは、UTC タイムスタンプ形式を使用して、Adobe Experience Platformの永続データを標準化します。 タイムゾーン要件をUTC タイムスタンプに変換する方法とUTC タイムスタンプから変換する方法について詳しくは、タイムゾーンをUTC タイムスタンプに変更する方法に関するFAQの節を参照してください。
次の手順
このドキュメントでは、Query Serviceを使用してクエリを作成する際の重要な考慮事項について説明しました。 SQL 構文を使用して独自のクエリを記述する方法の詳細については、SQL構文のドキュメントを参照してください。
クエリサービス内で使用できるクエリの詳細なサンプルについては、次のユースケースのドキュメントを参照してください。