Hologres:CREATE PARTITION TABLE

Statement description

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

Before you can use a partitioned table, you must create child tables in advance. You can execute the CREATE PARTITION TABLE statement to create a partitioned table. You can also enable dynamic partitioning for a table to automatically create child tables.

Child tables of a partitioned table are stored in different files. To query data in a partitioned table, you must specify a partition. This way, the system does not need to scan all the partitions in the table and can quickly locate the desired file. This improves the efficiency. In most cases, a fact table is divided into different partitions by date. A child table of a partitioned table is equivalent to a non-partitioned table in metadata storage. Therefore, a large number of partitions cause the volume of metadata to increase and result in a large number of small files and fragments.

If your data source is a database, we recommend that you do not use partitioned tables. If you use partitioned tables in this scenario, excessive partitions may waste I/O resources. To resolve this issue and implement index-based query acceleration, you can specify the commonly used partition fields to constitute the segment key.

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 to a parent table in Hologres in real time. For more information, see Write data to a partitioned result table in Hologres in real time.

• Each partitioning 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 column.

• If a partitioned table has a primary key, the partition key must be a subset of the primary key.

• Hologres V1.3 and later support dynamic partitioning. In Hologres V2.1.11 and later, you use a column of the DATE data type as the partition key column for dynamic partitioning. You can view the version of your Hologres instance in the Hologres console. If you want to use features that are available in specific versions, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Manual upgrade (beta). For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

Usage notes

• If the number of data records in a single day is less than 100 million, we recommend that you do not use the date as a partitioning condition and do not create a partitioned table by date. Otherwise, the size of each partition is small and queries are not significantly accelerated. We recommend that you specify a coarser granularity.

• If you need to frequently replace the data of a partition by performing TRUNCATE or DROP operations, we recommend that you use partitioned tables. If you use partitioned tables in this scenario, you can perform TRUNCATE or DROP operations in a more efficient manner, without the need to scan a large amount of data.

Create a partitioned table

• Syntax

  You can use the following statements to create a partitioned table:

  -- 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 child tables.
  CREATE TABLE [if not exists] [<schema_name>.]<table_name> PARTITION OF <parent_table>
    FOR VALUES IN (<string_literal>);

• Parameter description

  The following table describes the parameters in the preceding syntax.

  Parameter	Description
  if not exists	Specifies that if a table with the same name already exists, the system does not return an error message but notifies you that the table already exists.
  schema_name	The name of the schema in which the table resides. If you create a parent table and child tables in the same schema, you do not need to specify a schema name. If you create a parent table and child tables across schemas, you must specify a schema name.
  table_name	The name of the parent table or the child table that you want to create.
  column_name	The name of the column that you want to create in the new table.
  column_type	The data type of the column.
  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 column.

• Columns of the TEXT, VARCHAR, and INT data types can be used as partition key columns. In Hologres V1.3.22 and later, columns of the DATE data type can be used as partition key columns.

