[Enhancement] add partition scan infomation in audit log

[MatthewH00]

Why I'm doing:

When user set a long TTL for table partitions, but the historic partitions are never visited or rarely visited, it would cause unnecessary storage waste and metadata pressure. So more detailed partition visit audit information is needed to distinguish cold and hot partitions, so as to better data governance. With the help of external tools, it is easy to parse the source table from query SQL, but it is difficult to parse the visit partition information.

What I'm doing:

Add a new FE session variable enable_scan_partitions_audit to control turn on or not, and avoid log file too large, add a new FE session variable max_scan_partitions_audit_num to limit the scan partitions print num in audit log. The fe.audit.log file would add a new item ScanPartitions after ScanRows like:

ScanPartitions=[{catalogName:default_catalog,databaseName:db1,tableName:tab1,partitionIds:[p20241001, p20241002]}, {catalogName:default_catalog,databaseName:db2,tableName:tab2,partitionIds:[tab2]}] explain as follows:

- catalogName: only support internal catalog now

- databaseName: database name

- tableName: table name

- partitionIds: when is partitioned table display visit partitionId, when is unpartitioned table display table name
(to be noticed: when scan partitions num exceed the FE session variable `max_scan_partitions_audit_num`, partitionIds display like [p20241001,...,p20241010])

Fixes #issue

What type of PR is this:

• [ ] BugFix
• [ ] Feature
• [x] Enhancement
• [ ] Refactor
• [ ] UT
• [ ] Doc
• [ ] Tool

Does this PR entail a change in behavior?

• [x] Yes, this PR will result in a change in behavior.
• [ ] No, this PR will not result in a change in behavior.

If yes, please specify the type of change:

• [x] Interface/UI changes: syntax, type conversion, expression evaluation, display information
• [ ] Parameter changes: default values, similar parameters but with different default values
• [ ] Policy changes: use new policy to replace old one, functionality automatically enabled
• [ ] Feature removed
• [ ] Miscellaneous: upgrade & downgrade compatibility, etc.

Checklist:

• [x] I have added test cases for my bug fix or my new feature
• [ ] This pr needs user documentation (for new or modified features or behaviors)
  • [ ] I have added documentation for my new feature or new function
• [ ] This is a backport pr

Bugfix cherry-pick branch check:

• [x] I have checked the version labels which the pr will be auto-backported to the target branch
  • [x] 3.3
  • [ ] 3.2
  • [ ] 3.1
  • [ ] 3.0
  • [ ] 2.5

[MatthewH00]

@kevincai Hi Could you please help to review the pr when you have free time ?

[kevincai]

@kevincai Hi Could you please help to review the pr when you have free time ?

I may not be adequate to review this PR. But I am kind of objection to add scanPartitions into audit log, (the query dump might be fine), it can be easily blow the audit log by a single full table scanning of thousands of partitions.

[MatthewH00]

@kevincai Hi Could you please help to review the pr when you have free time ?

I may not be adequate to review this PR. But I am kind of objection to add scanPartitions into audit log, (the query dump might be fine), it can be easily blow the audit log by a single full table scanning of thousands of partitions.

From the view of a cluster administrator, the partitions scan info is needed when faced the Insufficient storage or metadata pressure(tablet too many)，it would become a reference to data governance. And consider the metadata pressure , it would not create the table with too many partition . From the 3.3.2 , it looks provide a param max_partition_num_per_table to limit the partition number of table

[kevincai]

@kevincai Hi Could you please help to review the pr when you have free time ?

I may not be adequate to review this PR. But I am kind of objection to add scanPartitions into audit log, (the query dump might be fine), it can be easily blow the audit log by a single full table scanning of thousands of partitions.

From the view of a cluster administrator, the partitions scan info is needed when faced the Insufficient storage or metadata pressure(tablet too many)，it would become a reference to data governance. And consider the metadata pressure , it would not create the table with too many partition . From the 3.3.2 , it looks provide a param max_partition_num_per_table to limit the partition number of table

There are alternative solutions, may not necessary to get it through audit log. e.g. through '/statistics' interface and thrilling down to db-table-partition level. If it is not there, it is good to enhance it.

[MatthewH00]

@kevincai Hi Could you please help to review the pr when you have free time ?

I may not be adequate to review this PR. But I am kind of objection to add scanPartitions into audit log, (the query dump might be fine), it can be easily blow the audit log by a single full table scanning of thousands of partitions.

From the view of a cluster administrator, the partitions scan info is needed when faced the Insufficient storage or metadata pressure(tablet too many)，it would become a reference to data governance. And consider the metadata pressure , it would not create the table with too many partition . From the 3.3.2 , it looks provide a param max_partition_num_per_table to limit the partition number of table

There are alternative solutions, may not necessary to get it through audit log. e.g. through '/statistics' interface and thrilling down to db-table-partition level. If it is not there, it is good to enhance it.

For detailed data governance, may be it is good for partitions scan info in the level of single sql. To avoid to make the audit log file too large , add a new FE variable to limit the number of the printed scan partitions , exceed the limit would display like partitionIds:[p20241001,...,p20241030]} it would be ok?

[kevincai]

For detailed data governance, may be it is good for partitions scan info in the level of single sql. To avoid to make the audit log file too large , add a new FE variable to limit the number of the printed scan partitions , exceed the limit would display like partitionIds:[p20241001,...,p20241030]} it would be ok?

