It is very important for us to use "hint" in "Select" statements to improve the query performance, like using the secondary indexes. For large tables, it is not possibles/practical to always create fully covered secondary indexes. Thus, we rely on using "hint" to make sure secondary indexes are used. However, our test showed that the performance through ODBC is much worse than through JDBC thin driver, for the same "select" statements with "hint" to use the secondary indexes. Since they both go through the Phoenix Query Server (PQS), the PQS log was analyzed and it turned out that the "hint" part does not exist when the "select" requests come from ODBC driver. Requests from JDBC thin driver keep the "hint" part. After turning on the ODBC driver log, it is confirmed that the "hint" part is stripped out by the ODBC driver. Here is the ODBC driver log. JSON serialization format is used for readability, using PROTOBUF behaves the same. From log, it can be seen that, a "select" statement like this: select /*+ INDEX(c.more_xref_cad more_xref_acct_idx) */ * from c.more_xref_cad where cad_acct_id = 219980018 is stripped out of "hint" part when ODBC sends it out: SELECT * FROM c.more_xref_cad WHERE cad_acct_id = 219980018 ====ODBC Log =========== Mar 20 16:15:00.601 INFO 6380 Statement::SQLSetStmtAttrW: Attribute: SQL_ATTR_MAX_ROWS (1) ----Commnents: the original query passed in has "hint" Mar 20 16:15:00.602 INFO 6380 StatementState::InternalPrepare: Preparing query: select /*+ INDEX(c.more_xref_cad more_xref_acct_idx) */ * from c.more_xref_cad where cad_acct_id = 219980018 Mar 20 16:15:00.602 DEBUG 6380 RESTAction::HMDebugCallback: Infor type: CURLINFO_TEXT ..... Mar 20 16:15:00.802 DEBUG 6380 RESTAction::HMDebugCallback: Info data: Connected to localhost (127.0.0.1) port 8765 (#1) Mar 20 16:15:00.802 DEBUG 6380 RESTAction::HMDebugCallback: Infor type: CURLINFO_HEADER_OUT Mar 20 16:15:00.802 DEBUG 6380 RESTAction::HMDebugCallback: Info data: POST / HTTP/1.1 Host: localhost:8765 Content-Type: application/octet-stream Accept: text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2 User-Agent: Phoenix ODBC Connection: keep-alive Content-Length: 160 Mar 20 16:15:00.803 DEBUG 6380 RESTAction::HMDebugCallback: Infor type: CURLINFO_DATA_OUT ----Comments: the query generated and submited to PQS has no "hint" part. Mar 20 16:15:00.803 DEBUG 6380 RESTAction::HMDebugCallback: Info data: {"request":"prepare","connectionId":"2166b30f-1bf8-1f9d-309e-4009877a1a62","sql":"SELECT * FROM c.more_xref_cad WHERE cad_acct_id = 219980018","maxRowCount":-1} Mar 20 16:15:00.803 DEBUG 6380 RESTAction::HMDebugCallback: Infor type: CURLINFO_TEXT Mar 20 16:15:00.803 DEBUG 6380 RESTAction::HMDebugCallback: Info data: upload completely sent off: 160 out of 160 bytes =====End of ODBC log===== Does anyone know how to make ODBC driver keep the "hint" in the "select" statements? @Josh Elser, please help! Thanks. ... View more

We have HDP 2.5.3 deployed on RedHat Linux with Phoenix Query Server (PQS) in front of Phoenix+HBase. Our plan is to use ODBC on Windows Server through Phoenix Query Server to access HBase. Since we do NOT plan to use Kerberos yet, we plan to use Knox as the secure server to proxy the data flow, as the diagram below: We set up the Knox for PQS as described here, http://knox.apache.org/books/knox-0-11-0/user-guide.html#Avatica. Even though this user guide is for Knox 0.11 and HDP 2.5.3 uses Knox 0.9, according to @Josh Elser fix for https://issues.apache.org/jira/browse/KNOX-844, it should work for earlier Knox. The setup does work in our system. The traffic from ODBC hit PQS successfully. However, there is a problem, the PQS failed to parse the request because the http request coming out of ODBC is not urlencoded, but the content is urlencoded after Knox. After tracing all the calls, it seems that it is caused by ODBC driver sending out Avatica Http request with content-type set to "application/x-www-form-urlencoded”, even though the content is NOT urlencoded at all. But, when the request passes through Knox, it comes out as urlencoded. The PQS seems not able to parse urlencoded content. It seems to take the input as it is. In comparison, the jdbc thin driver sends out http request with content-type “application/octet-stream”, which seems to be more appropriate. Question: Is there any way to change the content type of ODBC driver to use content type “application/octet-stream” instead? Or is there anyway to tell Knox server not to urlencode the content even though the content type is "application/x-www-form-urlencoded”? Do prefer the first approach. We also need help on the following questions to use Ranger control: How can Knox pass the authenticated user to PQS along with the proxy-ed HTTP request? Then How can PQS impersonate as the authenticated user to call HBase so that Ranger can control the access by that user? Since PQS runs as "hbase" and it calls PQS as "hbase", ranger control cannot be used. Thanks for any help! ... View more

We have HDP 2.5.3 deployed on RedHat Linux with Phoenix Query Server (PQS) in front of Phoenix+HBase. Our plan is to use ODBC on Windows Server through Phoenix Query Server to access HBase. We are looking for a solution WITHOUT using Kerberos. We would like to turn on httpS on the PQS to secure the connection. The data flow would look like this. We tested that once the ODBC DSN is configured with a username and password, the request sent out from it to PQS does use HTTP Basic Authentication. We need help on: How to have PQS turn on HTTP Basic authentication to authenticate the calls from ODBC? How to turn on httpS on PQS to secure the connection? How to impersonate the calls from PQS to HBase with the authenticated user? By default, PQS calls HBase as user “hbase” because PQS runs under this user. We need to have PQS call HBase with the impersonated user so that Ranger can be used to control the access. This configuration for impersonation, http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.5.3/bk_security/content/kerb-config-secure-phoenix.html, does not seem to work without Kerberos. Are these above configurations possible? Thanks for any help! ... View more