• Examples

  The following examples show how 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.

    • Syntax supported in Hologres V2.1 and later:

      BEGIN;
      CREATE TABLE public.hologres_parent (
          a text,
          b int,
          c timestamp,
          d text
      )
      PARTITION BY LIST (a) 
      WITH (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;

    • Syntax supported in all Hologres versions:

      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.

    Syntax supported in Hologres V2.1 and later:

    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) 
    WITH (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;

    Syntax supported in all Hologres versions:

    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;

Query all child tables

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

Dynamic partitioning

Hologres V1.3 and later support dynamic partitioning in a partitioned table. This feature runs schedule tasks as configured to create partitions in advance and delete expired partitions as required. This way, you are less burdened with partition lifecycle management. Hologres V1.3.37 and later support dynamic management of tiered storage of hot and cold data. This way, data can be automatically stored in cold storage. This helps reduce storage costs. For more information, see Tiered storage of hot data and cold data.

• Configure dynamic partitioning

  • Syntax

    You can configure dynamic partitioning for a partitioned table when you create the partitioned table. You can also configure dynamic partitioning for an existing partitioned table. The following sample code shows the syntax:

    • Syntax supported in Hologres V2.1 and later:

      -- Configure dynamic partitioning for a partitioned table when you create the partitioned table.
      CREATE TABLE [if not exists] [<schema_name>.]<table_name>  ([
        {
         <column_name> <column_type> [ <column_constraints>, [...]]
         | <table_constraints>
         [, ...]
        }
      ])
      PARTITION BY LIST(<column_name>)
      WITH (
         auto_partitioning_enable = 'xxx',
         auto_partitioning_time_unit = 'xxx',
         auto_partitioning_time_zone = 'xxx',
         auto_partitioning_num_precreate = 'xxx',
         auto_partitioning_num_retention = 'xxx'
      );
      
      -- Configure dynamic partitioning for an existing partitioned table.
      ALTER TABLE [<schema_name>.]<table_name> SET (
         auto_partitioning_enable = 'xxx',
         auto_partitioning_num_precreate = 'xxx',
         auto_partitioning_num_retention = 'xxx'
      );
      

    • Syntax supported in all Hologres versions:

      -- Configure dynamic partitioning for a partitioned table when 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 settings 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');

  • Parameter description

    Important

    Before you execute the CREATE TABLE WITH statement to configure dynamic partitioning, change the periods (.) in parameter names to underscores (_). For example, before you execute the CREATE TABLE WITH statement to configure dynamic partitioning in Hologres V2.1, change auto_partitioning.enable to auto_partitioning_enable.

    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 configure this parameter, the time zone in which your Hologres instance is located is used.	No
    auto_partitioning.num_precreate	No	The number of partitions that you want to create in advance. Valid values: 0: No partitions are created in advance. [1, 512]: The specified number of partitions are 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 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 that you want to retain. Valid values: 0: No historical partitions are retained. -1: All historical partitions are retained. This is the default value. A positive integer: The specified number of historical partitions are retained. The default value is 512. You can execute the set hg_experimental_auto_part_max_maintained_partitions=<value>; statement to change the number of historical partitions that you want to retain. The maximum value is 8760. For example, if the current date is January 10, 2022, <time_unit = DAY,num_retention = 3> indicates that partitions for January 9, January 8, and January 7 of 2022 are retained, and partitions for dates before January 7, 2022 are deleted.	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, or 20224, which indicates 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 or 2023, which indicates 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.

  • Examples

    The following SQL statements provide examples on how to configure dynamic partitioning. In the examples, 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.

    Syntax supported in Hologres V2.1 and later

    1. Create a partitioned table named tbl1.

       -- Create a partitioned table and configure dynamic partitioning for the table in Hologres V2.1.
       
       CREATE TABLE tbl1 (
           c1 text NOT NULL,
           c2 text 
       )
       PARTITION BY LIST (c2)
       WITH (
          auto_partitioning_enable = 'true',
          auto_partitioning_time_unit = 'DAY',
          auto_partitioning_time_zone = 'Asia/Shanghai',
          auto_partitioning_num_precreate = '3',
          auto_partitioning_num_retention = '2'
       );
       

    2. Insert data into partitions.

       INSERT INTO tbl1 (c1, c2) VALUES ('Data 1', '20231212');
       INSERT INTO tbl1 (c1, c2) VALUES ('Data 2', '20231213');
       INSERT INTO tbl1 (c1, c2) VALUES ('Data 3', '20231214');

    3. Query data.

       SELECT * FROM tbl1 WHERE c2='20231212';

       The following result is returned:

       c1	     c2
       Data 1   20231212

    Syntax supported in all Hologres versions

    1. Create a partitioned table named tbl1.

       -- Create a partitioned table and configure dynamic partitioning for the table.
       BEGIN;
       CREATE TABLE tbl1 (
           c1 text NOT NULL,
           c2 text 
       )
       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;

    2. Insert data into partitions.

       INSERT INTO tbl1 (c1, c2) VALUES ('Data 1', '20231212');
       INSERT INTO tbl1 (c1, c2) VALUES ('Data 2', '20231213');
       INSERT INTO tbl1 (c1, c2) VALUES ('Data 3', '20231214');

    3. Query data.

       SELECT * FROM tbl1 WHERE c2='20231212';

       The following result is returned:

       c1	     c2
       Data 1   20231212

    The following table describes the execution result.

    Time	Event	Execution result
    2023-12-12 09:00:00	SQL statements are executed to create a parent table and its child tables.	The parent table named tbl1 and child tables named tbl1_20231212, tbl1_20231213, and tbl1_20231214 are created.
    2023-12-13 00:00:00	The system automatically creates a child table.	A child table named tbl1_20231215 is created.
    2023-12-14 00:00:00	The system automatically creates a child table.	A child table named tbl1_20231216 is created.
    2023-12-15 00:00:00	The system automatically creates a child table and deletes another child table.	A child table named tbl1_20231217 is created, and the child table named tbl1_20231212 is deleted.
    2023-12-16 00:00:00	The system automatically creates a child table and deletes another child table.	A child table named tbl1_20231218 is created, and the child table named tbl1_20231213 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 on November 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.

    • Syntax supported in Hologres V2.1 and later:

      -- Add the keep_alive property to the child table.
      ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'true');
      
      -- Remove the keep_alive property from the child table. After the keep_alive property is removed, the child table is deleted after it expires.
      ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'false');
      

    • Syntax supported in all Hologres versions:

      -- 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 to be retained belongs. table_name specifies the name of the child table that you want to retain.

  • Examples

    • Execute the following SQL statement to retain the child table named tbl1_20220111:

      • Syntax supported in Hologres V2.1 and later:

        -- Add the keep_alive property to a child table.
        ALTER TABLE pulbic.tbl1_20220111 SET (keep_alive = 'true');

      • Syntax supported in all Hologres versions:

        -- Add the keep_alive property to a child table.
        call set_table_property('pulbic.tbl1_20220111', 'keep_alive', 'true');

    • Execute the following SQL statement to cancel the retention of the child table named tbl1_20220111:

      • Syntax supported in Hologres V2.1 and later:

        -- Remove the keep_alive property from the child table. After the keep_alive property is removed, the child table is deleted after it expires.
        ALTER TABLE pulbic.tbl1_20220111 SET (keep_alive = 'false');

      • Syntax supported in all Hologres versions:

        -- 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 configured 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 
  FROM
      hologres.hg_partitioning_config AS a
      LEFT JOIN pg_user AS b ON a.cret_user = b.usesysid;

  The 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 that you want to create in advance.
  num_retention	The number of historical partitions that you want to retain.
  cret_time	The time when the partitioned table was created.
  schd_time	The nearest point in time when partitioning is scheduled.

• Query the logs for creating and deleting child tables

  The logs of creating and deleting child tables are not included in the data query logs. You can execute the following SQL statement to query the logs of 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. The following table describes the columns in the query result.

  Column	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 was enabled.
  status	The status of the dynamic partitioning task.
  message	The description of a table.
  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.

  Important

  The auto_partitioning.time_unit and auto_partitioning.time_zone parameters are the core parameters of the dynamic partitioning feature. Therefore, you can configure the parameters only once after a Hologres instance is upgraded. After that, the values of the parameters cannot be changed.

  -- 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, are the child tables that already exist 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.

• I execute SQL statements to create a partitioned table and configure dynamic partitioning for the table with auto_partitioning_num_precreate set to 3. However, a partitioned table is created but no child tables are created. Why?

  By default, the backend checks tasks in which dynamic partitioning is configured for the first time every 10 minutes. The child tables are created within 10 minutes. You can check the child tables later.