This topic describes how to execute the CREATE TABLE statement to create a partitioned table.

Description

A parent partitioned table is divided into different child partitioned tables based on partition key values. Data in the child partitioned tables is publicly available. Unless otherwise stated, parent tables and child tables mentioned in this topic refer to parent partitioned tables and child partitioned tables.

Before you use a parent table, you must create child tables first. You can execute the CREATE TABLE statement to create a partitioned table.

The child tables of a parent table use different types of file storage. To query data in a partitioned table, you must specify a partition. This way, the system does not need to scan all the partitions. This helps the system find the queried file and improves efficiency. In most cases, a fact table is divided into different child tables by date.

Limits

  • Hologres allows you to import data to a child table rather than a parent table.
    Note Realtime Compute for Apache Flink allows you to import data into a parent table. For more information, see Write data to a partitioned result table in Hologres in real time.
  • Partition fields must be of the TEXT, VARCHAR, or INT type.
  • Each partition rule can be used to create only one partitioned table.
  • The PARTITION BY clause supports only list partitioning. The partition key must be a single field.
  • A parent table and its child tables must be in the same schema.
  • If a partitioned table has a primary key, the partition key must be a subset of the primary key.
  • Only Hologres V1.3 and later allow you to dynamically manage partitions. You can view the version of your Hologres instance in the Hologres console. If the version of your Hologres instance is earlier than V1.3,submit a ticket or join the Hologres DingTalk group to update the instance.

Usage notes

  • If you want to import data from a database, we recommend that you do not use partitioned tables. If you use partitioned tables in this scenario, excessive partitioned tables may cause wastes of I/O resources. To resolve this issue and implement index-based query acceleration, you can specify the commonly used partition fields as the segment key.
  • If the number of data entries to be written to the partition of each day is less than 100 million, we recommend that you do not use partitioned tables. If you use partitioned tables in this scenario, the size of each partitioned table is too small. In this case, partitioned tables do not have clear advantages in query acceleration. To resolve this issue, you can specify a coarser granularity.
  • If you need to frequently replace the data of a specific day by performing TRUNCATE operations, we recommend that you use partitioned tables. If you use partitioned tables in this scenario, you can perform TRUNCATE operations in a more efficient manner, without the need to delete a large amount of data.

Create a partitioned table

  • Syntax
    The CREATE TABLE statement that is used to create a partitioned table uses the following syntax: 
    -- Create a parent table.
CREATE TABLE [if not exists] [<schema_name>.]<table_name>  ([
  {
   <column_name> <column_type> [ <column_constraints>, [...]]
   | <table_constraints>
   [, ...]
  }
])
PARTITION BY LIST(<column_name>);

-- Create a child table.
CREATE TABLE [if not exists] [<schema_name>.]<table_name> PARTITION OF <parent_table>
  FOR VALUES IN (<string_literal>);
  • Parameters
    The following table describes the parameters in the CREATE TABLE statement that is used to create a partitioned table.
    Parameter Description
    if not exists The clause that indicates that if a table with the same name already exists, the system does not return an error but notifies you that the table already exists.
    schema_name The name of the schema in which the table resides. If you create parent and child tables in the same schema, you do not need to specify a schema name. If you create parent and child tables across schemas, you must specify a schema name.
    table_name The name of the parent or child table to be created.
    column_name The name of the field to be created in the new table.
    column_type The data type of the field.
    column_constraints The name of the column constraint.
    table_constraints The name of the table constraint.
    parent_table The name of the parent table of the child table.
    string_literal The partition key.
  • Examples
    The following examples show how to execute the CREATE TABLE statement to create partitioned tables:
    • Example 1: Create a parent table that does not have a primary key and its child tables in the public schema. 
      BEGIN;
CREATE TABLE public.hologres_parent(
  a text, 
  b int, 
  c timestamp, 
  d text
) 
  PARTITION BY LIST(a);
CALL set_table_property('public.hologres_parent', 'orientation', 'column');           
CREATE TABLE public.hologres_child1 PARTITION OF public.hologres_parent FOR VALUES IN('v1');
CREATE TABLE public.hologres_child2 PARTITION OF public.hologres_parent FOR VALUES IN('v2');
CREATE TABLE public.hologres_child3 PARTITION OF public.hologres_parent FOR VALUES IN('v3');
COMMIT;
    • Example 2: Create a parent table that has a primary key and its child tables in the public schema. 
      BEGIN;
