プログラマ38の日記

主にプログラムメモです。

TERADATA: TERADATAにデータを入れるツールを作りました(ACCESSのデータをTERADATAにロードできます)

開発中に手持ちのExcelデータをTERADATAに入れたいことは多々あります。

ちょっとした件数(10万件程度)を入れるのに便利なツールの紹介です。

 

通常TERADATAに入れるやり方は、

1.CSVファイルにして、FASTLOADや、MULTILOADのスクリプトを書いてデータを投入する。

2.ACCESSに、TERADATAのODBCリンクテーブルを作成し、Excelからデータを貼る。

3.作成したいデータのinsert文を作成し、TERADATAへSQLを実行する。

 

上記のようなものだと思いますが、私は以下のような感想を持っています。

 

1の方法は、FASTLOADとMULTILOADのスクリプトの作成に手間がかかるのと、ExcelデータをCSVにする作業がある。後、そもそもFASTLOADはサーバにしかインストールしてないなんてこともあります。

 

2の方法は、MS製品なのでExcelのデータはACCESSに貼り付けできますが、リンクテーブル経由でデータを投入するのにすごい時間がかかります。

 

3の方法ではinsert文の作成をしないといけなくて、日付型のフォーマットも意識しないといけなくなります。

 

2の方法が一番便利だけど、数万件登録しようとするといつ終わるかわからないといった状況は避けたい。

そんな時に使えるツールを作りました。自分がプログラマ3年目あたりで作成したものですが、きちんとテストしています。

 

 

    

ダウンロードはこちらから 50MB近くあります。

[ツール詳細]

Windows用です。

・zipを解凍してから利用します。

java+jdbcで動きます。javaは7、jdbcはTERADATA13用があらかじめ入っています。

・フォルダ内のdataload.batに設定ファイル(xml)をドラッグして実行します。

f:id:crmprogrammer38:20170221125450p:plain

<設定ファイルの書き方>

 設定ファイルに以下を定義します。

接続元の情報

接続元からデータを取得するためのSQL(select)

接続先の情報

接続先にデータをロードするためのSQL(insert)

※ただし、設定ファイルをxmlにしたので、SQL(select)など条件に ">" や "<" を利用する場合はxmlエスケープ が必要です。

サンプル

 <?xml version="1.0" encoding="Shift_JIS" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Sample設定ファイル</comment>

<!-- ソース情報 -->
<entry key="SELECT_SQL">
Select
textfield
,datefield
,timestampfield
,decimalfield
From
table1
</entry>
<entry key="SELECT_USR"></entry>
<entry key="SELECT_PWD"></entry>
<entry key="SELECT_URL">jdbc:odbc:Driver=Microsoft Access Driver (*.mdb);DBQ=D:\sampledb\access\sample.mdb</entry>
<entry key="SELECT_JDBCDRIVER">sun.jdbc.odbc.JdbcOdbcDriver</entry>
<entry key="SELECT_FETCHSIZE">1000</entry>

<!-- ターゲット情報 -->
<entry key="INSERT_SQL">
INSERT INTO table1 (
textfield
,datefield
,timestampfield
,decimalfield
) values (
?,?,?,?
)
</entry>
<entry key="INSERT_USR">SAMPLEUSR</entry>
<entry key="INSERT_PWD">SAMPLEPWD</entry>
<entry key="INSERT_URL">jdbc:teradata://samplehost/CHARSET=KANJISJIS_0S,DATABASE=Sample,TMODE=ANSI</entry>
<entry key="INSERT_JDBCDRIVER">com.teradata.jdbc.TeraDriver</entry>
<entry key="INSERT_COMMITSIZE">10000</entry>
</properties>

 ほぼ見たままの設定です。

もちろん、以前の記事で書いた通りinsertはexecuteBatchとしています。

 

crmprogrammer38.hatenablog.com

 

TERADATAお使いの方は、お試しください!

 

 

TERADATA: Aggregate Join Indexでさらに検索を早くする

Join Indexはクエリーリライト用にあらかじめ結合、集計しておく仕組み

TERADTAに、Join Indexという機能があります。

これはOracleのMaterialized Viewをクエリーリライトに特化したような機能で、あらかじめ結合や集計をした結果を保持しておく仕組みです。

もちろん、元のテーブルが更新されれば即座にJoin Indexも更新されます。(V4ぐらいでは、元のテーブルが更新されてもJoin Indexは更新されなかったような記憶がありますが、最近のバージョンでは問題なく更新されます)

 

BI/DWHでは、BIツールとTERADATAでシステムを構築した場合、なるべく加工(集計)をしないでデータを持っているほうが、利用者は使いやすいシステムとなります。

が、加工(集計)しないと性能に問題が生じる場合が多く、その時にJoin Indexを利用すると性能が改善できます。

 

例えば、次のようなデータモデルで、

f:id:crmprogrammer38:20170208123750p:plain

売上データは集計していなくて件数が2億件あるとした場合、顧客別の分析をしない次のような人には、売上データに顧客コードを除いて集計したJoin Indexが有効です。

・店舗別で過去数年に渡ってデータを見たい

