Re: [sqoop-user] Import from mysql table with bit fields

["arvind@cloudera.com" <ar...@cloudera.com>]

[Moving the conversation to Apache mailing list:
sqoop-user@incubator.apache.org]

Ken,

Using CDH3U1 version of Hive and the latest sources from Sqoop trunk,
I was able to successfully import a table from MySQL containing a bit
field into Hive. I did this using defaults and only specifying the
--hive-import option.

One thing you can look at to troubleshoot this further is the hive.log
file that gets generated under /tmp/${user}/ directory. This file
should identify if there are any exceptions during the load. You can
also look at the directory by the name of the table under
/user/hive/warehouse on HDFS to see the contents of the imported data.

Thanks,
Arvind

On Tue, Aug 16, 2011 at 1:12 PM, Ken <ke...@gmail.com> wrote:
> I have been trying to get a mysql table to import into hive using
> sqoop. I have tried many variations of fields-terminated-by/lines-
> terminated-by, mysql-delimiters, etc. however cannot get the bit
> fields to show up in hive. The bit fields always end up as NULL. I
> have tried both --direct and not. I have tried sqoop'ing it into HDFS
> and then moving it. I have tried --hive-import --hive-overwrite but
> nothing seems to work. What am I missing?
>
> The bit fields get created in hive as boolean.
>
> Any suggestions/pointer would be more helpful.
>
> Much thanks.
>
>
> --
> NOTE: The mailing list sqoop-user@cloudera.org is deprecated in favor of Apache Sqoop mailing list sqoop-user@incubator.apache.org. Please subscribe to it by sending an email to incubator-sqoop-user-subscribe@apache.org.
>

Re: [sqoop-user] Re: Import from mysql table with bit fields

["arvind@cloudera.com" <ar...@cloudera.com>]

[bcc:sqoop-user@cloudera.org, to:sqoop-user@incubator.apache.org]

Here are the details:

Hive Table:

hive> describe extended foo;
OK
a	boolean	
b	string	
	 	
Detailed Table Information	Table(tableName:foo, dbName:default,
owner:arvind, createTime:1313688938, lastAccessTime:0, retention:0,
sd:StorageDescriptor(cols:[FieldSchema(name:a, type:boolean,
comment:null), FieldSchema(name:b, type:string, comment:null)],
location:hdfs://localhost/user/hive/warehouse/foo,
inputFormat:org.apache.hadoop.mapred.TextInputFormat,
outputFormat:org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat,
compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null,
serializationLib:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe,
parameters:{serialization.format= , field.delim= , line.delim=
}), bucketCols:[], sortCols:[], parameters:{}), partitionKeys:[],
parameters:{transient_lastDdlTime=1313688939, comment=Imported by
sqoop on 2011/08/18 10:35:32}, viewOriginalText:null,
viewExpandedText:null, tableType:MANAGED_TABLE)
Time taken: 0.057 seconds
hive>


MySQL Detiails:
mysql> describe foo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | bit(1)      | YES  |     | NULL    |       |
| b     | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


Sqoop command:
bin/sqoop import --connect jdbc:mysql://localhost/testdb --username
test --password test --table foo --hive-import -m 1


Hope this helps.

Thanks,
Arvind

On Thu, Aug 18, 2011 at 11:57 AM, Ken <ke...@gmail.com> wrote:
> Hi Arvind and thanks for getting back to me.
>
> At this point I have manually defined the hive table to use tinyint
> and am
> using a sqoop free form query and converting the bit columns to
> tinyint (bitcol+0).
>
> This is working well but its not the most direct method. What would
> help is if you
> could post your table definitions (both mysql and hive) and the sqoop
> command you
> are using so I can find out where I went wrong (or if this is a source
> code version
> issue).
>
> Much thanks!
>
> Ken
>
>
> On Aug 18, 1:44 pm, "arv...@cloudera.com" <ar...@cloudera.com> wrote:
>> [Moving the conversation to Apache mailing list:
>> sqoop-u...@incubator.apache.org]
>>
>> Ken,
>>
>> Using CDH3U1 version of Hive and the latest sources from Sqoop trunk,
>> I was able to successfully import a table from MySQL containing a bit
>> field into Hive. I did this using defaults and only specifying the
>> --hive-import option.
>>
>> One thing you can look at to troubleshoot this further is the hive.log
>> file that gets generated under /tmp/${user}/ directory. This file
>> should identify if there are any exceptions during the load. You can
>> also look at the directory by the name of the table under
>> /user/hive/warehouse on HDFS to see the contents of the imported data.
>>
>> Thanks,
>> Arvind
>>
>>
>>
>>
>>
>>
>>
>> On Tue, Aug 16, 2011 at 1:12 PM, Ken <ke...@gmail.com> wrote:
>> > I have been trying to get a mysql table to import into hive using
>> > sqoop. I have tried many variations of fields-terminated-by/lines-
>> > terminated-by, mysql-delimiters, etc. however cannot get the bit
>> > fields to show up in hive. The bit fields always end up as NULL. I
>> > have tried both --direct and not. I have tried sqoop'ing it into HDFS
>> > and then moving it. I have tried --hive-import --hive-overwrite but
>> > nothing seems to work. What am I missing?
>>
>> > The bit fields get created in hive as boolean.
>>
>> > Any suggestions/pointer would be more helpful.
>>
>> > Much thanks.
>>
>> > --
>> > NOTE: The mailing list sqoop-u...@cloudera.org is deprecated in favor of Apache Sqoop mailing list sqoop-u...@incubator.apache.org. Please subscribe to it by sending an email to incubator-sqoop-user-subscr...@apache.org.
>
> --
> NOTE: The mailing list sqoop-user@cloudera.org is deprecated in favor of Apache Sqoop mailing list sqoop-user@incubator.apache.org. Please subscribe to it by sending an email to incubator-sqoop-user-subscribe@apache.org.
>