SQLコレクション

WorkatoのSQL Collectionは、テーブルデータを操作するためのツールを提供する強力なネイティブアプリケーションです。データベース、ウェブサービスなど、複数のシステム間で関連データを同期するためにSQL Collectionを使用できます。

WorkatoのSQL Collection

SQLコレクションを使用する理由

SQLコレクションを使用すると、複数のソースからのデータでSQLステートメントを実行できます。リスト入力やCSVファイルから一時的なテーブルを作成できます。その後、ユースケースに必要な出力を得るために様々なSQLクエリを実行できます。

SQLコレクションのテーブル（SQL Collection listsとも呼ばれます）は一時的なものです。つまり、ジョブの期間中のみ存在し、複数のジョブ間で持続しません。データ処理が完了したら、データを直接ターゲットシステムにロードしてください。

変換

SQLコレクションはSQLite上に構築され、他のデータベースと同様に機能します。リストを作成し、標準のSQL構文を使用してクエリを実行できます。WHERE、GROUP BY、JOINなどの一般的なSQLキーワードを使用して、テーブルからデータを操作し、計画された形式に変換します。

その後、SQLコレクションの出力を使用してターゲットシステムに直接ロードするか、データをCSVファイルとしてエクスポートします。

SQLコレクションは大規模なデータセットで動作します

SQLコレクションは大規模なデータセットの移動に優れています。テーブルをバッチ処理し、最適な速度のために並行して実行します。1,000件や100,000件のレコードでも同じようにレシピを実行できます。

SQLコレクションではストレージ容量を心配する必要はありません。サードパーティのアプリケーションを導入することなく、直接Workatoに抽出して処理できます。

コネクション設定

接続設定は必要ありません。App > SQL Collection by Workatoを選択するだけで開始できます。

アクション

SQLコレクションでリストを作成するアクション

このアクションは、リスト入力からレシピ内にSQLコレクションリストを作成します。SQLコレクションリストには、リストのスキーマに従ったカラムヘッダーが含まれます。

リストのデータピルを追加する際、List sourceフィールドはformula modeである必要があることに注意してください。

例えば、Workdayからすべての従業員のリストを取得できます。

SQLコレクションでリストを作成

入力フィールド	説明
リストソース	リストのデータピルを選択します。Ensure that this field is in formula mode.
リスト名	リストの名前。
プライマリインデックス	リストのプライマリインデックスとして1つ以上のカラムを選択します。
セカンダリインデックス	リストのセカンダリインデックスとして1つ以上のカラムを選択します。

日付フィールドエラー

SQL Collectionを使用する際に、Dateフィールドで無効な形式のエラーが発生する場合があります。このエラーは、SQLiteが日付と時刻のためのストレージクラスを提供していないために起こります。このエラーを修正するには、Mapper by Workatoを使用して以下のいずれかを行ってください：

• 日付を次の形式に更新する：YYYY-MM-DD HH:MM:SS.SSS
• 下流のアプリケーションで日付が必要ない場合は、日付フィールドをスキップする

SQLコレクションに行を挿入するアクション

このアクションは、リスト入力からレシピ内のSQLコレクションリストに行を挿入します。SQLコレクションリストには、リストのスキーマに従ったカラムヘッダーが含まれます。これは繰り返しループで使用できます。

SQLコレクションに行を挿入

入力フィールド	説明
リストソース	リストのデータピルを選択します。 このフィールドがフォーミュラモードになっていることを確認してください。
リスト名	リストの名前。
テーブルが存在しない場合はテーブルを作成しますか？	テーブルが存在しない場合は作成します。
プライマリインデックス	リストのプライマリインデックスとして1つ以上のカラムを選択します。
セカンダリインデックス	リストのセカンダリインデックスとして1つ以上のカラムを選択します。

CSVからSQLコレクションにリストを作成

このアクションは、CSV入力からSQLコレクションリストを作成します。新しく作成されたリストには、CSV文字列のスキーマに従ったカラムヘッダーが含まれます。

例えば、on-premシステムからファイルを取得する場合、CSVファイルをダウンロードし、SQLコレクションで直接使用できます。

CSVからSQLコレクションにリストを作成

