ODBCでデータベースに接続(1).R

2021年1月25日 22:02

要約

・外部データベースと接続してデータを取り込もう
・RODBC::odbc*()で接続・切断
・sqlFetch()でローカルに保存→あとはRでjoin、で最低限OK

せっかくなのでデータベース接続しよう

　前回までやってきたjoinの源流はSQL、つまりデータベースを扱う言語にある。
　SQLでは、データベース（データソース）にクエリ（Query）という問い合わせ文を投げつけて、その回答を受け取るということができる。この回答がRで扱うデータ（データフレーム）であり、前回までで扱ってきた左右のテーブルになる。
　SQLでは、データベースが持っている大量のテーブルの中から複数のテーブルを指定して、さらにその中から必要なとこだけを寄せ集めて持ってくることが想定され、さらにさらに一つのテーブルに統合することができる。これがまさしくjoinで、この機能をRに実装したのがjoin関数になる。
　また、joinした結果から欠損値がある行を抽出したり、昇順で並べ替えたりなどなどしてみたけれど、これもSQLではクエリの中で実行できる。
　一方、Rでは予めローカルに保存したデータファイルを扱うことが多いと思われる。でも、データベースから直接データが取れたら楽に違いない。
　そういう時に使えるのが、RODBC::パッケージだ。

RODBC::パッケージ

　ODBC（Open DataBase Connectivity）というMicrosoftが提唱したデータベース接続がある。実はこないだまでOracleだと思っていた。これをRで使えるようにしたのがRODBC::だ。
　データベースのシステムには色々なものがある（らしい）。例えばOracleデータベース、Micorosoft SQL server、MySQLなどの、WEBやLANのどこかにサーバがあって大規模なデータベースを管理しているものから、MS Accessだとか、いっそExcelのシートで管理されているような小さなものまで色々ある。
　こういった色んなデータベースに対して共通的に接続可能なようにするものがODBCだ。ExcelのVBAマクロでも使えるので、たまにお世話になっている。
　テレワークが急速に進んでしまったので、自分のような非IT部署の社員でも、データベースから直接データを取ってこないと仕事が遅滞する時代になってしまった。できる人に出してとお願いしていては間に合わないし、できる人もそんなことばかりやりたいわけでもないだろうし。
　そういった必要に迫られての例以外には、そこにあるcsvファイルをそのまま一度read.csv()してからR上で必要な列を絞り込んで必要なデータだけにするのを、予め列を絞り込んだ状態でcsvから取り込むということができる。
　分析のために必要なステップ自体は変えないだけれど、その分担を変えることができるため、入手できるデータのサイズがクソデカなときにもお世話になったりする。

RODBCを使ってデータベースからデータを引っこ抜く

　大雑把に2種類ある。ODBCによって各種データベースに接続・切断するodbc*系関数と、クエリを投げたりするsql*系関数だ。
　read.*()なんかは指定したファイルをそのまま開いて全部読み込むのだけれど、ODBCではまずデータベースやファイルに”接続”を行って、その後にSQL文でクエリをデータベースに投げつけて、必要な部分だけを引っこ抜いてくる。そして終わったら接続を"切断"して、データベースを開放してあげる。（実際にSQL文でできるのは、データベースに新しい行を追加したり削除したり変更したりと色々なのだけど。）

接続と切断（odbc*）

#基本の接続
channel = odbcConnect(dsn, uid="userID", pwd="PW")

#Access(2007-)への接続
channel = odbcConnectAccess2007(access.file)

#Excel(2007-)への接続
channel = odbcConnectExcel2007(excel.file)

#切断
odbcClose(channel)
odbcCloseAll()

　channel = odbc*()のようにして、channelというオブジェクトを作成しておく。このオブジェクトはsql*で引数として渡せるのだけれど、他の関数でデータフレームやベクタを引数として与えていたのと同じ感じで、代わりにオブジェクトを渡すことになる。
　また、接続先の種類によって、これ以外にも色んなodbc*が用意されているし、データベースへの接続に特定のユーザーIDとパスワードが必要な場合は、uidとpwdを指定することができる。
　ちなみにExcelやAccessは2007前後で大きな変化があったので、別々に関数が用意されている。今は2007以降のものが多いと思われるので、上記のodbc*2007を使えば良い。
　一方、切断する方はとりあえずodbcCloseAll()しておけば間違いない。

