DataWorks:Add an AnalyticDB for PostgreSQL data source

Prerequisites

• An AnalyticDB for PostgreSQL instance is purchased, and an AnalyticDB for PostgreSQL database is created in the instance. For more information, see Create an instance and Manage databases.

  Note

  We recommend that you create an AnalyticDB for PostgreSQL instance in the same region as the workspace to which you want to add an AnalyticDB for PostgreSQL data source. If the regions are different, you can add only a cross-region data source to the workspace. The data source cannot be associated with DataStudio for data development or periodic task scheduling. The data source can be used only for data synchronization.

• The required resource group is purchased and configured. An AnalyticDB for PostgreSQL data source supports only exclusive resource groups for Data Integration, exclusive resource groups for scheduling, and exclusive resource groups for DataService Studio. After the AnalyticDB for PostgreSQL data source is added, you can use the data source in scenarios such as data synchronization, development and scheduling of computing tasks, and generation of DataService Studio APIs. In these scenarios, a resource group for Data Integration, a resource group for scheduling, and a resource group for DataService Studio of DataWorks are separately required.

  You must purchase and configure the required resource group based on the use scenario of the AnalyticDB for PostgreSQL data source and establish a network connection between the data source and resource group in advance. For information about resource groups provided by DataWorks and how to select a resource group, see Overview.

• A DataWorks workspace is created, or the account that you use is added to the desired workspace as a member.

  You must add the desired AnalyticDB for PostgreSQL instance to the workspace as a data source. This way, you can use the data source to perform data development operations in the workspace. In addition, you must associate the purchased resource group with the workspace and establish a network connection between the resource group and data source. For information about how to create a workspace, see Create and manage workspaces.

  Note

  You can add the same AnalyticDB for PostgreSQL instance to multiple workspaces as a data source.

Limits

• An AnalyticDB for PostgreSQL data source can be associated with DataStudio only if the data source meets the following conditions: The AnalyticDB for PostgreSQL instance based on which the data source is added resides in the same region and belongs to the same Alibaba Cloud account as the workspace. This way, the AnalyticDB for PostgreSQL data source can be used for data development and periodic task scheduling.

• You can add an AnalyticDB for PostgreSQL instance that does not belong to the current Alibaba Cloud account to a workspace within the current Alibaba Cloud account as a data source. After the data source is added, you can use only a RAM role to access the related AnalyticDB for PostgreSQL instance. AnalyticDB for PostgreSQL data sources that are added across accounts cannot be used for data development or periodic task scheduling.

• AnalyticDB for PostgreSQL data sources that are added in connection string mode cannot be used for data development or periodic task scheduling. These data sources can be used only for data synchronization. For more information, see Add a data source.

• You can use only an exclusive resource group for Data Integration or exclusive resource group for scheduling to run an AnalyticDB for PostgreSQL task. We recommend that you use an exclusive resource group for DataService Studio to create DataService Studio APIs based on an AnalyticDB for PostgreSQL data source. For more information, see Create and use an exclusive resource group for Data Integration, Create and use an exclusive resource group for scheduling, and Create and use an exclusive resource group for DataService Studio.

Preparations: Permission description and configuration

• If you want to add a data source as a RAM user or by using a RAM role, make sure that the RAM user or RAM role meets one of the following requirements:

  • The RAM user or RAM role is added to the desired workspace as a member and is assigned the Workspace Owner, Workspace Administrator, or O&M role. For more information, see Add a RAM user to a workspace as a member and assign roles to the member.

  • The RAM user or RAM role is attached the AliyunDataWorksFullAccess or AdministratorAccess policy. For more information, see Grant permissions to a RAM user or Grant permissions to a RAM role.

