Converting RESQML to S3 and Athena

Uli Bethke Flexter, XML

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 as a Service (FaaS) 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 FaaS API

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

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

No manual coding
Cut cost by up to 80%

Find out more

ER Diagram

We can create and download an ER Diagram of the model that FaaS 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. If you are interested in Flexter you can try out the free version 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.