Use DataStudio's visual table designer to define columns, indexes, distribution keys, and partitions for an AnalyticDB for PostgreSQL table, then submit it to your target environment.
Prerequisites
Before you begin, make sure that:
A serverless resource group or an exclusive resource group for scheduling has been created based on your business requirements.
The AnalyticDB for PostgreSQL computing resource has been associated with DataStudio. Test network connectivity before proceeding.
AnalyticDB for PostgreSQL metadata has been collected on the DataMap page. See Collect metadata from an AnalyticDB for PostgreSQL data source.
Create a table
Step 1: Open the table creation dialog
Log on to the DataWorks console. In the top navigation bar, select the region. In the left-side navigation pane, choose Data Development and O&M > Data Development, select your workspace from the drop-down list, and click Go to Data Development.
On the DataStudio page, move the pointer over the
icon and choose Create Table > AnalyticDB for PostgreSQL > Table. Alternatively, find the target workflow, click the workflow name, right-click AnalyticDB for PostgreSQL, and select Create Table.In the Create Table dialog box, enter a value for Name.
ImportantThe table name must be in the
schema_name.table_nameformat.The
schema_nameandtable_namemust start with a letter or an underscore (_), contain only letters, digits, and underscores (_), and be 63 characters or less in length.If multiple AnalyticDB for PostgreSQL computing sources are associated with DataStudio, select the target computing resource.
Click Create. The configuration tab for the table opens. The tab header displays the table name and the AnalyticDB for PostgreSQL instance name.
Step 2: Configure basic properties
In the Basic properties section, configure the following parameters.
| Parameter | Description |
|---|---|
| The stair theme | The level-1 folder where the table resides. Level-1 and level-2 folders organize table locations in DataWorks for easier management. |
| The secondary theme | The level-2 folder where the table resides. |
| New theme | Click to open the Folder Management tab and create level-1 or level-2 folders. After creating a folder, click the refresh icon next to New theme to sync the folder list. |
| Describe | A description of the table. |
Step 3: Configure the physical model
In the Physical model design section, configure the following parameters.
| Parameter | Description |
|---|---|
| Level selection | The data warehouse layer where the table is stored or processed. A standard data warehouse includes the operational data store (ODS), common data model (CDM), and application data service (ADS) layers. Specify a name for each layer as needed. |
| Physical classification | The business category for the table: basic, advanced, or other-purpose. Categories are for management purposes only and do not affect the underlying implementation. |
| New Level | Click to open the Level Management tab and create layers or business categories. Click the refresh icon after creating them. |
Step 4: Design the table schema
In the AnalyticDB for PostgreSQL table design section, configure the table schema across four tabs.
Column information settings
Click Added columns to add a field. Configure the following parameters for each field.
| Parameter | Description |
|---|---|
| Name | The name of the field. |
| Field type | The data type of the field. |
| Length setting | The length of the field. Applies only to fields of specific data types. |
| Default value | The default value of the field. |
| Whether to allow empty | Whether the field can be null. |
| Primary Key Field | Whether the field is the primary key. |
| The unique key | Whether the field has a unique constraint. |
| Operation | For a new field: save, cancel, delete, move up, or move down. For an existing field: modify, delete, move up, or move down. |
Index settings
Click New columns to add an index. Configure the following parameters for each index.
| Parameter | Description |
|---|---|
| Index name | The name of the index. Must be unique. |
| Include columns | The field on which the index is created. Click Edit, then click + in the Select at least one index dialog box. Select a field from the Column information drop-down list and click Save. |
| Index type | The type of the index: Normal, Primary Key, or Unique. |
| Index mode | The indexing algorithm: B-tree, Bitmap, or GiST. |
| Operation | For a new index: save, cancel, delete, move up, or move down. For an existing index: modify, delete, move up, or move down. |
Distribution key design
Select a distribution strategy, then click New columns to assign a field as the distribution key.
| Strategy | Description |
|---|---|
| Hash (Recommended) | Distributes data across compute nodes based on the distribution key. |
| Copy Schema | Replicates the table across compute nodes. |
| Random (Not Recommended) | Distributes data randomly across compute nodes. |
After selecting a strategy, click New columns, select the target field from the Name drop-down list, and click Save.
Partition table design (optional)
Configure partitions based on your business requirements.
Step 5: Submit the table
Click Submit to development environment, then click Submit to production environment.
If your workspace is in basic mode, click only Submit to production environment.
In the Submit changes dialog box, review the table creation statements. Select a resource group from the Select a resource group drop-down list, and click Confirm execution.
NoteSelect the same resource group that was used when adding the AnalyticDB for PostgreSQL data source.
What's next
After the table is created, query data, modify the schema, or delete the table. See Manage tables.