You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Devender Yadav <de...@impetus.co.in> on 2016/05/30 11:38:03 UTC
Does hive need exact schema in Hive Export/Import?
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.
Re: Does hive need exact schema in Hive Export/Import?
Posted by Mich Talebzadeh <mi...@gmail.com>.
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 <de...@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 <mi...@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 <de...@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.
>
Re: Does hive need exact schema in Hive Export/Import?
Posted by Devender Yadav <de...@impetus.co.in>.
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 <mi...@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
http://talebzadehmich.wordpress.com<http://talebzadehmich.wordpress.com/>
On 30 May 2016 at 12:38, Devender Yadav <de...@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.
Re: Does hive need exact schema in Hive Export/Import?
Posted by Mich Talebzadeh <mi...@gmail.com>.
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 <de...@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.
>
RE: Does hive need exact schema in Hive Export/Import?
Posted by "Markovitz, Dudu" <dm...@paypal.com>.
Hi
1)
I was able to do the import by doing the following manipulation:
· Export table dev101
· Create an empty table dev102
· Export table dev102
· replace the _metadata file of dev101 with the _metadata file of dev102
· import table dev101 to table dev102
2)
Another option is not to create dev102 in advance but let the import from dev101 to create it.
After the import you can alter the table, e.g.:
Alter table dev102 change column col2 col2 varchar(10);
Dudu
From: Devender Yadav [mailto:devender.yadav@impetus.co.in]
Sent: Monday, May 30, 2016 2:38 PM
To: user@hive.apache.org
Subject: Does hive need exact schema in Hive Export/Import?
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.