CREATE TABLE public.hologres_parent_2(
  a text , 
  b int, 
  c timestamp, 
  d text,
  ds text,
  primary key(ds,b)
  )
  PARTITION BY LIST(ds);
CALL set_table_property('public.hologres_parent_2', 'orientation', 'column');
CREATE TABLE public.holo_child_1 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201215');
CREATE TABLE public.holo_child_2 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201216');
CREATE TABLE public.holo_child_3 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201217');
COMMIT;

View all child tables

You can view all child tables of a specific parent table by using one of the following methods:
  • View all child tables of a specific parent table in a visualized manner by using HoloWeb.
  • View all child tables of a specific parent table by executing the following SQL statement. In the SQL statement, you can change parent_table_name to the name of the specified parent table. 
    SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';

Rules on property settings between a parent table and its child tables

The following list and table describe the rules on property settings that apply when a child table is attached to a parent table.
  • Must be consistent with the parent table: A property of the child table must be consistent with that of the parent table. Otherwise, an error is reported when the child table is attached to the parent table, and another child table must be created.
  • Need not be consistent with the parent table: A property of the child table can be different from that of the parent table. If the property of the child table is not explicitly specified, the child table inherits the corresponding property setting of the parent table. If the property of the child table is explicitly specified, the property setting of the child table is retained.
  • Must include indexed columns of the parent table: The indexed columns of the child table must include those of the parent table. Columns that are not specified as indexed columns for the parent table can be explicitly specified for the child table.
Category Table property Description Whether a child table created by executing the CREATE TABLE PARTITION OF statement inherits the property setting from its parent table Rule that applies when a child table is attached to a parent table
Table property orientation The storage format of the table. Yes Must be consistent with the parent table
table_group The table group to which the table belongs. This property also specifies the shard count for the table group. Yes Must be consistent with the parent table
time_to_live_in_seconds The time-to-live (TTL) period of the table. Yes Need not be consistent with the parent table
  • If this property is not specified for the child table, the child table inherits the property setting from its parent table.
  • If this property is specified for the child table, the specified property setting is retained.
Index primary key The primary key of the table. Yes Must be consistent with the parent table
distribution key The distribution key of the table. Yes Must be consistent with the parent table
clustering_key The clustered index of the table. This property specifies the columns and the order in which data is stored in the table. Yes Must be consistent with the parent table
event_time_column The segment key of the table. Yes Must be consistent with the parent table
bitmap_columns The bitmap indexes of the table. Yes Must include indexed columns of the parent table
dictionary_encoding_columns The field indexes of the table. Yes Must include indexed columns of the parent table
binlog_level Specifies whether to enable binary logging. Yes Must be consistent with the parent table
proxima_vectors The indexes used to perform vector searches on the table. Yes Must include indexed columns of the parent table
Column constraint nullable The NOT NULL constraint. Yes Must be consistent with the parent table
default value The default value constraint. Yes Must be consistent with the parent table

Dynamic partitioning

In Hologres V1.3 and later versions, Hologres supports the dynamic management of partitions in a partitioned table. You can configure rules and schedule tasks generated based on the rules to be run on a regular basis to automatically create or delete partitions for a partitioned table. This feature creates partitions in advance and deletes expired partitions as required. This way, you are less burdened with the partition lifecycle management.

  • Configure dynamic partitioning
    • Syntax
      You can configure dynamic partitioning for a partitioned table when you create the partitioned table. You can also configure the dynamic partitioning settings for an existing partitioned table. The following sample code shows the syntax: 
      -- Configure dynamic partitioning for a partitioned table you create the partitioned table.
BEGIN;
CREATE TABLE [if not exists] [<schema_name>.]<table_name>  ([
  {
   <column_name> <column_type> [ <column_constraints>, [...]]
   | <table_constraints>
   [, ...]
  }
])
PARTITION BY LIST(<column_name>);
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.enable', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_unit', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_zone', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_precreate', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');
COMMIT;

