One Star

Execute hive script (.HQL) from talend

Hi,
Is there any way that i can just execute the hive script which is there on hdfs from talend?
All i can see in talend is component to write hive queries like thiveconncetion,thiveload etc... but none of them have way to execute hive script.
you can easily do this in oozie editor (Hue) by calling and executing hive script.
is this possible in talend?
Please advice!
Thanks

15 REPLIES
Employee

Re: Execute hive script (.HQL) from talend

Hi nairgokul70,
Of course ,you can execute your hive script in Talend,using tHiveRow component maybe can help you .
Regards
jjzhou
One Star

Re: Execute hive script (.HQL) from talend

Hi,
Can you please explain how?
i am not seeing anything in thiverow which can call a script?

Thanks
One Star

Re: Execute hive script (.HQL) from talend

Just like for any other Talend 'row' component, whatever you can do in Hive QL directly, you can do from Talend in the query window - from selecting records to altering tables etc...
One Star

Re: Execute hive script (.HQL) from talend

Hi,
Thanks for reply,
But i am trying to execute hive script in row component and its not working.
I have script on hdfs file system of my hadoop cluster
and i just want to execute that script. usually to execute hive script you just say
hive -f /pathtofile/script.hql
i am trying to do this row component but i am getting an error stating
hive cannot be resolved to a variable
f cannot be resolved to a variable
Syntax error on token ""/user/hive/warehouse/queries/vgt_repository/loaddata/IncrementalLoad_AccountLog.hql"", delete this token
I tried with "" and with double quotes etc but its still not working.
Please suggest.
One Star

Re: Execute hive script (.HQL) from talend

Just a thought - I wonder if tSystem might work... (example calling a shell script attached)
Employee

Re: Execute hive script (.HQL) from talend

Agree with @willm.
If you want to execute the hive command "hive -f /pathtofile/script.hql", the tSystem will be required, and a local installation of Hive will be required.
One Star

Re: Execute hive script (.HQL) from talend

Hello,
a local installation of Hive will be required!
what you mean by above? i am executing the talend from machine different from hadoop cluster. when you say local does that mean i have to have hive installed on the machine from where i am running talend?
If that is the case the whole purpose of using talend as platform independent tool gets defeated!. Also, if i have use to bash like component i.e. tsystem component than the point of having all the hive components also gets defeated!
currently, i have divided all the scripts into different thiverow components and executing them. But now i am ending up with many components in the same job!
In my personal opinion executing a script file is very basic requirement!. I am evaluating talend for client with big data requirements and things like this is hard to explain to the client!
The java api have already class called
java.util.List executeScript(HiveScript script)
throws org.springframework.dao.DataAccessException
why isnt the above implemented in any of the component?
Regards
Gokul
Employee

Re: Execute hive script (.HQL) from talend

Hello,
When you execute: "hive -f /pathtofile/script.hql", you call the hive CLI, which indeed requires hive to be installed locally. It's nevertheless not how we usually do with Talend.
We don't have a component which allows executing a Hive QL file currently.
Does your HQL file contain 1 query per line ?
One Star

Re: Execute hive script (.HQL) from talend

Gokul - pardon me, because I'm sure you already thought of this - but what about deploying your Talend job on the server running Hive?
In the example I showed with the sh script, I was developing on a Windows machine, and I'd independently tested the sh script on a Linux box and it worked. Once I deployed the completed job on the Linux box, it worked as designed... Same thing if you're using TAC, you could install a Job Server on a box running Hive and constrain this job to only run on that Server.
One Star

Re: Execute hive script (.HQL) from talend

rdubois - I see where you're going with your question :-). Parse the lines and execute them in HiveRow within Talend? Smart if it works...
Employee

Re: Execute hive script (.HQL) from talend

Yes, thinking about reading the HQL file with a tFileInputFullRaw (or tFileInputDelimited) and using the tFlowToIterate and finally the tHiveRow. In such a case, the tHiveRow would be called for each single input query.
One Star

Re: Execute hive script (.HQL) from talend

Hi,
I have the hql statement which should create the directory in the local unix server.Something like below
insert overwrite local directory 'abc/xyz/sum' select sum(open),sum(high),sum(low),sum(close),sum(volume) from database.tablename;
Ideally, When you execute this statement, sum folder will be created inside /abc/xyz and inside the folder , a file will be created with the result of the select statement.
However,When i am using thiverow ,It is not creating folder neither with thiveinput.
If i am executing manually in Hive CLI, Like hive -f hqlname.hql,It is giving the desired output.
But, through talend, I am not able to achieve.
It is not even giving any error message.
Can someone please advise.
One Star

Re: Execute hive script (.HQL) from talend

Hi,
i am getting below error while trying to use sqoop import from oracle.
Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/lang3/StringUtils
at org.apache.sqoop.manager.oracle.OracleUtils.escapeIdentifier(OracleUtils.java:36)
at org.apache.sqoop.manager.OracleManager.escapeTableName(OracleManager.java:927)
at org.apache.sqoop.manager.OracleManager.getColNamesQuery(OracleManager.java:264)
at org.apache.sqoop.manager.SqlManager.getColTypesQuery(SqlManager.java:240)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:245)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:328)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1853)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1653)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
disconnected
disconnected
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at test.oth_test_0_1.oth_test.tSqoopImport_1Process(oth_test.java:586)
at test.oth_test_0_1.oth_test.tOracleConnection_1Process(oth_test.java:428)
at test.oth_test_0_1.oth_test.runJobInTOS(oth_test.java:994)
at test.oth_test_0_1.oth_test.main(oth_test.java:851)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.lang3.StringUtils
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
... 18 more
Job oth_test ended at 16:49 05/01/2017.

I have added ojdbc6.jar and class name as "oracle.jdbc.driver.OracleDriver" in the component.
i am using below
please help me further.
thanks,
Rishit Shah
One Star

Re: Execute hive script (.HQL) from talend

Sorry for wrong reply here.
One Star

Re: Execute hive script (.HQL) from talend

Sorry for wrong reply here.

Sorry to bother you.
I installed Hadoop 1.1.2 and Hive 0.9.0. I didn't set username and password when i installed Hive. Now i use hiveconnection component. What can i set in the  username and password part?
Thanks.