Categories
日常应用

linux学习笔记:JDK, rJava以及RJDBC

出于某些原因,最近申请了一个虚拟服务器(虽然有现成的R服务器,但是没有sudo总是各种不爽...),然后开始自己折腾linux...(向着码农方向前进,前进,前...)以下为小白笔记,抄在这里防止自己日后翻回来重新折腾一遍。高手请无视~

服务器是公司C3云上的一片,我选的ubuntu 12.04开始折腾。由于是远程server,所以只能shell操作 T_T 看不到ubuntu华丽丽的界面了。

装R倒是不麻烦,把镜像加一下就好了。编辑/etc/apt/sources.list,然后加上这句(看了一下我的ubuntu是precise版本的)。不加这句的后果就是...装了个上个世纪版本的R?

sudo vi /etc/apt/sources.list
deb http://cran.cnr.berkeley.edu/bin/linux/ubuntu precise/ #文件里加上这一行

然后就直接安...

sudo apt-get update
sudo apt-get install r-base

然后就开始折腾RODBC,给teradata跪了,安个teradata ODBC Driver死活不成功,最后请教同事决定投保RJDBC的怀抱。

JDBC基于Java,自然需要装JDK。网上搜了一下,貌似有个open jdk可用。

sudo apt-get install openjdk-7-jdk
sudo update-alternatives --config java #如果已经有多个jre,记得选一个...

然后保险起见,配一下java的路径。

sudo R CMD javareconf

如果看到下面这一段,那么就OK了。

JAVA_HOME        : /usr/lib/jvm/java-7-openjdk-amd64/jre
Java library path: $(JAVA_HOME)/lib/amd64/server
JNI cpp flags    : -I$(JAVA_HOME)/../include
JNI linker flags : -L$(JAVA_HOME)/lib/amd64/server -ljvm
Updating Java configuration in /usr/lib/R
Done.

然后就可以开始装R里面的那堆包了。R很神奇,必须sudo R之后install.packages()才有用...可怜我折腾了半天一直报错。
然后发现sudo的问题之后,顺利的装上了RJDBC.

sudo R
 install.packages("rJava")
 install.packages("RJDBC")

然后就是Teradata JDBC Driver了。这厮比ODBC好装的多...基本就是解压...然后就没有然后了。

都弄好了之后,就可以在R里面测试着玩了。

library(RJDBC)
jdbc.drv <- JDBC("com.teradata.jdbc.TeraDriver", c("/home/user_name/terajdbc4.jar", "/home/user_name/tdgssconfig.jar"))
 jdbc.conn <- dbConnect(jdbc.drv, "jdbc:teradata://xxxx", "xxx", "xxxx")

然后就是JDBC的一些相关函数了。

dbSendQuery(jdbc.conn ,"CREATE TABLE XXX AS XXXX WITH DATA;") #只执行不返回结果
dbGetQuery(jdbc.conn ,"sel 1 from xxx") #执行且返回data.frame(fetch完毕的)

然后就可以开始各种蹂躏了。

只是让我不爽的是,折腾了许久也没搞定发email...sendmail、mail什么的都不管用,莫非我SMTP没有配?继续google去了。

-----------------4.3更新----------------

原来C3和LDAP是直接集成的,所以别人的帐号也可以登入我的server。之后,一句命令可以加入sudo组:

sudo adduser username sudo

----------------4.4更新----------------

今天在同事的帮助下搞定了Rstudio Server版,瞬间爽翻了...

以前是ubuntu 12.04下有个东西装不上: libssl0.9.8。因为默认装好了1.0版本的。但是Rstudio现在的版本必须调用0.9.8版本...于是强行装旧版:

sudo wget http://mirrors.kernel.org/ubuntu/pool/universe/o/openssl098/libssl0.9.8_0.9.8o-7ubuntu3.1_amd64.deb
sudo gdebi libssl0.9.8_0.9.8o-7ubuntu3.1_amd64.deb

然后再去装一下Rstudio Server版就好了。

sudo gdebi rstudio-server-0.98.501-amd64.deb

然后为了调RJDBC,配置一下环境变量:

sudo vi /etc/profile

加入这么两行

export JAVA_HOME="/usr/lib/jvm/java-7-openjdk-amd64/jre"
export PATH=$PATH:$HOME/bin:$JAVA_HOME/bin

然后刷新一下。

source /etc/profile

这样就搞定啦。Rstudio监听在服务器的8787端口。可以直接/server:8787 登入。

