Go to Qiita Advent Calendar Top
LoginSignup
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@mohkawainIBM

watsonx.dataで安価なオブジェクトストレージに保存したデータをCognosからJDBC接続して分析してみた

Last updated at Posted at 2023-11-18

以下の記事にてwatsonx.dataで安価なオブジェクトストレージにテーブルicos1.hr.hr_trainingを作成してデータを投入したので、そのデータをCognosからJDBC接続して分析してみた記事です。

watsonx.dataはオープンなデータレイクハウス・アーキテクチャに基づいて構築されており、大容量のデータを安価に保存できるオブジェクトストレージにテーブルを作成したり、他のデータベースを統合することができ、それらのデータベースのデータをwatsonx.dataに組み込まれているオープンソースのPrestoエンジンを使って横断的にSQL文でアクセスできます。

この記事の内容

  • 以下の環境を作って試しました
    • IBM Cloud Pak for Data(CP4D)上にwatsonx.dataとCognos Analytics with Watsonを導入
      • CP4D バージョン: 4.7.3
      • watsonx.data (SW版) バージョン: 1.0.3
      • Cognos Analytics with Watson バージョン: 24.3.0
  • Cognos Analytics with Watson(Cognos)からwatsonx.dataのPrestoエンジンに接続するために、Presto JDBCドライバーをダウンロードし、Cognosに組み込み、watsonx.dataとの接続設定を行いました
  • Cognosで分析するテーブルに関する構成を行いました
  • Cognosにダッシュボードを作成し、データ分析するための構成を行い、データを分析してみました
    • Cognosにはさまざまなレイアウトのテンプレート、データをビジュアライゼーションするためのさまざまなパーツが用意されており、その一部を使ってデータを分析してみました

なお、Cognos側の設定は、他のJDBCドライバーでも共通の設定なので、そのような場合でも参考にしていただけると幸いです。

手順の流れ

この手順は以下のようになる。

  1. Presto JDBCドライバー・ファイルをダウンロードして動作確認してみる
  2. CognosにJDBCドライバー・ファイルをインポートする
  3. Cognosにwatsonx.dataとの接続設定を行う
  4. Cognosに分析するテーブルが含まれるスキーマをインポートする
  5. Cognosにコンテンツを追加する
  6. Cognosのダッシュボードを作成してデータを分析する

1. Presto JDBCドライバー・ファイルをダウンロードして動作確認してみる

以下のサイトからPresto JDBCドライバーをダウンロードする。(ダウンロードしたファイル名: presto-jdbc-0.284.jar)

上記のほかに、watsonx.dataのPrestoエンジンに接続するためには以下の情報が必要となる。

  • ホスト名
  • ポート番号
  • ユーザーID
  • パスワード

(*) 以下はwatsonx.dataのSW版の情報取得方法についてです。SaaS版については以下を参照してください。
watsonx.data SaaS版のPrestoエンジンにアクセスするための事前情報取得

ホスト名とポート番号を得るために、watsonx.dataのインフラストラクチャー・マネージャーに表示されているPrestoエンジンをクリックする。

watsonx_data_infra.png

以下のようにPrestoエンジンの情報が表示される。External hostがホスト名で、watsonx.dataのSW版のExternal hostのポート番号は443である。

watsonx_data_presto_info.png

ユーザーIDとパスワードはCP4Dにログインしたものが使用可能で、今回はデフォルトで使用可能となっているadminを使用する。

項目 値
ホスト名 ibm-lh-lakehouse-presto-01-presto-svc-cpd-instance.mycluster-jp-tok-1-bx2-16-309ee4c04392c50ffd16fd0a4c2688e1-0000.jp-tok.containers.appdomain.cloud
ポート番号 443
ユーザーID admin
パスワード xxx
JDBCドライバー・ファイル名 presto-jdbc-0.284.jar
JDBCドライバー・クラス名 com.facebook.presto.jdbc.PrestoDriver

