yy16ki’s diary


スポンサードリンク

IICSでS3から複数ファイル読み込み

IICS(Informatica Intelligent Cloud Services)のデータ統合機能で、S3から複数ファイルを読み込む必要があったので、その際に調べた方法をまとめたものです。また後でわかったことですが、有償版であれば「一括取り込み」機能でもっと簡単に実装できるようです。

IICSとは

IICS(Informatica Intelligent Cloud Services)は、Informatica社が提供するクラウド型のデータ統合ソリューションです。SaaS型のETLツールといってもよいかもしれません。
一般的なETLとしての、各種データソースからのデータ抽出、統合処理、ロードまでをGUIで実装できます。

前提条件

IICSは、公式サイトより30日間の無償トライアルを利用できます。一部機能は制限されますが、基本的なデータ統合処理を試すことは可能です。
IICSでは、ランタイム環境として提供される「Secure Agent」をサーバーにインストールすることで、データを外部に出すことなく処理を実行できます。
今回は下記の環境を用意しました。EC2上にSecure Agentをインストールしています。

f:id:yy16ki:20220416130445p:plain
検証環境

EC2へのSecure Agentのインストールは、クラスメソッド様の記事が参考になります。

IICS Secure AgentをWindows環境にインストールしてみる | DevelopersIO

処理概要

IICSで下記の手順を実施します。
1.データソースとの接続を作成
2.マニフェストファイルを作成
3.ジョブの作成

ソースにS3、ターゲットにはRedshiftを使用します。
また、事前に下記のようなデータファイルを作成し、S3の所定の階層にアップロードしておきます。
S3の階層は<バケット名>/informatica/sampleとしています。

