This topic provides answers to frequently asked questions about how to use HBase SQL (Phoenix).

What JAR packages have been released in the latest version of ali-phoenix in the Maven central repository?

  • ali-phoenix 4.x dependencies:
    GroupIdArtifactIdLatest Version
    com.aliyun.phoenixali-phoenix4.12.0-AliHBase-1.1-0.9
    com.aliyun.phoenixali-phoenix-hive4.12.0-AliHBase-1.1-0.9
    com.aliyun.phoenixali-phoenix-spark4.12.0-AliHBase-1.1-0.9
    com.aliyun.phoenixali-phoenix-pherf4.12.0-AliHBase-1.1-0.9
    com.aliyun.phoenixali-phoenix-queryserver4.12.0-AliHBase-1.1-0.9
    com.aliyun.phoenixali-phoenix-queryserver-client4.12.0-AliHBase-1.1-0.9
    com.aliyun.phoenixali-phoenix-pig4.12.0-AliHBase-1.1-0.9
    com.aliyun.phoenixali-phoenix-flume4.12.0-AliHBase-1.1-0.9
    com.aliyun.phoenixali-phoenix-core4.12.0-AliHBase-1.1-0.9
  • ali-phoenix 5.x dependencies:
    GroupIdArtifactIdLatest Version
    com.aliyun.phoenixali-phoenix-shaded-thin-client5.2.5-HBase-2.x
    com.aliyun.phoenixali-phoenix-shaded-thin-client5.2.5-HBase-2.x
    Note We recommend that you use the shaded client to avoid incompatibility issues that may occur between client and third-party dependencies.

What is the format of the ali-phoenix JDBC connection string?

