Extracting text and schema from single tag

[belenaj]

Hello,
we are trying to extract information from the XML Tag SubscriptionType that only has text content.

<myFile>
    <SalesBySubscription>
       <!-- One SubscriptionType & Currency per XML-->
        <SubscriptionType>Free</SubscriptionType>
        <Currency>EUR</Currency>
         .....
        <!-- millions of rows-->
    </SalesBySubscription>
</myFile>

However, the schema printed from the call:

val SubscriptionTypeDS = sparksession.read
      .format("com.databricks.spark.xml")
      .option("rowTag", "SubscriptionType")
SubscriptionTypeDS.load(pathToFile)

SubscriptionTypeDS.cache()
SubscriptionTypeDS.printSchema()

is always null.
Is this a known issue? Can we help you somehow?

Thank you!

[HyukjinKwon]

Yup, it's a know issue. There's a PR for it #285.

[BioQwer]

@jbelenag hello.
PR #285 doesn't fix it.

val SubscriptionTypeDS2 = sqlContext.read
  .format("com.databricks.spark.xml")
  .option("rowTag", "SubscriptionType")
  .load(xmlFile)
SubscriptionTypeDS2.printSchema()
SubscriptionTypeDS2.show()

yes it's filled nulls, i think it's bug)

root

++
||
++
||
||
++

but use can use in this way

val SubscriptionTypeDS = sqlContext.read
  .format("com.databricks.spark.xml")
  .option("rowTag", "SalesBySubscription")
  .load(xmlFile)
SubscriptionTypeDS.cache()
SubscriptionTypeDS.printSchema()
SubscriptionTypeDS.show(100,false)
SubscriptionTypeDS.select("SubscriptionType").show()

out

root
 |-- Currency: string (nullable = true)
 |-- SubscriptionType: string (nullable = true)

+--------+----------------+
|Currency|SubscriptionType|
+--------+----------------+
|EUR     |Free            |
+--------+----------------+

+----------------+
|SubscriptionType|
+----------------+
|            Free|
+----------------+

Can you use in this way?

[belenaj]

Thanks for having a look a it.
Yes, we could fetch the parent tag <SalesBySubscription> but sometimes we run into memory issues since the object SubscriptionTypeDS is huge.
We are handling 20-30Gb big files. In the future they might be even bigger, and we are looking for a scalable solution.

[BioQwer]

Firstly, If you have memory issues, you should not cache parent tag, cache only child.

val SalesBySubscriptionDS = sqlContext.read
  .format("com.databricks.spark.xml")
  .option("rowTag", "SalesBySubscription")
  .load(xmlFile)
val SubscriptionType = SalesBySubscriptionDS.select("SubscriptionType").cache()

Secondary, make you structSchema and use them, because this program work in 2 parts:

1. read xml (cheap)
2. create structSchema for your data, read all row and merge them (very expensive operation, with shuffle and aggregation)

I think if you do this thing you will forget about perfomance and memory issues.

But still, you can fix this bug for your and make PR with your fixes.
Whatever, all libraries will have first part the same, because it's standart way to read xml to rdd.

[belenaj]

Hi!
Unfortunately, we run into the same memory issues.

There is insufficient memory for the Java Runtime Environment to continue.
Native memory allocation (mmap) failed to map 12288 bytes for committing reserved memory.
An error report file with more information is saved as:

java.lang.OutOfMemoryError: Requested array size exceeds VM limit
-XX:OnOutOfMemoryError="kill -9 %p"
Executing /bin/sh -c "kill -9 10803"...

And when run in local machine:

Caused by: java.lang.OutOfMemoryError: Java heap space

[belenaj]

Hi! We have developed a test code to replicate the error.

First, we created a sample XML File that generates the error (big version compressed in
myFile.zip)

<?xml version="1.0" encoding="UTF-8"?>
<file>
	<veryBig>
		<wantedTag>Hello World</wantedTag>
		<anotherWantedTag>Hello All</anotherWantedTag>
		<otherData>many Rows</otherData>
		<otherData>many Rows</otherData>
		<otherData>many Rows</otherData>
		<otherData>many Rows</otherData>
		<otherData>many Rows</otherData>
		<otherData>many Rows</otherData>		
                <!-- introduce here extra rows as needed -->
	</veryBig>
</file>

Secondly, here is the code that reads the file.

import org.apache.spark.sql.SparkSession

object sparkXmlBug {
  def main (Args: Array[String]): Unit = {
    val input = "C:\\tmp\\myFile.xml"
    val ss = SparkSession
      .builder()
      .appName("sparkXmlBug")
      .master("local[*]")
      .getOrCreate()

// this causes a java.util.NoSuchElementException
/*    val df=  ss.read
      .format("com.databricks.spark.xml")
      .option("rowTag", "wantedTag")
      .load(input)
*/

     val customSchema =  StructType(Array(
        StructField("wantedTag", DataTypes.StringType, true, Metadata.empty),
        StructField("anotherWantedTag", DataTypes.StringType, true, Metadata.empty),
        StructField("otherData", DataTypes.StringType, true,Metadata.empty)
      ))

    val df =  ss.read
      .format("com.databricks.spark.xml")
      .option("rowTag", "veryBig")
      .schema(customSchema)
      .load(input)

    val wantedTag = df.select("wantedTag").cache()
    val anotherWantedTag = df.select("anotherWantedTag").cache()

    wantedTag.show()
    anotherWantedTag.show()

  }
}

Finally, there is a rudimentary script to generate as many rows as desired.

#!/bin/bash

iterations=100000
fileName=myFile.xml

# header
echo "<?xml version="1.0" encoding="UTF-8"?>
<file>
<veryBig>
<wantedTag>Hello World</wantedTag>
<anotherWantedTag>Hello All</anotherWantedTag>" >> $fileName;

# body
for ((i=1;i<=$iterations;i++)) ;
do echo "<otherData>many Rows</otherData>"; 
done >> $fileName

# tail
echo "</veryBig>
</file>" >> $fileName;

I hope this helps!

[BioQwer]

@jbelenag why are you need cache with dataSets?

[maccopper2]

@BioQwer The cache is not essential to run the Code / show the issue.
Feel free to edit the Code in any way that makes it working with the huge myFile.xml

[HyukjinKwon]

self-closing XML is supported now. I am leaving this closed.

[srowen]

I don't think the issue is self-closing tags here. If you parse the XML above with SubscriptionType as row tag you don't get any result. I think there is some problem in the parser that I have been debugging where it skips character events. Although it is also probably the root of a few other bugs, let me reopen this one too until we narrow down which ones have common cause and close all but one as a duplicate.