How to issue Apache Phoenix sqlline.py query with filter on UUID?

Question

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 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_TEN |
+------------+--------------+-------------------------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+-----------+
|            | SYSTEM       | CATALOG                       | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false     |
|            | SYSTEM       | FUNCTION                      | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false     |
|            | SYSTEM       | LOG                           | SYSTEM TABLE  |          |            |                            |                 |              | true            | 32            | false     |
|            | SYSTEM       | SEQUENCE                      | SYSTEM TABLE  |          |            |                            |                 |              | false           | 2             | false     |
|            | SYSTEM       | STATS                         | SYSTEM TABLE  |          |            |                            |                 |              | false           | null          | false     |
|            |              | CONTAINER_METRICS             | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | HOSTED_APPS_METADATA_UUID     | TABLE         |          |            |                            |                 |              | false           | null          | false     |
|            |              | INSTANCE_HOST_METADATA        | TABLE         |          |            |                            |                 |              | false           | null          | false     |
|            |              | METRICS_METADATA_UUID         | TABLE         |          |            |                            |                 |              | false           | null          | false     |
|            |              | METRIC_AGGREGATE_DAILY_UUID   | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | METRIC_AGGREGATE_HOURLY_UUID  | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | METRIC_AGGREGATE_MINUTE_UUID  | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | METRIC_AGGREGATE_UUID         | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | METRIC_RECORD_DAILY_UUID      | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | METRIC_RECORD_HOURLY_UUID     | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | METRIC_RECORD_MINUTE_UUID     | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | METRIC_RECORD_UUID            | TABLE         |          |            |                            |                 |              | true            | null          | false     |
|            |              | METRIC_TRANSIENT              | TABLE         |          |            |                            |                 |              | true            | null          | 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  | SQ |
+------------+--------------+----------------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----+
|            |              | HOSTED_APPS_METADATA_UUID  | HOSTNAME     | 12         | VARCHAR    | null         | null           | null            | null            | 0         |          |             | nu |
|            |              | HOSTED_APPS_METADATA_UUID  | UUID         | -2         | BINARY     | 4            | null           | null            | null            | 1         |          |             | nu |
|            |              | HOSTED_APPS_METADATA_UUID  | APP_IDS      | 12         | VARCHAR    | null         | null           | null            | null            | 1         |          |             | nu |
+------------+--------------+----------------------------+--------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+----------+-------------+----+

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@66e8997c  | hiveserver2,hivemetastore,resourcemanager,historyserver,HOST,applicationhistoryserver,jobhistoryserver,namenode,ams-hbase  |
| hn1-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net  | [B@6f0cb5a1  | hiveserver2,hivemetastore,resourcemanager,historyserver,HOST,namenode                                                      |
| wn0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net  | [B@1b29d52b  | datanode,HOST,nodemanager                                                                                                  |
| zk0-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net  | [B@47547132  | journalnode,HOST                                                                                                           |
| zk1-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net  | [B@655523dd  | journalnode,HOST                                                                                                           |
| zk3-gjshdi.5uu4vnfquqlelgdmoqysq1y3jg.cx.internal.cloudapp.net  | [B@6f7e336b  | 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 of the uuid value below. I think there's something syntactically wrong:

I've tried numerous variations and it's driving me nuts :)

select * from HOSTED_APPS_METADATA_UUID where UUID = [B@66e8997c;

...results in the following error:

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

Broader picture. I'm trying to take logging output from another application and create an isolated query in sqlline.py.

Here's an example of what this logging output looks like where the ? are replaced the values that follow:

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}

Above, 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 can't seem to figure out how to supply a query with these uuid and I keep getting the same LPAREN error whenever I try UUID anywhere in a query.

Driver version -> PhoenixEmbeddedDriver (version 5.0)

Any tips greatly appreciated!!

Answer 1

I used this query to find out if a certain metrics of a service been collected and saved to ams hbase and seems to work:

0: jdbc:phoenix:localhost:2181/ams-hbase-unse> select * from METRIC_AGGREGATE_UUID where UUID in (select UUID from METRICS_METADATA_UUID where APP_ID='datanode' and METRIC_NAME='mem_free');
+-------------+---------------+----------------------+-------------+----------------------+----------------------+
|    UUID     |  SERVER_TIME  |      METRIC_SUM      | HOSTS_COUNT |      METRIC_MAX      |      METRIC_MIN      |
+-------------+---------------+----------------------+-------------+----------------------+----------------------+
| [B@164dea80 | 1699082940000 | 1.203734E7           | 1           | 1.203734E7           | 1.203734E7           |
| [B@4392362c | 1699082970000 | 1.203734E7           | 1           | 1.203734E7           | 1.203734E7           |
| [B@338a4c61 | 1699083000000 | 1.203734E7           | 1           | 1.203734E7           | 1.203734E7           |
| [B@839755f  | 1699083030000 | 1.203734E7           | 1           | 1.203734E7           | 1.203734E7           |
| [B@71d99732 | 1699083060000 | 1.2034292E7          | 1           | 1.2034292E7          | 1.2034292E7          |

Answer 2

I checked on the Phoenix User forum and received the following answer. This explains why my queries never return any data from sqlline.py once I got past the LPARAM error using the single quotes.

Phoenix has only added the ability to specify a binary literal in 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.

Answer 3

Playing with this more, I'm assuming single quotes are needed around each UUID similar to below. I no longer get the LPARAM error - but expected data doesn't seem to be there. Probably missing something else.

SELECT UUID, SERVER_TIME, METRIC_SUM, METRIC_MAX, METRIC_MIN, METRIC_COUNT FROM METRIC_RECORD_HOURLY_UUID WHERE (UUID IN ('[B@70242f38')) ORDER BY UUID, SERVER_TIME;

+-------+--------------+-------------+-------------+-------------+---------------+
| UUID  | SERVER_TIME  | METRIC_SUM  | METRIC_MAX  | METRIC_MIN  | METRIC_COUNT  |
+-------+--------------+-------------+-------------+-------------+---------------+
+-------+--------------+-------------+-------------+-------------+---------------+
No rows selected (0.167 seconds)