Hologres:CREATE TABLE

Hologres is compatible with the PostgreSQL ecosystem. The syntax that is used to create a table in Hologres is the same as that in PostgreSQL. However, the indexes that are supported by Hologres differ from the indexes that are supported by PostgreSQL. For more information about the indexes supported by Hologres, see Configure properties for a table. If you configure appropriate indexes when you create a table, the data that you want to query can be quickly located after an SQL statement is executed. This reduces I/O resource consumption and accelerates queries with fewer computing resources. The following figure shows the execution process from executing an SQL statement to obtaining data. The following figure can help you understand each index used in the execution process and configure appropriate indexes based on your business requirements.

1. In this example, you perform an SQL query on a partitioned table and locate the partition in which the desired data resides by using the partition pruning feature.

2. You can use a distribution key to locate the shard in which the desired data resides.

3. You can use an event time column to locate the file in which the desired data resides. An event time column is previously referred to as a segment key.

4. You can use a clustering key to sort data in a file and locate the file block in which the desired data resides.

5. You can use a bitmap index to locate the row in which the desired data resides. A bitmap index is an index in files.

The following table describes the scenarios for different indexes.

Hologres provides set_table_property and create table with for you to configure table properties. Appropriate settings of table properties help Hologres sort and query data in an efficient manner. You must execute the parameters related to data storage layout together with the CREATE TABLE statement.

• Syntax

  -- Syntax supported in Hologres V2.1 and later:
  create table <table_name> (...)
  with (property = 'value'[, ...]);
  
  -- Syntax supported in all Hologres versions:
  call set_table_property('<table_name>', property, 'value');

  Note

  The set_table_property function and the CREATE TABLE statement must be executed in the same transaction.

  Hologres allows you to execute SQL statements for configuring table properties that include but are not limited to the following properties:

  • Syntax supported in Hologres V2.1 and later:

    CREATE TABLE <table_name> (...)
    WITH (
        orientation = '[column | row | row,column]',
        table_group = '[tableGroupName]',
        distribution_key = 'columnName[,...]]',
        clustering_key = '[columnName{:asc]} [,...]]',
        event_time_column = '[columnName [,...]]',
        bitmap_columns = '[columnName [,...]]',
        dictionary_encoding_columns = '[columnName [,...]]',
        time_to_live_in_seconds = '<non_negative_literal>'
        [,storage_mode, ...]
    );

  • Syntax supported in all Hologres versions:

    BEGIN;
    CREATE TABLE <table_name> (...);
    call set_table_property('table_name', 'orientation', '[column | row]');
    call set_table_property('table_name', 'table_group', '[tableGroupName]');
    call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
    call set_table_property('table_name', 'clustering_key', '[columnName{:asc]} [,...]]');
    call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');
    call set_table_property('table_name', 'bitmap_columns', '[columnName [,...]]');
    call set_table_property('table_name', 'dictionary_encoding_columns', '[columnName [,...]]');
    call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');
    COMMIT;

