BigData- Nifi XML Job Injection

To process XML in NiFi

• Use GetFile or ListFile and FetchFile processors to read XML files from a source directory.
• Use SplitXml (if needed) to break large XML into smaller parts.
• Convert XML into a structured format using ConvertRecord (XMLReader → Avro/JSON) or EvaluateXPath to extract key elements.
• Use PutHDFS to store the processed XML data in HDFS.
• Use ExecuteSQL (if needed) to insert the data into a Hive table.

If the XML is already in HDFS, use CREATE EXTERNAL TABLE with ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' or convert it into ORC/Parquet. 

CREATE EXTERNAL TABLE xml_data_table (
    id STRING,
    name STRING,
    value STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/path/to/hdfs/xml_data';

Use EXCEPT or JOIN to compare XML-loaded data with reference Hive table:

SELECT * FROM xml_data_table
EXCEPT
SELECT * FROM reference_hive_table;

or

SELECT x.id, x.value, r.value
FROM xml_data_table x
LEFT JOIN reference_hive_table r
ON x.id = r.id
WHERE x.value != r.value OR r.id IS NULL;

You can use ExecuteSQL in NiFi to query both tables and check for mismatches and compare datasets using RouteOnAttribute or QueryRecord.

If the dataset is large, you can use PySpark for efficient validation:

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("Validation").enableHiveSupport().getOrCreate()

xml_df = spark.sql("SELECT * FROM xml_data_table")
hive_df = spark.sql("SELECT * FROM reference_hive_table")

mismatch_df = xml_df.join(hive_df, "id", "left_anti")
mismatch_df.show()