使用RJDBC连接R和Netezza [英] Connect R and Netezza using RJDBC
问题描述
我正在尝试使用JDBC驱动程序将R与Netezza连接.
I´m trying to connect R with Netezza using the JDBC driver.
我设法与数据库成功连接，但结果不正确.
I manage to connect succesfully with the database, but the results are not corretc.
# Here are the connection details
library(RJDBC)
drv <- JDBC(driverClass="org.netezza.Driver", classPath = "C://JDBC//nzjdbc.jar", "'")
con <- dbConnect(drv, "jdbc:netezza://10.206.0.66:5480//DBASE", "USER", "PASS")
# > con
# An object of class "JDBCConnection"
# Slot "jc":
# [1] "Java-Object{org.netezza.sql.NzConnection@bce3d7}"
# Slot "identifier.quote":
# [1] "'"
res <- dbSendQuery(con, "SELECT * FROM DBASE.MARBEL.DATOS limit 10000;")
res <- fetch(res, n = -1)
问题在于，这些字段是使用垂直"变量而不是表的列作为列表返回的！
The problem is that the fields are resturned as list with "vertical" variables instead of columns of a table!
head(res)
SUBSCRIPTION_ID
1 245206318120314
2 235109338101206
3 238463669110624
4 214177015090830
5 212403495090830
6 13874138618090824
SUB_ACCOUNT_ID
1 MV_SUBCTA_45206318_20120316
2 MV_SUBCTA_35109338_20101207
3 MV_SUBCTA_38463669_20110627
4 MV_SUBCTA_45223848_20120316
5 MV_SUBCTA_12403495_20081224
6 MV_SUBCTA_18932919_20091012
ACCOUNT_ID
1 MV_CTA_44123765_20120316
2 MV_CTA_35213277_20101207
3 MV_CTA_37772612_20110627
4 MV_CTA_14217213_20090330
5 MV_CTA_12477560_20081224
6 MV_CTA_18758944_20091012
ACCESS_METHOD_ID
1 1167391804
2 1159354610
3 2966407995
4 1153360304
5 1131960835
6 3874138618
有什么办法解决这个问题吗?我有一个有效的ODBC连接，但是我宁愿使用JDBC.
Any idea how to solve this?? I have a working ODBC connection, but i´d rather use JDBC.
推荐答案
我将您的输出一直滚动到最右边，看起来您列中的字符串非常宽(它们是CHAR而不是VARCHAR?)，所以结果不适合R控制台的宽度.因此，R以这种方式显示它们.
I scrolled your output all the way to the right and it looks like the strings in your columns are very wide (are they CHAR instead of VARCHAR?), so the result does not fit the width of R console. Hence R displays them that way.
因此，请尝试在查询中进行修剪
So try to either trim them in your query
select rtrim(SUB_ACCOUNT_ID), ...
或在R中:
require('stringr')
res$SUB_ACCOUNT_ID <- str_trim(res$SUB_ACCOUNT_ID)
这篇关于使用RJDBC连接R和Netezza的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