入力フィールド	説明
CSVソース	ソース入力としてCSV文字列を選択します。
リスト名	リストの名前。
ファイルエンコードタイプ	ファイルのエンコードタイプを選択します。デフォルトの値はUTF-8です。
カラム名	CSVソース入力のカラムヘッダー。use a sample CSV fileを選択してCSVファイルでスキーマを定義します。
CSVヘッダ行を無視する	CSVソースにヘッダ行がある場合はYesを選択し、ない場合はNoを選択します。
カラム区切り文字	CSVの各行で値を区切るために使用される文字を選択します。
プライマリインデックス	リストのプライマリインデックスとして1つ以上のカラムを選択します。
セカンダリインデックス	リストのセカンダリインデックスとして1つ以上のカラムを選択します。

CSVファイルからSQLコレクションに行を挿入するアクション

このアクションは、CSVファイルからレシピ内のSQLコレクションリストに行を挿入します。これには、CSV文字列のスキーマに従ったカラムヘッダーが含まれます。

CSVファイルからSQLコレクションに行を挿入

入力フィールド	説明
CSVソース	ソース入力としてCSV文字列を選択します。
リスト名	リストの名前。
ファイルエンコードタイプ	ファイルのエンコードタイプを選択します。デフォルトの値はUTF-8です。
カラム名	CSVソース入力のカラムヘッダー。use a sample CSV fileを選択してCSVファイルでスキーマを定義します。
CSVヘッダ行を無視する	CSVソースにヘッダ行がある場合はYesを選択し、ない場合はNoを選択します。
カラム区切り文字	CSVの各行で値を区切るために使用される文字を選択します。
プライマリインデックス	リストのプライマリインデックスとして1つ以上のカラムを選択します。
セカンダリインデックス	リストのセカンダリインデックスとして1つ以上のカラムを選択します。

SQLコレクションでリストをクエリする

このアクションを使用すると、リストに対して標準のSQLクエリを実行できます。

SQLコレクションでリストをクエリ

入力フィールド	説明
SQLクエリ	SQLクエリを記述します。通常のSQL構文が適用されます。
出力リストのスキーマ	出力リストのカラムヘッダーに従ってスキーマを定義します。use sample JSONを選択してJSONでスキーマを定義します。
CSVに書き込む	クエリ結果をCSV文字列に変換するにはYesを選択します。これにより以下の入力フィールドが表示されます。このクエリ出力をさらにSQLクエリで使用する場合はNoを選択します。
CSVヘッダを追加	カラム名をCSVヘッダ行として使用するにはYesを選択し、そうでない場合はNoを選択します。
カラム区切り文字	CSVの各行で値を区切るために使用される文字を選択します。

以下は、Query listアクションで使用できる一般的なSQLキーワードの一部です。

SQLキーワード	説明
SELECT	このリスト内のすべてのカラムを呼び出すためにSQLワイルドカード*を使用します。
WHERE	リストから取得したいデータを指定する条件を定義します。
JOIN	リストを結合するためにJOIN、LEFT JOIN、INNER JOINを使用します。
INSERT INTO	リストの新しいエントリを定義します。
DELETE	リストから削除する行を定義します。

ターゲットシステムにエクスポートまたはロードする前に、完全なリストをクエリすることを忘れないでください。一部のSQLキーワード（例えば、INSERT INTO、DELETE）はリスト出力を返しません。そのため、これらのアクションからのデータピルには、SQLコレクションリスト内のすべての行/カラムが含まれていません。

クエリ例：リストから行を削除

例えば、Workdayからの従業員リストを特定のグループの人々を除外するように調整することができます。

SQLコレクションでリストをクエリして行を削除

DELETEクエリはリスト出力を返さないため、このステップからのデータピルはリストのエクスポートにshould not be used（使用すべきではありません）。

クエリ例：SQLコレクションをCSVとしてエクスポート

行をCSVとしてエクスポート

そのSQLコレクションからすべての行を取得するためにSELECTクエリを実行します。

SELECT * FROM workers_list

次に、アクションの構成でWrite to CSVを選択します。このアクションの出力はCSVファイルとして直接エクスポートできます。