Categories
日常应用

坑爹的RJDBC和Teradata

我实在是忍不住要抱怨了,哪有这样的半成品就给我们用的?RJDBC连TD是没有问题,可以读数据,但是dbWriteTable()写不回去!

dbWriteTable(jdbc.conn, name = "testdf", value =test_df)
Error in .verify.JDBC.result(s, "Unable to execute JDBC prepared statement ",  : 
  Unable to execute JDBC prepared statement INSERT INTO testdf VALUES(?,?) ([Teradata Database] 
[TeraJDBC 14.10.00.26] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.)

[2016.1.12 更新]

亲爱的同事写了一个很方便用的r函数来解决这个问题,大家猛戳 -> github

Categories
日常应用

ODBC(Teradata)和R连接常见问题

以前总结了一些R、SAS和TD彼此之间数据导入导出的办法,可是实际使用中还是会碰到各种各样的问题...问的人多了,就多少攒出一些FAQ放在了内部的wiki上。

还有一些问题估计大家都会遇到,所以也摘到blog上来好了。一开始用英文写的,简单翻译一下,不全翻译了。首先是通过RODBC连TD的一些常见问题,不知道是不是通用于其他ODBC driver...

  • Q: I cannot load RODBC on my own computer (not on R servers)... what can I do?
    A: Try to debug in these steps -1)  Check if you have installed the 64-bit version of Teradata ODBC driver on your 64-bit OS. If not, download and install these three packages here: http://downloads.teradata.com/download/connectivity/odbc-driver/windows(保证R、操作系统和ODBC driver位数一致,如果是64位的操作系统就装一套儿64位的吧...上面那个link是64位TD ODBC Driver的下载地址)。2)  Open your 64-bit ODBC administrator and configure the TD server DNS. (在新的ODBC中配置server地址) 

    3)  Close and re-open R again. (重启R,这步一定不能省)
    4)  If you still have problems, use R 2.*** instead of R 3.0+ (R 3.0以上版本有时候会莫名其妙报错,如是,改用2.15+版本吧。)

  • Q: How can I call R in the command line (to schedule a recurring job)? (怎么run batch job?)
    A: You can call RScript directly in your command line. If failure, you need to add R's path to your system environment variable - PATH. e.g. C:\Program Files\R\R-2.15.2\bin (设一下环境变量)
    Pay attention to its parameters. Also, you can save the log. A sample command is as follows - (命令行类似如下)

    Rscript --no-save --no-restore --verbose "/home/Liyun.Chen/R/recurring/recurring_dashboard_by_week.r" > outputFile.log 2>&1

    (记得存个log)

暂时就是这些...做个存档好了。

Categories
日常应用

从R里面底层操纵Excel/xlsx(自动化报告福音)

好吧,我在eBay折腾的最多的就是生成自动化报告时候各种软件之间的相互调用,什么R啊,SAS啊,Teradata啊,Excel啊,Python啊,反正基本都有机会相互调用一下。每到此时我就深深感慨选择一个library丰富的工具是多么的重要!You could hardly expect what you colleagues are handy with!(P.s. 不要跟我提VBA这种逆天存在的东西。有哪个时间研究它你学点啥别的不好...)

今天忍无可忍+心情大好的折腾了一下R和excel。这个不是简单的从R里面读写excel数据,而是真心用R去操纵excel里面的单元格(cell),除了读写数据之外还要定义样式什么的。excel作为一个奇葩的软件,you may never expect where people would paste data to! 然后他们再自定义一堆样式(我恨这种点点鼠标就能改的东西,你丫又不是Photoshop...)。

但是没办法,人家定义好的“高端洋气”的报表姿态你不能轻易动啊。只能乖乖的往里面paste数据。这件事虽说一次两次手动也就罢了,三五次真的是要疯掉的。anyway,万事总有解决的途径...

很久以前从Yixuan 的博客上得知有xlsx这么个包,当时只记得这东西可以读写xlsx...直到后面折腾了一下才知道这货底层居然调用的是java的xlsx API,也就是说不用写Java也可以操作xlsx了,yeah!

为了生成excel格式的自动化报告(不要问我为啥不用knitr,不用***,说起来都是泪呀!),我主要需要解决的就是:

  • 读取原有xlsx文件,保持格式、附加新格式。
  • 在相应的位置粘进去新的数据。(当然如果只有这么一个需求可以通过ODBC来做...)

