How to parse XML data in Google BigQuery
Google BigQuery is an enterprise data warehouse that can store large datasets and it helps in superfast querying using Google infrastructure. It has a data type called ‘Record’ to store nested and repeated data. It supports loading and querying such nested and repeated data from source formats that support object-based schemas, such as JSON files, Avro files, Firestore export files, and Datastore export files.
If our data is in Avro, JSON, Parquet, etc. formats then we can load it easily into BigQuery. But what if we have to load XML data? BigQuery does not provide any native support to parse XML.
Let’s check some alternative ways to parse XML in BigQuery.
Converting XML to CSV
The first solution is converting our input XML files into CSV and then loading them using BigQuery Load. To convert XML to CSV, we need to write a code by analyzing the underlying xml structure. If the underlying structure is complex, writing a conversion code is a significant overhead. Also, we will have to modify the code if there is any change in the input structure.
Loading XML using Apache Beam pipeline
Step 1. Convert the XML file to Python Dictionary.
In the first step we need to convert the XML file into a Python dictionary using the ‘xmltodict’ package. This package provides a method to parse the XML structure and convert it to a Python dictionary.
Step 2: Specify the schema of the output table in BigQuery.
In this step, we just need to define the schema of the table where we want to load the data. If the particular element has child elements, then we should use ‘record’ (struct) datatype to store it in BigQuery.
Step 3: Run an Apache Beam job to read xml and load it to the BigQuery table.
The next step is to write the Beam pipeline to take an XML file and use it to populate a BigQuery table. This job can be run using Google Cloud Dataflow.
In this approach, we are basically converting XML to JSON like structure and then loading it into BigQuery. We are using the ‘record’ datatype to store the nested elements. The maximum nested depth allowed for the ‘record’ type is 15 levels. So, we cannot use this approach to load multilevel nested data which contains more than 15 levels. Also, there is an overhead of writing code for the apache beam pipeline.
In short, if we want to use BigQuery as a platform for a use case where XML processing is required then the only option is converting it from XML to CSV or JSON like format (Python Dictionary). Once we have converted the file, we can use BigQuery to load or use Dataflow to run Apache Beam jobs. The overhead of writing the code to change the format will be there. To conclude, we can say that BigQuery is not a perfect choice to process XML data.
Our own product Flexter automatically converts XML to BigQuery. Find out how ecobee convert XML to BigQuery using Flexter in our success story.
Related Articles
How to Cut Prompt Costs by 90% with Prompt Caching in Snowflake
When building production LLM applications—whether question-answering systems over voluminous documentation, conversational agents with sprawling context windows, or batch processing pipelines—input token processing dominates both latency and compute costs. To solve…
