Скачать с ютуб hive left outerJoin - how to perform left outer join in hive в хорошем качестве

hive left outerJoin - how to perform left outer join in hive

======================= Joins: ============================================================================= create table if not exists products (name string, id int , price int) row format delimited fields terminated by '\t' lines terminated by '
' stored as textfile; create table if not exists sales (name string, year int , percentage int) row format delimited fields terminated by '\t' lines terminated by '
' stored as textfile; products: books 1 10 java 2 100 hadoop 3 1000 python 4 500 sales: books 2010 10 books 2012 20 java 2013 50 java 2012 13 java 2011 20 hadoop 2010 10 hadoop 2013 40 load data local inpath '${env:HOME}/work/hive_inputs/products.txt' overwrite into table products; load data local inpath '${env:HOME}/work/hive_inputs/sales.txt' overwrite into table sales; select * from products; books 1 10 java 2 100 hadoop 3 1000 python 4 500 select * from sales; books 2010 10 books 2012 20 java 2013 50 java 2012 13 java 2011 20 hadoop 2010 10 hadoop 2013 40 Inner Join: ----------- select products.* , sales.* from products join sales on products.name = sales.name; books 1 10 books 2010 10 books 1 10 books 2012 20 hadoop 3 1000 hadoop 2010 10 hadoop 3 1000 hadoop 2013 40 java 2 100 java 2013 50 java 2 100 java 2012 13 java 2 100 java 2011 20 Left Outer Join: ---------------- select products.* , sales.* from products left outer join sales on products.name = sales.name; books 1 10 books 2010 10 books 1 10 books 2012 20 hadoop 3 1000 hadoop 2010 10 hadoop 3 1000 hadoop 2013 40 java 2 100 java 2013 50 java 2 100 java 2012 13 java 2 100 java 2011 20 python 4 500 NULL NULL NULL Right Outer Join: ----------------- select products.* , sales.* from products right outer join sales on products.name = sales.name; books 1 10 books 2010 10 books 1 10 books 2012 20 hadoop 3 1000 hadoop 2010 10 hadoop 3 1000 hadoop 2013 40 java 2 100 java 2013 50 java 2 100 java 2012 13 java 2 100 java 2011 20 Full Outer Join: ------------------ select products.* , sales.* from products full outer join sales on products.name = sales.name; books 1 10 books 2010 10 books 1 10 books 2012 20 hadoop 3 1000 hadoop 2010 10 hadoop 3 1000 hadoop 2013 40 java 2 100 java 2013 50 java 2 100 java 2012 13 java 2 100 java 2011 20 python 4 500 NULL NULL NULL Map Join: --------- select /*+ mapjoin(sales) / products. , sales.* from products join sales on products.name = sales.name; books 1 10 books 2010 10 books 1 10 books 2012 20 java 2 100 java 2013 50 java 2 100 java 2012 13 java 2 100 java 2011 20 hadoop 3 1000 hadoop 2010 10 hadoop 3 1000 hadoop 2013 40 select /*+ mapjoin(products) / products. , sales.* from products join sales on products.name = sales.name; books 1 10 books 2010 10 books 1 10 books 2012 20 java 2 100 java 2013 50 java 2 100 java 2012 13 java 2 100 java 2011 20 hadoop 3 1000 hadoop 2010 10 hadoop 3 1000 hadoop 2013 40 select /*+ mapjoin(products) / products. , sales.* from products right outer join sales on products.name = sales.name; books 1 10 books 2010 10 books 1 10 books 2012 20 java 2 100 java 2013 50 java 2 100 java 2012 13 java 2 100 java 2011 20 hadoop 3 1000 hadoop 2010 10 hadoop 3 1000 hadoop 2013 40 python 4 500 NULL NULL NULL select /*+ mapjoin(sales) / products. , sales.* from products left outer join sales on products.name = sales.name; books 1 10 books 2010 10 books 1 10 books 2012 20 java 2 100 java 2013 50 java 2 100 java 2012 13 java 2 100 java 2011 20 hadoop 3 1000 hadoop 2010 10 hadoop 3 1000 hadoop 2013 40 Semi Join: ---------- select * from products left semi join sales on products.name = sales.name; select * from sales right semi join products on sales.name = products.name; books 1 10 hadoop 3 1000 java 2 100