ByteHouse Cloud Data Warehouse provides SQL audit capabilities and an interface for viewing SQL execution logs. It supports viewing SQL history and running queries, helping you quickly locate and optimize slow queries, troubleshoot errors, and audit user behavior to ensure data security. These features are suitable for database performance tuning, security compliance audits, post-incident tracing, and other scenarios. Additionally, ByteHouse provides a detailed SQL log table. After enabling the detailed log toggle, you can view the corresponding detailed SQL log table data to further inspect more granular log data. This article introduces how to use the ByteHouse SQL audit feature.

The ByteHouse SQL audit feature supports viewing SQL history and running queries. In SQL history, you can view slow SQL queries, error queries, and perform precise searches by ID or SQL statement content.
This feature provides comprehensive audit dimensions, including query ID, status, username/ID, effective roles, virtual warehouse, start/end time, duration, SQL type, SQL statement, total CPU time, maximum memory usage (Segment), and others. Additionally, this feature supports a wide range of filter options, making it convenient for you to efficiently analyze SQL execution status.

In addition, ByteHouse also supports viewing detailed logs. After enabling this feature, ByteHouse will begin accumulating data (a new connection to ByteHouse is required) and will automatically create a database named bh_system and a query_log table within it to record detailed SQL execution log data. You have all privileges on this database and table. The default data lifecycle of this table is 7 days. To retain data for a longer period, you can execute SQL commands to delete the TTL as needed or modify the TTL time. For operation details, see Adjust the query_log table lifecycle.

• Administrators can view all users' SQL audit information, including SQL history, running queries, and detailed logs. Sub-users can only view the SQL audit information of the current account. To view all users' SQL audit information, sub-users can contact the administrator to grant global audit log privileges. For operation details, refer to Resource Permission Settings.
• 'CPU total time' and 'Max memory usage (Segment)' in the SQL history list are Beta features and require the engine version to be v2.3 or above. To view the corresponding information, submit a ticket or contact the ByteHouse team to obtain Allowlist privilege.
• Detailed log is a non-public feature. To use it, submit a ticket or contact the ByteHouse team to obtain Allowlist privilege.

1. Log in to the ByteHouse Console, click the top Audit logs, click Query history, and under the History tab, you can see the query history list. You can filter SQL history using filter conditions. It supports searching by Query ID or SQL Statement; you can select and switch from the dropdown list.
   
   1. The SQL history list contains the following information:
      • Query ID: Unique ID number of the SQL query.
      • Status: Event completion state, such as "Success" or "Failed". For failed events, you can check the specific details using the button on the right.
      • Active role: Role type for executing this event.
      • Virtual warehouse: Name of the virtual warehouse (VW) executing the query.
      • CPU Total Duration: Sum of CPU time for all segments in the virtual warehouse. Since segments can run in parallel, this value may be greater than the query duration.
      • Max Memory Usage (Segment): Maximum memory usage of all segments executed by the virtual warehouse.
      • Start time: Time when the event occurred.
      • End time: Time when the event ended.
      • Duration: Duration of the event execution.
      • SQL type: Type of SQL statement executed, such as CREATE, DROP, INSERT, DELETE, and others.
      • SQL Statement: Content of the executed SQL statement, as well as its dialect type.
   2. Switch to the "Failed queries" tab to quickly locate failed events and query related states as needed.
      
   3. Switch to the "Search by ID or SQL" tab to query by query ID or SQL statement.
2. Switch to the "Running query" tab to view the list of running queries. You can view the query ID, start time, virtual warehouse, and SQL statement. You can also use Query ID, SQL Statement, or Virtual warehouse to query running queries.

   tip

   You can also use the SQL command SHOW PROCESSLIST to view currently running queries. For usage instructions, see SHOW PROCESSLIST.

After obtaining Allowlist privilege, you can open the Enable detailed logging toggle at the top right of the Query history page. Once enabled, ByteHouse will create a query_log table in the system for writing detailed SQL execution log data.

1. Open the Enable detailed logging toggle.
   
   After clicking the detailed log button, ByteHouse will obtain your authorization to create the bh_system database under your [tenant-environment], and create a query_log table in this database. You have full privileges for this database and table. To allow sub-users to view the query_log, contact the administrator to grant data privileges. For operation details, refer to Data privilege management.

   warning

   Because ByteHouse continuously writes the SQL log information you run into the bh_system.query_log table, any actions such as adding, deleting, or modifying this table will affect log writing. Except for modifying the TTL, adding, deleting, or modifying this table is prohibited.

2. View the query_log table. You can view the query_log table by clicking Database at the top of the page or Data Query.
   For example, to view the query_log table using the SQL worksheet, you can see this table in the bh_system database under the data objects on the left side of the SQL worksheet.
   

Adjust the query_log table lifecycle #

The bh_system.query_log table has a default data lifecycle of only 7 days. To retain data for a longer period, refer to the following commands to delete the TTL or modify the TTL time as needed.

# 查看 bh_system.query_log 表当前生命周期
SHOW CREATE TABLE bh_system.query_log;

# 删除 TTL
ALTER TABLE bh_system.query_log REMOVE TTL;

# 将默认 TTL 从 7 天改为 180 天（您可以将 180 替换为您希望保留日志的天数，需管理员权限执行）
ALTER TABLE bh_system.query_log  MODIFY TTL event_time + INTERVAL 180 DAY;

query_log table information #