Converting RESQML XML to Athena
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.
Table of Contents
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.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/oauth/token" \ --header "Content-Type: application/x-www-form-urlencoded" \ --header "Authorization: Basic NmdORDZ0MnRwMldmazVzSk5BWWZxdVdRZXRhdWtoYWI6ZzlROFdRYm5Ic3BWUVdCYzVtZ1ZHQ0JYWjhRS1c1dUg=" \ --data "username=XXXXXXXXX&password=XXXXXXXXX&grant_type=password" |
Example of output
|
1 2 3 4 5 6 7 8 |
{ "access_token": "eyJhbG........", "token_type": "bearer", "refresh_token": "..........", "expires_in": 43199, "scope": "read write", "jti": "9f75f5ad-ba38-4baf-843a-849918427954" } |
Step 2 – Define Source Connection (Upload) for Source Data (RESQML XML)
In a second step we will upload our RESQML XML source data
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/data_sources/resqml" \ --header "Authorization: Bearer <access_token>" \ --form "source_type=uploaded_file" \ --form "file=@<file_path>" \ --form "data_type=xml" |
Example of output
|
1 2 3 4 5 6 7 8 |
{ "name" : "resqml", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-06e2f2b9-84cb-4332-ae60-e10f99bf8ffd", "size" : 327765, "create_date" : "2019-10-15T13:41:02.423+0000" } |
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.
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/target_connections/resqml" \ --header "Authorization: Bearer <access_token>" \ --form "target_type=s3" \ --form "path=<s3://path>" \ --form "role_arn=<arn:::RoleWithAccess>" |
Example of output
|
1 2 3 4 5 6 |
{ "name" : "resqml", "target_type" : "s3", "path" : "s3://pathl", "create_date" : "2019-10-15T10:55:30.758+0000" } |
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.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/conversions/resqml" \ --header "Authorization: Bearer <access_token>" \ --form "data_source=resqml" \ --form "target=resqml" |
Example of output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
{ "name" : "resqml", "schema_source_connection" : null, "data_source_connection" : { "name" : "resqml", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-fa622ed6-6c93-4bd7-9901-b5d122187da3", "size" : 327765, "create_date" : "2019-10-15T13:47:50.480+0000" }, "target_connection" : { "name" : "resqml", "target_type" : "resqml", "path" : "s3://path", "create_date" : "2019-10-15T13:34:10.769+0000" }, "create_date" : "2019-10-15T13:48:04.705+0000", "status" : "I", "download_link" : null, "full_status" : "INITIALIZED" } |
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.
|
1 2 |
curl --location --request GET "https://api.sonra.io/conversions/resqml" \ --header "Authorization: Bearer <access_token>" |
Example of output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
{ "name" : "resqml", "schema_source_connection" : null, "data_source_connection" : { "name" : "resqml", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-68009cc1-1cc7-4aeb-ad3f-84dbf8ef5ebf", "size" : 327765, "create_date" : "2019-10-15T14:31:14.094+0000" }, "target_connection" : { "name" : "resqml", "target_type" : "s3", "path" : "s3://path", "create_date" : "2019-10-15T15:02:02.273+0000" }, "create_date" : "2019-10-15T15:02:24.844+0000", "status" : "C", "download_link" : null, "diagram_link" : "<ER Diagram Download Link>", "mapping_link" : "<Mapping Download Link>", "credit_usage" : 0, "full_status" : "COMPLETED" } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT SSFR.Citation_Creation ,SSFR.Citation_Format ,SSFR.Citation_Originator ,SSFR.Citation_Title ,SSFR.Citation_type ,SSFR.IsHomogeneous ,CNT.ContentType ,CNT.Count ,CNT.PatchIndex ,CNT.RepresentationIndex ,CNT.Title Contact_TIitle FROM SealedSurfaceFrameworkRepresen SSFR LEFT JOIN SealedContactRepresentation SCR ON SCR.FK_SealedSurfaceFrameworkRepre = SSFR.PK_SealedSurfaceFrameworkRepre LEFT JOIN Contact CNT ON CNT.FK_SealedContactRepresentation = SCR.PK_SealedContactRepresentation |
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.