• If you want to add a data source across accounts, you must also make the following permission configurations:

  If you add an AnalyticDB for PostgreSQL data source across Alibaba Cloud accounts, you can use only a RAM role to access the related AnalyticDB for PostgreSQL instance, and you must grant the required permissions to the RAM role.

  Sample scenario

  In this sample scenario, an AnalyticDB for PostgreSQL instance that belongs to Alibaba Cloud Account B is added to a DataWorks workspace that belongs to Alibaba Cloud Account A as a data source, and Alibaba Cloud Account A needs to access the AnalyticDB for PostgreSQL instance.

  • Alibaba Cloud Account A: A DataWorks workspace is created within Alibaba Cloud Account A, and Alibaba Cloud Account A needs to access an AnalyticDB for PostgreSQL instance that is created within Alibaba Cloud Account B.

  • Alibaba Cloud Account B: An AnalyticDB for PostgreSQL instance is created within Alibaba Cloud Account B, and an AnalyticDB for PostgreSQL database is created in the instance. In addition, a RAM role is created within Alibaba Cloud Account B and is assigned to Alibaba Cloud Account A. This way, Alibaba Cloud Account A can access the AnalyticDB for PostgreSQL instance by assuming the RAM role.

  After the preceding operations are performed, you can use Alibaba Cloud Account A to add an AnalyticDB for PostgreSQL instance to DataWorks as a data source and then access the AnalyticDB for PostgreSQL instance.

  Create and grant permissions to a RAM role

  You must use Alibaba Cloud Account B to create a RAM role, grant the RAM role the permissions to view and modify information about the AnalyticDB for PostgreSQL instance, and then assign the RAM role to Alibaba Cloud Account A. This way, Alibaba Cloud Account A can access services within Alibaba Cloud Account B by assuming the RAM role.

  1. Create a RAM role and add Alibaba Cloud Account A as the trusted cloud account of the RAM role. The RAM role is required for Alibaba Cloud Account A to access the AnalyticDB for PostgreSQL instance.

  2. Modify the trust policy of the RAM role to allow Alibaba Cloud Account A to assume the RAM role. The following code shows the document of the modified trust policy:

     {
         "Version": "1",
         "Statement": [
             {
                 "Action": [
                     "sts:AssumeRole",
                     "gpdb:DescribeDBInstanceAttribute",
                     "gpdb:DescribeDBInstances",
                     "gpdb:DescribeResourceUsage",
                     "gpdb:DescribeDBInstanceIPArrayList",
                     "gpdb:DescribeDBClusterIPArrayList",
                     "gpdb:DescribeDBInstancePerformance",
                     "gpdb:DescribeDBInstanceNetInfo",
                     "gpdb:DescribeRegions",
                     "gpdb:ModifySecurityIps"
                 ],
                 "Resource": "*",
                 "Effect": "Allow",
                 "Principal": {
                     "Service": [
                         "ID of Alibaba Cloud Account A@engine.dataworks.aliyuncs.com"
                     ]
                 }
             }
         ]
     }

     For information about how to create a RAM role and modify the trust policy of the RAM role, see Create a RAM role for a trusted Alibaba Cloud account and Edit the trust policy of a RAM role.

Add a data source

DataWorks allows you to add a data source in one of the following modes.

What to do next

After the data source is added, you can perform the following operations based on your business requirements:

• Develop and schedule computing tasks:

  DataWorks DataStudio and Operation Center provide the capabilities of developing and scheduling AnalyticDB for PostgreSQL tasks. If you want to develop AnalyticDB for PostgreSQL tasks based on the AnalyticDB for PostgreSQL data source or periodically schedule AnalyticDB for PostgreSQL tasks, you must go to the DataStudio page in the DataWorks console and associate the AnalyticDB for PostgreSQL data source with DataStudio.

  Note

  You can associate an AnalyticDB for PostgreSQL data source with DataStudio only if the AnalyticDB for PostgreSQL instance based on which the data source is added resides in the same region and belongs to the same Alibaba Cloud account as the workspace to which the data source is added.

• Perform data synchronization:

  DataWorks Data Integration provides AnalyticDB for PostgreSQL Reader and AnalyticDB for PostgreSQL Writer for you to read data from and write data to the AnalyticDB for PostgreSQL data source. You can configure a batch synchronization task for the AnalyticDB for PostgreSQL data source in DataStudio or configure a synchronization task for the AnalyticDB for PostgreSQL data source in Data Integration based on your business requirements to perform data synchronization.

• Manage the data source: You can go to the Data Source page in SettingCenter to perform management operations on the data source. For example, you can edit or delete the data source.