・サンプルデータ(data-01.csv

id val file
1 41 file1
2 36 file1
3 10 file1

f:id:yy16ki:20220416135257p:plain
対象ファイル

1ファイル10行のデータを、10ファイルに分割し配置(合計100行のデータ)。列「file」にはファイル番号を入力。

データソースとの接続を作成

ここから、IICSでの作業に入ります。まずはデータソースに使うS3とRedshiftの接続を作成します。
IICSの管理者画面より「新しい接続」を作成します。

  • S3
    下記の通り設定しました。「Use EC2 Role to Assume Role」にチェックを入れることで、EC2に付与したS3への読み取り権限をIICSでも使用しています。

f:id:yy16ki:20220416140731p:plain
S3接続設定

  • Redshift
    こちらは直接接続情報を入力しています。JDBCURLを使用することに注意してください。

f:id:yy16ki:20220416141140p:plain
Redshift接続設定

マニフェストファイルの作成

S3から複数ファイルを読み込むには、マニフェストファイルを作成します。詳細はマニュアルに記載があり、雛形としても使用できます。(PowerCenterのマニュアルですが同様の設定で問題ありませんでした。)
今回は、すべてのファイルが同じ階層にあるので、下記のように設定しました。マニュフェストファイルを作成したら、同じS3バケットにアップロードしておきます。

docs.informatica.com

{
    "fileLocations": [{
        "WildcardURIs": [
            "informatica/sample/data_*.csv"
        ]
    }, {
        "URIPrefixes": [
            "<バケット名>/"                       
        ]
    }],
    "settings": {
        "stopOnFail": "true"
    }
}

ジョブの作成

新規のマッピングタスクを起動し、ジョブを作成します。今回のジョブは下図の通りです。データを左から右に流すシンプルなつくりです。

f:id:yy16ki:20220416145553p:plain
ジョブ

  • ソースの設定

ソースのプロパティは下記のように設定しました。

f:id:yy16ki:20220416150432p:plain
ソース:設定

オブジェクトの設定では、先程作成したマニュフェストファイルを指定します。

f:id:yy16ki:20220416150722p:plain
オブジェクトの設定(黒塗りはバケット名)

形式には「フラットファイル」を指定します。オプションで区切り文字やヘッダーの有無を指定します。

f:id:yy16ki:20220416151132p:plain
形式:詳細オプション

また、今回は、スキーマを別ファイルで作成しインポートしています。
IICSで各種データを読み込む際は、データのスキーマを定義する必要があります。ソースがデータベースであればスキーマ情報を取得できますが、ファイルの場合、全項目が「String」型として扱われます。
列数が少ない際は、「フィールド」設定からGUIで編集しても良いですが、列が多い場合はミスの原因となります。
json形式で定義したスキーマ情報を適用することができるので、この場で指定しています。
スキーマは下記のように定義しました。

{"Columns":
    [
        {"Name":"id","Type":"number","Precision":"316","Scale":"0"},
        {"Name":"val","Type":"number","Precision":"316","Scale":"0"},
        {"Name":"file","Type":"string","Precision":"316","Scale":"0"}
    ]
}

ファイルを適用後、スキーマ情報が更新されていることを確認します。

f:id:yy16ki:20220416152050p:plain
スキーマ情報

  • ターゲットの設定

ターゲットの設定は下記の通りです。publicスキーマに、「informatica_out」という新規テーブルを作成することとしました。

f:id:yy16ki:20220416152305p:plain
ターゲット:設定

IICSでターゲットをRedshiftとする際は、ターゲットの詳細設定の「S3 Bucket Name」を指定しなければいけないので、今回使用するバケット名を記入しておきます。
また「Copy Options」に下記を記述し、インサート時にはコピーコマンドが呼び出されるようにしておきます。(事前にRedshiftへS3に対する権限は付与しておきます。)
「Copy Options」欄にコピーコマンドのオプションを記述する際のオプションの区切りにはセミコロンを使用することに注意してください。マニュアルでは、ロールARNの区切り文字にもセミコロンが使用されていますが、この部分はコロンでないとエラーとなります。

DELIMITER=,;AWS_IAM_ROLE=<iam role arn>

f:id:yy16ki:20220416153007p:plain
コピーコマンドオプション指定(マニュアル)

また、Redshiftのコピーコマンドの実行時は、Secure Agentに下記の設定を済ませておくことを推奨します。

docs.informatica.com

f:id:yy16ki:20220416155451p:plain
Secure Agent:Bulkプロセスの有効化

テーブルの確認

上述の設定後、ジョブを実行します。問題無く実行出来たら、Redshift側でテーブルを確認します。

f:id:yy16ki:20220416154314p:plain
ジョブ:モニタ画面

f:id:yy16ki:20220416154349p:plain
テーブル確認:件数

f:id:yy16ki:20220416154404p:plain
テーブル確認:ファイル

まとめ

有償版の「一括取り込み」機能であればもっと簡単に実装できるようですが、データ統合機能のみでも複数ファイル読み込みが実行できることを確認できました。この場合、Informaticaの機能というよりは、Redshiftの機能を呼び出すことになるのですが、複雑な処理を実装しようとすると、ツール以外のリソースの機能もある程度抑えておく必要がありそうです。
今回は以上になります。

TalendでELT処理を実施する

ETLツールとして有名なTalendですが、ELT処理を実施するためのコンポーネントが用意されています。データベース側の挙動もあわせて使用感を確認してみました。

前提条件

「Talend Open Studio for Data Integration」の「Version 8.0.1」で検証を実施しました。
ELT処理の対象となるデータベースには、Snowflakeを使用しました。 (Snowflake環境は構築済みとします。)

Snowflake側の準備

今回の検証用に、Snowflake側でデータベース・スキーマを定義します。

create database test;
create schema talend;

作成したスキーマに、統合処理に使うテーブルを定義します。

CREATE OR REPLACE TABLE STUDENT (
    ID VARCHAR(316),
    NAME VARCHAR(316)
);

INSERT INTO STUDENT (ID,NAME)
VALUES 
  ('C01','Shohei'),
  ('C02','Mike'),
  ('C03','Anthony'),
  ('C04','Jack'),
  ('C05','Max'),
  ('C06','Matt') ;
CREATE OR REPLACE TABLE TEST (
    ID VARCHAR(316),
    SUBJECT VARCHAR(316),
    SCORE NUMBER(38,0)
);

INSERT INTO TEST (ID,SUBJECT,SCORE)
VALUES 
  ('01','Math',60),
  ('01','English',70),
  ('02','Math',50),
  ('02','English',80),
  ('03','Math',70),
  ('03','English',80),
  ('04','Math',50),
  ('04','English',40),
  ('05','Math',80),
  ('05','English',30),
  ('06','Math',70),
  ('06','English',70)

下記の処理をTalendのELTコンポーネントで実装します。
SQLでいうところの、Join・Group By・Having句が入ってくるような処理を想定します。
1.「Student」テーブルと、「Test」をIDで結合(「Test」テーブルのID列の先頭に文字列'C'を付与し結合)
2.生徒ごとに全テストの合計点数を算出し、合計が130点以上の生徒を抽出

Talend側の処理

作成したジョブ

上記の処理をELTコンポーネントで、下図のように実装しました。

f:id:yy16ki:20220415184902p:plain
ジョブ全体図

Snowflakeとの接続

まずはリポジトリからSnowflakeの対象とするデータベースへの接続を作成します。

f:id:yy16ki:20220415183023p:plain
接続の作成

Snowflakeとの接続に必要な情報は下記の通りです。
AccountSnowflakeのコンソールのURLから取得
User IDSnowflakeのコンソールへログインする際のユーザー名
PasswordSnowflakeのコンソールへログインする際のパスワード
Warehouse:使用するウェアハウス(コンピュートエンジン)の名称
Schema:使用するスキーマの名称
Database:使用するデータベースの名称
Snowflakeのコンソールでは、URLのsnowflakecomputing.comの前に、アカウント情報・Snowflakeの構築に使用しているクラウドサービス情報が記載されています。Account欄にはこの情報を記載します。

f:id:yy16ki:20220415184344p:plain
アカウント情報

接続確認が取れたら、ジョブの作成に入ります。
ジョブ全体図のPrejob・Postjobの通り、データベースへの接続には各種の接続用コンポーネントを使用します。

tELTInputコンポーネント

ここから、ELT処理の実装に入ります。ELT処理を実施する際は、通常のInputコンポーネントではなく、tELTInputコンポーネントを使用します。今回は2つのテーブルを使用するので、2つ配置しています。 コンポーネントの設定は下図の通りです。

f:id:yy16ki:20220415185357p:plain
tELTInputコンポーネント:設定

スキーマ名をコンテキスト変数で設定し、テーブル名はそのまま入力しています。使用するテーブルのスキーマを定義し、MappingMapping Snowflakeを指定します。

tELTMap

統合処理には、tELTMapコンポーネントを使用します。設定は下記の通りです。

f:id:yy16ki:20220415190417p:plain
tELTMapコンポーネント:設定
細かいですがポイントとなる箇所を①~⑤で示しています。それぞれの詳細を後述します。

①:結合条件の指定
今回の場合、TestテーブルのID列先頭に文字列を付与する必要があります。TestテーブルのID列をStudentテーブルのID列にドラッグアンドドロップします。

f:id:yy16ki:20220415190929p:plain
結合条件の指定

式ビルダを開き、下記の通り文字列の結合処理を定義します。

f:id:yy16ki:20220415191102p:plain
文字列結合処理

ここでは、concat()を使用し、TestテーブルのID列先頭に文字Cを付与しています。Talend Studioには無くても、Snowflakeで定義されていれば関数を使用することができます。
最後には、StudentテーブルのID列・NAME列も表示したいので、新しい出力を下記の通り作成しておきます。

f:id:yy16ki:20220415191737p:plain
出力の作成

②、③:集計処理
生徒ごとの点数合計を作成します。出力の設定で「Add an other (GROUP...) clause」を押下し、式ビルダを表示します。

f:id:yy16ki:20220415192044p:plain
GROUP BY句の追加

f:id:yy16ki:20220415192341p:plain
集計処理の式ビルダ

式ビルダには、下記のように入力します。

GROUP BY context.SCHEMA.STUDENT.ID ,context.SCHEMA.STUDENT.NAME 

さらに今回は、集計後の値で絞り込みを行いたい(having句を記述したい)ので、再度、出力の設定から「Add an other (GROUP...) clause」を押下し、別の式ビルダを表示し下記の通り入力します。

HAVING SUM(context.SCHEMA.TEST.SCORE ) >= 130 

入力後は下記のようになっているはずです。

f:id:yy16ki:20220415193413p:plain
集計処理:設定後

④,⑤:集計列の追加
集計した生徒の合計点数は、テーブルでも表示したいので、画面右下の出力スキーマの編集箇所より列を追加し、列名・データ型を指定します。

f:id:yy16ki:20220415193758p:plain
出力列の追加

スキーマを追加すると、出力欄にも列が追加されるので、式ビルダより下記のように入力します。

SUM(context.SCHEMA.TEST.SCORE ) 

f:id:yy16ki:20220415194143p:plain
集計列の追加

これで統合処理の設定は完了です。tELTMapコンポーネントの設定画面左下の「Generated SQL」タブを開くと設定した内容をもとに、SQL文が生成されています。この画面は随時更新されるので、ここを確認しながら設定を進めていくことになるかと思います。

f:id:yy16ki:20220415194446p:plain
生成されたSQL

"SELECT
" +context.SCHEMA+ ".STUDENT.ID, " +context.SCHEMA+ ".STUDENT.NAME, SUM(" +context.SCHEMA+ ".TEST.SCORE ) 
FROM
 " +context.SCHEMA+ ".STUDENT , " +context.SCHEMA+ ".TEST
WHERE
  " +context.SCHEMA+ ".STUDENT.ID = concat('C', " +context.SCHEMA+ ".TEST.ID) 
GROUP BY " +context.SCHEMA+ ".STUDENT.ID ," +context.SCHEMA+ ".STUDENT.NAME 
HAVING SUM(" +context.SCHEMA+ ".TEST.SCORE ) >= 130 
"

tELTOutputコンポーネント

作成した統合処理の内容を、データベースで実行する方法を指定します。
今回は、下記のように新規テーブルを作成することとしました。スキーマ同期を忘れないように注意してください。

f:id:yy16ki:20220415194845p:plain
tELTOutputコンポーネント:設定

Snowflakeのログを確認

エラー無くジョブを実行出来たら、Snowflake側でログを確認してみます。

f:id:yy16ki:20220415195522p:plain
Snowflakeログ:CREATE文

f:id:yy16ki:20220415195545p:plain
Snowflakeログ:INSERT文

生成した統合処理をもとに、新規テーブルが作成されていることが確認できます。データも確認します。

select * from TALEND_OUT ;

f:id:yy16ki:20220415195923p:plain
データの確認

合計が130以上の生徒のデータに絞り込まれていることを確認できます。

まとめ

ELT処理となると、多少SQLの知識が求められますが、GUIでスムーズに構築ができる点はTalendの特徴に感じました。また、Talendの機能は制限されてしまいますが、その代わりデータベースの関数をそのまま使用できるので、SQLに慣れている人からすると、この方がジョブも構築しやすいのではないでしょうか。
今回は機能を使ってみたのみで、ELTのメリットである、データベース側のリソースを使用することによるETLとの処理性能のような観点は見れていないですが、この点も確認してみると面白いかもしれません。
今回は以上になります。

Talend StudioでS3からの複数ファイル読み込み・統合

Talend StudioでS3上にある,同スキーマではあるものの,ファイル自体が分割されたデータを統合する必要があったのでその際のメモになります.

前提条件

「Talend Open Studio for Data Integration」の「Version 8.0.1」で検証を実施しました.

作成したジョブ

今回作成したジョブは下図の通りです.

f:id:yy16ki:20220318235026p:plain
ジョブ

サンプルデータ

検証用に,下記のようなデータを用意しました.
システムから年月別に売上データをExcel形式で出力するようなイメージです.

f:id:yy16ki:20220318235415p:plain
サンプルデータ
このようなファイルがS3にアップロードされているとして,Talendから取得し加工できるように統合します.サンプルデータは,事前にS3へアップロードしておきます.キープレフィックスは「talendinput/」としています.
f:id:yy16ki:20220318235908p:plain
S3へアップロード

ジョブの構成

S3への接続

はじめに,PrejobとしてS3との接続を確立します.S3との接続には「tS3Connection」コンポーネントを使用します.
他にもS3関連のコンポーネントを使用するので,はじめに接続情報をもっておくと,後工程で使いまわすことができて便利です.

f:id:yy16ki:20220319001431p:plain
Prejob
「tS3Connection」コンポーネントの設定は下記の通りです.
個人の検証用なので,アクセスキー・シークレットアクセスキーを指定する方法としています.また,いずれもコンテキストを使用しています.
f:id:yy16ki:20220319002157p:plain
tS3Connection:設定

S3からのファイル取得

次に,S3からファイルを取得します.
「特定階層下のファイル名一覧の取得」→「取得した名称でファイルを指定しローカルへget」という手順になります.

f:id:yy16ki:20220319002555p:plain
S3からファイル取得
ファイル名一覧の取得には,「tS3List」を使用します.設定は下記の通りです.「tS3Connection」の接続情報を指定し,バケット・キープレフィックスをコンテキストで指定しています.
f:id:yy16ki:20220319002949p:plain
tS3List:設定
ファイルのローカルへの取得には,「tS3Get」を使用します.
f:id:yy16ki:20220319003411p:plain
tS3Get:設定
ここで,設定のバケット・キー・ファイルは下記の通り前の「tS3List」由来の変数として指定します.

Bucket:((String)globalMap.get("tS3List_1_CURRENT_BUCKET"))
Key:((String)globalMap.get("tS3List_1_CURRENT_KEY"))
File:context.FOLDER_PATH+((String)globalMap.get("tS3List_1_CURRENT_KEY")).replaceAll(context.KEY_PREFIX,"")

Talendでは,globalMapオブジェクトという形で,コンポーネントの情報を格納しており,ジョブの中で参照することが可能です.

www.talendbyexample.com

ジョブで使用しているコンポーネントのglobalMapオブジェクトはTalend Studio画面左下の「Outline」より参照可能です.「tS3List」の場合,バケット名・キープレフィックス等の情報が参照可能なことがわかります.

f:id:yy16ki:20220319010234p:plain
Outline:tS3List

試しに下記のようなフローを作成し,「tS3List_X_CURRENT_KEY」の中身を見てみます.
tS3Listの出力はiterateなので,「tlogrow」で閲覧するために「tIterateToflow」をはさんでいます.
※ここでは「tS3_List 2 CURRENT_KEY」となっていることに注意してください.

f:id:yy16ki:20220319010649p:plain
tS3_List_CURRENT_KEYの表示

f:id:yy16ki:20220321111351p:plain
tIterateToFlow:設定

今回の場合,出力は以下の通りです.「talendinput/」配下に目的のファイル名があることがわかります.「tS3Get」では,.replaceAll()メソッドを使用し,このキープレフィックス空欄に置き換えることで,ファイル名のみを取得し,指定のフォルダに出力しています.

f:id:yy16ki:20220319011011p:plain
tS3_List_CURRENT_KEY

「tS3Get」ファイル設定

context.FOLDER_PATH+((String)globalMap.get("tS3List_1_CURRENT_KEY")).replaceAll(context.KEY_PREFIX,"")

取得したファイルの統合(ユニオン)

「tS3Get」で出力したファイルを読み込み,統合(ユニオン)します.「対象のファイル名一覧の取得→読み込み→ユニオン」の流れです.

f:id:yy16ki:20220319012023p:plain
sampale

ファイル名の取得には,「tFile_List」を使用します.Directoryに先程の手順でファイルを出力したフォルダを指定します.今回は指定フォルダのすべてのファイルが対象なので特に指定はありませんが,特定の拡張子を持つファイルなど,対象を絞り込むことができます.

f:id:yy16ki:20220321111116p:plain
tFile_List:設定

出力したファイルは,「tFileInputExcel」で読み込みます.「tS3Get」の時と同様にファイル名は,「tFile_List」のglobalMapオブジェクトである((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))を指定します.

f:id:yy16ki:20220321111155p:plain
tFileInputExcel:設定

データの統合には,「tUnite」コンポーネントを使用します.このコンポーネントについては,下記の記事でわかりやすく取り上げられています.

dev.classmethod.jp

最後に,今回はtLogRowで出力します.出力結果は下記のようになりました.

f:id:yy16ki:20220321111242p:plain
出力

S3との接続を閉じる

Postjobとして,明示的にS3との接続を閉じておきます.

f:id:yy16ki:20220321123907p:plain
PostJob

まとめ

TalendでS3→ローカルへのファイル展開・統合処理を実施してみました.GUIでこういった処理を機械的に実行できるのは便利だなと感じました.今回は以上になります.

Talend Open Studio 8.0.1 のインストール

オープンソースのETLツールとして有名なTalend の無償試用版である Talend Open Studio を使用しようとしたところ,実行するまでにはまったので,インストール手順も含め記事にしました.結論からいうと,Talend側の言語設定が日本語だったことが問題で,英語にすれば問題無く実行できるようになりました.
※こちらは2022/2/22時点の検証結果になります.

インストール環境

下記の環境にインストールを試みました.

Windows 11 Home

事前準備

Java環境の構築

Talend製品を使用するには,Java環境を用意する必要があります.Talend Open Studio 8.0 の場合,Javaバージョン11と互換性があるようです.
Talendをインストールする前に,下記よりJava環境をインストールします.

help.talend.com

Javaをインストール後,JAVA_HOME環境変数を設定しておきます.
こちらも公式ドキュメントに記載がある通り進めれば問題ありませんでした.

help.talend.com

Talend Open Studioのインストール

Java環境の用意ができたら,下記よりTalend Open Studio をインストールします.

www.talend.com

Java環境の用意が問題無くできていれば,Talendが開き下記の通りサードパーティのライブラリをインストールすることを求められるので,問題無ければ同意し,インストールします.

f:id:yy16ki:20220304222018p:plain
ライセンスのインストール

Talend起動と合わせて,ジョブのデモがインストール可能なのでこちらを実行してみます.

f:id:yy16ki:20220304222336p:plain
デモジョブ実行画面

が,下図の通りエラーとなってしまいました.

f:id:yy16ki:20220304222533p:plain
エラーメッセージ

メッセージによると,Java環境の互換性がないことが疑われましたが,インストールしたJava環境は公式が推奨するものですし,パスも問題無く通っていることは何度も確認しました.(結局これが問題ということはありませんでした...)

Talendでは,ジョブの構築・実行とあわせてJavaソースコードが自動生成されます.
下図の赤枠内の「code」を押下すると,生成されたコードが表示されます.ジョブの実行ができなくても,コードは生成されており,エラー箇所がわかるようになっています.ここからエラー箇所を読み取り,ジョブを修正することができます.

f:id:yy16ki:20220304223400p:plain
codeを表示する

下図が今回エラーとなったデモジョブのコードの一部です.
画面右側の赤色表示されている箇所を押下すると,エラー箇所にとぶことができます.

f:id:yy16ki:20220304224019p:plain
生成されたコード

今回のエラー箇所は下記の通りで,実際のコードというよりもはじめのコメントアウトが外れておりコンパイルエラーとなっているみたいでした.

f:id:yy16ki:20220304223844p:plain
エラー箇所

色々と試行錯誤しましたが,実行ファイル内からこのコメント生成箇所を特定・変更することはできませんでした.試しにとTalend Open Studioの言語設定を「ウィンドウ > Preferences > Talend > 国際化」より英語に変更し,一度Talend Studioを再起動します.

f:id:yy16ki:20220304224613p:plain
言語設定の変更

先ほどはエラーとなったデモジョブを再度実行してみると...
問題なく実行されました!

f:id:yy16ki:20220304225733p:plain
ジョブ実行完了画面

コードを見てみると,日本語設定だとコメントアウトが外れていたものがなくなっていました.

f:id:yy16ki:20220304225951p:plain
生成されたコード

言語を英語に変更した後,いくつかジョブを作成してみましたが,ひとまず問題はなさそうです.
他の方も日本語版でインストールされた場合は同様の現象となっているのでしょうか...
今回は以上になります.

AWS CLIでEC2インスタンスの起動から接続まで

業務にてawsを使用することになりました.環境構築には,AWS CLI(Command Line Interface)を使用することにもなったので、awsCLIの操作に慣れるために,簡単な構成作成の手順を残しておきます.

作成対象

今回はaws初心者がよくはじめに作成するであろう,下記の構成をCLIで作成します.

f:id:yy16ki:20220219164129p:plain
作成対象

前提条件

環境構築には,admin権限を持つIAMユーザーを使用します.
CLIでの作業用に,アクセスキーID・シークレットアクセスキーは発行済みとしています.

CLIでの作業

タグ付け用の変数を定義しておきます.

NAME=practice

VPCの作成

VPCは,create-vpcコマンドで作成できます.

aws ec2 create-vpc \
    --cidr-block 10.0.0.0/16 \
    --instance-tenancy default \
    --tag-specifications "ResourceType=vpc,Tags=[{Key=Name,Value=${NAME}-vpc}]"

作成後はIDを取得しておくと,後々の作業がしやすいです.

VPC_ID=$(aws ec2 describe-vpcs --filters Name=tag:Name,Values=${NAME}-vpc \
    --query "Vpcs[*].VpcId" \
    --output text)

実行後,マネジメントコンソールでも確認しておきます.
「practice-vpa」が作成されています.

f:id:yy16ki:20220219165338p:plain
VPCの作成

インターネットゲートウェイの作成

インターネットゲートウェイを作成は,create-internet-gatewayを使用します.

NTERNET_GATEWAY_ID=$(aws ec2 create-internet-gateway \
    --tag-specifications "ResourceType=internet-gateway,Tags=[{Key=Name,Value=${NAME}-igw}]" \
    --query "InternetGateway.InternetGatewayId" \
    --output text)

作成できたらattach-internet-gatewayで先ほどのVPCのIDを指定し,紐付けます.

aws ec2 attach-internet-gateway \
    --internet-gateway-id $INTERNET_GATEWAY_ID \
    --vpc-id $VPC_ID

マネージメントコンソールからも確認します.
状態が「Attached」で,VPC IDが先程作成したVPCのものになっているはずです.

f:id:yy16ki:20220219165857p:plain
インターネットゲートウェイVPCにアタッチ

Subnetの作成

VPC内に,パブリックサブネットを作成します.アベイラビリティーゾーンを指定する場合は、引数[--availability-zone <value>] [--availability-zone-id <value>] のいずれか一方のみの指定でOKです. ここでは、idとしてus-west-2aを指定しています.また,サブネット作成とあわせてIDも変数に格納しています.

PUBLIC_SUBNET_ID=$(aws ec2 create-subnet \
    --vpc-id $VPC_ID \
    --cidr-block 10.0.1.0/24 \
    --availability-zone-id usw2-az1 \
    --tag-specifications "ResourceType=subnet,Tags=[{Key=Name,Value=${NAME}-public-subnet-2a}]" \
    --query "Subnet.SubnetId" \
    --output text)

アベイラビリティーゾーンとして指定可能なIDや名称はaws ec2 describe-availability-zonesで確認可能です.

ルートテーブルの作成と編集

create-route-tableで,ルートテーブルを作成します.

PUBLIC_ROUTE_TABLE_ID=$(aws ec2 create-route-table \
    --vpc-id $VPC_ID \
    --tag-specifications "ResourceType=route-table,Tags=[{Key=Name,Value=${NAME}-public-route}]" \
    --query "RouteTable.RouteTableId" \
    --output text)

作成したルートテーブルに,インターネットゲートウェイへのルートを登録します.

aws ec2  create-route \
    --route-table-id $PUBLIC_ROUTE_TABLE_ID \
    --destination-cidr-block 0.0.0.0/0 \
    --gateway-id $INTERNET_GATEWAY_ID

インターネットゲートウェイへのルートが登録されたルートテーブルをサブネットに紐づけることで,パブリックサブネットとします.

aws ec2 associate-route-table \
    --route-table-id $PUBLIC_ROUTE_TABLE_ID \
    --subnet-id $PUBLIC_SUBNET_ID

f:id:yy16ki:20220219171402p:plain
マネジメントコンソール

セキュリティグループの作成

EC2に付与するセキュリティグループを作成します.

SECURITY_GROUP_ID=$(aws ec2 create-security-group \
  --group-name ${NAME}-ec2-sg \
  --description ${NAME}-ec2-sg \
  --vpc-id $VPC_ID \
  --tag-specifications "ResourceType=security-group,Tags=[{Key=Name,Value=${NAME}-ec2-sg}]" \
  --query "GroupId" --output text)

今回は,自身のIPアドレスからのSSH接続を許可することとします.
curlコマンドでIPアドレスを取得します.

IP=$(curl -s https://api.ipify.org)

作成したセキュリティグループのインバウンドルールに、自身のIPからのSSH接続を許可するルールを追加します.

aws ec2 authorize-security-group-ingress \
  --group-id $SECURITY_GROUP_ID \
  --protocol tcp \
  --port 22 \
  --cidr ${IP}/32

キーペアの作成

EC2インスタンスへのSSH接続用のキーペアを作成します.下記のコマンドは、キーペアの作成と,作業フォルダへのpemファイル作成を実行しています.

aws ec2 create-key-pair \
  --key-name ${NAME}-key \
  --query "KeyMaterial" \
  --output text > ${NAME}-key.pem

EC2インスタンスの作成

作成するEC2インスタンスのAMI IDを指定します.

AMI_ID=$(aws ssm get-parameters --names /aws/service/ami-amazon-linux-latest/amzn2-ami-hvm-x86_64-gp2 --query "Parameters[*].Value" --output text) && echo ${AMI_ID}

出力

ami-090bc08d7ae1f3881

こちらは,2022/2時点で無料利用枠で使用開始可能なAMIになります.

f:id:yy16ki:20220219172912p:plain
マシンイメージ

プライベートIPアドレスも指定してみます.プライベートIPアドレスは、サブネットの範囲内であることに注意します.

PRIVATE_IP_ADDRESS="10.0.1.11"

run-instancesインスタンスを起動します.特に引数を指定しない場合は,デフォルトの仕様が適用されますが,キーペアを使用する際はここで--key-nameで対象のキーペアを指定する必要があります.
インスタンスタイプは引数--instance-typet2.microを指定しています.(デフォルトではm1.small)となります.

INSTANCE_ID=$(aws ec2 run-instances \
  --image-id $AMI_ID \
  --instance-type t2.micro \
  --count 1 \
  --subnet-id $PUBLIC_SUBNET_ID \
  --associate-public-ip-address \
  --private-ip-address $PRIVATE_IP_ADDRESS \
  --tag-specifications "ResourceType=instance,Tags=[{Key=Name,Value=${NAME}-ec2}]" \
  --security-group-ids $SECURITY_GROUP_ID \
  --key-name ${NAME}-key \
  --query "Instances[*].InstanceId" \
  --output text)

しばらくして,インスタンスが作成されたら,パブリックIPアドレスを取得します.

PUBLIC_IP_ADDRESS=$(aws ec2 describe-instances --instance-id $INSTANCE_ID --query "Reservations[*].Instances[*].PublicIpAddress" --output text)

上述の手順で作成したキーペアとインスタンスのパブリックIPアドレスを指定し,下記コマンドでSSH接続を行います.

ssh -i ${NAME}-key.pem ec2-user@$PUBLIC_IP_ADDRESS

ターミナルに下記の出力が表示されればOKです.

f:id:yy16ki:20220220012503p:plain
EC2への接続確認

まとめ

今回はCLIの基本操作を,簡単な構成を作成しつつ練習しました.
AWS CLIについては,いくつか記事にしようと考えているので,次回以降は少しずつ複雑な構成を作成しいてみたいと思います.
※リソースの削除を忘れずに...

Alteryx Write Data In-DB Toolを用いた新規テーブル作成②

本記事は,「Alteryx Write Data In-DB Toolを用いた新規テーブル作成①(Redshift環境構築~Alteryxからの接続)」の続きにあたります.Write Data In-DB ToolをRedshiftで使用する際に,AWSコンソール側での見え方やこのツールの挙動自体を記した記事は中々見当たらなかったため,前回構築した環境にて下記の内容を確認していきます.

1.  Write Data In-DB Toolの概要

1.1. オプション

ツールで使用可能なオプションは下記の5つです.
1. 新規テーブルを作成する
2. 既存のものを付加する
3. データを削除して付加する
4. テーブルを上書きする(ドロップ)
5. 一時テーブルを作成する

1.2. オプションごとに必要な権限

上述のオプションを実行する際は,Alteryxで接続したユーザーが操作対象のテーブルに対して,適切な権限を持つ必要があります.それぞれ下記の権限が必要となります.

f:id:yy16ki:20220115163440p:plain
オプションと実行に必要な権限

以降の内容は,この検証となります.

2.  管理者ユーザーでのツール使用

権限周りの前に,ツールの挙動・AWSコンソール上からの見え方を確認しておきます.まずは,管理者権限で以降の処理を試します.

2.1. 新規テーブルの作成

管理者権限でAlteyxからRedshiftに接続し,デフォルトで用意されている「category」テーブルに接続します.テーブルの内容は下記の通りとなっています.

f:id:yy16ki:20220111225525p:plain
categoryテーブル

このテーブルに対し,下記の処理を実行します.③のWrite Data In-DB Toolにて,出力モードを「新しいテーブルを作成する」とし,集約後のデータを書き出しています.

f:id:yy16ki:20220111225919p:plain
ワークフロー:新規テーブルの作成
f:id:yy16ki:20220111230200p:plain
Write Data In-DB Toolの設定

フロー実行後,Redshift上でテーブルを確認します.クエリエディタで下記の処理を実行します.

SELECT DISTINCT tablename 
FROM pg_table_def 
WHERE schemaname = 'public' AND tablename NOT LIKE'%_pkey' 
ORDER BY tablename; 

クエリ結果画面にて,Alteryxで作成した「aws_user_test1」テーブルを確認できました.

f:id:yy16ki:20220111233732p:plain
テーブルの確認
f:id:yy16ki:20220111234650p:plain
データの確認

今回は特に設定などしていませんが,デフォルトで作成される「public」スキーマ上にテーブルが作成されるようです.

2.2. テーブルの更新

先程作成した「aws_user_test1」テーブルの内容を更新します.「catgroup」が「Sports」の行を削除するように変更してみます.ワークフローを下記の通り,Write Data In-DB Toolの出力モードを「データを削除して付加する」に変更し,データを書き出します.

f:id:yy16ki:20220111235241p:plain
ワークフロー:テーブルの更新

実行後,Redshift上でテーブルを確認すると下記の通り,「catgroup」が「Sports」を除くデータにテーブルが変更されていました.

f:id:yy16ki:20220111235808p:plain
データの確認

Alteryx上のツール実行時メッセージを確認すると,下記の表示となっており,公式ドキュメントの記載通り「元あったデータをすべて削除→ツールのインプットとなるデータを追加する」という処理が実行されていることが見てとれました.

f:id:yy16ki:20220112000340p:plain
Alteryx:ツール実行時メッセージ

なお,このオプション実行時は,下図赤枠内のさらに詳細な設定を選択可能となります. いずれもデータ付加時に,ツールのインプットデータの列構成が更新対象テーブルと異なる場合に,どのような処理とするかを制御可能です.こちらは公式ドキュメントに詳細の記載があるので,そちらをご参照ください.

f:id:yy16ki:20220112000923p:plain
詳細設定

2.3. テーブルの上書き

次に「テーブルを上書きする(ドロップ)」オプションを実行して見ます.「category」を接続元とし,下記のフローを実行します.ここでは,上書き対象のテーブル名として先程作成した「aws_user_test1」テーブルとしています.こちらは問題無く実行されます.

f:id:yy16ki:20220112190322p:plain
ワークフロー:テーブルの上書き
f:id:yy16ki:20220112190854p:plain
Alteryx:ツール実行時メッセージ

処理としては,「元あったテーブルを削除」する処理が実行されているので,作成済みのテーブルと異なるテーブル構造で同じ名称を指定しても,新たなテーブルが作成されることが見てとれました.作成したテーブルの構造自体を変更したい場合は,このツールが使えそうですが,既存テーブルをドロップすることになるため,誤って既存の他テーブルを指定しないように注意する必要がありそうです.

2.3. 一時テーブルの作成

データ加工には必要だが,新規でテーブルを作成するほどの必要が無い場合はこのオプションが便利です.「新規テーブルの作成」と同様のフローでツールオプションを「一時テーブルを作成する」に変更し,実行するとツールの結果ウィンドウに下記のようなメッセージが表示されます.

f:id:yy16ki:20220115113701p:plain
ワークフロー:「一時テーブルを作成する」オプションの実行

ツール側で機械的に付与された名称でテーブルが作成されていることがわかります.一時テーブルですので,ツール実行後にクエリエディタで確認しても,このテーブルは確認できません.
このオプションは,「データストリームイン」ツールでも使用でき,In DB以外のツールで加工したデータをDB上で扱う際に便利です.

3.  一般ユーザーでのツール使用

3.1 新規ユーザーの作成

先程はすべての権限をもつ管理者ユーザーでの実行だったので,各種オプション実行に必要な権限を確認するために,一般ユーザーを作成します.Redshift上のクエリエディタに管理者ユーザーで接続し,下記の通りユーザーを作成します.

CREATE USER test_user PASSWORD 'Testuser1' ;

一般ユーザーの「public」スキーマ上の各テーブルに対する,デフォルトの権限は以下の通りです.管理者ユーザーである「aws_user」はすべての権限を持ちますが,先程作成した「test_user」は,デフォルトで下記の権限のうちでは,「public」スキーマに対するusage,create権限を持ちます.

f:id:yy16ki:20220112003525p:plain
ユーザーごとのテーブルに対する権限

3.2 一般ユーザーでの接続

  • SELECT権限
    前回の記事の手順通り,先程作成した一般ユーザー「test_user」でRedshiftに接続します.「category」テーブルに接続し,下記のフローを実行します.デフォルトでは,テーブルに対するSELECT権限がないので,Alteryx上では閲覧ツールによる参照が拒否されました.
    f:id:yy16ki:20220112004721p:plain
    ワークフロー:一般ユーザーのデフォルト権限での実行結果
    Redshift上で,下記の通り「category」テーブルに対するSELECT権限を付与します.
CREATE USER test_user PASSWORD 'Testuser1' ;
f:id:yy16ki:20220112005409p:plain
test_userへのcategoryテーブルに対するSELECT権限の付与

SELECT権限を付与した「category」テーブルと,未付与の「aws_user_test1」テーブルへの参照を実施します.結果は下図の通り,SELECT権限を付与した「category」テーブルは閲覧でき,SELECT権限の無い「aws_user_test1」テーブルは参照できないことが確認できました.

f:id:yy16ki:20220112005800p:plain
ワークフロー:categoryテーブルへのSELECT権限付与後の実行結果
  • CREATE権限
    続いて,一般ユーザーで新規テーブル作成を行います.デフォルトでは,「public」スキーマへのCREATE権限が付与されているので,問題無く実行されます.
    f:id:yy16ki:20220112183123p:plain
    ワークフロー:新規テーブルの作成(一般ユーザー)
    f:id:yy16ki:20220114185404p:plain
    一般ユーザーのデフォルト権限で作成したテーブル
    一般ユーザーで作成したこのテーブルに対する各種権限を確かめます.結果は下図の通り,管理者ユーザーはもちろんとして,テーブルを作成した一般ユーザーは一覧にしているすべての権限を持つことが確認できました.
    f:id:yy16ki:20220112183618p:plain
    一般ユーザーで作成したテーブルのユーザーごと各種権限
    ちなみに,参照権限の無いテーブルを使用し,新規テーブルを作成しようとした場合,そもそも閲覧権限がないため,実行エラーとなります.
  • INSERT権限DELETE権限
    ツールのプションでINSERTまたはDELETE処理が必要と考えられるものは下記の2オプションです.これを確認します.
    ・ 既存のものを付加する
    ・データを削除して付加する
    一般ユーザーで,先程作成した新規テーブルに対し,下記のフローを実行します.
    f:id:yy16ki:20220114185959p:plain
    ワークフロー:「既存のものを付加する」オプションの実行
    ここでは,「既存のものを付加する」オプションを実行し,下記のデータを追加しています.
catname count
TEST 1

ツール実行後,AWSのクエリエディタで確認すると追加されていることが確認できます.

SELECT *
FROM test_user_test
WHERE catname = 'TEST' ;
f:id:yy16ki:20220114190752p:plain
追加されたデータ

続いて同じテーブルを対象に上記のフローで,WriteData In DBツールのオプションを「データを削除して付加する」に変更し実行し,下記のデータを追加してみます.

catname count
delete 1

こちらも問題無く実行され,データも指定したデータのみになっていることがマネジメントコンソール上からも確認できました.

f:id:yy16ki:20220114192448p:plain
オプション「データを削除して付加する」実行後

それでは,権限を削除して確認を行います.
一般ユーザーの作成したこのテーブルに対するDELETE権限を下記の通り削除します.

REVOKE DELETE ON test_user_test FROM test_user ;

その後,再度「データを削除して付加する」オプションを実行すると下記の通りエラーとなります.

f:id:yy16ki:20220114193016p:plain
ワークフロー:「データを削除して付加する」オプションの実行

この状態では,オプションを「既存のものを付加する」に変更しても問題無く実行されます.
クエリエディタで下記を実行し「INSERT権限」も削除すると,「既存のものを付加する」オプションも実行できなくなります.

REVOKE INSERT ON test_user_test FROM test_user ;
  • DROP権限
    先程までのクエリを実行したときの一般ユーザーの権限は下記の通りです.
    f:id:yy16ki:20220114194412p:plain
    テストテーブルへの権限
    オプション「テーブルを上書きする(ドロップ)」では,「テーブルのドロップ→新規テーブルの作成」の処理を実行するので,このまま一般ユーザーで実行できるはずです.下記のフローを実行します.
    f:id:yy16ki:20220114194830p:plain
    ワークフロー:「テーブルを上書きする(ドロップ)」オプションの実行
    このフローは問題無く実行されます.また,ここで新規テーブルとして作成されるので,再度同じ名称のテーブル権限を確認すると下記の通り,すべての権限が付与されていることに注意してください.
    f:id:yy16ki:20220114195046p:plain
    テーブルを上書きする(ドロップ)後の権限
    最後に,一般ユーザーで作成したテーブルの所有者を管理者ユーザー(別のユーザー)に移譲します.この状態のままツールを実行するとエラーとなります.
ALTER TABLE test_user_test OWNER TO test_user;

所有者の権限移譲後,管理者ユーザーで下記の通り,一般ユーザーのドロップ権限のみを付与し,ツールを実行すると,ワークフローもエラー無く実行されることが確認できました.

GRANT DROP ON test_user_test TO test_user ;

4.  public以外のスキーマの使用

次に,public以外のスキーマに属するテーブルを使用した場合の一部処理を確認します.

4.1 スキーマの作成

下記の通り,スキーマを作成し,一般ユーザーである「test_user」に権限を付与します.

CREATE SCHEMA test_schema AUTHORIZATION test_user ;

作成した「test_schema」にテスト用テーブルを作成します.

CREATE TABLE test_schema.test_tbl (id int) ;
INSERT INTO test_schema.test_tbl VALUES
(1),
(2),
(3),
(4) ;

このスキーマとテーブルに対する権限は下記の通りです.

f:id:yy16ki:20220112194700p:plain
新規スキーマへの権限

4.2 publicスキーマ以外に属するテーブル接続とテーブル作成

Alteryxから管理者ユーザーで,「test_schema」上の「test_tbl」に接続し下記の通りフローを実行します.

f:id:yy16ki:20220112195232p:plain
ワークフロー:テストスキーマへの接続と新規テーブル作成

フロー実行後,AWSのクエリエディタでテーブルの定義を確認します.

SELECT * FROM pg_table_def WHERE tablename = 'aws_user_test2' ;
f:id:yy16ki:20220115120439p:plain
テーブル定義

publicスキーマ以外のデータのみを使用したとしても,Alteryxからテーブルを作成する場合は,publicスキーマが指定されテーブルが作成されるようです.

5.  データ型

最後に,Alteryx側で新規に列を追加した場合,Redshift上ではどのようなデータ型に変換されるのかを見ていきます.
「テストスキーマへの接続と新規テーブル作成」にて使用したワークフローのフォーミュラツールのオプションを下記の通りとします.

f:id:yy16ki:20220115123802p:plain
フォーミュラツールの設定

この実行結果より作成された5列のデータ型をRedshift上で確認すると下記の通りとなっていました.

f:id:yy16ki:20220115123941p:plain
Type列がRedshift上でのデータ型(columnはAlteryxのデータ型名)

Alteryxのデータ型からそれぞれ自動的に変換してくれるようです.ただし,可変長文字列や実数のスケールは,ツールインプットを格納可能な最大値を設定してくれるようです.
試しに,このテーブルに下記のデータを追加してみます.

id v_wstring int64 double bool
5 ABCDEFGHIJ 1 1.0 TRUE

フローは下記の通りです.

f:id:yy16ki:20220115125818p:plain
ワークフロー:「既存のものを付加する」オプションの実行

すると下記のようなエラーが出力されます.

Error: データ書込In-DB (20): PreSQLを実行: "INSERT INTO "datatype"WITH "Tool18_42e7" AS (SELECT * FROM "AYX2201155ffd41cf519f7c808d2a3b4f2e5f3b50")  SELECT "id", "v_wstr..." : [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState XX000] ERROR:  Value too long for character type
DETAIL:  
  -----------------------------------------------
  error:  Value too long for character type
  code:      8001
  context:   Value too long for type character varying(9)
  query:     1620
  location:  string.cpp:192
  process:   query0_127_1620 [pid=10621]
  -----------------------------------------------

新規テーブル作成時に,そのデータから最適なスケールを決めてくれるので,後々その容量を上回るデータを追加する場合は,データ構造の変更が必要なようです.

Alteryx Write Data In-DB Toolを用いた新規テーブル作成①(Redshift環境構築~Alteryxからの接続)

AlteryxのIn-DB機能にて使用可能なWrite Data In-DB Tool(データ書き込みIn-DBツール)を用いて,データベース上にテーブルを作成する機会がありました.ツールの使用に際して,いくつか確認・調査の必要があり,検証用の環境を用意する必要があったので,一連の流れを記事として残しておくこととしました.本記事では,Redshiftのクラスター構築からAlteryx経由での接続・データ表示までの手順を記載しています.実際にWrite Data In-DB Toolを使用する工程は,次の記事に記載する予定です.

本記事の内容は下記の通りです.

1. Alteryx

1.1. 概要

本題に入る前に,Alteryx(アルタリクス)について簡単に説明を.

Alteryxを一言でいうなれば,GUI版のSQLというとイメージしやすいでしょうか.データの準備,クレンジングから一部分析までもをワンストップで実施可能なツールです.SQLはデータベース上のデータを操作する言語ですが,Alteryxはデータベースのみならず,現場でよく用いられるようなExcelCSVファイルといった様々なインプットデータに対応し,その加工を柔軟かつ強力にサポートします.

実際にAlteryxでデータを操作する際は,「ワークフロー」というものを作成します.ワークフロー内に,特定の機能を持つアイコンを配置し,データの加工を行っていきます.SQLやその他プログラミング言語では,実際に「コード」にあたる箇所を,このワークフローが代替しており,各種アイコンの意味がわかれば,プログラミング言語に習熟することなくその成果物や処理を再現可能なまま共有することが可能となります.

1.2. In-DB機能

alteryxには,実行環境のメモリ上ではなく,直接データベース上で処理を実行可能な機能が備わっています.これをIn-DBといい,通常よりも使用可能な処理(ツール)は限定されますが,充分に強力なデータ加工をGUIで実行可能です.今回,仕様の調査対象となる,「Write Data In-DB Tool」はその名の通りIn-DB機能となります.

alteryxのIn-DB機能では,本記事で扱うAmazon Redshiftをはじめとした主要なデータベース/データウェアハウスと連携することが可能です.詳細は公式のドキュメントをご参照ください.

help.alteryx.com

 

Tableau等のBIツールでデータの可視化をする際は,元データをある程度可視化しやすい形まで整形する必要がある場合が多々あるかと思います.BIツール側でもデータの加工自体は可能ですが,元データからの加工処理が複雑な場合には,alteryxのようにデータ加工に特化したツールを用いる場合もあるでしょう.

他のBIツールもデータベースと連携可能な場合が多いので,In-DB機能を通すことでほぼノンコーディングでデータ加工~可視化までを容易に実施可能となります.

1.3. Write Data In-DB Tool

In-DB機能で使用可能なツールの一つに,データベースに直接テーブルを作成可能なWrite Data In-DB Toolがあります.

このツールを使用することで,Alteryxで加工済みのデータを新規テーブルとしてデータベース上に作成・更新といった処理を実行可能となります.ツールの詳細は下記の公式ドキュメントをご参照ください.

help.alteryx.com

2. データ連携の流れ

今回は,Amazon RedshiftをIn-DB機能で接続する対象とし,Write Data In-DB Toolの仕様を確認します.Redshiftとの連携イメージは下図の通りです.

※本記事では,Alteryxのツール仕様の確認が主目的のため,Redshift側の構成はほぼデフォルト(最低限)です.

f:id:yy16ki:20220111131651p:plain

連携のイメージ

3. Redshift環境の構築

3.1. クラスターの作成

Amazon Redshiftは,Amazon Web ServicesAWS)が提供する,データウェアハウス(DWH)です。Redshiftは「PostgreSQL」がベースとなって開発されており,クラスターと呼ばれる単位での構成となっています.作成手順は下記通りです.

AWSマネジメントコンソールよりRedshiftを検索し,「クラスターを作成」を押下します.

f:id:yy16ki:20220110130639p:plain

クラスターの作成①

クラスターの名称,用途を選択します.今回は検証用のため「無料トライアル」を選択します.

f:id:yy16ki:20220110130852p:plain

クラスターの作成②

③管理者名,管理者パスワードを設定します.

f:id:yy16ki:20220110131239p:plain

クラスターの作成③

最後に「クラスターを作成」を押下すると,設定した内容でクラスターが作成されます.

デフォルトの設定では,クラスター作成とあわせて「dev」という名前のデータベースが作成され,サンプルデータがインポートされます.今回はそのデータを使用していくこととします.

3.2. 外部接続の許可

クラスターが作成できたら,クラスターの選択画面より対象のクラスターを選択します.

f:id:yy16ki:20220110132359p:plain

作成されたクラスターの選択

作成されたクラスターについて,デフォルトでは,パブリックアクセスが「無効」となっているのでこの設定を「有効」とし,Alteryxから接続可能なように変更します.

f:id:yy16ki:20220110132706p:plain

パブリックアクセスの有効化①

パブリックアクセスが有効化されると,プロパティタブの「ネットワークとセキュリティの設定」欄の「パブリックアクセス可能」が「有効」となります.

f:id:yy16ki:20220110133042p:plain

パブリックアクセスの有効化②

4. Alteryxの準備

手元の端末にAlteryxを実行可能な環境を用意します.ツールを使用するには,ライセンスの購入が必要ですが,Alteryxを使用したことのない人や学生等は,下記の公式サイトより無料トライアルを活用できます.なお,無料トライアルの期間が終了すると,ライセンスの認証が切れ,Alteryxを使用することはできなくなりますが,自動的に料金が発生するようなことはありません.

www.alteryx.com

インストールとライセンスの認証が完了すると,実際にツールを使用可能となります.今回調査対象となるIn-DBに関するツールは,画面上部の「ツールパレット」にて「インデータベース」タブを選択することで一覧が表示されます.

f:id:yy16ki:20220110012226p:plain

インデータベースアイコン

5. Redshiftへの接続

5.1. 接続方法

新規のワークフローを開き,「データ入力ツール」をドラッグアンドドロップします.

f:id:yy16ki:20220110133715p:plain

データ入力ツール

ツールの設定より,データ接続方法として「データソース」を選択します.すると下記のように使用可能なデータソースが一覧化されるので,「Amazon Redshift」のODBC接続を選択します.

f:id:yy16ki:20220110134031p:plain

データソースの選択

下記のようなポップアップが表示されたら,「ODBCアドミニストレーター」を押下します.

f:id:yy16ki:20220110134106p:plain

ODBCアドミニストレーター

「ドライバー」タブを開き,Redshiftのドライバーがインストールされていることを確認します.

f:id:yy16ki:20220110134253p:plain

ドライバーの確認

Redshiftのドライバーのインストールを確認出来たら,「ユーザーDNS」タブを開き,「追加」を押下し,「Amazon Redshift」ドライバーが選択された状態で「完了」を押下します.

 

f:id:yy16ki:20220110135125p:plain

接続の追加

下記のような設定を入力する画面が表示されたら,接続対象となるデータベースの名称・接続するユーザーの情報を入力します.これらの情報はRedshiftのクラスター情報画面から確認できます.

f:id:yy16ki:20220110140034p:plain

接続設定

必要な情報を入力し,画面下「Test」を押下し下記のポップアップが表示されれば,問題無いです.

f:id:yy16ki:20220110140450p:plain

接続の確認

5.2. In-DB接続の管理

続いてIn-DB接続の管理を行います.「接続In-DBツール」をドラッグアンドドロップし,「接続名を作成または選択」から,「接続を管理」を押下します.

f:id:yy16ki:20220110141842p:plain

接続の作成①

「接続の管理」にて,今回は下記の通り接続するユーザーごとの接続を作成します.ひとまず,管理者ユーザーである「awsuser」に関する接続を作成します.

f:id:yy16ki:20220110232111p:plain

接続の作成②

その後,先程作成した接続方法(今回の接続名は「awsuser」)を選択すると,下記の通り,テーブルの選択画面が表示されます.

f:id:yy16ki:20220110232702p:plain

テーブルの選択

試しに「public」スキーマの「category」テーブルを選択し下記の通りフローを実行すると,データの内容を一部確認することができます.

f:id:yy16ki:20220110232905p:plain

フローの実行とデータ確認

6. まとめ

今回は,Redshiftのクラスター構築からAlteryx経由での接続・データ表示までを実施しました.これにより,以降はデータ分析に必要な前処理工程を,Alteryxの強力なデータ加工機能を用いてノンコーディング実施できます.

次の記事では,データ分析に用いるためにAlteryxで加工したデータを新規テーブルとして実際にRedshift上に作成する手順をしながら表記のWrite Data In-DBツールの詳細を確認します.


スポンサードリンク