[
https://issues.apache.org/jira/browse/SQOOP-3146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sandish Kumar HN reassigned SQOOP-3146:
---------------------------------------
Assignee: Sandish Kumar HN
> Sqoop (import + --as-parquetfile) with Oracle (CLOB vs. BLOB) is inconsistent
> -----------------------------------------------------------------------------
>
> Key: SQOOP-3146
> URL: https://issues.apache.org/jira/browse/SQOOP-3146
> Project: Sqoop
> Issue Type: Bug
> Reporter: Anna Szonyi
> Assignee: Sandish Kumar HN
>
> ########################################################################
> # Owner: Sqoopinators
> # Component: Sqoop1
> # Purpose: Escalation Test Case
> # SFDC Case ID:127558
> # SFDC EscalationID: CDH-50699
> # File: SupportTest_Case_127558_JIRA_CDH-50699.txt
> #
> # Description
> # 1. Sqoop import + —as-parquetfile + CLOB Data Types (Gives Error)
> # 2. Sqoop import + —as-parquetfile + BLOB Data Types (Works Good)
> ########################################################################
> ######################################################
> USE CASE [1] . Sqoop import + —as-parquetfile + CLOB Data Types (Gives Error)
> ######################################################
> #######################
> # STEP 01 - CREATE DATA
> #######################
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
> "create table t1_clob (c1 int,c2 clob)"
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
> "insert into t1_clob values(1,'qwqewewqrerew121212121212’)”
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
> "select * from t1_clob"
> #########
> OUTPUT
> #########
> -----------------------------------------------
> | C1 | C2 |
> -----------------------------------------------
> | 1 | qwqewewqrerew121212121212 |
> -----------------------------------------------
> ########################################################
> STEP 02 - IMPORT AS PARQUET FILE (Without —map-column-java) [REPRODUCING
> THE ERROR]
> ########################################################
> sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD
> --as-parquetfile --table T1_CLOB --delete-target-dir --target-dir
> '/projects/t1_clob' -m 1
> OUTPUT
> ——————
> Please set $ACCUMULO_HOME to the root of your Accumulo installation.
> 17/02/21 10:07:08 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.8.3
> 17/02/21 10:07:08 WARN tool.BaseSqoopTool: Setting your password on the
> command-line is insecure. Consider using -P instead.
> 17/02/21 10:07:08 INFO oracle.OraOopManagerFactory: Data Connector for Oracle
> and Hadoop is disabled.
> 17/02/21 10:07:08 INFO manager.SqlManager: Using default fetchSize of 1000
> 17/02/21 10:07:08 INFO tool.CodeGenTool: Beginning code generation
> 17/02/21 10:07:08 INFO tool.CodeGenTool: Will generate java class as
> codegen_T1_CLOB
> 17/02/21 10:07:09 INFO manager.OracleManager: Time zone has been set to GMT
> 17/02/21 10:07:09 INFO manager.SqlManager: Executing SQL statement: SELECT
> t.* FROM "T1_CLOB" t WHERE 1=0
> 17/02/21 10:07:09 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is
> /opt/cloudera/parcels/CDH/lib/hadoop-mapreduce
> Note:
> /tmp/sqoop-root/compile/cbaf5013e6bc9dad7283090f9d761289/codegen_T1_CLOB.java
> uses or overrides a deprecated API.
> Note: Recompile with -Xlint:deprecation for details.
> 17/02/21 10:07:11 INFO orm.CompilationManager: Writing jar file:
> /tmp/sqoop-root/compile/cbaf5013e6bc9dad7283090f9d761289/codegen_T1_CLOB.jar
> 17/02/21 10:07:13 INFO tool.ImportTool: Destination directory
> /projects/t1_clob is not present, hence not deleting.
> 17/02/21 10:07:13 INFO manager.OracleManager: Time zone has been set to GMT
> 17/02/21 10:07:13 INFO manager.OracleManager: Time zone has been set to GMT
> 17/02/21 10:07:13 INFO mapreduce.ImportJobBase: Beginning import of T1_CLOB
> 17/02/21 10:07:13 INFO Configuration.deprecation: mapred.jar is deprecated.
> Instead, use mapreduce.job.jar
> 17/02/21 10:07:13 INFO manager.OracleManager: Time zone has been set to GMT
> 17/02/21 10:07:13 INFO manager.OracleManager: Time zone has been set to GMT
> 17/02/21 10:07:13 INFO manager.SqlManager: Executing SQL statement: SELECT
> t.* FROM "T1_CLOB" t WHERE 1=0
> 17/02/21 10:07:13 INFO manager.SqlManager: Executing SQL statement: SELECT
> t.* FROM "T1_CLOB" t WHERE 1=0
> 17/02/21 10:07:13 ERROR tool.ImportTool: Imported Failed: Cannot convert SQL
> type 2005
> #####################################################################
> STEP 02.1 - IMPORT AS PARQUET FILE + —map-column-java (For CLOB data type)
> #####################################################################
> sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD
> --as-parquet file --table T1_CLOB --map-column-java C2=String
> --delete-target-dir --target-dir '/projects/t1_clob' -m 1
> OUTPUT
> ———————
> 17/02/21 10:09:50 INFO tool.ImportTool: Destination directory
> /projects/t1_clob is not present, hence not deleting.
> 17/02/21 10:09:50 INFO mapreduce.ImportJobBase: Beginning import of T1_CLOB
> 17/02/21 10:10:09 INFO mapreduce.Job: map 0% reduce 0%
> 17/02/21 10:10:34 INFO mapreduce.Job: map 100% reduce 0%
> 17/02/21 10:10:35 INFO mapreduce.Job: Job job_1487339765830_0009 completed
> successfully
> 17/02/21 10:10:35 INFO mapreduce.ImportJobBase: Transferred 1.7402 KB in
> 43.4127 seconds (41.0479 bytes/sec)
> 17/02/21 10:10:35 INFO mapreduce.ImportJobBase: Retrieved 1 records.
> [root@host-10-17-81-247 ~]# hadoop fs -ls /projects/t1_clob
> Found 3 items
> drwxr-xr-x - root supergroup 0 2017-02-21 10:09
> /projects/t1_clob/.metadata
> drwxr-xr-x - root supergroup 0 2017-02-21 10:10
> /projects/t1_clob/.signals
> -rw-r--r-- 3 root supergroup 698 2017-02-21 10:10
> /projects/t1_clob/20aeeff1-68e1-4c9c-9a74-51a5f2ccb369.parquet
> ###################################################################
> USE CASE [2] . Sqoop import + —as-parquetfile BLOB Data Types (Works Good)
> ###################################################################
> #######################
> # STEP 01 - CREATE DATA
> #######################
> export
> MYCONN=jdbc:oracle:thin:@host-10-17-101-252.coe.cloudera.com:1521/orcl12c;
> export MYUSER=sqoop
> export MYPSWD=welcome1
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
> "create table t1_blob_parq_new (c1 int,c2 blob)"
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
> "insert into t1_blob_parq_new values(1,utl_raw.cast_to_raw('testblobtype'))"
> sqoop eval --connect $MYCONN --username $MYUSER --password $MYPSWD --query
> "select C1,utl_raw.cast_to_varchar2(C2) as C2 from t1_blob_parq_new"
> #########
> OUTPUT
> #########
> -----------------------------------------------
> | C1 | C2 |
> -----------------------------------------------
> | 1 | testblobtype |
> -----------------------------------------------
> [root@host-10-17-81-247 ~]#
> ########################################################
> STEP 02 - IMPORT AS PARQUET FILE
> ########################################################
> sqoop import --connect $MYCONN --username $MYUSER --password $MYPSWD --table
> T1_BLOB_PARQ_NEW --as-parquetfile --target-dir '/projects/t1_bob_parq_new' -m
> 1
> #########
> OUTPUT
> #########
> 17/02/22 08:03:51 INFO mapreduce.ImportJobBase: Beginning import of
> T1_BLOB_PARQ_NEW
> 17/02/22 08:03:52 INFO manager.SqlManager: Executing SQL statement: SELECT
> t.* FROM "T1_BLOB_PARQ_NEW" t WHERE 1=0
> 17/02/22 08:03:52 INFO manager.SqlManager: Executing SQL statement: SELECT
> t.* FROM "T1_BLOB_PARQ_NEW" t WHERE 1=0
> 17/02/22 08:04:00 INFO mapreduce.Job: Running job: job_1487339765830_0010
> 17/02/22 08:04:13 INFO mapreduce.Job: Job job_1487339765830_0010 running in
> uber mode : false
> 17/02/22 08:04:13 INFO mapreduce.Job: map 0% reduce 0%
> 17/02/22 08:04:32 INFO mapreduce.Job: map 100% reduce 0%
> 17/02/22 08:04:32 INFO mapreduce.Job: Job job_1487339765830_0010 completed
> successfully
> 17/02/22 08:04:32 INFO mapreduce.ImportJobBase: Transferred 1.7598 KB in
> 38.6604 seconds (46.611 bytes/sec)
> 17/02/22 08:04:32 INFO mapreduce.ImportJobBase: Retrieved 1 records.
> [root@host-10-17-81-247 ~]# hadoop fs -ls /projects/t1_bob_parq_new
> Found 3 items
> drwxr-xr-x - root supergroup 0 2017-02-22 08:03
> /projects/t1_bob_parq_new/.metadata
> drwxr-xr-x - root supergroup 0 2017-02-22 08:04
> /projects/t1_bob_parq_new/.signals
> -rw-r--r-- 3 root supergroup 666 2017-02-22 08:04
> /projects/t1_bob_parq_new/94e7de9a-88fc-4b6b-be4d-2547698fe599.parquet
> [root@host-10-17-81-247 ~]# hadoop fs -cat
> /projects/t1_bob_parq_new/.metadata/schemas/1.avsc
> {
> "type" : "record",
> "name" : "T1_BLOB_PARQ_NEW",
> "doc" : "Sqoop import of T1_BLOB_PARQ_NEW",
> "fields" : [ {
> "name" : "C1",
> "type" : [ "null", "string" ],
> "default" : null,
> "columnName" : "C1",
> "sqlType" : "2"
> }, {
> "name" : "C2",
> "type" : [ "null", "bytes" ],
> "default" : null,
> "columnName" : "C2",
> "sqlType" : "2004"
> } ],
> "tableName" : "T1_BLOB_PARQ_NEW"
> }
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
