Accessing Amazon QLDB using the console - Amazon Quantum Ledger Database (Amazon QLDB)

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