SqlServer Datatype to Hive Datatype using Spark Scala

Question

Spark is used to get schema of a table from SQL server DB. I am facing issue while creating Hive tables using this schema due to datatype mismatch. How can we convert the SQL Server datatype to Hive datatype in Spark Scala.

val df = sqlContext.read.format("jdbc")
  .option("url", "jdbc:sqlserver://host:port;databaseName=DB")
  .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
  .option("dbtable", "schema.tableName")
  .option("user", "Userid").option("password", "pswd")
  .load().schema

For which types are you facing the issue? Here is one source that you can use to map your Hive/SQL Server schemas docs.oracle.com/goldengate/v12212/gg-veridata/GVDUG/… — abiratsis
– abiratsis, Commented Jul 20, 2019 at 8:54
From the schema it should automatically convert to hive datatype. How can that be achieved using spark scala? — Amrutha K
– Amrutha K, Commented Jul 21, 2019 at 10:20

Amrutha K · Accepted Answer · 2019-07-22 12:21:07Z

2

Thanks, Got the solution.Created a method to check datatypes as given below.

def sqlToHiveDatatypeMapping(inputDatatype: String): String = inputDatatype match {
  case "numeric" => "int"
  case "bit" => "smallint"
  case "long" => "bigint"
  case "dec_float" => "double"
  case "money" => "double" 
  case "smallmoney" => "double"  
  case "real" => "double"
  case "char" => "string" 
  case "nchar" => "string"  
  case "varchar" => "string"
  case "nvarchar" => "string"
  case "text" => "string"
  case "ntext" => "string"
  case "binary" => "binary"
  case "varbinary" => "binary"
  case "image" => "binary"
  case "date" => "date"
  case "datetime" => "timestamp"
  case "datetime2" => "timestamp"
  case "smalldatetime" => "timestamp"
  case "datetimeoffset" => "timestamp"
  case "timestamp" => "timestamp"
  case "time" => "timestamp"
  case "clob" => "string"
  case "blob" => "binary"
  case _ => "string"
}
val columns = df.fields.map({field => field.name.toLowerCase+" "+sqlToHiveDatatypeMapping(field.dataType.typeName.toLowerCase)}).mkString(",")

answered Jul 22, 2019 at 12:21

Amrutha K

2041 gold badge4 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abiratsis Over a year ago

Exactly, this seems reasonable to me

Collectives™ on Stack Overflow

SqlServer Datatype to Hive Datatype using Spark Scala

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related