Creating tables with indexes and inserting documents - Amazon Quantum Ledger Database (Amazon QLDB)

Creating tables with indexes and inserting documents

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 QLDB-assigned IDs 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 help speed up queries.

Indexes are optional in QLDB, but we strongly recommend using them to improve query performance for seek operations. Note the following constraints when creating indexes:

  • Indexes can only be created on a single top-level field. Composite, nested, unique, and function-based indexes are currently 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). Be sure to enclose the list of values in parentheses when you use the IN operator. Without the parentheses, the statement invokes a table scan rather than an indexed lookup.

    QLDB doesn't currently 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.

CREATE INDEX ON VehicleRegistration (VIN)
CREATE INDEX ON VehicleRegistration (LicensePlateNumber)
CREATE INDEX ON Vehicle (VIN)

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 PartiQL's syntax and semantics, see Querying Ion with PartiQL.

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 does not 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.