また、PrestoエンジンにTLS/SSLで接続する場合、プロパティとしてSSL=trueをセットする。

プロパティ 値
SSL true

JavaのプログラムでJDBC接続を確認してみる。

PrestoAccessTest.java
import java.sql.*;
import java.util.Properties;

public class PrestoAccessTest {
    public static void main (String[] args)
    {
        String url = "jdbc:presto://ibm-lh-lakehouse-presto-01-presto-svc-cpd-instance.mycluster-jp-tok-1-bx2-16-309ee4c04392c50ffd16fd0a4c2688e1-0000.jp-tok.containers.appdomain.cloud:443";
        Properties properties = new Properties();
        properties.put("user", "admin");
        properties.put("password", "xxx");
        properties.put("SSL", "true");
        Connection con = null;
        try {
            Class.forName("com.facebook.presto.jdbc.PrestoDriver").getDeclaredConstructor().newInstance();
        } catch (Exception e) {
            System.out.println("Error: failed to load PrestoDriver.");
            System.exit(1);
        }
        try {
            con = DriverManager.getConnection(url, properties);
            String sql = "SELECT distinct year, organization, department FROM icos1.hr.hr_training limit 3";
            System.out.println(sql);
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                int year = rs.getInt(1);
                String org = rs.getString(2);
                String dept = rs.getString(3);
                System.out.println(year + ", " + org + ", " + dept);
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(1);
        }
        System.exit(0);
    }
}

CLASSPATH環境変数をセットし、コンパイルしてから実行してみる。

$ export CLASSPATH=.:./presto-jdbc-0.284.jar:$CLASSPATH
$ javac PrestoAccessTest.java
$ java -cp $CLASSPATH PrestoAccessTest
SELECT distinct year, organization, department FROM icos1.hr.hr_training limit 3
2014, GO Americas corporate, Human Resources
2014, GO Americas corporate, Finance
2014, GO Americas corporate, Marketing

SELECT文の結果が返ってきており、問題なく動作している。

2. CognosにJDBCドライバー・ファイルをインポートする

以下のガイドを参照した。

実行するのに必要な変数を環境変数にセットする。
CPDURLはCognosが起動しているホスト名で、NAMESPACEはCP4Dを導入する際にセットしたPROJECT_CPD_INST_OPERANDS環境変数の値である。
USERIDとPASSWORDはCognosにアクセス可能なもので、今回はCP4D導入時にデフォルトでアクセス可能となっているadminユーザーを使用している。

環境変数 値
CPDURL https://cpd-cpd-instance.mycluster-jp-tok-1-bx2-16-309ee4c04392c50ffd16fd0a4c2688e1-0000.jp-tok.containers.appdomain.cloud
NAMESPACE cpd-instance
DRIVERSURL \${CPDURL}/cognosanalytics/\${NAMESPACE}/artifacts/v1/drivers
CPDAUTHURL \${CPDURL}/icp4d-api/v1/authorize
USERID admin
PASSWORD xxx

実行環境のシェルがbashでcurlコマンドとjqコマンドが導入されている場合、以下を実行するとBEARER環境変数にBearer Tokenがセットされる。

CPDURL=https://cpd-cpd-instance.mycluster-jp-tok-1-bx2-16-309ee4c04392c50ffd16fd0a4c2688e1-0000.jp-tok.containers.appdomain.cloud
NAMESPACE=cpd-instance
DRIVERSURL=${CPDURL}/cognosanalytics/${NAMESPACE}/artifacts/v1/drivers
CPDAUTHURL=${CPDURL}/icp4d-api/v1/authorize
USERID=admin
PASSWORD=xxx
BEARER=$(curl -s -k -X POST -H 'Content-Type: application/json'  -d "{ \"username\":\"${USERID}\", \"password\":\"${PASSWORD}\" }" "$CPDAUTHURL" | jq -r '. | .token')

