This topic describes the syntax of the SET_TABLE_PROPERTY function in Hologres.

Introduction

set_table_property: sets a property for a table, such as the index, distribution column, storage model, and time-to-live (TTL) of the table.

Synopsis

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

Parameters

  • table_name: the name of the table for which you want to set a property. The table name can be schema-qualified. The name can contain only case-insensitive letters, digits, and underscores (_), and must start with a letter. If the name contains any special characters, enclose the name in double quotation marks (" ").
  • property: the name of the property to be set.
  • orientation property: Indicates columnar or row store. Should be specified in the same transaction of creating table.
  • clustering_key property: Creating clustering index on specified columns. Should be specified in the same transaction of creating table .
  • segment_key property:Segment key is used to split the data into files. Specifying a column(Ex: event_time) as segment key could benefit the queries which has a where clause on the segment key. Should be specified in the same transaction of creating table..
  • bitmap_columns property: Create bitmap index on specified columns. It benefits the filtering on the columns on which have it. Can be a stand alone command.
  • dictionary_encoding_columns property: Create a mapping dictionary for specified columns. It benefits filtering comparison, aggregation, joins. Can be a stand alone command.
  • distribution_key property :Declares the distribution policy. Should be specified in the same transaction of creating table.
  • time_to_live_in_seconds property :specifies the TTL of data in a table, which is represented in seconds. It must be a non-negative number, which can be an integer or a floating-point number. This property can be set in a single transaction.
  • value: the value of the property. If the value contains a column name and the column name contains any uppercase letters, enclose the value in double quotation marks (" ").

Examples

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;