読者です 読者をやめる 読者になる 読者になる

crmprogrammer38の日記

プログラマのメモ

【雑記】DWHのアプライアンスDBについて思うこと

TERADATAについての記事を書いてきましたが、他のDWHのアプライアンスDBはどうなんだろうなと思いました。

 

ただ、調べると、Oracle exadataとTERADATA以外は情報が少ないので、この2つの争いになってるように思います。

 

TERADATAからOracle exadataに移行したり、その逆もあったりしてますが、Oracleは情報が多いのと、技術者が多いのがいいなと思います。もちろんexadataを扱う上での作法はあると思いますが、使い馴染んだOracleSQL、使いやすい関数がそのまま使えるのは魅力的。

 

TERADATAは数々の実績に裏打ちされた実力があって、納得の企業に採用されています。ただ、情報が少ない。技術者がOracleより少ない。この部分のハードルが高い。

 

どちらを選んでもとてもいいDWHが作れると思いますが、最近の時代の流れは情報が沢山あるOracle exadataの方が有利なんだろうなーとか意味も無く考えています。

【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は不要です。

 

BIツールの設定でも、売上データは1つなのに、商品別サマリーや、店舗別サマリーなどがあると複数のデータモデルを用意する必要があったりでメンテナンスも大変だし、利用者も慣れるのに時間がかかる。

 

個人的な考え

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拡張のQUALIFY句がある。

QUALIFY句を使って、分析関数の値でさらに条件をかけることができる。

 

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

 

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

 

ただ、分析関数自体が、煩わしいのもあってあまり使われて無いように感じます。(sum関数のover句内でorder by した際には、 rows between unbounded preceding   and current row がデフォルトで指定される とかね。。最初のとっつきづらさが良くないと思います。)

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

 

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

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

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

 

--改行コード

select 'AAA' || '0A'XC || 'BBB' 

 

--Tab

select 'AAA' || '09'XC || 'BBB' 

 

独特だなー。