You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Suaro Email <su...@live.com> on 2023/02/04 08:07:16 UTC
How to issue query with filter on UUID ?
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
Re: How to issue query with filter on UUID ?
Posted by Suaro Email <su...@live.com>.
Thank you Istvan.
Get Outlook for iOS<https://aka.ms/o0ukef>
________________________________
From: Istvan Toth <st...@cloudera.com>
Sent: Monday, February 6, 2023 3:33:21 AM
To: user@phoenix.apache.org <us...@phoenix.apache.org>
Subject: Re: How to issue query with filter on UUID ?
Phoenix has only added the ability to specify a binary literal in PHOENIX-6764<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fissues.apache.org%2Fjira%2Fbrowse%2FPHOENIX-6764&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=o6Rh4dzqwA0QIp8StCB0URVcllsqkYs2BQJZHrOBBus%3D&reserved=0>, 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<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fhn0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net%2F&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=a5UO%2BXeGAQnno1OG4KIgzq0yEUydhNIny5NZIBU987Q%3D&reserved=0> | [B@4632cfc | hiveserver2,hivemetastore,resourcemanager,historyserver,HOST,applicationhistoryserver,jobhistoryserver,namenode,ams-hbase |
| hn1-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fhn1-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net%2F&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=YaZ2o3nPLBBCGLMWfmFdakR2foWSC3r9DCPOY8vSvFM%3D&reserved=0> | [B@6e1f8469 | hiveserver2,hivemetastore,resourcemanager,historyserver,HOST,namenode |
| wn0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwn0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net%2F&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=YhBDm%2B7%2ByFKBNh0SG9Z%2BjyyYZiQ3TD3ujZExlekcN%2B8%3D&reserved=0> | [B@2e380628 | datanode,HOST,nodemanager |
| zk0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fzk0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net%2F&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=jlcKiGf0HA4qN9l0VJq94uLA7jN7YHsDDjumgve4sxM%3D&reserved=0> | [B@3b6c624 | journalnode,HOST |
| zk1-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fzk1-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net%2F&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=h4mf0diIEjpFBTamVke0CNz9%2FpLwyhAnea5Hvt3coDo%3D&reserved=0> | [B@1eaf1e62 | journalnode,HOST |
| zk3-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net<https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fzk3-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net%2F&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=I6pypqXJiGCmq8cowTqo4r%2FDfD1SLTS9yyYaUTE7yk8%3D&reserved=0> | [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: stoty@cloudera.com<ma...@cloudera.com>
cloudera.com<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.cloudera.com%2F&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=EJKbbPFKFVufwxREQFwNOSHQmMpajHfiLSS3rH5dUWQ%3D&reserved=0>
[Cloudera]<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.cloudera.com%2F&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=EJKbbPFKFVufwxREQFwNOSHQmMpajHfiLSS3rH5dUWQ%3D&reserved=0>
[Cloudera on Twitter]<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fcloudera&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=LahU5X3Y2RZ%2FQ7nnzZ8HiSjvubi5xafslePcQg8XTIY%3D&reserved=0> [Cloudera on Facebook] <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2Fcloudera&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=KR3iHo3E%2B%2FBi56aKewzjhWb2CFgyZNj6I9lnyDzUm%2B4%3D&reserved=0> [Cloudera on LinkedIn] <https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fcloudera&data=05%7C01%7C%7Cdc85535f02ba446e6ab608db081cdbc8%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638112692275932219%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=XS%2FzpdUyQoz1D0v7Xg701wAgJxp%2BtZTSgS%2FqOeA9khU%3D&reserved=0>
[https://www.cloudera.com/content/dam/www/marketing/brand/email-signature/2022-cldr-buffer.png]
________________________________
________________________________
Re: How to issue query with filter on UUID ?
Posted by Istvan Toth <st...@cloudera.com>.
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*: stoty@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>
------------------------------
------------------------------