If Query Server is enabled, you must use a thin client. Otherwise, use a thick client. ali-phoenix does not allow you to use keytab to access ApsaraDB for HBase. Therefore, the JDBC connection string of ali-phoenix is different from the JDBC connection string of Apache Phoenix.
  • Thick client driver
    Connection string syntax
      jdbc:phoenix:[[ZK_HOST1:port, ZK_HOST2:port, ZK_HOST3:port | comma-separated ZooKeeper Quorum [:port] [:hbase root znode] ]
    Connection string example
    jdbc:phoenix:localhost   
jdbc:phoenix:localhost:123:/hbase  
jdbc:phoenix:v1,v2,v3:123:/hbase  
jdbc:phoenix:v1:2181,v2:2181,v3:2181:/hbase  
jdbc:phoenix:v1:2181,v2:2181,v3:2181
  • Thin client driver
    Connection string syntax
    jdbc:phoenix:thin:[key=value[;key=value...]]
    Connection string example
    jdbc:phoenix:thin:url=http://localhost:8765;serialization=PROTOBUF

Does ApsaraDB for HBase support Query Server?

  • ApsaraDB for HBase 1.x uses Phoenix 4.x thick clients. You must manually create a Query Server service.
  • ApsaraDB for HBase 2.0 supports the HBase SQL service. By default, Query Server is enabled for ApsaraDB for HBase 2.0.

Can web applications be traced?

No, ali-phoenix in ApsaraDB for HBase does not support tracing web applications. This feature will be available soon.

What do I do if secondary indexes time out when I create a synchronization task?

You must add the following configuration to your client for Phoenix 4.x in ApsaraDB for HBase 1.0, and then restart the client. 
<property>    
     <name>hbase.rpc.timeout</name>    
     <value>60000000</value>
</property>
<property>    
     <name>hbase.client.scanner.timeout.period</name>    
     <value>60000000</value>
</property>
<property>    
        <name>phoenix.query.timeoutMs</name>   
        <value>60000000</value>
</property>
For Phoenix 5.x in ApsaraDB for HBase 2.0, you can modify the preceding parameters in the ApsaraDB for HBase console, and then restart the HBase SQL service. Take note that you do not need to restart HBase.
Note If you adjust the timeout parameters in a Phoenix 5.x cluster, online access is affected. We recommend that you use a thick client or a MapReduce cluster to create an index. For more information, see Best practices for creating indexes in ApsaraDB Phoenix.

How do I enable namespace mapping?

  • Enable namespace mapping for ali-phoenix 4.x.
    1. Add the following configuration to the client: 
      <property>    
     <name>hbase.rpc.timeout</name>    
     <value>60000000</value>
</property>
<property>    
     <name>hbase.client.scanner.timeout.period</name>    
     <value>60000000</value>
</property>
<property>    
        <name>phoenix.query.timeoutMs</name>   
        <value>60000000</value>
</property>
    2. Contact the ApsaraDB for HBase Q&A group to enable this feature on the HBase server.
    3. Restart the HBase cluster.
  • Enable namespace mapping for ali-phoenix 5.x.
    1. Log on to the ApsaraDB for HBase console.
    2. Configure parameters.

      Set the phoenix.schema.isNamespaceMappingEnabled and phoenix.schema.mapSystemTablesToNamespace parameters to true.

    3. Restart the HBase cluster.
    4. Restart the HBase SQL service.
    Note You must configure these parameters on both the query server and HBase server. Take note that the descriptions of the parameters on the two servers are different.

Are connection pools supported?

It depends. Phoenix 4.12.0.Xsupports connection pools. For more information, see PhoenixConnectionPool.java.

Phoenix 5.x uses thin clients. You cannot use PhoenixConnectionPool.java to create connection pools. We recommend that you use third-party connection pools, such as MyBatis. For more information, see Connection pool demo.

Can I disconnect my client if it takes too much time to create indexes?

No, you must not disconnect your client. The procedure to create an index consists of two steps. In Step 1, the server synchronizes data from source tables to index tables. In Step 2, the client sends a request to change the state of the index tables to active. Typically, in Step 1, the client sends a request to the server, and then the server processes the request.

How much time does it take to create an index table and synchronize index data?

In most cases, it takes 5 to 20 minutes to create an index on 10 million rows of data. The amount of time required depends on the specification of your cluster and the number of computing resources.

What do I do if it takes too much time to create an index and my client connection is automatically closed?

An index table is created only after its state changes from building to active. You can run the !table command in SQLLine to query the index table state. To resolve this issue, try one of the following solutions:
  1. Execute the ALTER INDEX statement to rebuild the index.
  2. Delete the index table in the building state, set the client connection timeout time to a greater value, and rebuild the index.

How do I import data from a relational database to a Phoenix table in ApsaraDB for HBase?

Use DataX or CDP. for more information about DataX, visit https://github.com/alibaba/DataX.

What do I do if an error message ERROR 599(42912): Default enable Force index, please set phoenix.force.index=false to disable it...., is returned when I query data?

The system automatically checks whether the primary key columns or index columns are used as query conditions when it compiles SQL statements. This helps avoid full table scans. If no primary key or index column is specified as a query condition, this error is returned. If you need to use a column other than the primary key or index columns as a query condition, use the following methods to resolve this issue. For Phoenix 4.x, open the client configuration file hbase-site.xml and set phoenix.force.index to false. Then, restart the client. For Phoenix 5.x, set phoenix.force.index to false in the ApsaraDB for HBase console, and then restart the HBase SQL service.

What do I do if an error message java.sql.SQLException: java.lang.IllegalArgumentException: Connection is null or closed is returned when I use a Druid connection pool in Spring Boot?

Phoenix caches connection information. If you add these connections to a connection pool, a connection is not removed from the pool when the connection is closed. As a result, this error message is returned when the system wants to reuse the closed connection. Therefore, we recommend that you do not use a Druid connection pool.

When I use an idle connection established between a Python client and a query server to query data, what do I do if the following error message is returned:phoenixdb.errors.InternalError: ('', None, None, None)

Open source Python clients do not support re-establishing connections that have timed out. If you send requests to query servers over an SLB connection that has timed out, this error message is returned.
  1. Download the Alibaba Cloud phoenix-python client.
  2. If you have already installed the phoenix-python driver, run the following command to remove it: 
    rm -rf /usr/lib/python${version}/site-packages/phoenixdb*
  3. Decompress the downloaded package, navigate to the phoenixdb directory, and run the following command to install the driver: 
    python setup.py install

Does Phoenix support multitenancy?

No, Phoenix does not support multi-tenancy. Commands such as grant and revoke are not supported.