SET_TABLE_PROPERTY¶
Introduction¶
set_table_property: Set the table properties includes index, distribution keys, columnar/row store, TTL …
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 new table which could belongs to some schema. The names can contain only alphanumeric characters or an underscore(). The first character must be an alphabetic character. All alphabetic characters are treated as lower case because the table name is case insensitive.
property : property name.
orientation:Indicates columnar or row store. Should be specified in the same transaction of creating table.
clustering_key :Creating clustering index on specified columns. Should be specified in the same transaction of creating table.
segment_key :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 : 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:_Create a mapping dictionary for specified columns. It benefits filtering comparison, aggregation, joins. Can be a stand alone command.
distribution_key :Declares the distribution policy. Should be specified in the same transaction of creating table.
time_to_live_in_seconds : Indicates the data expiration period. The precision is one second. It must be a non-negative integer or float number. Can be a stand alone command.
value : Property value. Use double quotes(“) to enclose it if it contains column names in which has upper case alphabetic characters.
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;