Converting RESQML XML to Athena

October 25, 2019

In a previous post we converted Energistics XML in the PRODML standard. Today we will continue converting Energistics standards. For this showcase we will use Sonra’s data warehouse automation tool for XML, JSON, and industry data standards Flexter to convert RESQML data to S3 and then use Athena to Query the data.

RESQML

RESQML is a data-exchange standard used for reservoir life cycle data.
The exploration and production of subsurface workflow is long, complex with a lot of iterations. Since a lot of people from different disciplines are involved, there is a use of many different software packages for complex analysis, modeling, simulation and interpretation. Because of those different software packages users have to move data back and forth between them.
RESQML enables developers to implement the processes into software packages, since it consists of a set of XML schemas, and with those implementations it reduces productivity loss, data loss, knowledge loss, sharing uncertainty and more by facilitating a reliable, automated exchange of data among software packages used in subsurface workflows.

Processing XML with Flexter

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to S3 can be performed 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 (RESQML XML)

In a second step we will upload our RESQML XML source data

Example of output

Step 3 – Define Target Connection (S3)

Since we don’t have a Source Schema (XSD) 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 S3 database.

Example of output


Step 4 – Convert XML data from Source Connection (Upload) to Target Connection (S3)

In the last step we convert RESQML XML. Data will be written to S3 Server Target Connection.

Example of output

ER Diagram

We can create and download an ER Diagram of the model that Flexter generated by making a GET call. You can download the ER Diagram here.

Example of output

Creating Tables on Athena

After we have converted our data to S3, we have to create a Database and Tables in AWS Athena in the Athena Catalog Manager so that we can run an SQL Query. You can find instructions on how to do that here.
Once we have created our Tables we will run an SQL Query.

Conclusion

We have shown you how to convert RESQML XML data to S3. With Flexter it’s a simple and fast way process.
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.