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
xml2er -g1 <FILE PATH>
Example of output
18:21:13.316 INFO Registering success of job 62 18:21:13.331 INFO Finished successfully in 3392260 milliseconds # schema origin: 29 logical: 7 job: 62 # statistics startup: 2326 ms load: 154 ms xpath stats: 137590 ms doc stats: 285597 ms parse: 141479 ms write: 2825066 ms xpaths: 52 | map:100%/52 new:0%/0 documents: 1233 | suc:92.4%/1140 part:7.5%/93 fail:0%/0 size:1.2GB
overall status: 99.9% | map:100% conv:99.9% fks:100% occur:100% warn:0%
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.
xml2er -x29 -o “jdbc:postgresql://<host>:<port>/db_name?yb=1?currentSchema=yb” -u <user> -p <password> -S o /home/user/samples/covid.xml
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.
20:28:15.900 INFO Registering success of job 64 20:28:15.912 INFO Finished successfully in 22516 milliseconds # schema origin: 13 logical: 6 job: 64 # statistics startup: 1895 ms load: 160 ms xpath stats: 9474 ms doc stats: 1609 ms parse: 123 ms write: 9232 ms xpaths: 441| map:100%/441 new:0%/0 documents: 1 | suc:100%/1 part:0%/0 fail:0%/0 size:655.2KB overall status:100%| map:100% conv:100% fks:100% occur:100%
warn:0%
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
create table xml_table (pk_col bigint, xml_col varchar(64000);
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.
insert into xml_table (pk_col, xml_col) values (1, ‘XML CONTENT1’);
insert into xml_table (pk_col, xml_col) values (2, ‘XML CONTENT2’);
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.
xml2er -g1 “jdbc:postgresql://<DATABASE URL>” -U <USER> -P <PASSWORD> -T xml_table -C xml_col 13:26:10.489 INFO Registering success of job 61 13:26:10.502 INFO Finished successfully in 3954923 milliseconds # schema origin: 22 logical: 16 job: 61 # statistics startup: 1870 ms load: 108 ms xpath stats: 172898 ms doc stats: 362092 ms parse: 177586 ms write: 3240341 ms xpaths: 52 | map:100%/52 new:0%/0 documents: 1233 | suc:92.4%/1140 part:7.5%/93 fail:0%/0 size:1.2GB
overall status: 99.9% | map:100% conv:99.9% fks:100% occur:100% warn:0%
Step 2 - Convert the data
We can now convert the data using the data flow we created in the previous step.
In this scenario we convert the XML documents stored in Yellowbrick to text files (TSV) for downstream processing by data scientists.
Note: We also could have converted from Yellowbrick XML to Yellowbrick relational format.
We have specified a folder for -o as the folder path.
xml2er -x <ORIGIN ID> -o <OUTPUT FOLDER PATH> -S o “jdbc:postgresql://<DATABASE URL>” -U <USER> -P <PASSWORD> -T xml_table -C xml_col
Example of output
13:26:10.489 INFO Registering success of job 61 13:26:10.502 INFO Finished successfully in 3954923 milliseconds # schema origin: 29 logical: 15 job: 61 # statistics startup: 1870 ms load: 108 ms xpath stats: 172898 ms doc stats: 362092 ms parse: 177586 ms write: 3240341 ms xpaths: 52 | map:100%/52 new:0%/0 documents: 1233 | suc:92.4%/1140 part:7.5%/93 fail:0%/0 size:1.2GB
overall status: 99.9% | map:100% conv:99.9% fks:100% occur:100% warn:0%
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.
json2er -g1 <FILE PATH>
Output
# schema origin: 70 logical: 17 job: 65 # statistics startup: 1870 ms load: 108 ms xpath stats: 172898 ms doc stats: 362092 ms parse: 177586 ms write: 3240341 ms xpaths: 52 | map:100%/52 new:0%/0 documents: 1233 | suc:92.4%/1140 part:7.5%/93 fail:0%/0 size:1.2GB
overall status: 99.9% | map:100% conv:99.9% fks:100% occur:100% warn:0%
Step 2 - Convert the data
In this step we convert our JSON documents to the Yellowbrick Data Warehouse.
json2er -x <origin ID> -o “jdbc:postgresql://<DATABASE URL>” -u <user> -p <password> -S o <FILE PATH>
Example of output
# schema origin: 70 logical: 18 job: 66 # statistics startup: 1895 ms load: 160 ms xpath stats: 9474 ms doc stats: 1609 ms parse: 123ms write: 9232ms xpaths: 441| map:100%/441 new:0%/0 documents:1| suc:100%/1 part:0%/0 fail:0%/0 size:655.2KB
overall status:100%| map:100% conv:100% fks:100% occur:100% warn:0%
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
SELECT p2.”team_displayName” , a.”athlete_displayName” FROM sonra_db.rugby_out.players p2 INNER JOIN sonra_db.rugby_out.BOXSCORE_PLAYERS_STATISTICS AS b ON p2.”PK_players” = b.”FK_players” INNER JOIN sonra_db.rugby_out.ATHLETES AS a ON b.”PK_boxscore_players_statistics” = a.”FK_statistics”
WHERE p2.”team_displayName” = ‘Gloucester Rugby’;
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.