TERADATA: JDBCのFASTLOADモードを使おう

TERADATAのJDBCドライバにはFASTLOADモードが用意されています。

TERADATA12頃から、JDBCでFASTLOADモードが追加されました。 

crmprogrammer38.hatenablog.com

前回TERADATAにJDBCを使ってデータをいれるときは、addBatchを使ってまとめて登録した方が早いということを書きましたが、FASTLOADモードはさらに高速でデータをロードできます。

FASTLOADモードのポイントとしては、

・JDBCのURLパラメータにTYPE=FASTLOADを指定する

・通常のFASTLOADと同様にエラーテーブル２つが作成される

・通常のFASTLOADと同様に空テーブルのみロード可能

・JDBCのsetAutoCommitはfalseにする。

・commitするのは最後の1回、途中でのcommitはしない

・使える文字コードは限定されている（V13では、KANJISJIS_0SとUTF-8がサポートされているので特に問題はないと思います）

サンプルとしては次の通りです。 

    //接続先を用意
    String url = "jdbc:teradata://sampleip/CHARSET=UTF8,DATABASE=sampledb,TMODE=ANSI,TYPE=FASTLOAD";
    Driver driver = (Driver)Class.forName("com.ncr.teradata.TeraDriver").newInstance();
	
    Properties loginInfo = new Properties();
    loginInfo.setProperty("user", "sampleusername");
    loginInfo.setProperty("password", "samplepassword");
	
    connection = driver.connect(url,loginInfo);
    connection.setAutoCommit(false);
    
    PreparedStatement insertStatement = 
      connection.prepareStatement("insert into sampletable (col1,col2,col3) values (?,?,?)");
    
    for(int i=0; i< 10000; i++){
        insertStatement.setString("col1" + i);
        insertStatement.setString("col2" + i);
        insertStatement.setString("col3" + i);
        
        insertStatement.addBatch();
    }
    
    insertStatement.executeBatch();
    connectionInsert.commit();
    
    

TERADATA: 1件ずつinsertは性能が著しく落ちるよ

TERADATAは、大量データを処理するようになっている

TERADATAへのデータロードは、ファイルからであれば、FASTLOADやMULTILOADなどを使います。

ETLツールで他のデータベースからデータを取得して、TERADATAに入れる処理を作成することも多いと思います。

ETLツールで、「ODBC接続」、「JDBC接続」を使って処理を作成する場合、1件ずつ処理すると、極端に遅くなるので注意が必要です。

コミットのタイミングを10,000件ずつにしていたとしても、コミットするまでのinsertが1件ずつでは遅くなってしまうので、insert自体も10,000件まとめて実施することが必要となります。

ETLツールで一度に複数件をまとめてをinsertとかupdateできる設定があれば、まとめてinsertやupdateを実行するようにETLを作る必要があります。例えば、ループの指定ができる場合、1回のループで扱うデータ量を10,000件にするなどの制御になります。

ETLツール以外にも、自分でデータロード処理のプログラムを書く場合も1件ずつinsertを発行しないようにします。

具体的にはjavaで開発する場合、PreparedStatement.executeBatchを使用することで大分早くなります。TERADATAは、addBatchでつめこめるだけつめこむと性能があがります。もちろんjavaのヒープサイズは多めにとっておかないと、メモリーエラーの可能性があるので、５万件とか10万件などが目安になると思います。

TERADATA: WindowsOSで、JDBC接続では、hostsファイルに複数Nodeを定義する

TERADTAのJDBCで複数ノードを指定する

ODBC接続では、ODBCの設定で複数ノードを指定しますが、JDBC接続ではhostsファイルに指定します。

[hostsファイルへ追記] 　ノードのIPアドレス ホスト名 JDBCの接続名+cop+連番

10.50.50.101 tera1 teracop1
10.50.50.102 tera2 teracop2
10.50.50.103 tera3 teracop3
10.50.50.104 tera4 teracop4
・・・・

※IPアドレスはサンプルです。

[jdbcのURL]　接続先のホスト名にはcop+連番を外した接続名で指定します。

jdbc:teradata://tera/CHARSET=KANJISJIS_0S,DATABASE=Sample,TMODE=ANSI

TERADATA: 日付の変換や文字のトリムメモ

たくさん書かないといけなくてよく忘れるのでメモ。

TERADATAの日付の変換

日付のフォーマット(dateをYYYY/MM/DD形式の文字列へ)

select cast( cast( current_date as format 'YYYY/MM/DD') as CHAR(10) ) ;

日付のパース(YYYY/MM/DD形式の文字列からdate)

select cast( '2010/11/21' as date format'YYYY/MM/DD');

TERADATAの文字のトリム

[右側のtrim]

select trim( trailing ' ' from 'space-> ' );

[左側のtrim]

select trim( leading ' ' from ' <-space' );

[両方のtrim]

select trim( both ' ' from ' <-space-> ' );

半角スペースだけの文字列にたいして、半角スペースのトリムを行うと戻るのは空文字な点に注意(nullではない)です。

もちろんnullに対して半角スペースのトリムを行えばnullが戻ります。

TERADATA: 定義情報(テーブル一覧、カラム一覧、インデックス一覧など)の取得

よく使う定義の参照などコマンド 

テーブル一覧の表示	help user [ユーザ名];
カラム一覧の表示	help table [テーブル名];
テーブルのインデックス一覧の表示	help index [テーブル名];
テーブルの統計情報一覧の表示	help stat [テーブル名];
テーブル定義の表示	show table [テーブル名];
ビュー定義の表示	show view [ビュー名];
Join Index定義の表示	show join index [Join Index名];
テーブルのバックアップ (テーブルデータと統計情報含めてバックアップ)	create table [バックアップ先テーブル名] as [バックアップ元テーブル名] with data and stat;

