Home » Data Pipeline » How to parse XML data in Amazon Redshift

How to parse XML data in Amazon Redshift

Redshift does not have native support for querying and parsing XML. Other data platforms have support for XML, e.g. parsing XML inside Snowflake is a native option.

Redshift has introduced a ‘Super’ data type a while back to load semi-structured data formats. Loading XML into this data type is not supported however.

As of now, Amazon Redshift does not provide any native support to handle XML data at all. The Redshift documentation indicates that XML is unsupported both as a format and as a function. In this section, we will see an alternative approach to load XML data into Redshift.

As Redshift has no native support for handling XML data, we need to pre-process the XML as a workaround first. One stratconvert the XML data to CSV format before loading. To convert complex XML into CSV there are few options that can be used.

Converting XML to CSV using AWS Glue

Step 1: Upload data to S3

We first need to upload our XML data files to the S3 storage.

Step 2: Crawl XML data

The next step is to load the XML structure in the Glue Data Catalog. To identify the structure of the underlying XML, we need to create an AWS Crawler that will access the data store, extract metadata, and create table definitions in the AWS Glue Data Catalog. Glue crawler will infer the schema from the top level by using default classifier, otherwise we can create a custom classifier to explicitly choose the row level tag.

Step 3: Convert to CSV using Glue job and use PySpark transforms to flatten the data

The next step is to write a Glue ETL job to convert the XML file to CSV. A job is the business logic that performs the extract, transform, and load (ETL) work in AWS Glue. When you configure a job, AWS Glue generates an Apache Spark API (PySpark) script that extracts data from sources, transforms the data, and loads it into targets. The converted data in the CSV format can be stored in S3 storage or in the Glue Data Catalog.

These Glue generated scripts work well for the simple XML data but fail to flatten the complex XML structures. As we all know, XML data is mostly multilevel nested and often also contains multiple branches. The crawled metadata table would have complex data types such as structs, array of structs, etc. To flatten or unnest this data, we need to write custom scripts using PySpark transforms or Databricks Spark-XML. We can write the code by analysing the structure of the XML data.

Step 4: Load data from S3 to Amazon Redshift

Now that we have the data in a format that Redshift supports in our S3 storage, we can load it into Amazon Redshift by using the COPY FROM option.

Converting XML to CSV using AWS Lambda

Step 1. Upload data to S3

We first need to upload our XML data files to the S3 storage.

Step 2: Trigger a lambda function on each S3 object, convert them to CSV files, and store it to a target bucket.

The next step is to write the lambda function which will convert each XML file to CSV. To write the code, we need to identify the structure of underlying data.

Step 3: Load data from S3 to Amazon Redshift

The CSV files in the S3 storage can now be loaded into Amazon Redshift by using COPY FROM option.

To summarise, we cannot load XML data directly into Redshift as there is no native support. We need to convert XML data into CSV before loading it into Redshift.

As mentioned above, we can use a Glue ETL job to convert simple XML files, but for complex XML structures we need to write custom code to convert the XML data. This is not a problem for simple XML documents, but it becomes a significant overhead when the underlying XML contains multilevel nested structures.

Also, if there is even a small change in the structure of input data, the code needs to be modified to accommodate each change. We can say that Amazon Redshift is not suitable for use cases in which there is a need to process complex XML files.

Our own product Flexter automatically converts XML to Redshift. Even better, it uses AI to optimise the output for simplified target schema and easy consumption of the data. Check out the Flexter product page.