Managing tables

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

Tagging tables on creation

Note

Tagging tables on creation is currently supported for ledgers in the STANDARD permissions mode only.

You can tag your table resources. To manage tags for existing tables, use the AWS Management Console or the API operations TagResource, UntagResource, and ListTagsForResource. For more information, see Tagging Amazon QLDB resources.

You can also define table tags while you're creating the table by using the QLDB console, or by specifying them in a CREATE TABLE PartiQL statement. The following example creates a table named Vehicle with the tag environment=production.

CREATE TABLE Vehicle WITH (aws_tags = `{'environment': 'production'}`)

By tagging resources while they're being created, you can eliminate the need to run custom tagging scripts after resource creation. After a table is tagged, you can control access to the table based on those tags. For example, you can grant full access only to tables that have a specific tag. For a JSON policy example, see Full access to all actions based on table tags.

Dropping tables

To drop a table, use a basic DROP TABLE statement. When you drop a table in QLDB, you're just deactivating it.

For example, the following statement deactivates 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 inactive tables

In addition to a table name, you can also query the QLDB History function with a table ID as the first input argument. You must use the table ID to query the history of an inactive table. After a table is deactivated, 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 Querying revision history. 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'

Reactivating tables

After you deactivate 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. In this case, the table ID is a unique identifier that you enclose in double quotation marks.

UNDROP TABLE "5PLf9SXwndd63lPaSIa0O6"

The status of VehicleRegistration should now be ACTIVE.

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