Converting and Masking Connexo XML to MySQL

Uli Bethke Flexter, SQL, XML

In this post we will show you how obfuscate XML data and convert it to a tabular format. We will use XML documents from Connexo NetSense and convert it to a relational format in MySQL.

We will be using Sonra’s masking tool Paranoid (open source) to obfuscate the data. We will use Flexter as a Service (FaaS) to convert the data to a relational format. FaaS follows a pay as you go model. Flexter is also available as a free edition and as an enterprise edition. The enterprise edition can be installed in the cloud or on-premise.

Connexo NetSense

Connexo NetSense collects data from RF mesh and cellular networks, giving the coverage and data flexibility from daily to real-time interval reads.

It provides interface to support node engaged in smart grid applications such as meter reading, street and commercial lighting infrastructure monitoring and more.

MySql

MySQL is an open-source relational database management system (RDBMS). MySQL is a component of the LAMP web application software stack. It is used in many database-driven web applications e.g. Drupal, Joomla, WordPress. It is also used by many popular websites such as Facebook, Twitter and Youtube.

Masking Connexo XML

In one of the first steps we will mask the Connexo XML data.

We have already shown how to install Paranoid in our Masking Sabre XML post.

To mask data we have to provide a path to our file and a path to an output location. Paranoid will create the folder automatically, no need to create it first.

As you can see in the image, it took less than 20 seconds to mask all of the values in the XML file. Optionally Paranoid has the feature to mask individual elements inside an XML document and also multiple XML documents in one go.

Let’s have a look at our file after masking

After checking that everything is masked we can go and zip our masked file and continue with processing it with Flexter.

Processing Connexo XML with FaaS API

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to MySQL can be done in a few simple steps. For more details please refer to the FaaS API documentation.

Step 1 - Authenticate

Step 2 - Define Source Connection (Upload or S3) for Source Data (JSON/XML)

Step 3 - Optionally define Source Connection (Upload or S3) for Source Schema (XSD)

Step 4 - Define your Target Connection, e.g. Snowflake, Redshift, SQL Server, Oracle etc.

Step 5 - Convert your XML/JSON from Source to Target Connection

Step 1 - Authenticate

To get an access_token you need to make a call to /oauth/token with Authorization header and 3 form parameters:

  • username=YOUR_EMAIL
  • password=YOUR_PASSWORD
  • grant_type=password

You will get your username and password from Sonra when you sign up for the service.

Example of output

Step 2 - Define Source Connection (Upload) for Source Data (Connexo XML)

In next step we upload Connexo XML source data.

Example of output

Step 4 - Define Target Connection (MySQL)

Since we don’t have a Source Schema we skip the optional step of defining a Source Schema.

We define our Target connection. We give the Target Connection a name and supply various connection parameters to the MySQL database.

Example of output

Convert XML/JSON automatically to a Database, Text, or Hadoop

No manual coding
Cut cost by up to 80%

Find out more

Step 5 - Convert JSON data from Source Connection (Upload) to Target Connection (MySQL)

In the final step, we convert the Connexo XML data. It will be written to MySQL Target Connection.

Example of output

 

Example of ER Diagram

You can download the ER Diagram for the converted Connexo XML file here.

Next we will run an SQL Query where we will select Information about Events.

Conclusion

In a couple of minutes we have masked Connexo XML and converted it to MySQL in 5 easy steps. This entire process is done in a few minutes with Paranoid and Flexter.

You can try out the free version of Flexter online.

Our enterprise edition can be installed on a single node or for very large volumes of XML on a cluster of servers.

If you have any questions please refer to the Flexter FAQ section. You can also request a demo of Flexter or reach out to us directly.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.