E-MapReduce:Audit logs

Notes

• The audit log feature is enabled by default and automatically creates the _starrocks_audit_db_ database and the starrocks_audit_tbl table. All subsequent audit logs are stored in this table. Do not delete the _starrocks_audit_db_ database or the starrocks_audit_tbl table, as doing so will cause the SQL task query feature in Manager to fail.

• Audit logs use dynamic partitioning and retain audit data for 30 days by default. You can adjust the data retention period by modifying the dynamic_partition.start and dynamic_partition.end parameters.

• The audit log feature cannot be disabled.

Features

On the EMR StarRocks Manager page, click Metadata Management in the left-side navigation pane. Under default_catalog, you can find the automatically created database _starrocks_audit_db_ and its table starrocks_audit_tbl.

To view the CREATE TABLE statement for starrocks_audit_tbl, run SHOW CREATE TABLE starrocks_audit_tbl;:

CREATE TABLE `starrocks_audit_tbl` (
  `queryId` varchar(64) NOT NULL COMMENT "Unique ID for the query",
  `timestamp` datetime NOT NULL COMMENT "Start time of the query",
  `queryTime` bigint(20) NOT NULL COMMENT "Query execution time (milliseconds)",
  `queryType` varchar(12) NULL COMMENT "Query type (query, slow_query, connection)",
  `clientIp` varchar(32) NULL COMMENT "IP address of the client",
  `user` varchar(64) NULL COMMENT "Username for the query",
  `authorizedUser` varchar(64) NULL COMMENT "Unique user identifier, that is, user_identity",
  `resourceGroup` varchar(64) NULL COMMENT "Name of the resource group",
  `catalog` varchar(32) NULL COMMENT "Name of the data catalog",
  `db` varchar(96) NULL COMMENT "Database where the query was run",
  `state` varchar(8) NULL COMMENT "Query status (EOF, ERR, OK)",
  `errorCode` varchar(512) NULL COMMENT "Error code",
  `scanBytes` bigint(20) NULL COMMENT "Bytes scanned by the query",
  `scanRows` bigint(20) NULL COMMENT "Rows scanned by the query",
  `returnRows` bigint(20) NULL COMMENT "Rows returned by the query",
  `cpuCostNs` bigint(20) NULL COMMENT "CPU time consumed by the query (nanoseconds)",
  `memCostBytes` bigint(20) NULL COMMENT "Memory consumed by the query (bytes)",
  `stmtId` int(11) NULL COMMENT "Incremental ID for the SQL statement",
  `isQuery` tinyint(4) NULL COMMENT "Whether the SQL statement is a query (1 or 0)",
  `feIp` varchar(128) NULL COMMENT "IP address of the frontend (FE) that executed the statement",
  `stmt` varchar(1048576) NULL COMMENT "Original SQL statement",
  `digest` varchar(32) NULL COMMENT "Fingerprint of the slow SQL statement",
  `planCpuCosts` double NULL COMMENT "CPU time consumed during the query planning phase (nanoseconds)",
  `planMemCosts` double NULL COMMENT "Memory consumed during the query planning phase (bytes)",
  `warehouse` varchar(96) NULL COMMENT "Compute group that the query uses",
  `stmtType` varchar(8) NULL COMMENT "SQL type (DQL, DML, DDL, DCL, OTHER)",
  `isFilter` tinyint(4) NULL COMMENT "Whether the SQL statement was filtered (1 or 0)",
  `errorMsg` varchar(1048576) NULL COMMENT "Error details",
  `pendingTimeMs` bigint(20) NULL COMMENT "Time the query spent waiting in the queue (milliseconds)",
  `candidateMVs` varchar(65533) NULL COMMENT "A list of candidate materialized views considered for the query.",
  `hitMvs` varchar(65533) NULL COMMENT "A list of materialized views that the query successfully used (hit)."
) ENGINE=OLAP 
DUPLICATE KEY(`queryId`, `timestamp`, `queryTime`)
COMMENT "Audit log table"
PARTITION BY RANGE(`timestamp`)
(PARTITION p20250909 VALUES [("0000-01-01 00:00:00"), ("2025-09-10 00:00:00")),
PARTITION p20250910 VALUES [("2025-09-10 00:00:00"), ("2025-09-11 00:00:00")),
PARTITION p20250911 VALUES [("2025-09-11 00:00:00"), ("2025-09-12 00:00:00")),
PARTITION p20250912 VALUES [("2025-09-12 00:00:00"), ("2025-09-13 00:00:00")),
PARTITION p20250913 VALUES [("2025-09-13 00:00:00"), ("2025-09-14 00:00:00")))
DISTRIBUTED BY HASH(`queryId`) BUCKETS 3 
PROPERTIES (
"compression" = "LZ4",
"datacache.enable" = "true",
"dynamic_partition.buckets" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.end" = "3",
"dynamic_partition.history_partition_num" = "0",
"dynamic_partition.prefix" = "p",
"dynamic_partition.start" = "-30",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"enable_async_write_back" = "false",
"replication_num" = "1",
"storage_volume" = "builtin_storage_volume"
);