Get dataframe schema load to metadata table

Question

Use case is to read a file and create a dataframe on top of it.After that get the schema of that file and store into a DB table.

For example purpose I am just creating a case class and getting the printschema however I am unable create a dataframe out of it

Here is a sample code

case class Employee(Name:String, Age:Int, Designation:String, Salary:Int, ZipCode:Int)

val spark = SparkSession
.builder()
.appName("Spark SQL basic example")
.config("spark.master", "local")
.getOrCreate()

import spark.implicits._
val EmployeesData = Seq( Employee("Anto",   21, "Software Engineer", 2000, 56798))
val Employee_DataFrame = EmployeesData.toDF
val dfschema = Employee_DataFrame.schema

Now dfschema is a structype and wanted to convert it in a dataframe of two columns , how to achieve that

dfschema is of structtype with 5 columns as defined in the case class. what 2 columns do you want to convert to? — fledgling, Jun 27 '19 at 19:51

abiratsis · Accepted Answer · 2019-06-30T15:16:38.620

3

Spark >= 2.4.0

In order to save the schema into a string format you can use the toDDL method of the StructType. In your case the DDL format should be:

`Name` STRING, `Age` INT, `Designation` STRING, `Salary` INT, `ZipCode` INT

After saving the schema you can load it from the database and use it as StructType.fromDDL(my_schema) this will return an instance of StructType which you can use to create the new dataframe with spark.createDataFrame as @Ajay already mentioned.

Also is useful to remember that you can always extract the schema given a case class with:

import org.apache.spark.sql.catalyst.ScalaReflection
val empSchema = ScalaReflection.schemaFor[Employee].dataType.asInstanceOf[StructType]

And then you can get the DDL representation with empSchema.toDDL.

Spark < 2.4

For Spark < 2.4 use DataType.fromDDL and schema.simpleString accordingly. Also instead of returning a StructType you should use an DataType instance omitting the cast to StructType as next:

val empSchema = ScalaReflection.schemaFor[Employee].dataType

Sample output for empSchema.simpleString:

struct<Name:string,Age:int,Designation:string,Salary:int,ZipCode:int>

edited Jun 30 '19 at 15:16

answered Jun 28 '19 at 07:00

abiratsis

7,051
3
28
46

Thanks a lot , one thing I notice toDDL method introduced in spark 2.4 – pratik rudra Jun 28 '19 at 16:29
True Pratik, fromDDL exists since 2.2.0 though. Which version are you using? – abiratsis Jun 28 '19 at 17:10
Hi Pratik I modified the answer for older version as well – abiratsis Jun 30 '19 at 15:18
you welcome Pratik, let me know if it worked for you – abiratsis Jul 01 '19 at 13:54
Worked like a charm. Can you please let me know similar function like toDDL or simplestring exists for python as well ? – pratik rudra Jul 01 '19 at 16:32
Yes there is a similar functionality for pyspark as well check this link https://stackoverflow.com/questions/55972609/assign-sql-schema-to-spark-dataframe/55976814#55976814 – abiratsis Jul 01 '19 at 16:39

score 0 · Answer 2 · answered Jun 28 '19 at 06:02

Try this -

//-- For local file
val rdd = spark.read.option("wholeFile", true).option("delimiter",",").csv(s"file:///file/path/file.csv").rdd

val schema = StructType(Seq(StructField("Name", StringType, true),
                            StructField("Age", IntegerType, true),
                            StructField("Designation", StringType, true),
                            StructField("Salary", IntegerType, true),
                            StructField("ZipCode", IntegerType, true)))

val df = spark.createDataFrame(rdd,schema)

Get dataframe schema load to metadata table

2 Answers2

Linked