0

I have created a couples of tables (data frames) in R that I need to upload to Cloudera Impala, I am using DBI package to connect with Impala. So I have for example:

df<-data.frame(x)

How do I insert df into Impala as a table?

I have seen that this is possible to do using dbSendUpdate(), so I have done something like this:

dbSendUpdate(dbh,paste0("Create Table db.df as select * from ",db))

but no luck.

Any idea on how to handle this issue with this function or any other function I could use for this?

L.Gut
  • 1
  • 1

1 Answers1

0

I think you should instead use the function dbCreateTable or dbWriteTable() from the DBI package.

In your case, you can send df in the following:

data <- DBI::dbCreateTable(con, "impala_new_df", df)

Where con is your database connection, "impala_new_df" is the name of the new table desired in Impala, and df is the dataframe you are writing from. You'll obviously need read/write access on your instance of Impala to do so.

I believe the dbSendUpdate function is from the package RJDBC. Which will only work if your connection is jdbc, not odbc. Also, I don't believe your use of paste0 is going to behave the way you want it to.

Here's a useful reference on databases in R: https://db.rstudio.com/odbc/

Update

Since you ARE using RJDBC, I believe the following should work:

dbSendUpdate(con, "Create Table ?", df)

Similar to the solution here: https://stackoverflow.com/a/35407579/6535514

If that doesn't work, I'd start looking into the package implyr for other ways around this

Dave Gruenewald
  • 5,329
  • 1
  • 23
  • 35
  • Yes you are right its from RJDBC package not DBI and I am using a jdbc connection. I have tried something like dbWriteTable(dbh,"db.mtcars"mtcars) but I get a syntax error from t – L.Gut Dec 10 '18 at 20:07
  • Yes, you are right its from RJDBC package not DBI and I am using a jdbc connection. I have tried something like dbWriteTable(dbh,"db.mtcars"mtcars) but I get a syntax error from the sql statement telling me that the use of DOUBLE PRECISION is not accepted. Impala does not accept the word Precision while creating a table, however dbWriteTable uses DOUBLE PRECISION as the default type. Is there any way to change this? – L.Gut Dec 10 '18 at 20:26