AlteryxでRedshiftへバルクロードする

安原朋紀

2023.03.03

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

2023/3/3時点のAlteryxからRedshiftへバルクロードを行うための設定方法になります。

前提条件

下記の環境で検証しています。

Windows 10 Pro
Alteryx Designer 2022.3.1
Amazon RedshiftODBC64-1.4.27.1000
Amazon Redshift 1.0.46987
- dc2.large

バルクロード接続の構成

Alteryxでは、Redshiftへのデータ書き込み時のオプションとして、バルクロードをサポートしています。バルクロードオプションを指定することで、COPYコマンドを使用したデータロードを行うことができます。
標準のワークフロー（データ出力ツール）と In-DBワークフローでこの機能を使用することができます。

事前用意

バルクロード実行にあたり、下記の作業は事前に実施済みとしています。

Redshiftクラスタの作成
※ここでは、クラスタを作成後、パブリックアクセス許可としています。
S3の作成
※Redshiftと同一のリージョンに作成します。

AWSでの作業

上記の作業後、まずはAWS側で、バルクロードを実行する際に必要な権限を持つユーザーを作成します。

IAM Userの作成
2023/3/3時点で、AlteryxでのRedshiftへのバルクロード時は、アクセスキー（アクセスキーID・シークレットキー)の入力が必要です。
本記事では、ワークフローでのバルクロード実行用ユーザーを作成することとしました。
IAM Roleの作成
バルクロード時は、下記の順に処理が実行されます。
1. S3へロード対象のデータファイルを配置
2. COPYコマンドによる、ロードを実行
3. S3のファイルを削除

上記の処理を同じユーザー権限で行うことになるため、IAM Roleを作成し、ポリシーに下記の権限を付与しておく必要があります。

COPYコマンドの実行に必要
- s3:ListBucket
- s3:GetObject
S3へのファイル配置・削除に必要
- s3:PutObject
- s3:DeleteObject

Amazon S3 バケットにファイルをアップロードする | AWS
S3 バケットのオブジェクトへの読み取りおよび書き込みアクセスを許可する | AWS

ここでは、下記のポリシーを作成しIAM Roleに関連付け、ユーザーに付与しました。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::<バケット名>"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::<バケット名>/*"
        }
    ]
}

Alteryxでの作業

ここでは、Redshiftへの接続から行います。

ODBCドライバのインストール

Redshiftへの接続にあたり、ODBCドライバをインストールします。マネジメントコンソールに接続できる場合、下記よりダウンロード可能です。

または、下記よりダウンロードも可能です。

ODBC 接続の設定 | AWS

接続設定

Alteryxからデータベースへの接続は、「オプション > 詳細オプション > データ接続の管理」より行えます。

「データ接続の管理」を選択すると、下記の画面が表示されるので、右上の「接続の追加」より「その他」を選択します。

次の画面では、「名前」欄に任意の接続の名称を入力します。下矢印のメニューより、「ODBC」を選択します。

下記の画面が表示されるので、「ODBCアドミニストレーター」を押下します。

次の画面では、環境に応じて適切な設定で、DNSエントリを作成します。ここでは、「システムDNSエントリ」を作成することとします。システムDNSタブを選択し、「追加」を押下します。

※ODBC接続の設定については、下記を参照ください。 ODBC 接続の設定 | AWS

セットアップするドライバとして、「Amazon Redshift 」を選択し、「完了」を押下します。

次の画面で、接続対象のデータベース、ユーザー等を入力します。入力後は、画面下のテストより、テスト接続を行えます。

主要な項目は下記の通りです。

Data Source Name（データソース名）
- 接続設定の名称。以降はここで、作成した名称を指定することになります。
Server
- エンドポイント
Port
- 接続に使用するポート番号。Redshiftの規定は「5439」
Datbase
- 接続先のデータベース
Authentification
- 認証方式
  上図では、「Standard」を選択しています。この方式は、データベースユーザーによるユーザー名・パスワードでの認証となります。Redshiftの場合、IAM認証による認証方式を用いることも可能です。
  RedshiftのIAM認証を試してみる | DevelopersIO

設定に問題なければ「OK」を押下します。すると、下記の画面で作成したデータソース名を選択できます。接続設定時に認証情報を記入しているので、ここでは、ユーザー名・パスワードは省略できます。

すると、接続の管理画面に接続文字列が入力されるので、「保存」を選択します。

