I guess one alternative is to import it AS IS (the same column type) to a staging table and then do insert/select into the target table from the staging table.
import/export is for coping data from say prod to dev like to like. the problem is that it does two things. it exports both data and metadata. see below hduser@rhes564:: :/home/hduser/dba/bin> hdfs dfs -ls hdfs://rhes564:9000/export 16/05/30 13:07:02 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable Found 2 items -rwxr-xr-x 2 hduser supergroup 1588 2016-05-25 16:46 hdfs://rhes564:9000/export/ *_metadata*drwxr-xr-x - hduser supergroup 0 2016-05-25 16:46 hdfs://rhes564:9000/export/data and uses the metadata file to create the target table which somehow does not work in this case! HTH Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com On 30 May 2016 at 13:00, Devender Yadav <devender.ya...@impetus.co.in> wrote: > Hi Mich, > > > you did not get my question I guess . > > > I am able to use import export. > > > I am exporting data from dev101 (col1 int, col2 char(10)) and importing > in dev102 (col1 int, col2 string) > > > > I am getting issue : > > > >FAILED: SemanticException [Error 10120]: The existing table is not > compatible with the import spec. Column Schema does not match > > > > Is it possible to import char(10) field in string or char(20) ? > > > > Because I tried and got above mentioned exception. > > > > Regards, > Devender > ------------------------------ > *From:* Mich Talebzadeh <mich.talebza...@gmail.com> > *Sent:* Monday, May 30, 2016 5:19 PM > *To:* user > *Subject:* Re: Does hive need exact schema in Hive Export/Import? > > it is pretty straight forward > > !hdfs dfs -rm -r hdfs://rhes564:9000/export; > EXPORT TABLE oraclehadoop.sales_staging to "hdfs://rhes564:9000/export"; > -- > DROP TABLE IF EXISTS test.sales_staging; > IMPORT TABLE test.sales_staging FROM "hdfs://rhes564:9000/export"; > select count(1) from test.sales_staging; > exit; > > > Dr Mich Talebzadeh > > > > LinkedIn * > https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* > > > > http://talebzadehmich.wordpress.com > > > > On 30 May 2016 at 12:38, Devender Yadav <devender.ya...@impetus.co.in> > wrote: > >> Hi All, >> >> >> I am using HDP 2.3 >> >> - Hadoop version - 2.7.1 >> >> - Hive version - 1.2.1 >> >> >> I created a table dev101 in hive using >> >> create table dev101 (col1 int, col2 char(10)); >> >> I inserted two records using >> >> insert into dev101 values (1, 'value1'); >> insert into dev101 values (2, 'value2'); >> >> I exported data to HDFS using >> >> export table dev101 to '/tmp/dev101'; >> >> >> Then, I created a new table dev102 using >> >> create table dev102 (col1 int, col2 String); >> >> >> I imported data from `/tmp/dev10` in `dev102` using >> >> import table dev102 from '/tmp/dev101'; >> >> I got error: >> >> >FAILED: SemanticException [Error 10120]: The existing table is not >> compatible with the import spec. Column Schema does not match >> >> >> Then I created another table `dev103` using >> >> create table dev103 (col1 int, col2 char(50)); >> >> Again imported: >> >> import table dev103 from '/tmp/dev101'; >> >> Same error: >> >> >FAILED: SemanticException [Error 10120]: The existing table is not >> compatible with the import spec. Column Schema does not match >> >> Finally, I create table with **exactly same schema** >> >> create table dev104 (col1 int, col2 char(10)); >> >> And imported >> >> import table dev104 from '/tmp/dev101'; >> >> Imported Successfully. >> >> Does hive need exact schema in Hive Export/Import? ​ >> >> >> >> Regards, >> Devender​ >> >> ------------------------------ >> >> >> >> >> >> >> NOTE: This message may contain information that is confidential, >> proprietary, privileged or otherwise protected by law. The message is >> intended solely for the named addressee. If received in error, please >> destroy and notify the sender. Any use of this email is prohibited when >> received in error. Impetus does not represent, warrant and/or guarantee, >> that the integrity of this communication has been maintained nor that the >> communication is free of errors, virus, interception or interference. >> > > > ------------------------------ > > > > > > > NOTE: This message may contain information that is confidential, > proprietary, privileged or otherwise protected by law. The message is > intended solely for the named addressee. If received in error, please > destroy and notify the sender. Any use of this email is prohibited when > received in error. Impetus does not represent, warrant and/or guarantee, > that the integrity of this communication has been maintained nor that the > communication is free of errors, virus, interception or interference. >
