Re: Hive Query - Issue
Hello, I think you are working with dynamic partition. Then you do not need to mention it's value.you only need to put partition like this::: try this:: insert overwrite table table_baseline partition (sourcedate) select * from (select * from table_a where sourcedate='tablea_2013_08' union all select * from table_b where sourcedate='tableb_2013_08') final If your problem will not resolve from this then you need to count the no.of columns in table i think problem is there then correct it if you not and try once again.I also found the same error in past.. On Mon, Sep 2, 2013 at 5:02 PM, Manickam P wrote: > Hello Experts, > > when i try to execute the below query i'm getting error. Please help me to > correct this. > > insert overwrite table table_baseline partition > (sourcedate='base_2013_08') select * from (select * from table_a where > sourcedate='tablea_2013_08' union all select * from table_b where > sourcedate='tableb_2013_08') final > > My intention here is i want to populate the table_baseline by using the > all records from table_a and table_b with partition. I am getting the below > error. > > *Error in semantic analysis: Line 1:23 Cannot insert into target table > because column number/types are different ''BASE_2013_08'': Table > insclause-0 has 62 columns, but query has 63 columns.* > > I verified the column count and types everything is same but here it says > some difference. The same query works fine without having any partitions in > all the three tables but getting error while executing with partitions. > > > please help. > > > > Thanks > Manickam P > -- Regards *Manish Dunani* *Contact No* : +91 9408329137 *skype id* : manish.dunani* *
Re: Hive - external (dynamically) partitioned table
, type:string,
> comment:null), FieldSchema(name:dep_date, type:string, comment:null),
> FieldSchema(name:crr_code, type:string, comment:null),
> FieldSchema(name:flight_no, type:string, comment:null),
> FieldSchema(name:orgn, type:string, comment:null), FieldSchema(name:dstn,
> type:string, comment:null), FieldSchema(name:physical_cap, type:int,
> comment:null), FieldSchema(name:adjusted_cap, type:int, comment:null),
> FieldSchema(name:closed_cap, type:int, comment:null),
> FieldSchema(name:leg_id, type:int, comment:null), FieldSchema(name:month,
> type:int, comment:null), FieldSchema(name:comp_code, type:string,
> comment:null)], location:hdfs://
> 172.25.37.21:54300/pros/olap2/dataimports/nas_comps,
> inputFormat:org.apache.hadoop.mapred.TextInputFormat,
> outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat,
> compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null,
> serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
> parameters:{serialization.format=1}), bucketCols:[], sortCols:[],
> parameters:{}), partitionKeys:[FieldSchema(name:leg_id, type:int,
> comment:null), FieldSchema(name:month, type:int, comment:null),
> FieldSchema(name:comp_code, type:string, comment:null)],
> parameters:{EXTERNAL=TRUE, transient_lastDdlTime=1374849456},
> viewOriginalText:null, viewExpandedText:null, tableType:EXTERNAL_TABLE)***
> *
>
> *But I'm not sure if the partitions are created because :*
>
> hive> show partitions nas_comps;
>
> OK
>
> Time taken: 0.599 seconds
>
>
>
>
>
> select count(1) from nas_comps;
>
> returns 0 records
>
> How do I create an external Hive table with dynamic partitions?****
>
>
>
>
>
>
>
>
>
> Regards,
>
> Omkar Joshi
>
>
>
> ** **
> --
>
> The contents of this e-mail and any attachment(s) may contain confidential
> or privileged information for the intended recipient(s). Unintended
> recipients are prohibited from taking action on the basis of information in
> this e-mail and using or disseminating the information, and must notify the
> sender and delete it from their system. L&T Infotech will not accept
> responsibility or liability for the accuracy or completeness of, or the
> presence of any virus or disabling code in this e-mail"
>
>
>
>
>
> ** **
>
> --
> Dean Wampler, Ph.D.
> @deanwampler
> http://polyglotprogramming.com
>
--
MANISH DUNANI
-THANX
+91 9426881954,+91 8460656443
manishd...@gmail.com
Re: Need help in joining 2 tables
Hi Rama, Have a look !! Both sql and hiveql looks similar.. try this one.. *How to create view??* * * *Ans:* * * create view v1 as select m.Emp_id, m.Name, m.Dob, a.address1, a.address2, a.address3, a.address4, a.city, a.state, a.zip, a.country from EMP_MASTER m join EMP_ADDRESS a on (m.Emp_id=a.Emp_id) where m.row_create_date=(select MAX(row_create_date) from EMP_MASTER where Emp_id = m.Emp_id) and a.row_create_date = (select MAX(row_create_date) from EMP_ADDRESS where Emp_id = a.Emp_id) On Fri, Jul 26, 2013 at 1:47 PM, Ramasubramanian Narayanan < ramasubramanian.naraya...@gmail.com> wrote: > Hi, > > Need to create a view in HIVE as per the below query (written in Oracle). > Need help to provide the equivalent query in HIVE to create view. > > > *EMP_ADDRESS* > > Emp_Id > Address1 > Address2 > Address3 > Address4 > City > State > Pin > Country > Alternate_Address1 > Alternate_Address2 > Alternate_Address3 > Alternate_Address4 > Alternate_City > Alternate_State > Alternate_Pin > Alternate_Country > row_create_date > > > *EMP_MASTER* > > Emp_Id > Name > DOB > Department > Sex > FathersName > row_create_date > > > *View Query* > > select > MAST.Emp_Id, > MAST.Name, > MAST.DOB, > ADDR.Address1, > ADDR.Address2, > ADDR.Address3, > ADDR.Address4, > ADDR.City, > ADDR.State, > ADDR.Pin, > ADDR.Country > from EMP_MASTER MAST, EMP_ADDRESS ADDR > where > MAST.row_create_date = (select max(row_create_date) from EMP_MASTER where > Emp_Id = MAST.Emp_Id) > and ADDR.row_create_date = (select max(row_create_date) from EMP_ADDRESS > where Emp_Id = ADDR.Emp_Id) > > > regards, > Rams > -- MANISH DUNANI -THANX +91 9426881954,+91 8460656443 manishd...@gmail.com
