<!--

    Licensed to the Apache Software Foundation (ASF) under one
    or more contributor license agreements.  See the NOTICE file
    distributed with this work for additional information
    regarding copyright ownership.  The ASF licenses this file
    to you under the Apache License, Version 2.0 (the
    "License"); you may not use this file except in compliance
    with the License.  You may obtain a copy of the License at
    
        http://www.apache.org/licenses/LICENSE-2.0
    
    Unless required by applicable law or agreed to in writing,
    software distributed under the License is distributed on an
    "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
    KIND, either express or implied.  See the License for the
    specific language governing permissions and limitations
    under the License.

-->

# Table Management

In the table model, it is recommended that one table corresponds to one type of device, for managing time series data of such devices. Devices of the same type usually have the same or similar set of measurement points, such as wind turbines, vehicles, production equipment or monitoring objects of the same category.

## 1. Basic Concepts

### 1.1 Table

Tables are generally used to store time series data for the same type of device. During data modeling, device identification information can be designed as TAG columns, static device descriptions as ATTRIBUTE columns, and time-varying collected values as FIELD columns.

### 1.2 Time Column, Tag Column, Attribute Column and Measurement Column

![](/img/sample-dataset-en-01.png)

The table structure in the table model is generally shown in the figure above. Columns can be classified into the following categories by purpose:

| Concept | Description |
| ------- | ----------- |
| Time Column (TIME) | Each table must contain one time column of the TIMESTAMP data type, which records the timestamp corresponding to each data point. |
| Tag Column (TAG) | Used to identify devices and can serve as the composite primary key of a device. It typically stores information for locating devices, such as region, plant, and device ID. Values in tag columns usually do not change over time. |
| Attribute Column (ATTRIBUTE) | Used to describe static attributes of a device, such as model, manufacturer, and maintenance information. Attribute columns do not change over time and can be added or updated. |
| Measurement Column (FIELD) | Used to store physical quantities or metrics collected from devices, whose values change over time, such as temperature, humidity, current, voltage, and status. |

In terms of query filtering efficiency, the general priority order is: time column and tag columns first, followed by attribute columns, and measurement columns last.

### 1.3 Devices and Measurement Points

In the table model, a device is uniquely identified by the combination of values from all TAG columns in a table. For example, if a table contains three TAG columns: `region`, `plant_id`, and `device_id`, each unique combination of `region + plant_id + device_id` represents an independent device.

Measurement points correspond to FIELD columns in the table. One FIELD column for a single device generates multiple data points over time, and these time-ordered data points form a time series.

Therefore, the number of measurement points per table in the table model can be expressed by the following formula:
`Number of measurement points per table = Number of devices × Number of FIELD columns`

### 1.4 Table-Level TTL

The TTL of a table defaults to the TTL of its parent database. If a table is configured with a separate TTL, the table-level TTL takes precedence. Proper use of table-level TTL allows different data retention periods to be set for different business tables.

For a more detailed introduction to TTL features, see: [TTL Delete Data](../Basic-Concept/TTL-Delete-Data_apache.md)

For further information on the IoTDB tree-table twin model, model selection methods and typical modeling solutions, please refer to [Modeling Scheme Design](../Background-knowledge/Data-Model-and-Terminology_apache.md).

## 2. Table Management

### 2.1 Create a Table

Manually create a table within the current or specified database.The format is "database name. table name".

**Syntax:**

```SQL
createTableStatement
    : CREATE TABLE (IF NOT EXISTS)? qualifiedName
        '(' (columnDefinition (',' columnDefinition)*)? ')'
        charsetDesc?
        comment?
        (WITH properties)?
     ;

charsetDesc
    : DEFAULT? (CHAR SET | CHARSET | CHARACTER SET) EQ? identifierOrString
    ;

columnDefinition
    : identifier columnCategory=(TAG | ATTRIBUTE | TIME) charsetName? comment?
    | identifier type (columnCategory=(TAG | ATTRIBUTE | TIME | FIELD))? charsetName? comment?
    ;

charsetName
    : CHAR SET identifier
    | CHARSET identifier
    | CHARACTER SET identifier
    ;

comment
    : COMMENT string
    ;
```

