Convert Oracle XML BLOB information utilizing Amazon EMR and cargo to Amazon Redshift

In legacy relational database administration programs, information is saved in a number of advanced information sorts, such XML, JSON, BLOB, or CLOB. This information may include priceless data that’s typically troublesome to remodel into insights, so that you could be searching for methods to load and use this information in a contemporary cloud information warehouse corresponding to Amazon Redshift. One such instance is migrating information from a legacy Oracle database with XML BLOB fields to Amazon Redshift, by performing preprocessing and conversion of XML utilizing Amazon EMR. On this publish, we describe an answer structure for this use case, and present you easy methods to implement the code to deal with the XML conversion.

Resolution overview

Step one in any information migration mission is to seize and ingest the info from the supply database. For this job, we use AWS Database Migration Service (AWS DMS), a service that helps you migrate databases to AWS rapidly and securely. On this instance, we use AWS DMS to extract information from an Oracle database with XML BLOB fields and stage the identical information in Amazon Easy Storage Service (Amazon S3) in Apache Parquet format. Amazon S3 is an object storage service providing industry-leading scalability, information availability, safety, and efficiency, and is the storage of selection for organising information lakes on AWS.

After the info is ingested into an S3 staging bucket, we used Amazon EMR to run a Spark job to carry out the conversion of XML fields to semi-structured fields, and the outcomes are loaded in a curated S3 bucket. Amazon EMR runtime for Apache Spark could be over thrice quicker than clusters with out EMR runtime, and has 100% API compatibility with commonplace Apache Spark. This improved efficiency means your workloads run quicker and it saves you compute prices, with out making any modifications to your utility.

Lastly, reworked and curated information is loaded into Amazon Redshift tables utilizing the COPY command. The Amazon Redshift desk construction ought to match the variety of columns and the column information sorts within the supply file. As a result of we saved the info as a Parquet file, we specify the SERIALIZETOJSON possibility within the COPY command. This enables us to load advanced sorts, corresponding to construction and array, in a column outlined as SUPER information sort within the desk.

The next structure diagram exhibits the end-to-end workflow.

Intimately, AWS DMS migrates information from the supply database tables into Amazon S3, in Parquet format. Apache Spark on Amazon EMR reads the uncooked information, transforms the XML information sort right into a struct information sort, and saves the info to the curated S3 bucket. In our code, we used an open-source library, referred to as spark-xml, to parse and question the XML information.

In the remainder of this publish, we assume that the AWS DMS duties have already run and created the supply Parquet information within the S3 staging bucket. If you wish to arrange AWS DMS to learn from an Oracle database with LOB fields, check with Successfully migrating LOB information to Amazon S3 from Amazon RDS for Oracle with AWS DMS or watch the video Migrate Oracle to S3 Knowledge lake through AWS DMS.

Stipulations

If you wish to observe together with the examples on this publish utilizing your AWS account, we offer an AWS CloudFormation template you possibly can launch by selecting Launch Stack:

Present a stack title and depart the default settings for all the things else. Anticipate the stack to show Create Full (this could solely take a couple of minutes) earlier than shifting on to the opposite sections.

The template creates the next sources:

• A digital personal cloud (VPC) with two personal subnets which have routes to an Amazon S3 VPC endpoint
• The S3 bucket {stackname}-s3bucket-{xxx}, which accommodates the next folders:
  • libs – Accommodates the JAR file so as to add to the pocket book
  • notebooks – Accommodates the pocket book to interactively take a look at the code
  • information – Accommodates the pattern information
• An Amazon Redshift cluster, in one of many two personal subnets, with a database named rs_xml_db and a schema named rs_xml
• A secret (rs_xml_db) in AWS Secrets and techniques Supervisor
• An EMR cluster

The CloudFormation template shared on this publish is solely for demonstration functions solely. Please conduct your individual safety overview and incorporate finest practices previous to any manufacturing deployment utilizing artifacts from the publish.

Lastly, some fundamental data of Python and Spark DataFrames can assist you overview the transformation code, however isn’t obligatory to finish the instance.

Understanding the pattern information

On this publish, we use school college students’ course and topics pattern information that we created. Within the supply system, information consists of flat construction fields, like course_id and course_name, and an XML discipline that features all of the course materials and topics concerned within the respective course. The next screenshot is an instance of the supply information, which is staged in an S3 bucket as a prerequisite step.

We are able to observe that the column study_material_info is an XML sort discipline and accommodates nested XML tags in it. Let’s see easy methods to convert this nested XML discipline to a generic struct discipline within the subsequent steps.

Run a Spark job in Amazon EMR to remodel the XML fields within the uncooked information to Parquet

On this step, we use an Amazon EMR pocket book, which is a managed setting to create and open Jupyter Pocket book and JupyterLab interfaces. It allows you to interactively analyze and visualize information, collaborate with friends, and construct purposes utilizing Apache Spark on EMR clusters. To open the pocket book, observe these steps:

1. On the Amazon S3 console, navigate to the bucket you created as a prerequisite step.
2. Obtain the file within the notebooks folder.
   
3. On the Amazon EMR console, select Notebooks within the navigation pane.
4. Select Create pocket book.
5. For Pocket book title, enter a reputation.
6. For Cluster, choose Select an current cluster.
7. Choose the cluster you created as a prerequisite.
   
