Accessing Amazon QLDB using the console

You can access the AWS Management Console for Amazon QLDB here: 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

Amazon QLDB supports a subset of PartiQL as its query language and Amazon Ion as its document-oriented data format. For a complete guide 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	Inactivates 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 you use 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