Accessing Amazon QLDB using the QLDB shell (data plane only) - Amazon Quantum Ledger Database (Amazon QLDB)

Accessing Amazon QLDB using the QLDB shell (data plane only)

Amazon QLDB provides a command line shell for interaction with the transactional data plane. The QLDB shell enables you to run PartiQL statements on ledger data. This shell is written in Python and is open-sourced in the GitHub repository awslabs/amazon-qldb-shell.

Note

The Amazon QLDB shell only supports the qldb-session transactional data API. This API is used only for running PartiQL statements on a QLDB ledger.

To interact with the qldb control plane API actions using a command line interface, see Accessing Amazon QLDB using the AWS CLI (control plane only).

This tool is not intended to be incorporated into an application or adopted for production purposes. The objective of this tool is to let you rapidly experiment with QLDB and PartiQL.

The following sections describe how to get started with the QLDB shell.

Prerequisites

Before you get started with the QLDB shell, you must do the following:

  1. Follow the AWS setup instructions in Accessing Amazon QLDB. This includes the following:

    1. Sign up for AWS.

    2. Create an AWS Identity and Access Management (IAM) user with the appropriate QLDB permissions.

    3. Get an IAM access key for development.

  2. Install Python version 3.4 or later from the Python downloads site.

  3. Set up your AWS credentials and your default AWS Region. For instructions, see Quickstart in the AWS SDK for Python (Boto3) documentation.

    For a complete list of available Regions, see Amazon QLDB endpoints and quotas in the AWS General Reference.

  4. For any ledgers in the STANDARD permissions mode, create AWS Identity and Access Management (IAM) policies that grant you permissions to run PartiQL statements on the appropriate tables.

    Important

    In addition to these permissions, you specifically need the qldb:PartiQLSelect permission on the ledger's catalog resource information_schema/user_tables. The shell requires this access to query the system catalog table information_schema.user_tables. Without this permission, the shell fails to initialize.

    To learn how to create these policies, see Getting started with the standard permissions mode in Amazon QLDB.

Setting up the shell

To install the QLDB shell from PyPI using pip3 (a package manager for Python 3), run the following at your command line terminal.

$ pip3 install qldbshell

Installing the shell also installs the following required package dependencies:

  • pyqldb – Required only up to version 1.1.0 of the shell. Starting with version 1.2.0, the shell packages the QLDB driver internally and no longer requires this package to be installed as a separate dependency.

  • amazon.ion

  • argparse

  • boto3

The shell is not compatible with the latest version (3.x) of the QLDB driver for Python (pyqldb). If you have the latest version installed, see Resolving the driver dependency for additional setup instructions.

Upgrading the shell

If you already have a previous version of the shell installed, run the following commands to upgrade it to the latest version.

Note

Version 1.1.0 of the QLDB shell introduces support for running multiple statements in a single transaction, and running statements that span multiple lines.

The latest version of the shell is backwards compatible, so upgrading doesn't break any previous versions.

  1. Upgrade your shell to the latest version.

    $ pip3 install --upgrade qldbshell
  2. Uninstall the QLDB driver. The latest version of the shell no longer requires this package to be installed as a separate dependency.

    $ pip3 uninstall pyqldb
  3. To check the current version of your shell, run the following command to list your installed packages.

    $ pip3 list

    The shell also displays the current version after you invoke it.

Invoking the shell

To invoke the QLDB shell on your command line terminal for a specific ledger, run the following command. Replace test-ledger with your ledger name.

$ qldbshell --ledger test-ledger

This command connects to your default AWS Region. To explicitly specify the Region, you can run the command with the --region parameter, as described in the following section.

After invoking a qldbshell session, you can enter PartiQL statements by using the following methods:

For a list of supported keys and commands, see the Command reference.

Connection parameters

To see a list of available input arguments, run the following command before you invoke a qldbshell session.

$ qldbshell --help

The following connection parameters are available for the qldbshell command. You can add the optional arguments to override the AWS Region, credentials profile, and endpoint that the shell uses.

