Hologres:CREATE TABLE LIKE

Limits

• In Hologres V0.9 and earlier, you can execute the CREATE TABLE LIKE statement to copy only table schemas, but not table properties such as the primary key and index. You can view the version of a Hologres instance on the instance details page in the Hologres console.

• In Hologres V0.10 and later, you can execute the CREATE TABLE LIKE statement to copy both table schemas and table properties such as the primary key and index. To copy table properties from another table when you create a table, you must run the following command to set the related Grand Unified Configuration (GUC) parameter to true. Then, you must use the SELECT * FROM <table_name> statement in the CREATE TABLE LIKE statement. Other SELECT statements, such as SELECT <column_1>, <column_2> FROM <table_name>, are not supported.

  set hg_experimental_enable_create_table_like_properties=true;

• The CREATE TABLE LIKE statement cannot synchronize data from the source table.

• If you specify columns in the SELECT statement, you must specify a unique alias for each column. If you specify the same alias for different columns, the CREATE TABLE LIKE statement creates multiple columns with the same name for the table. As a result, an error message is returned. Sample statements:

  CALL hg_create_table_like('new_table', 'select *, 1 as c, ''a'' as c from src_table');
  ERROR:  column "c" specified more than once
  CONTEXT:  SQL statement "create table new_table (
  "a"     integer,
  "b"     text,
  "c"     integer,
  "c"     text
  );"
  PL/pgSQL function hg_create_table_like(text,text) line 22 at EXECUTE

• When you execute the CREATE TABLE LIKE statement, you can copy column comments and binary log properties. You cannot copy table comments or the dynamic partitioning property.

Create a standard table

1. Syntax

   The CREATE TABLE LIKE statement is used in the following syntax to create a standard table in Hologres:

   -- Create a standard table by copying the schema, but not the properties, of another table.
   CALL hg_create_table_like('new_table_name', 'select_query');
   
   -- Create a standard table by copying both the schema and properties of another table.
   set hg_experimental_enable_create_table_like_properties=true; -- Specify whether to allow the operation of copying table properties. The parameter setting applies only to the current session.
   CALL hg_create_table_like('new_table_name', 'select * from old_table_name');

   Note

   After you call the hg_create_table_like function, Hologres creates a table with the same schema as the table that is obtained by executing the SQL statement specified by the select_query parameter. new_table_name specifies the name of the table to be created. No data is inserted into the table.

2. Parameters

   • new_table_name: the name of the table to be created. You must set this parameter to a fixed string but not a string of variables or a function that is used to generate a table name. You cannot create a foreign table by executing the CREATE TABLE LIKE statement.

   • select_query: the SQL statement that is used to query data. If you set the select_query parameter to select * from tablename, the CREATE TABLE LIKE statement automatically copies all properties of the source table, including the primary key and index. If the SQL statement contains a large number of single quotation marks ('), we recommend that you specify the query statement in the format of $$query_sql$$ to escape single quotation marks ('). This method can help simplify operations. Sample statement:

     CALL HG_CREATE_TABLE_LIKE ('table_name', $$query_sql$$ [, 'partition_clause'])

   • old_table_name: the name of the table to be copied.

3. Example

   In this example, the source table is created in Hologres by executing the following statements:

   BEGIN;
   CREATE TABLE public.src_table (
    "a" int8 NOT NULL,
    "b" text NOT NULL,
   PRIMARY KEY (a)
   );
   CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column');
   CALL SET_TABLE_PROPERTY('public.src_table', 'bitmap_columns', 'b');
   CALL SET_TABLE_PROPERTY('public.src_table', 'dictionary_encoding_columns', 'b:auto');
   CALL SET_TABLE_PROPERTY('public.src_table', 'time_to_live_in_seconds', '3153600000');
   CALL SET_TABLE_PROPERTY('public.src_table', 'distribution_key', 'a');
   CALL SET_TABLE_PROPERTY('public.src_table', 'storage_format', 'segment');
   COMMIT;

   You can execute the CREATE TABLE LIKE statement by using one of the following methods to create a table in Hologres based on the source table:

   • To create a table by copying both the schema and properties of the source table, execute the following statements:

     -- Create a table by copying both the schema and properties of the source table.
     set hg_experimental_enable_create_table_like_properties=true;
     CALL hg_create_table_like('new_table', 'select * from src_table');

   • To create a table by adding a column to the schema of the source table, execute the following statement:

     -- Create a table by adding Column c to the schema of the source table. Column c has the same properties as Column b.
     CALL hg_create_table_like('holo_table_1', $$select *, "b" as c from src_table$$);

