sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Suryani Simon Turtan (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (SQOOP-3254) sqoop export Hive ORC to Oracle CLOB columns are somewhat case-sensitive
Date Thu, 16 Nov 2017 03:59:00 GMT

     [ https://issues.apache.org/jira/browse/SQOOP-3254?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Suryani Simon Turtan updated SQOOP-3254:
----------------------------------------
    Description: 
I am trying to export a String column from Hive ORC table into Oracle CLOB column using HCatalog
in Sqoop 1.4.6 (both HDP 2.4 and HDP 2.6).
- HDP 2.4: Sqoop 1.4.6.2.4.3.0-227
- HDP 2.6: Sqoop 1.4.6.2.6.3.0-235

Table DDL in Oracle:
CREATE TABLE ORACLE_CLOB_TABLE (ID NVARCHAR2(10), CLOBCOLUMN CLOB);

Table DDL in Hive:
CREATE TABLE default.hive_clob_table (id string, clobcolumn string) ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';

insert into default.hive_clob_table (id, clobcolumn) values ("A0001", "This is a very large
string text"); 
insert into default.hive_clob_table (id, clobcolumn) values ("B0002", "This is the second
very large string text");

Sqoop Export Command:

*CASE 1 -- Use Lower Case on Table name and Column list*
- The command is not working on HDP 2.6 at all due to Oracle tables being defined as CAPITALIZED
table and column names.
- The command is working fine on HDP 2.4, with CLOB column exported accordingly

 sqoop \
        export \
        -Dmapreduce.job.queuename=queue \
        --connect 'jdbc:oracle:thin:@//host:port/database_name'\
        --username 'user_name' \
        --password 'user_password' \
        --verbose \
        --table 'oracle_clob_table' \
        --columns 'id, clobcolumn' \
        --hcatalog-table 'hive_clob_table' \
        --hcatalog-database 'default' \
	--map-column-java clobcolumn=String 

Output in Oracle:
| A0001 | This is a very large string text |
| B0002 | This is the second very large string text |


*CASE 2 -- Use Upper Case on Table name and Column list*
- The command is working fine on both HDP 2.4 and HDP 2.6. However, CLOB column exported as
NULL.

 sqoop \
        export \
        -Dmapreduce.job.queuename=queue \
        --connect 'jdbc:oracle:thin:@//host:port/database_name'\
        --username 'user_name' \
        --password 'user_password' \
        --verbose \
        --table 'ORACLE_CLOB_TABLE' \
        --columns 'ID, CLOBCOLUMN' \
        --hcatalog-table 'hive_clob_table' \
        --hcatalog-database 'default' \
	--map-column-java CLOBCOLUMN=String 

Output in Oracle:
| A0001 | NULL |
| B0002 | NULL |

I am curious why would the CASE of the column used in sqoop command would affect the CLOB
export ability? Thank you.

  was:
I am trying to export a String column from Hive ORC table into Oracle CLOB column using HCatalog
in Sqoop 1.4.6 (both HDP 2.4 and HDP 2.6).
- HDP 2.4: Sqoop 1.4.6.2.4.3.0-227
- HDP 2.6: Sqoop 1.4.6.2.6.3.0-235

Table DDL in Oracle:
CREATE TABLE ORACLE_CLOB_TABLE (ID VARCHAR(10), CLOBCOLUMN CLOB);

Table DDL in Hive:
CREATE TABLE default.hive_clob_table (id string, clobcolumn string) ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';

insert into default.hive_clob_table (id, clobcolumn) values ("A0001", "This is a very large
string text"); 
insert into default.hive_clob_table (id, clobcolumn) values ("B0002", "This is the second
very large string text");

Sqoop Export Command:

*CASE 1 -- Use Lower Case on Table name and Column list*
- The command is not working on HDP 2.6 at all due to Oracle tables being defined as CAPITALIZED
table and column names.
- The command is working fine on HDP 2.4, with CLOB column exported accordingly

 sqoop \
        export \
        -Dmapreduce.job.queuename=queue \
        --connect 'jdbc:oracle:thin:@//host:port/database_name'\
        --username 'user_name' \
        --password 'user_password' \
        --verbose \
        --table 'oracle_clob_table' \
        --columns 'id, clobcolumn' \
        --hcatalog-table 'hive_clob_table' \
        --hcatalog-database 'default' \
	--map-column-java clobcolumn=String 

Output in Oracle:
| A0001 | This is a very large string text |
| B0002 | This is the second very large string text |


*CASE 2 -- Use Upper Case on Table name and Column list*
- The command is working fine on both HDP 2.4 and HDP 2.6. However, CLOB column exported as
NULL.

 sqoop \
        export \
        -Dmapreduce.job.queuename=queue \
        --connect 'jdbc:oracle:thin:@//host:port/database_name'\
        --username 'user_name' \
        --password 'user_password' \
        --verbose \
        --table 'ORACLE_CLOB_TABLE' \
        --columns 'ID, CLOBCOLUMN' \
        --hcatalog-table 'hive_clob_table' \
        --hcatalog-database 'default' \
	--map-column-java CLOBCOLUMN=String 

Output in Oracle:
| A0001 | NULL |
| B0002 | NULL |

I am curious why would the CASE of the column used in sqoop command would affect the CLOB
export ability? Thank you.


> sqoop export Hive ORC to Oracle CLOB columns are somewhat case-sensitive
> ------------------------------------------------------------------------
>
>                 Key: SQOOP-3254
>                 URL: https://issues.apache.org/jira/browse/SQOOP-3254
>             Project: Sqoop
>          Issue Type: Bug
>    Affects Versions: 1.4.6
>         Environment: HDP 2.4 and HDP 2.6
>            Reporter: Suryani Simon Turtan
>            Priority: Critical
>              Labels: beginner, newbie, usability
>
> I am trying to export a String column from Hive ORC table into Oracle CLOB column using
HCatalog in Sqoop 1.4.6 (both HDP 2.4 and HDP 2.6).
> - HDP 2.4: Sqoop 1.4.6.2.4.3.0-227
> - HDP 2.6: Sqoop 1.4.6.2.6.3.0-235
> Table DDL in Oracle:
> CREATE TABLE ORACLE_CLOB_TABLE (ID NVARCHAR2(10), CLOBCOLUMN CLOB);
> Table DDL in Hive:
> CREATE TABLE default.hive_clob_table (id string, clobcolumn string) ROW FORMAT SERDE
>   'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
> STORED AS INPUTFORMAT
>   'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
> OUTPUTFORMAT
>   'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';
> insert into default.hive_clob_table (id, clobcolumn) values ("A0001", "This is a very
large string text"); 
> insert into default.hive_clob_table (id, clobcolumn) values ("B0002", "This is the second
very large string text");
> Sqoop Export Command:
> *CASE 1 -- Use Lower Case on Table name and Column list*
> - The command is not working on HDP 2.6 at all due to Oracle tables being defined as
CAPITALIZED table and column names.
> - The command is working fine on HDP 2.4, with CLOB column exported accordingly
>  sqoop \
>         export \
>         -Dmapreduce.job.queuename=queue \
>         --connect 'jdbc:oracle:thin:@//host:port/database_name'\
>         --username 'user_name' \
>         --password 'user_password' \
>         --verbose \
>         --table 'oracle_clob_table' \
>         --columns 'id, clobcolumn' \
>         --hcatalog-table 'hive_clob_table' \
>         --hcatalog-database 'default' \
> 	--map-column-java clobcolumn=String 
> Output in Oracle:
> | A0001 | This is a very large string text |
> | B0002 | This is the second very large string text |
> *CASE 2 -- Use Upper Case on Table name and Column list*
> - The command is working fine on both HDP 2.4 and HDP 2.6. However, CLOB column exported
as NULL.
>  sqoop \
>         export \
>         -Dmapreduce.job.queuename=queue \
>         --connect 'jdbc:oracle:thin:@//host:port/database_name'\
>         --username 'user_name' \
>         --password 'user_password' \
>         --verbose \
>         --table 'ORACLE_CLOB_TABLE' \
>         --columns 'ID, CLOBCOLUMN' \
>         --hcatalog-table 'hive_clob_table' \
>         --hcatalog-database 'default' \
> 	--map-column-java CLOBCOLUMN=String 
> Output in Oracle:
> | A0001 | NULL |
> | B0002 | NULL |
> I am curious why would the CASE of the column used in sqoop command would affect the
CLOB export ability? Thank you.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message