Converting Covid XML and JSON to Yellowbrick

 
Sonra has recently certified Flexter against Yellowbrick.
In this blog post we show you how Flexter and Yellowbrick work together to easily convert and query data that is locked away in XML or JSON documents.
Using Flexter we convert XML and JSON documents to tables in Yellowbrick without writing a single line of code. We then run SQL queries on Yellowbrick to analyse the data and easily generate insights.
Yellowbrick Data Warehouse is the first analytic database built and optimized for flash memory from the bottom up. Yellowbrick made a key architectural shift called NFQ (Native Flash Query). It allows them to run analytic queries against flash just as fast as an in-memory database.
Flexter is a data warehouse automation solution for semi-structured data. It automates the conversion of XML or JSON documents to a database, text or big data formats such as Parquet, ORC, or Avro. 0% coding. 100% automation.
Flexter is available in three editions. An enterprise edition, which can be installed on-premise or in your cloud. A managed SaaS edition Flexter as a Service. You can also use Flexter for free.

The XML and JSON data sets

We will use an XML data set and a JSON data set.
The XML data set is a collection of Covid related news articles from around the world. The Covid XML data set is compiled and kept up to date by Medisys.
The JSON data set is a collection of JSON documents related to the sport of Rugby.

Converting Covid XML to Yellowbrick Data Warehouse

Converting our XML data set to Yellowbrick Data Warehouse can be performed in a few simple steps.

Step 1 – Create a data flow

Flexter can use a sample of XML documents, an XSD (if available), or a combination of the two to create a data flow, which is a relational target model and its mappings to the XML / JSON documents.
Flexter collects information such as the data types, the structure of the XML, the XPaths, the names of XML elements etc. from the XSD or sample of XMLs. Using this information, Flexter generates a relational data model and the mappings between source XML and destination tables. This mapping is called a data flow. The information is stored in Flexter’s metadata database.

Step 2 – Convert the XML data

We use the data flow we created in step 1 to convert the XML documents to Yellowbrick. Each time we want to convert new XML data we use the data flow we generated in Step 1.

Step 3 – Querying the data with SQL on Yellowbrick

We query the data that we converted to Yellowbrick with SQL.
Let’s look at the steps in detail.

Step 1 – Create a data flow

For the Covid XML data set no XSD has been made available. We use a sample of XML documents to create the data flow. We analyse the sample and collect metadata such as the data types or relationships inside the sample.
We run the xml2er command

Example of output

Step 2 – Convert the data

The Medisys XML documents are encoded in UTF-8 . Here is a screenshot of an example document.

We need to set up a Yellowbrick database with UTF-8 encoding to load Unicode character sets correctly.

We can then convert the XML to Yellowbrick tables using Flexter’s xml2er command line tool.

In the output we get some key information on the status of the conversion, e.g. how long each phase took, how many different XPaths were processed, how many documents were converted successfully etc.

 
Flexter generated the following data model

Step 3 – Querying Covid data

Now that we have converted the Covid XML documents to Yellowbrick, we can take a closer look at a couple of tables. In table “item” we find information on the journal articles such as the title of the article, an abstract, and the sentiment of the article.




All articles have a sentiment attribute, which represents the sentiment the readers feel after reading them.
In the next step we will run a simple SQL query which will show us a summary of the sentiment across all articles in the dataset.
Negative sentiment prevails…

Reading XML from Yellowbrick Data Warehouse

Flexter is also able to use Yellowbrick as a source of XML data and read XML documents stored inside a table column. This is a very handy feature.
We can store the output of Flexter directly in Yellowbrick or one of the other supported formats. In this example we convert the XML from Yellowbrick to text files.
Let’s first load some XML documents to Yellowbrick.
We first create the table in Yellowbrick

Next step is to load our XML documents to the table. We can do that by using some simple SQL queries. XML CONTENT represents the content of the XML document in the query below.

We are now ready to kick off the conversion

Step 1 – Create the data flow

In this step we will read XML data, collect metadata and create a data flow. The -T switch tells Flexter which table to read from. The -C switch provides the column name containing the XML documents.

Step 2 – Convert the data

Example of output

Once the conversion has completed successfully we will get the output as files in the specified folder.

 

Converting JSON data to Yellowbrick

For the last part of this post, we will use the json2er command line tool, which allows you to convert JSON files to the Yellowbrick Data Warehouse in a couple of simple steps. The process is the same as for XML.
We first create the data flow from a sample of representative JSON documents and then re-use the data flow to convert the data.

Step 1 – Create the data flow

In this step we will collect JSON data statistics and create a data flow. We derive the data types, relationships, create the target data model, and the mappings. This metadata is stored in the Flexter metadata catalog.

Output

Step 2 – Convert the data

In this step we convert our JSON documents to the Yellowbrick Data Warehouse.

Example of output

As you can see, the generated schema is relatively complex.

Step 3 – Querying Rugby data

We can now run an SQL query where we will retrieve the players from the Gloucester Rugby team.

We will use next SQL Query

 
 

Conclusion

We have shown you how you can easily convert and query XML and JSON documents in a few minutes. We didn’t have to write a single line of code. The data is available in an instant in the Yellowbrick data warehouse.
On Yellowbrick data analysts can use familiar tools such as SQL, the lingua franca of data to answer complex questions and generate meaningful insights for decision makers.