27 May 2022

BigQuery の UDF で MySQL のクエリを正規化する

BigQuery にクエリのログ、例えばスローログなどが貯めてあり、それを分析したいとする。似たようなクエリを集約し、それぞれのクエリの実行時間や走査した行数の avg, min, max などを見たい。「似たようなクエリを集約」するためにはクエリを正規化、つまり引数の違いを無くしたりスペースや改行を統一したりする必要がある。

そこでクエリを正規化する BigQuery の UDF を実装し、正規化したクエリごとに GROUP BY できるようにした。またせっかくなので npm のモジュールにまとめた。

sql-fingerprint - 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 モジュールにまとめておいた。

次のようにモジュールとしての利用や、一応 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