R自动化生成报表里踩过的坑(一):R连Oracle

作者: dingdingxia | 来源:发表于2017-01-27 15:47 被阅读929次

    我们组不久前申请到了一台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 <- RODBC::odbcConnect("tuominku",uid="uid",pwd="pwd") 
    

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

    data<-RODBC::sqlQuery(myconn, "select * from table")
    

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

    Sys.setenv(NLS_LANG="Simplified Chinese_china.AL32UTF8")
    myconn <- RODBC::odbcConnect("tuominku",uid="uid",pwd="pwd",DBMSencoding="utf8", believeNRows=FALSE)
    

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

    把R的encoding设置成utf8

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

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

    1. 在sql语句中使用R中的对象和函数:paste函数。
      最典型的应用是配合时间函数。如我做的日报,每天早上读取前一天的数据,手动去改时间就太麻烦了,也做不到完全自动化。本质上来说,sqlQuery函数中查询的就是引号里的一段文本,就可以用paste函数来生成,配合R里的Sys.Date函数。具体语法见下。还有一种方法是在sql里直接把时间变成系统时间的函数。
    data<-RODBC::sqlQuery(myconn, 
    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="")
    
    1. 数字以文本格式保存
      像会员号之类的一串数字,默认是以num格式读入的,如果很长的话就显示为科学计数法了。一个方法是设置科学计数法到很长的位数才使用:
    options(scipen=200)
    

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

    data<-RODBC::sqlQuery(myconn, "select * from table", as.is=T)
    
    1. 把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的限制吧,我没有仔细研究过。

    1. 通过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。

    相关文章

      网友评论

      本文标题:R自动化生成报表里踩过的坑(一):R连Oracle

      本文链接:https://www.haomeiwen.com/subject/ffovbttx.html