Archive for the ‘MySQL’ Category

1億2千万行のcsvデータをデータベースに保管したくなったので、rubyを使ってデータベース(Mysql)に挿入した。

gem install mysql

でrubyのmysqlライブラリを入れようとしたら

libmysqlclient-dev を先にaptでインストールするべしとのお告げを得たので

sudo apt install libmysqlclient-dev

でまずはlibmysqlclient-devをインストール

その後

gem install mysql

で無事gemが入った

以下mysqlDBにファイルからデータをINSERTするのに使ったコードです。

#!/usr/bin/ruby

require 'mysql'

client = Mysql::new("127.0.0.1", "username", "password", "db-name")

# set charcter code UTF-8
client.query("set character set utf8")

File.open('./source.csv', 'r') do |f|
    f.each_slice(1000) do |chunk|
        chunk.each do |lines|
			# chop() cuts "\n" end of line
            buf = lines.chop.split(',')
            client.query("INSERT INTO test(time,price,amount) VALUES(#{buf[0]},#{buf[1]},#{buf[2]});")
        end
    end
end

client.close

io.each_slice(1000)

普通のeachを使うと一行ずつの処理になってしまい、時間もかかるものと思われるが、each_slice(n)によってnの数分だけ一気に処理を行うことができる。

nの数は各々のメモリ搭載量に合わせて変更していただければと思います。

ただ想定外だったのは、これでも一万件のレコードを挿入するのにおよそ1分くらいかかってしまうこと

単純に計算して1億2千万件のレコードを挿入し終えるのは166時間以上の時間が必要ということになる…

NOSQLデータベースだったらもっと早いんだろうね。

そのうちそっち方面も探っていきたい

テーブルの構造を変える時には"ALTER"句を使います。
今回はカラムの削除と追加方法をメモっておきます。

テーブルの既存のカラムを削除する

構文はこの様になります。
ALTER TABLE [テーブル名] DROP COLUMN [削除する列名];

具体的には以下の通り。

ALTER TABLE tableName DROP COLUMN oldColumn;

テーブルに新しいカラムを追加する

構文はこの様になります。
ALTER TABLE [テーブル名] ADD [カラム名] [型名];

具体的には以下の通り。

ALTER TABLE tableName ADD newColumnName TINYINT;

次の構文で制約を設ける事もできます。
ALTER TABLE [テーブル名] ADD [カラム名] [型名] [制約];

以下では新しくTINYINT型の列を追加し、その列のデフォルト値を0に設定しています。

ALTER TABLE tableName ADD newColumnName TINYINT DEFAULT 0;