Hive TIPS

LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
OVERWRITE INTO TABLE employees
PARTITION (country = 'US', state = 'CA');

 

If the LOCAL keyword is used, the path is assumed to be in the local filesystem. The data
is copied into the final location. If LOCAL is omitted, the path is assumed to be in the
distributed filesystem. In this case, the data is moved from the path to the final location.

 

FROM staged_employees se
　　INSERT OVERWRITE TABLE employees
　　PARTITION (country = 'US', state = 'OR')
　　SELECT * WHERE se.cnty = 'US' AND se.st = 'OR'
　　INSERT OVERWRITE TABLE employees
　　PARTITION (country = 'US', state = 'CA')
　　SELECT * WHERE se.cnty = 'US' AND se.st = 'CA'
　　INSERT OVERWRITE TABLE employees
　　PARTITION (country = 'US', state = 'IL')
　　SELECT * WHERE se.cnty = 'US' AND se.st = 'IL';

扫描一遍

 

Furthermore, Hive will attempt to run other operations in local mode if the
hive.exec.mode.local.auto property is set to true :
set hive.exec.mode.local.auto=true;

but unfortunately it’s not valid:
hive> SELECT name, salary, deductions["Federal Taxes"],
>
salary * (1 - deductions["Federal Taxes"]) as salary_minus_fed_taxes
> FROM employees
> WHERE round(salary_minus_fed_taxes) > 70000;
FAILED: Error in semantic analysis: Line 4:13 Invalid table alias or
column reference 'salary_minus_fed_taxes': (possible column names are:
name, salary, subordinates, deductions, address)

Use nested select

 

Take care about FLOAT V DOUBLE!!  0.2