Amazon Quantum Ledger Database (Amazon QLDB)
Developer Guide

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

Step 2: Create Tables, Indexes, and Sample Data

When your Amazon QLDB ledger is active, you can start creating tables for data about vehicles, their owners, and their registration information. After creating the tables and indexes, you can load them with data.

In this step, you create four tables in the vehicle-registration ledger:

  • VehicleRegistration

  • Vehicle

  • Person

  • DriversLicense

You also create the following indexes.

Table Name Field
VehicleRegistration VIN
VehicleRegistration LicensePlateNumber
Vehicle VIN
Person GovId
DriversLicense LicenseNumber
DriversLicense PersonId

You can use the QLDB console to automatically create these tables with indexes and load them with sample data. Or, you can use the Query editor on the console to manually run each PartiQL statement step-by-step.

Automatic Option

To create tables, indexes, and sample data

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

  2. In the navigation pane, choose Getting started.

  3. Under Automatic option on the Sample application data card, choose vehicle-registration in the list of ledgers.

  4. Choose Load sample data.

    If the operation finishes successfully, the console displays the message Sample data loaded.

    This script runs all statements in a single transaction. If any part of the transaction fails, every statement is rolled back, and an appropriate error message is displayed. You can retry the operation after addressing any issues.

    Note

    • One possible cause for a transaction failure is attempting to create duplicate tables. Your request to load sample data will fail if any of the following table names already exist in your ledger: VehicleRegistration, Vehicle, Person, and DriversLicense.

      Instead, try loading this sample data in an empty ledger.

    • This script runs parameterized INSERT statements. So, these PartiQL statements are recorded in your journal blocks with bind parameters instead of the literal data. For example, you might see the following statement in a journal block, where the question mark (?) is a variable placeholder for the document contents.

      INSERT INTO Vehicle ?
Manual Option

You insert documents into VehicleRegistration with an empty PrimaryOwner field, and into DriversLicense with an empty PersonId field. Later, you populate these fields with the system-assigned document id from the Person table.

As a best practice, use this document id metadata field as a foreign key. For more information, see Querying Document Metadata.

