oracle 作自动化报表,R自动化生成报表里踩过的坑（一）：R连Oracle

我们组不久前申请到了一台windows服务器，除了装了系统什么都没有，于是自己配置了一下R连Oracle，以及JAVA，因为不少R包要用到JAVA。此过程对我来说已经是颇为繁琐复杂了，还要配置环境变量这种我从来没听过的东西，总之最后不得已求助了开发哥哥才算配置成功了。这个过程略去不表，网上搜搜也是有不少教程的。

R自动化生成报表的思路是这样的，先用R连Oracle，从Oracle数据库里把前一天相关的所有数据都取出来，不做任何汇总操作。然后在R里处理数据，主要用data.table和dplyr两个包，做一些筛选和汇总。数据处理完了以后，在一个rmarkdown文件里用recharts包画图(如何使用recharts可以参考我前一篇博客)，用DT包做表格，生成最后的报表。这样每天手动运行了一段时间后，用taskscheduleR包设置任务计划，每天定时自动运行。

虽然从Oracle里读取所有数据的时间较长，每天大约有70w行数据，但之后在R里进行数据处理和查询就比较方便快速。当然也可以在Oracle里写sql做汇总，然后用R读汇总数据，但因为我现在读的这个表索引做的不够好，在Oracle里做汇总本身也要花相当长的时间，比data.table要慢上许多。总之这个做法只是根据我的个人偏好和数据情况决定的。

好了，下面就一步步来说吧。

第一步，R连Oracle。

用的包是RODBC，64位windows，64位R 3.3.2，Rstudio 1.0.136，需要设置数据源(ODBC)。可以在网上搜一下，挺多教程的。如在这里配置了tuominku这个数据源。

ODBC数据源设置

RODBC包的基本使用方法是，先创建连接，tuominku就是前面创建的ODBC源，uid就是你访问这个数据库的用户名，pwd是对应的密码，都要放在引号里。

myconn

然后查询，用sqlQuery，把查询结果保存赋给data。

data

这里就有了第一个坑，就是中文乱码。 PL/SQL里查询中文正常显示，但是从R里读的就是中文乱码。在网上搜了一圈，都说要设置DBMSencoding为utf8，还有把system language设置成和oracle里一样的。那就这么设置吧

Sys.setenv(NLS_LANG="Simplified Chinese_china.AL32UTF8")

myconn

结果设置了以后还是不对，这就很郁闷了，R里的encoding也是utf-8，那怎么还是不对呢。终于发现(现在也忘了是怎么发现的了)，utf8和utf-8是两个编码，具体有什么区别我也不清楚，但是在R里把encoding设置成utf8之后，中文就正常显示啦。

把R的encoding设置成utf8

另外提一句，上一段代码中的believeNRows=FALSE，虽然我没太搞清楚是什么意思，但它着实避免了一些报错，也没看到加上以后的坏处，所以就一直放着了。

这里就一起说下自己用RODBC的一些小心得。

在sql语句中使用R中的对象和函数：paste函数。

最典型的应用是配合时间函数。如我做的日报，每天早上读取前一天的数据，手动去改时间就太麻烦了，也做不到完全自动化。本质上来说，sqlQuery函数中查询的就是引号里的一段文本，就可以用paste函数来生成，配合R里的Sys.Date函数。具体语法见下。还有一种方法是在sql里直接把时间变成系统时间的函数。

data

paste("select * from table

where create_time between between to_date('", format(Sys.Date()-1,'%Y%m%d'), " 00:00:00', 'yyyymmdd hh24:mi:ss')

and to_date('", format(Sys.Date()-1,'%Y%m%d')," 23:59:59', 'yyyymmdd hh24:mi:ss')",

sep="")

数字以文本格式保存

像会员号之类的一串数字，默认是以num格式读入的，如果很长的话就显示为科学计数法了。一个方法是设置科学计数法到很长的位数才使用：

options(scipen=200)

还有一个方法更为直接一点，在查询的时候就以文本保存，只要在sqlQuery中加入as.is=T即可，如：

data

把R中的dataframe保存到Oracle数据库的临时表中：sqlSave

有时需要把R中清洗过的数据和Oracle中某些表关联，但这时候就不能像第一条中提到的用paste函数了，可以先将R中的dataframe存到数据库的临时表中，再用sql进行关联查询。

sqlSave(myconn, Rdataframe, tablename='TEMP1', rownames=FALSE, addPK=FALSE')

这里的Rdataframe就是在R中的dataframe，tablename是Oracle临时表的名字，切记一定要大写，否则无法查询，同时Rdataframe的column name也必须是大写。这可能是Oracle的限制吧，我没有仔细研究过。

通过R查询Oracle并将结果直接保存为临时表：sqlCopy

对于一些复杂的报表需求，有时候需要创建临时表，减少同一段查询的次数，这时候如果先用sqlQuery将查询结果导出，再用sqlSave保存到Oracle临时表中，一读一存，写起来有点繁琐，这时就可以用sqlCopy直接将查询保存在临时表中，作用类似于create table。

sqlCopy(myconn, 'select * from table;', destination='TEST', verbose =T, errors = T)

但这里其实有一个bug，仍旧是字符型数字被当做科学计数法保存的问题，如一个18位的纯数字会员号，在表里是字符型的，如果直接通过sql查询也是字符型，但在通过sqlCopy则会变成数字型，即使定义这个字段的格式是文本型也不行。猜测这是因为sqlCopy的操作过程其实本质上仍是sqlQuery+sqlSave，而它确不提供第二条里提到的as.is选项。所以我现在不太使用sqlCopy，如果是要做create table as select..的操作，就直接在PL/SQL中先运行，或是在查询时前面加一个文本，如查询select "m"||customerid from table，之后再去掉m，最后一种方法就是用sqlQuery+sqlSave。

以上就是一些RODBC包使用中碰到过的问题和经验，还在不断地摸索中，欢迎交流~

下一篇讲taskscheduleR。