Usage syntax:

$ qldbshell -l LEDGER_NAME [-v] [-p PROFILE] [-r REGION_CODE] [-s QLDB_SESSION_ENDPOINT]

Required arguments

-l LEDGER_NAME
--ledger LEDGER_NAME

Specifies the name of the ledger that the shell connects to. The ledger name must already exist and must be active.

Optional arguments

-v
--verbose

Increases output verbosity in your shell session.

-p PROFILE
--profile PROFILE

Specifies the location of your AWS credentials profile that the shell uses for authentication.

If not provided, the shell uses your default AWS profile, which is located at ~/.aws/credentials.

-r REGION_CODE
--r region REGION_CODE

Specifies the AWS Region code of the QLDB ledger that the shell connects to. For example: us-east-1.

If not provided, the shell connects to your default AWS Region as specified in your AWS profile.

-s QLDB_SESSION_ENDPOINT
--qldb-session-endpoint QLDB_SESSION_ENDPOINT

Specifies the qldb-session API endpoint that the shell connects to.

For a complete list of available QLDB Regions and endpoints, see Amazon QLDB endpoints and quotas in the AWS General Reference.

Parameter file

As an alternative to passing arguments on the command line, you can also save input parameters in a file. Put each parameter on a separate line. Then, pass the file name to the qldbshell as follows. Replace params.conf with your file name.

$ qldbshell @params.conf

Command reference

After you invoke a qldbshell session, the shell supports the following keys and commands:

QLDB shell keys
Key Function description
Enter Runs the statement.

Option+Enter (macOS)

Escape+Enter (Windows)

Starts a new line to enter a statement that spans multiple lines. For instructions on setting up the Option key as a Meta key in macOS, see the OS X Daily site.

You can also copy input text with multiple lines and paste it into the shell.

Tab Autocompletes a partial word and suggests QLDB PartiQL reserved words and active table names in the current ledger.
Ctrl+C Cancels the current command.
Ctrl+D Exits the current qldbshell session.
QLDB shell commands
Command Function description
help Displays the help section.
start Begins a transaction.
commit Commits your transaction to the ledger's journal.
abort Cancels your transaction and rejects any changes that you made.
clear Clears the screen.
exit Exits the current qldbshell session.
quit
Note

All QLDB shell commands are case insensitive.

Running individual statements

Except for the QLDB shell commands listed in the Command reference, the shell interprets each command that you enter as a separate PartiQL statement. By default, the shell implicitly runs each statement in its own transaction and automatically commits the transaction if no errors are found.

Managing transactions

Alternatively, the QLDB shell lets you manually control transactions. You can run multiple statements within a transaction interactively, or non-interactively by batching commands and statements sequentially.

Interactive transactions

To run an interactive transaction, do the following steps.

  1. To begin a transaction, enter the start command.

    qldbshell > start

    After you start a transaction, the command prompt displays the current system-assigned transaction ID. For example, if your transaction ID is FMoVdWuPxJg3k466Iz4i75, the shell displays the following command prompt.

    qldbshell (tx:FMoVdWuPxJg3k466Iz4i75) >
  2. Then, each statement that you enter runs in the same transaction.

    • For example, you can run a single statement as follows.

      qldbshell (tx:FMoVdWuPxJg3k466Iz4i75) > SELECT * FROM Vehicle WHERE VIN = '1N4AL11D75C109151'

      After you press Enter, the shell displays the results of the statement.

    • You can also enter multiple statements or commands separated by a semicolon as follows.

      qldbshell (tx:FMoVdWuPxJg3k466Iz4i75) > SELECT * FROM Vehicle WHERE VIN = '1N4AL11D75C109151'; commit
  3. To end the transaction, enter one of the following commands.

    • Enter the commit command to commit your transaction to the ledger's journal.

      qldbshell (tx:FMoVdWuPxJg3k466Iz4i75) > commit Transaction with transaction ID FMoVdWuPxJg3k466Iz4i75 committed
    • Enter the abort command to cancel your transaction and reject any changes that you made.

      qldbshell (tx:FMoVdWuPxJg3k466Iz4i75) > abort Transaction with transaction ID FMoVdWuPxJg3k466Iz4i75 aborted

