You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by anil gupta <an...@gmail.com> on 2017/03/30 07:09:42 UTC
ERROR 201 (22000): Unable to load CSV files using CsvBulkLoadTool due
to blank columns
Hi,
We are trying load a simple CSV file to a table via CsvBulkLoadTool and
some of the columns have blank/null value. We are using HDP 2.3 and Phoenix
4.4. We are getting following error:
2017-03-29 21:53:34,106 ERROR [main]
org.apache.phoenix.mapreduce.CsvToKeyValueMapper: Error on record [45056,
82851, Both, Deal.com, 23, MODERNFHICKORY, '', '', Internal API, 2017-03-19
21:56:39.0, Tim , 2016-02-25 07:53:05.0]: java.sql.SQLException: ERROR 201
(22000): Illegal data.
Isn't there a way to handle blank or null value in input dataset while
using CsvBulkLoadTool? I have looked at documentation but i am unable to
find a solution for this.
--
Thanks & Regards,
Anil Gupta
Re: ERROR 201 (22000): Unable to load CSV files using CsvBulkLoadTool
due to blank columns
Posted by anil gupta <an...@gmail.com>.
I agree, It doesn't. Unfortunately, we have it for historical reasons.
And pig-phoenix integration treats '' as null for an Integer column so the
behavior of ingestion is varying.
On Thu, Mar 30, 2017 at 11:07 AM, Pedro Boado <pe...@gmail.com> wrote:
> It doesn't make a lot of sense having quotes in an integer column, does
> it?
>
> Maybe removing this quotes from the source would solve the problem.
>
> On 30 Mar 2017 18:43, "anil gupta" <an...@gmail.com> wrote:
>
>> Hi Brian,
>>
>> It seems like Phoenix is not liking ''(single quotes) in an integer
>> column. IMO, it will be better if phoenix can handle that by providing an
>> option in csvbulkloadtool to specify '' to be treated as null. Single
>> quotes works fine for varchar columns.
>>
>> Thanks,
>> Anil Gupta
>>
>> On Thu, Mar 30, 2017 at 4:15 AM, Brian Jeltema <bd...@gmail.com>
>> wrote:
>>
>>> The record in question
>>>
>>> 45056, 82851, Both, Deal.com, 23, MODERNFHICKORY, '', '', Internal
>>> API, 2017-03-19 21:56:39.0, Tim , 2016-02-25 07:53:05.0
>>>
>>> doesn’t appear to have a blank or null value. There are either two
>>> columns containing a single quote character, or more likely
>>> a column containinig a quoted comma which the parser treats a two fields.
>>>
>>> > On Mar 30, 2017, at 3:09 AM, anil gupta <an...@gmail.com> wrote:
>>> >
>>> > Hi,
>>> >
>>> > We are trying load a simple CSV file to a table via CsvBulkLoadTool
>>> and some of the columns have blank/null value. We are using HDP 2.3 and
>>> Phoenix 4.4. We are getting following error:
>>> > 2017-03-29 21:53:34,106 ERROR [main] org.apache.phoenix.mapreduce.CsvToKeyValueMapper:
>>> Error on record [45056, 82851, Both, Deal.com, 23, MODERNFHICKORY, '', '',
>>> Internal API, 2017-03-19 21:56:39.0, Tim , 2016-02-25 07:53:05.0]:
>>> java.sql.SQLException: ERROR 201 (22000): Illegal data.
>>> >
>>> > Isn't there a way to handle blank or null value in input dataset while
>>> using CsvBulkLoadTool? I have looked at documentation but i am unable to
>>> find a solution for this.
>>> >
>>> > --
>>> > Thanks & Regards,
>>> > Anil Gupta
>>>
>>>
>>
>>
>> --
>> Thanks & Regards,
>> Anil Gupta
>>
>
--
Thanks & Regards,
Anil Gupta
Re: ERROR 201 (22000): Unable to load CSV files using CsvBulkLoadTool
due to blank columns
Posted by Pedro Boado <pe...@gmail.com>.
It doesn't make a lot of sense having quotes in an integer column, does it?
Maybe removing this quotes from the source would solve the problem.
On 30 Mar 2017 18:43, "anil gupta" <an...@gmail.com> wrote:
> Hi Brian,
>
> It seems like Phoenix is not liking ''(single quotes) in an integer
> column. IMO, it will be better if phoenix can handle that by providing an
> option in csvbulkloadtool to specify '' to be treated as null. Single
> quotes works fine for varchar columns.
>
> Thanks,
> Anil Gupta
>
> On Thu, Mar 30, 2017 at 4:15 AM, Brian Jeltema <bd...@gmail.com>
> wrote:
>
>> The record in question
>>
>> 45056, 82851, Both, Deal.com, 23, MODERNFHICKORY, '', '', Internal
>> API, 2017-03-19 21:56:39.0, Tim , 2016-02-25 07:53:05.0
>>
>> doesn’t appear to have a blank or null value. There are either two
>> columns containing a single quote character, or more likely
>> a column containinig a quoted comma which the parser treats a two fields.
>>
>> > On Mar 30, 2017, at 3:09 AM, anil gupta <an...@gmail.com> wrote:
>> >
>> > Hi,
>> >
>> > We are trying load a simple CSV file to a table via CsvBulkLoadTool and
>> some of the columns have blank/null value. We are using HDP 2.3 and Phoenix
>> 4.4. We are getting following error:
>> > 2017-03-29 21:53:34,106 ERROR [main] org.apache.phoenix.mapreduce.CsvToKeyValueMapper:
>> Error on record [45056, 82851, Both, Deal.com, 23, MODERNFHICKORY, '', '',
>> Internal API, 2017-03-19 21:56:39.0, Tim , 2016-02-25 07:53:05.0]:
>> java.sql.SQLException: ERROR 201 (22000): Illegal data.
>> >
>> > Isn't there a way to handle blank or null value in input dataset while
>> using CsvBulkLoadTool? I have looked at documentation but i am unable to
>> find a solution for this.
>> >
>> > --
>> > Thanks & Regards,
>> > Anil Gupta
>>
>>
>
>
> --
> Thanks & Regards,
> Anil Gupta
>
Re: ERROR 201 (22000): Unable to load CSV files using CsvBulkLoadTool
due to blank columns
Posted by anil gupta <an...@gmail.com>.
Hi Brian,
It seems like Phoenix is not liking ''(single quotes) in an integer column.
IMO, it will be better if phoenix can handle that by providing an option in
csvbulkloadtool to specify '' to be treated as null. Single quotes works
fine for varchar columns.
Thanks,
Anil Gupta
On Thu, Mar 30, 2017 at 4:15 AM, Brian Jeltema <bd...@gmail.com> wrote:
> The record in question
>
> 45056, 82851, Both, Deal.com, 23, MODERNFHICKORY, '', '', Internal API,
> 2017-03-19 21:56:39.0, Tim , 2016-02-25 07:53:05.0
>
> doesn’t appear to have a blank or null value. There are either two columns
> containing a single quote character, or more likely
> a column containinig a quoted comma which the parser treats a two fields.
>
> > On Mar 30, 2017, at 3:09 AM, anil gupta <an...@gmail.com> wrote:
> >
> > Hi,
> >
> > We are trying load a simple CSV file to a table via CsvBulkLoadTool and
> some of the columns have blank/null value. We are using HDP 2.3 and Phoenix
> 4.4. We are getting following error:
> > 2017-03-29 21:53:34,106 ERROR [main] org.apache.phoenix.mapreduce.CsvToKeyValueMapper:
> Error on record [45056, 82851, Both, Deal.com, 23, MODERNFHICKORY, '', '',
> Internal API, 2017-03-19 21:56:39.0, Tim , 2016-02-25 07:53:05.0]:
> java.sql.SQLException: ERROR 201 (22000): Illegal data.
> >
> > Isn't there a way to handle blank or null value in input dataset while
> using CsvBulkLoadTool? I have looked at documentation but i am unable to
> find a solution for this.
> >
> > --
> > Thanks & Regards,
> > Anil Gupta
>
>
--
Thanks & Regards,
Anil Gupta
Re: ERROR 201 (22000): Unable to load CSV files using CsvBulkLoadTool
due to blank columns
Posted by Brian Jeltema <bd...@gmail.com>.
The record in question
45056, 82851, Both, Deal.com, 23, MODERNFHICKORY, '', '', Internal API, 2017-03-19 21:56:39.0, Tim , 2016-02-25 07:53:05.0
doesn’t appear to have a blank or null value. There are either two columns containing a single quote character, or more likely
a column containinig a quoted comma which the parser treats a two fields.
> On Mar 30, 2017, at 3:09 AM, anil gupta <an...@gmail.com> wrote:
>
> Hi,
>
> We are trying load a simple CSV file to a table via CsvBulkLoadTool and some of the columns have blank/null value. We are using HDP 2.3 and Phoenix 4.4. We are getting following error:
> 2017-03-29 21:53:34,106 ERROR [main] org.apache.phoenix.mapreduce.CsvToKeyValueMapper: Error on record [45056, 82851, Both, Deal.com, 23, MODERNFHICKORY, '', '', Internal API, 2017-03-19 21:56:39.0, Tim , 2016-02-25 07:53:05.0]: java.sql.SQLException: ERROR 201 (22000): Illegal data.
>
> Isn't there a way to handle blank or null value in input dataset while using CsvBulkLoadTool? I have looked at documentation but i am unable to find a solution for this.
>
> --
> Thanks & Regards,
> Anil Gupta