Rename an output file in hive {was: Re: Possibility of defining the Output directory programmatically}

Hi all, Thanks alot for your help. With your advice, could successfully pass the needed parameters and generate the output file as required. In my use case, the output file needed to be a .gz file. When I run my query with the following commands, the file that gets generated is named as *00_0

Re: Creating Hive table by pulling data from mainFrames

Sqoop is a nice tool to get dAta to/from DB2 to hive and then you can run hive queries on top of it. Lot of people are using this for traditional DBs and hadoop connectivity. Sent from my iPhone On Jul 26, 2012, at 11:32 AM, Siddharth Tiwari wrote: > Hey Team, > > We have huge tables in Mai

Re: HBASE and HIVE Integration

Hi Bejoy, My normal hive query works but I am receiving an error while inserting data into hbase table.. see attached log.. hive> select * from pokes where foo=9; Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks is set to 0 since there's no reduce operator Starting Job =

Re: STREAM (TABLE) IN HIVE

Thank you Bejoy. Regards Abhishek On Thu, Jul 26, 2012 at 3:57 PM, Bejoy Ks wrote: > Hi Abshiek > > From my understanding, when you have the join happening in map or reduce, > the values corresponding to a key from all all table's except one (if two > tables are involved in join on same key, the

Re: STREAM (TABLE) IN HIVE

Hi Abshiek From my understanding, when you have the join happening in map or reduce, the values corresponding to a key from all all table's except one (if two tables are involved in join on same key, then just one table here) are buffered in memory and the left out one is streamed. Usually it i

RE: Creating Hive table by pulling data from mainFrames

Can you export from DB2 to a plain text tab-separated file? You can certainly import that to Hive. Chuck Connell Nuance R&D Data Team Burlington, MA From: Siddharth Tiwari [mailto:siddharth.tiw...@live.com] Sent: Thursday, July 26, 2012 2:33 PM To: hive user list Subject: Creating Hive table by

Re: Creating Hive table by pulling data from mainFrames

YesWe have implemented a solution where we pulled data from DB2 using sqoop. There is certain problem we faced while doing this exercise which we overcame. I think the solution was a specific DB2 jdbc jar and creating a connection pool or something i can't recollect.Debarshi BasakTata Consultancy

Re: HBASE and HIVE Integration

Hi Vijay Your current error looks like some issue with the Select query. Is the select query working as desired? hive> SELECT * FROM pokes where foo=98; Regards Bejoy KS From: vijay shinde To: user@hive.apache.org; Bejoy Ks Sent: Friday, July 27, 2012 12:24

Transitive dependencies with hive

Hello, I know that a custom jar can be added to hive classpath via "--auxpath" command. But for any transitive dependencies that my jar depends on, should they be added explicitly to the classpath too? I tried doing that too, but still get the "ClassNotFoundException" for classes in my transitive

Re: HBASE and HIVE Integration

Hi Bejoy, Thanks for your reply. My Hbase is working fine independently. I was able reolve the zookeeper error. I had issues with hive-site.xml configuration.. Now my job run little bit longer and this is the lastest error I am on Cloudera CDH3U4 demo VM Number of reduce tasks is set to 0 since

Nested SELECT DISTINCT runs out of memory

I have this query that consistently fails with out-of-memory errors. I know it can be re-written without a nested subquery (using count distinct) and then it runs fine. Why does this query fail though? Is this is a known Hive issue? The subquery returns 5M records. SELECT x, COUNT(1) AS num FROM

Re: Creating Hive table by pulling data from mainFrames

try looking out at sqoop .. meant to transfer data from rdbms systems to hive not sure it works with DB2 or not On Fri, Jul 27, 2012 at 12:02 AM, Siddharth Tiwari wrote: > Hey Team, > > We have huge tables in Mainframe DB2. Can some one tell if its possible to > pull data from DB2 in Mainframe t

Creating Hive table by pulling data from mainFrames

Hey Team, We have huge tables in Mainframe DB2. Can some one tell if its possible to pull data from DB2 in Mainframe to hive and then use MapReduce to sort the data in hive and push it back to Mainframe table. Please help ** Cheers !!! Siddharth Tiwari Have a refresh

RE: Can't use OR in left join

How about: select    a.pid,   b.pid tab1 a   left join tab2 b   on (a.pid=b.pid); union all -- Need 'union all' to remove dups for the case of when (a.pid=b.pid) and (substr(a.pid,1,27)=b.pid) select    a.pid,   b.pid tab1 a   left join tab2 b   on (substr(a.pid,1,27)=b.pid); --- Mark E. Sunder

Problem with indexing in Hive

I have written a custom index handler and wanted to test it. However hive is not using it. So I test with simple table (pokes (int foo, string bar)) which comes with hive distribution for testing purpose. Then I created a compact index and set the set hive.optimize.index.filter=true; However, upon

Re: Can't use OR in left join

