技術的に自由なブログ: 2013

2013年11月22日金曜日

tnsname.ora設定無しでsqlplus

Oracleを使っている時の不満の一つがtnsname.ora。
ODBCで接続する場合ですら管理者権限が必要なtnsname.oraにHost名などを記述する必要がある。

例えばこんなぐらいに定義が必要です。

POD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PODS.GATE.COM)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ODS)
    )
  )

sqlplusからこれを使って接続する場合は

sqlplus user/password@pod

で接続出来ます。

で、問題はtnsname.oraを編集したく無い場合。Windowsでは

Oracle 8i, 9i

sqlplus user/password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=PODS.GATE.COM)(PORT=1521)))(CONNECT_DATA=(SID=ODS)))

Oracle 10g(R2?)以降

sqlplus USER/PASSWORD@//PODS.GATE.COM:1521/ODS

あ〜、すっきりした。

参照：Ask Tom "How to connect SQLPlus without tnsnames.ora"

2013年11月16日土曜日

root権限無しでPerlモジュールを使う

色々な方法はあるようですが、local::libすらない環境でもPerlモジュールを使う方法があっりました。

CPANMをインストール

wget --no-check-certificate -O- http://cpanmin.us | perl - -l ~/perl5 App::cpanminus local::lib
# モジュールをインストール。　例ではMojoliciousを入れました。 
cd ~/perl5/bin
./cpan Mojolicious

環境変数に自分のパスを設定。

setenv PERL5LIB ~/perl5/lib/perl5
# 動作確認
./mojo get www.google.com

環境変数を使わない場合でもPerlスクリプトの先頭で読み込ませる事が出来ます。

#!/usr/local/bin/perl 

BEGIN {
  push ( @INC, '/yourhome/perl5/lib/perl5');
};
use Mojolicious::Lite;

FreeBSDにPerl, curl, Mojoliciousをインストール

ひょんな事からFreeBSDにMojoliciousをインストールする必要があったのでメモ。

OS : FreeBSD 9.1 amd64

rootでログインして作業

# Perl 5.16をインストール
cd /usr/ports/lang/perl5.16
make
make install

# Curlをインストール
pkg_add -r curl

# Mojoliciousをインストール
curl get.mojolicio.us | sh

#動作確認
mojo get www.google.com

2013年9月9日月曜日

Windows BacthからUnix/Linxを制御する方法

Puttyに含まれるplinkを使って外部Unix/Linuxサーバーでコマンドを実行する。
事前に公開鍵をサーバー上の~/.sshにいておく。
ダイレクトログインを禁止されていたらplinkでリモート実行出来ないかも知れません。

setlocal
set USER=server_username
set SERVER=server
rem 実行ユーザーが誰でも読める場所に秘密鍵を置く
set RSA_FILE=C:\Windows\SysWOW64\config\systemprofile/.ssh/id_rsa.ppk
set FTP_PATH=/home/hoge
set FTP_FILENAME=serverfile.txt
set FTP_FILE=%FTP_PATH%/%FTP_FILENAME%
set LOCAL_PATH=C:\path\to\file
set LOCAL_FILENAME=localfile.txt
set LOCAL_FILE=%LOCAL_PATH%\%LOCAL_FILENAME%

rem サーバーへファイルを置く。エラーが起きれば中断
rem 接続に必要な情報は全て明記。 実行ユーザーがセッションファイルを読み込めないかも知れない。
pscp -agent -batch -i %RSA_FILE% %LOCAL_FILE% %USER%@%SERVER%:%FTP_PATH% || exit 1



rem サーバーからファイルを取得。エラーが起きれば中断
rem 接続に必要な情報は全て明記。 実行ユーザーがセッションファイルを読み込めないかも知れない。
pscp -agent -batch -i %RSA_FILE%  %USER%@%SERVER%:%FTP_FILE% %LOCAL_PATH% || exit 1



rem サーバー上のスクリプトを実行
set MESSAGE=Success!
set SERVER_COMMAND=echo %MESSAGE%

rem 接続に必要な情報は全て明記。 実行ユーザーがセッションファイルを読み込めないかも知れない。エラーが起きれば中断
set COMMAND=plink -batch -ssh -agent -i %RSA_FILE% %USER%@%SERVER% %SERVER_COMMAND% || exit 1

rem 出力結果を%RESULTS%に入れる
for /f "delims=" %%i in ('%COMMAND%') do @set RESULTS=%%i

