set hive hint

[#1]

Hello Geeks,

Need clarifications on below question

1. is hql used to perform multiple quires in single go ?

2. if yes, How do i set hint for different quires ? ex: first query I have to set, SMB join , on second I have to set skew join. ( confused about , the property I have set for first query is also applicable for second query )

3. all the 10 quires are returned results , how to process the result. how industry handling this ?

[#2 · Accepted solution]

Hi @Gobi Subramani

1.In a single HQL file you can trigger multiple queries provided each query has to be terminated with ";"

2.By hint i assume you meant to say to set a property. You have to know the default setting first. Once that is known you can set the property for first query then disable the property and set another property needed for second, then trigger the second query. orelse you should use unix to handle it. Have different property for different query in a seperate HQL file. From unix you have use hive -f or hive -e to trigger the file or hive statements.

3. If you are using select statements it will be displayed in the CLI. Orelse you would perform some DML which you insert into a table. Problem solved. In case if you want to process the output of the select clause you have to handle it with unix.

Hope it helps!!

If it answers your question then please accept it as the best answer!!

[#3]

Hi @Bala Vignesh N V ,

looking for detailed info, assume below are the query in hql ,

        select  count(*) from table1;     

       select sum(col1) from table1 group by col2 ;   

       select sum(col3) from table1 group by col4

first query will returns single column which has count , remaining returns 10 columns each.

where this result will store and how to process and more importantly what is the recommended way handle this or how industry handling this ?

Thanks.

[#4]

@Gobi Subramani it resemble what you are trying to do is auditing. If that is the case then create a audit table with source table name ,target table name, Records loaded and counts and sum. Get the values from select statement and use it with insert to the audit table. It should work fine. If it is not audit then you have to either append the data to a file which I have mentioned in the previous comment or you have to create a table and insert the data into the corresponding tables. Happy Hadooping!

[#5]

@Gobi Subramani

I think you are not getting the point. Select clause wont store the result.

If you really wanted to store the result then it should be

Insert overwrite directory '/pathtostorethefile' select count(*) from table1;

Insert overwrite LOCAL directory '/pathforsecondfile' select sum(col1) from table1 group by col2; --> If you wanted the result to be stored in local directory

[#6]

@Bala Vignesh N V , Thanks.

In case, I am running same hql queries daily, then I need to store the result in the file . once hive tasks are completed then read the file and update the table ? and any other way to achieve it.