**Note:**

1. When creating a table, you do not need to specify a time column. IoTDB automatically adds a column named "time" and places it as the first column. All other columns can be added by enabling the `enable_auto_create_schema` option in the database configuration, or through the session interface for automatic creation or by using table modification statements.
2. Since version V2.0.8, tables support custom naming of the time column during creation. The order of the custom time column in the table is determined by the order in the creation SQL. The related constraints are as follows:

   - When the column category is set to TIME, the data type must be TIMESTAMP.
   - Each table allows at most one time column (columnCategory = TIME).
   - If no time column is explicitly defined, no other column can use "time" as its name to avoid conflicts with the system's default time column naming.
3. The column category can be omitted and defaults to FIELD. When the column category is TAG or ATTRIBUTE, the data type must be STRING (can be omitted).
4. The TTL of a table defaults to the TTL of its database. If the default value is used, this attribute can be omitted or set to default.
5. <TABLE_NAME> table name has the following characteristics:

   - It is case-insensitive and, upon successful creation, is uniformly displayed in lowercase.
   - The name can include special characters, such as `~!`"%`, etc.
   - Table names containing special characters or Chinese characters must be enclosed in double quotation marks ("") during creation.

      - Note: In SQL, special characters or Chinese table names must be enclosed in double quotes. In the native API, no additional quotes are needed; otherwise, the table name will include the quote characters.
   - When naming a table, the outermost double quotation marks (`""`) will not appear in the actual table name.
   - ```sql
      -- In SQL
      "a""b" --> a"b
      """""" --> ""
      -- In API
      "a""b" --> "a""b"
     ```
6. columnDefinition column names have the same characteristics as table names and can include the special character `.`.
7. COMMENT adds a comment to the table.

**Examples:** 

```SQL
CREATE TABLE table1 (
  time TIMESTAMP TIME,
  region STRING TAG,
  plant_id STRING TAG,
  device_id STRING TAG,
  model_id STRING ATTRIBUTE,
  maintenance STRING ATTRIBUTE COMMENT 'maintenance',
  temperature FLOAT FIELD COMMENT 'temperature',
  humidity FLOAT FIELD COMMENT 'humidity',
  status Boolean FIELD COMMENT 'status',
  arrival_time TIMESTAMP FIELD COMMENT 'arrival_time'
) COMMENT 'table1' WITH (TTL=31536000000);
```

Note: If your terminal does not support multi-line paste (e.g., Windows CMD), please reformat the SQL statement into a single line before execution.

### 2.2 View Tables

Used to view all tables and their properties in the current or a specified database.

**Syntax:**

```SQL
SHOW TABLES (DETAILS)? ((FROM | IN) database_name)?
```

**Note:**

1. If the `FROM` or `IN` clause is specified, the command lists all tables in the specified database.
2. If neither `FROM` nor `IN` is specified, the command lists all tables in the currently selected database. If no database is selected (`USE` statement not executed), an error is returned.
3. When the `DETAILS` option is used, the command shows the current state of each table:
   1. `USING`: The table is available and operational.
   2. `PRE_CREATE`: The table is in the process of being created or the creation has failed; the table is not available.
   3. `PRE_DELETE`: The table is in the process of being deleted or the deletion has failed; the table will remain permanently unavailable.

**Examples:**

```sql
show tables details from database1;
```
```shell
+---------------+-----------+------+-------+
|      TableName|    TTL(ms)|Status|Comment|
+---------------+-----------+------+-------+
|         table1|31536000000| USING| table1|
+---------------+-----------+------+-------+
```

### 2.3 View Table Columns

Used to view column names, data types, categories, and states of a table.

**Syntax:**

```SQL
(DESC | DESCRIBE) <TABLE_NAME> (DETAILS)?
```

