All Products
Search
Document Center

:Create an AnalyticDB for PostgreSQL table

Last Updated:Nov 15, 2024

This topic describes how to create an AnalyticDB for PostgreSQL table.

Prerequisites

Procedure

  1. Go to the DataStudio page.

    Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Development and Governance > Data Development. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Development.

  2. On the DataStudio page, move the pointer over the 新建 icon and choose Create Table > AnalyticDB for PostgreSQL > Table.

    Alternatively, you can find the desired workflow and click the workflow name, right-click AnalyticDB for PostgreSQL, and then select Create Table.

  3. In the Create Table dialog box, configure the Name parameter.

    Important
    • The table name must be in the format of schema_name.table_name.

    • The values of schema_name and table_name cannot exceed 63 characters in length and can contain only letters, digits, and underscores (_). The values must start with a letter or an underscore (_).

    • If multiple AnalyticDB for PostgreSQL data sources are associated with DataStudio, select one based on your business requirements.

  4. Click Create. The configuration tab of the table appears.

    The upper part of the table configuration tab displays the table name and AnalyticDB for PostgreSQL instance name.

  5. In the Basic properties section, configure the parameters. The following table describes the parameters.

    基本属性

    Parameter

    Description

    The stair theme

    The name of the level-1 folder in which the table resides.

    Note

    The level-1 and level-2 folders show the table locations in DataWorks to help you easily manage tables.

    The secondary theme

    The name of the level-2 folder in which the table resides.

    New theme

    Click New theme to go to the Folder Management tab. On the Folder Management tab, you can create level-1 and level-2 folders.

    After you create a folder, click the 刷新 icon next to New theme to synchronize the folder.

    Describe

    The description of the table.

  6. In the Physical model design section, configure the parameters. The following table describes the parameters.

    物理模型设计

    Parameter

    Description

    Level selection

    The layer where the table data is stored or processed. A data warehouse consists of the operational data store (ODS), common data model (CDM), and application data service (ADS) layers. You can specify a name for each layer.

    Physical classification

    The business category to which the table belongs. Tables are categorized into the basic, advanced, and other-purpose business categories. You can specify a name for each business category.

    Note

    Business categories are designed only to help you manage the table and do not affect underlying implementation.

    New Level

    The layers and business categories that you want to create. To create layers and business categories, click New Level to go to the Level Management tab. After you create levels and business categories, click the 刷新 icon.

  7. In the AnalyticDB for PostgreSQL table design section, configure the parameters.

    You can configure the schema of an AnalyticDB for PostgreSQL table on the following tabs: Column information settings, Index settings, Distribution key design, and Partition table design (optional). 表设计

    Tab

    Parameter

    Description

    Column information settings

    Added columns

    Allows you to click the button and configure relevant parameters to create a field.

    Name

    The name of the field.

    Field type

    The data type of the field.

    Length setting

    The length of the field. You can specify the length for fields only of specific data types.

    Default value

    The default value of the field.

    Whether to allow empty

    Specifies whether the field can be empty.

    Primary Key Field

    Specifies whether the field serves as the primary key.

    The unique key

    Specifies whether the field serves as the unique key.

    Operation

    • You can perform the following operations on a new field: save, cancel, delete, move up, and move down.

    • You can perform the following operations on an existing field: modify, delete, move up, and move down.

    Index settings

    New columns

    Allows you to click the button and configure relevant parameters to create an index.

    Index name

    The name of the index. Make sure that you specify a unique name.

    Include columns

    The field on which the index will be created. To select a field, click Edit. In the Select at least one index dialog box, click the + icon. All the created fields appear in the Column information drop-down list.

    Select the field from the Column information drop-down list and click Save.

    Index type

    The type of the index. Valid values: Normal, Primary Key, and Unique.

    Index mode

    The mode for indexing data in the fields. Valid values: B-tree, Bitmap, and GiST.

    Operation

    • You can perform the following operations on a new index: save, cancel, delete, move up, and move down.

    • You can perform the following operations on an existing index: modify, delete, move up, and move down.

    Distribution key design

    Hash (Recommended), Copy Schema, and Random (Not Recommended)

    The way in which the partition key is generated. Take Hash (Recommended) as an example. Click New columns and select the desired field from the Name drop-down list. The information about the selected field appears. Click Save.

    For more information, see the Column information settings section of this table.

    Partition table design (optional)

    Partition table design (optional)

    The partitions of the table. You can configure the partitions based on your business requirements.

  8. Click Submit to development environment and Submit to production environment in sequence.

    If you are using a workspace in basic mode, you need to only click Submit to production environment.

  9. In the Submit changes dialog box, confirm that the table creation statements are correct, select a resource group from the Select a resource group drop-down list, and then click Confirm execution.

    Note

    The resource group that you select in this step must be the resource group that you selected when you add the data source.

What to do next

After the AnalyticDB for PostgreSQL table is created, you can query data in the table and modify or delete the table. For more information, see Manage tables.