jqが導入されていない場合、上記で述べた環境変数をセットしたあと、以下を実行する。

$ curl -s -k -X POST -H 'Content-Type: application/json' -d "{ \"username\":\"${USERID}\", \"password\":\"${PASSWORD}\" }" "$CPDAUTHURL"
{"_messageCode_":"200","message":"Success","token":"eyJhbGciOiJSUzI1..."}

上記、"token"キーの値がBearer Tokenなので、この値をBEARER環境変数にセットする。
BEARER環境変数の値をセットしたら、最初に、登録されているJDBCドライバーのリストを取得してみる。

$ curl -k -X GET "${DRIVERSURL}" -H "Content-Type:application/json" -H "Authorization: Bearer $BEARER"     
[]

最初は何も登録されてないので空の配列[]が返ってくる。
次に、JDBCドライバー・ファイルを登録してみる。presto-jdbc-0.284.jarファイルのあるディレクトリに移動し、以下のコマンドを実行する。

$ curl -k -X POST "${DRIVERSURL}" -H "Authorization: Bearer $BEARER" -F "files=@./presto-jdbc-0.284.jar"

結果として、登録に成功すると以下のようなJSONデータが返ってくる。
(見やすいようにjqコマンドで整形した)

{
  "id": "41bd7e7671aab30b22ecac5e3be6eeb3636ca2f3676d41c44d5c8e99b1d44873",
  "driverFileNames": [
    {
      "filename": "presto-jdbc-0.284.jar",
      "fileid": "469a9489ea7e8588d1e08d6a56aa2a0022c41a905adc34e48d928b8cf4f2afb9"
    }
  ],
  "driverVersion": "",
  "targetDatabase": "",
  "createdAt": 1699974214
}

再度、登録されているJDBCドライバーのリストを取得してみる。

$ curl -k -X GET "${DRIVERSURL}" -H "Content-Type:application/json" -H "Authorization: Bearer $BEARER"

結果として、以下のように配列の中に上記の登録情報がセットされて返ってくる。
(こちらも見やすいようにjqコマンドを使って整形した)

[
  {
    "id": "41bd7e7671aab30b22ecac5e3be6eeb3636ca2f3676d41c44d5c8e99b1d44873",
    "driverFileNames": [
      {
        "filename": "presto-jdbc-0.284.jar",
        "fileid": "469a9489ea7e8588d1e08d6a56aa2a0022c41a905adc34e48d928b8cf4f2afb9"
      }
    ],
    "driverVersion": "",
    "targetDatabase": "",
    "createdAt": 1699974214
  }
]

最後に、JDBCドライバーの登録を反映させるためにCognosを再起動する。
以下を実行する。

oc project ${NAMESPACE}
ARTIFACTS_POD=$(oc get pods | grep artifacts | awk '{print $1}')
CA_INSTANCE=$(echo $ARTIFACTS_POD | awk -F '-' '{print $1}')
oc delete pod $(oc get pod | grep $CA_INSTANCE | grep ca-cpd-rest | awk '{print $1}')
oc delete pod $(oc get pod | grep $CA_INSTANCE | grep ca-cpd-reporting | awk '{print $1}')
oc delete pod $(oc get pod | grep $CA_INSTANCE | grep ca-cpd-smarts | awk '{print $1}')

上記のコマンドを実行したら、CP4Dのインスタンス画面で、Cognosが起動するのを待つ。
(Cognosの状況が他のものと同じアイコンになったらOK)

cp4d_instance_list.png

3. Cognosにwatsonx.dataとの接続設定を行う

Cognos Analytics with Watsonのホーム画面の左上メニューから、管理 > データ・サーバー接続 - 接続の作成と管理を選択する。

cognos_home.png

右側にあるデータ・サーバーの作成ボタンをクリックする。

cognos_ds_conn_list.png

以下の値を入力して、次へをクリックする。

