SQL Server

SQL Server (opens new window) は、Microsoft が開発したリレーショナルデータベース管理システムです。企業向けのトランザクション処理、ビジネスインテリジェンス、および分析アプリケーションをサポートしています。Workato を SQL Server と統合することで、データの移行、データウェアハウスやデータレイクにデータを送るデータパイプラインの構築、データの自動バックアップなどさまざまなタスクを実行できるようになります。データを変換またはエクスポートして、複雑なワークフローを構築することもできます。このドキュメントでは、Workato から SQL Server へのコネクションを設定する方法や、Workato と SQL Server を連携させることでビジネスニーズを満たす方法について説明します。

サポートされるバージョン

SQL Server のすべてのバージョンがサポートされます。ただし、一部のトリガー/アクションは新しいバージョンのみで利用できます。詳細については、個々のトリガー/アクションのドキュメントを参照してください。

Workato での SQL Server への接続方法

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

項目	説明
Connection name	この SQL Server コネクションに対して、接続先の SQL Server インスタンスを識別する一意の名前を付けます。
On-prem secure agent	直接接続が許可されていないネットワークでデータベースを実行している場合は、オンプレミスエージェントを選択します。接続を試行する前に、アクティブなオンプレミスエージェントがあることを確認します。詳細については、オンプレミスエージェントに関するガイドをご覧ください。
Username	SQL Server に接続するためのユーザー名。
Password	SQL Server に接続するためのパスワード。
Host	ホストされるサーバーの URL。
Port	サーバーが動作するポート番号。通常は 3306 です。
Database	接続する SQL Server データベースの名前。
Advanced Settings	日時処理の変更やデータベースタイムゾーンの設定など、コネクションの詳細設定が含まれます。
Azure SQL	Azure SQL インスタンスに接続する場合は [Yes] を選択します。

Use improved datetime handling

SQL Server コネクターに、datetime、datetime2、および datetimeoffset データ型の改善された処理を利用できるオプションが加わりました。これは、各 SQL Server コネクションのコネクション設定で有効にできます。このオプションは、すべての新しいコネクションでデフォルトで Yes に設定され、UTC タイムゾーンが設定されます。必要に応じて、データベースのローカルタイムゾーンに変更してください。この変更は、SQL Server に 行を挿入 するすべてのアクションに影響します。

SQL Server コネクションの高度な設定

動作の概要

データ型	Workato で入力する内容	オプションを false に設定/未選択	オプションを true に設定
datetime/datetime2	タイムゾーンなしの時間	Workato ワークスペースのタイムゾーンが想定され、挿入前に UTC に変換されます。	タイムゾーンは想定されず、そのまま挿入されます。
datetime/datetime2	タイムゾーンありの時間	挿入前に UTC に変換されます。	挿入前にコネクション設定の [Database timezone] のタイムゾーンに変換されます。
datetimeoffset	タイムゾーンなしの時間	Workato ワークスペースのタイムゾーンが想定され、挿入前に UTC (+00:00タイムゾーン) に変換されます。	想定される +00:00タイムゾーンで挿入されます。
datetimeoffset	タイムゾーンありの時間	挿入前に UTC (+00:00タイムゾーン) に変換されます。	指定されたタイムゾーンで挿入されます。

注

datetime/datetime2/datetimeoffset 項目に日付選択のカレンダーを使用する場合、時間は Workato ワークスペースのタイムゾーンを使用して定義されていることに注意してください。

接続に必要な権限

データベースユーザーアカウントには、最低でもコネクションで指定されているデータベースに対する SELECT 権限が付与されている必要があります。業務で使用する SQL Server の接続設定を担当しているユーザーは、下記の例で、権限の設定方法の詳細を確認してください。

権限の設定方法については、こちらをクリックしてください。

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

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

CREATE LOGIN workato WITH PASSWORD = 'password1234';
USE HR_PROD;
CREATE USER workato FOR LOGIN workato;

