Step 3: Query the tables in a ledger - Amazon Quantum Ledger Database (Amazon QLDB)

Step 3: Query the tables in a ledger

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.

After creating tables in an Amazon QLDB ledger and loading them with data, you can run queries to review the vehicle registration data that you just inserted. QLDB uses PartiQL as its query language and Amazon Ion as its document-oriented data model.

PartiQL is an open-source, SQL-compatible query language that has been extended to work with Ion. With PartiQL, you can insert, query, and manage your data with familiar SQL operators. Amazon Ion is a superset of JSON. Ion is an open-source, document-based data format that gives you the flexibility of storing and processing structured, semistructured, and nested data.

In this step, you use SELECT statements to read data from the tables in the vehicle-registration ledger.

Warning

When you run a query in QLDB without an indexed lookup, it invokes a full table scan. PartiQL supports such queries because it's SQL compatible. However, don't run table scans for production use cases in QLDB. Table scans can cause performance problems on large tables, including concurrency conflicts and transaction timeouts.

To avoid table scans, you must run statements with a WHERE predicate clause using an equality operator on an indexed field or a document ID; for example, WHERE indexedField = 123 or WHERE indexedField IN (456, 789). For more information, see Optimizing query performance.

To query the tables

  1. Open the Amazon QLDB console at https://console.aws.amazon.com/qldb.

  2. In the navigation pane, choose PartiQL editor.

  3. Choose the vehicle-registration ledger.

  4. In the query editor window, enter the following statement to query the Vehicle table for a particular vehicle identification number (VIN) that you added to the ledger, and then choose Run.

    To run the statement, you can also use the keyboard shortcut Ctrl+Enter for Windows, or Cmd+Return for macOS. For more keyboard shortcuts, see PartiQL editor keyboard shortcuts.

    SELECT * FROM Vehicle AS v
WHERE v.VIN = '1N4AL11D75C109151'

  5. You can write inner join queries. This query example joins Vehicle with VehicleRegistration and returns registration information along with attributes of the registered vehicle for a specified VIN.

    Enter the following statement, and then choose Run.

    SELECT v.VIN, r.LicensePlateNumber, r.State, r.City, r.Owners
FROM Vehicle AS v, VehicleRegistration AS r
WHERE v.VIN = '1N4AL11D75C109151'
AND v.VIN = r.VIN

    You can also join the Person and DriversLicense tables to see attributes related to the drivers who were added to the ledger.

    Repeat this step for the following.

    SELECT * FROM Person AS p, DriversLicense AS l
WHERE p.GovId = l.LicensePlateNumber

To learn about modifying documents in the tables in the vehicle-registration ledger, see Step 4: Modify documents in a ledger.