Amazon introduces PartiQL, a SQL-compatible unifying query language for multi-valued, nested, and schema-less data

Yesterday, Amazon introduced a new SQL-compatible query language named PartiQL, which is a “unifying query language” that allows you to query data regardless of the database type and vendor.

Amazon has open-sourced the language’s lexer, parser, and, compiler under the Apache 2.0 license. The open-source implementation also provides an interactive shell or Read Evaluate Print Loop (REPL) using which you can quickly write and evaluate PartiQL queries.

Why PartiQL is introduced

Amazon’s business requires querying and transforming huge amounts and types of data that are not just limited to SQL tabular data but also nested and semi-structured data. The tech giant wants to make its relational database services like Redshift capable of accessing non-relational data while maintaining backward-compatibility with SQL. To address these requirements, Amazon created PratiQL that enables you to query data across a variety of formats and services in a simple and consistent way.

Here’s a diagram depicting the basic idea behind PartiQL:

amazon-introduces-partiql-a-sql-compatible-unifying-query-language-for-multi-valued-nested-and-schema-less-data-img-0

Source: Amazon

Many Amazon services are already using PratiQL including Amazon S3 Select, Amazon Glacier Select, Amazon Redshift Spectrum, Amazon Quantum Ledger Database (Amazon QLDB), and Amazon internal systems.

Advantages of using PartiQL

PartiQL is fully compatible with SQL: You will not have much trouble adopting PartiQL as it is fully compatible with SQL. All the existing queries that you are familiar with will work in SQL query processors that are extended to provide PartiQL.

Works with nested data: PartiQL treats nested data as a first-class citizen of the data abstraction. Its syntax and semantics enable users to “comprehensively and accurately access and query nested data.”

Format and datastore independent: PartiQL allows you to write the same query for all data formats as its syntax and semantics are not tied to a specific data format. To enable this behavior, the query operates on a logical type system that maps to diverse formats. Because of its expressiveness, you can use it with diverse underlying datastores.

Optional schema and query stability: You do not require to have a predefined schema over a dataset. It is built to work with engines that are schemaless or assume the presence of a schema.

Requires minimal extensions: It requires a minimum number of extensions as compared to SQL. These extensions for multi-valued, nested, and schema-less combine seamlessly with joining, filtering, and aggregation, and windowing capabilities of standard SQL.