[
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 |
*Additional Test Done*
I did an additional test by Re-creating Oracle table with small case column
names:
Table DDL in Oracle:
CREATE TABLE oracle_clob_table ("id" NVARCHAR2(10), "clobcolumn" CLOB);
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 |
I am curious why would the CASE of the column used in sqoop export 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 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.
> 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 |
> *Additional Test Done*
> I did an additional test by Re-creating Oracle table with small case column
> names:
> Table DDL in Oracle:
> CREATE TABLE oracle_clob_table ("id" NVARCHAR2(10), "clobcolumn" CLOB);
> 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 |
> I am curious why would the CASE of the column used in sqoop export command
> would affect the CLOB export ability? Thank you.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
