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
240

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
masterpradipg
Discoverer
0 Kudos

i use pyspark