question Phoenix Query Server does not return results (HDP 2.5) in Archives of Support Questions (Read Only)

Phoenix Query Server does not return results (HDP 2.5)

alexandre1 — Mon, 17 Oct 2016 18:38:15 GMT

I use HDP 2.5 on Azure (HDInsight Template : HDI 3.5)

When I query Phoenix with PQS with PhoenixDb + Python or curl, I got no results but this message Do you have any idea why ?

{"response":"executeResults","missingStatement":false,"rpcMetadata":{"response":"rpcMetadata","serverAddress":"xxx-xxxxxx:8765"},"results":[{"response":"resultSet","connectionId":"000000-0000-0000-00000000","statementId":1,"ownStatement":true,"signature":{"columns":[{"ordinal":0,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":0,"signed":true,"displaySize":40,"label":"COMPANY_ID","columnName":"COMPANY_ID","schemaName":"","precision":0,"scale":0,"tableName":"COMPANY","catalogName":"","type":{"type":"scalar","id":4,"name":"INTEGER","rep":"PRIMITIVE_INT"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.Integer"},{"ordinal":1,"autoIncrement":false,"caseSensitive":false,"searchable":true,"currency":false,"nullable":1,"signed":false,"displaySize":225,"label":"NAME","columnName":"NAME","schemaName":"","precision":225,"scale":0,"tableName":"COMPANY","catalogName":"","type":{"type":"scalar","id":12,"name":"VARCHAR","rep":"STRING"},"readOnly":true,"writable":false,"definitelyWritable":false,"columnClassName":"java.lang.String"}],"sql":null,"parameters":[],"cursorFactory":{"style":"LIST","clazz":null,"fieldNames":null},"statementType":null},"firstFrame":{"offset":0,"done":true,"rows":[]},"updateCount":-1,"rpcMetadata":{"response":"rpcMetadata","serverAddress":"xxx-xxxxx:8765"}}]}

CREATE TABLE Company (COMPANY_ID INTEGER PRIMARY KEY, NAME VARCHAR(225));

UPSERT INTO Company VALUES(1, 'Microsoft');

curl -XPOST -H 'request:{"request":"openConnection","connectionId":"000000-0000-0000-00000000"}' <URL> --user xxxxxxxx:xxxxxxxx --basic --insecure -v -d ''

curl -XPOST -H 'request:{"request":"createStatement","connectionId":"000000-0000-0000-00000000"}' <URL> --user xxxxxxxx:xxxxxxxx --basic --insecure -v -d ''

curl -XPOST -H 'request:{"request":"prepareAndExecute","connectionId":"000000-0000-0000-00000000","statementId": 1,"sql": "SELECT * FROM COMPANY","maxRowCount":-1}' <URL> --user xxxxxxxx:xxxxxxxx --basic --insecure -v -d ''

Re: Phoenix Query Server does not return results (HDP 2.5)

elserj — Mon, 17 Oct 2016 22:10:26 GMT

"firstFrame":{"offset":0,"done":true,"rows":[]}

Is showing that there were no results read from your query. Can you verify that the results are actually present in your table?

Also, I do not believe the author is maintaining the Python PhoenixDB library. Please refer to the official Apache Calcite Avatica documentation on how to use the JSON API: http://calcite.apache.org/avatica/docs/json_reference.html

Re: Phoenix Query Server does not return results (HDP 2.5)

alexandre1 — Tue, 18 Oct 2016 14:40:31 GMT

I don't think the problem is related to phoenixdb since i used curl also to check if it's a driver's problem I downgraded the version of HDI to 3.4 and it works again

I will try another upgrade later and will let you know if I have the problem again

Re: Phoenix Query Server does not return results (HDP 2.5)

elserj — Fri, 21 Oct 2016 23:22:09 GMT

Turns out this was a bug in the Phoenix Query Server (technically Apache Avatica): https://issues.apache.org/jira/browse/CALCITE-1458

The author of the Phoenix-Sharp (C# driver) https://github.com/Azure/hdinsight-phoenix-sharp had a similar problem to what you were saying. What happens is that a change in serialization was not made fully backwards compatible and the attribute which the phoenixdb driver was likely expected (I have not verified this) was no longer being serialized over the wire. As such, the server was sending the data back in an attribute the client was not aware existed.

I have a fix I'm putting up on CALCITE-1458 shortly which should address the issue without requiring a change in the client.