R语言连接TeradataDB

第一步：从Teradata官方网站下载JDBC driver

第二步：把Driver放在一个目录下（我放在D:\R\JDBC）

第三步：为R安装RJDBC package     install.packages("RJDBC")

安装成功后，可以键入如下代码进行连接Teradata：

<span class="GJWPQFQDN4 ace_keyword" style="word-wrap: break-word; white-space: pre; color: blue;">> </span><span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">library(RJDBC)</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"><br style="word-wrap: break-word;" /></span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"><span class="GJWPQFQDN4 ace_keyword" style="word-wrap: break-word; white-space: pre;">> </span>jdbc.drv<-JDBC("com.teradata.jdbc.TeraDriver", c("C:/R/JDBC/terajdbc4.jar", "C:/R/JDBC/tdgssconfig.jar"))</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">
<span class="GJWPQFQDN4 ace_keyword" style="word-wrap: break-word; white-space: pre;">> </span>jdbc.conn<-dbConnect(jdbc.drv, "jdbc:teradata://tdp.vip.XXX.com", "userID", "passWD") #打开数据库连接</span>

<span style="color:#0000ff;">
</span>

<span style="color:#0000ff;">#此处注意 </span><span style="color: blue; font-size: 10pt; line-height: 1.2;">userID、</span><span style="color: blue; font-size: 10pt; line-height: 1.2;">passWD填写的是自己的数据库用户名和密码；jdbc:teradata://数据库的ip地址/TMODE=,CHARSET=,CLIENT_CHARSET=,LOB_SUPPORT=,DATABASE=（以及其他的相关设置）</span>

<span style="color: blue; font-size: 10pt; line-height: 1.2;">
</span>

<span style="color: blue; font-size: 10pt; line-height: 1.2;">（1）查询当前的系统时间、用户以及会话</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"><br style="word-wrap: break-word;" /></span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">dbGetQuery(jdbc.conn, "select current_date, user, session")</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">
</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"></span><div><img src="file:///C:\Users\hua\AppData\Roaming\Tencent\Users\840054314\QQ\WinTemp\RichOle\IA~6)78]Z_GOX]{LEAB%5JH.png" alt="" /></div><img src="https://img-blog.csdn.net/20160826144517641?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" />

输出结果的数据类型为data.frame

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">
（2）进行简单的数据查询</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"><img src="https://img-blog.csdn.net/20160826144939320?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" />
</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"><br style="word-wrap: break-word;" /></span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"><span class="GJWPQFQDN4 ace_keyword" style="word-wrap: break-word; white-space: pre;">> </span>dbDisconnect(jdbc.conn) #关闭连接</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">
</span><span style="word-wrap: break-word; color: blue; font-size: 10pt; line-height: 1.2;">[1] TRUE</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"><span class="GJWPQFQDN4 ace_keyword" style="word-wrap: break-word; white-space: pre;"><br style="word-wrap: break-word;" /></span></span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;"><span class="GJWPQFQDN4 ace_keyword" style="word-wrap: break-word; white-space: pre;">> </span>dbGetQuery(jdbc.conn, "select current_date, user, session")
<span class="GJWPQFQDF4" style="word-wrap: break-word;"><br style="word-wrap: break-word;" /></span></span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">Error in .verify.JDBC.result(s, "Unable to execute JDBC statement ", statement) : 
  Unable to execute JDBC statement select current_date, user, session ([Teradata JDBC Driver] [TeraJDBC 13.10.00.18] [Error 1095] [SQLState HY000] Cannot call a method on closed connection)</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">
</span>

<span class="GJWPQFQDB4 ace_keyword" style="word-wrap: break-word; color: blue;">
</span>