以降、接続を行う場合は、キャンバスにデータ入力ツールを配置します。

データ接続ウィンドウの「データソース」メニューより、「Amazon Redshift ODBC」を選択すると「ODBC接続」ウィンドウが再度表示されるもで、作成したデータソース名を選択します。

使用するデータソース名を選択し、「OK」を押下すると、テーブルの選択画面が開くので、接続するテーブルやビューを選択できます。

バルク設定

Redshiftへのデータ出力時は、「データ出力ツール」や「データ書込みIn-DB」ツールを使用できます。以降で、それぞれについてみていきます。

データ出力ツール

Alteryxから、Redshiftへの出力（テーブル作成、テーブルへのデータ追加など）時は、データ出力ツールを使用できます。
キャンバスにツールを配置後、データ接続ウィンドウの「データソース」メニューより、「Amazon Redshift バルク」を選択します。

すると、バルク接続の設定画面が開くので、必要な項目を記入します。

主要な設定項目は下記の通りです。

ODBCデータソース
- データソース名
  - Redshiftへの接続に使用する認証情報です。接続設定時に作成した「データソース名」を指定します。
- ユーザー名・パスワード（オプション）
  - 接続設定時にユーザー名・パスワードを設定している場合ここでの入力は不要です。
Amazon S3
- AWSアクセスキー・AWS 秘密キー
  - AWSの作業で作成した、IAM ユーザーのアクセスキー（アクセスキーID・シークレットキー)を入力します。このユーザーに付与された IAM Role に紐づく権限に基づき、S3へのデータファイル配置、削除、COPY コマンドが実行されます。
- 秘密キー暗号化
  - シークレットキーの暗号化オプションを選択します。
- エンドポイント
  - バケットのリージョンを指定します。Default を選択すると、選択したバケットに基づいて自動的に決定されます。
- サーバー側の暗号化
  - サーバー側の暗号化方式を指定します。
- バケット名
  - 一時的にデータファイルを配置するバケットの名前を入力します。オブジェクトはワークフロー実行完了時には削除されます。

詳細は下記を参照ください。

Amazon Redshift | Alteryx Help

入力後、「OK」を押下し出力するテーブルの名称を入力します。

他にもデータ出力ツールでは、Redshift側のテーブル設定を指定することができます。

以上で設定は完了です。

ここでは、下記のフローとし、実行します。Redshiftのサンプルデータ内の「sales」テーブルを上記で設定した「bulk_out」テーブルとして新規作成しています。

ワークフローを実行すると、指定のS3バケットにファイル作成され、そのファイルをCOPYコマンドで指定し、データをロードします。

クエリの実行履歴を確認するとコピーコマンドの実行を確認できます。

データ書込 In-DBツール

インデータベースでの設定には、64ビットのデータベースドライバが必要となります。この条件の下、はじめに、In-DBでの接続設定を行います。

キャンバスに接続In-DBツールを配置し、「接続を管理」を選択します。
In-DB 接続の管理では、「読み込み」と「書き込み」ごとに設定を行います。

読み込み

「新規」を選択し、接続名を入力します。

ドライバーは「Amazon Redshift ODBC」を指定します。接続文字列には、すでに作成済みのデータソース名を指定します。
※ここでは「redshift-bulk-test」

書き込み

「書き込み」タブを選択し、書き込み設定を行います。「ドライバー」に「Amazon Redshift バルクローダー」を指定し、新規の接続文字列を作成します。

下記の画面が開くので、データ出力ツールと同様の設定を行います。

以上で設定は完了です。

サンプルとして、下記のフローを実行します。先ほどと同様に、Redshiftのサンプルデータ内の「sales」テーブルを、「bulk_out_indb」テーブルとして新規作成する設定をデータ書込 In-DBツールで行っています。

ツール実行後、クエリの実行履歴を確認すると、こちらでは、SELECT INTOコマンドを実行したことが確認できます。

さいごに

以上、AlteryxでのRedshiftへのバルクロードの設定手順でした。
アクセスキーを使用することになるので、ユーザーには最低限の権限を与えるように注意しなければならないですね。
こちらの記事が何かの参考になれば幸いです。

参考

Alteryxを使用してRedshiftへテキストデータをバルクロードする | DevelopersIO

AlteryxでRedshiftへバルクロードする

前提条件

バルクロード接続の構成

事前用意

AWSでの作業