CODEWITHSUNDEEP
apache-sparkapache-spark-sqlteradatapyspark
Venkat M
Venkat M

Reputation: 11

How to write dataframe results to teradata with session set commands enabled before writing using Spark Session

My data is having Special characters(like Smiley Characters, some junk characters ). I want to insert the data as is into Terdata table. I can see that Hive stores data as with UTF-8 format. Where In teradata spark dataframe write is failing with error

Error 6705 An illegally formed character string was encountered during translation

To fix the issue, I have followed the below process

  1. Write custom Scala code using DriverManager and Connection createStatement.

JDBC ConnectionString = jdbc:teradata://connection/DATABASE=dbname,CHARSET=UTF-8,COLUMN_NAME=ON,MAYBENULL=ON

  1. Before writing the actual data , enabled SESSION commands -

SET SESSION CHARACTER SET UNICODE PASS THROUGH ON

  1. After session command execution is success then writing actual data to teradata table.

But I wanted to see is there any possible option to enable session level commands before writing dataframe data to teradata table.

Can I use session level set statements in dataframe option method ? Like

table_df.write.format("jdbc").option("url",jdbcurl").option("dbtable",tablename).option("dbname",dbname).option("username",user).option("password",password)

Please provide your thoughts on this. Thank you

Upvotes: 1

Views: 1236

Answers (1)

Tom Nolan
Tom Nolan

Reputation: 450

You can specify the Teradata JDBC Driver's RUNSTARTUP=ON connection parameter and specify the SET SESSION CHARACTER SET UNICODE PASS THROUGH ON command in the database user's STARTUP clause.

Please refer to the Teradata JDBC Driver User Guide for details, in the section titled User STARTUP SQL Request:

https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGBHBDAB

Upvotes: 2

Related Questions