sql-metadataを試してみる

はじめに

データアナリティクス事業本部のkobayashiです。

Pythonでクエリをパースしてその内容を扱う必要があったため簡単に扱えるライブラリを探していた所 sql-metadata というライブラリが見つかりました。クエリのメタデータ（カラム名、テーブル名など）が簡単に取得できたのでその内容をまとめます。

• GitHub - macbre/sql-metadata: Uses tokenized query returned by python-sqlparse and generates query metadata
• sql-metadata · PyPI

環境

• macOS Big Sur
• Python 3.7.12

sql-metadataとは

クエリ中に含まれているカラム名やテーブル名やそのエイリアス名を簡単に取得できます。またInset文では挿入する値も取得できます。sql-metadataは非検証SQLパーサーのpythonライブラリであるsqlparse を内部で使用しています。sql-metadataではsqlparseで取得できるTokenクラスを使いメタデータを取得しています。sqlparseの説明は弊社ブログ記事で説明されていますのでそちらの記事も御覧ください。

sql-metadataで対応しているクエリ構文は以下となっています。

• MySQL
• PostgreSQL
• Sqlite
• MSSQL
• Apache Hive

オンラインデモがありますのでこちらでsql-metadataの使用感を試すことができます。

• Sql Parser

sql-metadataを試してみる

インストールはpipを使ってインストールするだけです。

$ pip install sql-metadata

では実際にカラム名、テーブル名、そのエイリアス名、Insert文の値の取得を試してみます。

カラム名の取得

はじめにカラム名の抽出を行ってみます。Pythonのスクリプトは非常に単純で特に難しいこともありません。

import sql_metadata

query_md = sql_metadata.Parser("select col1 as aaa, col2 as bbb  from table1 as t_1;").columns
print(query_md)
# ['col1', 'col2']

query_md = sql_metadata.Parser("select col1 as aaa, col2 as bbb  from table1 as t_1;").columns_aliases
print(query_md)
# {'aaa': 'col1', 'bbb': 'col2'}

query_md = sql_metadata.Parser(
    "select t_1.col1 , t_2.col2 from table1 as t_1 inner join table2 as t_2 on t_1.id = t_2.id;").columns_dict
print(query_md)
# {'select': ['table1.col1', 'table2.col2'], 'join': ['table1.id', 'table2.id']}

• 1つ目はクエリからカラム名だけを取得しています。
• 2つ目はエイリアスを取得していますが、「エイリアス名をキー」「元のカラム名を値」としてdictで取得しています。
• 3つ目はカラム名をクエリ中の句ごとに取得しています。今回ですとselect句とjoin句で使用しているカラム名を取得しています。またエイリアス名は元のテーブル名に置換されて取得できるのでわかりやすい表現となっています。

テーブル名の取得

次にテーブル名の抽出を行ってみます。

import sql_metadata

query_md = sql_metadata.Parser("select col1 as aaa, col2 as bbb  from table1 as t_1;").tables
print(query_md)
# ['table1']

query_md = sql_metadata.Parser("select col1 as aaa, col2 as bbb  from table1 as t_1;").tables_aliases
print(query_md)
# {'t_1': 'table1'}

query_md = sql_metadata.Parser(
    "select col1 , col2  from table1 as t_1 inner join table2 as t_2 on t_1.id = t_2.id;").tables
print(query_md)
# ['table1', 'table2']

• 1つ目はクエリからテーブル名だけを取得しています。
• 2つ目はエイリアスを取得していますが、「エイリアス名をキー」「元のカラム名を値」としてdictで取得しています。カラムのエイリアス取得と同じです。
• 3つ目は結合しているテーブルがlist形式で取得できています。

サブクエリの取得

次はサブクエリの抽出を行ってみます。

import sql_metadata

query_md = sql_metadata.Parser(
    "select x.aaa,x.bbb from (select col1 as aaa, col2 as bbb  from table1 as t_1) x;").subqueries
print(query_md)
# {'x': 'select col1 as aaa, col2 as bbb from table1 as t_1'}

• エイリアス名をキー、サブクエリを値としてdict形式で取得しています。

Insertでの値の取得

最後にInsert文での値を取得してみます。

import sql_metadata

query_md = sql_metadata.Parser("INSERT INTO table1 (col1, col2, col3) VALUES ('Japan', 'Tokyo', 99.9);").values
print(query_md)
# ['Japan', 'Tokyo', 99.9]

query_md = sql_metadata.Parser("INSERT INTO table1 (col1, col2, col3) VALUES ('Japan', 'Tokyo', 99.9);").values_dict
print(query_md)
# {'col1': 'Japan', 'col2': 'Tokyo', 'col3': 99.9}

• 1つ目はセットしている値をlist形式で取得しています。
• 2つ目はカラム名をキー、セットしている値を値としてdict形式で取得しています。

このようにとても簡単にクエリのメタデータが取得できました。

sql-metadataのソースコードのtest/ディレクトリにはより複雑なクエリでの実行結果例も載っているのでそちらも確認いただくとより知見が深まると思いますので、一度ご確認ください。

• sql-metadata/test at master · macbre/sql-metadata · GitHub

まとめ

Pythonのライブラリsql-metadataライブラリを使ってクエリからカラム名やテーブル名といったクエリのメタデータを取得してみました。それ以外にもInsert文の値やサブクエリなども簡単に取得でき、これらのメタデータを使って何か処理を行いたい際にはsql-metadataはおすすめのライブラリです。

最後まで読んで頂いてありがとうございました。