Transaction timeout limit

An interactive transaction adheres to QLDB's transaction timeout limit. If you don't commit a transaction within 30 seconds of starting it, QLDB automatically expires the transaction and rejects any changes made during the transaction.

Then, instead of displaying the statement results, the shell displays an expiration error message and returns to the normal command prompt. You must enter the start command again to begin a new transaction.

Transaction with ID FMoVdWuPxJg3k466Iz4i75 expired. Aborting transaction.

Non-interactive transactions

You can run a complete transaction with multiple statements by batching commands and statements sequentially as follows.

qldbshell > start; SELECT * FROM Vehicle WHERE VIN = '1N4AL11D75C109151'; SELECT * FROM Person p, DriversLicense l WHERE p.GovId = l.LicenseNumber; commit

You must separate each command and statement with a semicolon (;). If any statement in the transaction isn't valid, the shell automatically rejects the transaction. The shell doesn't proceed with any subsequent statements that you entered.

You can also set up multiple transactions.

qldbshell > start; statement1; commit; start; statement2; statement3; commit

Similar to the previous example, if a transaction fails, the shell doesn't proceed with any subsequent transactions or statements that you entered.

If you don't end a transaction, the shell switches to interactive mode and prompts you for the next command or statement.

qldbshell > start; statement1; commit; start qldbshell (tx:FMoVdWuPxJg3k466Iz4i75) >

Exiting the shell

To exit the current qldbshell session and close the current ledger database connection, run the exit or quit command.

qldbshell > exit $
qldbshell > quit $

Example

For information about writing PartiQL statements in QLDB, see the Amazon QLDB PartiQL reference.

The following example shows a common sequence of basic commands.

Note

The QLDB shell runs each PartiQL statement in this example in its own transaction.

This example assumes that the ledger test-ledger already exists and is active.

$ qldbshell --ledger test-ledger --region us-east-1 qldbshell > CREATE TABLE TestTable qldbshell > INSERT INTO TestTable `{"Name": "John Doe"}` qldbshell > SELECT * FROM TestTable qldbshell > DROP TABLE TestTable qldbshell > exit

Common errors

This section provides instructions to resolve common errors that you might encounter while using the QLDB shell.

Resolving the driver dependency

The QLDB shell (up to version 1.1.0) requires the QLDB driver for Python version 2.x (2.0.0, 2.0.1, or 2.0.2) to be installed as a separate dependency. If you have an incompatible version of the driver installed, you might see one of the following error messages.

ModuleNotFoundError: No module named 'pyqldb.driver.pooled_qldb_driver'
ERROR: pyqldb 3.0.0rc1 has requirement amazon.ion<0.6,>=0.5.0, but you'll have amazon-ion 0.6.0 which is incompatible.

To resolve this driver dependency issue, you can downgrade to an earlier version. Or, you can run the shell in a virtual environment, which installs an older version of the driver.

To downgrade the driver to version 2.x

  1. Uninstall the current version of the driver.

    $ pip3 uninstall pyqldb
  2. Install version 2.0.2 of the driver.

    Note

    You can skip this step if you are using version 1.2.0 or later of the shell. The latest version of the shell packages the QLDB driver internally and no longer requires this package to be installed separately.

    $ pip3 install pyqldb==2.0.2

To run the shell in a virtual environment

  1. Install virtualenv.

    $ pip3 install virtualenv
  2. Create your virtual environment. You can replace venv with your own environment name.

    $ virtualenv venv
  3. Activate your virtual environment. Replace venv with the environment name that you created in the previous step.

    $ source venv/bin/activate
  4. Install the QLDB shell.

    $ pip3 install qldbshell
  5. Proceed to Invoking the shell to run the shell in your virtual environment.

  6. After you finish using the shell, you can deactivate the virtual environment.

    $ deactivate