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