• Properties

  The following table describes the properties and their settings in different types of tables.

  Property	Description	Column-oriented table	Row-oriented table	Row-column hybrid table	Recommended setting	Modifiable after a table is created
  orientation	The storage mode of the table.	Default value: column.	row	row,column	column	No. If you want to modify this property, create another table.
  table_group	The table group to which the table belongs.	By default, the default table group is used.	By default, the default table group is used.	By default, the default table group is used.	Use the default value.	No. If you want to modify this property, create another table or reshard the existing table.
  distribution_key	The distribution key of the table.	By default, the primary key is configured as the distribution key. You can modify the distribution key based on your business requirements.	By default, the primary key is configured as the distribution key.	By default, the primary key is configured as the distribution key.	We recommend that you specify only one of the primary key columns as the distribution key.	No. If you want to modify this property, create another table.
  clustering_key	The clustering key of the table.	By default, the property is empty.	By default, the primary key is used as the clustering key.	By default, the property is empty.	We recommend that you specify only one column as the clustering key. The values in the column can be sorted only in ascending order.	No. If you want to modify this property, create another table.
  event_time_column	The event time column of the table.	By default, the first non-null timestamp column is used as the event time column.	Not supported.	By default, the first non-null timestamp column is used as the event time column.	We recommend that you specify a timestamp column.	No. If you want to modify this property, create another table.
  bitmap_columns	The bitmap index of the table.	Configure this property based on your business requirements.	Not supported.	Configure this property based on your business requirements.	We recommend that you specify less than 10 columns that are used for equality comparisons.	Yes. For more information, see ALTER TABLE.
  dictionary_encoding_columns	The dictionary encoding on the columns of the table.	Configure this property based on your business requirements.	Not supported.	Configure this property based on your business requirements.	We recommend that you specify less than 10 columns with low cardinality.	Yes. For more information, see ALTER TABLE.
  time_to_live_in_seconds	The time-to-live (TTL) of the table.	Configure this property based on your business requirements.	Configure this property based on your business requirements.	Configure this property based on your business requirements.	Use the default value.	Yes. For more information, see ALTER TABLE.

  • orientation

    This property specifies whether a database table uses the column-oriented or row-oriented storage mode in Hologres. Hologres V1.1 and later support the row-column hybrid storage mode. The following syntax is used when you specify this property. Different storage modes apply to different query scenarios. When you create a table, the column-oriented storage mode is used by default. If you want to use another storage mode, explicitly specify the storage mode when you create a table. For more information, see Storage models of tables: row-oriented storage, column-oriented storage, and row-column hybrid storage.

    call set_table_property('table_name', 'orientation', '[column | row |row,column]');

  • table_group

    In Hologres, a table group is a unique concept of logical storage and is used to manage shard counts. A table group corresponds to a group of shards. If you do not create a table group when you create a database, a default table group named in the <db>_tg_default format is automatically created when you create the first table in the database. If no table group is specified when you create other tables, the tables are automatically created in the default table group. In most cases, you do not need to specify a table group. You can use the default table group. If your Hologres instance has more than 256 CPU cores, we recommend that you configure different table groups and specify the shard count for each table group based on your business requirements. This improves performance. For more information, see User guide of table groups and shard counts.

    call set_table_property('table_name', 'table_group', '[tableGroupName]');

  • distribution_key

    This property specifies a distribution key. The distribution_key property is used to specify the distribution policy of data in tables. Data is distributed to each shard based on the distribution key. After this property is configured, the entries with the same distribution key value are distributed to the same shard. A distribution key is important for distributed computing, and can help improve query performance and increase queries per second (QPS). For more information, see Distribution key.

    call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');

  • clustering_key

    This property specifies a clustering key. You can use the following syntax to configure a clustering key. Hologres sorts data in a file based on the clustering key. By default, the data is sorted in ascending order. A clustering key can help accelerate range queries and filter queries on index columns and improve query performance. For more information, see Clustering key.

    call set_table_property('table_name', 'clustering_key', '[columnName{:asc} [,...]]');

  • event_time_column

    This property specifies an event time column. You can use the following syntax to configure an event time column. Files are sorted based on the event time column. When the event time column is hit, Hologres can quickly locate the file where the required data resides. We recommend that you set the event_time_column property to columns whose data monotonically increases or decreases, such as timestamp columns. The event_time_column property is applicable to time-related columns, such as log and traffic columns. Appropriate settings of this property can improve query performance. For more information, see Event time column (segment key).

    call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');

  • bitmap_columns

    This property specifies a bitmap index. You can use the following syntax to configure a bitmap index. The bitmap index can be used to quickly locate the row where data that meets conditions resides. You can specify columns that meet equivalent query conditions as bitmap index columns. By default, all columns of the TEXT type in a column-oriented table are implicitly specified as bitmap index columns. For more information, see Bitmap index.

    call set_table_property('table_name', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');

  • dictionary_encoding_columns

    This property specifies a dictionary encoding column. You can use the following syntax to configure a dictionary encoding column. Dictionary encoding specifies whether to build dictionary mappings for the values of specific columns. Dictionary mappings can be used to convert string comparisons into numeric comparisons to accelerate queries, such as the queries that involve the GROUP BY or FILTER operation. By default, all columns of the TEXT type in a column-oriented table are specified as dictionary encoding columns. Hologres V0.9 and later automatically determine whether to build dictionary mappings based on data characteristics.

    call set_table_property('table_name', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');

  • time_to_live_in_seconds (not recommended)

    This property specifies the TTL of the data in a table. Unit: seconds. You can use the following syntax to configure the TTL.

    • The system counts the start of the TTL from the time when data is first written to the table, not the time when the data is updated. If you do not configure the TTL, the data in the table is retained for 100 years by default. In Hologres V1.3.24 and later, the minimum TTL can be set to one day, which is equivalent to 86,400 seconds. For more information about TTL, see Other PostgreSQL statements.

    • The TTL does not specify an accurate time. If the TTL expires, the data in the table is deleted within a period of time instead of at a specified point in time. The data is deleted, but the table where the data resides still exists. Therefore, duplicate primary keys may appear. We recommend that you do not use the TTL to manage the lifecycle of data in the production environment. We recommend that you manage the lifecycle of data based on partitions. For more information, see CREATE PARTITION TABLE.

    call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');

You can execute the following statements to query a table schema:

create extension hg_toolkit; -- This statement takes effect at the database level. You need to execute the statement in a database only once.
select hg_dump_script('[<schema_name>.]<table_name>');

You can use HoloWeb to create an internal table in a visualized manner, without the need to write SQL statements. To create an internal table in the HoloWeb console, perform the following steps:

1. Log on to the HoloWeb console. For more information, see Connect to HoloWeb.

2. In the top navigation bar of the HoloWeb console, click Metadata Management. Then, click Tables.

   You can also click Instances Connected in the left-side navigation pane of the Metadata Management tab. Click the desired instance and click the database that you want to manage. Right-click the schema that you want to manage and select Create Internal Table.

3. On the Create Internal Table page, configure the parameters based on your business requirements. The following table describes the parameters.

   Parameter description (show more)

   Section	Parameter	Description
   Basic Information	Mode	The name of the schema. You can select the default schema named public or a custom schema.
   	Table Name	The name of the Hologres internal table that you want to create.
   	Description	The description of the Hologres internal table that you want to create.
   Fields	Field Name	The name of each column in the internal table.
   	Data Type	The data type of each column.
   	Primary Key	Specifies whether to configure the column as the primary key for the internal table.
   	Nullable	Specifies whether the column can be null.
   	Array	Specifies whether the column is an ordered array of elements.
   	Description	The description of the column.
   	Actions	The operations that you can perform on the column. You can delete the column, move the column up, or move the column down.
   Attributes	Storage Mode	The storage mode of the table. Valid values: Column-oriented storage, Row-oriented storage, and Row-column Storage. Default value: Column-oriented storage.
   	Data Lifecycle	The TTL of the data in the table. The system counts the start of the TTL from the time when data is first written to the table. If the TTL expires, the data of the table is deleted in a period of time instead of at a specified point in time. Default value: Maximum.
   	Binlog	Specifies whether to enable binary logging for the table. For more information, see Subscribe to Hologres binary logs.
   	Lifecycle of Binary Logs	The TTL of binary logs. For more information, see Subscribe to Hologres binary logs. Default value: Maximum.
   	Distribution Column	The distribution key of the table. For more information, see Distribution key.
   	Event Time Column	The event time column of the table. For more information, see Event time column (segment key).
   	Clustering Key	The clustering key of the table. For more information, see Clustering key.
   	Dictionary Encoding Columns	The dictionary encoding column of the table. For more information, see Dictionary encoding.
   	Bitmap Column	The bitmap column of the table. For more information, see Bitmap index.
   Partitioned Tables	N/A	Select partition key columns of the internal table.

4. In the upper-right corner, click Submit. After you click Submit, you can refresh the left-side instance list. The created internal table is displayed under the schema that you selected.

More operations:

• Edit an internal table

  1. Go to the Metadata Management page and double-click the internal table that you want to edit in Instances Connected.

  2. On the table information tab, click Edit Table to modify properties of the table, such as adding columns and changing the TTL of the data in the table.

  3. Click Submit.

• Delete an internal table

  1. Go to the Metadata Management page, right-click the internal table that you want to delete in Instances Connected, and then select Delete Table.

  2. In the Delete table message, click OK.

• View data in a table

  1. In Instances Connected, double-click the internal table whose data you want to view.

  2. On the table information tab, click Data preview to preview the data in the table.

• View DDL statements

  On the table details tab, click DDL statement to view the DDL statements of the table.