LEFT SEMI JOIN用法 附数据及代码可hive建表自己测试

建hive表

本文使用的excel表格下载链接：
链接：https://pan.baidu.com/s/1HUzw3-vs3rXC80l_DVgmyQ
提取码：0f59
有更简单的建表方式，如建完空表之后使用insert into，我下面这样做主要为了记录下我工作中建表的四个步骤：
1.将csv文件拷贝到sublime中，另存为任何格式，保存完成后，再改成txt格式，如linshi.txt
2.将txt文件通过xftp拉到虚拟机中
3.在hive使用建表语句建空表

drop table if exists analyst.dividends;
CREATE TABLE IF NOT EXISTS analyst.dividends(   
jiaoyi             string          comment 'exchange交易',            
symbol             string          comment '标志',        
ymd                string          comment '年月日',        
dividend           float           comment '股息'
 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

drop table if exists analyst.stocks;
CREATE TABLE IF NOT EXISTS analyst.stocks(   
jiaoyi             string          comment 'exchange交易',            
symbol             string          comment '标志',        
ymd                string          comment '年月日',        
dividend           float           comment '股息',
price_open         float           comment 'none',
price_high         float           comment 'none',
price_low          float           comment 'none',
price_close        float           comment 'none',
volume             int             comment 'none',
price_adj_close    float           comment 'none'
 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

4.通过xshell在空表中存入txt数据。

sh /abcde-hive.sh /data2/tmphdfs/fiona/linshi.txt   analyst.dividends

到目前为止
我们有2个表格

SELECT * from analyst.stocks

SELECT * from analyst.dividends

LEFT SEMI JOIN使用实例

现在试图返回限定的股息支付日内的股票交易记录
我们很容易想到SQL方言：

SELECT s.ymd, s.symbol, s.price_close FROM stocks s
WHERE s.ymd, s.symbol IN
(SELECT d.ymd, d.symbol FROM dividends d);

但是，hive不支持这种语法
幸好，我们可以使用如下的 LEFT SEMI-JOIN语法达到同样的目的：

SELECT s.ymd,
       s.symbol,
       s.price_close
FROM analyst.stocks s LEFT SEMI JOIN 
analyst.dividends d ON s.ymd = d.ymd
AND s.symbol = d.symbol;

结果：

总结

可见，
1.LEFT SEMI JOIN语句，大多数sql会通过in结构来处理。
2.LEFT SEMI JOIN语句，select 和 where 语句不能引用右表中的字段。
3.因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。 。
比如以下A表和B表进行 join 或 left semi join，然后 select 出所有字段，结果区别如下：

注意：蓝色叉的那一列实际是不存在left semi join中的，因为select 和 where 语句不能引用右表中的字段。

引用：

https://blog.csdn.net/HappyRocking/article/details/79885071
Programming Hive. Data Warehouse and Query Language for Hadoop by Edward Capriolo, Dean Wampler, Jason Rutherglen (z-lib.org)