DWS维表

功能描述

创建DWS表用于与输入流连接，从而生成相应的宽表。

前提条件

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

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

注意事项

创建Flink OpenSource SQL作业时，在作业编辑界面的“运行参数”处，“Flink版本”需要选择“1.12”，勾选“保存作业日志”并设置保存作业日志的OBS桶，方便后续查看作业日志。

语法格式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

create table dwsSource (
  attr_name attr_type 
  (',' attr_name attr_type)* 
)
with (
  'connector' = 'gaussdb',
  'url' = '',
  'table-name' = '',
  'username' = '',
  'password' = ''
);

参数说明

示例

从Kafka源表中读取数据，将DWS表作为维表，并将二者生成的宽表信息写入Kafka结果表中，其具体步骤如下：

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

   create table public.area_info(
     area_id VARCHAR,
     area_province_name VARCHAR,
     area_city_name VARCHAR,
     area_county_name VARCHAR,
     area_street_name VARCHAR,
     region_name VARCHAR);

4. 连接DWS数据库实例，向DWS维表area_info中插入测试数据，其语句如下：

     insert into area_info
     (area_id, area_province_name, area_city_name, area_county_name, area_street_name, region_name) 
     values
     ('330102', 'a1', 'b1', 'c1', 'd1', 'e1'),
     ('330106', 'a1', 'b1', 'c2', 'd2', 'e1'),
     ('330108', 'a1', 'b1', 'c3', 'd3', 'e1'),
     ('330110', 'a1', 'b1', 'c4', 'd4', 'e1');

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

   CREATE TABLE orders (
     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,
     proctime as Proctime()
   ) WITH (
     'connector' = 'kafka',
     'topic' = 'KafkaSourceTopic',
     'properties.bootstrap.servers' = 'KafkaAddress1:KafkaPort,KafkaAddress2:KafkaPort',
     'properties.group.id' = 'dws-order',
     'scan.startup.mode' = 'latest-offset',
     'format' = 'json'
   );
   
   --创建地址维表
   create table area_info (
       area_id string, 
       area_province_name string, 
       area_city_name string, 
       area_county_name string,
       area_street_name string, 
       region_name string 
   ) WITH (
     'connector' = 'gaussdb',
     'driver' = 'org.postgresql.Driver',
     'url' = 'jdbc:gaussdb://DwsAddress:DwsPort/DwsDbName',
     'table-name' = 'area_info',
     'username' = 'DwsUserName',
     'password' = 'DwsPassword',
     'lookup.cache.max-rows' = '10000',
     'lookup.cache.ttl' = '2h'
   );
   
   --根据地址维表生成详细的包含地址的订单信息宽表
   create table order_detail(
       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,
       area_province_name string,
       area_city_name string,
       area_county_name string,
       area_street_name string,
       region_name string
   ) with (
     'connector' = 'kafka',
     'topic' = 'KafkaSinkTopic',
     'properties.bootstrap.servers' = 'KafkaAddress1:KafkaPort,KafkaAddress2:KafkaPort',
     'format' = 'json'
   );
   
   insert into order_detail
       select orders.order_id, orders.order_channel, orders.order_time, orders.pay_amount, orders.real_pay, orders.pay_time, orders.user_id, orders.user_name,
              area.area_id, area.area_province_name, area.area_city_name, area.area_county_name,
              area.area_street_name, area.region_name  from orders
       left join area_info for system_time as of orders.proctime as area on orders.area_id = area.area_id;

6. 连接Kafka集群，向kafka中source topic中插入如下测试数据：

   {"order_id":"202103241606060001", "order_channel":"appShop", "order_time":"2021-03-24 16:06:06", "pay_amount":"200.00", "real_pay":"180.00", "pay_time":"2021-03-24 16:10:06", "user_id":"0001", "user_name":"Alice", "area_id":"330106"}
   
   {"order_id":"202103251202020001", "order_channel":"miniAppShop", "order_time":"2021-03-25 12:02:02", "pay_amount":"60.00", "real_pay":"60.00", "pay_time":"2021-03-25 12:03:00", "user_id":"0002", "user_name":"Bob", "area_id":"330110"}
   
   {"order_id":"202103251505050001", "order_channel":"qqShop", "order_time":"2021-03-25 15:05:05", "pay_amount":"500.00", "real_pay":"400.00", "pay_time":"2021-03-25 15:10:00", "user_id":"0003", "user_name":"Cindy", "area_id":"330108"}

7. 连接Kafka集群，读取kafka中sink topic中数据，结果参考如下：

   {"order_id":"202103241606060001","order_channel":"appShop","order_time":"2021-03-24 16:06:06","pay_amount":200.0,"real_pay":180.0,"pay_time":"2021-03-24 16:10:06","user_id":"0001","user_name":"Alice","area_id":"330106","area_province_name":"a1","area_city_name":"b1","area_county_name":"c2","area_street_name":"d2","region_name":"e1"}
   
   {"order_id":"202103251202020001","order_channel":"miniAppShop","order_time":"2021-03-25 12:02:02","pay_amount":60.0,"real_pay":60.0,"pay_time":"2021-03-25 12:03:00","user_id":"0002","user_name":"Bob","area_id":"330110","area_province_name":"a1","area_city_name":"b1","area_county_name":"c4","area_street_name":"d4","region_name":"e1"}
   
   {"order_id":"202103251505050001","order_channel":"qqShop","order_time":"2021-03-25 15:05:05","pay_amount":500.0,"real_pay":400.0,"pay_time":"2021-03-25 15:10:00","user_id":"0003","user_name":"Cindy","area_id":"330108","area_province_name":"a1","area_city_name":"b1","area_county_name":"c3","area_street_name":"d3","region_name":"e1"}

常见问题

• 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：如下示例是使用schema为dbuser2下的表area_info：

  --创建地址维表
  create table area_info (
      area_id string, 
      area_province_name string,
      area_city_name string,
      area_county_name string,
      area_street_name string, 
      region_name string 
  ) WITH (
   'connector' = 'gaussdb',
    'driver' = 'org.postgresql.Driver',
    'url' = 'jdbc:postgresql://DwsAddress:DwsPort/DwsDbname',
    'table-name' = 'dbuser2.area_info',
    'username' = 'DwsUserName',
    'password' = 'DwsPassword',
    'lookup.cache.max-rows' = '10000',
    'lookup.cache.ttl' = '2h'
  );