question Re: Phoenix Error Description in Archives of Support Questions (Read Only)

Phoenix Error Description

bsaini — Tue, 06 Oct 2015 02:52:15 GMT

I am using phoenix “psql” utility to ingest some file data and it fails with many generic “ERROR 201 (22000): Illegal data” without more details. It is difficult to figure out which column data or datatype cause the failure.

Is there any way to get more details?

[root@dev HbaseData]# psql.py -d "|" localhost tgb_counter.csv

……

15/09/17 17:57:55 ERROR util.CSVCommonsLoader: Error upserting record [263, 1442437680, 1442437730, , 1442437703, 9, Moundville, UNKNOWN, Standard Outdoor TGB w/ Argus/Alpha Supply, 20150916, 20150916, 0, 0, 3, 0, 0, 18, 2, 3, 8, 0, 0, 18, 2, 3, 8, 0, 0, 0, 0, 0, 0, 61, 61, 61, 1442437716, 1442437806, ALOHA, 901162486, ALOHA/Double density reduced, ALOHA, 901112486, ALOHA/Normal, Poll Response/Priority, 901137486, Poll Response/Priority, ALOHA, 901187486, ALOHA/Double density reduced, , , , , , , , , , , , ]: java.sql.SQLException: ERROR 201 (22000): Illegal data.

15/09/17 17:57:55 ERROR util.CSVCommonsLoader: Error upserting record [263, 1442437860, 1442437913, , 1442437888, 10, Moundville, UNKNOWN, Standard Outdoor TGB w/ Argus/Alpha Supply, 20150916, 20150916, 5, 0, 1, 0, 0, 20, 6, 21, 11, 4, 0, 20, 6, 21, 11, 0, 0, 0, 0, 0, 0, 61, 61, 61, 1442437995, 1442438125, ALOHA, 901162486, ALOHA/Double density reduced, ALOHA, 901112486, ALOHA/Normal, Poll Response/Priority, 901137486, Poll Response/Priority, ALOHA, 901187486, ALOHA/Double density reduced, , , , , , , , , , , , ]: java.sql.SQLException: ERROR 201 (22000): Illegal data.

Re: Phoenix Error Description

awatson — Wed, 07 Oct 2015 03:00:18 GMT

Try loading your data into Phoenix via the MapReduce loader, I believe it provides better error logging - http://phoenix.apache.org/bulk_dataload.html

Re: Phoenix Error Description

rchintaguntla — Wed, 07 Oct 2015 17:04:26 GMT

This error will be thrown when an invalid or illegal data value for a columns is found. Please verify all the data as for the columns' data types'.

found there is an empty value for some column check whether it's expected or not?

1442437730, , 1442437703

1442437913, , 1442437888

Re: Phoenix Error Description

Jagatheeshr — Wed, 07 Oct 2015 17:11:05 GMT

@bsaini@hortonworks.com.

What i usually do in this case is to open the data file(CSV) and copy the row to problematic row excel sheet and also place the row which is inserting without any issues and compare these 2 rows on individual columns values against the datatype. its most likely either the datatype is not matching with the values are you are inserting a null / no value for a primary key column.

Re: Phoenix Error Description

vzlatkin — Mon, 08 Feb 2016 04:03:39 GMT

I also ran into this problem and it was painful to troubleshoot. Is there a JIRA to improve the error message?