Converting and Masking Connexo XML to a database (MySQL)

August 13, 2019

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 to convert the data to a relational format. 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 Flexter

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to MySQL can be done in a few simple steps.
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

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.
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.