question Re: How do I split columns on table that has varchar datatypes only? in Archives of Support Questions (Read Only)

How do I split columns on table that has varchar datatypes only?

dcedielunesa — Sat, 24 Mar 2018 09:39:37 GMT

Re: How do I split columns on table that has varchar datatypes only?

RahulSoni — Sun, 25 Mar 2018 05:10:37 GMT

If you are talking about a Sqoop Import, that's the only tag with your question :), it is always highly recommended to use an Integral column as split-by. But since you have only string/varchar columns in your data source, you can try following options based on the data that you have.

add surrogate int PK and use it also as a split or
split your data manually using a custom query with WHERE clause and run sqoop few times with num-mappers=1, or
apply some deterministic Integer non-aggregation function to you varchar column, for example cast(substr(...) as int) as split-column.

Let know if you need any other help!

Re: How do I split columns on table that has varchar datatypes only?

RahulSoni — Tue, 27 Mar 2018 07:32:20 GMT

@Christian Lunesa

If the answer helped solving your query, please mark the answer as accepted 🙂

Re: How do I split columns on table that has varchar datatypes only?

dcedielunesa — Tue, 27 Mar 2018 16:06:02 GMT

thank you for answering my questions sir, do you mean I have to alter the table in mysql when adding surrogate int PK?

Re: How do I split columns on table that has varchar datatypes only?

elkrish — Sun, 08 Jul 2018 11:04:02 GMT

I want to use the third option on the split-by but i am not seeing example of how to pass in that value. I have tried with single quote 'cast(id as unsigned)' and it throws error saying column not found on the sqoop run.