第一个倒是满简单的,就是较之yixuan代码里面的createWorkbook(),改成loadWorkbook()就可以了。然后就是找到相应的sheet,这个也满简单的,一行getSheets搞定。

然后第二步建议不要去操作cell(太没效率了),直接操作cellblock。CellBlock()可以用来定义一个新的CellBlock,然后灵活运用CB.setBorder()和CB.setColData()就可以先增加边框、然后一列列填充数据。这里使用按列填充数据主要是因为R里面的Data Frame是一列一个数据格式的,一下子把一块儿都paste到excel的cellblock里面的话,会报错...BTW为了定义边框的样式,需要用到Border()。类似的还可以定义Fill和Font这些。

同上,最好不要直接用addDataFrame()来直接贴数据...格式不能覆盖。如果是要在一个新的sheet上贴数据,那么就write.xlsx(sheetName="newsheet",append=T)好了。不需要通过上述底层的API折腾了。

最后还有一个比较有用的函数,autoSizeColumn()可以用来自动调整列宽。全鼓捣完之后saveWorkbook()保存就可以啦。

最后的最后,一个珍贵的建议——都在R里面把数据整理好再去想输出到excel里面(什么reshape2啊,data.table啊,plyr啊,该上的一起上啊!),千万别手贱在excel里面改一点点小东西...每一次都手动改一下下你的时间就被白白浪费了好几分钟!珍爱生命,远离excel...

附上一段我最后搞定自动化报告的代码:

library("xlsx")
test_template <- loadWorkbook("template.xlsx") #读入template.xlsx文件。定义好各种乱七八糟的格式的。
design_tab <- getSheets(test_template)[["design"]] #转到design这个sheet。
data_block <- CellBlock(design_tab, 5,5,nrow(mydata),ncol(mydata)) #准备贴数据的方块,我这里从第5行第5列开始贴。
border <-  Border(color="black", position=c("LEFT", "RIGHT"),
pen=c("BORDER_THIN", "BORDER_THIN")) #定义边框样式——左右黑色细直线。
for (i in 1:ncol(mydata))
{
CB.setBorder(data_block, border,colIndex = i,rowIndex=1:nrow(onetime_design_tab)) #给每一列都贴上边框
CB.setColData(data_block, mydata[,i], i, rowOffset=0, showNA=F, colStyle=NULL)#给每一列贴数据
}
border_bottom <-  Border(color="black", position=c("LEFT", "RIGHT","BOTTOM"),pen="BORDER_THIN") #定义结尾行样式——底端黑细直线
data_block_bottom <- CellBlock(design_tab, 5+nrow(mydata),5,1,ncol(mydata)) #选择最后一行
CB.setBorder( data_block_bottom, border_bottom, 1, 1:ncol(onetime_design_tab)) #定义最后一行格式
autoSizeColumn(design_tab, 5:(5+ncol(onetime_design_tab)))#调整列宽
saveWorkbook(test_template, file=output_xlsx_name) #保存
##add row data
write.xlsx(rawdata, file=output_xlsx_name, sheetName="raw_data",append=T,row.names=F) #直接贴原始数据,无格式
###add queries
R_file <- readLines(R_file_name, n=-1) #直接贴R代码
SQL_file <- readLines(SQL_file_name, n=-1)
write.xlsx(SQL_file, file=output_xlsx_name, sheetName="query_SQL",append=T,row.names=F) #直接贴代码到新的sheet中
write.xlsx(R_file, file=output_xlsx_name, sheetName="query_R",append=T,row.names=F)

 


Categories
日常应用

无知的比较:R和Teradata SQL(附赠TD经验几枚)

今年夏天的时候,刚刚开始被SQL虐,写了一篇很无知且更多是吐槽意味的blog post: 关于R的若干SQL等价问题。当时被若干朋友批评,我还浑然不觉个中精要。现在用Teradata也有半年多的时间了,越来越习惯了SQL的表述方式,也越来越体会到Teradata作为一个强大的数据仓库系统,是有多么的伟大...这感觉,就是只玩过几个G数据的乡下人进城,猛然看到各路英雄都是动辄几个T的数据,只能暂时以原来落后的思维方式、勉强挥舞着新型工具...好在个性不是特别愚钝,终究还是可以慢慢地领悟到T级数据的奥妙之处,终究用着新武器也越来越顺手了。

