SQLからtable(view)の依存関係図(PlantUML)を作るPythonコードを書いた
これは何?
こんな感じのtable(view)の依存関係図を....
↓こんな感じのSQL群から、出力するためのPythonコードを書いたというお話です。
(図の作成にはPlantUMLを用いました)
CREATE TABLE
`project.dataset.table5` AS
SELECT
*
FROM
`project.dataset.table1`;
CREATE TABLE IF NOT EXISTS
`project.dataset.table6` AS
SELECT
*
FROM
`project.dataset.table2`
UNION ALL
SELECT
*
FROM
`project.dataset.table3`;
CREATE VIEW
`project.dataset.table7` AS
SELECT
*
FROM
`project.dataset.table3`
INNER JOIN
`project.dataset.table4`
USING
(user_id);
PlantUMLとは
wikipediaによると
PlantUMLはオープンソースのUMLダイアグラム作成用のテキストベースの言語である
だそうです。
様々なモデル図を作成可能です。
学ぶのに立ったサイトを並べておきます。
- 基本的な構文を学んだり、図のレンダリングができる
- とても使いやすかった個人開発のアプリ
依存関係図作成の仕方
以下をPython3で実行してください。
sql1 = '''CREATE TABLE
`project.dataset.table5` AS
SELECT
*
FROM
`project.dataset.table1`;
'''
sql2 = '''CREATE TABLE IF NOT EXISTS
`project.dataset.table6` AS
SELECT
*
FROM
`project.dataset.table2`
UNION ALL
SELECT
*
FROM
`project.dataset.table3`;
'''
sql3 = '''CREATE VIEW
`project.dataset.table7` AS
SELECT
*
FROM
`project.dataset.table3`
INNER JOIN
`project.dataset.table4`
USING
(user_id);
'''
import re
prog_destination = re.compile(r'(?:CREATE TABLE|CREATE TABLE IF NOT EXISTS|CREATE VIEW|CREATE VIEW IF NOT EXISTS|INSERT INTO|INSERT)[\s \n]+`(.+?)`')
prog_origin = re.compile(r'(?:FROM|JOIN)[\s \n]+`(.+?)`')
platuml_tempate = '''@startuml
skinparam padding 10 /'paddingの調整'/
left to right direction /'diagramを左から右に伸ばして行くレイアウトにしたい場合'/
hide members /'classの属性を消す'/
hide circle /'classマークを消す'/
{}
@enduml
'''
def make_table_dependencies_for_platuml(sql:str):
# 作成するテーブル(ビュー)を取得
if len(prog_destination.findall(sql)) != 1:
raise Exception('CREATE TABLE|INSERT INTO句が存在しません。')
else:
destination_table = prog_destination.findall(sql)[0]
# 自己参照を除いた参照元テーブルを取得
origin_tables = [table for table in prog_origin.findall(sql) if table != destination_table]
if len(origin_tables) == 0:
raise Exception('依存関係が存在しません')
return [F'"{table}" <|-- "{destination_table}"' for table in origin_tables]
# sql毎にPlantUMLを出力したい場合
# print(platuml_tempate.format('\n'.join(make_table_dependencies_for_platuml(sql1))))
# print(platuml_tempate.format('\n'.join(make_table_dependencies_for_platuml(sql2))))
# print(platuml_tempate.format('\n'.join(make_table_dependencies_for_platuml(sql3))))
all_sql_dependencies = [make_table_dependencies_for_platuml(sql) for sql in [sql1, sql2, sql3]]
print(platuml_tempate.format('\n'.join(sum(all_sql_dependencies, []))))
すると、以下の結果が出力されるかと思います。
先程紹介したリンク先などで図をレンダリングしてみましょう。
(冒頭に出てきた関係図が描画されます。)
@startuml
skinparam padding 10 /'paddingの調整'/
left to right direction /'diagramを左から右に伸ばして行くレイアウトにしたい場合'/
hide members /'classの属性を消す'/
hide circle /'classマークを消す'/
"project.dataset.table1" <|-- "project.dataset.table5"
"project.dataset.table2" <|-- "project.dataset.table6"
"project.dataset.table3" <|-- "project.dataset.table6"
"project.dataset.table3" <|-- "project.dataset.table7"
"project.dataset.table4" <|-- "project.dataset.table7"
@enduml
なぜこのコードを書いたか
データ分析の仕事をさせて頂いているのですが、BigQueryでテーブルを加工してまた別のテーブルを作り、さらにそのテーブルを加工してテーブルを作ることがよくあります。特にアドホックな分析をしていると、テーブル間の関係を把握するのが難しくなってきます。テーブル関係の把握や、無駄な中間テーブルを作っていないかなどに役立てられるかと思います。
参考
- SQLからテーブル間の依存関係を把握するという発想と正規表現は下記記事から得ました。さすがZOZOです
その他
- GraphViz & PyDot を使うという手もある。
最後に
- GraphViz & PyDot を使うという手もある。
最後に
弊社、今年もアドベントカレンダーに挑戦中ですのでどうぞご覧ください。
https://qiita.com/advent-calendar/2019/sensy
Author And Source
この問題について(SQLからtable(view)の依存関係図(PlantUML)を作るPythonコードを書いた), 我々は、より多くの情報をここで見つけました https://qiita.com/nassy20/items/ef4d241600ed348c2620著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .