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 <user@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<mailto: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: st...@cloudera.com<mailto:st...@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] ________________________________ ________________________________
