# Exp 5 - Hive database + descriptive analytics
# files: emp.csv (id,name,dept,salary), queries.hql

# ---- put data in hdfs ----
hadoop fs -mkdir -p /home/training/hive /home/training/hive_ext
hadoop fs -put emp.csv /home/training/hive/            # for managed table (LOAD moves it)
hadoop fs -put emp.csv /home/training/hive_ext/        # for external table

# ---- run ----
hive -f queries.hql                                    # whole script
# or type: hive    then paste queries one by one, end each with ;
# beeline: beeline -u jdbc:hive2://localhost:10000 -f queries.hql

# ---- check what hive wrote ----
hadoop fs -ls /user/hive/warehouse/company_db.db/      # managed tables live here
hadoop fs -ls /user/hive/warehouse/company_db.db/emp_part   # dept=IT folder = partition
hadoop fs -cat /home/training/hive_out/*               # saved result

# ---- expected (emp.csv given) ----
# n=6 total=319000 mean=53166.67 min=38000 max=72000 median=51500
# dept having avg>45000 : IT 66500 , Finance 54000 , HR 47000   (Sales 38000 filtered out)

# ---- viva ----
# hive = sql like (HiveQL) layer on hdfs, queries turn into mapreduce/tez jobs, for batch not real time
# metastore (mysql/derby) keeps table schema, data stays in hdfs (/user/hive/warehouse)
# managed table: DROP deletes data too. external: DROP deletes only metadata
# LOAD DATA LOCAL = copy from local disk ; without LOCAL = move a file already in hdfs
# partition = folder per column value (faster where-filter), bucket = fixed no. of hashed files
# schema on read (hive) vs schema on write (rdbms)
