This topic describes how to create an AnalyticDB for PostgreSQL table.
Prerequisites
DataWorks Standard Edition or higher is activated.
An exclusive resource group for scheduling is added based on business needs. For more information, see Create and use an exclusive resource group for scheduling.
The resource group added when you bind an AnalyticDB for PostgreSQL instance passes the connectivity test.
An AnalyticDB for PostgreSQL instance is bound to the workspace where you want to create an ADB for PostgreSQL table. The AnalyticDB for PostgreSQL service is available in a workspace only after you bind an AnalyticDB for PostgreSQL instance to the workspace on the Workspace Management page. For more information, see Create and manage workspaces.
The metadata of the bound AnalyticDB for PostgreSQL instance is collected on the DataMap page.
Procedure
Go to the DataStudio page.
Log on to the DataWorks console. In the left-side navigation pane, choose . On the page that appears, select the desired workspace from the drop-down list and click Go to DataStudio.
On the Data Development tab, move the pointer over the icon and choose .
Alternatively, you can click a workflow in the Business process section, right-click AnalyticDB for PostgreSQL, and then choose Create Table.
In the New table dialog box, set the name parameter.
ImportantThe table name is in the format of schema_name.table_name.
The schema name and table name must be 1 to 63 characters in length, and can contain letters, digits, and underscores (_). They must start with a letter or underscore (_).
If you have bound multiple AnalyticDB for PostgreSQL instances to the current workspace, you must select one based on your needs.
Click Create. The table configuration tab appears.
The upper part of the table configuration tab displays the table name and AnalyticDB for PostgreSQL instance name.
In the Basic properties section, set the parameters as required.
Parameter
Description
The stair theme
The name of the level-1 folder where the table resides.
NoteLevel-1 and level-2 folders show the table locations in DataWorks for you to manage tables more conveniently.
The secondary theme
The name of the level-2 folder where the table resides.
New theme
Click New theme to go to the Theme management tab. On this tab, you can create level-1 and level-2 folders for tables.
After you create a folder, click the icon next to New theme to synchronize the folder.
Description
The description of the table.
In the Physical model design section, set the parameters as required.
Parameter
Description
Level selection
The layer where the table data is stored or processed. Generally, a data warehouse consists of the operational data store (ODS), common data model (CDM), and application data store (ADS) layers. You can specify a custom name for each layer.
Physical classification
The category of the table. Tables are categorized into basic services, advanced services, and other services. You can specify a custom name for each category.
NoteCategories are designed only for your management convenience and do not involve underlying implementation.
New Level
The levels and categories to be created. To add levels and categories, click New Level to go to the Hierarchical management tab. After levels and categories are created, click the icon.
In the AnalyticDB for PostgreSQL table design section, set the parameters as required.
You can configure the schema of an AnalyticDB for PostgreSQL table on the following tabs: Column information settings, Index settings, Sub-table design, and Partition settings (optional).
Tab
Parameter
Description
Column information settings
Added columns
Allows you to click the button and set the 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 of only some 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 a 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 set the 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 is to be created. To select a field, click Edit. In the Select at least one index dialog box, click the + icon. All 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.
Sub-table 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 target 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 settings (optional)
Partition settings (optional)
The partitions of the table. You can configure the partitions as required.
Click Submit to development environment and Submit to production environment in sequence.
If you are using a workspace in basic mode, you only need to click Submit to production environment.
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.
NoteYou can select only an exclusive resource group for scheduling or a custom resource group.
The selected resource group must be the same as the one that passes the connectivity test when you bind an AnalyticDB for PostgreSQL instance.
What to do next
After the AnalyticDB for PostgreSQL table is created, you can query the table data and modify or delete the table. For more information, see Manage tables.