Flink OpenSource SQL Jobs Using DEW to Manage Access Credentials

Constraints

DEW can be used to manage access credentials only in Flink 1.15. When creating a Flink job, select version 1.15 and configure the information of the agency that allows DLI to access DEW for the job.

Prerequisites

• A shared secret has been created on the DEW console and the secret value has been stored. For details, see Creating a Secret.
• An agency has been created and authorized for DLI to access DEW. The agency must have been granted the following permissions:
  • Permission of the ShowSecretVersion interface for querying secret versions and secret values in DEW: csms:secretVersion:get.
  • Permission of the ListSecretVersions interface for listing secret versions in DEW: csms:secretVersion:list.
  • Permission to decrypt DEW secrets: kms:dek:decrypt

  For details about agency permission examples, see Creating a Custom DLI Agency and Agency Permission Policies in Common Scenarios.

• On the DLI management console, create an enhanced datasource connection and configure the network connection between DLI and the data source.

  For details, see Overview of Enhanced Datasource Connections.

Syntax Format

create table tableName(
   attr_name attr_type
   (',' attr_name attr_type)* 
   (',' WATERMARK FOR rowtime_column_name AS watermark-strategy_expression) )
 with (
    ... 
  'dew.endpoint'='',
  'dew.csms.secretName'='',
  'dew.csms.decrypt.fields'='',
  'dew.projectId'='',
  'dew.csms.version'=''
   );

Parameters

Example

This example demonstrates how to configure Flink OpenSource SQL to manage access credentials using DEW by generating random data through a DataGen table and outputting it to a MySQL result table.

1. Create a shared secret on DEW.
   1. Log in to the DEW console.
   2. In the navigation pane on the left, choose Cloud Secret Management Service > Secrets.
   3. Click Create Secret. On the displayed page, configure basic secret information.
      • Secret Name: Enter a secret name. In this example, the name is secretInfo.
      • Secret Value: Enter the username and password for logging in to the RDS for MySQL DB instance.
        • The key in the first line is MySQLUsername, and the value is the username for logging in to the DB instance.
        • The key in the second line is MySQLPassword, and the value is the password for logging in to the DB instance.
      Figure 1 Setting the secret values
      
   4. Set other parameters as required and click OK.

      For details, see Creating a Secret.

2. Configure DEW to manage access credentials in the Flink OpenSource SQL job.

   Ensure that an enhanced datasource connection between DLI and MySQL has been created. For details, see Creating an Enhanced Datasource Connection.

   Ensure that an agency has been created for DLI to access DEW and authorization has been completed. For details, see Creating a Custom DLI Agency.

   The following shows an example configuration for a Flink OpenSource SQL job:

   create table dataGenSource(
      user_id string,
      amount int) with (
      'connector' = 'datagen',
      'rows-per-second' = '1', --Generate a piece of data per second.
     'fields.user_id.kind' = 'random', --Specify a random generator for the user_id field.
     'fields.user_id.length' = '3' --Limit the length of user_id to 3.
   );
     CREATE TABLE jdbcSink (
       user_id string,
      amount int
    )
   WITH (
      'connector' = 'jdbc',
      'url? = 'jdbc:mysql://MySQLAddress:MySQLPort/flink',--flink is the MySQL database where the orders table locates.
      'table-name' = 'orders',
      'username' = 'MySQLUsername',  -- Shared secret in DEW whose name is secretInfo and version is v1. The key MySQLUsername defines the secret value. The value is the user's sensitive information.
    'password' = 'MySQLPassword',  -- Shared secret in DEW whose name is secretInfo and version is v1. The key MySQLPassword defines the secret value. The value is the user's sensitive information.
     'sink.buffer-flush.max-rows' = '1',
    'dew.endpoint'='endpoint', --Endpoint information for the DEW service being used
     'dew.csms.secretName'='secretInfo', --Name of the DEW shared secret
     'dew.csms.decrypt.fields'='username,password', --The password field value must be decrypted and replaced using DEW CSMS.
     'dew.csms.version'='v1'
   );
     insert into jdbcSink select * from dataGenSOurce;