Would be good to have others thought on this PR, not just mine.

[sonarqubecloud[bot]]

Quality Gate failed

Failed conditions
C Reliability Rating on New Code (required ≥ A)

See analysis details on SonarCloud

Catch issues before they fail your Quality Gate with our IDE extension SonarLint

[github-actions[bot]]

[Java-Extensions Incremental Coverage Report]

:white_check_mark: pass : 0 / 0 (0%)

[github-actions[bot]]

[FE Incremental Coverage Report]

:x: fail : 34 / 61 (55.74%)

file detail

	path	covered_line	new_line	coverage	not_covered_line_detail
:large_blue_circle:	com/starrocks/planner/PlanNode.java	0	1	00.00%	[503]
:large_blue_circle:	com/starrocks/qe/StmtExecutor.java	7	28	25.00%	[1205, 1206, 1207, 1208, 1209, 1210, 1211, 1212, 1213, 1214, 1215, 1216, 1218, 1219, 1221, 1223, 1224, 1226, 1227, 1232, 1305]
:large_blue_circle:	com/starrocks/qe/SessionVariable.java	4	6	66.67%	[2646, 2647]
:large_blue_circle:	com/starrocks/sql/common/MetaUtils.java	6	8	75.00%	[101, 102]
:large_blue_circle:	com/starrocks/qe/QueryDetail.java	4	5	80.00%	[285]
:large_blue_circle:	com/starrocks/plugin/AuditEvent.java	3	3	100.00%	[]
:large_blue_circle:	com/starrocks/planner/ScanNode.java	1	1	100.00%	[]
:large_blue_circle:	com/starrocks/common/util/AuditStatisticsUtil.java	3	3	100.00%	[]
:large_blue_circle:	com/starrocks/http/HttpResultSender.java	1	1	100.00%	[]
:large_blue_circle:	com/starrocks/qe/ConnectProcessor.java	1	1	100.00%	[]
:large_blue_circle:	com/starrocks/http/JsonSerializer.java	4	4	100.00%	[]

[github-actions[bot]]

[BE Incremental Coverage Report]

:white_check_mark: pass : 0 / 0 (0%)

[MatthewH00]

@Seaven Hi Could you please help review this pr when have free time? The PR achieve add partition scan infomation in audit log. From the view of cluster administrator, the partitions scan infomation is needed when faced the Insufficient storage or metadata pressure(too many tablet)，it would become a reference to data governance.

[MatthewH00]

AuditLog literally means it's used to audit the query in the system, but not used to inspect the performance.

I would suggest create a view in information_schema to track the data accessing information, it's much more easy to use.

1.It is not used to inspect the performance, the partition scan info like ScanRows/ScanBytes only display more detailed information of a query. 2.for detailed data governance, may be it is good for partitions scan info in the level of single sql, would provide user with more choices by analyze fe.audit.log, like: 1)optimize/governance the query of scan too many partitons 2)audit different time ranges/account for different cluster/business's actual situation 3)distinguish cold and hot partitions , drop never visited or rarely visited historic partition to reduce unnecessary storage waste and metadata pressure ... 3.the pr currently only aimed to internal table, when query exteral table like hive/paimon，the data governance of hive/paimon also need partition scan infomation. like Trino it also provide the partion scan infomation. I would extend exteral table like hive/paimon partition scan info later. 4.later i would place the new added item scanPartitions mapping to audit log table by AuditLoader plugin , It is also easy to use compared to view in information_schema.

In summary, i think print the the partition scan into fe.audit.log would more flexible than view in information_schema.

[MatthewH00]

AuditLog literally means it's used to audit the query in the system, but not used to inspect the performance. I would suggest create a view in information_schema to track the data accessing information, it's much more easy to use.

1.It is not used to inspect the performance, the partition scan info like ScanRows/ScanBytes only display more detailed information of a query. 2.for detailed data governance, may be it is good for partitions scan info in the level of single sql, would provide user with more choices by analyze fe.audit.log, like: 1)optimize/governance the query of scan too many partitons 2)audit different time ranges/account for different cluster/business's actual situation 3)distinguish cold and hot partitions , drop never visited or rarely visited historic partition to reduce unnecessary storage waste and metadata pressure ... 3.the pr currently only aimed to internal table, when query exteral table like hive/paimon，the data governance of hive/paimon also need partition scan infomation. like Trino it also provide the partion scan infomation. I would extend exteral table like hive/paimon partition scan info later. 4.later i would place the new added item scanPartitions mapping to audit log table by AuditLoader plugin , It is also easy to use compared to view in information_schema.

In summary, i think print the the partition scan into fe.audit.log would more flexible than view in information_schema.

@murphyatwork For create a view in information_schema to track the data accessing information , i have several question: 1)view is not real store data, for data governance usually need trace back to the last 30/90/180/365 days, too many partition scan data would not cause the fe resource waste seriously? 2)when use starrocks query external table like hive/paimon, the partition scan data is also suitable to query by a view in information_schema?? Because hive/paimon table also need data governance accoding to the partition scan infomation.

So based the the viewpoint I mentioned above, i think may be good for partitions scan info in the level of single sql by print into fe.audit.log. Please consider it. (add a trino query audit log sample like this, it print the partition scan info into audit log at the single query level: )