Hi Venkat, All most column have some value except these three.
Regards, Ajit -----Original Message----- From: Venkat Ranganathan [mailto:vranganat...@hortonworks.com] Sent: Wednesday, March 06, 2013 9:36 PM To: user@hive.apache.org Cc: u...@sqoop.apache.org Subject: Re: Data mismatch when importing data from Oracle to Hive through Sqoop without an error Hi Ajit Do you know if rest of the columns also are null when the three non null columns are null Venkat On Wed, Mar 6, 2013 at 12:35 AM, Ajit Kumar Shreevastava <ajit.shreevast...@hcl.com> wrote: > Hi Abhijeet, > > > > Thanks for your response. > > If values that don't fit in double must be getting inserted as Null is > the case then count should not be mis-match in both the case. > > Here the null value inserted are extra value apart from the other > value which is already present in both Oracle Table and Hive table. > > > > Correct me if I am wrong in interpretation. > > > > Thanks and Regards, > > Ajit Kumar Shreevastava > > > > From: abhijeet gaikwad [mailto:abygaikwa...@gmail.com] > Sent: Wednesday, March 06, 2013 1:46 PM > To: user@hive.apache.org > Cc: u...@sqoop.apache.org > Subject: Re: Data mismatch when importing data from Oracle to Hive > through Sqoop without an error > > > > Sqoop maps numeric and decimal types (RDBMS) to double (Hive). I think > the values that don't fit in double must be getting inserted as NULL. > You can see this warning in your logs. > > Thanks, > Abhijeet > > On Wed, Mar 6, 2013 at 1:32 PM, Ajit Kumar Shreevastava > <ajit.shreevast...@hcl.com> wrote: > > Hi all, > > I have notice one interesting thing in the below result-set. > > I have fired one query in both Oracle and Hive shell and found the > following result set:à > > > > SQL> select count(1) from bttn > > 2 where bttn_id is null or data_inst_id is null or scr_id is null; > > > > COUNT(1) > > ---------- > > 0 > > hive> select count(1) from bttn > > > where bttn_id is null or data_inst_id is null or scr_id is null; > > Total MapReduce jobs = 1 > > Launching Job 1 out of 1 > > Number of reduce tasks determined at compile time: 1 > > In order to change the average load for a reducer (in bytes): > > set hive.exec.reducers.bytes.per.reducer=<number> > > In order to limit the maximum number of reducers: > > set hive.exec.reducers.max=<number> > > In order to set a constant number of reducers: > > set mapred.reduce.tasks=<number> > > Starting Job = job_201303051835_0020, Tracking URL = > http://NHCLT-PC44-2:50030/jobdetails.jsp?jobid=job_201303051835_0020 > > Kill Command = /home/hadoop/hadoop-1.0.3/bin/hadoop job -kill > job_201303051835_0020 > > Hadoop job information for Stage-1: number of mappers: 1; number of > reducers: 1 > > 2013-03-06 13:22:56,908 Stage-1 map = 0%, reduce = 0% > > 2013-03-06 13:23:05,928 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:06,931 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:07,934 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:08,938 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:09,941 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:10,944 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:11,947 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:12,956 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:13,959 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU 5.2 sec > > 2013-03-06 13:23:14,962 Stage-1 map = 100%, reduce = 33%, Cumulative > CPU > 5.2 sec > > 2013-03-06 13:23:15,965 Stage-1 map = 100%, reduce = 33%, Cumulative > CPU > 5.2 sec > > 2013-03-06 13:23:16,969 Stage-1 map = 100%, reduce = 33%, Cumulative > CPU > 5.2 sec > > 2013-03-06 13:23:17,974 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 6.95 sec > > 2013-03-06 13:23:18,977 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 6.95 sec > > 2013-03-06 13:23:19,981 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 6.95 sec > > 2013-03-06 13:23:20,985 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 6.95 sec > > 2013-03-06 13:23:21,988 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 6.95 sec > > 2013-03-06 13:23:22,995 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 6.95 sec > > 2013-03-06 13:23:23,998 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 6.95 sec > > MapReduce Total cumulative CPU time: 6 seconds 950 msec > > Ended Job = job_201303051835_0020 > > MapReduce Jobs Launched: > > Job 0: Map: 1 Reduce: 1 Cumulative CPU: 6.95 sec HDFS Read: 184270926 > HDFS Write: 4 SUCCESS > > Total MapReduce CPU Time Spent: 6 seconds 950 msec > > OK > > 986 > > Time taken: 35.983 seconds > > hive> > > > > and 739169 - 738183=986 > > > > can anyone tell me why this happened as BTTN_ID , DATA_INST_ID, > SCR_ID are not null constrains of BTTN table and also composite > Primary Key of the table? > > Also tell me how can I prevent this unnecessary data generation in > HIVE table. > > > > Regards > > Ajit Kumar Shreevastava > > > > From: Ajit Kumar Shreevastava > Sent: Wednesday, March 06, 2013 12:40 PM > To: 'u...@sqoop.apache.org' > Cc: user@hive.apache.org > Subject: Data mismatch when importing data from Oracle to Hive through > Sqoop without an error > > > > HI, > > > > I have a table BTTN in Oracle database having 738183 records:à > > > > SQL> select count(1) from bttn; > > > > COUNT(1) > > ---------- > > 738183 > > > > Now I want to import this table to HIVE and I have fired the > following command at command prompt:à > > > > [hadoop@NHCLT-PC44-2 sqoop-oper]$ sqoop import --connect > jdbc:oracle:thin:@10.99.42.11:1521/clouddb --username HDFSUSER > --table BTTN --verbose -P --hive-table bttn --create-hive-table > --hive-import --hive-home /home/hadoop/user/hive/warehouse > > The output is as follows:à > > Warning: /usr/lib/hbase does not exist! HBase imports will fail. > > Please set $HBASE_HOME to the root of your HBase installation. > > 13/03/06 12:16:25 DEBUG tool.BaseSqoopTool: Enabled debug logging. > > Enter password: > > 13/03/06 12:16:32 INFO tool.BaseSqoopTool: Using Hive-specific > delimiters for output. You can override > > 13/03/06 12:16:32 INFO tool.BaseSqoopTool: delimiters with > --fields-terminated-by, etc. > > 13/03/06 12:16:32 DEBUG sqoop.ConnFactory: Loaded manager factory: > com.cloudera.sqoop.manager.DefaultManagerFactory > > 13/03/06 12:16:32 DEBUG sqoop.ConnFactory: Trying ManagerFactory: > com.cloudera.sqoop.manager.DefaultManagerFactory > > 13/03/06 12:16:32 DEBUG manager.DefaultManagerFactory: Trying with scheme: > jdbc:oracle:thin:@10.99.42.11 > > 13/03/06 12:16:32 DEBUG manager.OracleManager$ConnCache: Instantiated > new connection cache. > > 13/03/06 12:16:32 INFO manager.SqlManager: Using default fetchSize of > 1000 > > 13/03/06 12:16:32 DEBUG sqoop.ConnFactory: Instantiated ConnManager > org.apache.sqoop.manager.OracleManager@2393385d > > 13/03/06 12:16:32 INFO tool.CodeGenTool: Beginning code generation > > 13/03/06 12:16:32 DEBUG manager.OracleManager: Using column names query: > SELECT t.* FROM BTTN t WHERE 1=0 > > 13/03/06 12:16:32 DEBUG manager.OracleManager: Creating a new > connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb, using > username: HDFSUSER > > 13/03/06 12:16:32 DEBUG manager.OracleManager: No connection > paramenters specified. Using regular API for making connection. > > 13/03/06 12:16:32 INFO manager.OracleManager: Time zone has been set > to GMT > > 13/03/06 12:16:32 DEBUG manager.SqlManager: Using fetchSize for next query: > 1000 > > 13/03/06 12:16:32 INFO manager.SqlManager: Executing SQL statement: > SELECT > t.* FROM BTTN t WHERE 1=0 > > 13/03/06 12:16:32 DEBUG manager.OracleManager$ConnCache: Caching > released connection for > jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: selected columns: > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BTTN_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: DATA_INST_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: SCR_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BTTN_NU > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: CAT > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: WDTH > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: HGHT > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: KEY_SCAN > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: KEY_SHFT > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: FRGND_CPTN_COLR > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: FRGND_CPTN_COLR_PRSD > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BKGD_CPTN_COLR > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BKGD_CPTN_COLR_PRSD > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BLM_FL > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: LCLZ_FL > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: MENU_ITEM_NU > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BTTN_ASGN_LVL_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: ON_ATVT > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: ON_CLIK > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: ENBL_FL > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BLM_SET_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BTTN_ASGN_LVL_NAME > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: MKT_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: CRTE_TS > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: CRTE_USER_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: UPDT_TS > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: UPDT_USER_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: DEL_TS > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: DEL_USER_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: DLTD_FL > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: MENU_ITEM_NA > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: PRD_CD > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BLM_SET_NA > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: SOUND_FILE_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: IS_DYNMC_BTTN > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: FRGND_CPTN_COLR_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: FRGND_CPTN_COLR_PRSD_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BKGD_CPTN_COLR_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: BKGD_CPTN_COLR_PRSD_ID > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: Writing source file: > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/BTTN.java > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: Table name: BTTN > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: Columns: BTTN_ID:2, > DATA_INST_ID:2, SCR_ID:2, BTTN_NU:2, CAT:2, WDTH:2, HGHT:2, > KEY_SCAN:2, KEY_SHFT:2, FRGND_CPTN_COLR:12, FRGND_CPTN_COLR_PRSD:12, > BKGD_CPTN_COLR:12, BKGD_CPTN_COLR_PRSD:12, BLM_FL:2, LCLZ_FL:2, > MENU_ITEM_NU:2, BTTN_ASGN_LVL_ID:2, ON_ATVT:2, ON_CLIK:2, ENBL_FL:2, > BLM_SET_ID:2, BTTN_ASGN_LVL_NAME:12, MKT_ID:2, CRTE_TS:93, > CRTE_USER_ID:12, UPDT_TS:93, UPDT_USER_ID:12, DEL_TS:93, > DEL_USER_ID:12, DLTD_FL:2, MENU_ITEM_NA:12, PRD_CD:2, BLM_SET_NA:12, > SOUND_FILE_ID:2, IS_DYNMC_BTTN:2, FRGND_CPTN_COLR_ID:2, > FRGND_CPTN_COLR_PRSD_ID:2, BKGD_CPTN_COLR_ID:2, > BKGD_CPTN_COLR_PRSD_ID:2, > > 13/03/06 12:16:32 DEBUG orm.ClassWriter: sourceFilename is BTTN.java > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: Found existing > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/ > > 13/03/06 12:16:32 INFO orm.CompilationManager: HADOOP_HOME is > /home/hadoop/hadoop-1.0.3/libexec/.. > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: Adding source file: > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/BTTN.java > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: Invoking javac with args: > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: -sourcepath > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/ > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: -d > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/ > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: -classpath > > 13/03/06 12:16:32 DEBUG orm.CompilationManager: > /home/hadoop/hadoop-1.0.3/libexec/../conf:/usr/java/jdk1.6.0_32/lib/to > ols.jar:/home/hadoop/hadoop-1.0.3/libexec/..:/home/hadoop/hadoop-1.0.3 > /libexec/../hadoop-core-1.0.3.jar:/home/hadoop/hadoop-1.0.3/libexec/.. > /lib/asm-3.2.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/aspectjrt-1. > 6.5.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/aspectjtools-1.6.5.ja > r:/home/hadoop/hadoop-1.0.3/libexec/../lib/commons-beanutils-1.7.0.jar > :/home/hadoop/hadoop-1.0.3/libexec/../lib/commons-beanutils-core-1.8.0 > .jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/commons-cli-1.2.jar:/hom > e/hadoop/hadoop-1.0.3/libexec/../lib/commons-codec-1.4.jar:/home/hadoo > p/hadoop-1.0.3/libexec/../lib/commons-collections-3.2.1.jar:/home/hado > op/hadoop-1.0.3/libexec/../lib/commons-configuration-1.6.jar:/home/had > oop/hadoop-1.0.3/libexec/../lib/commons-daemon-1.0.1.jar:/home/hadoop/ > hadoop-1.0.3/libexec/../lib/commons-digester-1.8.jar:/home/hadoop/hado > op-1.0.3/libexec/../lib/commons-el-1.0.jar:/home/hadoop/hadoop-1.0.3/l > ibexec/../lib/commons-httpclient-3.0.1.jar:/home/hadoop/hadoop-1.0.3/l > ibexec/../lib/commons-io-2.1.jar:/home/hadoop/hadoop-1.0.3/libexec/../ > lib/commons-lang-2.4.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/comm > ons-logging-1.1.1.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/commons > -logging-api-1.0.4.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/common > s-math-2.1.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/commons-net-1. > 4.1.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/core-3.1.1.jar:/home/ > hadoop/hadoop-1.0.3/libexec/../lib/hadoop-capacity-scheduler-1.0.3.jar > :/home/hadoop/hadoop-1.0.3/libexec/../lib/hadoop-fairscheduler-1.0.3.j > ar:/home/hadoop/hadoop-1.0.3/libexec/../lib/hadoop-thriftfs-1.0.3.jar: > /home/hadoop/hadoop-1.0.3/libexec/../lib/hsqldb-1.8.0.10.jar:/home/had > oop/hadoop-1.0.3/libexec/../lib/jackson-core-asl-1.8.8.jar:/home/hadoo > p/hadoop-1.0.3/libexec/../lib/jackson-mapper-asl-1.8.8.jar:/home/hadoo > p/hadoop-1.0.3/libexec/../lib/jasper-compiler-5.5.12.jar:/home/hadoop/ > hadoop-1.0.3/libexec/../lib/jasper-runtime-5.5.12.jar:/home/hadoop/had > oop-1.0.3/libexec/../lib/jdeb-0.8.jar:/home/hadoop/hadoop-1.0.3/libexe > c/../lib/jersey-core-1.8.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/ > jersey-json-1.8.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/jersey-se > rver-1.8.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/jets3t-0.6.1.jar > :/home/hadoop/hadoop-1.0.3/libexec/../lib/jetty-6.1.26.jar:/home/hadoo > p/hadoop-1.0.3/libexec/../lib/jetty-util-6.1.26.jar:/home/hadoop/hadoo > p-1.0.3/libexec/../lib/jsch-0.1.42.jar:/home/hadoop/hadoop-1.0.3/libex > ec/../lib/junit-4.5.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/kfs-0 > .2.2.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/log4j-1.2.15.jar:/ho > me/hadoop/hadoop-1.0.3/libexec/../lib/mockito-all-1.8.5.jar:/home/hado > op/hadoop-1.0.3/libexec/../lib/oro-2.0.8.jar:/home/hadoop/hadoop-1.0.3 > /libexec/../lib/servlet-api-2.5-20081211.jar:/home/hadoop/hadoop-1.0.3 > /libexec/../lib/slf4j-api-1.4.3.jar:/home/hadoop/hadoop-1.0.3/libexec/ > ../lib/slf4j-log4j12-1.4.3.jar:/home/hadoop/hadoop-1.0.3/libexec/../li > b/xmlenc-0.52.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/jsp-2.1/jsp > -2.1.jar:/home/hadoop/hadoop-1.0.3/libexec/../lib/jsp-2.1/jsp-api-2.1. > jar:/home/hadoop/sqoop/conf::/home/hadoop/sqoop/lib/ant-contrib-1.0b3. > jar:/home/hadoop/sqoop/lib/ant-eclipse-1.0-jvm1.2.jar:/home/hadoop/sqo > op/lib/avro-1.5.3.jar:/home/hadoop/sqoop/lib/avro-ipc-1.5.3.jar:/home/ > hadoop/sqoop/lib/avro-mapred-1.5.3.jar:/home/hadoop/sqoop/lib/commons- > io-1.4.jar:/home/hadoop/sqoop/lib/hsqldb-1.8.0.10.jar:/home/hadoop/sqo > op/lib/jackson-core-asl-1.7.3.jar:/home/hadoop/sqoop/lib/jackson-mappe > r-asl-1.7.3.jar:/home/hadoop/sqoop/lib/jopt-simple-3.2.jar:/home/hadoo > p/sqoop/lib/ojdbc6.jar:/home/hadoop/sqoop/lib/paranamer-2.3.jar:/home/ > hadoop/sqoop/lib/snappy-java-1.0.3.2.jar:/home/hadoop/sqoop/sqoop-1.4. > 2.jar:/home/hadoop/sqoop/sqoop-test-1.4.2.jar::/home/hadoop/hadoop-1.0 > .3/hadoop-core-1.0.3.jar:/home/hadoop/sqoop/sqoop-1.4.2.jar > > Note: > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/BTTN.java > uses or overrides a deprecated API. > > Note: Recompile with -Xlint:deprecation for details. > > 13/03/06 12:16:34 INFO orm.CompilationManager: Writing jar file: > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/BTTN.jar > > 13/03/06 12:16:34 DEBUG orm.CompilationManager: Scanning for .class > files in > directory: /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e > > 13/03/06 12:16:34 DEBUG orm.CompilationManager: Got classfile: > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/BTTN.class > -> BTTN.class > > 13/03/06 12:16:34 DEBUG orm.CompilationManager: Finished writing jar > file > /tmp/sqoop-hadoop/compile/6aa39c498a3c94ffa01a5352f368712e/BTTN.jar > > 13/03/06 12:16:34 DEBUG manager.OracleManager$ConnCache: Got cached > connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:16:34 INFO manager.OracleManager: Time zone has been set > to GMT > > 13/03/06 12:16:34 DEBUG manager.OracleManager$ConnCache: Caching > released connection for > jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:16:34 WARN manager.OracleManager: The table BTTN contains > a multi-column primary key. Sqoop will default to the column BTTN_ID > only for this job. > > 13/03/06 12:16:34 DEBUG manager.OracleManager$ConnCache: Got cached > connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:16:34 INFO manager.OracleManager: Time zone has been set > to GMT > > 13/03/06 12:16:34 DEBUG manager.OracleManager$ConnCache: Caching > released connection for > jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:16:34 WARN manager.OracleManager: The table BTTN contains > a multi-column primary key. Sqoop will default to the column BTTN_ID > only for this job. > > 13/03/06 12:16:34 INFO mapreduce.ImportJobBase: Beginning import of > BTTN > > 13/03/06 12:16:34 DEBUG manager.OracleManager$ConnCache: Got cached > connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:16:34 INFO manager.OracleManager: Time zone has been set > to GMT > > 13/03/06 12:16:34 DEBUG manager.OracleManager$ConnCache: Caching > released connection for > jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:16:34 DEBUG mapreduce.DataDrivenImportJob: Using table class: > BTTN > > 13/03/06 12:16:34 DEBUG mapreduce.DataDrivenImportJob: Using InputFormat: > class com.cloudera.sqoop.mapreduce.db.OracleDataDrivenDBInputFormat > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/sqoop-1.4.2.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/ojdbc6.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/sqoop-1.4.2.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/sqoop-1.4.2.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/jackson-mapper-asl-1.7.3.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/hsqldb-1.8.0.10.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/avro-ipc-1.5.3.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/jopt-simple-3.2.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/ojdbc6.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/jackson-core-asl-1.7.3.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/ant-contrib-1.0b3.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/ant-eclipse-1.0-jvm1.2.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/snappy-java-1.0.3.2.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/paranamer-2.3.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/avro-1.5.3.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/commons-io-1.4.jar > > 13/03/06 12:16:34 DEBUG mapreduce.JobBase: Adding to job classpath: > file:/home/hadoop/sqoop/lib/avro-mapred-1.5.3.jar > > 13/03/06 12:16:35 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: > SELECT MIN(BTTN_ID), MAX(BTTN_ID) FROM BTTN > > 13/03/06 12:16:35 INFO mapred.JobClient: Running job: > job_201303051835_0014 > > 13/03/06 12:16:36 INFO mapred.JobClient: map 0% reduce 0% > > 13/03/06 12:16:52 INFO mapred.JobClient: map 25% reduce 0% > > 13/03/06 12:17:01 INFO mapred.JobClient: map 75% reduce 0% > > 13/03/06 12:17:13 INFO mapred.JobClient: map 100% reduce 0% > > 13/03/06 12:17:19 INFO mapred.JobClient: Job complete: > job_201303051835_0014 > > 13/03/06 12:17:19 INFO mapred.JobClient: Counters: 18 > > 13/03/06 12:17:19 INFO mapred.JobClient: Job Counters > > 13/03/06 12:17:19 INFO mapred.JobClient: SLOTS_MILLIS_MAPS=71591 > > 13/03/06 12:17:19 INFO mapred.JobClient: Total time spent by all reduces > waiting after reserving slots (ms)=0 > > 13/03/06 12:17:19 INFO mapred.JobClient: Total time spent by all maps > waiting after reserving slots (ms)=0 > > 13/03/06 12:17:19 INFO mapred.JobClient: Launched map tasks=4 > > 13/03/06 12:17:19 INFO mapred.JobClient: SLOTS_MILLIS_REDUCES=0 > > 13/03/06 12:17:19 INFO mapred.JobClient: File Output Format Counters > > 13/03/06 12:17:19 INFO mapred.JobClient: Bytes Written=184266237 > > 13/03/06 12:17:19 INFO mapred.JobClient: FileSystemCounters > > 13/03/06 12:17:19 INFO mapred.JobClient: HDFS_BYTES_READ=454 > > 13/03/06 12:17:19 INFO mapred.JobClient: FILE_BYTES_WRITTEN=117870 > > 13/03/06 12:17:19 INFO mapred.JobClient: HDFS_BYTES_WRITTEN=184266237 > > 13/03/06 12:17:19 INFO mapred.JobClient: File Input Format Counters > > 13/03/06 12:17:19 INFO mapred.JobClient: Bytes Read=0 > > 13/03/06 12:17:19 INFO mapred.JobClient: Map-Reduce Framework > > 13/03/06 12:17:19 INFO mapred.JobClient: Map input records=738183 > > 13/03/06 12:17:19 INFO mapred.JobClient: Physical memory (bytes) > snapshot=735293440 > > 13/03/06 12:17:19 INFO mapred.JobClient: Spilled Records=0 > > 13/03/06 12:17:19 INFO mapred.JobClient: CPU time spent (ms)=28950 > > 13/03/06 12:17:19 INFO mapred.JobClient: Total committed heap usage > (bytes)=581435392 > > 13/03/06 12:17:19 INFO mapred.JobClient: Virtual memory (bytes) > snapshot=6086893568 > > 13/03/06 12:17:19 INFO mapred.JobClient: Map output records=738183 > > 13/03/06 12:17:19 INFO mapred.JobClient: SPLIT_RAW_BYTES=454 > > 13/03/06 12:17:19 INFO mapreduce.ImportJobBase: Transferred 175.73 MB > in > 45.4959 seconds (3.8625 MB/sec) > > 13/03/06 12:17:19 INFO mapreduce.ImportJobBase: Retrieved 738183 records. > > 13/03/06 12:17:19 DEBUG hive.HiveImport: Hive.inputTable: BTTN > > 13/03/06 12:17:19 DEBUG hive.HiveImport: Hive.outputTable: bttn > > 13/03/06 12:17:19 DEBUG manager.OracleManager: Using column names query: > SELECT t.* FROM BTTN t WHERE 1=0 > > 13/03/06 12:17:19 DEBUG manager.OracleManager$ConnCache: Got cached > connection for jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:17:19 INFO manager.OracleManager: Time zone has been set > to GMT > > 13/03/06 12:17:19 DEBUG manager.SqlManager: Using fetchSize for next query: > 1000 > > 13/03/06 12:17:19 INFO manager.SqlManager: Executing SQL statement: > SELECT > t.* FROM BTTN t WHERE 1=0 > > 13/03/06 12:17:19 DEBUG manager.OracleManager$ConnCache: Caching > released connection for > jdbc:oracle:thin:@10.99.42.11:1521/clouddb/HDFSUSER > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column BTTN_ID had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column DATA_INST_ID had to > be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column SCR_ID had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column BTTN_NU had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column CAT had to be cast > to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column WDTH had to be cast > to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column HGHT had to be cast > to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column KEY_SCAN had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column KEY_SHFT had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column BLM_FL had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column LCLZ_FL had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column MENU_ITEM_NU had to > be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column BTTN_ASGN_LVL_ID > had to be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column ON_ATVT had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column ON_CLIK had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column ENBL_FL had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column BLM_SET_ID had to > be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column MKT_ID had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column CRTE_TS had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column UPDT_TS had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column DEL_TS had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column DLTD_FL had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column PRD_CD had to be > cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column SOUND_FILE_ID had > to be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column IS_DYNMC_BTTN had > to be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column FRGND_CPTN_COLR_ID > had to be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column > FRGND_CPTN_COLR_PRSD_ID had to be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column BKGD_CPTN_COLR_ID > had to be cast to a less precise type in Hive > > 13/03/06 12:17:19 WARN hive.TableDefWriter: Column > BKGD_CPTN_COLR_PRSD_ID had to be cast to a less precise type in Hive > > 13/03/06 12:17:19 DEBUG hive.TableDefWriter: Create statement: CREATE > TABLE `bttn` ( `BTTN_ID` DOUBLE, `DATA_INST_ID` DOUBLE, `SCR_ID` > DOUBLE, `BTTN_NU` DOUBLE, `CAT` DOUBLE, `WDTH` DOUBLE, `HGHT` DOUBLE, > `KEY_SCAN` DOUBLE, `KEY_SHFT` DOUBLE, `FRGND_CPTN_COLR` STRING, > `FRGND_CPTN_COLR_PRSD` STRING, `BKGD_CPTN_COLR` STRING, > `BKGD_CPTN_COLR_PRSD` STRING, `BLM_FL` DOUBLE, `LCLZ_FL` DOUBLE, > `MENU_ITEM_NU` DOUBLE, `BTTN_ASGN_LVL_ID` DOUBLE, `ON_ATVT` DOUBLE, > `ON_CLIK` DOUBLE, `ENBL_FL` DOUBLE, `BLM_SET_ID` DOUBLE, > `BTTN_ASGN_LVL_NAME` STRING, `MKT_ID` DOUBLE, `CRTE_TS` STRING, > `CRTE_USER_ID` STRING, `UPDT_TS` STRING, `UPDT_USER_ID` STRING, > `DEL_TS` STRING, `DEL_USER_ID` STRING, `DLTD_FL` DOUBLE, > `MENU_ITEM_NA` STRING, `PRD_CD` DOUBLE, `BLM_SET_NA` STRING, > `SOUND_FILE_ID` DOUBLE, `IS_DYNMC_BTTN` DOUBLE, `FRGND_CPTN_COLR_ID` > DOUBLE, `FRGND_CPTN_COLR_PRSD_ID` DOUBLE, `BKGD_CPTN_COLR_ID` DOUBLE, > `BKGD_CPTN_COLR_PRSD_ID` DOUBLE) COMMENT 'Imported by sqoop on > 2013/03/06 12:17:19' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' > LINES TERMINATED BY '\012' STORED AS TEXTFILE > > 13/03/06 12:17:19 DEBUG hive.TableDefWriter: Load statement: LOAD DATA > INPATH 'hdfs://NHCLT-PC44-2:8020/user/hadoop/BTTN' INTO TABLE `bttn` > > 13/03/06 12:17:19 INFO hive.HiveImport: Removing temporary files from > import > process: hdfs://NHCLT-PC44-2:8020/user/hadoop/BTTN/_logs > > 13/03/06 12:17:19 INFO hive.HiveImport: Loading uploaded data into > Hive > > 13/03/06 12:17:19 DEBUG hive.HiveImport: Using external Hive process. > > 13/03/06 12:17:21 INFO hive.HiveImport: Logging initialized using > configuration in file:/home/hadoop/Hive/conf/hive-log4j.properties > > 13/03/06 12:17:21 INFO hive.HiveImport: Hive history > file=/home/hadoop/tmp/hadoop/hive_job_log_hadoop_201303061217_79020659 > 6.txt > > 13/03/06 12:17:32 INFO hive.HiveImport: OK > > 13/03/06 12:17:32 INFO hive.HiveImport: Time taken: 11.345 seconds > > 13/03/06 12:17:32 INFO hive.HiveImport: Loading data to table > default.bttn > > 13/03/06 12:17:33 INFO hive.HiveImport: Table default.bttn stats: > [num_partitions: 0, num_files: 5, num_rows: 0, total_size: 184266237, > raw_data_size: 0] > > 13/03/06 12:17:33 INFO hive.HiveImport: OK > > 13/03/06 12:17:33 INFO hive.HiveImport: Time taken: 0.36 seconds > > 13/03/06 12:17:33 INFO hive.HiveImport: Hive import complete. > > 13/03/06 12:17:33 INFO hive.HiveImport: Export directory is empty, > removing it. > > [hadoop@NHCLT-PC44-2 sqoop-oper]$ > > > > Now I have fired the following query at hive prompt:à > > > > hive> select count(1) from bttn; > > Total MapReduce jobs = 1 > > Launching Job 1 out of 1 > > Number of reduce tasks determined at compile time: 1 > > In order to change the average load for a reducer (in bytes): > > set hive.exec.reducers.bytes.per.reducer=<number> > > In order to limit the maximum number of reducers: > > set hive.exec.reducers.max=<number> > > In order to set a constant number of reducers: > > set mapred.reduce.tasks=<number> > > Starting Job = job_201303051835_0016, Tracking URL = > http://NHCLT-PC44-2:50030/jobdetails.jsp?jobid=job_201303051835_0016 > > Kill Command = /home/hadoop/hadoop-1.0.3/bin/hadoop job -kill > job_201303051835_0016 > > Hadoop job information for Stage-1: number of mappers: 1; number of > reducers: 1 > > 2013-03-06 12:21:45,350 Stage-1 map = 0%, reduce = 0% > > 2013-03-06 12:21:54,370 Stage-1 map = 76%, reduce = 0% > > 2013-03-06 12:21:57,378 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:21:58,381 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:21:59,385 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:00,388 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:01,391 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:02,394 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:03,397 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:04,401 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:05,404 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:06,407 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:07,410 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:08,415 Stage-1 map = 100%, reduce = 0%, Cumulative > CPU > 3.68 sec > > 2013-03-06 12:22:09,418 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 7.37 sec > > 2013-03-06 12:22:10,421 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 7.37 sec > > 2013-03-06 12:22:11,425 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 7.37 sec > > 2013-03-06 12:22:12,428 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 7.37 sec > > 2013-03-06 12:22:13,432 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 7.37 sec > > 2013-03-06 12:22:14,435 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 7.37 sec > > 2013-03-06 12:22:15,439 Stage-1 map = 100%, reduce = 100%, Cumulative > CPU > 7.37 sec > > MapReduce Total cumulative CPU time: 7 seconds 370 msec > > Ended Job = job_201303051835_0016 > > MapReduce Jobs Launched: > > Job 0: Map: 1 Reduce: 1 Cumulative CPU: 7.37 sec HDFS Read: 184270926 > HDFS Write: 7 SUCCESS > > Total MapReduce CPU Time Spent: 7 seconds 370 msec > > OK > > 739169 > > Time taken: 37.639 seconds > > hive> > > > > The data present in oracle database is 738183 but imported into HIVE > is 739169. > > Can you please let me know the issue. > > I have already pasted all my screen log hare. > > > > Thanks and Regards, > > Ajit Kumar Shreevastava > > > > ::DISCLAIMER:: > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > -------- > > The contents of this e-mail and any attachment(s) are confidential and > intended for the named recipient(s) only. > E-mail transmission is not guaranteed to be secure or error-free as > information could be intercepted, corrupted, lost, destroyed, arrive > late or incomplete, or may contain viruses in transmission. The e mail > and its contents (with or without referred errors) shall therefore not > attach any liability on the originator or HCL or its affiliates. > Views or opinions, if any, presented in this email are solely those of > the author and may not necessarily reflect the views or opinions of > HCL or its affiliates. Any form of reproduction, dissemination, > copying, disclosure, modification, distribution and / or publication > of this message without the prior written consent of authorized > representative of HCL is strictly prohibited. If you have received > this email in error please delete it and notify the sender > immediately. > Before opening any email and/or attachments, please check them for > viruses and other defects. > > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > -------- > >
