Step 2: Create tables, indexes, and sample data 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.

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`	`LicensePlateNumber`
`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 PartiQL editor on the console to manually run each PartiQL statement step-by-step.

To create tables, indexes, and sample data

Open the Amazon QLDB console at https://console.aws.amazon.com/qldb.
In the navigation pane, choose Getting started.
Under Automatic option on the Sample application data card, choose vehicle-registration in the list of ledgers.
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 ?

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.

Tip

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

Open the Amazon QLDB console at https://console.aws.amazon.com/qldb.
In the navigation pane, choose PartiQL editor.
Choose the vehicle-registration ledger.
Start by creating four tables. QLDB supports open content and doesn't enforce schema, so you don't specify attributes or data types.

In the query editor window, enter the following statement, 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.
```
CREATE TABLE VehicleRegistration
```
Repeat this step for each of the following.
```
CREATE TABLE Vehicle
```
```
CREATE TABLE Person
```
```
CREATE TABLE DriversLicense
```
Next, create indexes that optimize query performance for each table.

Important
QLDB requires an index to efficiently look up a document. Without an index, QLDB needs to do a full table scan when reading documents. This 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 (= or IN) on an indexed field or a document ID. For more information, see Optimizing query performance.

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 (LicensePlateNumber)
```
```
CREATE INDEX ON DriversLicense (PersonId)
```

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'
} >>

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
<< {
    'LicensePlateNumber' : 'LEWISR261LL',
    'LicenseType' : 'Learner',
    'ValidFromDate' : `2016-12-20T`,
    'ValidToDate' : `2020-11-15T`,
    'PersonId' : ''
},
{
    'LicensePlateNumber' : 'LOGANB486CG',
    'LicenseType' : 'Probationary',
    'ValidFromDate' : `2016-04-06T`,
    'ValidToDate' : `2020-11-15T`,
    'PersonId' : ''
},
{
    'LicensePlateNumber' : '744 849 301',
    'LicenseType' : 'Full',
    'ValidFromDate' : `2017-12-06T`,
    'ValidToDate' : `2022-10-15T`,
    'PersonId' : ''
},
{
    'LicensePlateNumber' : 'P626-168-229-765',
    'LicenseType' : 'Learner',
    'ValidFromDate' : `2017-08-16T`,
    'ValidToDate' : `2021-11-15T`,
    'PersonId' : ''
},
{
    'LicensePlateNumber' : 'S152-780-97-415-0',
    'LicenseType' : 'Probationary',
    'ValidFromDate' : `2015-08-15T`,
    'ValidToDate' : `2021-08-21T`,
    'PersonId' : ''
} >>

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' : []
    }
} >>

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.

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Step 1: Create a new ledger

Step 3: Query the tables

Step 2: Create tables, indexes, and sample data in a ledger

Important

To create tables, indexes, and sample data

Note

Tip

To create tables, indexes, and sample data

Important