如何才能在这个hive命令中多加一个表？

pgvzfuti 于 2021-04-02 发布在 Hive

关注(0)|答案(1)|浏览(488)

dt1=$1

desfile="data_$dt1"
 hql="
 select DISTINCT b.mid, b.create_time, d.content from
     (select mid, create_time, to_id, dt from table_1 where dt>=$dt1 and to_id='1042015' ) b
  join
      (select mid, content from table_2 where dt>=$dt1) d
  on(b.mid=d.mid)
 "

 hive -e "$hql"> $desfile

这个查询是为了从两个表table_1和table_2中获取内容，不同的表有不同的字段。如果我想从另一个表table_3中多获取一个字段，条件是同一个'mid'字段，我应该把查询改成下面这个形式。

hql="
     select DISTINCT a.off_time, b.mid, b.create_time, d.content from
         (select mid, create_time, to_id, dt from table_1 where dt>=$dt1 and to_id='1042015' ) b
      join
          (select mid, content from table_2 where dt>=$dt1) d
      join 
          (select mid, off_time from table_3 where dt>=dt1) a
      on(b.mid=d.mid=a.mid)
     "

Hive

来源：https://stackoverflow.com/questions/65010581/how-can-i-add-one-more-table-to-this-hive-command

1条答案

按热度按时间

qyzbxkaa1#

你不能同时连接三个表，但你应该先连接b和d，然后再连接到a。

select DISTINCT a.off_time, b.mid, b.create_time, d.content from
         (select mid, create_time, to_id, dt from table_1 where dt>=$dt1 and to_id='1042015') b
      join
          (select mid, content from table_2 where dt>=$dt1) d
      on(b.mid = d.mid)
      join 
          (select mid, off_time from table_3 where dt>=$dt1) a
      on(b.mid = a.mid)

赞(0）回复(0）举报 2021-04-03

我来回答

如何才能在这个hive命令中多加一个表？

1条答案

相关问题

热门标签

最新问答