vithalsamp

hduser@vithal-Inspiron-3558:~$ nzload -host 192.168.0.100 -u admin -pw password -db training -df 'hdfs:///data/stud_dtls/stud_detls.csv' -t stud_dtls -delim ',' 
Error: Specified value 'hdfs:///data/stud_dtls/stud_detls.csv' in option Datafile does not exists. 
Usage: nzload [-h|-rev] [<options>] 
Try using 'nzload -h' for more information. 
hduser@vithal-Inspiron-3558:~$ 

vithalsamp

hive> ANALYZE TABLE stud_dtls COMPUTE STATISTICS;
Query ID = impadmin_20180117141106_5b55d716-b5b3-4fa8-82db-35be9fda85dc
Total jobs = 1
Launching Job 1 out of 1
….
Table ds_tbl_db.stud_dtls stats: [numFiles=9, numRows=9, totalSize=63, rawDataSize=54]
OK
Time taken: 8.248 seconds
hive>

vithalsamp

hive> describe extended stud_dtls;
OK
stud_id int
sub_cod int
Detailed Table Information 
Table(tableName:stud_dtls, 
dbName:test_db, 
owner:admin, 
createTime:1514193965, 
lastAccessTime:0, 

vithalsamp

$ hdfs dfs -du -s /user/hive/warehouse/test_database.db/* | sort -rn | awk '/^[0-9]+/ { print int($1/(1024**2)) " [MB] --> " $2 }'
775 [MB] --> /user/hive/warehouse/test_database.db/store
372 [MB] --> /user/hive/warehouse/test_database.db/temp_store_sales
281 [MB] --> /user/hive/warehouse/test_database.db/temp_catalog_sales
215 [MB] --> /user/hive/warehouse/test_database.db/temp_inventory
139 [MB] --> /user/hive/warehouse/test_database.db/temp_web_sales
99 [MB] --> /user/hive/warehouse/test_database.db/temp_customer
….

vithalsamp

hive> select * from stud where id in (select stud_id from dept);
Query ID = admin_20180118151357_c109594d-2079-441f-afc0-328525b9709c
Total jobs = 1
Launching Job 1 out of 1
…
OK
1 100 ABC
2 200 BBB
3 200 CCC
4 300 DDD

vithalsamp

hive> select * from (select * from dept) a ;
OK
100 Chem 1
200 CS 2
300 Phy 3
100 Chem 5
300 Phy 4
Time taken: 0.086 seconds, Fetched: 5 row(s)
hive>

vithalsamp

hive> SELECT stud.name,dept.dep_name
> FROM stud
> LEFT JOIN dept
> on (stud.id = dept.stud_id)
> ;
Query ID = admin_20180118195811_e94be9f5-176d-4e8b-8743-64d2bd12c826
Total jobs = 1
Launching Job 1 out of 1
…
OK

vithalsamp

hive> select * from stud where exists (select 1 from dept where dept.stud_id = stud.id);
Query ID = admin_20180118191502_ecaf3e2b-67c4-4e7b-88ba-a068b1e80e47
Total jobs = 1
…
OK
1 100 ABC
2 200 BBB
3 200 CCC
4 300 DDD
5 100 EEE

vithalsamp

select
max(new_col) as max_col
from
(
--this part of the query is a derived table
select col+1 as new_col from table
) as tab1 -- derived table have an alias name. 

hive> select max(new_col) as max_col from (select id+1 as new_col from dept) tab1 ;
Query ID = admin_20180119131810_4e7e71e6-4ac7-4d47-8ef0-3ca58e90999e

vithalsamp

hive> select 'fruitbar' RLIKE '^f.*r$';
OK
true
Time taken: 0.218 seconds, Fetched: 1 row(s)
hive>