SQL Server用のSQL構文解析プログラムを作ったのと、SQLの可視化に挑戦中 な話

これはAzure SQL & Synapse Analytics Advent Calendar 2020 13日目の記事です。

構文解析プログラムを作りました

山ほどあるSQLからデータの流れを追いたかったので、SQLの構文解析プログラムを作りました。

MicrosoftのMicrosoft SQL Server 2016 Service Pack 2 Feature Packに入っているMicrosoft SQL Server 2016 SP2 Transact-SQL ScriptDomを使って作成したので、SQL Server用です。
最新のSQL Server2019でしか使えない構文には対応していませんが、Microsoft製の構文解析ライブラリを使っているのでそれ以外の構文にはほぼ対応しています。

余談ですが・・・

最初は [私家版のSQLのISO規格の構文定義](https://github.com/ronsavage/SQL) をベースに不特定多数のDBMSを対象としたプログラムをで作ろうとissueやpull requestを送っていたりしたのですが、不具合が多かった事とオーナーが体調を崩したのをきっかけに断念しました。 次は[ANTLRのコミュニティが作ったTSQLの構文定義](https://github.com/antlr/grammars-v4/tree/master/sql/tsql)を元に作る事を試みました。 動作するところまでは持っていったのですが、元の構文定義に不具合が多くいくら修正しても焼け石に水だったので一旦断念しました。 https://github.com/GCer-Hidenori/ANTSQLParser

GraphvizでのSQLの可視化に挑戦中です

この構文解析プログラムを使ったわかりやすい応用例が無いかと考えて、GraphvizでのSQLの可視化を思いつきました。

作成したSQL構文解析プログラムはXMLを出力するので、これをGraphvizのdotに変換して可視化します。
まだまだ作りかけですが、SQLから次のような画像を作れます。

• UPDATE

USE AdventureWorks2016;
GO 
UPDATE Production.Product
SET ListPrice = ListPrice * 2
FROM Production.Product AS p
INNER JOIN Purchasing.ProductVendor AS pv
    ON p.ProductID = pv.ProductID AND BusinessEntityID = 1540;
GO   

-- Query data/Subqueries
-- https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver15

• 副問合せ

USE AdventureWorks2016;
GO

/* SELECT statement built using a subquery. */
SELECT Name
FROM Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM Production.Product
     WHERE Name = 'Chainring Bolts' );
GO

/* SELECT statement built using a join that returns
   the same result set. */
SELECT Prd1. Name
FROM Production.Product AS Prd1
     JOIN Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2. Name = 'Chainring Bolts';
GO

-- Query data/Subqueries
-- https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver15

• CREATE VIEW

CREATE VIEW hiredate_view  
AS   
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate  
FROM HumanResources.Employee e   
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;  
GO  
  

-- Reference/Transact-SQL (T-SQL) Reference/Statements/CREATE/VIEW
-- https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15

使い方

1. プログラムでSQLから構文の情報をXMLに出力します。
   sqlparser -o xml -i -f <SQLファイルパス> -u <XMLファイルパス>
2. XMLファイルをgraphvizのdotファイルに変換します
   ruby proofofconcept2.rb -x <XMLファイルパス> > <dotファイルパス>
3. graphvizでdotファイルから画像(pngファイル)を作成します。
   dot -T png <dotファイルパス> -o <pngファイルパス>

sqlparserはTSQLScriptDomParserから。
proofofconcept2はSQLVisualizeから入手できます。

sqlparserの使い方はここや、ここを見てください。