8. For Safety Teams, select BDB1909-EMR-LIVY-SG and BDB1909-EMR-Pocket book-SG
9. For AWS Service Position, select the position bdb1909-emrNotebookRole-{xxx}.
10. For Pocket book location, specify the S3 path within the notebooks folder (s3://{stackname}-s3bucket-xxx}/notebooks/).
11. Select Create pocket book.
    
12. When the pocket book is created, select Open in JupyterLab.
    
13. Add the file you downloaded earlier.
    
14. Open the brand new pocket book.
    
    The pocket book ought to look as proven within the following screenshot, and it accommodates a script written in Scala.
    
15. Run the primary two cells to configure Apache Spark with the open-source spark-xml library and import the wanted modules.The spark-xml bundle permits studying XML information in native or distributed file programs as Spark DataFrames. Though primarily used to transform (parts of) massive XML paperwork right into a DataFrame, spark-xml may parse XML in a string-valued column in an current DataFrame with the from_xml perform, with the intention to add it as a brand new column with parsed outcomes as a struct.
16. To take action, within the third cell, we load the info from the Parquet file generated by AWS DMS right into a DataFrame, then we extract the attribute that accommodates the XML code (STUDY_MATERIAL_INFO) and map it to a string variable title payloadSchema.
17. We are able to now use the payloadSchema within the from_xml perform to transform the sector STUDY_MATERIAL_INFO right into a struct information sort and added it as a column named course_material in a brand new DataFrame parsed.
18. Lastly, we will drop the unique discipline and write the parsed DataFrame to our curated zone in Amazon S3.

As a result of construction variations between DataFrame and XML, there are some conversion guidelines from XML information to DataFrame and from DataFrame to XML information. Extra particulars and documentation can be found XML Knowledge Supply for Apache Spark.

Once we convert from XML to DataFrame, attributes are transformed as fields with the heading prefix attributePrefix (underscore (_) is the default). For instance, see the next code:

  <e-book class="undergraduate">
    <title lang="en">Introduction to Biology</title>
    <writer>Demo Writer 1</writer>
    <yr>2005</yr>
    <value>30.00</value>
  </e-book>

It produces the next schema:

root
 |-- class: string (nullable = true)
 |-- title: struct (nullable = true)
 |    |-- _VALUE: string (nullable = true)
 |    |-- _lang: string (nullable = true)
 |-- writer: string (nullable = true)
 |-- yr: string (nullable = true)
 |-- value: string (nullable = true)

Subsequent, we now have a price in a component that has no baby components however attributes. The worth is put in a separate discipline, valueTag. See the next code:

<title lang="en">Introduction to Biology</title>

It produces the next schema, and the tag lang is transformed into the _lang discipline contained in the DataFrame:

|-- title: struct (nullable = true)
 |    |-- _VALUE: string (nullable = true)
 |    |-- _lang: string (nullable = true)

Copy curated information into Amazon Redshift and question tables seamlessly

As a result of our semi-structured nested dataset is already written within the S3 bucket as Apache Parquet formatted information, we will use the COPY command with the SERIALIZETOJSON choice to ingest information into Amazon Redshift. The Amazon Redshift desk construction ought to match the metadata of the Parquet information. Amazon Redshift can substitute any Parquet columns, together with construction and array sorts, with SUPER information columns.

The next code demonstrates CREATE TABLE instance to create a staging desk.

create desk rs_xml_db.public.stg_edw_course_catalog 
(
course_id bigint,
course_name character various(5000),
course_material tremendous
);

The next code makes use of the COPY instance to load from Parquet format:

COPY rs_xml_db.public.stg_edw_course_catalog FROM 's3://<<your Amazon S3 Bucket for curated information>>/information/goal/<<your output parquet file>>' 
IAM_ROLE '<<your IAM position>>' 
FORMAT PARQUET SERIALIZETOJSON; 

By utilizing semistructured information help in Amazon Redshift, you possibly can ingest and retailer semistructured information in your Amazon Redshift information warehouses. With the SUPER information sort and PartiQL language, Amazon Redshift expands the info warehouse functionality to combine with each SQL and NoSQL information sources. The SUPER information sort solely helps as much as 1 MB of information for a person SUPER discipline or object. Observe, a person worth inside a SUPER object is proscribed to the utmost size of the corresponding Amazon Redshift sort. For instance, a single string worth loaded to SUPER is proscribed to the utmost VARCHAR size of 65535 bytes. See Limitations for extra particulars.

The next instance exhibits how nested buildings in a SUPER column could be simply accessed utilizing SELECT statements:

SELECT DISTINCT bk._category
	,bk.writer
	,bk.value
	,bk.yr
	,bk.title._lang
FROM rs_xml_db.public.stg_edw_course_catalog principal
INNER JOIN principal.course_material.e-book bk ON true;

The next screenshot exhibits our outcomes.

Clear up

To keep away from incurring future costs, first delete the pocket book and the associated information on Amazon S3 bucket as defined in this EMR documentation web page then the CloudFormation stack.

Conclusion

This publish demonstrated easy methods to use AWS providers like AWS DMS, Amazon S3, Amazon EMR, and Amazon Redshift to seamlessly work with advanced information sorts like XML and carry out historic migrations when constructing a cloud information lake home on AWS. We encourage you to do that answer and benefit from all the advantages of those purpose-built providers.

When you have questions or options, please depart a remark.

Concerning the authors

Abhilash Nagilla is a Sr. Specialist Options Architect at AWS, serving to public sector prospects on their cloud journey with a give attention to AWS analytics providers. Outdoors of labor, Abhilash enjoys studying new applied sciences, watching films, and visiting new locations.

Avinash Makey is a Specialist Options Architect at AWS. He helps prospects with information and analytics options in AWS. Outdoors of labor he performs cricket, tennis and volleyball in free time.

Fabrizio Napolitano is a Senior Specialist SA for DB and Analytics. He has labored within the analytics house for the final 20 years, and has lately and fairly without warning change into a Hockey Dad after shifting to Canada.