SELECT *FROM
(
SELECT tt.*
FROM
input_tabe_01 tt
FULL OUTER JOIN input_tabe_02 mt
ON (mt.transaction_sn = tt.reference_id)
and tt.create_time >= mt.create_time + INTERVAL '5' MINUTES
and tt.create_time <= mt.create_time - INTERVAL '5' MINUTES
WHERE COALESCE(tt.create_time, mt.create_time) is not NULL
) lt
LEFT JOIN exchange_rate ex
/*+
OPTIONS('streaming-source.enable'='true','streaming-source.partition.include'
= 'all') */
FOR SYSTEM_TIME AS OF lt.event_time ex ON DATE_FORMAT
(lt.event_time, 'yyyy-MM-dd') = cast(ex.date_id as String)
Rui Li <[email protected]> 于2021年2月9日周二 上午10:20写道：
> Hi,
>
> 那join的语句是怎么写的呢？
>
> On Mon, Feb 8, 2021 at 2:45 PM macia kk <[email protected]> wrote:
>
> > 图就是哪个报错
> >
> > 建表语句如下，表示公共表，我也没有改的权限.
> >
> > CREATE EXTERNAL TABLE `exchange_rate`(`grass_region` string COMMENT
> > 'country', `currency` string COMMENT 'currency', `exchange_rate`
> > decimal(25,10) COMMENT 'exchange rate')
> > PARTITIONED BY (`grass_date` date COMMENT 'partition key, yyyy-MM-dd')
> > ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io
> > .parquet.serde.ParquetHiveSerDe'
> > WITH SERDEPROPERTIES (
> > 'serialization.format' = '1'
> > )
> >
> >
> > Rui Li <[email protected]> 于2021年2月8日周一 下午2:17写道：
> >
> > > 你好，图挂了，可以贴一下hive建表的DDL和join的语句是怎么写的么？
> > >
> > > On Mon, Feb 8, 2021 at 10:33 AM macia kk <[email protected]> wrote:
> > >
> > > > Currently the join key in Temporal Table Join can not be empty.
> > > >
> > > > 我的 Hive 表 join DDL 没有设置 is not null ，但是都是有值的，还是会报这个错
> > > >
> > > > [image: image.png]
> > > >
> > >
> > >
> > > --
> > > Best regards!
> > > Rui Li
> > >
> >
>
>
> --
> Best regards!
> Rui Li
>
