Phoenix: Error connecting through Java

[#1]

<code>

            package com.test.utility;
            import java.sql.Connection;
            import java.sql.DriverManager;
            import java.sql.ResultSet;
            import java.sql.SQLException;
            import java.sql.Statement;

            public class EHPhoenix {

                static final String JDBC_DRIVER = "org.apache.phoenix.jdbc.PhoenixDriver";
                static final String ZOOKEPER = "trnsyxsvr:2181";
                static final String SECURITYTYPE = "/hbase-secure:phtest@HDP_DEV.COM";
                static final String KEYTAB = "/home/phtest/phtest.headless.keytab";
                static final String DB_URL = "jdbc:phoenix:" + ZOOKEPER + ":" + SECURITYTYPE + ":" + KEYTAB;

                public static void main(String[] args) {
                    Connection conn = null;
                    Statement st = null;

                System.out.println(DB_URL + ";");
                    try {
                        Class.forName("org.apache.phoenix.jdbc.PhoenixDriver");

                        System.out.println("Connecting to database..");
                        conn = DriverManager.getConnection(DB_URL);
                        System.out.println("Creating statement...");
                        st = conn.createStatement();
                        String sql;
                        sql = "SELECT * FROM collector_status";
                        ResultSet rs = st.executeQuery(sql);

                        while (rs.next()) {
                            String did = rs.getString(1);
                            System.out.println("Did found: " + did);
                        }
                        rs.close();
                        st.close();
                        conn.close();

                    }
                    catch (SQLException se) {
                        se.printStackTrace();
                    }
                    catch (Exception e) {
                        // Handle errors for Class.forName
                        e.printStackTrace();
                    }
                    finally {
                        // finally block used to close resources
                        try {
                            if (st != null)
                                st.close();
                        }
                        catch (SQLException se2) {
                        } // nothing we can do
                        try {
                            if (conn != null)
                                conn.close();
                        }
                        catch (SQLException se) {
                            se.printStackTrace();
                        }
                    }
                    System.out.println("Goodbye!");
                }
            }

<code>Gives the following error when running it from Eclipse IDE. Can you please advise?

<code>    jdbc:phoenix:trnsyxsvr:2181:/hbase-secure:phtest@HDP_DEV.COM:/home/phtest/phtest.headless.keytab;
            log4j:WARN No appenders could be found for logger (org.apache.hadoop.util.Shell).
            log4j:WARN Please initialize the log4j system properly.
            log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
            Connecting to database..
            java.sql.SQLException: org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=36, exceptions:
            Thu May 04 03:19:36 EDT 2017, null, java.net.SocketTimeoutException: callTimeout=60000, callDuration=73361: row 'SYSTEM:CATALOG,,' on table 'hbase:meta' at region=hbase:meta,,1.1588230740, hostname=trnsyxsvr,16020,1493844289998, seqNum=0

                at org.apache.phoenix.query.ConnectionQueryServicesImpl$12.call(ConnectionQueryServicesImpl.java:2465)
                at org.apache.phoenix.query.ConnectionQueryServicesImpl$12.call(ConnectionQueryServicesImpl.java:2382)
                at org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:76)
                at org.apache.phoenix.query.ConnectionQueryServicesImpl.init(ConnectionQueryServicesImpl.java:2382)
                at org.apache.phoenix.jdbc.PhoenixDriver.getConnectionQueryServices(PhoenixDriver.java:255)
                at org.apache.phoenix.jdbc.PhoenixEmbeddedDriver.createConnection(PhoenixEmbeddedDriver.java:149)
                at org.apache.phoenix.jdbc.PhoenixDriver.connect(PhoenixDriver.java:221)
                at java.sql.DriverManager.getConnection(DriverManager.java:664)
                at java.sql.DriverManager.getConnection(DriverManager.java:270)
                at com.cox.util.EHPhoenix.main(EHPhoenix.java:27)
            Caused by: org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=36, exceptions:
            Thu May 04 03:19:36 EDT 2017, null, java.net.SocketTimeoutException: callTimeout=60000, callDuration=73361: row 'SYSTEM:CATALOG,,' on table 'hbase:meta' at region=hbase:meta,,1.1588230740, hostname=trnsyxsvr,16020,1493844289998, seqNum=0

                at org.apache.hadoop.hbase.client.RpcRetryingCallerWithReadReplicas.throwEnrichedException(RpcRetryingCallerWithReadReplicas.java:271)
                at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas.call(ScannerCallableWithReplicas.java:210)
                at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas.call(ScannerCallableWithReplicas.java:60)
                at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithoutRetries(RpcRetryingCaller.java:210)
                at org.apache.hadoop.hbase.client.ClientScanner.call(ClientScanner.java:327)
                at org.apache.hadoop.hbase.client.ClientScanner.nextScanner(ClientScanner.java:302)
                at org.apache.hadoop.hbase.client.ClientScanner.initializeScannerInConstruction(ClientScanner.java:167)
                at org.apache.hadoop.hbase.client.ClientScanner.<init>(ClientScanner.java:162)
                at org.apache.hadoop.hbase.client.HTable.getScanner(HTable.java:797)
                at org.apache.hadoop.hbase.MetaTableAccessor.fullScan(MetaTableAccessor.java:602)
                at org.apache.hadoop.hbase.MetaTableAccessor.tableExists(MetaTableAccessor.java:366)
                at org.apache.hadoop.hbase.client.HBaseAdmin.tableExists(HBaseAdmin.java:403)
                at org.apache.phoenix.query.ConnectionQueryServicesImpl$12.call(ConnectionQueryServicesImpl.java:2410)
                ... 9 more
            Caused by: java.net.SocketTimeoutException: callTimeout=60000, callDuration=73361: row 'SYSTEM:CATALOG,,' on table 'hbase:meta' at region=hbase:meta,,1.1588230740, hostname=trnsyxsvr,16020,1493844289998, seqNum=0
                at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:169)
                at org.apache.hadoop.hbase.client.ResultBoundedCompletionService$QueueingFuture.run(ResultBoundedCompletionService.java:65)
                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
                at java.lang.Thread.run(Thread.java:745)
            Caused by: org.apache.hadoop.hbase.exceptions.ConnectionClosingException: Call to trnsyxsvr/210.220.3.139:16020 failed on local exception: org.apache.hadoop.hbase.exceptions.ConnectionClosingException: Connection to trnsyxsvr/210.220.3.139:16020 is closing. Call id=11, waitTime=189
                at org.apache.hadoop.hbase.ipc.AbstractRpcClient.wrapException(AbstractRpcClient.java:281)
                at org.apache.hadoop.hbase.ipc.RpcClientImpl.call(RpcClientImpl.java:1238)
                at org.apache.hadoop.hbase.ipc.AbstractRpcClient.callBlockingMethod(AbstractRpcClient.java:223)
                at org.apache.hadoop.hbase.ipc.AbstractRpcClient$BlockingRpcChannelImplementation.callBlockingMethod(AbstractRpcClient.java:328)
                at org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$BlockingStub.scan(ClientProtos.java:32831)
                at org.apache.hadoop.hbase.client.ScannerCallable.openScanner(ScannerCallable.java:379)
                at org.apache.hadoop.hbase.client.ScannerCallable.call(ScannerCallable.java:201)
                at org.apache.hadoop.hbase.client.ScannerCallable.call(ScannerCallable.java:63)
                at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithoutRetries(RpcRetryingCaller.java:210)
                at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas$RetryingRPC.call(ScannerCallableWithReplicas.java:364)
                at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas$RetryingRPC.call(ScannerCallableWithReplicas.java:338)
                at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:136)
                ... 4 more
            Caused by: org.apache.hadoop.hbase.exceptions.ConnectionClosingException: Connection to trnsyxsvr/210.220.3.139:16020 is closing. Call id=11, waitTime=189
                at org.apache.hadoop.hbase.ipc.RpcClientImpl$Connection.cleanupCalls(RpcClientImpl.java:1057)
                at org.apache.hadoop.hbase.ipc.RpcClientImpl$Connection.close(RpcClientImpl.java:856)
                at org.apache.hadoop.hbase.ipc.RpcClientImpl$Connection.run(RpcClientImpl.java:575)
            Goodbye!