・商品別で過去数年に渡ったデータを見たい

・店舗/商品別で当年のデータを見たい

 

Join Indexのサンプル
--1.売上テーブルの定義

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
    ,AccoundCode    CHAR(10)    CHARACTER SET LATIN CASESPECIFIC
    ,Amount         DECIMAL(6,0) COMPRESS (0)
)
PRIMARY INDEX
(
     Datestring
    ,ProductCode
    ,StoreCode
)
PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1
;

--2.顧客コードを除いたJoin Indexを指定する。

CREATE JOIN INDEX JX_Sample1
AS
SELECT
     Datestring
    ,ProductCode
    ,StoreCode
    ,SUM(Amount) AS Amount
FROM
    Sample1
GROUP BY
     Datestring
    ,ProductCode
    ,StoreCode
PRIMARY INDEX (
     Datestring
    ,ProductCode
    ,StoreCode
)
PARTITION BY
(CAST(Datestring AS INTEGER) MOD 65535) + 1
;

Join Indexが有効となるのは、そのJoin Indexを作成した場合、どのくらいデータサイズが小さくなるかがポイントとなります。

Join Indexを作成した結果、さほどデータサイズが小さくならないならそのJoin Indexはあまり効果がありません。

 

個人的な考え

Join Indexは"あらかじめ結合する"、"あらかじめ集計する"、"あらかじめ結合して集計する"を行うことができます。BIツールを使う場合、常にJoin Indexで指定した結合キーをつかって結合するとは限らないので、"あらかじめ集計する"のが一番使いやすいと思っています。

 

また、下記のように年月日が決まると確実に年月が定まるような項目はファクトに持たせておき、年月に対応するマスタを用意しておくと、年月別で集計できてJoin Indexのサイズをさらに小さくできます。

f:id:crmprogrammer38:20170210131922p:plain

 

Join Indexのサンプル
--1.売上テーブルの定義 CREATE SET TABLE Sample2 ( Datestring CHAR(8) CHARACTER SET LATIN CASESPECIFIC ,YearMonth CHAR(6) CHARACTER SET LATIN CASESPECIFIC ,ProductCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,StoreCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,AccoundCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,Amount DECIMAL(6,0) COMPRESS (0) ) PRIMARY INDEX ( Datestring ,ProductCode ,StoreCode ) PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1 ; --2.顧客コードを除き、さらに年月別別でJoin Indexを指定する。 CREATE JOIN INDEX JX_Sample2 AS SELECT YearMonth ,ProductCode ,StoreCode ,SUM(Amount) AS Amount FROM Sample2 GROUP BY YearMonth ,ProductCode ,StoreCode PRIMARY INDEX ( YearMonth ,ProductCode ,StoreCode ) PARTITION BY (CAST(YearMonth AS INTEGER) MOD 65535) + 1 ;

 テーブル定義では、年月日にパーティション指定されているけど、Join Indexでは、年月にパーティション指定ができるのでパーティションも使えます。

ただ、普通に集計軸の考えだと、年月日のカレンダの上位に年月のカレンダ(年月)をつなぐのが普通なので、年月のカレンダ(年月)を単独で結合するケースはあまりないと思います。

が、BIツールでショートカット結合が用意されていれば、Join Indexをうまく利用できると思います。(Business Objectsではショートカット結合があります)

 

Business Objectsユニバースでのショートカット結合

ショートカット結合で、カレンダ(年月)の年月と売上データの計上年月を結合する。

f:id:crmprogrammer38:20170210160826p:plain

ユニバースでショートカット結合を指定すると、例えば、カレンダ(年月)の年月項目と売上データの売上金額を選択すると、発行されるSQLはテーブルは、カレンダ(年月)と売上データとなる。

そして、結合条件は「カレンダ(年月).年月=売上データ.計上年月」となる。

そのため売上データに対して、計上年月で集計したJoin Indexを利用可能となる。

他のBIツールにもショートカット結合と同様の機能があるかはわかりませんが、Business Objectsなら使えるので、お試しください!

TERADATA: 他のテーブルの値で更新するUPDATE文

UPDATEの文法って、DB毎に癖があって覚えられないのでメモ。

 

UPDATE [更新するテーブル]
FROM [更新元テーブル]
SET [更新するカラム] = [更新元テーブル].[更新元カラム]
WHERE [更新するテーブル].[結合カラム] = [更新元テーブル].[結合カラム];

 

TERADATAでいうと、INSERTが圧倒的に早いのと、大量データのテーブルのPrimary IndexをUpdateしたりすると、とてつもなく時間がかかったりするので、Updateはあまり使う機会がない気がします。

 

でもdeleteもしくはdropしてから入れ直しだと、ワークテーブルの数が増えてしまうのがデメリットかもしれません。

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

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

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

 

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

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

 

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

 

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

 

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

 

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

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

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

 

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

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

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

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

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

 

※1 経験はないのですが、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では条件の指定は次の3通りとなります。

  • 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の半分のサイズとなるので、

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

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

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

 

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

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

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

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

 

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

カラム1とカラム2を連結する際に、カラム1はLATIN、カラム2は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にしておくことで解消します。

 

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