MAPREDUCE服务 MRS-Flink SQL逻辑开发建议:多表join场景且join key是联合主键时select字段要显示添加联合主键所有字段

时间：2024-09-06 10:03:29

MAPREDUCE服务 MRS Flink SQL逻辑开发规范

多表join场景且join key是联合主键时select字段要显示添加联合主键所有字段

如果不显示select联合主键所有字段，join算子会丢弃部分主键，导致join spec为NoUniqueKey。

优化前SQL：

create table table1(
  uuid varchar(20),
  name varchar(10),
  age int,
  ts timestamp,
  primary key (uuid) not enforced
) with (
  'connector' = 'datagen',
  'rows-per-second' = '1'
);
create table table2(
  uuid varchar(20),
  name varchar(10),
  age int,
  ts timestamp,
  primary key (uuid, name) not enforced
) with (
  'connector' = 'datagen',
  'rows-per-second' = '1'
);
create table print(
  uuid varchar(20),
  name varchar(10),
  age int,
  ts timestamp
) with ('connector' = 'print');
insert into
  print
select
  t1.uuid,
  t1.name,
  t2.age,
  t2.ts
from
  table1 t1
  join table2 t2 on t1.uuid = t2.uuid;

图1 join spec为NoUniqueKey

优化后SQL：

create table table1(
  uuid varchar(20),
  name varchar(10),
  age int,
  ts timestamp,
  primary key (uuid) not enforced
) with (
  'connector' = 'datagen',
  'rows-per-second' = '1'
);
create table table2(
  uuid varchar(20),
  name varchar(10),
  age int,
  ts timestamp,
  primary key (uuid, name) not enforced
) with (
  'connector' = 'datagen',
  'rows-per-second' = '1'
);
create table print(
  uuid varchar(20),
  name varchar(10),
  name1 varchar(10),
  age int,
  ts timestamp
) with ('connector' = 'print');
insert into
  print
select
  t1.uuid,
  t1.name,
  t2.name as name1,
  t2.age,
  t2.ts
from
  table1 t1
  join table2 t2 on t1.uuid = t2.uuid;

图2 优化后