password1234 を安全なパスワードで置き換えます。自分や組織内の他のユーザーが覚えやすいパスワードを設定するようにしてください。

これによって、ユーザーは SQL Server インスタンスにログインするためのアクセス権を得られます。ただし、このユーザーにテーブルへのアクセス権はありません。

次のステップとして、必要なテーブルに対する権限を付与します。これには、いくつかの方法があります。最も単純な方法の1つは、ロールに基づいてアクセス権を付与することです。
データベースレベルのロールの詳細については、こちらをご覧ください。

ALTER ROLE db_datareader ADD MEMBER workato;

または、SCHEMA、HR で定義されたすべてのテーブルに対するアクセス権を付与することもできます。

GRANT SELECT,INSERT ON SCHEMA :: HR TO workato;

特定のテーブルに対するアクセス権を付与するには、テーブル名を個別に指定して、このクエリーを実行します。

GRANT SELECT,INSERT ON tablename1 TO workato;
GRANT SELECT,INSERT ON tablename2 TO workato;

選択的な権限を付与することは、機密情報が含まれるデータベースで役立ちます。レシピに必要な情報を含むテーブルにのみ Workato がアクセスできるようにしてください。

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

SELECT
  pr.name,
  pr.type_desc,
  perm.permission_name,
  perm.class_desc,
  object_name(perm.major_id) AS "object",
  schema_name(perm.major_id) AS "schema"
FROM sys.database_principals pr
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = pr.principal_id
WHERE pr.name = 'workato';

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

+---------+-----------+-----------------+------------+--------+-------------+
| name    | type_desc | permission_name | class_desc | object | schema      |
+---------+-----------+-----------------+------------+--------+-------------+
| workato | SQL_USER  | CONNECT         | DATABASE   | NULL   | NULL        |
| workato | SQL_USER  | INSERT          | SCHEMA     | NULL   | workatodemo |
| workato | SQL_USER  | SELECT          | SCHEMA     | NULL   | workatodemo |
+---------+-----------+-----------------+------------+--------+-------------+
3 rows in set (0.20 sec)

SQL Server コネクターの使用

テーブル、ビュー、およびストアドプロシージャの使用

SQL Server への接続が成功し、レシピでアクション/トリガーを選択したら、多くの場合、テーブル、ビュー、またはストアドプロシージャのいずれかを選択するよう求められます。これにより、データをプルまたは送信する対象のテーブルを Workato に指示します。

テーブルおよびビュー

SQL Server コネクターはすべてのテーブルおよびビューと連携します。ユーザーは、テーブルを各トリガー/アクションのピックリストで選択することも、正確な名前を指定することもできます。ビューもテーブルと同様に呼び出して使用することができます。

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

正確なテーブル/ビュー名をテキスト項目に入力

テーブル/ビュー名の大文字と小文字が区別されるかは、データベースの実装に依存します。デフォルトの SQL Server では大文字と小文字は区別されません。 COLLATION (照合順序) に CS が含まれる場合、そのデータベースまたはデータベースオブジェクトでは大文字と小文字が区別されることを意味しています。

ストアドプロシージャ

ストアドプロシージャは、SQL Server 内で作成され保存された、カスタム作成による SQL ステートメントです。行の作成、更新、および削除など、幅広い機能を実行できます。パラメータを受け入れることもできます。Workato でストアドプロシージャを使用する方法の詳細については、こちらをご覧ください。

WHERE 条件の使用

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

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

この句は各リクエストの WHERE ステートメントとして使用され、基本的な SQL 構文に従っている必要があります。WHERE ステートメントを構築するためのルールの包括的なリストについては、こちらの SQL Server のドキュメント (opens new window)を参照してください。以降のセクションでは、WHERE ステートメントのいくつかの基本的な構成要素について説明します。

TIP

以下の例では、ユーザー入力で直接 WHERE 条件を使用する方法を示しています。セキュリティを強化するには、WHERE 条件をパラメータとともに使用して SQL インジェクションを防止するようにしてください。詳細については、こちらを参照してください。

演算子

