TERADATA: 大きいテーブル同士を結合する時に結合条件のカラムがそれぞれPrimary Indexだと結合が最速になります

TERADATAは、データを分散している

TERADATAは、データを分散させて管理していて、Primary Indexに指定したカラムを元に分散させている。分散された１つはAMPと呼ばれ、１つのDBに複数AMPがある状態となります。（詳細は企業のサイトにあると思います）

データは分散されているため、あるテーブルとあるテーブルを結合しようとするとAMP間でデータの移動させて結合を行っています。

例えば、1000件の商品マスタと、10億件の売上データの結合では、1000件の商品マスタを全AMPにコピーして、そのAMP内で結合するようにプランを立てています。

マスタという名の巨大なテーブルがあった場合、そのマスタに関連するトランザクションと結合する際にデータの移動でとても時間がかかる場合があります。

例えば、5千万件の顧客マスタがあって、その顧客の取引履歴データが10億件ある場合で、顧客コードで顧客マスタと取引履歴データを結合する必要があったとします。

顧客マスタはUPIで顧客コードが指定されていて、取引履歴データはPIで取引番号が指定されていた場合、TERADATAは結合できるようにAMP間でデータの再配置を実施してから結合する。仮に全AMPに顧客マスタを配置したとするとこれは時間がかかります（※１）

Primary  Indexが同じだと再配置無しで結合できる

顧客マスタはUPIで顧客コードが指定されていて、取引履歴データはPIで顧客コードが指定されていた場合、結合したいデータは最初から同じAMPに配置された状態です。再配置無しで結合できるので、早くなります。（merge join　というプランのようです）

もちろん、取引履歴データのPIを顧客コードにしても、大きく偏らないことが前提とはなります。（ちょっとぐらい偏るのは気にしなくて大丈夫です。）

補足ですが、結合を早くしたくてPrimary Indexを揃える場合は、等号で結合するカラムをPI（もしくはUPI）に指定します。

マスタで有効開始と有効終了の日付があって、次のような結合になる場合でも、PIにするのは顧客コードです（等号で結合する項目なので）

select  ・・・ from  取引履歴データ inner join 顧客マスタ

on    取引履歴データ.顧客コード = 顧客マスタ.顧客コード

and  取引履歴データ.取引日付 between 顧客マスタ.有効開始日 and 有効終了日

※１　経験はないのですが、TERADATAのノード数が多ければ遅くならないかもしれません。10ノードのシステムとかさわってみたいものです。

TERADATA: TERADATAの拡張SQL構文のQUALIFY句は、他のDBでも使えるといいなと思う

TERADATAは分析関数の値でさらにフィルタできる

TERADATAのSQLには、独自拡張されたQUALIFY句があります。

QUALIFY句を使うと、分析関数の値でさらにフィルタをかけることができます。

例えば、金額を大きい順に並べて100番までの商品を取得したい場合は次のようなSQLになります。

select
   productcode
 , ammount
 , rank() over(order by ammount desc)
from sample
qualify rank() over(order by ammount desc) <= 100

こんな感じで使えます。他のDBだと、一度分析関数の副問合せの結果にさらに条件を入れたりすることになりますが、上記のようにとても綺麗なSQLで書くことができます。

上のSQLはあまり例が良くなくて、単純にammountをソートして、top 100 や limit 100を指定すれば同じことになります。（Oracleだと、前は副問合せ後rownumで条件をつけるやり方でしたが、今も同じですかね。。）

もう少し複雑にして、商品カテゴリ別に、金額を大きい順に並べて100番までの商品を取得したい場合にすると次のようになります。

select
   productcategory
  ,productcode
 , ammount
 , rank() over(partition by productcategory order by ammount desc)
from sample
qualify rank() over(partition by productcategory  order by ammount desc) <= 100

こうなると他のDBでは、分析関数の結果を副問合せにして後、フィルタをつけることになりますが、TERADATAでは副問合せの必要がなくなります。

qualify句があるため、TERADATAでは条件の指定は次の３通りとなります。

• where : データ抽出時にフィルタする。
• having :データ集計後にフィルタする。
• qualify :データの結果に対して、分析関数の値でフィルタする。

検索に特化しているだけあって隙が無いと思います。

最後に

分析関数自体が、あまり親しみ易くないと感じています。（最初は、どういう結果が返ってくるのかさっぱりわかりませんでした）

• sum関数のover句内でorder by した際には、 rows between unbounded preceding and current row がデフォルトで指定される　などの動き
• 各DBで実装されている関数が違う
• 通常のSQLを習得してても分析関数を使われると調べるのに時間がかかる

などとっつきづらい点があります。

前Oracleの業務システムで分析関数使ったら、そもそもこんな書き方すんなから始まり、order by がコストが高いのでやめろと色々言われたことがありました。。

でも、使えるととても便利です。

分析関数(Window関数)を理解できた時の思い出を次に書いてみました。

crmprogrammer38.hatenablog.com

crmprogrammer38.hatenablog.com

TERADATA: SQLで改行やタブをコードで指定する

文字コードで改行やタブを指定したい

Oracleのchr関数のようなものを探していましたが、関数はなく次のように書けることがわかりました。

--改行コード
select 'AAA' || '0A'XC || 'BBB'

--Tab
select 'AAA' || '09'XC || 'BBB'

独特だなーと思いました。

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