Apache spark dataframe jdbc write- how to use table_type= COLUMN during writing dataframe using jdbc

[masterpradipg]

I have a apache spark dataframe, it has multiple columns with datatype of string, decimal, integer, boolean.

I want to write this dataframe to sap hana using spark jdbcDF.write.format("jdbc") . 

Problem: Spark dataframe is column type. BY default sap hana creates a table of row type. I want to be create a column type of table using jdbc options. But I could not find the solutions.

my code:

df.write.format("jdbc").option("driver", "com.sap.db.jdbc.Driver").option(
    "url", "jdbc:sap://10.10.xxx.xxx:30015/"
).option("dbtable", "SAP.example_table").option("user", "*****").option(
    "password", "********"
).mode(
    "overwrite"
).save()

Here I have used ngdbc jar (com.sap.db.jdbc.Driver)

I need to understand how can I create table with  table_type= COLUMN.

If I use the above code it will throw error saying 

com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [264] (at 117): invalid datatype: TEXT type is not supported for row table: line 1 col 118 (at pos 117)

[Vitaliy-R]

What are the versions of SAP HANA database and HANA JDBC driver? I am not that familiar with Spark, but indeed by default the new table should be of the COLUMN type in SAP HANA db.

[masterpradipg]

Hey @Vitaliy-R I use Driver: ngdbc_2_18_11.jar (2.18.11) SAP HANA version: 1.00.120.00.1462275491

[Vitaliy-R]

Wow! HANA 1 SPS 12 is quite dated and, indeed, it creates a row table https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d58a5f75191014b2fe921...

I would assume you need to use JDBC compatible with that version of SAP HANA, which did not have a "TEXT" data type in Java-to-SQL conversion: https://help.sap.com/docs/SAP_HANA_PLATFORM/0eec0d68141541d1b07893a39944924e/5be923a2e5bc45afaee4937...

[bettyJa]

Hello, I'm facing the same issue, were you able to solve this?

[RobertWaywell]

Not only is HANA 1.0 quite dated, it has been out of maintenance for several years now and isn't even available to download anymore. 

You should be upgrading to HANA 2.0 to be on a current supported version of HANA. 

[masterpradipg]

i use pyspark