Managing tables - Amazon Quantum Ledger Database (Amazon QLDB)

Managing tables

This section describes how to manage tables using the DROP TABLE and UNDROP TABLE statements in Amazon QLDB. The quotas for the number of active tables and total tables that you can create are defined in Quotas and limits in Amazon QLDB.

Dropping tables

To drop a table, use a basic DROP TABLE statement. When you drop a table in QLDB, you are just inactivating it.

For example, the following statement inactivates the VehicleRegistration table.

DROP TABLE VehicleRegistration

A DROP TABLE statement returns the system-assigned ID of the table. The status of VehicleRegistration should now be INACTIVE in the system catalog table information_schema.user_tables.

SELECT status FROM information_schema.user_tables WHERE name = 'VehicleRegistration'

Querying the history of dropped tables

In addition to a table name, you can also query the QLDB History function with a table ID as the first input parameter. This enables you to query the history of dropped tables. After a table is dropped, you can no longer query its history with the table name.

First, find the table ID by querying the system catalog table. For example, the following query returns the tableId of the VehicleRegistration table.

SELECT tableId FROM information_schema.user_tables WHERE name = 'VehicleRegistration'

Then, you can use this ID to run the same history query from the previous section. The following is an example that queries the history of document ID ADR2Ll1fGsU4Jr4EqTdnQF from table ID 5PLf9SXwndd63lPaSIa0O6. The table ID is a string literal that must be enclosed in single quotation marks.

--replace both the table and document IDs with your values SELECT * FROM history('5PLf9SXwndd63lPaSIa0O6', `2000T`, `2019-06-05T23:59:59Z`) AS h WHERE h.metadata.id = 'ADR2Ll1fGsU4Jr4EqTdnQF'

Undropping tables

After you drop a table in QLDB, you can use the UNDROP TABLE statement to reactivate it.

First, find the table ID from information_schema.user_tables. For example, the following query returns the tableId of the VehicleRegistration table. The status should be INACTIVE.

SELECT tableId FROM information_schema.user_tables WHERE name = 'VehicleRegistration'

Then, use this ID to reactivate the table. The following is an example that undrops table ID 5PLf9SXwndd63lPaSIa0O6. The table ID is a unique identifier that should be enclosed in double quotation marks.

UNDROP TABLE "5PLf9SXwndd63lPaSIa0O6"

The status of VehicleRegistration should now be ACTIVE.

To learn how to create and describe indexes, proceed to Managing indexes.