**Note:** If the `DETAILS` option is specified, detailed state information of the columns is displayed:

- `USING`: The column is in normal use.
- `PRE_DELETE`: The column is being deleted or the deletion has failed; it is permanently unavailable.


**Examples:** 

```sql
desc table1 details;
```
```shell
+------------+---------+---------+------+------------+
|  ColumnName| DataType| Category|Status|     Comment|
+------------+---------+---------+------+------------+
|        time|TIMESTAMP|     TIME| USING|        null|
|      region|   STRING|      TAG| USING|        null|
|    plant_id|   STRING|      TAG| USING|        null|
|   device_id|   STRING|      TAG| USING|        null|
|    model_id|   STRING|ATTRIBUTE| USING|        null|
| maintenance|   STRING|ATTRIBUTE| USING| maintenance|
| temperature|    FLOAT|    FIELD| USING| temperature|
|    humidity|    FLOAT|    FIELD| USING|    humidity|
|      status|  BOOLEAN|    FIELD| USING|      status|
|arrival_time|TIMESTAMP|    FIELD| USING|arrival_time|
+------------+---------+---------+------+------------+
```


### 2.4 View Table Creation Statement

Retrieves the complete definition statement of a table or view under the table model. This feature automatically fills in all default values that were omitted during creation, so the displayed statement may differ from the original CREATE statement.

> This feature is supported starting from v2.0.5.

**Syntax:**

```SQL
SHOW CREATE TABLE <TABLE_NAME>
```

**Note:：**

1. This statement does not support queries on system tables.

**Example:**

```SQL
show create table table1;
```
```shell
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table|                                                                                                                                                                                                                                                                     Create Table|
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|table1|CREATE TABLE "table1" ("region" STRING TAG,"plant_id" STRING TAG,"device_id" STRING TAG,"model_id" STRING ATTRIBUTE,"maintenance" STRING ATTRIBUTE,"temperature" FLOAT FIELD,"humidity" FLOAT FIELD,"status" BOOLEAN FIELD,"arrival_time" TIMESTAMP FIELD) WITH (ttl=31536000000)|
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```


### 2.5 Update Tables

Used to update a table, including adding or deleting columns and configuring table properties.

**Syntax:**

```SQL
#addColumn;
ALTER TABLE (IF EXISTS)? tableName=qualifiedName ADD COLUMN (IF NOT EXISTS)? column=columnDefinition; 
#dropColumn;              
| ALTER TABLE (IF EXISTS)? tableName=qualifiedName DROP COLUMN (IF EXISTS)? column=identifier;
#setTableProperties;                     
// set TTL can use this;
| ALTER TABLE (IF EXISTS)? tableName=qualifiedName SET PROPERTIES propertyAssignments;                
| COMMENT ON TABLE tableName=qualifiedName IS 'table_comment';
| COMMENT ON COLUMN tableName.column IS 'column_comment';
```

**Note:：**

1. The `SET PROPERTIES` operation currently only supports configuring the `TTL` property of a table
2. The delete column function only supports deleting the ATTRIBUTE and FILD columns, and the TAG column does not support deletion.
3. The modified comment will overwrite the original comment. If null is specified, the previous comment will be erased.

**Example:** 

add column
```SQL
ALTER TABLE table1 ADD COLUMN IF NOT EXISTS a TAG COMMENT 'a';
ALTER TABLE table1 ADD COLUMN IF NOT EXISTS b FLOAT FIELD COMMENT 'b';
```
set TTL
```SQL
ALTER TABLE table1 set properties TTL=3600; 
```
set comment
```SQL
COMMENT ON TABLE table1 IS 'table1';
COMMENT ON COLUMN table1.a IS null;
```

### 2.6 Delete Tables

Used to delete a table.

**Syntax:**

```SQL
DROP TABLE (IF EXISTS)? <TABLE_NAME>
```

**Examples:**

```SQL
DROP TABLE table1;
```