Converting Automotive Standard ASAM ODS XML to PostgreSQL

Uli Bethke Flexter, XML

In this blog post we will show you how to obfuscate ASAM ODS XML data and convert it to PostgreSQL. For this quick process which will just take a couple of minutes, we will use Sonra’s obfuscation tool Paranoid (open source) and Sonra’s data warehouse automation tool for XML, JSON, and industry data standards Flexter as a Service (FaaS).

FaaS follows a pay as you go model. Flexter is also available as a free edition and as an enterprise edition. The enterprise edition can be installed in the cloud or on-premise.

ASAM

ASAM (Association for Standardization of Automation and Measuring Systems) is a non-profit organization that promotes standardization for tool chains in automotive development and testing.

ASAM members are international car manufacturers, suppliers, tool vendors, engineering service providers and research institutes from the automotive industry. ASAM standards are developed in work groups, composed of experts from our member companies.

For this blog post we will look at the ODS (Open Data Services) standard. This standard has been developed for test data post-processing and evaluation.

Obfuscating ASAM ODS XML

To prevent sharing confidential data, we will start by obfuscating our XML data with Paranoid. To mask data we have to provide a path to our file and a path to an output location. Paranoid will create the folder automatically, no need to create it first.

Optionally Paranoid has the feature to mask individual elements inside of a document.

Let’s check the file after obfuscation.

Now we can start with converting our obfuscated ASAM ODS XML data to PostgreSQL.

Processing masked XML with FaaS API

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to PostgreSQL can be done in 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 (ASAM ODS XML)

In a second step we upload the ASAM ODS XML source data

Example of output

Step 4 - Define Target Connection (PostgreSQL)

Since we don’t have a Source Schema 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 PostgreSQL database.

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

Step 5 - Convert XML data from Source Connection (Upload) to Target Connection (PostgreSQL)

In last step we convert ASAM ODS XML. Data will be written to PostgreSQL Target Connection.

Example of output

Example of ER Diagram

You can download the ER Diagram for the converted ASAM ODS XML file here.

Next we will run an SQL Query that selects “project_iname” element and its attributes.

In the figure above you see the results of the query with obfuscated values.

Conclusion

We have shown you how easy it is to obfuscate and convert ASAM ODS XML with Paranoid and Flexter. 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.