次の行の開始日から終了日を計算するSQL

時価で変動する商品の値段のように、期間で管理されるデータ管理する場合のテーブルの持ち方とSQLについての考察です。

例えば、以下のように通貨変換レートが適用されるようなケースがあるとします。

sql-currency-enable-period

各期間には適用開始日と終了日がありますが、現在進行系の期間は終了日が無いですし、次の期間の開始日から前の期間の終了日は計算できるので、テーブルには開始日のみ管理すれば良さそうです。

開始日のみを管理するメリットとしては、余分な値「終了日」を保持しなくてよいのでカラムが減らせることと、終了日をプログラム(プロシージャ)やトリガーなどでセットしないのでバグりにくいことなどがあります。

この場合に、次の期間のレコードから計算した終了日を含むレコードを取得するSQLを作成してみます。

サンプルのテーブル

CREATE TABLE `currency_rates` (
  `from_currency_code` char(3) NOT NULL,
  `to_currency_code` char(3) NOT NULL,
  `enabled_at` date NOT NULL,
  `rate` decimal(12,2) NOT NULL,
  PRIMARY KEY (`from_currency_code`,`to_currency_code`,`enabled_at`)
);

“次の行の開始日から終了日を計算するSQL” の続きを読む

MongoDBをChefでインストール

MongoDBをChef Supermarketに公開されているCookbookを利用してKnife soloでインストールする手順のまとめです

公開されているCookbookは、mongodbとmongodb3などがり、2.x系はmongodb、3.x系はmongodb3でインストールできます

以下、手順
確認は、Ubuntu 14.04で行っています

対象サーバーへのアクセス設定

対象サーバーのホスト名を登録
[text title=”/etc/hosts”]
54.33.11.22 mongodb
[/text]
ssh ubuntu@mongodbでsshできるように、公開キーを対象サーバーに登録しておく
[bash gutter=”false”]
$ cat ~/.ssh/id_rsa.pub | ssh ubuntu@mongodb -i ~/.ssh/server.pem ‘cat >> ~/.ssh/authorized_keys’
[/bash]
さらに、Chef実行時にユーザー名も省略できるように.ssh/configにUserを指定しておくと便利
[text title=”.ssh/config”]
Host mongoldb
User ubuntu
[/text]
“MongoDBをChefでインストール” の続きを読む

Oracle Instant Client をWindows ODBC データソースとして登録手順メモ

Oracle Instant Clientを利用してODBCデータソースを定義する手順を調べたのでまとめてみました。

セットアップ手順

1. Oracle Instant Clientを入手

OracleのダウンロードサイトよりOracle Instant Clientをダウンロードします。
http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

今回は32bit版を利用するので、Instant Client Downloads for Microsoft Windows (32-bit)にアクセスして以下のファイルを落とします。

instantclient-basic-nt-12.1.0.2.0.zip
instantclient-odbc-nt-12.1.0.2.0.zip

2. ファイルを展開

ダウンロードした2つのファイルを同じディレクトリに展開します。
C:¥直下に展開したすると以下のようになります。
expand_instantclient_archives

3. パスを通す

[システム] > [システムの詳細]を選択して[詳細設定]タブの[環境変数]を開いて、システム環境変数”Path”に展開したディレクトリ”FileMaker調査”を追加します。

4. ODBCドライバのインストール

管理者としてコマンドプロンプトを開き以下のコマンドを実行します。

c:> odbc_install.exe JA
Oracle ODBC Driver is installed successfully.

5. instantclient-odbc-windows.x64-11.2.0.4.0.zipを再度配置

後の手順のODBC データソースの追加で、[Oracle]を選択すると以下のエラーが発生します。
DriverTitle Error Connot Load Resource File sqresus.dll

odbc_installを実行すると、sqresus.dllが消されてしまうので、再度instantclient-odbc-nt-12.1.0.2.0.zipを解凍しなおしておく必要があります。

6. Microsoft Visual C++ 2005 SP1 再頒布可能パッケージをインストール

