SQL-Compatible Query Language: PartiQL

Introduction to PartiQL

PartiQL offers SQL-compatible query access, across numerous data repositories that comprise structured data, semi-structured data, and layered data. It is widely utilized by Amazon and is currently offered as a component of several AWS services, including DynamoDB.

Customized Cloud Solutions to Drive your Business Success

Cloud Migration
Devops
AIML & IoT

Know More

Features of PartiQL

AWS R&D engineers have created the partiql language to make it easier to query different semi-structured data that they got from Amazon’s retail division.

Nested data sets: It provides the ability to query nested data sets provided with first-class.

Query Stability and Optional Schema: The language is compatible with both schema-based NoSQL engines and relational database engines.

Independent Format: Partiql is independent of all types of data formats. A query can be created to access data in other formats, such as JSON, Parquet, ORC, CSV, Ion, etc.,

Minimal Extensions: Compared to SQL, PartiQL has the fewest extensions, which provide logical windowing, aggregating, joining, and filtering on a combination of structured, semi-structured, and nested datasets.

Use Case

PartiQL query language is compatible with SQL and can be used with Amazon DynamoDB to select, insert, update, and delete data. Amazon DynamoDB, a key-value, and document database offer exceptional performance at scale. The availability, latency, and performance of PartiQL operations are equivalent to those of conventional DynamoDB data plane operations.

How PartiQL works?

The reference implementation for PartiQL. The lexer, parser, and compiler for PartiQL query expressions are open sourced. It offers a library that may be integrated into another application or used independently to execute queries. Using this library, a user might either embed a PartiQL evaluator to process data inside their system or use it to validate PartiQL queries. The library offers a data interface that may be bound to any type of data back end your application might have, and it comes pre-configured to support JSON and Ion.

Developers will find it simple to parse and incorporate PartiQL into their applications thanks to the PartiQL open source. Users can parse PartiQL questions into abstract syntax trees for their applications to review or process, and the implementation also enables direct processing of PartiQL queries.

working

Ways to Use PartiQL

One way is we can find the PartiQL editor embedded in the AWS account in the DynamoDB service as shown in the below picture.

way1

Another way is we can directly use the boto3 library as shown in the below examples.

1. Create a table in DynamoDB with a Partition key and sort key as shown below:

way2

Now insert any data to our demo table using boto3 you can try this code from AWS lambda as well.

import boto3
client = boto3.client('dynamodb')
stmt = " INSERT INTO demo value {'demo_id' : '1', 'name' : 'James', 'pid': '101'}"
response = client.execute_statement(Statement=stmt)
print(response)

import boto3

client = boto3.client('dynamodb')

stmt = " INSERT INTO demo value {'demo_id' : '1', 'name' : 'James', 'pid': '101'}"

response = client.execute_statement(Statement=stmt)

print(response)

Now let’s check whether our data was inserted properly or not, using the select query:

import boto3
client = boto3.client('dynamodb')
stmt = "SELECT * FROM demo"  
response = client.execute_statement(Statement=stmt)
print(response["Items"])

import boto3

client = boto3.client('dynamodb')

stmt = "SELECT * FROM demo"

response = client.execute_statement(Statement=stmt)

print(response["Items"])

way3

4. Now let’s use the update statement to update the existing record

import boto3
client = boto3.client('dynamodb')
stmt = "UPDATE demo SET name='Hari' where demo_id='1'" 
response = client.execute_statement(Statement= stmt)
print(response)

import boto3

client = boto3.client('dynamodb')

stmt = "UPDATE demo SET name='Hari' where demo_id='1'"

response = client.execute_statement(Statement= stmt)

print(response)

You invoke the execute statement method in the code above supplying the UPDATE SQL statement. The name for the demo_id = 1 record is being changed by the SQL statement. The response is then printed by the code.

way4

5. Similarly let’s check how the delete statement is written

import boto3
client = boto3.client('dynamodb')
stmt = "DELETE FROM demo WHERE demo_id = '1'"	
response = client.execute_statement(Statement= stmt)
print(response)

import boto3

client = boto3.client('dynamodb')

stmt = "DELETE FROM demo WHERE demo_id = '1'"

response = client.execute_statement(Statement= stmt)

print(response)

You invoke the execute statement method in the code above supplying the Delete SQL statement. The record with demo_id = 1 is being deleted by the SQL query. The response is then printed by the code.

Conclusion

In this blog, we have gone through the PartiQL purpose and its key features. We have explored other ways of querying AWS dynamo DB instead of APIs we used PartiQL query statements for scanning the data items which are present in the table. Also, it made easy to explore PartiQL as it’s an open source project.

Get your new hires billable within 1-60 days. Experience our Capability Development Framework today.

Cloud Training
Customized Training
Experiential Learning

About CloudThat

CloudThat is also the official AWS (Amazon Web Services) Advanced Consulting Partner and Training partner and Microsoft gold partner, helping people develop knowledge of the cloud and help their businesses aim for higher goals using best in industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.

Drop a query if you have any questions regarding PartiQL and I will get back to you quickly.

To get started, go through our Consultancy page and Managed Services Package that is CloudThat’s offerings.

FAQs

1. In which use case do we need PartiQL?

ANS: – When the SQL language is difficult to utilize to support nested or semi-structured data, PartiQL should be used instead. Use PartiQL if you believe that you will need to change the format or location of your data. When your schema changes over time or you have data that lacks a schema, you should use PartiQL.

2. What types of data models and formats can I utilize with PartiQL?

ANS: – Using the right serializer/deserializer, practically any tabular or hierarchical format, schema or not, may be mapped into PartiQL. Given that PartiQL and conventional SQL are backward compatible, relational data is also modeled.

WRITTEN BY Imraan Pattan

Imraan is a Software Developer working with CloudThat Technologies. He has worked on Python Projects using the Flask framework. He is interested in participating in competitive programming challenges and Hackathons. He loves programming and likes to explore different functionalities for creating backend applications.