Database and table management functions - Hologres - Alibaba Cloud Documentation Center

Hologres is compatible with PostgreSQL. This topic describes the database and table management functions that you can use to configure the properties of databases and tables.

Function	Description
HG_UPDATE_DATABASE_PROPERTY	Configures the default_table_group and shard_count properties of a database.
SET_TABLE_PROPERTY	Configures the properties of a table, including the index, distribution column, storage mode, and lifecycle.

HG_UPDATE_DATABASE_PROPERTY

Description: Configures the default_table_group and shard_count properties of a database.
```
CALL HG_UPDATE_DATABASE_PROPERTY ('property', 'value');
```
Limits:
To use the HG_UPDATE_DATABASE_PROPERTY function, you must be the superuser of an instance or the owner of a database.
Parameters:
- property: the name of the property. Only the default_table_group and shard_count properties are supported.
  - default_table_group: specifies a table group as the default table group.
    Note
    Only Hologres V0.10 and later support this property. If the version of your Hologres instance is earlier than V0.10, manually upgrade your Hologres instance or join the Hologres DingTalk group to contact technical support. For more information about how to upgrade an instance, see Upgrade instances. For more information about how to obtain online support, see Obtain online support for Hologres.
  - shard_count: specifies the number of shards for the default table group. We recommend that you do not configure this property.
- value: the value of the property.
Example:
Execute the following statement to specify a table group named TG120 as the default table group:
```
CALL HG_UPDATE_DATABASE_PROPERTY ( 'default_table_group', 'TG120' );
```

SET_TABLE_PROPERTY

Description: Configures the properties of a table, including the index, distribution column, storage mode, and lifecycle.
```
CALL SET_TABLE_PROPERTY ('table_name', 'property', 'value')
WHERE PROPERTY IN
    orientation
  clustering_key
  segment_key
  bitmap_columns
  dictionary_encoding_columns
  time_to_live_in_seconds
  distribution_key
```
Note
To modify or update a table, execute the ALTER TABLE statement. To drop a table, execute the DROP TABLE statement.

Parameters:

Parameter	Description
table_name	The name of the table. The table name can be schema-qualified. Note The table name can contain only lowercase letters, uppercase letters, digits, and underscores `(_)`. It must start with a letter. If the table name contains special characters, escape the special characters by using double quotation marks (`" "`). Uppercase letters are treated as lowercase letters because the parameter value is not case-sensitive.
property	The name of the property.
orientation	The storage mode of the table in Hologres, which can be row-oriented storage, column-oriented storage, or hybrid row-column storage. Note You can configure this parameter only in the same transaction as the `CREATE TABLE` statement.
clustering_key	The columns for which you want to create a clustered index. Note You can configure this parameter only in the same transaction as the `CREATE TABLE` statement.
segment_key	The columns that you want to use as the segment key. For example, you can specify the column that contains time data as the segment key. If the segment key is involved in query conditions, Hologres can find the storage location of data based on the segment key. Note You can configure this parameter only in the same transaction as the `CREATE TABLE` statement.
bitmap_columns	The columns for which you want to create a bitmap index. The bitmap index can be used to filter data in a segment. Note You can separately configure this parameter.
dictionary_encoding_columns	The columns for which you want to create dictionary mappings. Note Dictionary encoding can convert string comparisons into numeric comparisons. This can accelerate queries, such as queries that involve GROUP BY and FILTER statements. You can separately configure this parameter.
distribution_key	The distribution policy of tables in the specified database. Note You can configure this parameter only in the same transaction as the `CREATE TABLE` statement.
time_to_live_in_seconds	The lifecycle of table data, in seconds. This parameter can be set to a non-negative number, an integer, or a floating-point number. Note You can separately configure this parameter.
value	The value of the property. If the value of this parameter contains a column name and the column name contains uppercase letters, enclose the value in double quotation marks (`" "`).

Example:

BEGIN;
CREATE TABLE ORDERS ( 
  O_ORDERKEY              INTEGER NOT NULL,
  O_CUSTKEY        INTEGER NOT NULL,
  O_ORDERSTATUS    TEXT NOT NULL,
  O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
  O_ORDERDATE      DATE NOT NULL,
  O_ORDERPRIORITY  TEXT NOT NULL,  
  O_CLERK          TEXT NOT NULL, 
  O_SHIPPRIORITY   INTEGER NOT NULL,
  O_COMMENT        TEXT NOT NULL);
CALL SET_TABLE_PROPERTY ('ORDERS', 'clustering_key', 'O_ORDERKEY:asc,O_CUSTKEY:asc');
CALL SET_TABLE_PROPERTY ('ORDERS', 'segment_key', 'O_ORDERDATE');
CALL SET_TABLE_PROPERTY ('ORDERS', 'bitmap_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY');
CALL SET_TABLE_PROPERTY ('ORDERS', 'dictionary_encoding_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY');
CALL SET_TABLE_PROPERTY ('ORDERS', 'time_to_live_in_seconds', '172800');
COMMIT;