Phoenix has only added the ability to specify a binary literal in
PHOENIX-6764 <https://issues.apache.org/jira/browse/PHOENIX-6764>, but that
is not available in any released version yet.
Currently the only way to specify binary strings in a query is via
PreparedStatment.setBytes() in JDBC.
It is not possible to specify them in the query text directly.
Also, *[B@4632cfc *doesn't even look like a valid UUID, more like a pointer
to a java object as generated by .toString(), so you may also have a
problem with inserting.
regards
Istvan
On Sat, Feb 4, 2023 at 9:07 AM Suaro Email <su...@live.com> wrote:
> Hi Phoenix Experts,
>
> I've never sent an email to an alias like this, so hope I'm doing the
> right thing.
> I'm working on a more complex query, but have my problem reduced to
> something simple. I'm hoping someone can help me with.
>
> Given the following tables (see HOSTED_APPS_METADATA_UUID in yellow below
> )
>
> !tables
>
> +------------+--------------+-------------------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+
> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE
> | REMARKS | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION |
> INDEX_STATE | IMMUTABLE_ROWS | SALT_BUCKETS | MULTI_TENANT |
> VIEW_STATEMENT | VIEW_TYPE | INDEX_TYPE | TRANSACTIONAL |
> IS_NAMESPACE_MAPPED |
>
> +------------+--------------+-------------------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+
> | | SYSTEM | CATALOG | SYSTEM TABLE
> | | | | |
> | false | null | false |
> | | | false | false |
> | | SYSTEM | FUNCTION | SYSTEM TABLE
> | | | | |
> | false | null | false |
> | | | false | false |
> | | SYSTEM | LOG | SYSTEM TABLE
> | | | | |
> | true | 32 | false |
> | | | false | false |
> | | SYSTEM | SEQUENCE | SYSTEM TABLE
> | | | | |
> | false | 2 | false |
> | | | false | false |
> | | SYSTEM | STATS | SYSTEM TABLE
> | | | | |
> | false | null | false |
> | | | false | false |
> | | | CONTAINER_METRICS | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | HOSTED_APPS_METADATA_UUID | TABLE
> | | | | |
> | false | null | false |
> | | | false | false |
> | | | INSTANCE_HOST_METADATA | TABLE
> | | | | |
> | false | null | false |
> | | | false | false |
> | | | METRICS_METADATA_UUID | TABLE
> | | | | |
> | false | null | false |
> | | | false | false |
> | | | METRIC_AGGREGATE_DAILY_UUID | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | METRIC_AGGREGATE_HOURLY_UUID | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | METRIC_AGGREGATE_MINUTE_UUID | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | METRIC_AGGREGATE_UUID | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | METRIC_RECORD_DAILY_UUID | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | METRIC_RECORD_HOURLY_UUID | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | METRIC_RECORD_MINUTE_UUID | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | METRIC_RECORD_UUID | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
> | | | METRIC_TRANSIENT | TABLE
> | | | | |
> | true | null | false |
> | | | false | false |
>
> +------------+--------------+-------------------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------------+------------+-------------+----------------+----------------------+
>
>
> !describe HOSTED_APPS_METADATA_UUID
>
>
> +------------+--------------+----------------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+-------------------+--------------------+-------------------+---------------+
> | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME |
> DATA_TYPE | TYPE_NAME | COLUMN_SIZE | BUFFER_LENGTH | DECIMAL_DIGITS |
> NUM_PREC_RADIX | NULLABLE | REMARKS | COLUMN_DEF | SQL_DATA_TYPE |
> SQL_DATETIME_SUB | CHAR_OCTET_LENGTH | ORDINAL_POSITION | IS_NULLABLE |
>
> +------------+--------------+----------------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+-------------------+--------------------+-------------------+---------------+
> | | | HOSTED_APPS_METADATA_UUID | HOSTNAME |
> 12 | VARCHAR | null | null | null |
> null | 0 | | | null |
> null | null | 1 | false |
> | | | HOSTED_APPS_METADATA_UUID | *UUID * |
> -2 | BINARY | 4 | null | null |
> null | 1 | | | null |
> null | null | 2 | true |
> | | | HOSTED_APPS_METADATA_UUID | APP_IDS |
> 12 | VARCHAR | null | null | null |
> null | 1 | | | null |
> null | null | 3 | true |
>
> +------------+--------------+----------------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----------------+-------------------+--------------------+-------------------+---------------+
>
> There is a column called UUID of BINARY type.
>
> I'm using sqlline.py to issue a query. Here's a simple one:
>
> *select * from HOSTED_APPS_METADATA_UUID;*
>
> +-----------------------------------------------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------+
> | HOSTNAME |
> UUID |
> APP_IDS |
>
> +-----------------------------------------------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------+
> | hn0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net |
> [B@4632cfc |
> hiveserver2,hivemetastore,resourcemanager,historyserver,HOST,applicationhistoryserver,jobhistoryserver,namenode,ams-hbase
> |
> | hn1-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net |
> [B@6e1f8469 |
> hiveserver2,hivemetastore,resourcemanager,historyserver,HOST,namenode
> |
> | wn0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net |
> [B@2e380628 | datanode,HOST,nodemanager
> |
> | zk0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net |
> [B@3b6c624 | journalnode,HOST
> |
> | zk1-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net |
> [B@1eaf1e62 | journalnode,HOST
> |
> | zk3-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net |
> [B@c81fd12 | journalnode,HOST
> |
>
> +-----------------------------------------------------------------+--------------+----------------------------------------------------------------------------------------------------------------------------+
>
>
>
> How can I setup a query to filter on one of the UUID values ?
>
> For example, I'd like to select just the first row from the output above
> but not sure about the formatting below:
>
> I've tried numerous variations and its driving me nuts :)
>
> *select * from HOSTED_APPS_METADATA_UUID where UUID = **[B@4632cfc**;*
>
> Error: ERROR 602 (42P00): Syntax error. Missing "LPAREN" at line 1, column
> 47. (state=42P00,code=602)
> org.apache.phoenix.exception.PhoenixParserException: ERROR 602 (42P00):
> Syntax error. Missing "LPAREN" at line 1, column 47.
> at
> org.apache.phoenix.exception.PhoenixParserException.newException(PhoenixParserException.java:33)
> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:111)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$PhoenixStatementParser.parseStatement(PhoenixStatement.java:1644)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1727)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1819)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:813)
> at sqlline.SqlLine.begin(SqlLine.java:686)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:291)
> Caused by: MissingTokenException(inserted [@-1,0:0='<missing
> LPAREN>',<100>,1:46] at UUID)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:374)
> at
> org.apache.phoenix.shaded.org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.not_expression(PhoenixSQLParser.java:7851)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.and_expression(PhoenixSQLParser.java:7671)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.or_expression(PhoenixSQLParser.java:7608)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.expression(PhoenixSQLParser.java:7573)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.single_select(PhoenixSQLParser.java:5192)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(PhoenixSQLParser.java:5274)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.select_node(PhoenixSQLParser.java:5340)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(PhoenixSQLParser.java:841)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:524)
> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
> ... 9 more
>
>
> Broder picture. I'm trying to take a query such as :
>
> SELECT UUID, SERVER_TIME, METRIC_SUM, HOSTS_COUNT, METRIC_MAX, METRIC_MIN
> FROM METRIC_AGGREGATE_UUID WHERE (UUID IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?)) AND SERVER_TIME >= ? AND SERVER_TIME < ? ORDER BY UUID,
> SERVER_TIME, condition => Condition{uuids=[[B@2948f779, [B@3ca87491,
> [B@6702e8f, [B@7a907628, [B@148da53c, [B@6bfe57c0, [B@6cfd0866,
> [B@15a512e7, [B@6c78f12a, [B@75a1eb32, [B@719b73d8, [B@4c946526,
> [B@67ce3c3f, [B@1dcca38f, [B@3763165d], appId='NODEMANAGER',
> instanceId='null', startTime=1675461194000, endTime=1675461510000,
> limit=null, grouped=true, orderBy=[], noLimit=true}
>
>
> and create an isolated test from sqlline.py
>
> The base query is:
>
> SELECT UUID, SERVER_TIME, METRIC_SUM, HOSTS_COUNT, METRIC_MAX, METRIC_MIN
> FROM METRIC_AGGREGATE_UUID WHERE (UUID IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?)) AND SERVER_TIME >= ? AND SERVER_TIME < ? ORDER BY UUID,
> SERVER_TIME
>
> and you can see the 15 UUID parameters that follow. I keep getting the
> same LPAREN error whenever I try UUID anywhere in a query.
>
>
> Any tips greatly appreciated!!
>
> THanks
>
> - Greg
>
>
>
>
--
*István Tóth* | Sr. Staff Software Engineer
*Email*: st...@cloudera.com
cloudera.com <https://www.cloudera.com>
[image: Cloudera] <https://www.cloudera.com/>
[image: Cloudera on Twitter] <https://twitter.com/cloudera> [image:
Cloudera on Facebook] <https://www.facebook.com/cloudera> [image: Cloudera
on LinkedIn] <https://www.linkedin.com/company/cloudera>
------------------------------
------------------------------