-- Configure dynamic partitioning for an existing partitioned table.
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.enable', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_unit', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_zone', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_precreate', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');
    • Parameters
      Parameter Required Description Modifiable
      auto_partitioning.enable No Specifies whether to enable dynamic partitioning. Valid values:
      • true: enables dynamic partitioning.
      • false: disables dynamic partitioning. This is the default value.
      		Yes
      auto_partitioning.time_unit Yes The time unit for dynamic partitioning. Valid values:
      • HOUR
      • DAY
      • MONTH
      • QUARTER
      • YEAR
      For example, if you set this parameter to DAY, the system automatically creates or deletes partitions by day.       		No
      auto_partitioning.time_zone No The time zone for dynamic partitioning. If you specify a time zone, partitions are dynamically managed based on the specified time zone. You can execute the following SQL statement to query the valid values for this parameter. Different time zones have different time offsets. The valid values for this parameter are displayed in the name column returned. Example: Asia/Shanghai. 
      SELECT * FROM pg_timezone_names;
      If you do not set this parameter, the time zone in which your Hologres instance is located is used.       		No
      auto_partitioning.num_precreate No The number of partitions to be created in advance. Valid values:
      • 0: No partitions are to be created.
      • [1, 512]: The specified number of partitions are to be created based on the current point in time. We recommend that you specify a number greater than or equal to 2. Default value: 4.
      For example, time_unit = DAY, num_precreate = 3 indicates that partitions are to be created for January 10, January 11, and January 12 of 2022 if the current date is January 10, 2022.       		Yes
      auto_partitioning.num_retention No The number of historical partitions to be retained. Valid values:
      • 0: No historical partitions are to be retained.
      • -1: All historical partitions are to be retained. This is the default value.
      • If you set this parameter to a positive integer, the specified number of partitions are retained. The maximum number that you can specify is 512.
      For example, <time_unit = DAY, num_retention = 3> indicates that partitions for January 9, January 8, and January 7 of 2022 are to be retained, and partitions for dates before January 7, 2022 are deleted if the current date is January 10, 2022.       		Yes
    • Naming conventions for child tables
      The time unit for dynamic partitioning can be specified as day, month, quarter, or year. The name of a child table that is automatically created by using the dynamic partitioning feature consists of the name of its parent table and a time suffix, in the {parent_table}_{time_suffix} format. Each time suffix follows a specific format and is generated based on the scheduled time and time unit for dynamic partitioning. The following table describes the relationships between time suffixes and the scheduled time for dynamic partitioning.
      time_unit Format of the time suffix Example Scheduled time for dynamic partitioning
      hour YYYYMMDDHH24 2022030117 The dynamic partitioning task is scheduled to run at the beginning of each hour. For example, the task is run at 01:00:01 on March 1, 2022.
      day YYYYMMDD 20220301 The dynamic partitioning task is scheduled to run at 00:00:01 every day. For example, the task is run at 00:00:01 on March 1, 2022.
      quarter YYYYQ 20221, 20222, 20223, and 20224, which indicate the first, second, third, or fourth quarter of 2022. The dynamic partitioning task is scheduled to run at 00:00:01 every day. For example, the task is run at 00:00:01 on March 1, 2022.
      year YYYY 2022 and 2023, which indicate the yearly partition for 2022 or 2023. The dynamic partitioning task is scheduled to run at 00:00:01 every day. For example, the task is run at 00:00:01 on March 1, 2022.
    • Example
      The following SQL statements provide an example on how to configure dynamic partitioning. In the example, partitions are created for the next three days and historical partitions for the previous two days are retained based on the current date in the time zone of Asia/Shanghai. 
      -- Create a partitioned table and configure dynamic partitioning for the table.
BEGIN;
CREATE TABLE tbl1 (
    c1 text NOT NULL,
    c2 int
)
PARTITION BY LIST (c2);
CALL set_table_property ('tbl1', 'auto_partitioning.enable', 'true');
CALL set_table_property ('tbl1', 'auto_partitioning.time_unit', 'DAY');
CALL set_table_property ('tbl1', 'auto_partitioning.time_zone', 'Asia/Shanghai');
CALL set_table_property ('tbl1', 'auto_partitioning.num_precreate', '3');
CALL set_table_property ('tbl1', 'auto_partitioning.num_retention', '2');
COMMIT;
      The following table describes the execution result.
      Time Event Return value
      2022-01-10 09:00:00 The preceding SQL statement is executed to create a parent table. The parent table named tbl1 and child tables named tbl1_20220110, tbl1_20220111, tbl1_20220112 are created.
      2022-01-11 00:00:00 The system automatically creates a child table. A child table named tbl1_20220113 is created.
      2022-01-12 00:00:00 The system automatically creates a child table. A child table named tbl1_20220114 is created.
      2022-01-13 00:00:00 The system automatically creates a child table and deletes another child table. A child table named tbl1_20220115 is created, and the child table named tbl1_20220110 is deleted.
      2022-01-14 00:00:00 The system automatically creates a child table and deletes another child table. A child table named tbl1_20220116 is created, and the child table named tbl1_20220111 is deleted.
  • Retain a specific child table
    • Syntax
      By default, the system creates and deletes child tables based on the configurations of dynamic partitioning. Child tables that expire are automatically deleted. However, you may want to retain the data in an important child table. For example, you want to retain the e-commerce data generated during Double 11 of each year to perform year-on-year or period-over-period analysis. In such scenarios, Hologres allows you to add the keep_alive property to the child table that you want to retain to prevent it from being automatically deleted. 
      -- Add the keep_alive property to a child table.
