Re: PARTITION error because different columns size
Actually, there is a legacy feature in Hive that would do exactly what you need : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select#LanguageManualSelect-REGEXColumnSpecification Another way would simply to use dynamic partitioning : INSERT INTO TABLE employee_orc PARTITION *(country, office)* select * from employee where country='USA' and office='HQ-TX'; The only downside of this method (that I know of), is that Hive will put a lock on every partitions, even if only one is inserted. I also know a third solution, but I can't talk about it yet. On Tue, Dec 13, 2016 at 3:27 PM, Joaquin Alzola <joaquin.alz...@lebara.com> wrote: > Hi Suresh > > > > I choose the * and not the specific fields because I have 520 columns. > > The data that I tested was only a testing ground. > > > > I suppose then that I need to select the 520 fileds. L > > > > > > > > *From:* Suresh Kumar Sethuramaswamy [mailto:rock...@gmail.com] > *Sent:* 13 December 2016 14:19 > *To:* user@hive.apache.org > *Subject:* Re: PARTITION error because different columns size > > > > Hi Joaquin > > > > In hive , when u run 'select * from employee' it is going to return > the partitioned columns also at the end, whereas you don't want that to be > inserted into ur ORC table , so ur insert query should look like > > > > INSERT INTO TABLE employee_orc PARTITION (country='USA', > office='HQ-TX') select eid,salary from employee where country='USA' and > office='HQ-TX'; > > > > > > Remember partition in hive is a physical folder name > > > > Regards > > Suresh > > > > > > > > On Tue, Dec 13, 2016 at 6:37 AM Joaquin Alzola <joaquin.alz...@lebara.com> > wrote: > > > > > > > > > > > > > > > > > Hi List > > > > > > > > I change Spark to 2.0.2 and Hive 2.0.1. > > > > I have the bellow tables but the INSERT INTO TABLE employee_orc PARTITION > (country='USA', office='HQ-TX') select * from employee where country='USA' > and office='HQ-TX'; > > > > Is giving me à Cannot insert into table `default`.`employee_orc` because > the number of columns are different: need 4 columns, but query has 6 > columns.; > > > > > > > > When doing select it is adding the Partition as columns …. > > > > > > > > CREATE TABLE IF NOT EXISTS employee ( eid int, name String, > > > > salary String, destination String) > > > > COMMENT 'Employee details' > > > > PARTITIONED BY(country string, office string) > > > > ROW FORMAT DELIMITED > > > > FIELDS TERMINATED BY '\t' > > > > LINES TERMINATED BY '\n' > > > > STORED AS TEXTFILE; > > > > > > > > CREATE TABLE IF NOT EXISTS employee_orc ( eid int, name String, > > > > salary String, destination String) > > > > COMMENT 'Employee details' > > > > PARTITIONED BY(country string, office string) > > > > STORED AS ORC tblproperties ("orc.compress"="ZLIB"); > > > > > > > > 0: jdbc:hive2://localhost:1> LOAD DATA LOCAL INPATH > '/mnt/sample.txt.gz' INTO TABLE employee PARTITION (country='USA', > office='HQ-TX'); > > > > +-+--+ > > > > | Result | > > > > +-+--+ > > > > +-+--+ > > > > No rows selected (0.685 seconds) > > > > 0: jdbc:hive2://localhost:1> select * from employee; > > > > +---+--+-++- > -+-+--+ > > > > | eid | name | salary |destination | country | office > | > > > > +---+--+-++- > -+-+--+ > > > > | 1201 | Gopal| 45000 | Technical manager | USA | HQ-TX > | > > > > | 1202 | Manisha | 45000 | Proof reader | USA | HQ-TX > | > > > > | 1203 | Masthanvali | 4 | Technical writer | USA | HQ-TX > | > > > > | 1204 | Kiran| 4 | Hr Admin | USA | HQ-TX > | > > > > | 1205 | Kranthi | 3 | Op Admin | USA | HQ-TX > | > > > > +---+--+-++- > -+-+--+ > > > > 5 rows selected (0.358 seconds) > > > > 0: jdbc:hive2://localhost:1> INSERT INTO TABLE employee_orc PARTITION > (country='USA', office='HQ-TX') select * from employee where country='USA' > and office='HQ-TX'; > > > > Error: or
RE: PARTITION error because different columns size
Hi Suresh I choose the * and not the specific fields because I have 520 columns. The data that I tested was only a testing ground. I suppose then that I need to select the 520 fileds. ☹ From: Suresh Kumar Sethuramaswamy [mailto:rock...@gmail.com] Sent: 13 December 2016 14:19 To: user@hive.apache.org Subject: Re: PARTITION error because different columns size Hi Joaquin In hive , when u run 'select * from employee' it is going to return the partitioned columns also at the end, whereas you don't want that to be inserted into ur ORC table , so ur insert query should look like INSERT INTO TABLE employee_orc PARTITION (country='USA', office='HQ-TX') select eid,salary from employee where country='USA' and office='HQ-TX'; Remember partition in hive is a physical folder name Regards Suresh On Tue, Dec 13, 2016 at 6:37 AM Joaquin Alzola <joaquin.alz...@lebara.com<mailto:joaquin.alz...@lebara.com>> wrote: Hi List I change Spark to 2.0.2 and Hive 2.0.1. I have the bellow tables but the INSERT INTO TABLE employee_orc PARTITION (country='USA', office='HQ-TX') select * from employee where country='USA' and office='HQ-TX'; Is giving me --> Cannot insert into table `default`.`employee_orc` because the number of columns are different: need 4 columns, but query has 6 columns.; When doing select it is adding the Partition as columns …. CREATE TABLE IF NOT EXISTS employee ( eid int, name String, salary String, destination String) COMMENT 'Employee details' PARTITIONED BY(country string, office string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE; CREATE TABLE IF NOT EXISTS employee_orc ( eid int, name String, salary String, destination String) COMMENT 'Employee details' PARTITIONED BY(country string, office string) STORED AS ORC tblproperties ("orc.compress"="ZLIB"); 0: jdbc:hive2://localhost:1> LOAD DATA LOCAL INPATH '/mnt/sample.txt.gz' INTO TABLE employee PARTITION (country='USA', office='HQ-TX'); +-+--+ | Result | +-+--+ +-+--+ No rows selected (0.685 seconds) 0: jdbc:hive2://localhost:1> select * from employee; +---+--+-++--+-+--+ | eid | name | salary |destination | country | office | +---+--+-++--+-+--+ | 1201 | Gopal| 45000 | Technical manager | USA | HQ-TX | | 1202 | Manisha | 45000 | Proof reader | USA | HQ-TX | | 1203 | Masthanvali | 4 | Technical writer | USA | HQ-TX | | 1204 | Kiran| 4 | Hr Admin | USA | HQ-TX | | 1205 | Kranthi | 3 | Op Admin | USA | HQ-TX | +---+--+-++--+-+--+ 5 rows selected (0.358 seconds) 0: jdbc:hive2://localhost:1> INSERT INTO TABLE employee_orc PARTITION (country='USA', office='HQ-TX') select * from employee where country='USA' and office='HQ-TX'; Error: org.apache.spark.sql.AnalysisException: Cannot insert into table `default`.`employee_orc` because the number of columns are different: need 4 columns, but query has 6 columns.; (state=,code=0) 0: jdbc:hive2://localhost:1> describe employee_orc; +--++--+--+ | col_name | data_type | comment | +--++--+--+ | eid | int| NULL | | name | string | NULL | | salary | string | NULL | | destination | string | NULL | | country | string | NULL | | office | string | NULL | | # Partition Information || | | # col_name | data_type | comment | | country | string | NULL | | office | string | NULL | +--++--+--+ 0: jdbc:hive2://localhost:1> describe employee; +--++--+--+ | col_name | data_type | comment | +--++--+--+ | eid | int| NULL | | name | string | NULL | | salary | string | NULL | | destination | string | NULL | | country | string | NULL | | office | string | NULL | | # Partition Information || | | # col_name | data_type | comment | | country | string | NULL | | office | string | NULL | +--+--
Re: PARTITION error because different columns size
Hi Joaquin, Suresh was faster than me ... Also, you should check this : https://cwiki.apache.org/confluence/display/Hive/Tutorial#Tutorial-Dynamic-PartitionInsert On Tue, Dec 13, 2016 at 3:19 PM, Suresh Kumar Sethuramaswamy < rock...@gmail.com> wrote: > Hi Joaquin > > In hive , when u run 'select * from employee' it is going to return > the partitioned columns also at the end, whereas you don't want that to be > inserted into ur ORC table , so ur insert query should look like > > INSERT INTO TABLE employee_orc PARTITION (country='USA', > office='HQ-TX') select eid,salary from employee where country='USA' and > office='HQ-TX'; > > > Remember partition in hive is a physical folder name > > Regards > Suresh > > > > On Tue, Dec 13, 2016 at 6:37 AM Joaquin Alzola> wrote: > >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> Hi List >> >> >> >> >> >> I change Spark to 2.0.2 and Hive 2.0.1. >> >> >> I have the bellow tables but the INSERT INTO TABLE employee_orc PARTITION >> (country='USA', office='HQ-TX') select * from employee where country='USA' >> and office='HQ-TX'; >> >> >> Is giving me à Cannot insert into table `default`.`employee_orc` because >> the number of columns are different: need 4 columns, but query has 6 >> columns.; >> >> >> >> >> >> When doing select it is adding the Partition as columns …. >> >> >> >> >> >> >> CREATE TABLE IF NOT EXISTS employee ( eid int, name String, >> >> >> salary String, destination String) >> >> >> COMMENT 'Employee details' >> >> >> PARTITIONED BY(country string, office string) >> >> >> ROW FORMAT DELIMITED >> >> >> FIELDS TERMINATED BY '\t' >> >> >> LINES TERMINATED BY '\n' >> >> >> STORED AS TEXTFILE; >> >> >> >> >> >> CREATE TABLE IF NOT EXISTS employee_orc ( eid int, name String, >> >> >> salary String, destination String) >> >> >> COMMENT 'Employee details' >> >> >> PARTITIONED BY(country string, office string) >> >> >> STORED AS ORC tblproperties ("orc.compress"="ZLIB"); >> >> >> >> >> >> 0: jdbc:hive2://localhost:1> LOAD DATA LOCAL INPATH >> '/mnt/sample.txt.gz' INTO TABLE employee PARTITION (country='USA', >> office='HQ-TX'); >> >> >> +-+--+ >> >> >> | Result | >> >> >> +-+--+ >> >> >> +-+--+ >> >> >> No rows selected (0.685 seconds) >> >> >> 0: jdbc:hive2://localhost:1> select * from employee; >> >> >> +---+--+-++- >> -+-+--+ >> >> >> | eid | name | salary |destination | country | >> office | >> >> >> +---+--+-++- >> -+-+--+ >> >> >> | 1201 | Gopal| 45000 | Technical manager | USA | >> HQ-TX | >> >> >> | 1202 | Manisha | 45000 | Proof reader | USA | >> HQ-TX | >> >> >> | 1203 | Masthanvali | 4 | Technical writer | USA | >> HQ-TX | >> >> >> | 1204 | Kiran| 4 | Hr Admin | USA | >> HQ-TX | >> >> >> | 1205 | Kranthi | 3 | Op Admin | USA | >> HQ-TX | >> >> >> +---+--+-++- >> -+-+--+ >> >> >> 5 rows selected (0.358 seconds) >> >> >> 0: jdbc:hive2://localhost:1> INSERT INTO TABLE employee_orc PARTITION >> (country='USA', office='HQ-TX') select * from employee where country='USA' >> and office='HQ-TX'; >> >> >> Error: org.apache.spark.sql.AnalysisException: Cannot insert into table >> `default`.`employee_orc` because the number of columns are different: need >> 4 columns, but query has 6 columns.; (state=,code=0) >> >> >> >> >> >> >> >> >> 0: jdbc:hive2://localhost:1> describe employee_orc; >> >> >> +--++--+--+ >> >> >> | col_name | data_type | comment | >> >> >> +--++--+--+ >> >> >> | eid | int| NULL | >> >> >> | name | string | NULL | >> >> >> | salary | string | NULL | >> >> >> | destination | string | NULL | >> >> >> | country | string | NULL | >> >> >> | office | string | NULL | >> >> >> | # Partition Information || | >> >> >> | # col_name | data_type | comment | >> >> >> | country | string | NULL | >> >> >> | office | string | NULL | >> >> >> +--++--+--+ >> >> >> >> >> >> 0: jdbc:hive2://localhost:1> describe employee; >> >> >> +--++--+--+ >> >> >> | col_name | data_type | comment | >> >> >> +--++--+--+ >> >> >> | eid | int| NULL | >> >> >> | name | string | NULL | >> >> >> | salary |
Re: PARTITION error because different columns size
Hi Joaquin In hive , when u run 'select * from employee' it is going to return the partitioned columns also at the end, whereas you don't want that to be inserted into ur ORC table , so ur insert query should look like INSERT INTO TABLE employee_orc PARTITION (country='USA', office='HQ-TX') select eid,salary from employee where country='USA' and office='HQ-TX'; Remember partition in hive is a physical folder name Regards Suresh On Tue, Dec 13, 2016 at 6:37 AM Joaquin Alzolawrote: > > > > > > > > > > > > > > > > > Hi List > > > > > > I change Spark to 2.0.2 and Hive 2.0.1. > > > I have the bellow tables but the INSERT INTO TABLE employee_orc PARTITION > (country='USA', office='HQ-TX') select * from employee where country='USA' > and office='HQ-TX'; > > > Is giving me à Cannot insert into table `default`.`employee_orc` because > the number of columns are different: need 4 columns, but query has 6 > columns.; > > > > > > When doing select it is adding the Partition as columns …. > > > > > > > CREATE TABLE IF NOT EXISTS employee ( eid int, name String, > > > salary String, destination String) > > > COMMENT 'Employee details' > > > PARTITIONED BY(country string, office string) > > > ROW FORMAT DELIMITED > > > FIELDS TERMINATED BY '\t' > > > LINES TERMINATED BY '\n' > > > STORED AS TEXTFILE; > > > > > > CREATE TABLE IF NOT EXISTS employee_orc ( eid int, name String, > > > salary String, destination String) > > > COMMENT 'Employee details' > > > PARTITIONED BY(country string, office string) > > > STORED AS ORC tblproperties ("orc.compress"="ZLIB"); > > > > > > 0: jdbc:hive2://localhost:1> LOAD DATA LOCAL INPATH > '/mnt/sample.txt.gz' INTO TABLE employee PARTITION (country='USA', > office='HQ-TX'); > > > +-+--+ > > > | Result | > > > +-+--+ > > > +-+--+ > > > No rows selected (0.685 seconds) > > > 0: jdbc:hive2://localhost:1> select * from employee; > > > > +---+--+-++--+-+--+ > > > | eid | name | salary |destination | country | office > | > > > > +---+--+-++--+-+--+ > > > | 1201 | Gopal| 45000 | Technical manager | USA | HQ-TX > | > > > | 1202 | Manisha | 45000 | Proof reader | USA | HQ-TX > | > > > | 1203 | Masthanvali | 4 | Technical writer | USA | HQ-TX > | > > > | 1204 | Kiran| 4 | Hr Admin | USA | HQ-TX > | > > > | 1205 | Kranthi | 3 | Op Admin | USA | HQ-TX > | > > > > +---+--+-++--+-+--+ > > > 5 rows selected (0.358 seconds) > > > 0: jdbc:hive2://localhost:1> INSERT INTO TABLE employee_orc PARTITION > (country='USA', office='HQ-TX') select * from employee where country='USA' > and office='HQ-TX'; > > > Error: org.apache.spark.sql.AnalysisException: Cannot insert into table > `default`.`employee_orc` because the number of columns are different: need > 4 columns, but query has 6 columns.; (state=,code=0) > > > > > > > > > 0: jdbc:hive2://localhost:1> describe employee_orc; > > > +--++--+--+ > > > | col_name | data_type | comment | > > > +--++--+--+ > > > | eid | int| NULL | > > > | name | string | NULL | > > > | salary | string | NULL | > > > | destination | string | NULL | > > > | country | string | NULL | > > > | office | string | NULL | > > > | # Partition Information || | > > > | # col_name | data_type | comment | > > > | country | string | NULL | > > > | office | string | NULL | > > > +--++--+--+ > > > > > > 0: jdbc:hive2://localhost:1> describe employee; > > > +--++--+--+ > > > | col_name | data_type | comment | > > > +--++--+--+ > > > | eid | int| NULL | > > > | name | string | NULL | > > > | salary | string | NULL | > > > | destination | string | NULL | > > > | country | string | NULL | > > > | office | string | NULL | > > > | # Partition Information || | > > > | # col_name | data_type | comment | > > > | country | string | NULL | > > > | office | string | NULL | > > > +--++--+--+ > > > 10 rows selected (0.045 seconds) > >
PARTITION error because different columns size
Hi List I change Spark to 2.0.2 and Hive 2.0.1. I have the bellow tables but the INSERT INTO TABLE employee_orc PARTITION (country='USA', office='HQ-TX') select * from employee where country='USA' and office='HQ-TX'; Is giving me --> Cannot insert into table `default`.`employee_orc` because the number of columns are different: need 4 columns, but query has 6 columns.; When doing select it is adding the Partition as columns CREATE TABLE IF NOT EXISTS employee ( eid int, name String, salary String, destination String) COMMENT 'Employee details' PARTITIONED BY(country string, office string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE; CREATE TABLE IF NOT EXISTS employee_orc ( eid int, name String, salary String, destination String) COMMENT 'Employee details' PARTITIONED BY(country string, office string) STORED AS ORC tblproperties ("orc.compress"="ZLIB"); 0: jdbc:hive2://localhost:1> LOAD DATA LOCAL INPATH '/mnt/sample.txt.gz' INTO TABLE employee PARTITION (country='USA', office='HQ-TX'); +-+--+ | Result | +-+--+ +-+--+ No rows selected (0.685 seconds) 0: jdbc:hive2://localhost:1> select * from employee; +---+--+-++--+-+--+ | eid | name | salary |destination | country | office | +---+--+-++--+-+--+ | 1201 | Gopal| 45000 | Technical manager | USA | HQ-TX | | 1202 | Manisha | 45000 | Proof reader | USA | HQ-TX | | 1203 | Masthanvali | 4 | Technical writer | USA | HQ-TX | | 1204 | Kiran| 4 | Hr Admin | USA | HQ-TX | | 1205 | Kranthi | 3 | Op Admin | USA | HQ-TX | +---+--+-++--+-+--+ 5 rows selected (0.358 seconds) 0: jdbc:hive2://localhost:1> INSERT INTO TABLE employee_orc PARTITION (country='USA', office='HQ-TX') select * from employee where country='USA' and office='HQ-TX'; Error: org.apache.spark.sql.AnalysisException: Cannot insert into table `default`.`employee_orc` because the number of columns are different: need 4 columns, but query has 6 columns.; (state=,code=0) 0: jdbc:hive2://localhost:1> describe employee_orc; +--++--+--+ | col_name | data_type | comment | +--++--+--+ | eid | int| NULL | | name | string | NULL | | salary | string | NULL | | destination | string | NULL | | country | string | NULL | | office | string | NULL | | # Partition Information || | | # col_name | data_type | comment | | country | string | NULL | | office | string | NULL | +--++--+--+ 0: jdbc:hive2://localhost:1> describe employee; +--++--+--+ | col_name | data_type | comment | +--++--+--+ | eid | int| NULL | | name | string | NULL | | salary | string | NULL | | destination | string | NULL | | country | string | NULL | | office | string | NULL | | # Partition Information || | | # col_name | data_type | comment | | country | string | NULL | | office | string | NULL | +--++--+--+ 10 rows selected (0.045 seconds) This email is confidential and may be subject to privilege. If you are not the intended recipient, please do not copy or disclose its content but contact the sender immediately upon receipt.