演算子はあらゆる WHERE ステートメントの基盤であり、Workato で返される行をフィルタリングして識別する上で役立ちます。SQL と同様の方法で演算子を連結することで、Workato から直接、データに対する堅牢で複雑なフィルターを作成できるようになります。

演算子のリストについてはこちらをクリックしてください。

演算子	説明	例
=	等しい	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
AND	前後の条件が true になる必要がある	WHERE ID = 445 AND NAME IS NOT NULL
OR	前後の条件のいずれかが true になる必要がある	WHERE ID = 445 OR NAME IS NOT NULL

データ型

WHERE 条件の他の構成要素では、これらの演算子を適切なデータ型と組み合わせて使用する必要があります。WHERE ステートメントを作成する際、テーブル内の data type = integer である変数は、data type = string ではなく data type = integer である変数と比較するようにしてください。

Workato では、以下のアクションの選択時、各入力項目に予期されるデータ型が分かるようにもしています。

• Update rows アクション
• Upsert rows アクション

このデータ型は出力項目のすぐ下に表示されるため、レシピを構築する際、送信されると予想されるデータ型を知ることができます。これらのヒントを使用して適切なデータ型を SQL Server に送信してください。そうでない場合、予期しない動作やジョブの失敗につながる恐れがあります。

予期されるデータ型についての各入力項目下のヒント

予想される一般的なデータ型を以下に示しています。より包括的なリストについては、こちら (opens new window)をご覧ください。

一般的なデータ型のリストについてはこちらをクリックしてください。

データ型	説明	例
int	-2,147,483,648 ～ 2,147,483,647の間の整数を使用できます。	-100,1,30,000
decimal	正確な固定小数点数および目盛り数。これは一般的に使用されます。最大長は指定できますが、デフォルトに設定されます。	1.11,2.0761,1.61803398875
smallint	0 ～ 255の整数を使用できます。	1,245,100
bigint	-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807の間の整数を使用できます。	10,000,000,000
bit	0、1、または NULL の整数。	1,0,NULL
varchar(n)	長さが n の可変長文字列。	Foo_bar
nchar(n)	長さが n の固定長文字列。	Foo where n = 3
datetime	1753年1月1日 ～ 9999年12月31日 (3.33ミリ秒の精度)	2011-09-16 13:23:18.767
datetime2	0001年1月1日 ～ 9999年12月31日 (100ナノ秒の精度)	2011-09-16 13:23:18.7676720
date	日付のみを格納します。0001年1月1日 ～ 9999年12月31日。	2012-10-11
time	100ナノ秒の精度で時刻のみを格納します。最小長は hh:mm:ss で、最大長は hh:mm:ss.nnnnnnnn です。	08:30:12,09:12:20.12898400

WHERE 条件の作成

演算子とデータ型について理解できたら、次は WHERE 条件を作成してみましょう。文字列値は一重引用符 ('') で囲む必要があります。また、使用する列はテーブル/ビューに存在していなければなりません。

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

country = 'United States of America'

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

WHERE 条件にデータピルを使用

スペースまたは特殊文字を含む列名は、二重引用符 ("") または角括弧 ([]) で囲む必要があります。例えば、 currency code を識別子として使用するには、括弧で囲まなければなりません。

[country code] = 'USA'

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

WHERE 条件を使用して実行できる機能の詳細については、以下のタブをクリックしてください。

WHERE 条件で AND および OR を使用する

WHERE 条件は、AND や OR などの基本的な SQL 論理演算子と組み合わせて使用し、返す行にさらにフィルターを追加することもできます。

([currency code] = 'USD' AND totalAmt >1000) OR totalAmt>2000

上記の WHERE 条件は、currency code 列に値 'USD' を含み、かつ (AND) totalAmt 列が1000より多いか、または (OR) totalAmt 列が2000より多いすべての行を返します。

WHERE 条件でサブクエリーを使用する

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

id in (select compensation_id from users where active = 0)

