Creating tables with indexes and inserting documents

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 an Amazon QLDB ledger, your first step is to create a table with a basic CREATE TABLE statement. Tables consist of QLDB documents, which are datasets in Amazon Ion struct format.

Creating tables and indexes

Tables have simple, case-sensitive names with no namespaces. QLDB supports open content and doesn't enforce schema, so you don't define attributes or data types when creating tables.

CREATE TABLE VehicleRegistration

CREATE TABLE Vehicle

A CREATE TABLE statement returns the system-assigned ID of the new table. All system-assigned IDs in QLDB are universally unique identifiers (UUID) that are each represented in a Base62-encoded string.

Note

Optionally, you can define tags for a table resource while you're creating the table. To learn how, see Tagging tables on creation.

You can also create indexes on tables to optimize query performance.

CREATE INDEX ON VehicleRegistration (VIN)

CREATE INDEX ON VehicleRegistration (LicensePlateNumber)

CREATE INDEX ON Vehicle (VIN)

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.

Note the following constraints when creating indexes:

• An index can only be created on a single top-level field. Composite, nested, unique, and function-based indexes are not supported.

• You can create an index on any Ion data types, including list and struct. However, you can only do the indexed lookup by equality of the whole Ion value regardless of the Ion type. For example, when using a list type as an index, you can't do an indexed lookup by one item inside the list.

• Query performance is improved only when you use an equality predicate; for example, WHERE indexedField = 123 or WHERE indexedField IN (456, 789).

  QLDB doesn't honor inequalities in query predicates. As a result, range filtered scans are not implemented.

• Names of indexed fields are case sensitive and can have a maximum of 128 characters.

• Index creation in QLDB is asynchronous. The amount of time it takes to finish building an index on a non-empty table varies depending on the table size. For more information, see Managing indexes.

Inserting documents

Then you can insert documents into your tables. QLDB documents are stored in Amazon Ion format. The following PartiQL INSERT statements include a subset of the vehicle registration sample data used in Getting started with the Amazon QLDB console.

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' : '294jJ3YUoH1IEEm8GSabOs' },
        'SecondaryOwners' : [ { 'PersonId' : '5Ufgdlnj06gF5CWcOIu64s' } ]
    }
},
{
    'VIN' : 'KM8SRDHF6EU074761',
    'LicensePlateNumber' : 'CA762X',
    'State' : 'WA',
    'City' : 'Kent',
    'PendingPenaltyTicketAmount' : 130.75,
    'ValidFromDate' : `2017-09-14T`,
    'ValidToDate' : `2020-06-25T`,
    'Owners' : {
        'PrimaryOwner' : { 'PersonId': 'IN7MvYtUjkp1GMZu0F6CG9' },
        'SecondaryOwners' : []
    }
} >>

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

PartiQL syntax and semantics

• Field names are enclosed in single quotation marks ('...').

• String values are also enclosed in single quotation marks ('...').

• Timestamps are enclosed in backticks (`...`). Backticks can be used to denote any Ion literals.

• Integers and decimals are literal values that don't need to be denoted.

For more details on the syntax and semantics of PartiQL, see Querying Ion with PartiQL in Amazon QLDB.

An INSERT statement creates the initial revision of a document with a version number of zero. To uniquely identify each document, QLDB assigns a document ID as part of the metadata. Insert statements return the ID of each document that is inserted.

Important

Because QLDB doesn't enforce schema, you can insert the same document into a table multiple times. Each insert statement commits a separate document entry to the journal, and QLDB assigns each document a unique ID.

To learn how to query the documents you inserted into your table, proceed to Querying your data.