Problems creating and populating tables to HANA RO

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.

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// ...