Delete rows アクションで使用すると、このステートメントは、compensation テーブル内のアクティブでなくなった (active = 0) ユーザーに関連するすべての行を抽出します。

WHERE 条件内でのサブクエリーの使用

パラメータの使用

パラメータを WHERE 条件と組み合わせて使用すれば、SQL インジェクションに対するセキュリティを強化することができます。パラメータを WHERE 条件で使用するには、まず入力でバインド変数を宣言する必要があります。バインド変数は :bind_variable の形式で宣言する必要があります。変数名の前には : が付きます。これが終わったら、直下のセクションで、指定した変数名を使用してパラメータを宣言します。

TIP

バインド変数は、列の名前ではなく、列の値の代わりにのみ使用する必要があります。

バインド変数を宣言した WHERE 条件

バインド変数は必要に応じていくつでも使用できますが、それぞれ固有の名前を指定する必要があります。一重引用符 (')、二重引用符 (")、および角括弧 ([]) 内のすべての : を無視することで、バインド変数を列名および静的値から区別します。

トリガーの設定

SQL Server コネクターには、新規行と更新行の両方に対するトリガーがあります。トリガーが機能するには、両方の [Unique keys] を設定する必要があります。最近更新された行をトリガーで見つけられるようにするには、 [Sort columns] を設定してください。

テーブルをトリガーで使用するには、いくつかの制約を満たす必要があります。特定の制約の詳細については、以下のセクションで説明します。Workato で使用するテーブルを準備する方法のベストプラクティスについては、こちらをご覧ください。

Unique keys

これはすべてのトリガーと一部のアクションにおいて必須の入力項目です。この列の値は、選択したテーブル内で行を一意に識別するために使用されます。そのため、選択した列の値は一意である必要があります。通常、この列はテーブルのプライマリキー (ID など) になります。

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

詳細な例については、こちらをクリックしてください。

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

Sort column

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

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

SQL Server の場合、 datetime2 および datetime の列型のみを使用できます。

詳細な例については、こちらをクリックしてください。

簡単な例を使ってこの動作を説明します。あるテーブルの行を処理した 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'

単一行またはバッチ (複数) 行を処理するアクション/トリガーの使用

SQL Server コネクターでは、データベースに対して単一行またはバッチ行で読み取りや書き込みを行うことができます。バッチ行を使用したトリガー/アクションでは、操作するバッチのサイズを入力する必要があります。バッチサイズには1 ～ 100の間の任意の数字を指定できます。最大バッチサイズは100です。このトリガーおよびアクションは、大量の行の読み取り、作成、または更新が想定されるジョブに最適です。ジョブを個別に分割して実行する代わりにバッチ処理することで、操作を簡素化できるだけでなく、レシピの実行時間を短縮し、サーバーの負荷を軽減できます。

バッチトリガーの入力

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

単一行の出力

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

バッチトリガーの出力

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

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

バッチトリガー/アクションの出力は、特にリストを操作するアクション以外でも使用することができます。Workato のリピートステップを使用すれば、バッチ出力を制御して、単一行を処理するよう作成されたアクションで使用することができます。

バッチアクションと単一行アクションをいつ使用すべきか判断に迷う場合は、レシピの設計のヒントを紹介したベストプラクティスのセクションをご覧ください。

Workato のトリガーとアクションのリスト

Workato は現在、以下のトリガーとアクションをサポートしています。それぞれの詳細については、以下のリンクをクリックしてください。リンク先にはサイドバーから移動することもできます。

• New row (新規行) トリガー
• New/updated row (新規行/更新行) トリガー
• Scheduled query (特定の時間に実行するクエリー) トリガー
• Select (選択) アクション
• Insert (挿入) アクション
• Update (更新) アクション
• Upsert (アップサート) アクション
• Delete (削除) アクション
• Run custom SQL (カスタム SQL 実行) アクション
• Execute stored procedure (ストアドプロシージャ実行)

レシピを作成する際は、Workato の以下の参考資料をご覧ください。

• ベストプラクティス
• ユースケース