BigQuery の UDF で MySQL のクエリを正規化する
BigQuery にクエリのログ、例えばスローログなどが貯めてあり、それを分析したいとする。似たようなクエリを集約し、それぞれのクエリの実行時間や走査した行数の avg, min, max などを見たい。「似たようなクエリを集約」するためにはクエリを正規化、つまり引数の違いを無くしたりスペースや改行を統一したりする必要がある。
そこでクエリを正規化する BigQuery の UDF を実装し、正規化したクエリごとに GROUP BY できるようにした。またせっかくなので npm のモジュールにまとめた。
なお今回は MySQL のクエリを対象にしている。
pt-fingerprint
一例として Percona Toolkit には pt-fingerprint というツールがある。例えば次のようにクエリの正規化をしてくれる。正規化された文字列は fingerprint と呼ばれている。実装はごりごりと perl と正規表現 で行われている。
SELECT name, password FROM user WHERE id='12823';
select name, password from user
where id=5;
-- 上記 2 クエリはどちらも以下の文字列に変換される。
select name, password from user where id=?
スローログや general log をそのまま解析してくれるツールとしては pt-query-digest もある。ただ今回は BigQuery 上のデータを見たい用途だったので、fingerprint の計算だけをしてくれる pt-fingerprint のほうがフィットした。なお pt-query-digest の fingerprint 計算も 同じロジック で行われている。
この他には、例えば TiDB のパーサ pingcap/tidb/parser を使うとクエリをパースし AST を得ることができるので、これを使って自分で正規化処理を実装するアプローチも考えられる。正規表現の実装よりはメンテナンスしやすいしパースも頑強になりそうだけど、細かいエッジケースなどに対応するのは大変なので、そこは pt-fingerprint に一日の長があると思う。
また RDS の場合は Performance Insights や、自分は利用したことがないが VividCortex, Monyog, PMM といった モニタリングツール は、スロークエリの分析機能などを提供している。今回はたまたま BigQuery にあるクエリの分析をしたいという用途だったのでフィットしなかったが、MySQL の管理全般をしたい場合にはこうしたツールもある。
sql-fingerprint
今回の目的は pt-fingerprint でも満たせるが、いちいち BigQuery からクエリをエクスポートし、手元などでそれに fingerprint をかけ、その結果をまた BigQuery に戻すなどが必要で、手間がかかる。BigQuery 上だけで正規化できたほうが簡単。
そこで BigQuery の UDF は JavaScript でロジックを書ける のでこれを利用することにした。pt-fingerprint のロジックを JavaScript に移植した。
次のような感じで BigQuery のクエリだけで正規化と集計ができる。
CREATE TEMP FUNCTION fingerprint(sql STRING, matchMD5Checksum BOOL, matchEmbeddedNumbers BOOL)
RETURNS STRING
LANGUAGE js AS r"""
function fingerprint(sql, matchMD5Checksum, matchEmbeddedNumbers) {
let query = sql;
// special cases
if (/^SELECT \/\*!40001 SQL_NO_CACHE \*\/ \* FROM `/.test(query)) {
return 'mysqldump';
}
if (/\/\*\w+\.\w+:[0-9]\/[0-9]\*\//.test(query)) {
return 'percona-toolkit';
}
if (/^administrator command: /.test(query)) {
return query;
}
const matchedCallStatement = query.match(/^\s*(call\s+\S+)\(/i);
if (matchedCallStatement) {
return matchedCallStatement[1].toLowerCase();
}
// shorten multi-value INSERT statement
const matchedMultiValueInsert = query.match(/^((?:INSERT|REPLACE)(?: IGNORE)?\s+INTO.+?VALUES\s*\(.*?\))\s*,\s*\(/is);
if (matchedMultiValueInsert) {
// eslint-disable-next-line prefer-destructuring
query = matchedMultiValueInsert[1];
}
// multi line comment
query = query.replace(/\/\*[^!].*?\*\//g, '');
// one_line_comment
query = query.replace(/(?:--|#)[^'"\r\n]*(?=[\r\n]|$)/g, '');
// USE statement
if (/^use \S+$/i.test(query)) {
return 'use ?';
}
// literals
query = query.replace(/([^\\])(\\')/sg, '$1');
query = query.replace(/([^\\])(\\")/sg, '$1');
query = query.replace(/\\\\/sg, '');
query = query.replace(/\\'/sg, '');
query = query.replace(/\\"/sg, '');
query = query.replace(/([^\\])(".*?[^\\]?")/sg, '$1?');
query = query.replace(/([^\\])('.*?[^\\]?')/sg, '$1?');
query = query.replace(/\bfalse\b|\btrue\b/isg, '?');
if (matchMD5Checksum) {
query = query.replace(/([._-])[a-f0-9]{32}/g, '$1?');
}
if (!matchEmbeddedNumbers) {
query = query.replace(/[0-9+-][0-9a-f.xb+-]*/g, '?');
} else {
query = query.replace(/\b[0-9+-][0-9a-f.xb+-]*/g, '?');
}
if (matchMD5Checksum) {
query = query.replace(/[xb+-]\?/g, '?');
} else {
query = query.replace(/[xb.+-]\?/g, '?');
}
// collapse whitespace
query = query.replace(/^\s+/, '');
query = query.replace(/[\r\n]+$/, '');
query = query.replace(/[ \n\t\r\f]+/g, ' ');
// to lower case
query = query.toLowerCase();
// get rid of null
query = query.replace(/\bnull\b/g, '?');
// collapse IN and VALUES lists
query = query.replace(/\b(in|values?)(?:[\s,]*\([\s?,]*\))+/g, '$1(?+)');
// collapse UNION
query = query.replace(/\b(select\s.*?)(?:(\sunion(?:\sall)?)\s\1)+/g, '$1 /*repeat$2*/');
// limit
query = query.replace(/\blimit \?(?:, ?\?| offset \?)?/, 'limit ?');
// order by
query = query.replace(/\b(.+?)\s+ASC/gi, '$1');
return query;
}
return fingerprint(sql, true, true);
""";
SELECT
fingerprint(query, true, true) fp,
count(*) as num,
max(query) as raw_query_sample,
avg(exec_time) as avg_exec_time
FROM
`your_table`
WHERE
DATE(timestamp, "Asia/Tokyo") = "2022-05-22"
group by
fp
order by
num desc
UDF には 最大サイズなどの制限がある ので、そういう意味でもパースするアプローチよりもこの正規表現のアプローチのほうが適していたと思う。
実装したものはせっかくなので npm モジュールにまとめておいた。
- cou929/sql-fingerprint-js: Converts a SQL into a fingerprint. A JavaScript port of pt-fingerprint.
- sql-fingerprint - npm
次のようにモジュールとしての利用や、一応 CLI からも呼び出せるようになっている。
// Module
import fingerprint from 'sql-fingerprint';
console.log(fingerprint('SELECT * FROM users WHERE id = 1', false, false));
# CLI
npm install -g sql-fingerprint
fingerprint --query="your query"
PR
