v.20.8New Feature

Add normalizeQuery and normalizedQueryHash Functions for Query Analysis

Official CH changelog text
Add function normalizeQuery that replaces literals, sequences of literals and complex aliases with placeholders. Add function normalizedQueryHash that returns identical 64bit hash values for similar queries. It helps to analyze query log. This closes #11271. #13816 (alexey-milovidov).
Introduces the normalizeQuery function to replace literals, sequences of literals, and complex aliases in SQL queries with placeholders, and the normalizedQueryHash function to generate consistent 64-bit hashes for structurally similar queries.

Why it matters

These functions address the need for improved query log analysis by normalizing queries to a common form and producing hash values that group similar queries together, allowing users to efficiently identify query patterns and optimize performance.

How to use it

Use normalizeQuery(query_text) to convert a query into its normalized form with placeholders replacing literals and aliases. Use normalizedQueryHash(query_text) to obtain a 64-bit hash representing the normalized query. These can be applied in SQL logs analysis or monitoring scripts to group similar queries regardless of literal or alias differences.

Related resources

PreviousNext