[#2 · Accepted solution]

Include the directory containing hbase-site.xml on your application's classpath. Phoenix relies on HBase RPCs, and the error is commonly a sign that your client is trying to initiate unsecure RPCs while HBase is expecting Kerberos authentication.

[#3]

That was it. Thanks much!

[#4]

What is the correction we have done 

[#5]

Query Timeout exception, when running phoenix queries using JDBC

HDP Version : 2.6.1

Pheonix : 4.7.0 (Inbuilt in HDP 2.6.1)

We have one phoenix table which is taking approximately 95 sec to do sum/count , issue is same in sqlline and Java program (JDBC )

This is what we did,

1. Increased phoenix query timeout to 3 mins

2. Increased hbase rpc time out to 3 mins

After the above change queries are running fine in sqlline, (this is how I know it's taking 95sec app), but the issue is still the same when I run using a Java program. I have tried what @Josh Elser said and it worked. Now the challenge is we wouldn't want the properties to be overwritten from the client side. It will be helpful if we can find a way from where phoenix is getting these default properties, so that we can push the updated config files.

Any help much appreciated...!, missed any details please let me know.

Exception in thread "main" org.apache.phoenix.exception.PhoenixIOException: org.apache.phoenix.exception.PhoenixIOException: Failed after attempts=36, exceptions:
Tue Jul 04 15:24:44 BST 2017, null, java.net.SocketTimeoutException: callTimeout=60000, callDuration=60305: row ' on table 'tablexyz' at region=tablexyz,\x00\x80\x00\x01R\xCF\x89|@\x00\x00\x00\x004103\x001645\x001\x008,1499089548608.14418aca90d9c564e131beda51afb205., hostname=abc.mn.ak.corp,16020,1499177694345, seqNum=1904864


	at org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:111)
	at org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:771)
	at org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:714)
	at org.apache.phoenix.iterate.ConcatResultIterator.getIterators(ConcatResultIterator.java:50)
	at org.apache.phoenix.iterate.ConcatResultIterator.currentIterator(ConcatResultIterator.java:97)
	at org.apache.phoenix.iterate.ConcatResultIterator.next(ConcatResultIterator.java:117)
	at org.apache.phoenix.iterate.BaseGroupedAggregatingResultIterator.next(BaseGroupedAggregatingResultIterator.java:64)
	at org.apache.phoenix.iterate.UngroupedAggregatingResultIterator.next(UngroupedAggregatingResultIterator.java:39)
	at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:778)
	at com.test.PhoenixJDBC.main(PhoenixJDBC.java:30)
Caused by: java.util.concurrent.ExecutionException: org.apache.phoenix.exception.PhoenixIOException: Failed after attempts=36, exceptions:
Tue Jul 04 15:24:44 BST 2017, null, java.net.SocketTimeoutException: callTimeout=60000, callDuration=60305: row 'n table 'tablexyz' at region=tablexyz,\x00\x80\x00\x01R\xCF\x89|@\x00\x00\x00\x004103\x001645\x001\x008,1499089548608.14418aca90d9c564e131beda51afb205., hostname=abc.mn.ak.corp,16020,1499177694345, seqNum=1904864


	at java.util.concurrent.FutureTask.report(FutureTask.java:122)
	at java.util.concurrent.FutureTask.get(FutureTask.java:206)
	at org.apache.phoenix.iterate.BaseResultIterators.getIterators(BaseResultIterators.java:766)
	... 8 more
Caused by: org.apache.phoenix.exception.PhoenixIOException: Failed after attempts=36, exceptions:
Tue Jul 04 15:24:44 BST 2017, null, java.net.SocketTimeoutException: callTimeout=60000, callDuration=60305: row 'on table 'tablexyz' at region=tablexyz,\x00\x80\x00\x01R\xCF\x89|@\x00\x00\x00\x004103\x001645\x001\x008,1499089548608.14418aca90d9c564e131beda51afb205., hostname=abc.mn.ak.corp,16020,1499177694345, seqNum=1904864


	at org.apache.phoenix.util.ServerUtil.parseServerException(ServerUtil.java:111)
	at org.apache.phoenix.iterate.TableResultIterator.initScanner(TableResultIterator.java:203)
	at org.apache.phoenix.iterate.ParallelIterators$1.call(ParallelIterators.java:108)
	at org.apache.phoenix.iterate.ParallelIterators$1.call(ParallelIterators.java:103)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at org.apache.phoenix.job.JobManager$InstrumentedJobFutureTask.run(JobManager.java:183)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=36, exceptions:
Tue Jul 04 15:24:44 BST 2017, null, java.net.SocketTimeoutException: callTimeout=60000, callDuration=60305: row 'on table 'tablexyz' at region=tablexyz,\x00\x80\x00\x01R\xCF\x89|@\x00\x00\x00\x004103\x001645\x001\x008,1499089548608.14418aca90d9c564e131beda51afb205., hostname=abc.mn.ak.corp,16020,1499177694345, seqNum=1904864


	at org.apache.hadoop.hbase.client.RpcRetryingCallerWithReadReplicas.throwEnrichedException(RpcRetryingCallerWithReadReplicas.java:271)
	at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas.call(ScannerCallableWithReplicas.java:210)
	at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas.call(ScannerCallableWithReplicas.java:60)
	at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithoutRetries(RpcRetryingCaller.java:200)
	at org.apache.hadoop.hbase.client.ClientScanner.call(ClientScanner.java:327)
	at org.apache.hadoop.hbase.client.ClientScanner.nextScanner(ClientScanner.java:302)
	at org.apache.hadoop.hbase.client.ClientScanner.initializeScannerInConstruction(ClientScanner.java:167)
	at org.apache.hadoop.hbase.client.ClientScanner.<init>(ClientScanner.java:162)
	at org.apache.hadoop.hbase.client.HTable.getScanner(HTable.java:794)
	at org.apache.phoenix.iterate.TableResultIterator.initScanner(TableResultIterator.java:199)
	... 7 more
Caused by: java.net.SocketTimeoutException: callTimeout=60000, callDuration=60305: row 'on table 'tablexyz' at region=tablexyz,\x00\x80\x00\x01R\xCF\x89|@\x00\x00\x00\x004103\x001645\x001\x008,1499089548608.14418aca90d9c564e131beda51afb205., hostname=abc.mn.ak.corp,16020,1499177694345, seqNum=1904864
	at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:159)
	at org.apache.hadoop.hbase.client.ResultBoundedCompletionService$QueueingFuture.run(ResultBoundedCompletionService.java:65)
	... 3 more
