problem while performing union on twotables
Hi, I am trying to perform union of two tables which are having identical schemas and distinct data.There are two tables 'oldtable' and 'newtable'. The old table contains the information of old users and the new table will conatin the information of new user. I am trying to update the new entry in the old table using the following query. INSERT OVERWRITE TABLE oldtable SELECT * FROM (SELECT * FROM oldtable UNION select * from newtable); executing the above query results, FAILED:Parse Error:line 1.68 mismatched input 'select' expecting ALL in query operator. I am currently using Hadoop verso 0.20.2 and Hive 0.4.1 from apache disctribution. Will Hive 0.4.1 support UNION and UNION ALL queries? Is the above problem is dueto the Hive version or due to a wrong query? Also I am not using any partitions in these tables and the table format is TextFile. Any pointers in this regard would be highly helpful. -- Regards, Sangita --
Hints on whether my data is "big enough"?
I'm considering a new project, for which I'm fairly sure that a conventional, relational Data Warehouse approach will be too "small-data." But everywhere I look, it seems like it's nowhere big enough to fit "big-data" approaches, like Hive or Hadoop. Is there some FAQ or blog or wiki or war-story archive or whatever with anything like guidance on making this choice? -==- Jack Repenning Chief Technology Officer CollabNet, Inc. 8000 Marina Boulevard, Suite 600 Brisbane, California 94005 office: +1 650.228.2562 twitter: http://twitter.com/jrep
RE: Hive Not Returning Column Names, even what not using 'When'??
Hi Mark, You can use JDBC driver provided by Amazon Elastic MapReduce service. When you use that driver with SQL Squirrel it returns column names. Here are the docs on how to get that driver: http://buyitnw.appspot.com/docs.amazonwebservices.com/ElasticMapReduce/latest/DeveloperGuide/index.html?UsingEMR_Hive.html Regards, Peter Sirota From: Sunderlin, Mark [mailto:mark.sunder...@teamaol.com] Sent: Thursday, February 17, 2011 1:00 PM To: user@hive.apache.org Subject: Hive Not Returning Column Names, even what not using 'When'?? When using either Hue or using Squirrel and jdbc, I get "_col0, _col1, _col2 ..." as my columns names when querying my hive tables. I see in https://issues.apache.org/jira/browse/HIVE-1346 that this is a known issue when using the where clause, but I get in circumstances other than using a where clause. This query, to return counts of rows of a log file that are of a certain log record type value, also returns generic column names: select sum(case cast(raw.log_type_id as int) when 1 then 1 else 0 end) type_1, sum(case cast(raw.log_type_id as int) when 2 then 1 else 0 end) type_2, sum(case cast(raw.log_type_id as int) when 6 then 1 else 0 end) type_6, from raw_log raw Is this a variation on the same issue, or a new issue, or it is something I am doing wrong? --- Mark E. Sunderlin Solutions Architect |AOL Data Warehouse P: 703-256-6935 | C: 540-327-6222 AIM: MESunderlin 22000 AOL Way | Dulles, VA | 20166
Re: does hive support Sequence File format ?
On Thu, Feb 17, 2011 at 5:48 PM, Karthik wrote: > I have a requirement to support data from the SequenceFile "KEY" (not the > "VALUE") to be used by Hive table. How can I do this. From the code, it > looks like the "VALUE" part is available for Hive. Please help. > > Regards. > > > From: Mapred Learn > To: "user@hive.apache.org" > Cc: "user@hive.apache.org" > Sent: Thu, February 17, 2011 1:48:07 PM > Subject: Re: does hive support Sequence File format ? > > Thanks Ted ! > Just found it few minutes ago. > On Feb 17, 2011, at 1:46 PM, Ted Yu wrote: > > Look under > http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL#Create_Table > > On Thu, Feb 17, 2011 at 12:00 PM, Mapred Learn > wrote: >> >> Hi, >> I was wondering if hive supports Sequence File format. If yes, could me >> point me to some documentation about how to use Seq files in hive. >> >> Thanks, >> -JJ > This has come up two or three times on the ML. It can be done with InputFormats. Edward
Re: does hive support Sequence File format ?
I have a requirement to support data from the SequenceFile "KEY" (not the "VALUE") to be used by Hive table. How can I do this. From the code, it looks like the "VALUE" part is available for Hive. Please help. Regards. From: Mapred Learn To: "user@hive.apache.org" Cc: "user@hive.apache.org" Sent: Thu, February 17, 2011 1:48:07 PM Subject: Re: does hive support Sequence File format ? Thanks Ted ! Just found it few minutes ago. On Feb 17, 2011, at 1:46 PM, Ted Yu wrote: Look under http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL#Create_Table > > >On Thu, Feb 17, 2011 at 12:00 PM, Mapred Learn wrote: > >Hi, >>I was wondering if hive supports Sequence File format. If yes, could me point >>me >>to some documentation about how to use Seq files in hive. >> >>Thanks, >>-JJ >
Re: does hive support Sequence File format ?
Thanks Ted ! Just found it few minutes ago. On Feb 17, 2011, at 1:46 PM, Ted Yu wrote: > Look under http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL#Create_Table > > On Thu, Feb 17, 2011 at 12:00 PM, Mapred Learn wrote: > Hi, > I was wondering if hive supports Sequence File format. If yes, could me point > me to some documentation about how to use Seq files in hive. > > Thanks, > -JJ >
Re: does hive support Sequence File format ?
Look under http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL#Create_Table On Thu, Feb 17, 2011 at 12:00 PM, Mapred Learn wrote: > Hi, > I was wondering if hive supports Sequence File format. If yes, could me > point me to some documentation about how to use Seq files in hive. > > Thanks, > -JJ >
Hive Not Returning Column Names, even what not using 'When'??
When using either Hue or using Squirrel and jdbc, I get "_col0, _col1, _col2 ..." as my columns names when querying my hive tables. I see in https://issues.apache.org/jira/browse/HIVE-1346 that this is a known issue when using the where clause, but I get in circumstances other than using a where clause. This query, to return counts of rows of a log file that are of a certain log record type value, also returns generic column names: select sum(case cast(raw.log_type_id as int) when 1 then 1 else 0 end) type_1, sum(case cast(raw.log_type_id as int) when 2 then 1 else 0 end) type_2, sum(case cast(raw.log_type_id as int) when 6 then 1 else 0 end) type_6, from raw_log raw Is this a variation on the same issue, or a new issue, or it is something I am doing wrong? --- Mark E. Sunderlin Solutions Architect |AOL Data Warehouse P: 703-256-6935 | C: 540-327-6222 AIM: MESunderlin 22000 AOL Way | Dulles, VA | 20166
does hive support Sequence File format ?
Hi, I was wondering if hive supports Sequence File format. If yes, could me point me to some documentation about how to use Seq files in hive. Thanks, -JJ
Re: Importing a file wich includes delimiter like into HIVE
ah ... try : https://issues.apache.org/jira/browse/HIVE-136 it says that one can escape using: CREATE TABLE table1 (a STRING, b STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ESCAPED BY '\\'; Cheers, Ajo 2011/2/17 안의건 > Dear All > > Thank you for who gave me advices, but it seems that I didn't explained my > problem exactly. > I have asked a question regarding 'Imporing a file wich include delimiter > into HIVE'. > > In this case, I need to import a file wich includes " | " pipe without > terminating the importing process. > > As you all know, other language programs have escaping method like "/", " > ` ` ". > > What should I do for escaping when I import file inot HIVE? > > Regards, > Henny Ahn (ahneui...@gamil.com) > > > > > > > 2011/2/15 hadoop n00b > > Or try the ascii value like "*DELIMITED FIELDS TERMINATED BY '124'*" >> >> See if that helps. >> >> Cheers! >> >> On Mon, Feb 14, 2011 at 9:44 PM, Ajo Fod wrote: >> >>> use delimited by "|" ... are you using this syntax: >>> >>> Are you saying that the syntax here not work for you? >>> >>> http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL#Create_Table >>> >>> ... if you tried this ... ccould it be that the error may be caused by >>> something else. >>> >>> Cheers, >>> -Ajo >>> >>> >>> >>> On Mon, Feb 14, 2011 at 3:40 AM, 안의건 wrote: >>> Dear All I need your opinions about the problem I encountered during the data migration process. The file, which includes "|" pipe, is recognized as a Delimiter, and than an error occurs. What could I do if I need to import a file wich includes delimiter like into HIVE? Regards, Henny Ahn (ahneui...@gamil.com) >>> >>> >> >
Re: Importing a file wich includes delimiter like into HIVE
Dear All Thank you for who gave me advices, but it seems that I didn't explained my problem exactly. I have asked a question regarding 'Imporing a file wich include delimiter into HIVE'. In this case, I need to import a file wich includes " | " pipe without terminating the importing process. As you all know, other language programs have escaping method like "/", " ` ` ". What should I do for escaping when I import file inot HIVE? Regards, Henny Ahn (ahneui...@gamil.com) 2011/2/15 hadoop n00b > Or try the ascii value like "*DELIMITED FIELDS TERMINATED BY '124'*" > > See if that helps. > > Cheers! > > On Mon, Feb 14, 2011 at 9:44 PM, Ajo Fod wrote: > >> use delimited by "|" ... are you using this syntax: >> >> Are you saying that the syntax here not work for you? >> http://wiki.apache.org/hadoop/Hive/LanguageManual/DDL#Create_Table >> >> ... if you tried this ... ccould it be that the error may be caused by >> something else. >> >> Cheers, >> -Ajo >> >> >> >> On Mon, Feb 14, 2011 at 3:40 AM, 안의건 wrote: >> >>> Dear All >>> >>> >>> I need your opinions about the problem I encountered during the data >>> migration process. >>> >>> The file, which includes "|" pipe, is recognized as a Delimiter, and than >>> an error occurs. >>> >>> What could I do if I need to import a file wich includes delimiter like >>> into HIVE? >>> >>> >>> Regards, >>> Henny Ahn (ahneui...@gamil.com) >>> >> >> >