Logging in Phoenix

Vladimir Adamenkov

Overview

Apache Phoenix has an option to log the executed SELECT statements, saving them to the SYSTEM.LOG table. You can specify the logging verbosity level and the average number of queries logged. Also, you can add the Phoenix Query Server log information to the HBase logs.

Set up query logging

To turn on the query logging in Phoenix, go to the ADCM UI and do the following:

  1. Go to Clusters and select your cluster.

  2. Open the Services tab and select the HBase service.

  3. Turn on the Show advanced switch and expand the Custom hbase-site.xml section.

  4. Click Add property and enter phoenix.log.level for the field name. For the field value, enter one of the following:

    • OFF — no queries will be logged (default);

    • INFO — basic details of the queries will be logged;

    • DEBUG — verbose details of the queries will be logged;

    • TRACE — verbose details and the bind parameters of the queries will be logged.

  5. Click Add property and enter phoenix.log.sample.rate for the field name. For the field value, specify the numeric representation of the query logging probability from 0.0 to 1.0. For example, 0.5 means approximately half of the executed SELECT queries will be logged. Default value is 1.0 (all queries).

  6. Save the configuration by clicking Save → Create and restart the service by clicking Actions → Reconfig and graceful restart.

  7. If the Phoenix shell was loaded at this point, exit it and load it back again for the changes to take effect.

  8. If necessary, grant the write priviliges for the SYSTEM.LOG table to users whose queries need to be logged using the GRANT command.

IMPORTANT
Everyone who has access to the SYSTEM.LOG table will be able to view the query log. Use the query logging at your own discretion.

View queries log

To view the query log, compose and execute a proper query against the SYSTEM.LOG table. Example:

SELECT * FROM SYSTEM.LOG;

Result:

+---------------+--------------+--------------------------------------+-----------+------------------+--------------+----------------------------------------------------+
|  START_TIME   |  TABLE_NAME  |               QUERY_ID               | TENANT_ID |       USER       |  CLIENT_IP   |                         QUERY                      |
+---------------+--------------+--------------------------------------+-----------+------------------+--------------+----------------------------------------------------+
| 1743693276315 | WEAPON_TYPES | efa9397d-56b4-4766-940f-b2dca9ed88ac |           | rsa-key-20240513 | 10.92.40.104 | SELECT ammo_type FROM weapon_types GROUP BY ammo_t |
| 1744124555613 |              | a380c6f4-cffc-4fd8-b35c-1ef02aa52632 |           | rsa-key-20240513 | 10.92.40.104 | SELECT ammo_type FROM weapon_types GROUP BY ammo_t |
+---------------+--------------+--------------------------------------+-----------+------------------+--------------+----------------------------------------------------+

The SYSTEM.LOG table has far more columns than a terminal window of any width can display. You may use the !outputformat vertical command before executing the query and/or narrow its conditions down.

To clear the SYSTEM.LOG table, use the DELETE statement with a WHERE clause. For example, to remove all records from this table, use the following query:

DELETE FROM SYSTEM.LOG WHERE START_TIME > 0;

Set up the Phoenix Query Server logging

To set up the Phoenix Query Server logging, go to the ADCM UI and do the following:

  1. Go to Clusters and select your cluster.

  2. Open the Services tab and select the HBase service.

  3. Turn on the Show advanced switch and select the Custom log4j.properties parameter.

  4. Add the following lines to the parameter contents:

    log4j.logger.org.apache.phoenix.queryserver=DEBUG
log4j.logger.org.apache.phoenix.queryserver.server=DEBUG
log4j.logger.org.apache.phoenix=DEBUG
log4j.logger.org.eclipse.jetty.io.AbstractEndPoint=DEBUG

  5. Change the log4j.threshold value to DEBUG and click Apply.

  6. Save the configuration by clicking Save → Create and restart the service by clicking Actions → Reconfig and graceful restart.

Found a mistake? Seleсt text and press Ctrl+Enter to report it