cancel
Showing results for 
Search instead for 
Did you mean: 

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

masterpradipg
Discoverer
0 Kudos
238

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
Developer Advocate
Developer Advocate
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
Discoverer
0 Kudos
Hey @Vitaliy-R I use Driver: ngdbc_2_18_11.jar (2.18.11) SAP HANA version: 1.00.120.00.1462275491
Vitaliy-R
Developer Advocate
Developer Advocate
0 Kudos

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

View Entire Topic
RobertWaywell
Product and Topic Expert
Product and Topic Expert
0 Kudos

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.