user2531569 user2531569 - 3 months ago 29
SQL Question

how to delete partitions from hive table dynamically?

I am new to hive. Can someone please help me with this requirement?

My requirement is to delete partitions dynamically. I had a SQL which results various regions (SQL is below: after

ALTER TABLE FROM
). Now I want to delete the regions (partitioned in my hive table) that are returned by my SQL.

I tried in the below way:

ALTER TABLE <TableName> PARTITION(region=tab.region)
FROM
select tab.region from
(SELECT * from Table1) tab join
(select filename from Table2) tab1
on tab1.filename = tab.filename


It's throwing the below exception:

'1:21:13 [ALTER - 0 row(s), 0.008 secs] [Error Code: 40000, SQL State: 42000] Error while compiling statement: FAILED: ParseException
line 1:77 cannot recognize input near 'tab' '.' 'region' in constant
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.008/0.000 sec [0 successful, 0 warnings, 1 errors]


Could someone help me please?

Thanks in Advance

Answer

Shell-script:

$ cat test
#!/bin/sh

IFS=$'\n' 

#get partitions 
part=`hive -e "select tab.region from (SELECT * from Table1) tab  join (select filename from  Table2) tab1 on tab1.filename = tab.filename"`

for p in $part
do

   partition=`echo $p|cut -d '=' -f2`

   echo Drooping partitions .... $partition

   #drop partitions
   hive -e "ALTER TABLE test_2 DROP PARTITION(region=\"$partition\")"

done

output:

$ ./test
OK
Time taken: 1.343 seconds, Fetched: 2 row(s)
Drooping partitions .... 2016-07-26 15%3A00%3A00
Dropped the partition region=2016-07-26 15%3A00%3A00
OK
Time taken: 2.686 seconds
Drooping partitions .... 2016-07-27
Dropped the partition region=2016-07-27
OK
Time taken: 1.612 seconds

update: ~~> Running shell script from hql (This is just a POC, make changes according to your req.) using ! <command> - Executes a shell command from the Hive shell.

test_1.sh:

#!/bin/sh
echo " This massage is from $0 file"

hive-test.hql:

! echo showing databases... ; 
show databases;

! echo showing tables...;
show tables;

! echo runing shell script...;
! /home/cloudera/test_1.sh

output:

$ hive -v -f hive-test.hql
showing databases...

    show databases
OK
default
retail_edw
sqoop_import
Time taken: 0.997 seconds, Fetched: 3 row(s)
showing tables...

    show tables
OK
scala_departments
scaladepartments
stack
stackover_hive
Time taken: 0.062 seconds, Fetched: 4 row(s)
runing shell script...
 This massage is from /home/cloudera/test_1.sh file
Comments