Database and table configuration functions-Hologres(Hologres)-阿里云帮助中心

Hologres is compatible with PostgreSQL and provides two stored procedures for configuring database and table properties: HG_UPDATE_DATABASE_PROPERTY and SET_TABLE_PROPERTY.

Function	Description
HG_UPDATE_DATABASE_PROPERTY	Sets the default table group and shard count for a database
SET_TABLE_PROPERTY	Sets properties on a table, including storage mode, indexes, distribution key, and lifecycle

HG_UPDATE_DATABASE_PROPERTY

Sets the default_table_group or shard_count property of a database.

Prerequisites

To call HG_UPDATE_DATABASE_PROPERTY, you must be a superuser of the instance or the owner of the database.

Syntax

CALL HG_UPDATE_DATABASE_PROPERTY ('property', 'value');

Parameters

Parameter	Description
`property`	The property to set. Valid values: `default_table_group` and `shard_count`.
`default_table_group`	Sets a table group as the default table group for the database. Requires Hologres V0.10 or later. If your instance is on an earlier version, manually upgrade your instance or join the Hologres DingTalk group to contact technical support. To upgrade your instance, see Upgrade instances. For support, see Obtain online support for Hologres.
`shard_count`	Sets 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

Set TG120 as the default table group:

CALL HG_UPDATE_DATABASE_PROPERTY ('default_table_group', 'TG120');

SET_TABLE_PROPERTY

Sets properties on a table, including storage mode, indexes, distribution key, and lifecycle.

To modify the schema of a table, use ALTER TABLE. To delete a table, use DROP TABLE.

Syntax

CALL SET_TABLE_PROPERTY ('table_name', 'property', 'value');

Parameters

`table_name`

The name of the table. The name can be schema-qualified. Valid characters: lowercase letters, uppercase letters, digits, and underscores (_). The name must start with a letter. If the name contains special characters, wrap it in double quotation marks (" "). The parameter is case-insensitive, so uppercase letters are treated as lowercase.

`property` and `value`

The following table lists the supported properties, their descriptions, and when each can be configured.

Property	Description	When to configure
`orientation`	Storage mode of the table. Valid values: row-oriented storage, column-oriented storage, or hybrid row-column storage.	Same transaction as `CREATE TABLE` only
`clustering_key`	Columns used to build a clustered index. Specify columns in `column:asc` or `column:desc` format.	Same transaction as `CREATE TABLE` only
`segment_key`	Columns used as the segment key. Hologres uses the segment key to locate data during queries. Time-based columns are a common choice.	Same transaction as `CREATE TABLE` only
`distribution_key`	Columns that determine how table data is distributed across shards.	Same transaction as `CREATE TABLE` only
`bitmap_columns`	Columns for which to build a bitmap index. Bitmap indexes filter data within a segment.	Can be set independently
`dictionary_encoding_columns`	Columns for which to build dictionary mappings. Dictionary encoding converts string comparisons into numeric comparisons, which accelerates `GROUP BY` and `FILTER` queries.	Can be set independently
`time_to_live_in_seconds`	Lifecycle of table data in seconds. Accepts non-negative integers and floating-point numbers. Note Starting from Hologres V4.2, the TTL for primary-key tables is subject to a minimum value enforced by the GUC parameter `hg_time_to_live_in_days_min_value` (unit: days, default: 36500, equivalent to 100 years). Only Superusers can modify this parameter. When you set the TTL of a primary-key table through CREATE TABLE, ALTER TABLE, SET_TABLE_PROPERTY, or REBUILD, the system validates that the specified value meets the minimum threshold (must be greater than or equal to the number of days defined by `hg_time_to_live_in_days_min_value`). This constraint does not apply to tables without primary keys.	Can be set independently

If value contains a column name with uppercase letters, wrap the value in double quotation marks (" ").

Example

The following example creates an ORDERS table and sets its storage properties in a single transaction. Properties that can only be configured during table creation—clustering_key, segment_key, and distribution_key—are set inside a BEGIN...COMMIT block together with CREATE TABLE. Properties such as bitmap_columns and dictionary_encoding_columns can also be set in the same transaction, or configured independently afterward. The time_to_live_in_seconds property sets data to expire after 48 hours (172800 seconds).

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;