You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by Andre Araujo <ar...@pythian.com> on 2013/11/04 09:18:43 UTC
Using Pig to load data imported with Sqoop
Hi, all,
I've loaded some data with Sqoop from Oracle onto HDFS, storing it as
SequenceFiles and I'm having problems loading it with Pig.
I'm using Sqoop 1.4.3 and used the following steps (simplified example
using the DUAL table).
Any ideas of why it loads incorrectly? Am I missing any steps?
Thanks,
Andre
*1. Imported data from the table onto HDFS (the DUAL table has only 1 row
with 1 field containing the string "X") *
sqoop import -D mapred.child.java.opts="$JDBC_JAVA_OPTS" --connect $CONNSTR
-m 1 --query "select DUMMY from dual where \$CONDITIONS" --target-dir test
--as-sequencefile --class-name com.acme.Dual
The Dual.java file is attached.
*2. Generated the Dual.jar file:*
javac -cp
/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/sqoop/sqoop-1.4.3-cdh4.3.0.jar:/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/client-0.20/hadoop-core-2.0.0-mr1-cdh4.3.0.jar:/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/hadoop-common.jar:.
com/acme/Dual.java
jar cf /tmp/Dual.jar com/acme/Dual.class
*3. Tried to load the data with Pig, however, the field value is read as 0
(zero) instead of the string "X"):*
REGISTER
/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/pig/piggybank.jar;
REGISTER
/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/sqoop/sqoop-1.4.3-cdh4.3.0.jar
REGISTER
/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/client-0.20/hadoop-core-2.0.0-mr1-cdh4.3.0.jar
REGISTER
/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/hadoop-common.jar
REGISTER /tmp/Dual.jar
DEFINE SequenceFileLoader
org.apache.pig.piggybank.storage.SequenceFileLoader();
log = LOAD 'test' USING SequenceFileLoader AS (DUMMY:chararray);
DUMP log;
...
2013-11-04 03:21:32,325 [main] INFO
org.apache.pig.tools.pigstats.SimplePigStats - Script Statistics:
HadoopVersion PigVersion UserId StartedAt FinishedAt
Features
2.0.0-cdh4.3.0 0.11.0-cdh4.3.0 araujo 2013-11-04 03:21:12 2013-11-04
03:21:32 UNKNOWN
Success!
Job Stats (time in seconds):
JobId Maps Reduces MaxMapTime MinMapTIme AvgMapTime
MedianMapTime MaxReduceTime MinReduceTime AvgReduceTime
MedianReducetime Alias Feature Outputs
job_201310230912_0065 1 0 6 6 6 6 0
0 0 0 log MAP_ONLY hdfs://
n1.hadoop.cto.pythian.com:8020/tmp/temp-805635901/tmp-702886222,
Input(s):
Successfully read 1 records (479 bytes) from: "hdfs://
n1.hadoop.cto.pythian.com:8020/user/araujo/test"
Output(s):
Successfully stored 1 records (8 bytes) in: "hdfs://
n1.hadoop.cto.pythian.com:8020/tmp/temp-805635901/tmp-702886222"
Counters:
Total records written : 1
Total bytes written : 8
Spillable Memory Manager spill count : 0
Total bags proactively spilled: 0
Total records proactively spilled: 0
Job DAG:
job_201310230912_0065
2013-11-04 03:21:32,338 [main] INFO
org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.MapReduceLauncher
- Success!
2013-11-04 03:21:32,342 [main] INFO org.apache.pig.data.SchemaTupleBackend
- Key [pig.schematuple] was not set... will not generate code.
2013-11-04 03:21:32,350 [main] INFO
org.apache.hadoop.mapreduce.lib.input.FileInputFormat - Total input paths
to process : 1
2013-11-04 03:21:32,350 [main] INFO
org.apache.pig.backend.hadoop.executionengine.util.MapRedUtil - Total
input paths to process : 1
*(0) <--- THIS SHOULD SHOW "X"*
--
André Araújo
Database Administrator / SDM
The Pythian Group - Australia - www.pythian.com
Office (calls from within Australia): 1300 366 021 x1270
Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270
Mobile: +61 410 323 559
Fax: +61 2 9805 0544
IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
“Success is not about standing at the top, it's the steps you leave behind.”
— Iker Pou (rock climber)
--
--
Re: Using Pig to load data imported with Sqoop
Posted by Jarek Jarcec Cecho <ja...@apache.org>.
I believe that Pig's SequenceFileStorage is not compatible with custom writables at the moment. Per the docs the storage is only able to work with following ones:
Text, IntWritable, LongWritable, FloatWritable, DoubleWritable, BooleanWritable, ByteWritable
Jarcec
On Mon, Nov 04, 2013 at 07:18:43PM +1100, Andre Araujo wrote:
> Hi, all,
>
> I've loaded some data with Sqoop from Oracle onto HDFS, storing it as
> SequenceFiles and I'm having problems loading it with Pig.
> I'm using Sqoop 1.4.3 and used the following steps (simplified example
> using the DUAL table).
>
> Any ideas of why it loads incorrectly? Am I missing any steps?
>
> Thanks,
> Andre
>
>
>
> *1. Imported data from the table onto HDFS (the DUAL table has only 1 row
> with 1 field containing the string "X") *
>
> sqoop import -D mapred.child.java.opts="$JDBC_JAVA_OPTS" --connect $CONNSTR
> -m 1 --query "select DUMMY from dual where \$CONDITIONS" --target-dir test
> --as-sequencefile --class-name com.acme.Dual
>
> The Dual.java file is attached.
>
> *2. Generated the Dual.jar file:*
>
> javac -cp
> /opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/sqoop/sqoop-1.4.3-cdh4.3.0.jar:/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/client-0.20/hadoop-core-2.0.0-mr1-cdh4.3.0.jar:/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/hadoop-common.jar:.
> com/acme/Dual.java
> jar cf /tmp/Dual.jar com/acme/Dual.class
>
> *3. Tried to load the data with Pig, however, the field value is read as 0
> (zero) instead of the string "X"):*
>
> REGISTER
> /opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/pig/piggybank.jar;
> REGISTER
> /opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/sqoop/sqoop-1.4.3-cdh4.3.0.jar
> REGISTER
> /opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/client-0.20/hadoop-core-2.0.0-mr1-cdh4.3.0.jar
> REGISTER
> /opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/hadoop-common.jar
> REGISTER /tmp/Dual.jar
> DEFINE SequenceFileLoader
> org.apache.pig.piggybank.storage.SequenceFileLoader();
> log = LOAD 'test' USING SequenceFileLoader AS (DUMMY:chararray);
> DUMP log;
>
>
> ...
> 2013-11-04 03:21:32,325 [main] INFO
> org.apache.pig.tools.pigstats.SimplePigStats - Script Statistics:
>
> HadoopVersion PigVersion UserId StartedAt FinishedAt
> Features
> 2.0.0-cdh4.3.0 0.11.0-cdh4.3.0 araujo 2013-11-04 03:21:12 2013-11-04
> 03:21:32 UNKNOWN
>
> Success!
>
> Job Stats (time in seconds):
> JobId Maps Reduces MaxMapTime MinMapTIme AvgMapTime
> MedianMapTime MaxReduceTime MinReduceTime AvgReduceTime
> MedianReducetime Alias Feature Outputs
> job_201310230912_0065 1 0 6 6 6 6 0
> 0 0 0 log MAP_ONLY hdfs://
> n1.hadoop.cto.pythian.com:8020/tmp/temp-805635901/tmp-702886222,
>
> Input(s):
> Successfully read 1 records (479 bytes) from: "hdfs://
> n1.hadoop.cto.pythian.com:8020/user/araujo/test"
>
> Output(s):
> Successfully stored 1 records (8 bytes) in: "hdfs://
> n1.hadoop.cto.pythian.com:8020/tmp/temp-805635901/tmp-702886222"
>
> Counters:
> Total records written : 1
> Total bytes written : 8
> Spillable Memory Manager spill count : 0
> Total bags proactively spilled: 0
> Total records proactively spilled: 0
>
> Job DAG:
> job_201310230912_0065
>
>
> 2013-11-04 03:21:32,338 [main] INFO
> org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.MapReduceLauncher
> - Success!
> 2013-11-04 03:21:32,342 [main] INFO org.apache.pig.data.SchemaTupleBackend
> - Key [pig.schematuple] was not set... will not generate code.
> 2013-11-04 03:21:32,350 [main] INFO
> org.apache.hadoop.mapreduce.lib.input.FileInputFormat - Total input paths
> to process : 1
> 2013-11-04 03:21:32,350 [main] INFO
> org.apache.pig.backend.hadoop.executionengine.util.MapRedUtil - Total
> input paths to process : 1
> *(0) <--- THIS SHOULD SHOW "X"*
>
>
> --
> André Araújo
> Database Administrator / SDM
> The Pythian Group - Australia - www.pythian.com
>
> Office (calls from within Australia): 1300 366 021 x1270
> Office (international): +61 2 8016 7000 x270 *OR* +1 613 565 8696 x1270
> Mobile: +61 410 323 559
> Fax: +61 2 9805 0544
> IM: pythianaraujo @ AIM/MSN/Y! or araujo@pythian.com @ GTalk
>
> “Success is not about standing at the top, it's the steps you leave behind.”
> — Iker Pou (rock climber)
>
> --
>
>
> --
>
>
>