这一段时间,也充分证明了我是master in economics而绝对不是 in cs。数据库系统的原理终究学的不深——我哪儿知道MySQL的SQL和Teradata的SQL差了那么多呀...后来慢慢的去听同事传授TD使用经验,慢慢的去看老板传过来的代码,慢慢的一次次处理掉 no more spool space的错误和一次次接到SQL语句效率低强制退出的警告信之后,才逐渐地越来越了解TD的原理和脾气。工欲善其事,必先利其器,这些都是沉重的学费。

所以各位如果没有看过那篇「无知者无畏」状post的,就不要看了。直接接受我诚挚的道歉然后看下文吧。Teradata下简称TD。绝非专业知识,只是个人有限的了解,不对之处请及时批评。

有次跟同事聊,问他们为什么不在本机上装个TD测试用...然后被狠狠鄙视了一番——TD没有单机版!天生就是架在云上的。这东西还真是个原生的分布式数据仓库。

TD和oracle的关系也比较简单:一个是数据仓库,一个是数据库,功能设计什么的压根就不一样。这么说吧,oracle支撑的是ebay的网站运行,所以必然涉及大量的查询、插入、删除等请求。更麻烦的是,以ebay的访问量,这些请求都是同时过来的,这就要求系统并发性要好一点(专业人士可以绕道了,我只是浅薄的知道一点东西...)。体验过12306买火车票排队的大家,想必都知道这个系统并发起来的厉害。ebay若是也来个排队,消费者还不疯掉...

为了应对这样的任务,oracle的数据库设计自然是要按那「三大范式」来。这个就不多说了,再说就暴露了...

TD则是把oracle的数据定期地导出来存着,所以除了简单的复制数据之外,还要对数据进行一定程度的清理和整理,并不完全是最最原始的数据。然后到了食物链上端数据分析师手里,面对的数据很多都是已经弄的很整齐的了。说是食物链上端,只是因为这大概是分工中需要用到原始数据的最后一拨人,且这拨人用到的最多的就是查询(甚至是整表查询)和计算,所以我们写SQL的时候更多是考虑到这些需求,利用TD在这方面的性能优势——我已经很少在SAS或者R里面进行数据整理的工作了,性能跟TD完全不是一个量级的。

下面是TD使用的若干经验,不过这东西只有自己碰壁了才知道个中真滋味,我就是缩短一下解决问题的进程,不用太折腾到处搜来搜去。

No more spool space。当你的SQL没有语法错误,那么最常见的运行不下去的情况就是 no more spool space了,这大概是每个用TD的不管新鸟老鸟都会经历的痛苦历程。这个错误就像R里面报"cannot allocate a vector of size ***",或者你玩游戏正high的时候系统告诉你内存不足。解决的思路就是"空间换时间",就是看你具体怎么换了。

1. 多表join查询的时候,就要看这些表是怎么merge的——TD会去算是一大一小join,还是两个大表join。前者TD会复制小表到每个大表的"节点"上(大表肯定要分块存起来嘛),所以可以事先加collect statistics on *** column ***。后者就要费点脑子了,争取两个表的排序(PI)一致,这样TD join的时候就不需要对两个表都重新排列了是不是(merge join)?每一次重排都会占掉大量的临时空间呢。再者,查询结果储存到另外的永久或者临时表里面,就要注意primary index(简称PI)的选择,不要让TD再把查询结果重排...

2. 除了看primary index,有时候还要去注意partition by。有些已经建好的超级长的表需要去看是怎么真正"分块"存储的。对于partition by 的字段设定一个where条件,会让TD很快的知道你要查询和join的是哪些部分,大大缩短范围。一般说来,最常见的partition by就是时间了,缩短一个时间范围也不失为良策嘛。

3. 擅用cast()可以避免很多跟数据类型有关的错误,这个就不赘述了。

4. No space on ***说明没有永久表的存储空间了,这个就得去删过于古老的表和去要新的空间了。

5. 每段SQL不要太长,join不宜太多。熟悉TD的脾气之后,就张弛有度了,擅用临时表。

6. 多用group by少用distinct。

7. 最后终极野蛮办法,如果实在是没法两个大表join又没有partition by的话...手动按PI拆其中某个表吧。

----例行碎碎念----
那些在LinkedIn上endorse我R的朋友们,我真心感觉承受不起呀!至今依旧觉得我的R很烂,代码只停留在"可运行"的水平,效率大都很糟糕,基本就是折磨CPU的...哎,非科班出身终究是有莫大的差距呀。