ODBC データソースの追加で以下のエラーが発生する場合
Microsoft ODBC アドミニストレーター ドライバーの ConfigDSN、ConfigDriver、または Config Translator が失敗しました。検出されたエラー:システム エラーコード 14001: このアプリケーションのサイド バイ サイド構成が正しくないため、アプリケーションを開始できませんでした。詳細については、アプリケーションのイベント ログを参照するか、コマンドライン ツール sxstrace.exe を使用してください。(C:\○○○\SQORAS32.DLL)のために、Oracle in instantclient_11_2 ODBC ドライバーのセットアップ プログラムを読み込むことができません。

Microsoft Visual C++ 2005 SP1 再頒布可能パッケージ (x86) をダウンロードしてインストールします。
http://www.microsoft.com/ja-jp/download/details.aspx?id=5638

6. 接続先の設定

以下のファイルをC:\instantclient_11_2に作成

// tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mydb.cl5xoo5zhuk1.ap-northeast-1.rds.amazonaws.com)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = ORCL))
)

環境変数 “TNS_ADMIN” に”C:\instantclient_11_2\tnsnames.ora”をセット
set_tns_admin_to_environments

http://apis.jpn.ph/fswiki/wiki.cgi?page=Oracle%2FInstantClient

7. ODBCデータソースの設定を追加

[コントロールパネル] > [システムとセキュリティ] > [管理ツール]を開き[ODBCデータソース(32ビット)]を選択
[システムDNS]タブを開き[追加]をクリックします
OCBD データソース アドミニストレーター (32ビット)

[データソースの新規作成]で[Oracle in instantclient_12_1]を選択し[完了]をクリックします
データソースの新規作成

[Oracle ODBC Driver Configuration]が開くので以下のように値をセットします
Oracle ODBC Driver Configuration
[TNS Service Name]はtnsnames.oraファイルに設定した”ORCL”が選択します。

接続テストは[Test Connection]をクリックします
OracleODBCDriverConnect
接続情報を入力して[OK]をクリックして以下が表示されれば接続成功です。
TestingConnection

最後に[Oracle ODBC Driver Configuration]の画面で[OK]をクリックして登録完了です。

データソースを利用してみる

ExcelにOracleのデータを読み込んでみます。
[データ]タブ[その他のデータソース]から[Microsoft Query]を選択すると以下のように追加したデータソースを選択できようになっていることが確認できます。
MicrosoftQuerySelectDatasource

Talendを使ってSQL ServerへCSVデータを追加するには

Microsoft SQL ServerにCSVのデータをTalendを使って追加する方法についてまとめました。

SQL Serverの管理はSQL Server Management Studioを利用しています。

SQL Server Mangement Studioの使い方はこちらが参考になりました。
【初級編③】Management Studio を使った SQL Server の基本的な操作方法(1/2) | SQLServer2008虎の巻

Talendの基本的な使い方については、以前の記事 オープンソースETLツールTalendでExcelのデータをデータベースにロードしてみた も参考にしてみてください。

では、さっそく

インポートするCSVを設定

読み込むCSVはEメールと名前のみのシンプルなものです。
[text]
E-Mail,Name
suzuki@example.com,鈴木一郎
satoh@example.com,佐藤太郎
[/text]
CSVのファイルの読み込みは[メタデータ] > [区切り記号付きフィアル] > [区切り記号付きファイルの作成]を選択します。
Talend-csv-1
[Name]を入力して[次へ]
Talend-csv-2
[ファイル]に入力CSVファイルを指定します。
Talend-csv-3
Mac上でTalendを動かしているので、UTF8のCSVファイルを入力として扱っています。Shift_JISだと文字化けします。
クライアントのJavaのデフォルトエンコーディングしだいだと思われるので、Windows上ならJavaのデフォルトエンコーディングがShift_JISなので、Shift_JISのCSVファイルで問題ないはず。未確認ですが。
しかし、[次へ]をクリックして、次のダイアログの[ファイル設定]の[エンコーディング]はWindows-31JやSfhit_JSが無いのでやっぱりCSVでShift_JISはNGかも。
ここでは、見出し付きのCSVなので[スキップする行]の[Header]にチェックをいれて”1″をセットします。
[プレビュー]で[列名として先頭業を指定]にチェックを入れて[プレビューのリフレッシュ]をクリックするとプレビューで先頭行が列名として設定されたことを確認できます。
Talend-csv-4
このダイアログは、内容を確認して[終了]します。
Talend-csv-6

出力先としてSQL Serverを設定

