1

I encounter this problem: the DB call only creates a table, it has problem of retrieving JDBC result set.

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for Calls: dbGetQuery ... dbSendQuery -> dbSendQuery -> .local -> .verify.JDBC.result Execution halted

    options( java.parameters = "-Xmx32g" )
    library(rJava)
    library(RJDBC)
    drv <- JDBC("org.apache.hive.jdbc.HiveDriver", "/tmp/r_jars/hive-jdbc.jar")
    for(jar in list.files('/tmp/r_jars/')){
        .jaddClassPath(paste("/tmp/r_jars/",jar,sep=""))
    }

    conn <- dbConnect(drv, "jdbc:hive2://10.40.51.75:10000/default", "myusername", "mypassword")

    createSCOREDDL_query <- "CREATE EXTERNAL TABLE hiveschema.mytable (
       myvariables
    )
    ROW FORMAT SERDE
    'com.bizo.hive.serde.csv.CSVSerde'
     STORED AS INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
     OUTPUTFORMAT
     'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
     LOCATION
    's3://mybucket/myschema/'"

    dbGetQuery(conn, createSCOREDDL_query)
    dbDisconnect(conn)
Mary Li
  • 11
  • 2
  • The table was created, and proper data was allocated from the S3 buckets, however this process always halts and returns error. So I cannot create multiple tables in one R script, instead I have to create one R script for each table I need to create. – Mary Li Feb 22 '17 at 22:20

2 Answers2

2

Instead of dbGetQuery can you try using dbSendUpdate? I was having similar issues and making this switch solved the problem.

KalC
  • 1,530
  • 3
  • 22
  • 33
0

I tried with the following code as suggested by @KaIC and it worked:

dbSendUpdate(conn, "CREATE EXTERNAL TABLE hiveschema.mytable ( col_A  string, col_B  string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE")

For multiple tables, you can create a list or loop within a function and use an apply() construct to apply it to the entire loop.

ML_Passion
  • 1,031
  • 3
  • 15
  • 33