| set hive.mapred.mode=nonstrict; |
| DROP TABLE Employee_Part_n0; |
| |
| CREATE TABLE Employee_Part_n0(employeeID int, employeeName String) partitioned by (employeeSalary double, country string) |
| row format delimited fields terminated by '|' stored as textfile; |
| |
| LOAD DATA LOCAL INPATH "../../data/files/employee.dat" INTO TABLE Employee_Part_n0 partition(employeeSalary='2000.0', country='USA'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee2.dat" INTO TABLE Employee_Part_n0 partition(employeeSalary='2000.0', country='UK'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee2.dat" INTO TABLE Employee_Part_n0 partition(employeeSalary='3000.0', country='USA'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee2.dat" INTO TABLE Employee_Part_n0 partition(employeeSalary='4000.0', country='USA'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee2.dat" INTO TABLE Employee_Part_n0 partition(employeeSalary='3500.0', country='UK'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee.dat" INTO TABLE Employee_Part_n0 partition(employeeSalary='3000.0', country='UK'); |
| |
| -- dynamic partitioning syntax |
| explain |
| analyze table Employee_Part_n0 partition (employeeSalary='4000.0', country) compute statistics for columns employeeName, employeeID; |
| analyze table Employee_Part_n0 partition (employeeSalary='4000.0', country) compute statistics for columns employeeName, employeeID; |
| |
| describe formatted Employee_Part_n0 partition (employeeSalary='4000.0', country='USA'); |
| |
| describe formatted Employee_Part_n0 partition (employeeSalary='4000.0', country='USA') employeeName; |
| |
| -- don't specify all partitioning keys |
| explain |
| analyze table Employee_Part_n0 partition (employeeSalary='2000.0') compute statistics for columns employeeID; |
| analyze table Employee_Part_n0 partition (employeeSalary='2000.0') compute statistics for columns employeeID; |
| |
| describe formatted Employee_Part_n0 partition (employeeSalary='2000.0', country='USA') employeeID; |
| describe formatted Employee_Part_n0 partition (employeeSalary='2000.0', country='UK') employeeID; |
| -- don't specify any partitioning keys |
| explain |
| analyze table Employee_Part_n0 partition (employeeSalary) compute statistics for columns employeeID; |
| analyze table Employee_Part_n0 partition (employeeSalary) compute statistics for columns employeeID; |
| |
| describe formatted Employee_Part_n0 partition (employeeSalary='3000.0', country='UK') employeeID; |
| explain |
| analyze table Employee_Part_n0 partition (employeeSalary,country) compute statistics for columns; |
| analyze table Employee_Part_n0 partition (employeeSalary,country) compute statistics for columns; |
| |
| describe formatted Employee_Part_n0 partition (employeeSalary='3500.0', country='UK') employeeName; |
| |
| -- partially populated stats |
| drop table Employee_n0; |
| CREATE TABLE Employee_n0(employeeID int, employeeName String) partitioned by (employeeSalary double, country string) |
| row format delimited fields terminated by '|' stored as textfile; |
| |
| LOAD DATA LOCAL INPATH "../../data/files/employee.dat" INTO TABLE Employee_n0 partition(employeeSalary='2000.0', country='USA'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee2.dat" INTO TABLE Employee_n0 partition(employeeSalary='2000.0', country='UK'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee2.dat" INTO TABLE Employee_n0 partition(employeeSalary='3500.0', country='UK'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee.dat" INTO TABLE Employee_n0 partition(employeeSalary='3000.0', country='UK'); |
| |
| analyze table Employee_n0 partition (employeeSalary,country) compute statistics for columns; |
| |
| describe formatted Employee_n0 partition (employeeSalary='3500.0', country='UK') employeeName; |
| LOAD DATA LOCAL INPATH "../../data/files/employee2.dat" INTO TABLE Employee_n0 partition(employeeSalary='3000.0', country='USA'); |
| LOAD DATA LOCAL INPATH "../../data/files/employee2.dat" INTO TABLE Employee_n0 partition(employeeSalary='4000.0', country='USA'); |
| |
| analyze table Employee_n0 partition (employeeSalary) compute statistics for columns; |
| |
| describe formatted Employee_n0 partition (employeeSalary='3000.0', country='USA') employeeName; |
| |
| -- add columns |
| alter table Employee_n0 add columns (c int ,d string); |
| |
| LOAD DATA LOCAL INPATH "../../data/files/employee_part.txt" INTO TABLE Employee_n0 partition(employeeSalary='6000.0', country='UK'); |
| |
| analyze table Employee_n0 partition (employeeSalary='6000.0',country='UK') compute statistics for columns; |
| |
| describe formatted Employee_n0 partition (employeeSalary='6000.0', country='UK') employeeName; |
| describe formatted Employee_n0 partition (employeeSalary='6000.0', country='UK') c; |
| describe formatted Employee_n0 partition (employeeSalary='6000.0', country='UK') d; |
| |
| analyze table Employee_n0 partition (employeeSalary,country) compute statistics for columns; |
| |
| describe formatted Employee_n0 partition (employeeSalary='6000.0', country='UK') employeeName; |
| describe formatted Employee_n0 partition (employeeSalary='6000.0', country='UK') c; |
| describe formatted Employee_n0 partition (employeeSalary='6000.0', country='UK') d; |
| |