SQL Serverのデータベース”sqldb”に定義した”Users”テーブルにCSVから読み込んだ行を追加します。

[メタデータ] > [DB接続] > [DB接続の作成]を選択します。
SQLServer-users-table

Talend-SQLServer-1

Nameを入力して[次へ]
Talend-SQLServer-2

接続情報を入力して[チェック]をクリックし接続に成功したら[終了]します。
Talend-SQLServer-3

追加された[Users]から[スキーマ情報の取得]を選択します。
Talend-SQLServer-4

ここはそのまま[次へ]
Talend-SQLServer-5

“dbo”を開き”Users”を選択して、[次へ]
Talend-SQLServer-6

内容をか確認して[終了]します。
Talend-SQLServer-7

補足: RDSのSQL Serverで日本語を使えるようにする

今回ためした出力先のSQL ServerはAWS RDSのSQL Serverを利用しています。
RDSのSQL Serverはデフォルトでは英語設定になっているので日本語が扱えるように設定する必要があります。
照合順を日本語に設定します。

SQL Server Management Studioでデータベースのメニューから[プロパティ]を選択します。
SQLServer-properties
[オプション]を選択して[照合順序]で”Japanese_XJIS_100_BIN”を選択します。
SQLServer-set-japanese
参考: RDSにSQL Serverのセットアップ
補足の補足: 他のプロセスが対象のデータベース、ここでは”sqldb”を利用していると5030エラーが発生します。その際はプロセスをKillしてあげると設定を反映することが出来ます。参考: 照合順序の変更について

マッピングの作成

[メタデータ] > [DB接続] > [Users 0.1] > [テーブル] > [Users]をドロップして、コンポーネントダイアログでは[tMSSqlOutput]を選択します。
Talend-mapping-1

[メタデータ] > [区切り記号付きファイル] > [UserList 0.1] > [metadata]をドロップして、コンポーネントダイアログから[tFileInputDelimited]を選択します。
Talend-mapping-2

[パレット]から[tMap]をドロップします。
Talend-mapping-3

[CSV]から[tMap]、[tMap]から[SQLServer]へと入出力をつなげます。
Talend-mapping-4
[tMap]から[SQLServer]への出力をつなげた後に、スキーマの取得しますか?と聞かれるので[はい]を選択します。

[tMap]をクリックしてマッピングを以下のように定義します。
Talend-mapping-5

ジョブを実行
ジョブが作成できたので、実行してみます。
Talend-run-job

CSVから正しくデータをインポートされたかManagement Studioで確認。
SQLServer-select

オープンソースETLツールTalendでExcelのデータをデータベースにロードしてみた

オープンソースのETLツールTalendの使い方についてインストールから基本的な使い方までをまとめてみました。

Talendとは

TalendはオープンソースのData Integrationツールです。
利用用途としては主にETLという認識でしたが、HPをさっとみただけでは全容が把握できないほど多機能です。

HP: http://www.talend.com/
HP(日本):http://jp.talend.com/

有償版もあるので企業導入の際にも支障がありません。
コミュニティ版と企業版の違いは http://jp.talend.com/products/ の各ページに比較表があります。
表中で「Talend Open Studio for Data Integration」が無償のコミュニティ版、「Talend Enterprise Data Integration」と「Talend Platform for Data Management」が有償版です。

では、セットアップしてタイトルの通りExcelのデータをデータベースMySQLにロードしてみます。

セットアップ

1. コミュニティ版を入手

http://www.talend.com/download
にアクセスして[Data Integration]タブを開き[Talend Open Studio for Data Integration]の[Download Free Tool »]をクリックし、OSを選択するとDLが始まる。
コミュニティ版もサイズが450Mほどあるので時間がかかります。

2. 実行環境の準備

Talendは、Javaで動きますのでJava実行環境をインストールしておきます。

3. Talend Open Studio for Data Integration の起動

Talendの開発ツール Talend Open Studio for Data Integration は、Macの場合はインストール不要で、ダウンロードしたファイルを解凍して、すぐに利用できます。
TOS_DI-macosx-cocoa を開きます。

(Windowsはインストーラexeでインストールします)

4. ライセンスの承諾

初回起動時は、LGPL3ライセンスの承諾を要求されますので[承諾]をクリックしておきます。

