皆さんCSVでデータベースのデータ抜きたい時どうしてるんですか？
よかったら教えてください！
私はこんな感じ↓↓↓
データベースのテーブルをCSVにしたい
抽出依頼が来てデータベースに対してクエリ書いて出た結果をCSVにしたい。
社内データ分析者はSQL書いて分析用のファイルを作成したりします。
エクセルで組んでODBC接続してきてもいいのですが、どうせならそのまま統計ソフトにデータをぶち込みたい。
そのまま数値の計算ロジックや入れ替えロジックを分析ソフト側で処理してしまいたい。
ODBCで接続
データベースとの接続の仲介をしてくれるodbcなるものがあります。
ODBCの設定やなんたるかは他のサイトに任せましょう。
データベースを自分で設定する人でなければ、誰かが使っているはずなので聞きましょう。
とりあえずデータベースサーバのIPアドレスと接続用ドライバを聞けばOKです。
設定時に「データソース名」を設定します。
これをメモっておいてください。
まずR言語の方から
RのインストールはCRANからダウンロードしてインストール、Rエディタに以下のスクリプトを打ち込めば実行できます。
テーブルの想定 と ぶっこ抜きスクリプト
user_logという600万レコードが詰まっているデーブルがあったとする
普通にデータベースに接続するソフトですべて抜き出そうとすると、とてつもなく時間がかかる。
他の人も接続していたらサーバーのCPU負荷が恐ろしいことになって皆様から怒られること必須。
データベースに繋げるソフトでは →CSE が好き。
これもODBCのデータソース名とID,passが分かれば接続できる。
基本的にはこんな構図
library(RODBC)
conn_DB <- odbcConnect("データソース名", "割り振られたID", "IDと一緒に発行されるであろうpass")
query <- paste0("select count(*) from user_log")
tbl <- sqlQuery(conn_DB, query)
odbcClose(conn_DB)
これをFOR文で回す。
まず上でデータベースに接続してテーブルが何行あるか確認できる。
600万と確認できたとしよう。
一括で抜こうとするとサーバーのCPUが思考停止するので分割で小規模に抜いていく。
分析ソフト側もメモリがパンパンになってしまう可能性があるので細かく書き出して最後に結合することをオススメする。
SEQ<-seq(1,6000000,5000)
LIM<-length(SEQ)-1
conn_DB <- odbcConnect("データソース名", "割り振られたID", "IDと一緒に発行されるであろうpass")
for(i in 1:LIM){
query <- paste0(
"select * from user_log ",
"limit 5000 offset ",
as.numeric(SEQ[i])
)
tbl <- sqlQuery(conn_DB, query)
write.csv(tbl, paste0("this_is_",i,"th.csv"), row.names=F)
Sys.sleep(10)
}
odbcClose(conn_DB)
細かい解説と注意
これで細かく5000レコードに分けてデータを抽出できた。
SQLで書いているクエリをpaste0で結合させてやればいい。
SQL中にシングルクォーテーション記号がある場合はバックスラッシュを前に着けてやる。
where colum = '10'
where colum = \'10\'
paste0で文字を結合するとSQLの改行が消える時があるので注意しながら書くこと。
paste0("select * from user_log", "where colum = '10'")
select * from user_logwhere colum = '10'
くっついちゃっている。
抽出したデータを結合
setwd("csvを吐き出したファイルのpath")
lf <- list.files(pattern="csv")
data <- data.frame()
for(i in 1:length(lf)){
add <- fread(lf[i])
data <- rbind(data,add)
}
これでdataに600万のデータが入る。
PCに十分なメモリ積んでないと処理がカタマルので注意。
機械学習するなら各csvをミニバッチに相当させるとよろしいのでは？
python言語編
ある程度方針は説明したので以降は説明を割愛して書く。
import pyodbc
import numpy as np
import pandas as pd
cnx = pyodbc.connect('DSN=データソース名; UID=IDを入れる; PWD=passを入れる')
cursor = cnx.cursor()
list_for = np.arange(1,60000,5000)
list_for=list_for.tolist()
for i in range(len(list_for)):
made_que = "SELECT * FROM user_log " + "LIMIT 5000 OFFSET " + str(list_for[i])
cursor.execute(made_que)
tbl = cursor.fetchall()
array_format = np.array(tbl)
dataframe_format = pd.DataFrame(array_format)
dataframe_format.to_csv('csv_data/' + str(i) + '.csv')
以上
抽出してからが分析本番