rem 出力結果を表示
echo %RESULTS%

rem Jenkinsで失敗と判断する為にShellで正常終了するとSuccess!と出力。
rem 正常終了の出力でない場合にエラーコードを設定
if not %RESULTS%==Success! exit 1

2013年9月8日日曜日

JenkinsでWindowsバッチを使う例

途中でエラーが起きたら中断
On Error Gotoなんて便利なものは無いのでエラーが起きそうな行に毎回書く必要がある。

copy a.txt b.txt || exit 1

外部バッチファイルを呼び出す
途中でエラーが起きたら中断

call somebatch.bat || exit 1

Puttyに含まれるplinkを使って外部Unix/Linuxサーバーでコマンドを実行する。事前に公開鍵をサーバー上の~/.sshにいておく。

setlocal
set USER=ssh_username
set SERVER=target_server
set MESSAGE=Success!
rem Jenkinsはユーザーのファイルを読めないので誰でも読める場所に秘密鍵を置く
set RSA_FILE=C:\Windows\SysWOW64\config\systemprofile/.ssh/id_rsa.ppk
set SERVER_COMMAND=echo %MESSAGE%

rem 接続に必要な情報は全て明記。 Jenkinsはセッションファイルを読み込めない。
rem 途中でエラーが起きたら中断
set COMMAND=plink -batch -ssh -agent -i %RSA_FILE% %USER%@%SERVER% %SERVER_COMMAND% || exit 1

rem 出力結果を%RESULTS%に入れる
for /f "delims=" %%i in ('%COMMAND%') do @set RESULTS=%%i

echo %RESULTS%

rem Jenkinsで失敗と判断する為に正常終了の出力でない場合にエラーコードを設定
if not %RESULTS%==Success! exit 1

2013年9月7日土曜日

Windowsタスクスケジューラの代わりにJenkinsを使う

Windowsで定期的に実行させる場合、タスクスケジューラを使わずにJenkinsを使う様にしました。
Jenkinsは本来はシステム開発を自動化するツールですが、定期的にバッチを実行する高性能タスクスケジューラとして使えます。

タスクスケジューラと比べて良い点は以下です。

どのジョブが失敗したか明確。
実行履歴を見てどの回が失敗したか明確。
失敗するとメール通知が出来る。
出力ログを取ってくれる上に古いログを自動的に消去も出来る。
前回の実行が終わっていなければ終わるまで待ってから実行される。(多重起動防止)
ジョブ毎にパスワードを設定する必要がない。パスワード変更時に変え忘れにくい。

実際に設定してみましょう。
新規ジョブを作る時はフリースタイルを選択します。

「説明」ではHTMLを書く事が出来ます。
「古いビルドの破棄」を設定すると古いログを自動的に削除してくれます。

「ビルド手順の追加」でWindowsバッチコマンドの実行を追加します。

「定期的に実行」で周期や時間を設定します。

表記方法はcrontabとほぼ同じです。

バッチコマンドを記入します。
Jenkinsでコマンドを入力する時の詳細は次回にでも紹介します。

ビルド後の処理の追加で「E-mail通知」を設定すると失敗の度にメール通知されます。

当然、通知先アドレスを設定します。

ジョブの一覧で失敗していると赤く表示されて分かりやすい。

履歴の一覧はこんな感じで見れます。

#5が失敗したのが一目で分かります。

実行ログはコンソール出力にこんな感じで表示されます。

本来の使い方はJenkinsではじめる継続的インテグレーションなどを参考にして下さい。
定期実行する方法に主に参考にしたのは邪道Jenkinsとおひとりさまからはじめよう。おひとりさまでもはじめよう。です。

2013年9月2日月曜日

初めてのData Integration 12 仕様書作成の自動化

http://code.google.com/p/kettle-cookbook/
成果物としてプログラムの流れなどを図に書いて提出しなければいけなかったり、人に説明する場合に手で取ったスクリーンショットを張ったりするのは面倒ですよね？
そんな場合に便利なのがkettle-cookbookです。

注意は２点

INPUT_DIRに日本語を含むファイルを置くとエラーで止まる。きっと簡単に直せるが手付かず。
出来上がった仕様書のオブジェクトを結ぶ線はInternet Explorer 6, 7では見えない。 Firefoxでは正常に表示される。

初めてのPentaho Data Integration 10 UnixサーバからSamba/Windows Shareを参照