プロジェクトの作成

Talend-create-project-1
新規プロジェクトの作成に「ExcelToMySQL」と入力して[作成]ボタンをクリックします。

Talend-create-project
[終了]をクリックするとプロジェクトが作成されます。

Talend-create-project-2
右上の[開く]をクリックします。

Talend-registration-to-cummunity
TalendForgeの登録は[スキップ]しておきます。

Talend-welcome
EclipseベースのTalend Open Studioが開きます。

ジョブの作成
[ようこそ]の画面中央の[新規作成…] > [Job]をクリックするか、 [ようこそ]を閉じて画面左の[ジョブ]の右クリックメニューから[ジョブの作成を]をクリックして新しいJobを作成していきます。

Talend-create-job
[Name]に適当なジョブ名(ここでは「FirstJob」)を入れて[終了]をクリックすると作成したジョブの編集画面が開きます。
なお、ジョブ名には空白や日本語は使えません。

Talend-create-job-2

Excelをデータソースとして定義

入力ファイルは以下のようなメールアドレスと名前のみを含むユーザリストのExcelデータです。
Talend-ExcelData

[メタデータ]を開き[Excelファイル]で右クリックし、メニューから[Excelファイルを作成]をクリックします。
Talend-Excel-1

[Name]のみが必須なので、[Name]を適当い入れて[次へ]をクリック
Talend-Excel-2

入力のExcelファイルを選択して、[シートパラメータの設定]で、入力のシートを選択します。ここでは”ユーザリスト”シートを選択しています。
Talend-Excel-3
[次へ]をクリックします。

[列名として先頭行を設定]にチェックをいれて[プレビューのリフレッシュ]ボタンをクリックすると、1行目が列名として設定されます。
Talend-Excel-4
Talendは日本語の扱いが微妙でExcelの先頭行が日本語だと”Cloumns1″の様な自動的に振られた名前になってしまいます。

[スキーマの説明]で[COLUMN]に1行目が列名として設定されていることを確認したら[終了]をクリックしてダイアログを閉じます。
Talend-Excel-5

MySQLのテーブルをデータソースとして追加

出力先のMySQLのテーブルは以下の通りです。
[SQL]
CREATE TABLE `users` (
`email` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`email`)
);
[/SQL]

MySQLのデータソースは、[メタデータ] > [DB接続]から[DB接続の作成]をクリックします。
Talend-MySQL-1

[Name]を入力して[次へ]
Talend-MySQL-2

[DB Type]で”MySQL”を選択肢、接続情報を入力していきます。
データベースはUTF-8なので、[追加パラメータ]には”&useUnicode=YES&characterEncoding=utf8″を追加します。
Talend-MySQL-3

接続のチェックのために[チェック]をクリックすると、接続にMySQLのJDBCドライバーが必要なためインストール用ダイアログが表示されます。
Talend-install-jdbc-1
[Download and install all module available]をクリックして、ライセンスを承諾するとモジュールのダウンロードが始まります。
インストールが終わると自動でダイアログが閉じ接続がチェックされます。
Talend-check-connection
[OK]をクリックして[次へ]をクリックします。

[次へ]が有効にならない場合は[終了]をクリックします。
データベース接続”users”が追加されているので、右クリックメニューを開き[スキーマ情報の取得]をクリックします。
Talend-get-scheme-info

すべてデフォルトのまま[次へ]をクリック
Talend-MySQL-4

[テーブル] “users”にチェックを入れて[次へ]
Talend-MySQL-5

内容を確認して[終了]
Talend-MySQL-6

ジョブを定義

Excelファイルを入力として定義します。
上で定義したExcelファイル”ユーザーリスト 0.1″をドロップするとExcelの入出力を選択するダイアログが表示されるので [tFileInputExcel]を選択します。
Talend-job-drop-Excel

DB接続を出力として定義します。
上で定義したDB接続”users”をドロップすると同様にダイアログが表示されるので[tMysqlOutput]を選択します。
Talend-job-drop-MySQL

これで、入出力が用意できました。
次に、入出力をつなげるマッピングを定義していきます。
マッピングを処理は右ペインから[tMap]をドロップします。ステンシルはたくさんあるので、検索を使っても探せます。
Talend-job-drop-tMap