sql*でデータベースに指示をする（クエリを投げる）

#テーブル一覧を取得する（Excelはシート一覧）
sqlTables(channel)

#テーブルから列フィールド情報を取得
sqlColumns(channel, table)

#DBからテーブルを読み出す
sqlFetch(channel, table) 

#DBにSQLを投げ結果を返す
sqlQuery(channel, query)

　SQL文でデータベースに対してクエリ（問合せ）を投げることで、データベースに指示を行うことができる。情報を取得する系だけ覚えておけば、データ分析に支障はない。（業務全体はともかく。）
　データベースにはいくつものテーブルが内包されているという話はしたけれど、そのテーブル一覧を取得するsqlTables()、選んだテーブルに含まれる列名（列フィールド）の一覧を取得するsqlColumns()がある。
　まずはこの2つを使って必要な情報があるテーブルを特定し、その上で、選んだテーブルをそのまま取り込むsqlFetch()や、選択的に引っこ抜くためのSQL文を乗せたsqlQuery()を使って、データベースからデータを引っこ抜いてくることになる。

　試しに、joinの練習用データを用意するのに使ったExcelファイルとODBC接続してみる。返ってくる結果が長たらしいが、%>% .$列名　とすれば特定の列だけ拾ってくることは可能だ。

#ODBC接続
> channel = odbcConnectExcel2007("test_join.xlsx", readOnly = T)
#テーブル一覧（シート一覧）の取得
> sqlTables(channel)
             TABLE_CAT TABLE_SCHEM TABLE_NAME
1 前略\\test_join.xlsx        <NA>     price$
2 前略\\test_join.xlsx        <NA>      sell$
3 前略\\test_join.xlsx        <NA>   shozoku$
4 前略\\test_join.xlsx        <NA>   tenzoku$
   TABLE_TYPE REMARKS
1 SYSTEM TABLE    <NA>
2 SYSTEM TABLE    <NA>
3 SYSTEM TABLE    <NA>
4 SYSTEM TABLE    <NA>

#列フィールドの取得
> sqlColumns(channel, "tenzoku")
             TABLE_CAT TABLE_SCHEM TABLE_NAME
1 前略\\test_join.xlsx        <NA>   tenzoku$
2 前略\\test_join.xlsx        <NA>   tenzoku$
3 前略\\test_join.xlsx        <NA>   tenzoku$
4 前略\\test_join.xlsx        <NA>   tenzoku$
5 前略\\test_join.xlsx        <NA>   tenzoku$
 COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS NUM_PREC_RADIX
1          NO         8    NUMBER          53             8             NA              2
2         SEX        12   VARCHAR         255           510             NA             NA
3         AGE         8    NUMBER          53             8             NA              2
4       ShoCo         8    NUMBER          53             8             NA              2
5        Kibo         8    NUMBER          53             8             NA              2
 NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
1        1    <NA>       <NA>             8               NA                NA                1
2        1    <NA>       <NA>            12               NA               510                2
3        1    <NA>       <NA>             8               NA                NA                3
4        1    <NA>       <NA>             8               NA                NA                4
5        1    <NA>       <NA>             8               NA                NA                5
 IS_NULLABLE ORDINAL
1         YES       1
2         YES       2
3         YES       3
4         YES       4
5         YES       5

#特定のテーブルを取得
> sqlFetch(channel, "tenzoku")
 NO SEX AGE ShoCo Kibo
1  1  男  26     1    2
2  2  男  28     3    6
3  3  女  27     4    3
4  4  男  25     5    1
5  5  女  24     5    4
6  6  女  29     3    1

#接続を切断
> odbcClose(channel)

　こうすることで、クソ重いExcelファイルを開かなくても中身を参照することができるのだが、元の作りがクソだとまあ何ともしがたい。
　それはともかく、基本の流れはこの通り。とりあえず必要なテーブルをsqlFetch()でそのままローカルに保存しておけば、あとは*_join()を使ってテーブルを色々joinできる。

　ただし、巨大なテーブルをそのまま読み込むのでは、せっかくODBC接続しているのにもったいないので、簡単なクエリを作ってsqlQuery()で投げてやりたい。

今日はここまで。