TERADATA: テーブルの結合や、文字の連結処理では文字コードが揃ってないと遅くなる

文字同士で処理する際に、文字コードが揃っていることを確認しよう

以前、TERADATAではカラム単位に文字コードが指定できるということを書きました。

crmprogrammer38.hatenablog.com

カラムをLATINで定義できれば、UNICODEの半分のサイズとなるので、

・データのサイズも小さくできる

・そのカラムで結合する際に、結合のスピードも向上する

などいいことが多いのですが、注意点もあります。

結合する項目は、文字コードを揃えよう

テーブル１とテーブル２を結合する際に、テーブル１の結合するカラムをLATIN、テーブル２の結合する項目をUNICODEで定義しないようにしよう。

LATINのカラムとUNICODEのカラムを結合すると、LATIN側のカラムに対してUNICODE変換する処理が走るので注意。

create tableが肝なので、DBAの腕の見せ所かもしれないですね。

文字列連結する際は文字コードを揃えよう

カラム１とカラム２を連結する際に、カラム１はLATIN、カラム２はUNICODEなどにならないようにしよう。

select col1 || col2 from sample 

これもLATIN側のカラムに対してUNICODE変換する処理が走る。

このようなSQLでは、col1とcol2の文字コードは同じになることをチェックしよう。

コード(LATIN)と名称（UNICODE）を連結するなどはよくやるので注意。UNICODEと文字列連結するのが見えてるなら、あえてカラムをLATINではなく、UNICODEで定義するのも手です。

固定値と結合する際も注意

select col1 || ':' || col2 from sample 

このようなSQLでcol1とcol2がLATINで「:」も半角だとしてもcol1とcol2にUNICODEの変換が走ります。（V12のバージョンで経験しました）

select col1 || TRANSLATE(':' USING UNICODE_TO_LATIN) || col2 from sample

と固定値をLATINにしておくことで解消します。

数十億件のテーブルなどを扱う際には文字コードに気をつける必要があります。

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 の効果があまり高くないためパーティションの指定をどこにするかが大きなポイントだと思います。