Excel入力を[tMap]につなげるには、Excel入力上で右クリックメニューから[Row] > [メイン]を選択します。
Talend-mapping-1

選択するとExcel入力からの線が表示されるので、それを[tMap]にドロップします。
Talend-mapping-2

次に、[tMap]からMySQL出力へ接続します。
同様に、[tMap]の右クリックメニューから[Row] > [*New Output*(メイン)]を選択します。
Talend-mapping-3

出力名を入力して[OK]をクリック
Talend-mapping-4

[はい]をクリック
Talend-mapping-5

以下のように入出力がつながりました。
Talend-mapping-6

まだ、Excelの各列とMySQLのカラムがマッピングされていない状態なので、次にマッピングを定義します。
[tMap]をダブルクリックするとマッピングを定義用のウィンドウが表示されます。
Talend-mapping-7
[自動マップ]をクリックすると自動でExcelの列とMySQLのカラムを適当にマッピングしてくれます。
Talend-mapping-8

ジョブを実行

ジョブの実行は、下の[実行(ジョブFirstJob)]タブの中の[実行]ボタンをクリックすると開始します。
Talend-run-job

実行結果が表示されます。
Talend-run-result

MySQLのテーブルに追加されたことも確認できました。
Talend-select-result

以下、参考情報

Talend Open Studio for DI_v5「2つのテーブルスキーマを統合して集計」
DB等利用のJava自動生成 Talend Open Studio その5-1:DB入力表示
DB等利用のJava自動生成 Talend Open Studio 5-2.CSV書き出し
Talend Open Studio でジョブを作成する(CSVからデータベースへ)

JDBC を使用して Windows Azure SQL データベースに接続する方法

MongoDB インストール on Ubuntu 12.04 メモ

MongoDBのインストールは、公式ドキュメント Install MongoDB にくわしい説明があります。
Ubuntuへのインストールも Install MongoDB on Ubuntu に載っています。

実行したコマンドをメモっておきます。

10genのリポジトリを追加
[bash]
$ sudo apt-key adv –keyserver hkp://keyserver.ubuntu.com:80 –recv 7F0CEB10
$ echo ‘deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen’ | sudo tee /etc/apt/sources.list.d/mongodb.list
$ sudo apt-get update
[/bash]

mongodb-10gen を指定してインストール
[bash]
$ sudo apt-get install mongodb-10gen
[/bash]
=に続けてインストールバージョンを指定することもできます。
[bash]
$ sudo apt-get install mongodb-10gen=2.4.3
[/bash]

データベースを作成するディレクトリを作成
データベースを保存するディレクトリは /etc/mongodb.confdbpath で指定します。
[text]
dbpath=/var/lib/mongodb
[/text]

データベース用のディレクトリの作成して設定を変更
[bash]
$ sudo mkdir -p /data/mongodb
$ sudo chown mongodb:mongodb /data/mongodb/
$ sudo vi /etc/mongodb.conf
dbpath=/data/mongodb
$ sudo service mongodb restart
[/bash]

上記の手順は、Cloudnで確認しました。
Cloudnで追加ディスクを利用する場合は以下の手順で用意します。
[bash]
$ sudo mkfs -t ext3 /dev/vdb
$ sudo mount /dev/vdb /data/mongodb/
$ df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/localhost-root 13123096 4622208 7834264 38% /
udev 242104 4 242100 1% /dev
tmpfs 100648 272 100376 1% /run
none 5120 0 5120 0% /run/lock
none 251616 0 251616 0% /run/shm
none 102400 0 102400 0% /run/user
/dev/vda1 233191 29081 191669 14% /boot
<strong>/dev/vdb 103212320 192248 97777192 1% /data/mongodb</strong>
$ sudo vi /etc/fstab

/dev/vdb /data/mongodb ext3 defaults 0 2
[/bash]

動作の確認
[bash]
$ mongo
MongoDB shell version: 2.4.3
connecting to: test
Welcome to the MongoDB shell.
For interactive help, type "help".
For more comprehensive documentation, see
http://docs.mongodb.org/
Questions? Try the support group
http://groups.google.com/group/mongodb-user
> db.test.save( { a: 1 } )
> db.test.find()
{ "_id" : ObjectId("5250e2491a1f3c8880326237"), "a" : 1 }
[/bash]