Hi Bertrand, Thanks for your quick reply, got it now. Thanks. On Fri, Jul 27, 2012 at 12:15 AM, Bertrand Dechoux wrote: > A join is implemented for most cases with a group by. > > Rows in your table a and your table b will be grouped by something let's > say the value of your colum id. > So for

Re: Can't use OR in left join

A join is implemented for most cases with a group by. Rows in your table a and your table b will be grouped by something let's say the value of your colum id. So for each group doing a join is a trivial operation. The simple way is to get all values, separate them somehow to know which are from th

Re: Can't use OR in left join

Thanks Bertrand, You said it's hadoop problem, is it means that if I change to use MapReduce (java MR or streaming), it still can't achieve the purpose? PS: I'm not very familiar with java MR and streaming:) but I have to find a way to implement it. On Thu, Jul 26, 2012 at 11:19 PM, Bertrand De

Re: Can't use OR in left join

That's a problem which is hadoop related and not really hive related. The solution is to use only equal (as you know it). For that, you should first extract your real identifier for a, which can be a.pid or a part of it. I assume that you can know it in advance which one will be used. Bertrand O

Can't use OR in left join

Hi all, I have problem when using left join with hive 0.7.1. I have a query below: select a.pid, b.pid tab1 a left join tab2 b on (a.pid=b.pid or substr(a.pid,1,27)=b.pid); But hive don't support "OR" in left join. Table a is huge, and table b has 4 rows now(will increase). Is there

Re: Group By with rollup in HiveQL?

I would just do a query that produces multiple outputs or select that data into a table and roll that up. On Thursday, July 26, 2012, Bejoy Ks wrote: > Hi > At the moment Hive QL doesn't support rollup clause, however the development is in progress for this feature. > https://issues.apache.org/ji

RE: Hive 0.9 and Indexing

I do not have answers to any of your questions, but I appreciate you raising them. My team is very interested in Hive indexing as well, so I look forward to this discussion. Chuck Connell Nuance R&D Data Team Burlington, MA From: John Omernik [mailto:j...@omernik.com] Sent: Thursday, July 26,

Hive 0.9 and Indexing

I am playing with Hive indexing and a little discouraged by the gap between the potential seen and the amount of documentation around indexing. I am running Hive 0.9 and started playing with indexing as follows: I have a table logs that has a bunch of fields but for this, lets say three. sessionut

Re: unable to see the file

Hi shaik You have given the table location as '/user/hive/warehouse' . When you load data into that table and if you have used overwrite, first the directory is deleted and then created and new file is loaded. So here your hive warehouse dir is same as your table location. So when you used Loa

unable to see the file

Hi Users, Before creating the table i enable the below cmds *set hive.exec.compress.output=true; set io.seqfile.compression.type=BLOCK;* As i created an external table with the below syntax *CREATE EXTERNAL TABLE test_data(vender string,supplier string,order_date st

Re: Group By with rollup in HiveQL?

Hi At the moment Hive QL doesn't support rollup clause, however the development is in progress for this feature. https://issues.apache.org/jira/browse/HIVE-2397  Regards Bejoy KS From: Techy Teck To: user@hive.apache.org Sent: Thursday, July 26, 2012 9:28

Re: HBASE and HIVE Integration

Hi Vijay Is your hbase working independently without any issues. I mean, are you able to insert data into hbase tables without using hive integration? Was the same error message thrown when you directly provided hbase.master instead of zookeeper quorum? Regards Bejoy KS _

Hive Drop database: RESTRICT and CASCADE?

Hi What does RESTRICT and CASCADE stands for in Hive drop database commands. Syntax: DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE]; I tried to use them but no effect. Thanks, Kuldeep

RE: HBASE and HIVE Integration

Hi，Vijay You need to add zookeeper.jar to the hive-site.xml hive.aux.jars.path file:///usr/lib/hive/lib/hive-hbase-handler-0.7.1-cdh3u4.jar,file:///usr/lib/hive/lib/hbase-0.92.jar,file:///usr/lib/hive/lib/zookeeper-3.4.3.jar,file:///usr/lib/hive/lib/hive-contrib-0.7.1-cdh3u4.jar, file:///

Hive, Cognos 8

Hi, Is there a way to connect Hive to Cognos 8 BI (running on a windows server 2003) ? I found an ODBC connector only for 2008. Thanks you. Guillaume Polaert | Cyrès Conseil

Re: HBASE and HIVE Integration

Sent from my iPhone On Jul 25, 2012, at 8:58 PM, vijay shinde wrote: > Hi Bejoy, > > I made some changes as per your suggetion. > > Here is the error from the > http://0.0.0.0:50030/jobdetails.jsp?jobid=job_201207251858_0004 Job: > > Error: java.lang.ClassNotFoundException: org.apache.z

Re: Group By with rollup in HiveQL?

> *select id2, SUM(count2),SUM(coalesce(count1, 0)) as count1 * > > *from table2 * > > * left outer join table1 * > > * on id1=id2 * > > * group by id2 * > > * with rollup * > > > > Whenever I am trying to run the above query, I am getting always as > `FAILED: Parse Error: line mismatched inp