DWS Source Table

Function

DLI reads data for Flink jobs from DWS. The DWS database kernel is compatible with PostgreSQL, which allows storage of more complex data types, supports spatial information services, multi-version concurrency control (MVCC), and high concurrency, making it suitable for scenarios such as location-based applications, finance and insurance, and Internet e-commerce.

DWS is an online data processing database built on infrastructure and platforms, providing users with extensive data mining and analytics services.

For further details on DWS, refer to Data Warehouse Service Management Guide.

Prerequisites

• You have created a DWS cluster.

  For details about how to create a DWS cluster, see "Creating a Cluster" in Data Warehouse Service Management Guide.

• You have created a DWS database table.
• An enhanced datasource connection has been created for DLI to connect to DWS clusters, so that jobs can run on the dedicated queue of DLI and you can set the security group rules as required.
  • For details about how to create an enhanced datasource connection, see Enhanced Datasource Connections.
  • For details about how to configure security group rules, see Security Group Overview.
• In Flink cross-source development scenarios, there is a risk of password leakage if datasource authentication information is directly configured. You are advised to use the datasource authentication provided by DLI.

  For details about datasource authentication, see Datasource Authentication Overview.

Precautions

When creating a Flink OpenSource SQL job, you need to set Flink Version to 1.12 on the Running Parameters tab of the job editing page, select Save Job Log, and set the OBS bucket for saving job logs.

Syntax

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

create table dwsSource (
  attr_name attr_type 
  (',' attr_name attr_type)* 
  (','PRIMARY KEY (attr_name, ...) NOT ENFORCED)
  (',' watermark for rowtime_column_name as watermark-strategy_expression)
)
with (
  'connector' = 'gaussdb',
  'url' = '',
  'table-name' = '',
  'username' = '',
  'password' = ''
);

Parameters

Example

In this example, data is read from the DWS data source and written to the Print result table. The procedure is as follows:

1. Create a table named dws_order in DWS.

   create table public.dws_order(
     order_id VARCHAR,
     order_channel VARCHAR,
     order_time VARCHAR,
     pay_amount FLOAT8,
     real_pay FLOAT8,
     pay_time VARCHAR,
     user_id VARCHAR,
     user_name VARCHAR,
     area_id VARCHAR);

   Insert data into the dws_order table.

   insert into public.dws_order
     (order_id,
     order_channel,
     order_time,
     pay_amount,
     real_pay,
     pay_time,
     user_id,
     user_name,
     area_id) values
     ('202103241000000001', 'webShop', '2021-03-24 10:00:00', '100.00', '100.00', '2021-03-24 10:02:03', '0001', 'Alice', '330106'),
     ('202103251202020001', 'miniAppShop', '2021-03-25 12:02:02', '60.00', '60.00', '2021-03-25 12:03:00', '0002', 'Bob', '330110');

2. Create an enhanced datasource connection in the VPC and subnet where DWS locates, and bind the connection to the required Flink elastic resource pool. For details, see Enhanced Datasource Connections.
3. Set DWS security groups and add inbound rules to allow access from the Flink queue. Test the connectivity using the DWS address by referring to Testing Address Connectivity. If the connection is successful, the datasource is bound to the queue. Otherwise, the binding fails.
4. Create a Flink OpenSource SQL job. Enter the following job script and submit the job. The job script uses the DWS data source and the Print result table.
   When you create a job, set Flink Version to 1.12 on the Running Parameters tab. Select Save Job Log, and specify the OBS bucket for saving job logs. Change the values of the parameters in bold as needed in the following script.

   CREATE TABLE dwsSource (
     order_id string,
     order_channel string,
     order_time string,
     pay_amount double,
     real_pay double,
     pay_time string,
     user_id string,
     user_name string,
     area_id string
   ) WITH (
     'connector' = 'gaussdb',
     'url' = 'jdbc:postgresql://DWSIP:DWSPort/DWSdbName',
     'table-name' = 'dws_order',
     'driver' = 'org.postgresql.Driver',
     'username' = 'DWSUserName',
     'password' = 'DWSPassword'
   );
   
   CREATE TABLE printSink (
     order_id string,
     order_channel string,
     order_time string,
     pay_amount double,
     real_pay double,
     pay_time string,
     user_id string,
     user_name string,
     area_id string
   ) WITH (
     'connector' = 'print'
   );
   
   insert into printSink select * from dwsSource;

5. Perform the following operations to view the data result in the taskmanager.out file:
   1. Log in to the DLI console. In the navigation pane, choose Job Management > Flink Jobs.
   2. Click the name of the corresponding Flink job, choose Run Log, click OBS Bucket, and locate the folder of the log you want to view according to the date.
   3. Go to the folder of the date, find the folder whose name contains taskmanager, download the taskmanager.out file, and view result logs.

   The data result is as follows:

   +I(202103241000000001,webShop,2021-03-24 10:00:00,100.0,100.0,2021-03-24 10:02:03,0001,Alice,330106)
   +I(202103251202020001,miniAppShop,2021-03-25 12:02:02,60.0,60.0,2021-03-25 12:03:00,0002,Bob,330110)

FAQ

• Q: What should I do if the job execution fails and the log contains the following error information?

  java.io.IOException: unable to open JDBC writer
  ...
  Caused by: org.postgresql.util.PSQLException: The connection attempt failed.
  ...
  Caused by: java.net.SocketTimeoutException: connect timed out

  A: The datasource connection is not bound or the binding fails.

  • To reconfigure datasource connections, refer to Enhanced Datasource Connections. Rectify the fault by referring to DLI Failed to Connect to DWS Through an Enhanced Datasource Connection.