Problems creating and populating tables to HANA RODBC R

Question

I am trying to write data to a table in a particular schema in HANA (SPS 11) using RODBC package in R and am having problems that I hope someone can help with.

I am using the sqlSave to create the file and write to it, using below command, but getting weird results.

res <- sqlSave(ch, dim_product_master_test, tablename = table.for.save, rownames = FALSE, verbose = TRUE)

Query: CREATE TABLE MYSCHEMA."DIM_PRODUCTSX" ("ProdSrcMonth" varchar(255), "Category" varchar(255), "SubCategory" varchar(255), "Brand" varchar(255), "Material" INTEGER, "Product" varchar(255), "EAN" varchar(255) .... etc)

I am getting the error:

Error in sqlColumns(channel, tablename) : ‘MYSCHEMA."DIM_PRODUCTSX"’: table not found on channel

However, the table is being created, then it can't seem to add the data or find it.

I tried with different quotes scheme (including around the schema name) but same result.

Query: CREATE TABLE "MYSCHEMA"."DIM_PRODUCTSY" ("ProdSrcMonth" varchar(255), "Category" varchar(255), "SubCategory" varchar(255), "Brand" varchar(255), "Material" INTEGER, "Product" varchar(255), "EAN" varchar(255) ... etc

Error in sqlColumns(channel, tablename) : ‘"MYSCHEMA"."DIM_PRODUCTSY"’: table not found on channel

Tried quoting both, but no difference. Again, creates table but cannot update it.

If I just throw the dataframe at sqlSave, it happily creates the table and adds the data but I need more control that that.

Also, anyone know how to create column store tables? seems to default to row store.

Thanks in advance.

In order to create column store tables you need to specify that you want this in your create table statement: CREATE COLUMN TABLE xyz ... — Lars Br.
Yes, good point. However, I am try use RODBC SQLSave and the SQL above is what it generates, not what I provided. — ingrid
Thanks @lars for the answer. However, I am try use RODBC SQLSave and the SQL above is what it generates, not what I provided. So, guess you are saying I need to execute the create statements myself to specify column store. I did pre-create a table but bigger problem I have is that SQLSave can't seem to find the created table to save to it. SQLUpdate and SQLSave both give the "table not found on channel" message. Is the table naming I am using not working? — ingrid

Lars Br. Lars Br. · Accepted Answer · 2017-02-27T06:18:13

Generally, it's a good idea to specify the target table in SAP HANA beforehand. That way things like COLUMN/ROW store setting and the specific data types for each column can be set as they should be (e.g. sqlSave doesn't seem to create NVARCHAR columns even when UNICODE data needs to be saved).

This is an example that just works out of the box for me (also SPS11):

library("RODBC") 
ch<-odbcConnect("SK1", uid="DEVDUDE",pwd="*******")

table.for.save <- 'AIRQUALITY'
aqdata <- airquality
sqlSave(ch,dat = aqdata, tablename = table.for.save, verbose = TRUE, rownames =  FALSE)
odbcClose(ch)

Query: CREATE TABLE "AIRQUALITY" ("Ozone" INTEGER, "SolarR" INTEGER, "Wind" DOUBLE, "Temp" INTEGER, "Month" INTEGER, "Day" INTEGER) Query: INSERT INTO "AIRQUALITY" ( "Ozone", "SolarR", "Wind", "Temp", "Month", "Day" ) VALUES ( ?,?,?,?,?,? )

Binding: 'Ozone' DataType 4, ColSize 10 Binding: 'SolarR' DataType 4, ColSize 10 Binding: 'Wind' DataType 8, ColSize 15 Binding: 'Temp' DataType 4, ColSize 10 Binding: 'Month' DataType 4, ColSize 10 Binding: 'Day' DataType 4, ColSize 10 Parameters: no: 1: Ozone 41//no: 2: SolarR 190//no: 3: Wind 7.4//no: 4: Temp 67//no: 5: Month 5//no: 6: Day 1// ...

Problems creating and populating tables to HANA RODBC R

1 Answers