To create tables, indexes, and sample data

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

  2. In the navigation pane, choose Query editor.

  3. Choose the vehicle-registration ledger.

  4. Start by creating four tables. QLDB supports open content and does not enforce schema, so you don't specify attributes or data types.

    In the query editor window, enter the following statement, and then choose Run.

    CREATE TABLE VehicleRegistration

    Repeat this step for each of the following.

    CREATE TABLE Vehicle
    CREATE TABLE Person
    CREATE TABLE DriversLicense
  5. Next, create indexes that help speed up queries against each table.

    In the query editor window, enter the following statement, and then choose Run.

    CREATE INDEX ON VehicleRegistration (VIN)

    Repeat this step for the following.

    CREATE INDEX ON VehicleRegistration (LicensePlateNumber)
    CREATE INDEX ON Vehicle (VIN)
    CREATE INDEX ON Person (GovId)
    CREATE INDEX ON DriversLicense (LicenseNumber)
    CREATE INDEX ON DriversLicense (PersonId)
  6. After creating your indexes, you can start loading data into your tables. In this step, insert documents into the Person table with personal information about owners of the vehicles that the ledger is tracking.

    In the query editor window, enter the following statement, and then choose Run.

    INSERT INTO Person << { 'FirstName' : 'Raul', 'LastName' : 'Lewis', 'DOB' : `1963-08-19T`, 'GovId' : 'LEWISR261LL', 'GovIdType' : 'Driver License', 'Address' : '1719 University Street, Seattle, WA, 98109' }, { 'FirstName' : 'Brent', 'LastName' : 'Logan', 'DOB' : `1967-07-03T`, 'GovId' : 'LOGANB486CG', 'GovIdType' : 'Driver License', 'Address' : '43 Stockert Hollow Road, Everett, WA, 98203' }, { 'FirstName' : 'Alexis', 'LastName' : 'Pena', 'DOB' : `1974-02-10T`, 'GovId' : '744 849 301', 'GovIdType' : 'SSN', 'Address' : '4058 Melrose Street, Spokane Valley, WA, 99206' }, { 'FirstName' : 'Melvin', 'LastName' : 'Parker', 'DOB' : `1976-05-22T`, 'GovId' : 'P626-168-229-765', 'GovIdType' : 'Passport', 'Address' : '4362 Ryder Avenue, Seattle, WA, 98101' }, { 'FirstName' : 'Salvatore', 'LastName' : 'Spencer', 'DOB' : `1997-11-15T`, 'GovId' : 'S152-780-97-415-0', 'GovIdType' : 'Passport', 'Address' : '4450 Honeysuckle Lane, Seattle, WA, 98101' } >>
  7. Then, populate the DriversLicense table with documents that include driver's license information for each vehicle owner.

    In the query editor window, enter the following statement, and then choose Run.

    INSERT INTO DriversLicense << { 'LicenseNumber' : 'LEWISR261LL', 'LicenseType' : 'Learner', 'ValidFromDate' : `2016-12-20T`, 'ValidToDate' : `2020-11-15T`, 'PersonId' : '' }, { 'LicenseNumber' : 'LOGANB486CG', 'LicenseType' : 'Probationary', 'ValidFromDate' : `2016-04-06T`, 'ValidToDate' : `2020-11-15T`, 'PersonId' : '' }, { 'LicenseNumber' : '744 849 301', 'LicenseType' : 'Full', 'ValidFromDate' : `2017-12-06T`, 'ValidToDate' : `2022-10-15T`, 'PersonId' : '' }, { 'LicenseNumber' : 'P626-168-229-765', 'LicenseType' : 'Learner', 'ValidFromDate' : `2017-08-16T`, 'ValidToDate' : `2021-11-15T`, 'PersonId' : '' }, { 'LicenseNumber' : 'S152-780-97-415-0', 'LicenseType' : 'Probationary', 'ValidFromDate' : `2015-08-15T`, 'ValidToDate' : `2021-08-21T`, 'PersonId' : '' } >>
  8. Now, populate the VehicleRegistration table with vehicle registration documents. These documents include a nested Owners structure that stores the primary and secondary owners.

    In the query editor window, enter the following statement, and then choose Run.

    INSERT INTO VehicleRegistration << { 'VIN' : '1N4AL11D75C109151', 'LicensePlateNumber' : 'LEWISR261LL', 'State' : 'WA', 'City' : 'Seattle', 'PendingPenaltyTicketAmount' : 90.25, 'ValidFromDate' : `2017-08-21T`, 'ValidToDate' : `2020-05-11T`, 'Owners' : { 'PrimaryOwner' : { 'PersonId': '' }, 'SecondaryOwners' : [] } }, { 'VIN' : 'KM8SRDHF6EU074761', 'LicensePlateNumber' : 'CA762X', 'State' : 'WA', 'City' : 'Kent', 'PendingPenaltyTicketAmount' : 130.75, 'ValidFromDate' : `2017-09-14T`, 'ValidToDate' : `2020-06-25T`, 'Owners' : { 'PrimaryOwner' : { 'PersonId': '' }, 'SecondaryOwners' : [] } }, { 'VIN' : '3HGGK5G53FM761765', 'LicensePlateNumber' : 'CD820Z', 'State' : 'WA', 'City' : 'Everett', 'PendingPenaltyTicketAmount' : 442.30, 'ValidFromDate' : `2011-03-17T`, 'ValidToDate' : `2021-03-24T`, 'Owners' : { 'PrimaryOwner' : { 'PersonId': '' }, 'SecondaryOwners' : [] } }, { 'VIN' : '1HVBBAANXWH544237', 'LicensePlateNumber' : 'LS477D', 'State' : 'WA', 'City' : 'Tacoma', 'PendingPenaltyTicketAmount' : 42.20, 'ValidFromDate' : `2011-10-26T`, 'ValidToDate' : `2023-09-25T`, 'Owners' : { 'PrimaryOwner' : { 'PersonId': '' }, 'SecondaryOwners' : [] } }, { 'VIN' : '1C4RJFAG0FC625797', 'LicensePlateNumber' : 'TH393F', 'State' : 'WA', 'City' : 'Olympia', 'PendingPenaltyTicketAmount' : 30.45, 'ValidFromDate' : `2013-09-02T`, 'ValidToDate' : `2024-03-19T`, 'Owners' : { 'PrimaryOwner' : { 'PersonId': '' }, 'SecondaryOwners' : [] } } >>
  9. Lastly, populate the Vehicle table with documents describing the vehicles that are registered in your ledger.

    In the query editor window, enter the following statement, and then choose Run.

    INSERT INTO Vehicle << { 'VIN' : '1N4AL11D75C109151', 'Type' : 'Sedan', 'Year' : 2011, 'Make' : 'Audi', 'Model' : 'A5', 'Color' : 'Silver' }, { 'VIN' : 'KM8SRDHF6EU074761', 'Type' : 'Sedan', 'Year' : 2015, 'Make' : 'Tesla', 'Model' : 'Model S', 'Color' : 'Blue' }, { 'VIN' : '3HGGK5G53FM761765', 'Type' : 'Motorcycle', 'Year' : 2011, 'Make' : 'Ducati', 'Model' : 'Monster 1200', 'Color' : 'Yellow' }, { 'VIN' : '1HVBBAANXWH544237', 'Type' : 'Semi', 'Year' : 2009, 'Make' : 'Ford', 'Model' : 'F 150', 'Color' : 'Black' }, { 'VIN' : '1C4RJFAG0FC625797', 'Type' : 'Sedan', 'Year' : 2019, 'Make' : 'Mercedes', 'Model' : 'CLK 350', 'Color' : 'White' } >>

Next, you can use SELECT statements to read data from the tables in the vehicle-registration ledger. Proceed to Step 3: Query the Tables in a Ledger.