CODEWITHSUNDEEP
scalaapache-sparkteradata
Riddhi Patel
Riddhi Patel

Reputation: 49

Spark read from Teradata view which has column with Title

I want to try read from Teradata using Spark.

I have a Teradata view from one table which has column(order_id) with title( "ORDER ID") column name.

So while using spark it is giving me following error.

Caused by: java.sql.SQLException: [Teradata Database] [TeraJDBC 16.20.00.08] [Error 5628] [SQLState HY000] Column ORDER ID not found in ST.

 val Query  = "(select order_id from table sample 100) ST ";

 var reader = sparkSession.read
      .format("jdbc")
      .option("url", "jdbc:teradata://xx/charset=xx, DBS_PORT=xx, TMODE=TERA, user=xx, password=xx")
      .option("dbtable", Query);

    reader.load().show(100);

Upvotes: 1

Views: 1341

Answers (2)

Inigo San Aniceto
Inigo San Aniceto

Reputation: 11

You can add the option "column_name" -> "on", to query using the columnNames instead of the titles (it should work for TD drivers >=16) [I've tested it with 17]

spark.read.format("jdbc")
  .options(Map(
    "url" -> tdURL,
    "user" -> yourUser,
    "password" -> yourPw,
    "dbtable" ->  table,
    "strict_names" -> "off",
    "column_name" -> "on",
    "driver" -> "com.teradata.jdbc.TeraDriver"))
  .load()

Upvotes: 1

ravioli
ravioli

Reputation: 3833

If I'm not mistaken, a title is just a way to change the display name of a column when returning the results. In your query you'd still need to reference the actual column name or alias.

That being said, if "ORDER ID" is actually an alias for the order_id column, then you just reference it like that in your query with double quotes: select "ORDER ID" from table sample 100

Just make sure you properly escape the quotes when setting the Query variable.

Upvotes: 1

Related Questions