You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by "Bulvik, Noam" <No...@teoco.com> on 2016/01/13 10:05:04 UTC
RE: array support issue
we have upgraded to the server to use the parcel and we created table with varchar array column
when working with client java client like squirrel we still get the same error (Error: org.apache.phoenix.schema.IllegalDataException: Unsupported sql type: VARCHAR ARRAY) and from sqlline.py it still works fine
any idea what else to check
From: Bulvik, Noam
Sent: Thursday, October 8, 2015 7:41 PM
To: 'user@phoenix.apache.org' <us...@phoenix.apache.org>
Subject: array support issue
Hi all,
We are using CDH 5.4 and phoenix 4.4. When we try to use the client jar (from squirrel ) to query table with array column we get the following error (even when doing simple thing like select <column> from <table with array>:
Error: org.apache.phoenix.schema.IllegalDataException: Unsupported sql type: VARCHAR ARRAY
SQLState: null
ErrorCode: 0
The same SQL from SQL command line (sqlline.py) it works fine (BTW only from phoenix 4.3.1 with 4.4 there is CDH compatibility issue .
Any idea how it can be fixed?
Regards ,
Noam
________________________________
PRIVILEGED AND CONFIDENTIAL
PLEASE NOTE: The information contained in this message is privileged and confidential, and is intended only for the use of the individual to whom it is addressed and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, or if any problems occur with transmission, please contact sender. Thank you.
Re: array support issue
Posted by James Taylor <ja...@apache.org>.
How can users know what to expect when they're using an undocumented,
unsupported, non public API?
On Thu, Mar 17, 2016 at 6:20 PM, Nick Dimiduk <nd...@gmail.com> wrote:
> > Applications should never query the SYSTEM.CATALOG directly. Instead
> they should go through the DatabaseMetaData interface from
> Connection.getMetaData().
>
> I may have this detail wrong, but the point remains: applications are
> getting an incorrect value, or misinterpreting the correct value they
> receive. From what I can see, this issue is unique to Phoenix.
>
> On Thursday, March 10, 2016, James Taylor <ja...@apache.org> wrote:
>
>> Applications should never query the SYSTEM.CATALOG directly. Instead they
>> should go through the DatabaseMetaData interface from
>> Connection.getMetaData(). For column type information, you'd use the
>> DatabaseMetaData#getColumn method[1] which would return the standard SQL
>> type for ARRAY in the "DATA_TYPE" or resultSet.getInt(5) and the type name
>> in the "TYPE_NAME" or resultSet.getString(6). For arrays, the String is of
>> the form "<element type name> ARRAY". The max length or precision would in
>> the "COLUMN_SIZE" or resultSet.getInt(7).
>>
>> If we do want query access to the system catalog table, we should
>> implement the INFORMATION_SCHEMA (PHOENIX-24) by creating a view on top of
>> the SYSTEM.CATALOG.
>>
>> Thanks,
>> James
>>
>>
>> [1]
>> https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
>>
>> On Thu, Mar 10, 2016 at 8:14 AM, Nick Dimiduk <nd...@gmail.com> wrote:
>>
>>> I believe I've seen something similar while interfacing between Apache
>>> NiFi and Phoenix. From the bit of debugging I did yesterday with my
>>> colleague, NiFi is querying the system table for schema information about
>>> the target table. My target table has a VARCHAR ARRAY column, which is
>>> reported as DATA_TYPE 2003. NiFi then provides this type number to the
>>> setObject method. Phoenix actually has no type registered to DATA_TYPE
>>> 2003. Perhaps Squirrel is doing something similar?
>>>
>>> I think Phoenix either needs a generic Array type registered to 2003
>>> that can dispatch to the appropriate PDataType implementation, or it needs
>>> to store the correct array DATA_TYPE number in the schema table. In this
>>> case, it should be 3000 (base array offset) + 12 (varchar) = 3012.
>>>
>>> On Wed, Jan 13, 2016 at 1:05 AM, Bulvik, Noam <No...@teoco.com>
>>> wrote:
>>>
>>>> we have upgraded to the server to use the parcel and we created table
>>>> with varchar array column
>>>> when working with client java client like squirrel we still get the
>>>> same error (Error: org.apache.phoenix.schema.IllegalDataException:
>>>> Unsupported sql type: VARCHAR ARRAY) and from sqlline.py it still works fine
>>>>
>>>>
>>>>
>>>> any idea what else to check
>>>>
>>>>
>>>>
>>>> *From:* Bulvik, Noam
>>>> *Sent:* Thursday, October 8, 2015 7:41 PM
>>>> *To:* 'user@phoenix.apache.org' <us...@phoenix.apache.org>
>>>> *Subject:* array support issue
>>>>
>>>>
>>>>
>>>> Hi all,
>>>>
>>>>
>>>>
>>>> We are using CDH 5.4 and phoenix 4.4. When we try to use the client jar
>>>> (from squirrel ) to query table with array column we get the following
>>>> error (even when doing simple thing like select <column> from <table with
>>>> array>:
>>>>
>>>>
>>>>
>>>> Error: org.apache.phoenix.schema.IllegalDataException: Unsupported sql
>>>> type: VARCHAR ARRAY
>>>>
>>>> SQLState: null
>>>>
>>>> ErrorCode: 0
>>>>
>>>>
>>>>
>>>> The same SQL from SQL command line (sqlline.py) it works fine (BTW only
>>>> from phoenix 4.3.1 with 4.4 there is CDH compatibility issue .
>>>>
>>>>
>>>>
>>>> Any idea how it can be fixed?
>>>>
>>>>
>>>>
>>>> Regards ,
>>>>
>>>> Noam
>>>>
>>>> ------------------------------
>>>>
>>>> PRIVILEGED AND CONFIDENTIAL
>>>> PLEASE NOTE: The information contained in this message is privileged
>>>> and confidential, and is intended only for the use of the individual to
>>>> whom it is addressed and others who have been specifically authorized to
>>>> receive it. If you are not the intended recipient, you are hereby notified
>>>> that any dissemination, distribution or copying of this communication is
>>>> strictly prohibited. If you have received this communication in error, or
>>>> if any problems occur with transmission, please contact sender. Thank you.
>>>>
>>>
>>>
>>
Re: array support issue
Posted by Nick Dimiduk <nd...@gmail.com>.
> Applications should never query the SYSTEM.CATALOG directly. Instead they
should go through the DatabaseMetaData interface from
Connection.getMetaData().
I may have this detail wrong, but the point remains: applications are
getting an incorrect value, or misinterpreting the correct value they
receive. From what I can see, this issue is unique to Phoenix.
On Thursday, March 10, 2016, James Taylor <ja...@apache.org> wrote:
> Applications should never query the SYSTEM.CATALOG directly. Instead they
> should go through the DatabaseMetaData interface from
> Connection.getMetaData(). For column type information, you'd use the
> DatabaseMetaData#getColumn method[1] which would return the standard SQL
> type for ARRAY in the "DATA_TYPE" or resultSet.getInt(5) and the type name
> in the "TYPE_NAME" or resultSet.getString(6). For arrays, the String is of
> the form "<element type name> ARRAY". The max length or precision would in
> the "COLUMN_SIZE" or resultSet.getInt(7).
>
> If we do want query access to the system catalog table, we should
> implement the INFORMATION_SCHEMA (PHOENIX-24) by creating a view on top of
> the SYSTEM.CATALOG.
>
> Thanks,
> James
>
>
> [1]
> https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
>
> On Thu, Mar 10, 2016 at 8:14 AM, Nick Dimiduk <ndimiduk@gmail.com
> <javascript:_e(%7B%7D,'cvml','ndimiduk@gmail.com');>> wrote:
>
>> I believe I've seen something similar while interfacing between Apache
>> NiFi and Phoenix. From the bit of debugging I did yesterday with my
>> colleague, NiFi is querying the system table for schema information about
>> the target table. My target table has a VARCHAR ARRAY column, which is
>> reported as DATA_TYPE 2003. NiFi then provides this type number to the
>> setObject method. Phoenix actually has no type registered to DATA_TYPE
>> 2003. Perhaps Squirrel is doing something similar?
>>
>> I think Phoenix either needs a generic Array type registered to 2003 that
>> can dispatch to the appropriate PDataType implementation, or it needs to
>> store the correct array DATA_TYPE number in the schema table. In this case,
>> it should be 3000 (base array offset) + 12 (varchar) = 3012.
>>
>> On Wed, Jan 13, 2016 at 1:05 AM, Bulvik, Noam <Noam.Bulvik@teoco.com
>> <javascript:_e(%7B%7D,'cvml','Noam.Bulvik@teoco.com');>> wrote:
>>
>>> we have upgraded to the server to use the parcel and we created table
>>> with varchar array column
>>> when working with client java client like squirrel we still get the
>>> same error (Error: org.apache.phoenix.schema.IllegalDataException:
>>> Unsupported sql type: VARCHAR ARRAY) and from sqlline.py it still works fine
>>>
>>>
>>>
>>> any idea what else to check
>>>
>>>
>>>
>>> *From:* Bulvik, Noam
>>> *Sent:* Thursday, October 8, 2015 7:41 PM
>>> *To:* 'user@phoenix.apache.org
>>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>' <
>>> user@phoenix.apache.org
>>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>>
>>> *Subject:* array support issue
>>>
>>>
>>>
>>> Hi all,
>>>
>>>
>>>
>>> We are using CDH 5.4 and phoenix 4.4. When we try to use the client jar
>>> (from squirrel ) to query table with array column we get the following
>>> error (even when doing simple thing like select <column> from <table with
>>> array>:
>>>
>>>
>>>
>>> Error: org.apache.phoenix.schema.IllegalDataException: Unsupported sql
>>> type: VARCHAR ARRAY
>>>
>>> SQLState: null
>>>
>>> ErrorCode: 0
>>>
>>>
>>>
>>> The same SQL from SQL command line (sqlline.py) it works fine (BTW only
>>> from phoenix 4.3.1 with 4.4 there is CDH compatibility issue .
>>>
>>>
>>>
>>> Any idea how it can be fixed?
>>>
>>>
>>>
>>> Regards ,
>>>
>>> Noam
>>>
>>> ------------------------------
>>>
>>> PRIVILEGED AND CONFIDENTIAL
>>> PLEASE NOTE: The information contained in this message is privileged and
>>> confidential, and is intended only for the use of the individual to whom it
>>> is addressed and others who have been specifically authorized to receive
>>> it. If you are not the intended recipient, you are hereby notified that any
>>> dissemination, distribution or copying of this communication is strictly
>>> prohibited. If you have received this communication in error, or if any
>>> problems occur with transmission, please contact sender. Thank you.
>>>
>>
>>
>
Re: array support issue
Posted by James Taylor <ja...@apache.org>.
Applications should never query the SYSTEM.CATALOG directly. Instead they
should go through the DatabaseMetaData interface from
Connection.getMetaData(). For column type information, you'd use the
DatabaseMetaData#getColumn method[1] which would return the standard SQL
type for ARRAY in the "DATA_TYPE" or resultSet.getInt(5) and the type name
in the "TYPE_NAME" or resultSet.getString(6). For arrays, the String is of
the form "<element type name> ARRAY". The max length or precision would in
the "COLUMN_SIZE" or resultSet.getInt(7).
If we do want query access to the system catalog table, we should implement
the INFORMATION_SCHEMA (PHOENIX-24) by creating a view on top of the
SYSTEM.CATALOG.
Thanks,
James
[1]
https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
On Thu, Mar 10, 2016 at 8:14 AM, Nick Dimiduk <nd...@gmail.com> wrote:
> I believe I've seen something similar while interfacing between Apache
> NiFi and Phoenix. From the bit of debugging I did yesterday with my
> colleague, NiFi is querying the system table for schema information about
> the target table. My target table has a VARCHAR ARRAY column, which is
> reported as DATA_TYPE 2003. NiFi then provides this type number to the
> setObject method. Phoenix actually has no type registered to DATA_TYPE
> 2003. Perhaps Squirrel is doing something similar?
>
> I think Phoenix either needs a generic Array type registered to 2003 that
> can dispatch to the appropriate PDataType implementation, or it needs to
> store the correct array DATA_TYPE number in the schema table. In this case,
> it should be 3000 (base array offset) + 12 (varchar) = 3012.
>
> On Wed, Jan 13, 2016 at 1:05 AM, Bulvik, Noam <No...@teoco.com>
> wrote:
>
>> we have upgraded to the server to use the parcel and we created table
>> with varchar array column
>> when working with client java client like squirrel we still get the same
>> error (Error: org.apache.phoenix.schema.IllegalDataException: Unsupported
>> sql type: VARCHAR ARRAY) and from sqlline.py it still works fine
>>
>>
>>
>> any idea what else to check
>>
>>
>>
>> *From:* Bulvik, Noam
>> *Sent:* Thursday, October 8, 2015 7:41 PM
>> *To:* 'user@phoenix.apache.org' <us...@phoenix.apache.org>
>> *Subject:* array support issue
>>
>>
>>
>> Hi all,
>>
>>
>>
>> We are using CDH 5.4 and phoenix 4.4. When we try to use the client jar
>> (from squirrel ) to query table with array column we get the following
>> error (even when doing simple thing like select <column> from <table with
>> array>:
>>
>>
>>
>> Error: org.apache.phoenix.schema.IllegalDataException: Unsupported sql
>> type: VARCHAR ARRAY
>>
>> SQLState: null
>>
>> ErrorCode: 0
>>
>>
>>
>> The same SQL from SQL command line (sqlline.py) it works fine (BTW only
>> from phoenix 4.3.1 with 4.4 there is CDH compatibility issue .
>>
>>
>>
>> Any idea how it can be fixed?
>>
>>
>>
>> Regards ,
>>
>> Noam
>>
>> ------------------------------
>>
>> PRIVILEGED AND CONFIDENTIAL
>> PLEASE NOTE: The information contained in this message is privileged and
>> confidential, and is intended only for the use of the individual to whom it
>> is addressed and others who have been specifically authorized to receive
>> it. If you are not the intended recipient, you are hereby notified that any
>> dissemination, distribution or copying of this communication is strictly
>> prohibited. If you have received this communication in error, or if any
>> problems occur with transmission, please contact sender. Thank you.
>>
>
>
Re: array support issue
Posted by Nick Dimiduk <nd...@gmail.com>.
I believe I've seen something similar while interfacing between Apache NiFi
and Phoenix. From the bit of debugging I did yesterday with my colleague,
NiFi is querying the system table for schema information about the target
table. My target table has a VARCHAR ARRAY column, which is reported as
DATA_TYPE 2003. NiFi then provides this type number to the setObject
method. Phoenix actually has no type registered to DATA_TYPE 2003. Perhaps
Squirrel is doing something similar?
I think Phoenix either needs a generic Array type registered to 2003 that
can dispatch to the appropriate PDataType implementation, or it needs to
store the correct array DATA_TYPE number in the schema table. In this case,
it should be 3000 (base array offset) + 12 (varchar) = 3012.
On Wed, Jan 13, 2016 at 1:05 AM, Bulvik, Noam <No...@teoco.com> wrote:
> we have upgraded to the server to use the parcel and we created table with
> varchar array column
> when working with client java client like squirrel we still get the same
> error (Error: org.apache.phoenix.schema.IllegalDataException: Unsupported
> sql type: VARCHAR ARRAY) and from sqlline.py it still works fine
>
>
>
> any idea what else to check
>
>
>
> *From:* Bulvik, Noam
> *Sent:* Thursday, October 8, 2015 7:41 PM
> *To:* 'user@phoenix.apache.org' <us...@phoenix.apache.org>
> *Subject:* array support issue
>
>
>
> Hi all,
>
>
>
> We are using CDH 5.4 and phoenix 4.4. When we try to use the client jar
> (from squirrel ) to query table with array column we get the following
> error (even when doing simple thing like select <column> from <table with
> array>:
>
>
>
> Error: org.apache.phoenix.schema.IllegalDataException: Unsupported sql
> type: VARCHAR ARRAY
>
> SQLState: null
>
> ErrorCode: 0
>
>
>
> The same SQL from SQL command line (sqlline.py) it works fine (BTW only
> from phoenix 4.3.1 with 4.4 there is CDH compatibility issue .
>
>
>
> Any idea how it can be fixed?
>
>
>
> Regards ,
>
> Noam
>
> ------------------------------
>
> PRIVILEGED AND CONFIDENTIAL
> PLEASE NOTE: The information contained in this message is privileged and
> confidential, and is intended only for the use of the individual to whom it
> is addressed and others who have been specifically authorized to receive
> it. If you are not the intended recipient, you are hereby notified that any
> dissemination, distribution or copying of this communication is strictly
> prohibited. If you have received this communication in error, or if any
> problems occur with transmission, please contact sender. Thank you.
>