本記事はPythonその2 Advent Calendar 2019に参加しています。
1. はじめに
こんにちは。ホクソエムサポーターの藤岡です。
データアナリストらしいですが、分析そっちのけでPySparkと戯れてます。
メソッドチェインを積み上げていくスタイルで最初はちょっと使いづらいなと思ったのですが、
DataFrameが思いのほか使いやすくて、
気がつくとPySpark無しでは生きられない身体になってしまいました......。
さて、今回紹介するライブラリはsqlparseです。
sqlparseは、SQLエンジンを一切使わずにSQLを解析し、そこから種々の情報を得ることができる非常に頼もしいライブラリです。
例えば、SQLの山の中から欲しいテーブルのDDLを簡単に検索できるようにしたり、
さらにそこからカラムの情報を抜き出してきたり、業務で大変お世話になっております。
ただ、パーサー自体が情報分野にいないと馴染みがないものであり、どう使っていいのか分かりづらい一面があります。
加えて、トークン周りの実装にクセがあるため正直馴染みづらいです。
例えば、トークンの種別を表すsqlparse.tokens.Tokenというオブジェクトがあるのですが、実体はtupleのサブクラスのインスタンスです。
isinstance(token, Token)をやると怒られます*1。
ついでに、sqlparse.sql.Tokenというオブジェクトもあるんですが、こっちはクラスです。なんで？
というわけで、前置きもそこそこに解説に入りたいと思います！
色々と書いていたら内容が重くなってしまったので、字句解析編と狭義の構文解析編の二つに分けています。
2. 注意
- 本記事に書かれた内容は自分の理解に基づいたものであり、誤りが含まれている可能性がありますが、ご了承ください。
- もし本記事の不備にお気付きの際には、コメントでご指摘いただければ幸いです。
- また、以下の解説ではSQLが何度か登場しますが、すべてHiveQLです。
- 今回のサンプルプログラムは説明用に作成したものであり、実際の業務での使用は一切想定していないことをご留意ください。
- 本記事のプログラムは全て以下の環境で動作させています。
- Python: 3.6.4
- sqlparse: 0.3.0 (2019/12/07現在の最新ver)
3. サンプルコードについて
本記事のサンプルプログラムはリポジトリに完成品がありますので、ぜひ遊んでみてください。
github.com
4. 導入
Python3.4以上*2がインストールされている環境で、$ pip install sqlparseしてください。
5. sqlparseについて
sqlparseは、作者によればA non-validating sql parser module for Python、つまり、Pythonの非検証のSQLパーサーモジュールです。
といっても、計算機科学でもやってなければ、なんのこっちゃと戸惑うかと思います。
そこで、本章ではパーサーと、その重要な機能の一つである字句解析について簡単に解説します。
少し小難しい話かもしれませんが、
sqlparseを使うときには、ただ使い方を知るだけでなく、
そのコアとなるパーサーについて簡単に知っておくことが重要です。
それにより機能を正しく、かつ十全に活用することができるので、
ぜひ本章を読んでみていただければと思います。
5.1. パーサー (parser) について
パーサーとは、テキストを一定の文法にしたがって解析するアルゴリズム、もしくはその実装のことです。
パーサーが行う処理を広義の構文解析と呼びます。
例えば、SQLやPythonのようなプログラミング言語で書かれた命令を実行するためには、
そこに書かれた個々の命令やその実行フローなどを計算機が読み取る必要があります。
それを実現するにはパーサーによるテキスト解析が欠かせません。
さらに、広義の構文解析は字句解析と狭義の構文解析の2ステップに分けることができます。
直感的なイメージとしては、私たちが英語を読解する場合と照らし合わせると、
- 字句解析: アルファベットの並びを単語・熟語として認識すること
- 狭義の構文解析: 文法の適用
に近い処理です。
こうした解析の結果として得られる情報は計算機だけでなくそれを扱う人間にとっても有用であり、
私たちがsqlparseを活用する意味もそこにあります。
5.2. 非検証パーサーについて
パーサーが処理するテキストの妥当性をチェックすることを検証と言います。
そして、検証を行うパーサーを検証パーサー、検証を行わないパーサーを非検証パーサーと言います。
ただ、これは一般的にXMLパーサー等で使われる言葉であり、SQLパーサーの文脈ではあまり見ない言葉です。
なので、ここではエラーがあっても無視して解析を進める、くらいの認識でいいかと思います。
5.3. 字句解析についてもう少し詳しく
5.1節で軽く触れた字句解析について、sqlparseを使いながらもう少し詳細に見ていきます。
なお、ここで使うコードについては後ほど解説しますので、まずは結果に注目していただければと思います。
さて、5.1節の説明では字句解析について、アルファベットの並びを単語・熟語として認識することと表現しましたが、
より正確には、文字列を入力としてその文字列を表すトークン列を得る処理のことです。
例えば、以下のようなSQLをトークン列に変換していくことを考えます。
SELECT
id,
age,
gender
FROM
hoge_table
WHERE
age > 10;
このsql (sql_1とします) を入力として以下のコードを実行してみます。
import sqlparse
sql_1 = "SELECT id, age, gender FROM hoge_table WHERE age > 10;"
parsed_queries = sqlparse.parse(sql_1)
parsed_query = parsed_queries[0]
list(parsed_query.flatten())
すると、以下のようなトークンオブジェクトのリストが得られます。
[<DML 'SELECT' at 0x107D69A08>,
<Whitespace ' ' at 0x107D841C8>,
<Name 'id' at 0x107D84348>,
<Punctuation ',' at 0x107D84228>,
<Whitespace ' ' at 0x107D842E8>,
<Name 'age' at 0x107D843A8>,
...
一つ一つのトークンオブジェクトは、<トークン '値' at アドレス>という表記になっています。
ここで注意なのですが、構文解析におけるトークンとは"SELECT"のような文字列ではなく、
その種別の名称であるDMLの部分です。
また、空白があっても必ずしもそこを基準に分割される訳ではありません。
例えば、sqlparseは"LEFT JOIN"を"LEFT"と"JOIN"には分割しません。
最初のトークンはDML、次のトークンはWhitespace、さらにName，Punctuation，...と続いていっています。
それぞれのトークンは、sql_1の文字列の分割である、"SELECT", " ", "id", ", ", ...といった値 (文字列) と対応しています。
なお、上で紹介したトークンはsqlparseによるトークン分割で得られる最小単位のトークン列であり、より大きな単位でトークンに分割することもできます。
この部分には狭義の構文解析が深く関わってくるので、詳しくは次回の記事で触れます。
5.4. 基本的な使用方法
5.3節で使用したサンプルコードについて簡単に解説しながら、
sqlparseの使い方の基本について触れていきます。
まず、sqlparse.parse関数にSQLの文字列を渡すと広義の構文解析が行われ、その結果が返されます。
import sqlparse
sql_1 = "SELECT id, age, gender FROM hoge_table WHERE age > 10;"
parsed_queries = sqlparse.parse(sql_1)
結果はタプルとして返されるので、ここで渡すsqlにはクエリが複数含まれていても大丈夫です。
ただし、今回のように一つしかクエリが入っていない場合も結果はタプルとして返ってくるので、
クエリを取り出すにはインデックスを指定する必要があります。
parsed_query = parsed_queries[0]
sqlparse.parse()で得られる各パース結果は、
一つのクエリのパース結果が一つのトークンオブジェクト*3として得られるようになっています。
例えば、
SELECT hoge FROM t1;
SELECT fuga FROM t2;
SELECT piyo FROM t3;
を渡すと、
(<Statement ' -- ho...' at 0x107D86D68>,
<Statement ' -- fu...' at 0x107D90048>,
<Statement ' -- pi...' at 0x107D86DE0>)
が返ってきます。
最後に、今回は字句解析の結果（最小分割単位のトークン分割）だけを得たいので、flattenメソッドで狭義の構文解析の結果を捨てます。
list(parsed_query.flatten())
こうして、5.3節で得られたトークン列が得られます。
[<DML 'SELECT' at 0x107D69A08>,
<Whitespace ' ' at 0x107D841C8>,
<Name 'id' at 0x107D84348>,
<Punctuation ',' at 0x107D84228>,
<Whitespace ' ' at 0x107D842E8>,
<Name 'age' at 0x107D843A8>,
...
以後、特に断りがない場合は、この最小分割単位のトークン列のことを、たんにトークン列と呼ぶこととします。
6. 使用例: CREATE TABLE DDL Finder
6.1 概要
ある案件で、テーブルの定義のDDLが書かれた大量のsqlファイルがフォルダに格納されている現場に遭遇しました。
その中からいちいち目視で必要なファイルを探すのは面倒だった上に、ファイル名が日本語だったり、コメント中に別のテーブル名が入っていたりして、findコマンドでも探せない......。
自分はそんなとき、テーブル名を入力としてそのDDLを実行してCREATE TABLE & MSCK REPAIRをする関数を作成しました。
そのキモとなるのがSQLの解析部分です。当時は正規表現を使ってサクッと作ったのですが
*4、
今回はそれをsqlparseを使って実装してみます。
想定する要件は以下の通りです。
- 多数の.sqlファイルが存在するフォルダが探索対象。
- それぞれの.sqlファイルにはCREATE TABLE文だけでなく、MSCK REPAIR TABLE文のような別のクエリが書かれている。
- テーブル名は探索範囲内でユニーク。ただし、当該ファイル以外のファイルのコメント等には出現しうる。
- ファイル名はテーブル名とは無関係。
- テーブル名はコメントやlocation等にも出現。
- 入力はテーブル名とフォルダのパス、出力は当該SQLファイルのパス。
- 見つからなければFileNotFoundErrorをraiseさせる。
6.2. 実装
まず、sqlparseが関わる部分以外をさっくりと作ると、以下のような感じになります。
from glob import iglob
from pathlib import Path
def find_table_definition(
sql: str, table: str, db: Optional[str] = None) -> Optional[TokenType]:
"""引数で指定したテーブルの定義を文字列`sql`中から探し出す関数。"""
<<メイン部分>>
def find_hive_ddl(directory: str, table: str, db: Optional[str] = None):
"""引数で指定したテーブルの定義スクリプトをフォルダから探し出す関数"""
for p in iglob(directory, recursive=True):
path = Path(p)
if not path.is_file():
continue
with p.open() as f:
if find_table_definition(f.read(), table, db):
return p
raise FileNotFoundError(
"DDL file of {table} not found.".format(table=table)
)
次に、上記のメイン部分find_table_definitionを作成していきます。
処理の流れとしては以下の通りです。
- SQLテキストからCREATE TABLEクエリを抽出
- クエリ中からテーブル名のトークンオブジェクトを抽出。
- テーブル名を取得。
- 一致判定の結果を返す。
HiveQLの場合、以下の条件を満たすのならばCREATE TABLEのDDLです。
- コメントを除けば、DDLトークン"CREATE"から始まる。
- さらにTABLEトークンが続く。ただ、Keywordトークンの"TEMPORARY"と"EXTERNAL"を挟むことがある。
上記の条件を満足しているかを判定する部分のコードは以下の通りです。
from typing import Optional, List, Sequence
from sqlparse.tokens import Keyword, DDL, Token, Name, Punctuation
from sqlparse.sql import Statement
import sqlparse
TokenType = Token.__class__
DEFAULT_DB = "default"
def find_table_definition(
sql: str, table: str, db: Optional[str] = None) -> Optional[TokenType]:
"""`table`で指定したテーブルの定義を文字列`sql`中から見つける"""
db = db or DEFAULT_DB
for query in sqlparse.parse(sql):
tokens = [
t for t in query.flatten()
if not (t.is_whitespace or is_comment(t))
]
if not match_consume(tokens, (DDL, "CREATE")):
continue
match_consume(tokens, (Keyword, "TEMPORARY"))
match_consume(tokens, (Keyword, "EXTERNAL"))
if not match_consume(tokens, (Keyword, "TABLE")):
continue
if match_consume(tokens, (Keyword, "IF")):
if not match_consume(tokens, (Keyword, "NOT")):
continue
if not match_consume(tokens, (Keyword, "EXISTS")):
continue
if db == DEFAULT_DB and match_consume(tokens, (Name, table)):
return query
if (match_consume(tokens, (Name, db))
and match_consume(tokens, (Punctuation, "."))
and match_consume(tokens, (Name, table))
):
return query
def is_comment(token: TokenType) -> bool:
return token.ttype in sqlparse.tokens.Comment
def match_consume(tokens: List[TokenType], match_args: Iterable) -> bool:
if tokens[0].match(*match_args):
tokens.pop(0)
return True
return False
試してみると、うまく動くことが確かめられます。
以下の例はサンプルコードリポジトリのSQLで試した場合です。
find_hive_ddl("./ddls/*.sql", "apachelog")
> './ddls/apachelog_ddl.sql'
6.3. 実装詳細
ここでは、find_table_definition()の実装について、ポイントとなる箇所を解説していきます。
まず、文字列sqlをパースして、そのflattenでトークン列に変換してから、空白文字とコメントを取り除いています。
for query in sqlparse.parse(sql):
tokens = [t for t in query.flatten() if not (t.is_whitespace or is_comment(t))]
空白文字の除去は簡単で、is_whitespace属性で判定できます。
ですが、コメントは少し特殊で、
is_commentという以下の関数を呼び出して除いています。
def is_comment(token: TokenType) -> bool:
return token.ttype in sqlparse.tokens.Comment
トークンの種別を表すオブジェクト（Comment, DDL, Statement等）はttype属性に格納されています。
ttypeはsqlparse.tokens*5に定義されているのですが、
少し特殊な実装になっていて、sqlparse内で定義されている関数やメソッドに頼らずに扱おうとすると予期しない動作を引き起こす可能性があります。
詳細については今回は省略しますが、面白い実装なのでソースコードを読んでみることをオススメします！
そのうち自分のブログの方でも取り上げようと思います。
話を戻すと、コメントの判定でtoken.ttypeとsqlparse.tokens.Commentをinで比較しているのは、
コメントのttypeはCommentトークンだけではなく、
一行コメントトークンSingleや複数行コメントトークンMultilineとなる場合があるからです。
先ほどの条件文は「トークンオブジェクトtokenがCommentトークンもしくはそのサブグループに属するSingle, Multilineのいずれかに該当する場合にのみ真」というものなので、これらを全て扱うことができます。
邪魔なトークンを取り除けたら、CREATE TABLE文かどうかの判定を行います。
ここまできたら正規表現でもいい気がしますが、
トークンオブジェクトのmatchメソッドを使って、個々のトークンを調べていきます。
まず、先頭にCREATEトークンが来ていることを確認します。
もしもCREATEトークンが来ていれば、そのトークンの次のトークンの判定に移り、
そうでなければ、そのクエリの解析を終えます。
この処理をコード化すると以下の通りになります。
if not tokens[0].match(DDL, "CREATE"):
continue
tokens.pop(0)
matchにttypeと値を渡すことでトークンのマッチングができます。
regex引数にTrueを渡すと、値を正規表現として処理することもできます。
このトークンマッチ->トークンを進めるという処理を繰り返していくことになるので、
match_consume関数にまとめてしまいます。
def match_consume(tokens: List[TokenType], match_args: Sequence) -> bool:
if tokens[0].match(*match_args):
tokens.pop(0)
return True
return False
この関数を作ることで、以下のように処理が簡略化されます。
if not match_consume(tokens, (DDL, "CREATE")):
continue
これを使って、以降の処理も同様に書くことができます。
match_consume(tokens, (Keyword, "TEMPORARY"))
match_consume(tokens, (Keyword, "EXTERNAL"))
if not match_consume(tokens, (Keyword, "TABLE")):
continue
if match_consume(tokens, (Keyword, "IF")):
if not match_consume(tokens, (Keyword, "NOT")):
continue
if not match_consume(tokens, (Keyword, "EXISTS")):
continue
if db == DEFAULT_DB and match_consume(tokens, (Name, table)):
return query
if (match_consume(tokens, (Name, db))
and match_consume(tokens, (Punctuation, "."))
and match_consume(tokens, (Name, table))
):
return query
実装は以上です。
もちろん、文法の細かいチェックなどは入っていないですが、
それでも着目している部分に関してはかなり正確なチェックをしつつ、
目的のDDL探索ができるようなスクリプトが簡単に書けることが分かります。
7. 次回予告
ここまで字句解析とその結果得られるトークン列の活用方法を紹介してきましたが、もっと高度なことをやろうと思うと色々と不便です。
例えば、SELECT文からサブクエリを抽出しようとすると、SELECT文の中の複雑な規則を条件分岐で表現することになり骨が折れます。
そこで、次回は今回紹介しなかった狭義の構文解析について簡単に解説し、
SELECT文中のテーブル間の依存関係抽出のスクリプト作成に挑戦しながら、
少しだけ高度なsqlparseの活用方法について紹介したいと思います。
8. おわりに
sqlparse.parseに色々と投げ込んでみると発見があって面白いので、ぜひぜひ試してみてください！
9. 補足
CREATE TABLE文の判定条件の詳細
HiveQLのCREATE TABLE文*6は、公式によると以下の通りです。
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
大事なのは最初の1行CREATE [TEMPORARY] [EXTERNAL] TABLEです。
HiveQLには、CREATE DATABASE文のような<DDL CREATE>から始まる別のタイプのSQLがあるため、
それを弾くためにTABLEまでを解析させる必要があるわけです。
10. References
GitHub - andialbrecht/sqlparse: A non-validating SQL parser module for Python
LanguageManual - Apache Hive - Apache Software Foundation
ease_aesに"cubic-in-out"を指定（増加に緩急が付く）
from 
from 
(Casedが大文字のテキスト、Uncasedが小文字化したテキストでのタスクの精度。Uncasedのほうが精度が低いことがわかる。)
from 
赤枠がvisual featureなし、緑枠がvisual featureありの予測結果。
この結果から、フォントサイズやテキストサイズなどの visual feature が有効であることがわかる。
From 
