| set hive.mapred.mode=nonstrict; |
| CREATE DATABASE db1; |
| |
| CREATE DATABASE db2; |
| |
| CREATE TABLE db1.sourceTable (one string,two string) PARTITIONED BY (ds string); |
| |
| load data local inpath '../../data/files/kv1.txt' INTO TABLE db1.sourceTable partition(ds='2011-11-11'); |
| |
| load data local inpath '../../data/files/kv3.txt' INTO TABLE db1.sourceTable partition(ds='2011-11-11'); |
| |
| CREATE TABLE db2.destinTable (one string,two string) PARTITIONED BY (ds string); |
| |
| EXPLAIN INSERT OVERWRITE TABLE db2.destinTable PARTITION (ds='2011-11-11') |
| SELECT one,two FROM db1.sourceTable WHERE ds='2011-11-11' order by one desc, two desc limit 5; |
| |
| INSERT OVERWRITE TABLE db2.destinTable PARTITION (ds='2011-11-11') |
| SELECT one,two FROM db1.sourceTable WHERE ds='2011-11-11' order by one desc, two desc limit 5; |
| |
| select one,two from db2.destinTable order by one desc, two desc; |
| |
| EXPLAIN INSERT OVERWRITE TABLE db2.destinTable PARTITION (ds='2011-11-11') |
| SELECT one,two FROM db1.sourceTable WHERE ds='2011-11-11' order by one desc, two desc limit 5; |
| |
| INSERT OVERWRITE TABLE db2.destinTable PARTITION (ds='2011-11-11') |
| SELECT one,two FROM db1.sourceTable WHERE ds='2011-11-11' order by one desc, two desc limit 5; |
| |
| select one,two from db2.destinTable order by one desc, two desc; |
| |
| INSERT OVERWRITE TABLE db2.destinTable PARTITION (ds='2011-11-11') IF NOT EXISTS SELECT 100, 200; |
| -- Ensure case sensitivity does not make a difference |
| INSERT OVERWRITE TABLE DB2.DESTINTABLE PARTITION (ds='2011-11-11') IF NOT EXISTS SELECT 100, 200; |
| INSERT OVERWRITE TABLE db2.destinTable PARTITION (ds='2012-11-11') IF NOT EXISTS SELECT 900, 800; |
| -- Ensure case sensitivity does not make a difference |
| INSERT OVERWRITE TABLE DB2.DESTINTABLE PARTITION (ds='2013-11-11') IF NOT EXISTS SELECT 950, 850; |
| |
| select one,two from db2.destinTable order by one desc, two desc; |
| |
| drop table db2.destinTable; |
| |
| drop table db1.sourceTable; |
| |
| DROP DATABASE db1; |
| |
| DROP DATABASE db2; |