jcifsと言うモジュールが組み込まれています。

下記のような内容でjcifs.propsと言う名前でファイルを作成。
    jcifs.smb.client.username=your_user_name
    jcifs.smb.client.password=yor_password
    jcifs.smb.client.domain=you_domein
    jcifs.netbios.wins=192.168.1.220
    jcifs.netbios.hostname=your_hostname
    jcifs.util.loglevel=3
詳細はjcifsのホームページを参照して下さい。

上記ファイルを使用する為にspoon.bat, kitchen.sh, pan.shを修正します。

Windows版Spoonを使う場合、spoon.batに追加
    set OPT=%PENTAHO_DI_JAVA_OPTIONS% " ...
    set OPT=%OPT% "-Djcifs.properties=./jcifs.props"
kitchen.shに追加
    OPT="$PENTAHO_DI_JAVA_OPTIONS ...
    OPT=$OPT" -Djcifs.properties=jcifs.props"

pan.shに追加
    OPT="$PENTAHO_DI_JAVA_OPTIONS
    OPT=$OPT" -Djcifs.properties=jcifs.props"

例えばGet File Namesで使う場合はこんな指定です。

2013年9月1日日曜日

初めてのPentaho Data Integration 9 テーブルの入れ替え

テーブルの中身を入れ替える場合に普通に作るとアプリケーションが入れ替え途中にテーブルにアクセスするつ入れ替え途中のデータが見えてしまいます。

途中のデータを見えなくするには入れ替え途中にCommitされないように工夫する必要があります。
PentahoでCommitされないように作る場合はこんな方法があります。

ファイルからの入力を元にSQL Insert文を作成

Generate RowsでDelete文、Commitを作成

Append Streamで Delete→Insert→Commitとなるように並べる

データが途切れるとデータが途切れると自動Commitされるのでデータ個数を数え最後のデータを待つ。

Execute row SQL scriptで実行する。コミット数を入力数より大きくする。

SQL文をファイルに書き込んでバルクロードを使った方が素直かも。
Transformationを２つに分けなくても良いのがこの方法の利点です。

初めてのPentaho Data Integration 8 Windows 7 64bitでの動作

Windows 7 64bitではPentaho Data Integration 4.3以上が動作しません。
その場合、下記に従って変更する事で動作する様になります。

swt.jarをダウンロード

http://archive.eclipse.org/eclipse/downloads/drops/R-3.5.1-200909170800/download.php?dropFile=swt-3.5.1-win32-win32-x86_64.zip

libswt\win64フォルダを作成
libswt\win32フォルダから以下の3ファイルをlibswt\win64へコピー

jaybird21.dll
JtdsXA.dll
ntlmauth.dll

ダウンロードしたswt.jar をlibswt\win64へコピー
Spoon.batを修正

変更前 : set CLASSPATH=%CLASSPATH%;libswt\win32\swt.jar
変更後 : set CLASSPATH=%CLASSPATH%;libswt\win64\swt.jar

2013年7月29日月曜日

MacでPerlbrew

ちょっと苦労したので自分へのメモ。
何時の間にかcpanmでモジュールのインストールが出来なくなっていた。
Macを買い替えた時かOSのバージョンアップで失敗したのかも？
sudo perlbrew install perl-5.19.2 としても失敗してしまう。
~/perl5/perlbrew/build.logを見ると「I can't find make or gmake, and my life depends on it.」と書いてある。
Xcodeはインストールしてあるのに何故？

Xcode > Preferences > Downloads > Command line tools を選択 > Install をクリックするとgcc, makeがインストールされる。

これで無事にモジュールがインストール出来る様になりました。

2013年5月14日火曜日

初めてのPentaho Data Integration 7 ファイルからのVariable読み込み

多数の変数を扱う場合、ShellやBatchで全部指定するのは使いにくいので設定ファイルを作って読み込む方が現実的でしょう。

例えばこのようなJobを組み込むと良いでしょう。

Variableを設定

変数を読み込むTransformationを指定

Transformation

CVS Input

Filter

Set Variable

Write to Log

Config

Key Value
# This is configuration file.  The line starting with # shall be ignored.
Country France

使用方法
区切り文字をタブでは無く、「;」に変更した場合あ以下の様に指定します。

Windowsの場合、

kitchen.bat -file:"C:\Temp\ETL\Job\SetVariables.kjb" -param:ConfigFieldDelimiter=";"

Linuxの場合、

