SPSS ModelerでClickHouseに接続する(Windows)

SPSS Modeler 18.4からClickHouseがサポートされました。
接続テストをしてみました。

* テスト環境
    * Modeler 18.4
    * Windows 11
    * ClickHouse 22.3.7.28
    * ClickHouse ODBC Driver Release 1.1.10.20210822

* 修正履歴
    * 制約についての記述を追加
 

*サンプルストリーム

https://github.com/hkwd/spsssample/raw/master/220630ClickHouse/220629Clicktest.str

# 1. ODBCのセットアップ
以下の手順でODBCをセットアップします。SDAPにはODBCドライバーがないのでNativeのODBCドライバーを使います。
    
ClickHouseにWindowsのODBCで接続する - Qiita 

https://qiita.com/kawada2017/items/70f61ddf96eb59895829

# 2. Modelerからの接続


データベースのエクスポートノードかインポートノードで＜新規データベース接続の追加...＞を選びます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/7ba653d6-991f-80ba-182e-94f77f23fc79.png)

1で登録したデータソースを選び、ユーザーとパスワードを入力して「接続」ボタンをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/2b306139-9668-3f9c-13d5-9e64453aac5e.png)

接続ができましたら「OK」で閉じます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/2d87d9e1-916c-6b0c-68d2-6cc4e0dbe9f1.png)

# 3. データの書きだし

SQLプッシュバックを確認するためにストリームのプロパティの「オプション」タブの「ログとステータス」でSQLメッセージをロギングするチェックを付けます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/733f2a2f-aad2-ebd2-be27-2473da16e081.png)


データベースのエクスポートノードで「テーブル名」を指定して「実行」をクリックします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/d9d0881b-8c46-9065-de55-ba582b241ef1.png)

実行後メッセージを確認するとCREATE TABLEしてINSERTしていることがわかります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/8a063a87-0fcf-7074-4620-f1baef22cbac.png)

```sql
CREATE TABLE `COND3N_J1000` 
( `機械ID` UInt32,`経過時間` UInt32,
`温度` UInt32,`電力` UInt32,`メッセージ` String ) 
ENGINE = MergeTree() ORDER BY tuple()
```

# 4. データの読みだし

データベースのインポートノードで「データソース名」と「テーブル名」を指定して、テーブルノードを「実行」します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/ab10171a-288b-0c37-3d37-41edc84d6c8b.png)

正しく読むことができました。日本語の列名やデータも読めています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/93862967-2d97-e7eb-c154-718bbdadc856.png)

実行後メッセージを確認するとGROUP BYがプッシュバックしていることがわかります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/675e0964-765d-cdc1-6cf4-65661f7bdc5f.png)

# 5.制約について

３つの制約がマニュアルに記載されています。

https://www.ibm.com/docs/ja/spss-modeler/18.4.0?topic=guide-whats-new-in-version-1840

## 5.1 SQLプッシュバックは時間タイプではサポートされていません（ClickHouseに時間タイプがないため）

一通りのデータ型をテストしてみます。時間型も入っています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/633b0a10-dfe9-8dfb-ff5f-56f90995dc6d.png)

時間型の列はVARCHAR(32)で定義されました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/b6a012bb-f850-d4cf-2c29-5f8851c8adf4.png)


```sql
CREATE TABLE `データ型テスト` ( 
`INT` UInt32,
`FLOAT` Float64,
STR1 String,
STR2 String,
`DAY` Date,
TS DateTime,
`TIME` VARCHAR(32) ) 
ENGINE = MergeTree() ORDER BY tuple()
```

プレビュー結果は以下のようになりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/7c6c2fda-ebb2-ca64-025b-c3ef70bc77cd.png)


## 5.2 SQLプッシュバックは、データベースエクスポートノードのマージ機能(UPSERT)ではサポートされていません
DBエクスポートノードには、「テーブルに結合」というUPSERTの機能があります（たいていは洗い替えしてしまうので、あまり使っている話は聞いたことがないのですが）。この機能がサポートされていません。「HY010 [Microsoft][ODBC Driver Manager] 関数シーケンス エラーです。」のエラーになりました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/a56c32cb-ccde-dc6e-c584-eb0112d12399.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/9d468d11-c59c-995f-7b15-c756278f37e8.png)

## 5.3 日付のベースラインは1970-01-01です
日付型の起点がModelerは1900－01－01ですが、ClickHouseは1970-01-01です。そのため1970より古いデータが入りません。

1900-01-01のデータを入れてみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/f70e8681-6067-4899-c38c-1f4856ed7d22.png)

自動的に1970-01-01のデータに変換されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/223763/74d397a3-53c4-7587-0cf8-e3fca5a812fa.png)