Accessing Amazon QLDB using the console - Amazon Quantum Ledger Database (Amazon QLDB)
PartiQL editor quick reference

Accessing Amazon QLDB using the console

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

You can access the AWS Management Console for Amazon QLDB at https://console.aws.amazon.com/qldb.

You can use the console to do the following in QLDB:

  • Create, delete, describe, and list ledgers.

  • Run PartiQL statements by using the PartiQL editor.

  • Manage tags for QLDB resources.

  • Verify journal data cryptographically.

  • Export or stream journal blocks.

To learn how to create an Amazon QLDB ledger and set it up with sample application data, see Getting started with the Amazon QLDB console.

PartiQL editor quick reference

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

Amazon QLDB supports a subset of PartiQL as its query language and Amazon Ion as its document-oriented data format. For a complete guide and more detailed information about the QLDB implementation of PartiQL, see the Amazon QLDB PartiQL reference.

The following topics provide a quick reference overview of how to use PartiQL in QLDB.

PartiQL quick tips in QLDB

The following is a short summary of tips and best practices for working with PartiQL in QLDB:

  • Understand concurrency and transaction limits – All statements, including SELECT queries, are subject to optimistic concurrency control (OCC) conflicts and transaction limits, including a 30-second transaction timeout.

  • Use indexes – Use high-cardinality indexes and run targeted queries to optimize your statements and avoid full table scans. To learn more, see Optimizing query performance.

  • Use equality predicates – Indexed lookups require an equality operator (= or IN). Inequality operators (<, >, LIKE, BETWEEN) don't qualify for indexed lookups and result in full table scans.

  • Use inner joins only – QLDB supports inner joins only. As a best practice, join on fields that are indexed for each table that you're joining. Choose high-cardinality indexes for both the join criteria and the equality predicates.

Commands

QLDB supports the following PartiQL commands.

Data definition language (DDL)
Command Description
CREATE INDEX Creates an index for a top-level document field on a table.
CREATE TABLE Creates a table.
DROP INDEX Deletes an index from a table.
DROP TABLE Deactivates an existing table.
UNDROP TABLE Reactivates an inactive table.
Data manipulation language (DML)
Command Description
DELETE Marks an active document as deleted by creating a new, final revision of the document.
FROM (INSERT, REMOVE, or SET) Semantically the same as UPDATE.
INSERT Adds one or more documents to a table.
SELECT Retrieves data from one or more tables.
UPDATE Updates, inserts, or removes specific elements within a document.

DML statement examples

INSERT

INSERT INTO VehicleRegistration VALUE
{
    'VIN' : 'KM8SRDHF6EU074761', --string
    'RegNum' : 1722, --integer
    'PendingPenaltyTicketAmount' : 130.75, --decimal
    'Owners' : { --nested struct
        'PrimaryOwner' : { 'PersonId': '294jJ3YUoH1IEEm8GSabOs' },
        'SecondaryOwners' : [ --list of structs
            { 'PersonId' : '1nmeDdLo3AhGswBtyM1eYh' },
            { 'PersonId': 'IN7MvYtUjkp1GMZu0F6CG9' }
        ]
    },
    'ValidToDate' : `2020-06-25T` --Ion timestamp literal with day precision
}

UPDATE-INSERT

UPDATE Vehicle AS v
INSERT INTO v VALUE 26500 AT 'Mileage'
WHERE v.VIN = '1N4AL11D75C109151'

UPDATE-REMOVE

UPDATE Person AS p
REMOVE p.Address
WHERE p.GovId = '111-22-3333'

SELECT – Correlated subquery

SELECT r.VIN, o.SecondaryOwners
FROM VehicleRegistration AS r, @r.Owners AS o
WHERE r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

SELECT – Inner join

SELECT v.Make, v.Model, r.Owners
FROM VehicleRegistration AS r INNER JOIN Vehicle AS v
ON r.VIN = v.VIN
WHERE r.VIN IN ('1N4AL11D75C109151', 'KM8SRDHF6EU074761')

SELECT – Get document ID using BY clause

SELECT r_id FROM VehicleRegistration AS r BY r_id
WHERE r.VIN = '1HVBBAANXWH544237'

System-defined views

QLDB supports the following system-defined views of a table.

View Description
table_name The default user view of a table that includes the current state of your user data only.
_ql_committed_table_name The full system-defined committed view of a table that includes the current state of both your user data and system-generated metadata, such as a document ID.
history(table_name) The built-in history function that returns the complete revision history of a table.

Basic syntax rules

QLDB supports the following basic syntax rules for PartiQL.

Character Description
' Single quotes denote string values, or field names in Amazon Ion structures.
" Double quotes denote quoted identifiers, such as a reserved word that is used as a table name.
` Backticks denote Ion literal values.
. Dot notation accesses field names of a parent structure.
[ ] Square brackets define an Ion list, or denote a zero-based ordinal number for an existing list.
{ } Curly braces define an Ion struct.
<< >> Double angle brackets define a PartiQL bag, which is an unordered collection. You use a bag to insert multiple documents into a table.
Case sensitivity All QLDB system object names—including field names and table names—are case sensitive.

PartiQL editor keyboard shortcuts

The PartiQL editor on the QLDB console supports the following keyboard shortcuts.

Action macOS Windows
Run Cmd+Return Ctrl+Enter
Comment Cmd+/ Ctrl+/
Clear Cmd+Shift+Delete Ctrl+Shift+Delete