項目 値
名前 watsonx_data (任意の名前)
接続タイプ Presto

cognos_ds_creation1.png

次に、以下の値を入力する。(JDBC URLのホスト名は実際の値に合わせる)

項目 値
JDBC URL jdbc:presto://ibm-lh-lakehouse-presto-01-presto-svc-cpd-instance.mycluster-jp-tok-1-bx2-16-309ee4c04392c50ffd16fd0a4c2688e1-0000.jp-tok.containers.appdomain.cloud:443
ドライバー・クラス名 com.facebook.presto.jdbc.PrestoDriver (io.prestosql.jdbc.PrestoDriverにセットされているので変更する)
接続プロパティ SSL=true
分離レベル デフォルトのままで良い(変えても良い)
認証 サインオンを使用する

cognos_ds_creation2.png

今回、認証は毎回入力するのが面倒なのでサインオンを使用するを選択した。その場合、その下にあるサインオンの追加をクリックして、ユーザーIDとパスワードをセットし、最後に完了をクリックする。

cognos_ds_creation3.png

画面下部にある接続のテストボタンをクリックして、テスト接続してみる。
接続が成功すると、以下のようにテスト接続は成功しましたが表示される。
接続が成功したら、次へをクリックする。

cognos_ds_creation4_test.png

以下の設定はデフォルトのままにして、作成するをクリックする。

cognos_ds_creation5.png

結果として、データ・サーバー設定が登録され、以下のようにリストされる。

cognos_ds_conn_list2.png

4. Cognosに分析するテーブルが含まれるスキーマをインポートする

データ・サーバー接続画面にリストされたwatsonx_dataをクリックする。
以下の画面に遷移するので、次に、右側のメニューを表示させアセットをクリックする。

cognos_watsonx_data_asset.png

検索フィールドにicosと入力すると以下の2つの資産がリストされる。
今回、icos1/hrスキーマにあるhr_trainingテーブルのデータを分析するので、icos1/hrの右側にあるロードをクリックする。

cognos_watsonx_data_asset2.png

ステータスがロード終了になったら完了をクリックする。

cognos_watsonx_data_loaded.png

5. Cognosにコンテンツを追加する

左上メニューからコンテンツを選択する。
次に、個人用コンテンツタブを選択し、右上の新規メニューからデータ・モジュールをクリックする。

cognos_contents_list.png

ソースの選択の左メニューから cognos_ds_icon.png をクリックすると、watsonx_dataがリストされるので、それをクリックする。

cognos_contents_source.png

リストされているicos1/hrを選択し、OKボタンをクリックする。

cognos_ds_source_selection.png

左のリストからhr_trainingを選択し、画面上部左の cognos_disk.png (保存)をクリックする。

cognos_ds_source_selection2.png

名前にhr_trainingを入力し、個人用コンテンツタブを選択し、保存をクリックする。

cognos_ds_source_save.png

6. Cognosのダッシュボードを作成してデータを分析する

以下のURLを参照した。

左上のメニューから+ 新規 > ダッシュボードをクリックする。

cognos_dashboard_menu.png

4等分されたペインを選択し作成をクリックする。

cognos_dashboard_4pane.png

ソースの選択をクリックする。

cognos_dashboard.png

個人用コンテンツタブでhr_trainingを選択し、追加ボタンをクリックする。

cognos_dashboard_src_selection.png

結果として、以下のような画面になる。

cognos_dashboard2.png

departmentを左上のペインにドラッグし、

cognos_dashboard_dept_drag.png

ペインに表示される正方形の上にドロップする。

cognos_dashboard_dept_drop.png

結果として以下のようになる。

cognos_dashboard3.png

externalhiresをドラッグして、departmentのリストのヘッダーあたりにドロップする。そうすると以下のようなグラフ表示に変わる。

cognos_dashboard4.png

上記のグラフ領域の枠あたりのスペースをクリックすると、上部に以下のようなツールバーが表示される。