Create a partitioned table

1. Syntax

   The CREATE TABLE LIKE statement is used in the following syntax to create a partitioned table in Hologres:

   -- Create a partitioned table by copying the schema, but not the properties, of another table.
   CALL hg_create_table_like('new_table_name', 'select_query', 'partition_clause');

2. Parameters

   • new_table_name: the name of the table to be created. You must set this parameter to a fixed string but not a string of variables or a function that is used to generate a table name. You cannot create a foreign table by executing the CREATE TABLE LIKE statement.

   • select_query: the SQL statement that is used to query data. If you set the select_query parameter to select * from tablename, the CREATE TABLE LIKE statement automatically copies all properties of the source table, including the primary key and index. If the SQL statement contains a large number of single quotation marks ('), we recommend that you specify the query statement in the format of $$query_sql$$ to escape single quotation marks ('). This method can help simplify operations. Sample statement:

     CALL HG_CREATE_TABLE_LIKE ('table_name', $$query_sql$$ [, 'partition_clause'])

   • partition_clause: the clause that is used to partition the table. This clause specifies the partition key. Hologres does not automatically create child partitioned tables. Therefore, you must manually create child partitioned tables.

3. Example

   In this example, the source table is created in Hologres by executing the following statements:

   BEGIN;
   CREATE TABLE public.src_table (
    "a" int8 NOT NULL,
    "b" text NOT NULL,
   PRIMARY KEY (a)
   );
   CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column');
   CALL SET_TABLE_PROPERTY('public.src_table', 'bitmap_columns', 'b');
   CALL SET_TABLE_PROPERTY('public.src_table', 'dictionary_encoding_columns', 'b:auto');
   CALL SET_TABLE_PROPERTY('public.src_table', 'time_to_live_in_seconds', '3153600000');
   CALL SET_TABLE_PROPERTY('public.src_table', 'distribution_key', 'a');
   CALL SET_TABLE_PROPERTY('public.src_table', 'storage_format', 'segment');
   COMMIT;

   You can execute the CREATE TABLE LIKE statement to create a partitioned table in Hologres based on the source table and execute CREATE TABLE statements to create child partitioned tables:

   • To create a partitioned table, execute the following statement:

     -- Create a partitioned table by adding Column ds to the schema of the source table. Configure Column ds as the partition key.
     CALL hg_create_table_like('new_table', $$select *, "b" as ds from src_table$$, 'partition by list(ds)');

   • To create child partitioned tables for the partitioned table, execute the following statements:

     create table new_table_child_20201213 partition of new_table for values in('20201213');-- Specify 20201213 as a partition key value.
     create table new_table_child_20201214 partition of new_table for values in('20201214');-- Specify 20201214 as a partition key value.

Copy a table in the HoloWeb console

You can use HoloWeb to copy a table in a visualized manner, without the need to write SQL statements. Procedure:

1. Log on to the HoloWeb console. For more information, see Connect to Hologres and query data.

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

3. In the left-side navigation pane of the Metadata Management tab, right-click the table that you want to copy in the Instances Connected list, and select Replicate Table Schema.

4. On the Replicate Table Schema tab, configure the parameters as required.

   Section	Parameter	Description
   Destination Location	Table name	The name of the destination table. You can configure a custom name. Default value: Source table name_copy.
   	Description	The description of the destination table. This parameter is optional.
   	Schema	The name of the schema in which the destination table resides. Default value: public.
   Advanced Settings	Replicate Properties of Source Table	Specifies whether to copy the properties of the source table to the destination table. Note You can copy the properties of the source table only in Hologres V0.10 and later. If the version of your Hologres instance is earlier than V0.10, set this parameter to No or upgrade the instance.

5. Click Submit in the upper-right corner.