DWS结果表

功能描述

DLI将Flink作业的输出数据输出到数据仓库服务（DWS）中。DWS数据库内核兼容PostgreSQL，PostgreSQL数据库可存储更加复杂类型的数据，支持空间信息服务、多版本并发控制（MVCC）、高并发，适用场景包括位置应用、金融保险、互联网电商等。

数据仓库服务（Data Warehouse Service，简称DWS）是一种基于基础架构和平台的在线数据处理数据库，为用户提供海量数据挖掘和分析服务。DWS的更多信息，请参见《数据仓库服务管理指南》。

前提条件

• 创建Flink OpenSource SQL作业时，在作业编辑界面的“运行参数”处，“Flink版本”需要选择“1.12”，勾选“保存作业日志”并设置保存作业日志的OBS桶，方便后续查看作业日志。
• 请务必确保您的账户下已在数据仓库服务（DWS）里创建了DWS集群。如何创建DWS集群，请参考《数据仓库服务管理指南》中“创建集群”章节。
• 请确保已创建DWS数据库表。
• 该场景作业需要运行在DLI的独享队列上，因此要与DWS集群建立增强型跨源连接，且用户可以根据实际所需设置相应安全组规则。
  • 如何建立增强型跨源连接，请参考《数据湖探索用户指南》中增强型跨源连接章节。
  • 如何设置安全组规则，请参见《虚拟私有云用户指南》中“安全组”章节。
• Flink跨源开发场景中直接配置跨源认证信息存在密码泄露的风险，优先推荐您使用DLI提供的跨源认证。

  跨源认证简介及操作方法请参考跨源认证简介。

注意事项

• 若需要使用upsert模式，则必须在DWS结果表和该结果表连接的DWS表都定义主键。
• 若DWS在不同的schema中存在相同名称的表，则在flink opensource sql中需要指定相应的schema。
• 提交Flink作业前，建议勾选“保存作业日志”参数，在OBS桶选项中选择日志保存的位置，方便后续作业提交失败或运行异常时，查看日志并分析问题原因。
• 使用gsjdbc4驱动连接时，加载的数据库驱动类为：org.postgresql.Driver。该驱动为默认，创建表时可以不填该驱动参数。
  例如，使用gsjdbc4驱动连接、upsert模式写入数据到DWS中。

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

  create table dwsSink( car_id STRING, car_owner STRING, car_brand STRING, car_speed INT ) with ( 'connector' = 'gaussdb', 'url' = 'jdbc:postgresql://DwsAddress:DwsPort/DwsDatabase', 'table-name' = 'car_info', 'username' = 'DwsUserName', 'password' = 'DwsPasswrod', 'write.mode' = 'upsert' );

• 使用gsjdbc200驱动连接时，加载的数据库驱动类为：com.huawei.gauss200.jdbc.Driver。
  当DWS表test在名为ads_game_sdk_base的schema下时，可以参考如下样例创建DWS结果表。

  create table dwsSink(
    car_id STRING,
    car_owner STRING,
    car_brand STRING,
    car_speed INT
  ) with (
    'connector' = 'gaussdb',
    'table-name' = 'ads_game_sdk_base\".\"test',
    'driver' = 'com.huawei.gauss200.jdbc.Driver',
    'url' = 'jdbc:gaussdb://DwsAddress:DwsPort/DwsDatabase',
    'username' = 'DwsUserName',
    'password' = 'DwsPasswrod',
    'write.mode' = 'upsert'
  );

语法格式

DWS结果表中不允许指定所有属性为PRIMARY KEY。

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

create table dwsSink (
  attr_name attr_type 
  (',' attr_name attr_type)* 
  (','PRIMARY KEY (attr_name, ...) NOT ENFORCED)
)
with (
  'connector' = 'gaussdb',
  'url' = '',
  'table-name' = '',
  'driver' = '',
  'username' = '',
  'password' = ''
);

参数说明

示例

该示例是从kafka数据源中读取数据，并以insert模式写入DWS结果表中，其具体步骤如下：

1. 参考增强型跨源连接，在DLI上根据DWS和Kafka所在的虚拟私有云和子网分别创建相应的增强型跨源连接，并绑定所要使用的Flink弹性资源池。
2. 设置DWS和Kafka的安全组，添加入向规则使其对Flink的队列网段放通。参考测试地址连通性分别根据DWS和Kafka的地址测试队列连通性。若能连通，则表示跨源已经绑定成功，否则表示未成功。
3. 连接DWS数据库，在DWS中创建相应的表，表名为dws_order，SQL语句参考如下：

   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);

4. 参考创建Flink OpenSource作业，创建flink opensource sql作业，输入以下作业运行脚本，提交运行作业。该作业脚本将Kafka作业数据源，将DWS作为结果表。
   注意：创建作业时，在作业编辑界面的“运行参数”处，“Flink版本”选择“1.12”，勾选“保存作业日志”并设置保存作业日志的OBS桶，方便后续查看作业日志。如下脚本中的加粗参数请根据实际环境修改。

   CREATE TABLE kafkaSource (
     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' = 'kafka',
     'topic' = 'KafkaTopic',
     'properties.bootstrap.servers' = 'KafkaAddress1:KafkaPort,KafkaAddress2:KafkaPort',
     'properties.group.id' = 'GroupId',
     'scan.startup.mode' = 'latest-offset',
     'format' = 'json'
   );
   
   CREATE TABLE dwsSink (
     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://DWSAddress:DWSPort/DWSdbName',
     'table-name' = 'dws_order',
     'driver' = 'org.postgresql.Driver',
     'username' = 'DWSUserName',
     'password' = 'DWSPassword',
     'write.mode' = 'insert'
   );
   
   insert into dwsSink select * from kafkaSource;

5. 连接Kafka集群，向Kafka中输入以下测试数据。

   {"order_id":"202103241000000001", "order_channel":"webShop", "order_time":"2021-03-24 10:00:00", "pay_amount":"100.00", "real_pay":"100.00", "pay_time":"2021-03-24 10:02:03", "user_id":"0001", "user_name":"Alice", "area_id":"330106"}

6. 从DWS中使用如下SQL语句查看数据结果。

    select * from dws_order

   数据结果参考如下：

   202103241000000001	webShop	2021-03-24 10:00:00	100.0	100.0	2021-03-24 10:02:03	0001	Alice	330106

常见问题

• Q：Flink作业运行失败，作业运行日志中如下报错信息，应该怎么解决？

  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：应考虑是跨源没有绑定，或者跨源没有绑定成功。

  • 参考增强型跨源连接章节，重新配置跨源。参考DLI跨源连接DWS失败进行问题排查。

• Q：如果该DWS表在某schema下，则应该如何配置？
  A：当DWS表test在名为ads_game_sdk_base的schema下时，可以参考如下样例中的'table-name'参数配置。

  CREATE TABLE ads_rpt_game_sdk_realtime_ada_reg_user_pay_mm (
    ddate DATE,
    dmin TIMESTAMP(3),
    game_appkey VARCHAR,
    channel_id VARCHAR,
    pay_user_num_1m bigint,
    pay_amt_1m bigint,
    PRIMARY KEY (ddate, dmin, game_appkey, channel_id) NOT ENFORCED
  ) WITH (
    'connector' = 'gaussdb',
    'url' = 'jdbc:postgresql://<yourDwsAddress>:<yourDwsPort>/dws_bigdata_db',
    'table-name' = 'ads_game_sdk_base\".\"test',
    'username' = '<yourUsername>',
    'password' = '<yourPassword>',
    'write.mode' = 'upsert'
  );