cognos_dashboard_toolbar.png

cognos_change_visualization.png (視覚化の変更) アイコンをクリックして、表示形式を変えてみる。

cognos_packed_bubble.png (パック・バブル) アイコンを選択してみる。
そうすると、グラフが以下のような画面に変わる。

cognos_dashboard5.png

左側のメニューで、cognos_change_visualization.png (視覚化) をクリックし、リストからツリー・マップをクリックする。

cognos_tree_map.png

結果として、空のツリー・マップが空のデータ・フィールドに作成される。

cognos_dashboard6.png

選択されたソースからorganizationを右側の上部にある領域の階層フィールドにドラッグ&ドロップする。
coursecostをその下にあるサイズフィールドにドラッグ&ドロップする。

cognos_dashboard7.png

凡例を非表示にして、ツリー・マップのスペースを広げてみる。
右上のプロパティーをクリックして表示されたプロパティー画面で、凡例を展開して、凡例の表示トグルを選択解除する。

cognos_dashboard_property.png

coursedaysをフィルターに追加してみる。
選択されたソースからcoursedaysをこのタブフィルター領域にドラッグ&ドロップする。
値の範囲を1から15として完了ボタンをクリックする。

cognos_dashboard_filter.png

以下のような画面となる。

cognos_dashboard8.png

左ペインのdepartmentのパック・バブルで、Financeをクリックしてみる。
そうすると、右のペインのorganizationも、それぞれFinanceの情報のみになる。

cognos_dashboard9.png

organizationとdepartmentの関係を見るために、watsonx.dataの照会ワークスペース(SQL)、または、Presto CLIで以下のSQL文を発行してみる。(以下は、Presto CLIを使用してやってみた結果を、一部省略して記載している)
以下のように、複数のorganizationがあり、それぞれのorganizationが複数のdepartmentで構成されていることがわかる。

$ ./presto --server https://ibm-lh-lakehouse-presto-01-presto-svc-cpd-instance.mycluster-jp-tok-1-bx2-16-309ee4c04392c50ffd16fd0a4c2688e1-0000.jp-tok.containers.appdomain.cloud --user admin --password
Password: 
presto> select organization, count(*) from icos1.hr.hr_training group by organization order by organization;
         organization          | _col1 
-------------------------------+-------
 GO Accessories corporate      |   116 
 GO Accessories operations     |    52 
 GO Americas corporate         |   124 
 GO Americas operations        |    60 
 GO Asia Pacific corporate     |   116 
 GO Asia Pacific operations    |    60 
 GO Central Europe corporate   |   116 
 GO Central Europe operations  |    64 
 GO Northern Europe corporate  |   116 
 GO Northern Europe operations |    60 
 GO Southern Europe corporate  |   116 
 GO Southern Europe operations |    56 
(12 rows)

presto> select organization, department, count(*) from icos1.hr.hr_training group by organization,department order by organization,department;
         organization          |             department              | _col2 
-------------------------------+-------------------------------------+-------
 GO Accessories corporate      | Executive Offices                   |     4 
 GO Accessories corporate      | Finance                             |    36 
 GO Accessories corporate      | Human Resources                     |    16 
 ...
 GO Americas corporate         | Executive Offices                   |    12 
 GO Americas corporate         | Finance                             |    36 
 GO Americas corporate         | Human Resources                     |    16
 ...

Cognosで、画面上部左の cognos_disk.png (保存) をクリックしてダッシュボードを保存する。
適当な名前を付けて個人用コンテンツタブを選択し、保存をクリックする。

cognos_dashboard_save.png

ホーム画面に戻って、左上メニューからコンテンツを選択する。
さらに、最初のダッシュボード(ダッシュボードを保存した名前)を選択し、詳細表示から開くを選択する。

cognos_contents2.png

そうすると、先ほど作成したダッシュボードが開き、データを分析することができる。

cognos_dashboard10.png

4
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?