sh kitchen.sh -file:"/home/somebody/ETL/Job/SetVariables.kjb" -param:ConfigFieldDelimiter=";"

初めてのPentaho Data Integration 6 Job

Jobはファイルを操作したり、エラー時にメールを送信したり、Transformationよりも大きな目的で使われます。また、TransformationはStepでデータが並列処理されるのに対し順々に処理される事が大きな違いです。
例えばこんな感じで使います。

処理は

Set varialbesで変数を設定
Transfromationでデータ処理
Simple evaluationで条件分岐
条件に合わなければメールを送信
終了

Set variables, Transformation, Mail too manyでエラーが起きればMail Alarmでメールが送信されます。

個人的に良く使うのは以下です。詳細はSampleやCommunityを参照して下さい。
当然、扱うデータと目的に大きく依存しますので参考程度にしてください。

全般

START : 当たり前です。ここから処理が開始されます。
Job : 他のJobを呼ぶ
Set variables : 同じTransformationでvariableだけ変えて処理する時に使います。
Success :処理の最後に入れます。入れなくても動くのですが理解しやすいように入れています。
Transformation : そのまま。

メール

Mail : メール送信。

ファイル管理

Copy Files : ファイルコピー。正規表現で複数ファイルをコピー出来ます。
Delete file : 1ファイルの削除
Delete files : 正規表現で複数ファイルを削除出来ます。

条件

Simple evaluation : 変数や前のTransformationの結果で条件分岐
Wait for : 待ち。エラーの後に1秒待って、再処理などで使います。

スクリプト

Shell : Pentahoで出来ない処理をShellに任せます。

Untility

Write to Log : ログを書き出します。

ファイル転送

Get a file with FTP : FTPでファイル取得。
Put a file with FTP : そのままです。

大きなファイルをGet/Put FTPすると中断される場合を想定するとこんな感じのJobを使います。

2013年5月13日月曜日

初めてのPentaho Data Integration 5 パラメータ(Variable)

使いこなすと非常に便利なパラメータ(Variable)です。
ファイル名や検索条件など変更の為にプログラムを修正したくない場合に使います。
また相対パスを使ったファイルの指定にも使います。
CVS file inputのファイル名のように<$>マークが付いている場所、SQLを書くStep、Modified Java Script Value、Get Varialbesで使えます。

パラメータの設定は

編集 - 設定で変換設定画面を出す

パラメータタブで名前と値を設定

そのままでは選択肢に現れず、使い勝手が悪いので一度、プレビューします。

色々な指定が出来ます。

<$>マークが付いている場所

カーソールを持って行き、Control+スペースを押す

選択すると${}で囲まれたパラメータが表示されます。手入力でもOKです。

相対パスを使う場合、例えば以下のような構造でTranformationフォルダ内のTransformationからReportフォルダフォルダ内のfileを指定する場合はこんな感じで指定します。

${Internal.Transformation.Filename.Directory}${file.separator}..${file.separator}..${file.separator}Report${file.separator}file

$[ASCIIコード]と手入力する事で特殊文字も入力出来ます。

Modified Java Script Value

Get Variables

変更方法は実行時にBatch, Shell Scriptで指定、Set Variables Stepで指定、Modified Java Script Valueで指定の３つです。

実行寺にBatch, Shell Scriptで指定

pan.bat -file:"C:\temp\Variables.ktr" -param:COUNTRY="France"

sh pan.sh -file:"/home/somebody/Variables.ktr" -param:COUNTRY="France"

Set Variables Stepで設定

Modified Java Script Valueで設定

覚えてしまえば非常に便利ですので使いこなして下さい。

2013年4月22日月曜日

初めてのPentaho Data Integration 4 良く使うStep

良く使うStepたち。具体的な使い方はSampleやCommunity Siteを参照して下さい。
使用頻度は扱うデータと目的に大きく依存しますので参考程度にして下さい。

入力

CSV input

1ファイルの読み込み。

Text file input

複数のファイルの読み込み
ファイル名に例えば*.txtなどの正規表現も使える

Generate Rows

空や定数の入力を作る

Get File Names

フォルダ内のファイル名を取得
その後、フィルターを通してファイルを読み込んだりする事が多い

Data Grid

Generate Rowsと異なり、Row毎に異なる値を設定出来る
テストの時にダミーデータを作るのに便利

Table input

データベースからの読み込み

出力

Table output