call set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'true');

-- Remove the keep_alive property from a child table. After you remove the keep_alive property, the child table is deleted after it expires.
call set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'false');
      schema_name specifies the name of the schema to which the child table that you want to retain belongs. table_name specifies the name of the child table that you want to retain.
    • Examples
      • You can execute the following SQL statement to retain the child table named tbl1_20220111: 
        -- Add the keep_alive property to the child table.
call set_table_property('pulbic.tbl1_20220111', 'keep_alive', 'true');
      • You can execute the following SQL statement to cancel the retention of the child table named tbl1_20220111: 
        -- Remove the keep_alive property from the child table. After the keep_alive property is removed, the child table is deleted after it expires.
call set_table_property('pulbic.tbl1_20220111', 'keep_alive', 'false');
  • Query the dynamic partitioning configurations and scheduling details
    You can execute the following SQL statement to query partitioned tables for which dynamic partitioning is enabled in the current database and the dynamic partitioning parameters set for each partitioned table: 
    SELECT
    nsp_name AS schema_name,
    tbl_name AS table_name,
    ENABLE,
    time_unit,
    time_zone,
    num_precreate,
    num_retention,
    b.usename AS create_user,
    cret_time,
    schd_time,
    options
FROM
    hologres.hg_partitioning_config AS a
    LEFT JOIN pg_user AS b ON a.cret_user = b.usesysid;
    The following figure shows a sample query result. Query dynamic partitioning configurationsThe following table describes the columns in the query result.
    Column Description
    schema_name The name of the schema.
    table_name The name of the table.
    ENABLE Indicates whether dynamic partitioning is enabled.
    time_unit The time unit for dynamic partitioning.
    time_zone The time zone for dynamic partitioning.
    num_precreate The number of partitions to be created in advance.
    num_retention The number of historical partitions to be retained.
    create_user The account used to create the partitioned table.
    cret_time The time when the partitioned table was created.
    schd_time The nearest point in time when partitioning is scheduled.
    options Other properties.
  • Query the logs for creating and deleting child tables
    The logs for creating and deleting child tables are not included in the data query logs. You can execute the following SQL statement to query the logs for creating and deleting child tables: 
    SELECT
    relname,
    relowner,
    schdtime,
    trigtime,
    status,
    message,
    precreate,
    discard
FROM
    hologres.hg_partitioning_log;
    The following figure shows a sample query result. Query logs for creating and deleting child tablesThe following table describes the columns in the query result.
    Parameter Description
    relname The name of the partitioned table and the schema to which the partitioned table belongs, in the {Schema name}.{Table name} format.
    relowner The owner of the partitioned table.
    schdtime The time scheduled for dynamic partitioning.
    trigtime The actual time when dynamic partitioning started.
    status The status of the dynamic partitioning task.
    message The error message returned if partitioning fails.
    precreate The names of the child tables that were created.
    discard The name of the child table that was deleted.

FAQ

  • How do I enable dynamic partitioning for an existing partitioned table?
    You can execute the following SQL statements to enable dynamic partitioning for an existing partitioned table:
    Notice The auto_partitioning.time_unit and auto_partitioning.time_zone parameters are the core parameters of the dynamic partitioning feature. Therefore, you can set the parameters only once after the update. After that, the parameters cannot be modified. 
    -- Sample SQL statements
BEGIN;
CALL set_table_property('auto_part_old', 'auto_partitioning.enable', 'true');
CALL set_table_property('auto_part_old', 'auto_partitioning.time_unit', 'HOUR');
CALL set_table_property('auto_part_old', 'auto_partitioning.time_zone', 'PRC');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_precreate', '4');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_retention', '-1');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_hot', '-1');
COMMIT;
  • If I enable dynamic partitioning for an existing parent table, will the child tables that already exist be automatically deleted?

    Hologres determines whether to delete a child table based on the table name. If the name of a child table follows the {parent_table}_{time_suffix} naming rule, the child table is automatically deleted after it expires. Otherwise, the child table is not automatically deleted.