CODEWITHSUNDEEP
hivehiveql
sofia
sofia

Reputation: 502

Hive insert with multiple select

I want to execute something like this in hive:

insert into mytable values (select count(*) from test2), (select count(*) from test3));

Is there a way to do this?

Upvotes: 0

Views: 1996

Answers (2)

sofia
sofia

Reputation: 502

I found out the answer. It should be something like this:

INSERT INTO TABLE mytable 
    SELECT c1,c2 FROM
      (SELECT count(*) FROM test2) AS c1
    JOIN
      (SELECT count(*) FROM test3) AS c2;

Upvotes: 0

Anil
Anil

Reputation: 420

Why would you need to create a hive table with row count as a column? Assuming that you have to log the row count everyday, I am not sure if we could do this in hive.

But you can try running a shell script something like this if you want a snap shot of the row count of all the tables...

$hive -e 'use schema_name; show tables' | tee tables.txt

This stores all tables in the database in a text file tables.txt Now, write a shell script to get the counts of all the tables that were gathered

while read line
do
echo "$line "
eval "hive -e 'select count(*) from $line'"
done

change the file permissions for the file generated now

$chmod +x count_tables.sh
$./count_tables.sh < tables.txt > counts.txt

If you are looking for a logging the row count periodically, you can store the rowcounts in a csv, by writing in the values as comma separated values and create an external table pointing to the file.

something like 

$./count_tables.sh < tables.txt | sed 's/\t/,/g' > counts.txt

Hope that's the best way to achieve this

Upvotes: 1

Related Questions