Redshift

Amazon Redshift (opens new window) は、高速でフルマネージド型のデータウェアハウスです。標準 SQL のツールと既存のビジネスインテリジェンス (BI: Business Intelligence) ツールを使用して、すべてのユーザーデータをシンプルで費用対効果が高い方法で分析できます。

Workato での Redshift への接続方法

Redshift コネクターは、Redshift での認証に基本認証を使用します。

項目	説明
Connection name	この Redshift コネクションに対して、接続先の Redshift インスタンスを識別する一意の名前を付けます。
URL	Redshift インスタンスの完全な JDBC URL。例 : jdbc:redshift://redshift-main.sample.us-east-2.redshift.amazonaws.com:5439/dev
Username	Redshift に接続するためのユーザー名。
Password	Redshift に接続するためのパスワード。
Schema	接続する Redshift データベース内のスキーマの名前。デフォルトは public スキーマです。
Connection type	直接接続が許可されていないネットワークでデータベースを実行している場合は、オンプレミスグループを選択します。接続を試みる前に、アクティブなオンプレミスエージェントがあることを確認します。詳細は、オンプレミスエージェントに関するガイドをご覧ください。

接続に必要な権限

データベースユーザーアカウントには、最低でもコネクションで指定されているデータベースに対する SELECT 権限が付与されている必要があります。

Redshift インスタンスに、新しいデータベースユーザー workato を使用して接続する場合は、次のサンプルクエリーを使用できます。

はじめに、Workato との統合ユースケース専用の新規ユーザーを作成します。

CREATE USER workato PASSWORD 'password';

次のステップでは、スキーマの customer テーブルへのアクセス権を付与します。この例では、SELECT と INSERT の権限のみを付与します。

GRANT SELECT,INSERT ON TABLE customer TO workato;

最後に、このユーザーが必要な権限を持っていることを確認します。付与されている全権限を表示するクエリーを実行します。

SELECT
    u.usename,
    t.schemaname||'.'||t.tablename AS "table",
    has_table_privilege(u.usename,t.tablename,'select') AS "select",
    has_table_privilege(u.usename,t.tablename,'insert') AS "insert",
    has_table_privilege(u.usename,t.tablename,'update') AS "update",
    has_table_privilege(u.usename,t.tablename,'delete') AS "delete"
FROM
    pg_user u
CROSS JOIN
    pg_tables t
WHERE
    u.usename = 'workato'

これにより、以下に示す、Workato で Redshift のコネクションを作成するために必要な最低限の権限が返されるはずです。

+---------+----------+--------+--------+--------+--------+
| usename | table    | select | insert | update | delete |
+---------+----------+--------+--------+--------+--------+
| workato | customer | true   | true   | false  | false  |
+---------+----------+--------+--------+--------+--------+
2 rows in set (0.26 sec)

Redshift コネクターの使用

テーブルとビュー

Redshift コネクターはすべてのテーブルおよびビューと連携します。ユーザーは、それらを各トリガー/アクションのピックリストで利用することも、正確な名前を指定することもできます。

ピックリストからテーブル/ビューを選択

正確なテーブル/ビュー名をテキストフィールドに入力

Redshift では、引用符で囲まれていない識別子は、大文字と小文字が区別されません。したがって、

SELECT ID FROM USERS

は、以下と等しくなります。

SELECT ID FROM users

しかし、引用符で囲まれた識別子は、大文字と小文字が区別されます。それゆえ、

SELECT ID FROM "USERS"

は、以下と等しく ありません。

SELECT ID FROM "users"

単一行とバッチ行

Redshift コネクターでは、単一行またはバッチ行でデータベースの読み取りや書き込みを実行できます。バッチ行を使用したトリガー/アクションでは、操作するバッチのサイズを入力する必要があります。バッチサイズには1～100の間の任意の数字を指定できます。最大バッチサイズは100です。

バッチトリガーの入力

これら2つの操作には、入力項目の違いに加えて、出力にも違いがあります。一度に1つの行を処理するトリガーには、その単一行のデータをマッピングできる出力データツリーがあります。

単一行の出力

それに対して、複数行をバッチ処理するトリガーは、それらを行の配列として出力します。Rows データピルは、出力がそのバッチ内の各行のデータを含むリストであることを示しています。

バッチトリガーの出力

そのため、バッチトリガー/アクションの出力は異なる方法で処理する必要があります。このレシピ (opens new window)では、users テーブル内の新規行についてバッチトリガーを使用します。トリガーの出力は、Rows データピルをソースリストにマッピングする必要がある Salesforce のバルク更新アクションで使用されます。

バッチトリガーの出力を使用する

WHERE condition

