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"

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

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

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

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

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

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

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

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

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

 

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

 

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

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

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

 

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

 

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

 

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

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

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

 

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

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

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

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

注意は２点

1. INPUT_DIRに日本語を含むファイルを置くとエラーで止まる。 きっと簡単に直せるが手付かず。
2. 出来上がった仕様書のオブジェクトを結ぶ線は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で使う場合はこんな指定です。

初めての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以上が動作しません。
その場合、下記に従って変更する事で動作する様になります。

1. 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
2.  libswt\win64フォルダを作成
3.  libswt\win32フォルダから以下の3ファイルをlibswt\win64へコピー
1. jaybird21.dll
2. JtdsXA.dll
3. ntlmauth.dll
4.  ダウンロードしたswt.jar をlibswt\win64へコピー
5. Spoon.batを修正
• 変更前 : set CLASSPATH=%CLASSPATH%;libswt\win32\swt.jar
• 変更後 : set CLASSPATH=%CLASSPATH%;libswt\win64\swt.jar

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がインストールされる。

   

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

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

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

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

• Job
  
• Variableを設定
これらはBatch/Shellで上書き出来ます。
 


• 変数を読み込むTransformationを指定
相対パスで指定してみました。

• Transformation
  
  
  




• CVS Input
区切り文字も指定出来るようにしてあります。
文字コードは残念ながらハードコードです。


• Filter
#で始まる行を無視します。 これでコメントを混ぜる事が出来ます。



• Set Variable
Kye列をパラメータ名とし、Value列を値として設定します。



• Write to Log
設定された情報を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でデータが並列処理されるのに対し順々に処理される事が大きな違いです。
例えばこんな感じで使います。

処理は

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

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を使います。

 

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

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

パラメータの設定は

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

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

3. そのままでは選択肢に現れず、使い勝手が悪いので一度、プレビューします。
エラーが出ても気にしない、気にしない。

色々な指定が出来ます。

1. <$>マークが付いている場所
1. カーソールを持って行き、Control+スペースを押す
使用出来るパラメータがドロップダウンメニューで表示されます。

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

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


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

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

1. SQL
「Table Inputの場合、先行のステップから値を引き継ぐ」にチェックを入れないと参照されません。

2. Modified Java Script Value
関数 - 特殊関数にあるgetVariableを使います。 １つ目の引数がパラメーター、２つ目は初期値です。


3. Get Variables
これは単純です。

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

1. 実行寺にBatch, Shell Scriptで指定
Windowsの場合、


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

Linuxの場合、


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

2. Set Variables Stepで設定
フィールド名の値を変数名に設定します。 スコープとは影響範囲です。
「Valid in the Java Virtual Machine」とするとJVMで共有されてしまいますので注意して下さい。
設定した変数は同じStepでは使えず、次のStepやJobで使えるようになります。


3. Modified Java Script Valueで設定
関数 - 特殊関数のsetVarialbesを使います。引数はフィールド名、変数、スコープの順です。
設定した変数は同じStepでは使えず、次のStepやJobで使えるようになります。

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

初めての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からテキストファイルへの出力方法を説明します。

1. Oracle JDBCドライバを準備
2. ドライバをPentahoインストレーションディレクトリのlibext¥JDBCへコピー
   
3. Table inputをドラッグ
4. ドラッグしたTable inputをダブルクリックして設定する
1. データソースをクリックして接続に必要な情報を設定 
• テスト接続を押して設定が正しいか確認出来ます
2. SQLの選択を押し、DBを解析してテーブルを表示させます
• 自分でSQLを入力する事も出来ます
5. テーブルを選択する

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


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

7.  Text file outputをドラッグ
8. Shiftを押しながらTable inputからText file outputまでドラッグして結合する
9.  Text file outputをダブルクリックして設定する
1.  「ファイル」タブのファイル名に出力ファイル名を入力する

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

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

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

動画はこちら