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

2021年1月26日 18:20

要約

・sqlQuery()でSQL文のクエリを投げる
・SELECT ”列名" FROM "テーブル名" WHERE 条件式

簡単なSQL文を作ってクエリを投げよう

基本は下記のようになる。

SELECT "列名1", "列名2" FROM "テーブル名"

　SELECT列名 FROMテーブルなので、まさしくテーブルから列を選ぶ、と割と自然文チックな書き方になる。dplyr::select()も同じように列を選んでくる関数なので覚えやすいかもしれない。
　列名の間はコンマ区切りで、SELECT以下とFROM以下の間には空白が入っている。
　sqlQuery(channel, Query)のQuery部分にこのSQL文を入れてあげれば良いのだけど、()内に長々書くのも可読性が落ちるので、channelと同じであらかじめSQL文を変数に代入しておいた。

> con = odbcConnectExcel2007("test_join.xlsx", readOnly = T)
> strQ1 <- 'SELECT "NO", "Kibo" FROM "tenzoku"'
> sqlQuery(con, strQ1)
[1] "42S02 -1305 [Microsoft][ODBC Excel Driver] オブジェクト 'tenzoku' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'tenzoku' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT \"NO\", \"Kibo\" FROM \"tenzoku\"'"

　……あれ？sqlColumns()ではテーブル名を"tenzoku"そのままでも受け付けてくれたのに？？
　見直してみると、sqlTables()で取得できるテーブル名（$TABLE_NAME）では"tenzoku$"になっている。ここを修正するとうまくいった。

> strQ1 <- 'SELECT "NO", "Kibo" FROM "tenzoku$"'
> sqlQuery(con, strQ1)
 NO Kibo
1  1    2
2  2    6
3  3    3
4  4    1
5  5    4
6  6    1

　類似の関数群くらい統一的に扱えるようにしておいてほしい。小さな親切余計なお世話というやつである。

　ちなみに、SQL文のクォーテーションのダブルとシングルを反対に使うとクエリがうっかり通ったり通らなかったりする。テーブルの指定は[]で囲っても良いことが分かる。
　そういうわけで、SQL文内ではダブルクォーテーションを使う必要があるので、変数に代入する際には外側をシングルクォーテーションで囲う必要がある。まあ理屈上その通りなんだけど。
　ついでに先のエラーメッセージのせい……おかげでエスケープ文字使って書けば良いことを思い出した。そりゃそうか。

> strQ2 <- "SELECT 'NO','Kibo' FROM 'tenzoku$'"
> sqlQuery(con, strQ2)
[1] "HY000 -3003 [Microsoft][ODBC Excel Driver] クエリの構文エラーです。クエリの句が不完全です。"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT 'NO','Kibo' FROM 'tenzoku$''" 

> strQ3 <- "SELECT 'NO','Kibo' FROM [tenzoku$]'"
> sqlQuery(con, strQ3)
 Expr1000 Expr1001
1       NO     Kibo
2       NO     Kibo
3       NO     Kibo
4       NO     Kibo
5       NO     Kibo
6       NO     Kibo

> strQ4 <- "SELECT \"NO\", \"Kibo\" FROM \"tenzoku$\""
> sqlQuery(con, strQ4)
 NO Kibo
1  1    2
2  2    6
3  3    3
4  4    1
5  5    4
6  6    1

WHEREを使ってさらに絞りこむ

　WHERE 条件式、という形でデータを絞り込むことができる。dplyr::filter()と似たような感じだ。こうやって条件式を使って絞り込むことで、無駄に大きなデータファイルを読む必要がなくなる。
　SQL文はRに限らず色んな言語で共通的に使えるので、書き方に困ったら、sqlQuery()について調べるよりも、SQL文の解説そのものを調べた方が早い。

> strQ5 <- "SELECT \"NO\", \"Kibo\" FROM \"tenzoku$\"
　　　　　　 WHERE \"NO\">=3 AND \"Kibo\"=1"
> sqlQuery(con, strQ5)
 NO Kibo
1  4    1
2  6    1

データクリーニングの設計

　ここまでやると、Rで*_join()なんかわざわざ使わなくても、最初からSQL文で書けばいいんじゃね？って思う。誰だってそう思う。ガンガン覚えて行きたい人はSQL文も覚えればいい。
　ただ、解析前にちまちまクリーニングしないといけないようなデータについては、一度sqlQuery()でローカルに落としてからRであれこれやるしかないし、するとjoinするのも*_join()を使うことになる。
　データ取得からクリーニングを経て分析しやすい形にデータを整理して初めてデータ分析ができる。クリーニングと一口に言っても、中身は文字列操作やテーブル変換、結合などなど多岐にわたる。
　それらについてどのタイミングで何を行うか、それを考えるのがまさしくデータクリーニングの設計だし、データ分析の設計の重要な一部になる。

以上。