この入力項目を使用すると、アクションを実行する対象行をフィルタリングして識別できます。以下のように、複数のトリガーおよびアクションで使用できます。

• トリガーで取得する行をフィルタリングする
• Select rows アクションで行をフィルタリングする
• Delete rows アクションで削除する行をフィルタリングする

この句は各リクエストの WHERE ステートメントとして使用されます。これは基本的な SQL 構文に従っている必要があります。Redshift SQL ステートメントを記述するためのルールの完全なリストについては、こちらの Redshift のドキュメント (opens new window) を参照してください。

演算子

演算子	説明	例
=	等しい	WHERE ID = 445
!= <>	等しくない	WHERE ID <> 445
> >=	より大きい 以上	WHERE PRICE > 10000
< <=	より小さい 以下	WHERE PRICE > 10000
IN(...)	値のリスト	WHERE ID IN(445, 600, 783)
LIKE	ワイルドカード文字 (% と _) を使用したパターンマッチング	WHERE EMAIL LIKE '%@workato.com'
BETWEEN	範囲を指定して値を取得	WHERE ID BETWEEN 445 AND 783
IS NULL IS NOT NULL	NULL 値のチェック NULL 以外の値のチェック	WHERE NAME IS NOT NULL

単純なステートメント

文字列値は一重引用符 ('') で囲む必要があります。また、使用する列はテーブルに存在していなければなりません。

単一列の値に基づいて行をフィルタリングする単純な WHERE 条件は、以下のようになります。

currency = 'USD'

Select rows アクションで使用すると、この WHERE 条件は currency 列の値が「USD」であるすべての行を返します。入力の際にデータピルを一重引用符で囲むことを覚えておいてください。

WHERE 条件でデータピルを使用する

スペースを含む列名は、二重引用符 ("") で囲む必要があります。たとえば、 currency code を識別子として使用するには、括弧で囲まれなければなりません。引用符で囲まれた識別子はすべて、大文字と小文字が区別されることに注意してください。

"currency code" = 'USD'

レシピでは、各値に適切な引用符を使用することを忘れないようにしてください。

識別子が括弧で囲まれている WHERE 条件

複雑なステートメント

WHERE 条件にサブクエリーを含めることもできます。以下のクエリーは accounts テーブルに対して使用できます。

id in (select user_id from tickets where priority = 2)

これを Delete rows アクションで使用すると、accounts テーブル内の1つ以上の関連付けられた行で account_name 列の値が2である、invoices テーブル内のすべての行が削除されます。

サブクエリーを含む WHERE 条件でデータピルを使用する

Unique key

これはすべてのトリガーと一部のアクションにおいて必須の入力項目です。この列の値は、選択したテーブル内で行を一意に識別するために使用されます。

そのため、選択した列の値は一意である必要があります。通常、この列はテーブルのプライマリキー (ID など) になります。

トリガーで使用する場合、これは増分列である必要があります。トリガーはこの列の値を使用して新規行を見つけるため、この制約が必要です。ポーリングのたびに、トリガーは、以前の最大値よりも大きな一意のキー値を持つ行を照会します。

簡単な例を使ってこの動作を説明します。あるテーブルの行を処理した New row トリガー があるとします。このトリガーに対して設定された 一意のキー は ID です。最後に処理された行には、ID 値として 100 が設定されています。次のポーリング時、トリガーは新規行を見つける条件として ID >= 101 を使用します。

一意のキー として使用するよう選択した列にインデックスを付けると、トリガーのパフォーマンスが向上します。

Sort column

これは New/updated row トリガー において必須の項目です。この列の値は、更新された行を識別するために使用されます。

行の更新時、 [Unique key] の値は変わりません。ただし、 [Sort column] は、最後の更新日時を反映するように更新されます。このロジックに従い、Workato ではこの列の値を、選択した [Unique key] 列の値と合わせて追跡します。 [Sort column] の値の変更が検出されると、更新された行のイベントがトリガーにより記録され、処理されます。

簡単な例を使ってこの動作を説明します。あるテーブルの行を処理した new/updated row トリガー があるとします。このトリガーに設定された [Unique key] と [Sort column] は、それぞれ ID と UPDATED_AT です。最後に処理された行には、100 の ID 値と、2018-05-09 16:00:00.000000 の UPDATED_AT 値が設定されています。次のポーリング時、トリガーは以下の2つの条件のいずれかを満たす新規行を照会します。

1. UPDATED_AT > '2018-05-09 16:00:00.000000'
2. ID > 100 AND UPDATED_AT = '2018-05-09 16:00:00.000000'

Redshift の場合、 timestamp および timestamptz の列型のみを使用できます。