Connect R and Netezza using RJDBC

问题:

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  

Any idea how to solve this?? I have a working ODBC connection, but i´d rather use JDBC.

回答1:

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), ...

or in R:

require('stringr')
res$SUB_ACCOUNT_ID <- str_trim(res$SUB_ACCOUNT_ID)

回答2:

Based on Alex answer i wrote this function to use rtrim in all variables.

query_nzz <- function(con, select="select * ", from="", where = "", limit = " 10000; "){
  options(scipen=666)

  # Get variable Names
  query_names = paste(select, " from ",from, where, sep = " ")
  names <- dbGetQuery(con, paste(query_names,"limit 1;", sep= " "))
  names <- names(names)

  # Trim spaces
  select <- paste0("trim(",names,") as ", names, collapse = ",")

  query = paste0("select ", select, " from ", from, where, " limit ",deparse(limit),";")
  data = dbGetQuery(con, query)
  data
}

Function usage

  dt <- query_nzz(
    con,
    select = "select * ",
    from = "DATABASE.TABLENAME",
    where = "",
    limit = 100000
  )