テーブルのバックアップは、データが1億件ぐらいあってもあっという間に終わるので快適です。

create tableや、create viewのsqlファイルは成果物として管理してあるのが理想です。create tableは、エクセルのテーブル定義書からマクロで出力するように工夫しているプロジェクトも多いと思いますが、viewや、join indexは、最終的にデータベースにあるものが正になっていることも多いと思います。統計情報に至っては、数々のチューニングを経て今の形になっていてどこにも管理されていないなんてこともあると思います。

その際には上記のコマンドで今の定義情報が取得できます。

油断しているとテーブル定義書も正じゃなかったりする時がありますが、そういう場合はきちんと時間を使ってテーブル定義書だけでもメンテナンスをすることをお勧めします。

統計情報は、次のデータディクショナリをselectすることで一覧での確認も可能です。

dbc.ColumnStats
dbc.IndexStats
dbc.MultiColumnStats

TERADATA: Primary Partition Index と Dynamic Partition Elimination実行プラン

結合時に、結合先に合致するPartitionだけ検索する

crmprogrammer38.hatenablog.com

• 上記で書いたようなパーティションを指定したテーブルと他のマスタテーブルを内部結合する
• 結合条件がパーティションを指定した項目である
• 結合するテーブルの関係は１：Nの関係が物理的に保証されている
• マスタテーブルに検索条件を指定する
• 統計情報が正しく取れている

以上を満たすと（あくまでバージョン13のTERADATAで開発した経験値です。）dynamic partition eliminationという実行プランが選択されます。
結合するテーブルの関係は１：Nの関係が物理的に保証されている　というのは、結合するマスタテーブル側の結合する項目にユニークの指定があることを意味しています。ユニークの指定は、unique primary index もしくは、 unique secandary indexで定義することになります。

このプランが選択されると、テーブルデータの該当するパーティションのみ読み込まれるため、フルスキャンさせたくない場合とても有効です。

パーティションは1から65536までで区切るため、データのカーディナリティが65536以内のマスタとの結合項目が候補となります。
そうすると、1年365日分のレコードとなるカレンダーマスタとの結合項目が相性がいいことがわかります。1年分のデータを検索すると、カレンダーマスタでは365件しかヒットしないので、結合するトランザクションテーブルでは、365個のパーティションのみ検索するようになります。
もちろん、トランザクションテーブルに1年分のデータしか格納されていなければ365個/365個の検索となり意味はありませんが、10年、20年というデータを保持していれば効果があります。また、1月分、1日分というデータの取得範囲を限定すればするほど素早い検索が可能となります。

(DWHで良くある結合の図)

カレンダーマスタ１に対して、売上ファクトはN

売上ファクトの年月日にパーティションが指定されている。(FKと記載はしていますが、特に外部キーの指定は必要ありません)

2010年度の条件を指定した場合、売上ファクトは、2010/4/1～2011/3/31までのパーティションのみをスキャンする。

最後に

DWHでは、時系列にデータが蓄積されていきますが、期間を指定してデータを見ることが多いと思います。その際に指定する日付にパーティションが指定されていると検索結果が素早く帰ってきて分析する人のメリットも大きくなります。
TERADATAでは、secanday index や hash index の効果があまり高くないためパーティションの指定をどこにするかが大きなポイントだと思います。

TERADATA: Primary Partition Indexでパーティション設定

Primary Partition Index(PPI)で検索をさらに早く

TERADATAは、分散したデータに、さらにパーティション設定ができます。

パーティション設定した項目に検索条件を指定すると、フルスキャンではなく該当するパーティションのみスキャンすることができて、性能があがります。

例えば次のテーブルで考えます。
Datestring項目には、YYYYMMDD形式で値がセットされる仕様とした場合に、Datestring項目へ検索条件を指定すると該当のパーティションだけスキャンされます。

CREATE SET TABLE Sample1
(
     Datestring     CHAR(8)     CHARACTER SET LATIN CASESPECIFIC
    ,ProductCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,StoreCode      CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,Amount         DECIMAL(6,0) COMPRESS (0)
)
UNIQUE PRIMARY INDEX
(
     Datestring
    ,ProductCode
    ,StoreCode
)
PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1
;

※上記は8文字の数字のためINTEGERにcastしているが、INTEGERでCASTできない場合（数値に変換した場合にINTEGERの最大値、約20億を超えてしまう場合）はテーブルにデータを登録する際にエラーとなるため注意。

補足としては、Unique Primary Index（以下UPI）を指定した場合は、UPIで指定した項目以外の項目はパーティションを指定することができません。

テーブルのユニーク項目以外の項目でパーティション指定をする場合は、UPIではなく、Primary Indexでテーブル定義をします。（ただし、ユニークであることを保証したい場合は、unique secondary indexをユニーク項目に指定しておくことをお勧めします）

CREATE SET TABLE Sample2
(
     UniqueNumber   DECIMAL(8)
    ,Datestring     CHAR(8)     CHARACTER SET LATIN CASESPECIFIC
    ,ProductCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,StoreCode      CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,Amount         DECIMAL(6,0) COMPRESS (0)
)
PRIMARY INDEX
     UniqueNumber
 PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1 ; 

パーティションの指定の仕方は、上記のように65535の余り＋1で指定する場合と、HASHBUCKET(HASHROW(カラム名１,カラム名2）で指定する場合があります。個人的にですが、HASHBUCKET(HASHROW(カラム名１,カラム名2）でテーブル定義をしたことはまだありません。