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 a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

CloudThat is the first Indian Company to win the prestigious Microsoft Partner 2024 Award and is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 850k+ professionals in 600+ cloud certifications and completed 500+ consulting projects globally, CloudThat is an official AWS Premier Consulting Partner, Microsoft Gold Partner, AWS Training Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, AWS GenAI Competency Partner, Education Competency Partner, Amazon EKS Service Delivery Partner, AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, Amazon RDS Service Delivery Partner, AWS CloudFormation Service Delivery Partner, and many more.

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.