Spark Dataframe with CLIXML

user_17681243 0
Viewed 54

  1. I am reading a csv file from ADLSGEN2 using databricks read API.

  2. The csv file contains only one column which contains Power Shell(clixml) content.

  3. While trying to extract with spark-xml functions ,I am not getting the correct schema and records are corrupted.

  4. So please help me to convert the clixml to xml or exact solution to extract the xml content.

Code Block:

import com.databricks.spark.xml._
import com.databricks.spark.xml.from_xml_string
import com.databricks.spark.xml.functions.from_xml
import org.apache.spark.sql.functions.{col}
import org.apache.spark.sql.types.{StructType}
import spark.implicits._

var path = "csvpath"
val df= spark.read
.option("header","false")
.option("inferschema","true")
.csv(path)
val dfrename= df.withColumnRenamed("_c0","xmldata")
val xmlSchema = schema_of_xml(dfrename.select("xmldata").as[String])
val xmlDF = dfrename.withColumn("xmldata", from_xml(col("xmldata"), xmlSchema))
display(xmlDF )

XML Content: Sample

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <DT>2018-03-21T08:05:39.5085956-04:00</DT>
    <MS>
      <Obj N="DisplayHint" RefId="1">
        <TN RefId="0">
          <T>Microsoft.PowerShell.Commands.DisplayHintType</T>
          <T>System.Enum</T>
          <T>System.ValueType</T>
          <T>System.Object</T>
        </TN>
        <ToString>DateTime</ToString>
        <I32>2</I32>
      </Obj>
    </MS>
  </Obj>
</Objs>
apache-spark scala xml
1 Answers

Reading XML in Spark

You do not need to read the data using the csv format before parsing the xml. The input can be read using the xml format and providing an appropriate value for the rowTag option. Looking at the data that you have provided, it seems like Obj is the relevant tag to create a row for.

Example usage in Spark Shell

Spark 3.1.2 with the package com.databricks:spark-xml_2.12:0.14.0 loaded.

scala> val xmlDataFrame = spark.read.option("rowTag", "Obj").format("xml").load(path)                                                                                                                      
xmlDataFrame: org.apache.spark.sql.DataFrame = [DT: timestamp, MS: struct<Obj: struct<I32: bigint, TN: struct<T: array<string>, _RefId: bigint> ... 3 more fields>> ... 1 more field]                      
                                                                                                                                                                                                           
scala> xmlDataFrame.printSchema                                                                                                                                                                            
root                                                                                                                                                                                                       
 |-- DT: timestamp (nullable = true)                                                                                                                                                                       
 |-- MS: struct (nullable = true)                                                                                                                                                                          
 |    |-- Obj: struct (nullable = true)                                                                                                                                                                    
 |    |    |-- I32: long (nullable = true)                                                                                                                                                                 
 |    |    |-- TN: struct (nullable = true)                                                                                                                                                                
 |    |    |    |-- T: array (nullable = true)                                                                                                                                                             
 |    |    |    |    |-- element: string (containsNull = true)                                                                                                                                             
 |    |    |    |-- _RefId: long (nullable = true)                                                                                                                                                         
 |    |    |-- ToString: string (nullable = true)                                                                                                                                                          
 |    |    |-- _N: string (nullable = true)                                                                                                                                                                
 |    |    |-- _RefId: long (nullable = true)                                                                                                                                                              
 |-- _RefId: long (nullable = true)                                                                                                                                                                        
                                                                                                                                                                                                           
                                                                                                                                                                                                           
scala> xmlDataFrame.show(false)                                                                                                                                                                            
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------+------+                                    
|DT                        |MS                                                                                                                                 |_RefId|                                    
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------+------+                                    
|2018-03-21 12:05:39.508595|{{2, {[Microsoft.PowerShell.Commands.DisplayHintType, System.Enum, System.ValueType, System.Object], 0}, DateTime, DisplayHint, 1}}|0     |                                    
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------+------+
user_59076540
Related