Scala XML processing

Question

I have a XML file in S3 contains the Schema for my table:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<DATA  CHARSET="UTF8" DELIMITER="\t">
<COLUMNS>
<COLUMN DATA_PRECISION="10" DATA_SCALE="0" DATA_TYPE="NUMBER"  ID="APPLICATION_ID" />
<COLUMN DATA_LENGTH="40" DATA_TYPE="VARCHAR2" ID="DESCRIPTIVE_FLEXFIELD_NAME"/>
<COLUMN DATA_LENGTH="30" DATA_TYPE="VARCHAR2" ID="LANGUAGE"/>
<COLUMN DATA_LENGTH="60" DATA_TYPE="VARCHAR2" ID="TITLE"/>
<COLUMN DATA_TYPE="DATE" DATE_MASK="YYYY/MM/DD.HH24:MI:SS" ID="LAST_UPDATE_DATE"/>
<COLUMN DATA_PRECISION="15" DATA_SCALE="0" DATA_TYPE="NUMBER" ID="LAST_UPDATED_BY" />
<COLUMN DATA_TYPE="DATE" DATE_MASK="YYYY/MM/DD.HH24:MI:SS" ID="CREATION_DATE"/>
<COLUMN DATA_PRECISION="15" DATA_SCALE="0" DATA_TYPE="NUMBER" ID="CREATED_BY" />
<COLUMN DATA_PRECISION="10" DATA_SCALE="0" DATA_TYPE="NUMBER" ID="LAST_UPDATE_LOGIN" />

</COLUMNS>
</DATA>

And I would need to get all the ID and DATA_TYPE out.

I read the file using sc.textFile("s3://XXX/schemas/XXXX.xml") but wouldn't be able to process.

Anyone can help me on this?

Convert the XML into JSON and then create data frame from JSON . — Rumesh Krishnan, Mar 20 '18 at 02:31

score 1 · Accepted Answer · answered Mar 20 '18 at 03:48

You can use Spark-XML to read the xml file as a dataframe and select only the columns you require and change collect it as a list.

import spark.implicits._
//load the xml from s3
val df = spark.sqlContext.read
  .format("com.databricks.spark.xml")
  .option("rowTag", "COLUMNS")
  .option("valueTag", "bvalue")
  .load("path to s3")

//explode the array 
val allvalue = df.select(explode($"COLUMN").as("column")).select("column.*")

//collect require column as a map
val schema = allvalue.select("_ID", "_DATA_TYPE")
    .rdd.map(x=>(x.getString(0), x.getString(1))).collectAsMap()

//print the output
schema.foreach(println)

Output:

(CREATION_DATE,DATE)
(LAST_UPDATE_LOGIN,NUMBER)
(CREATED_BY,NUMBER)
(LAST_UPDATE_DATE,DATE)
(LANGUAGE,VARCHAR2)
(APPLICATION_ID,NUMBER)
(TITLE,VARCHAR2)
(LAST_UPDATED_BY,NUMBER)
(DESCRIPTIVE_FLEXFIELD_NAME,VARCHAR2)

Now you can change this to the schema that spark supports.

This works great! But my company has restrictions on using external sources, I would need to apply for permission to do so. — Sidi, Mar 20 '18 at 21:26
I hope they allow your permission otherwise you will end up writing own parser. — koiralo, Mar 21 '18 at 03:20

Scala XML processing

1 Answers1