Caused by: java.io.IOException: Call to abc.mn.ak.corp/877.445.27.87:16020 failed on local exception: org.apache.hadoop.hbase.ipc.CallTimeoutException: Call id=70, waitTime=60001, operationTimeout=60000 expired.
	at org.apache.hadoop.hbase.ipc.RpcClientImpl.wrapException(RpcClientImpl.java:1261)
	at org.apache.hadoop.hbase.ipc.RpcClientImpl.call(RpcClientImpl.java:1229)
	at org.apache.hadoop.hbase.ipc.AbstractRpcClient.callBlockingMethod(AbstractRpcClient.java:213)
	at org.apache.hadoop.hbase.ipc.AbstractRpcClient$BlockingRpcChannelImplementation.callBlockingMethod(AbstractRpcClient.java:287)
	at org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$BlockingStub.scan(ClientProtos.java:32831)
	at org.apache.hadoop.hbase.client.ScannerCallable.openScanner(ScannerCallable.java:379)
	at org.apache.hadoop.hbase.client.ScannerCallable.call(ScannerCallable.java:201)
	at org.apache.hadoop.hbase.client.ScannerCallable.call(ScannerCallable.java:63)
	at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithoutRetries(RpcRetryingCaller.java:200)
	at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas$RetryingRPC.call(ScannerCallableWithReplicas.java:364)
	at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas$RetryingRPC.call(ScannerCallableWithReplicas.java:338)
	at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:126)
	... 4 more
Caused by: org.apache.hadoop.hbase.ipc.CallTimeoutException: Call id=70, waitTime=60001, operationTimeout=60000 expired.
	at org.apache.hadoop.hbase.ipc.Call.checkAndSetTimeout(Call.java:70)
	at org.apache.hadoop.hbase.ipc.RpcClientImpl.call(RpcClientImpl.java:1203)
	... 14 more