Table
This page compiles crucial insights into table operations, serving as a comprehensive guide for you navigating the intricacies of working with tables in Databend. It strings together essential table-related commands to provide a cohesive understanding of key considerations in table management.
Table Creation Essentials
It is a good idea to to familiarize yourself with the following topics before proceeding to create a table.
1. Understanding Table Types
Databend supports two types of tables based on their Time Travel support:
General Tables (Default): These tables inherently support Time Travel, allowing you to trace and retrieve historical data. This feature is valuable for data analysis and auditing.
Transient Tables: In contrast, transient tables do not support Time Travel. They are designed for scenarios where historical data tracking is not necessary. To create a transient table, you must explicitly specify the keyword TRANSIENT in the CREATE TABLE command. For more information, see CREATE TRANSIENT TABLE.
2. Selecting Table Storage
Databend defaults to storing table data in the location configured in the databend-query.toml configuration file. Additionally, it provides the flexibility to store table data in a different bucket, deviating from the default setting. For more information, see CREATE TABLE ... EXTERNAL_LOCATION.
3. Defining Table Structure
The primary method to define columns in a table is through the CREATE TABLE command, where you list your columns one by one. Please note that Computed Columns are supported as an Enterprise Edition feature in Databend. For more information, see Computed Columns.
Databend also offers convenient methods for creating tables by copying column structures and even data from existing tables:
- CREATE TABLE ... LIKE: Creates a table with the same column definitions as an existing one.
- CREATE TABLE ... AS: Creates a table and inserts data based on the results of a SELECT query.
- ATTACH TABLE: Creates a table by associating it with an existing table.
4. Setting Cluster Key for Big Tables
Cluster Key is designed to enhance query performance by physically organizing data in proximity. Databend recommends configuring cluster keys, especially for large tables encountering sluggish query performance. For the syntax to set a cluster key during table creation, see SET CLUSTER KEY.
Routine Table Maintenance
Once your table is created, you gain the foundation for organizing and managing your data effectively. With this structure in place, you can seamlessly execute various commands to enhance, modify, or extract information from your table. Whether it's adjusting column properties, fine-tuning configurations, or querying data, Databend provides a versatile set of tools to meet your evolving needs.
- DESCRIBE TABLE, SHOW FIELDS: Shows information about the columns in a given table.
- SHOW FULL COLUMNS: Retrieves comprehensive details about the columns in a given table.
- SHOW CREATE TABLE: Shows the CREATE TABLE statement that creates the named table.
- SHOW DROP TABLES: Lists the dropped tables in the current or a specified database.
- SHOW TABLE STATUS: Shows the status of the tables in a database.
- SHOW TABLES: Lists the tables in the current or a specified database.
- ALTER TABLE COLUMN: Modifies the structure of a table by making changes to its columns.
- ALTER TABLE OPTION: Modifies the Fuse engine Options of a table.
- RENAME TABLE: Changes the name of a table.
Table Deletion & Recovery Strategies
Databend provides a variety of commands for deleting a table or vacuuming the table data. The table below compares these commands, which may initially seem complex, outlining any associated recovery options for each operation.
| Command | Enterprise Edition? | Description | Recovery |
|---|---|---|---|
| TRUNCATE TABLE | No | Removes all data from a table while preserving the table's schema. | FLASHBACK TABLE |
| DROP TABLE | No | Deletes a table. | UNDROP TABLE |
| VACUUM TABLE | Yes | Permanently removes historical data files of a table. | Not applicable. |
| VACUUM DROP TABLE | Yes | Permanently removes data files of dropped tables. | Not applicable. |
Advanced Table Optimization Techniques
Tables in Databend might need optimizations over time to ensure efficient performance and storage utilization. In this case, the following commands can help you out:
Table Optimization is an advanced-level operation. Databend recommends carefully reading the links below and understanding the optimization process before proceeding to avoid potential data loss.
- ANALYZE TABLE: Calculates table statistics.
- OPTIMIZE TABLE: Involves compacting or purging historical data to save storage space and enhance query performance.