Flink SQL的N way join

阳光穿透心脏的1/2处 2022-10-29 03:28 145阅读 0赞

# 概述 #

本文用来详解\[1\]

# SQL结构 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FwcGxleXVjaGk_size_16_color_FFFFFF_t_70][]

\[1\]中的整个md文件中的sql其实是想表达上图的这个逻辑

这里的SQL写的时候需要注意一个细节:

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>connector设置</td> 
  </tr> 
  <tr> 
   <td>维度表</td> 
   <td>'connector' = 'upsert-kafka'</td> 
  </tr> 
  <tr> 
   <td>事实表</td> 
   <td>'connector' = 'kafka'&nbsp; &nbsp;</td> 
  </tr> 
 </tbody> 
</table>

所谓的N way Join就是在SQL里面一个fact table和多个维度表进行join,如下:

SELECT 
      t.actual_departure_date, 
      p.first_name,
      p.last_name,
      b.channel, 
      os.city AS origin_station,
      ds.city AS destination_station
    FROM train_activities t
    LEFT JOIN booking_channels FOR SYSTEM_TIME AS OF t.actual_departure_date AS b 
    ON t.booking_channel_key = b.booking_channel_key
    LEFT JOIN passengers FOR SYSTEM_TIME AS OF t.actual_departure_date AS p
    ON t.passenger_key = p.passenger_key
    LEFT JOIN stations FOR SYSTEM_TIME AS OF t.actual_departure_date AS os
    ON t.origin_station_key = os.station_key
    LEFT JOIN stations FOR SYSTEM_TIME AS OF t.actual_departure_date AS ds
    ON t.destination_station_key = ds.station_key;

这个实验对硬件资源有些要求，

如果资源不够的话，提交一段时间后，web UI会显示任务会超时

**Reference:**

\[1\][https://github.com/ververica/flink-sql-cookbook/blob/master/joins/05/05\_star\_schema.md][https_github.com_ververica_flink-sql-cookbook_blob_master_joins_05_05_star_schema.md]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FwcGxleXVjaGk_size_16_color_FFFFFF_t_70]: /images/20221024/596dd8a179314a25ac5b4b92f1a5c6a7.png
[https_github.com_ververica_flink-sql-cookbook_blob_master_joins_05_05_star_schema.md]: https://github.com/ververica/flink-sql-cookbook/blob/master/joins/05/05_star_schema.md