データベースへの書き込み
テーブルの入れ替えと追加だけ出来、変更は出来ない

Update

データベースへ変更だけが出来る

Insert / Update

データベースへ追加と変更が出来る

Delete

データベースの削除

Text file output

ファイルへの書き込み

変換

Select values

残すフィールドの指定
削除するフィールドの指定
フィールド名の変更、型変換

Calculator

プログラムを書かないのは良いのですが、機能が少ないのと冗長になるので余り使っていません。 Modified Java Script Valueを代わりに使います。

Replace in string

文字列の置き換え
1フィールドの置き換えだけの場合に使います。複数の処理になるとModified Java Script Valueを使う事が多いです。

Row normalizer

指定行を列に変換する。
使い方が難しいので別に説明するかも知れません。

Row denormalizer

複数の列を行に変換する
使い方が難しいので別に説明するかも知れません。

Sort rows

並べ替え
良く使います

ユーティリティ

Write to log

デバッグに重要ですので出力しましょう

Mail

e-mailを送る

フロー

Abort

エラーや異常値が見つかったら中断したりする時に使う

Append streams

1つの入力の後に別の入力を繋げて出力

Dummy (do nothing)

デバッグ時にプレビュー用に追加する事もある
複数の入力を順序を気にせず繋げる場合にも使う

Filter rows

そのままフィルターです。非常に良く使います。

Java Filter

分岐条件をJava Expressionで書く事が出来る
便利そうですが使った事がありません

Switch / Case

条件によって複数の分岐先を指定
余り使う機会が無い

スクリプト

Modified Java Script

Javascriptを使って色々な処理が出来る
新しい列の作成、文字列の切り出し、計算、型変換など
使いすぎるとGUIベースのPentahoの意味が無いとの批判も、、、

Execute SQL script

入力を元にSQLを実行
入力をWhere句の条件に入れてDeleteする場合に使ったりします

ルックアップ

Stream lookup

フィールドの値が同じ場合に「ステップ名」のフィールドを取得

Database lookup

入力を元にデータベースに問い合わせてフィールドを取得
毎回データベースに問い合わせるのでパフォーマンスが悪い。大量のデータを処理するなら別にTable inputとStream lookupを使った方が良い。

Database join

Database lookupは表またはビューに対して単純なlookupしか出来ないが、joinでは複雑なSQLを書く事が出来る

結合

Merge Join

SQLで言う所のInner Join, Outer Join

Merge Rows (diff)

二つの入力の差を出力

統計

Group by

ユニークはRowだけを出力したり、件数を数えたり、合計を計算したりする
文字列を連結する事も可能

Analytic Query

前後のRowの情報を取得する事が出来る

Sample rows

テストの時にデータを減らしたりする場合に使う事が多い
先頭行だけ取得したい時にも使う

ジョブ

Copy rows to result

入力をメモリに保持

Get rows from result

メモリから入力を取得

Get Variables

変数の取得

Set Variables

変数の設定

マッピング

Mapping (sub-transformation)

いわゆるサブルーチンの呼び出し(Transformation)

Mapping input step

サブルーチンでの入力取得

Mapping output step

サブルーチンでの出力

初めてのPentaho Data Integration 3 Oracle DBからテキストファイルへの出力

Oracle DBからテキストファイルへの出力方法を説明します。

Oracle JDBCドライバを準備
ドライバをPentahoインストレーションディレクトリのlibext¥JDBCへコピー
Table inputをドラッグ
ドラッグしたTable inputをダブルクリックして設定する

データソースをクリックして接続に必要な情報を設定

テスト接続を押して設定が正しいか確認出来ます

SQLの選択を押し、DBを解析してテーブルを表示させます

自分でSQLを入力する事も出来ます

テーブルを選択する

フィールド名をSQLに追加しますか?に「はい」を押し、全ての列名を入力させます

もちろん、「いいえ」を押し、SELECT *のままでも問題ありません
プレビューする事も出来ます

Text file outputをドラッグ
Shiftを押しながらTable inputからText file outputまでドラッグして結合する
Text file outputをダブルクリックして設定する

「ファイル」タブのファイル名に出力ファイル名を入力する

「フィールド」タブの「フィールドの取得」を押すと入力ステップの全フィールドが入力される

フィールド」タブの「最小桁数にする」を押すと出力ファイルが最小になります

「ファイル→名前を付けて保存」で出来上がったファイルを保存
「Action→実行」で動作！

こちら