Zenn
CareNet EngineersPublicationへの投稿
🐾

SQLパーサーを使用してみた

Kaz
2023/07/21に公開1件
C#
JavaScript
Node.js
Python
SQL
tech

はじめに

この記事ではいくつかのプログラミング言語でSQLパーサーを利用する方法について解説します。
SQLパーサーを使用することでSQLクエリを構成要素に分割したり、構文解析したり、クエリの改変・整形が可能になります。
具体的には以下のようなことが行えるようになります。

  • SQLクエリのフォーマッタ：SQLパーサーを使用してSQLクエリの整形を行います。一定のルールでSQLを整形することで可読性を向上させることができます。
  • クエリの検証：SQLパーサーを利用して実際にSQLを実行せずに構文的にSQLが正しいかを検証できます。
  • テーブルやカラムの調査：SQLパーサーを利用することで、クエリの構成要素を取得できます。これにより、使用しているテーブルやカラムの調査が容易になります。これは障害発生時の影響調査において活用できます。
  • クエリの改変：SQLパーサーを利用して元のクエリを一部改変して新しいクエリを作成することができます。例えば元のクエリに新しい条件を追加したり、カラムが似ている別のテーブルを生成するクエリを作成します。

これらは一部の例であり、SQLパーサーを利用することで、様々な場面での選択肢が広がります。

様々なSQLパーサー

名前 言語 備考
node-sql-parser JavaScript SQLステートメントを構文ツリーに変換可能。構文ツリーからSQLステートメントを作成可能
sqlparse Python SQLの解析、分割、フォーマッタをサポートしている
TSqlParser C# SqlServerの方言であるT-SQLを解析できる

node-sql-parser

JavaScriptでSQLステートメントを構文ツリーに変換したり、その逆が可能になっています。
Node.jsだけでなくブラウザで使用できるので、特定のプログラミング言語をインストールできない環境においても使用が可能になっています。

サイト
https://www.npmjs.com/package/node-sql-parser
https://github.com/taozhi8833998/node-sql-parser

対象DB

  • BigQuery
  • DB2
  • Hive
  • MariaDB
  • MySQL
  • PostgresQL
  • SQLite(developing)
  • TransactSQL
  • FlinkSQL

インストール方法

Node.jsで利用する場合

npm install node-sql-parser --save

or

yarn add node-sql-parser

ブラウザで利用する場合

<script src="https://unpkg.com/node-sql-parser/umd/index.umd.js"></script>

サンプル

SQLから構文木を作成する例
const { Parser } = require('node-sql-parser/build/mysql');
const parser = new Parser();
const sql = `
-- テーブルの作成
CREATE TABLE \`Users\` (
  \`userId\` varchar(12),
  \`nameKanji\` varchar(34) COMMENT '名前(漢字)',
  \`nameKana\` varchar(50) COMMENT '名前(カナ)',
  \`dateOfBirth\` date COMMENT '生年月日',
  \`createdDate\` datetime(6),
  PRIMARY KEY (\`userId\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザテーブル';

select * from Users;
`
const result = parser.astify(sql, {
  database: 'MySQL'
});

console.log(JSON.stringify(result, null, 2));

出力

[
  {
    "type": "create",
    "keyword": "table",
    "temporary": null,
    "if_not_exists": null,
    "table": [
      {
        "db": null,
        "table": "Users"
      }
    ],
    "ignore_replace": null,
    "as": null,
    "query_expr": null,
    "create_definitions": [
      {
        "column": {
          "type": "column_ref",
          "table": null,
          "column": "userId"
        },
        "definition": {
          "dataType": "VARCHAR",
          "length": 12
        },
        "resource": "column"
      },
      {
        "column": {
          "type": "column_ref",
          "table": null,
          "column": "nameKanji"
        },
        "definition": {
          "dataType": "VARCHAR",
          "length": 34
        },
        "resource": "column",
        "comment": {
          "type": "comment",
          "keyword": "comment",
          "symbol": null,
          "value": {
            "type": "single_quote_string",
            "value": "名前(漢字)"
          }
        }
      },
      {
        "column": {
          "type": "column_ref",
          "table": null,
          "column": "nameKana"
        },
        "definition": {
          "dataType": "VARCHAR",
          "length": 50
        },
        "resource": "column",
        "comment": {
          "type": "comment",
          "keyword": "comment",
          "symbol": null,
          "value": {
            "type": "single_quote_string",
            "value": "名前(カナ)"
          }
        }
      },
      {
        "column": {
          "type": "column_ref",
          "table": null,
          "column": "dateOfBirth"
        },
        "definition": {
          "dataType": "DATE"
        },
        "resource": "column",
        "comment": {
          "type": "comment",
          "keyword": "comment",
          "symbol": null,
          "value": {
            "type": "single_quote_string",
            "value": "生年月日"
          }
        }
      },
      {
        "column": {
          "type": "column_ref",
          "table": null,
          "column": "createdDate"
        },
        "definition": {
          "dataType": "DATETIME",
          "length": 6,
          "parentheses": true
        },
        "resource": "column"
      },
      {
        "constraint": null,
        "definition": [
          {
            "type": "column_ref",
            "table": null,
            "column": "userId"
          }
        ],
        "constraint_type": "primary key",
        "keyword": null,
        "index_type": null,
        "resource": "constraint",
        "index_options": null
      }
    ],
    "table_options": [
      {
        "keyword": "engine",
        "symbol": "=",
        "value": "INNODB"
      },
      {
        "keyword": "default charset",
        "symbol": "=",
        "value": "utf8mb4"
      },
      {
        "keyword": "comment",
        "symbol": "=",
        "value": "'ユーザテーブル'"
      }
    ]
  },
  {
    "with": null,
    "type": "select",
    "options": null,
    "distinct": null,
    "columns": "*",
    "into": {
      "position": null
    },
    "from": [
      {
        "db": null,
        "table": "Users",
        "as": null
      }
    ],
    "where": null,
    "groupby": null,
    "having": null,
    "orderby": null,
    "limit": null,
    "locking_read": null,
    "window": null
  }
]
SQLの書き換え例：UsersをPersonsに書き換える
const { Parser } = require('node-sql-parser/build/mysql');
const parser = new Parser();
const sql = `
-- テーブルの作成
CREATE TABLE \`Users\` (
  \`userId\` varchar(12),
  \`nameKanji\` varchar(34) COMMENT '名前(漢字)',
  \`nameKana\` varchar(50) COMMENT '名前(カナ)',
  \`dateOfBirth\` date COMMENT '生年月日',
  \`createdDate\` datetime(6),
  PRIMARY KEY (\`userId\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザテーブル';

select * from Users;
select * from Logs;
`
const ast = parser.astify(sql, {
  database: 'MySQL'
});

function convertTableName(root, before, after) {
  if (!root) {
    return
  }
  for (const key of Object.keys(root)) {
    if (Array.isArray(root[key])) {
      for (const item of root[key]) {
        convertTableName(item, before, after)
      }
    }
    else if (typeof root[key] === 'object' && root[key] !== null) {
      convertTableName(root[key], before, after)
    }
    else {
      if (key === 'table') {
        if (root[key] === before) {
          root[key] = after
        } 
      }
    }
  }
}
convertTableName(ast, "Users", "Persons")
const changeSql = parser.sqlify(ast, {
  database: 'MySQL'
})
console.log(changeSql);

出力

CREATE TABLE `Persons` (`userId` VARCHAR(12), `nameKanji` VARCHAR(34) COMMENT '名前(漢字)', `nameKana` VARCHAR(50) COMMENT '名前(カナ)', `dateOfBirth` DATE COMMENT '生年月日', `createdDate` DATETIME(6), PRIMARY KEY (`userId`)) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = 'ユーザテーブル' ; SELECT * FROM `Persons` ; SELECT * FROM `Logs`
SQLに含まれるテーブルとカラムの列挙
const { Parser } = require('node-sql-parser/build/mysql');
const parser = new Parser();
const sql = `
-- テーブルの作成
CREATE TABLE \`Users\` (
  \`userId\` varchar(12),
  \`nameKanji\` varchar(34) COMMENT '名前(漢字)',
  \`nameKana\` varchar(50) COMMENT '名前(カナ)',
  \`dateOfBirth\` date COMMENT '生年月日',
  \`createdDate\` datetime(6),
  PRIMARY KEY (\`userId\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザテーブル';

select * from Users;
select userId from Users;
select id, createdTime from Logs;
`
const { tableList, columnList } = parser.parse(sql, {
  database: 'MySQL'
});
console.log(JSON.stringify(tableList, null, 2));
console.log(JSON.stringify(columnList, null, 2));

出力

[
  "create::null::Users",
  "select::null::Users",
  "select::null::Logs"
]
[
  "select::null::userId",
  "create::null::userId",
  "select::null::nameKanji",
  "create::null::nameKanji",
  "select::null::nameKana",
  "create::null::nameKana",
  "select::null::dateOfBirth",
  "create::null::dateOfBirth",
  "select::null::createdDate",
  "create::null::createdDate",
  "select::null::(.*)",
  "select::null::id",
  "select::null::createdTime"
]

使用感

容易にSQLの構文解析と、クエリの改変ができます。
ただ、Node.jsでフォーマッタを行いたい場合は、当ライブラリを使用するより、sql-formatterあるいはsql-parser-cst利用した方が楽だと思います。

実運用で使用している大量のテーブルの改変が必要になった時は、このライブラリを採用しました。

sqlparse

PythonでSQLの解析、分割、フォーマッタを行えるライブラリです。
デフォルトでは全てのsql構文には対応していませんが、SQLの解析を拡張することができます。

サイト
https://pypi.org/project/sqlparse/
https://github.com/andialbrecht/sqlparse
https://sqlparse.readthedocs.io/en/latest/

インストール方法

pip install sqlparse

サンプル

SQLの解析例
import sqlparse
sql = """
-- テーブルの作成
CREATE TABLE `Users` (
  `userId` varchar(12),
  `nameKanji` varchar(34) COMMENT '名前(漢字)',
  `nameKana` varchar(50) COMMENT '名前(カナ)',
  `dateOfBirth` date COMMENT '生年月日',
  `createdDate` datetime(6),
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザテーブル';
-- insert処理
INSERT Users VALUES(
    'user1',
    '田中',
    '太郎',
    '2000-01-01',
    '2023-07-01 10:10:15'
);
-- select処理
select * from Users;
-- joinの処理
select Users.id, Users.name, Codes.id from Users inner join Codes on Codes.id = Users.code;
-- サブクエリ
select count(*) from (
    select distinct Users.id from Users where Users.id > 10
) as t;
"""

def dump_sub_list(token, indent=0):
    if not token.is_group:
        return
    sub_list = []
    if isinstance(token, sqlparse.sql.IdentifierList):
        sub_list = token.get_identifiers()
    else:
        sub_list = token.get_sublists()
    for sub_token in sub_list:
        print(f"{' '*indent}type:{type(sub_token)} ttype: {sub_token.ttype} value:{sub_token.value} is_group: {sub_token.is_group}")
        dump_sub_list(sub_token, indent+2)

# SQLのステートメント毎に分割する
statements = sqlparse.split(sql)
for statement in statements:
    # ステートメントを解析する
    parsed_list = sqlparse.parse(statement)
    for parsed in parsed_list:
        print(f"get_type: {parsed.get_type()} type: {type(parsed)} ==========")
        tokens = parsed.tokens
        for token in tokens:
            print(f"type:{type(token)} type: {token.ttype}, value: {token.value}, is_group: {token.is_group}")
            dump_sub_list(token, 2)

出力例

get_type: CREATE type: <class 'sqlparse.sql.Statement'> ==========
type:<class 'sqlparse.sql.Comment'> type: None, value: -- テーブルの作成
, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Keyword.DDL, value: CREATE, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: TABLE, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: `Users`, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Parenthesis'> type: None, value: (
  `userId` varchar(12),
  `nameKanji` varchar(34) COMMENT '名前(漢字)',
  `nameKana` varchar(50) COMMENT '名前(カナ)',
  `dateOfBirth` date COMMENT '生年月日',
  `createdDate` datetime(6),
  PRIMARY KEY (`userId`)
), is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:`userId` is_group: True
  type:<class 'sqlparse.sql.IdentifierList'> ttype: None value:varchar(12),
  `nameKanji` is_group: True
    type:<class 'sqlparse.sql.Function'> ttype: None value:varchar(12) is_group: True
      type:<class 'sqlparse.sql.Identifier'> ttype: None value:varchar is_group: True
      type:<class 'sqlparse.sql.Parenthesis'> ttype: None value:(12) is_group: True
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:`nameKanji` is_group: True
  type:<class 'sqlparse.sql.Function'> ttype: None value:varchar(34) is_group: True
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:varchar is_group: True
    type:<class 'sqlparse.sql.Parenthesis'> ttype: None value:(34) is_group: True
  type:<class 'sqlparse.sql.IdentifierList'> ttype: None value:'名前(漢字)',
  `nameKana` is_group: True
    type:<class 'sqlparse.sql.Token'> ttype: Token.Literal.String.Single value:'名前(漢字)' is_group: False
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:`nameKana` is_group: True
  type:<class 'sqlparse.sql.Function'> ttype: None value:varchar(50) is_group: True
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:varchar is_group: True
    type:<class 'sqlparse.sql.Parenthesis'> ttype: None value:(50) is_group: True
  type:<class 'sqlparse.sql.IdentifierList'> ttype: None value:'名前(カナ)',
  `dateOfBirth` is_group: True
    type:<class 'sqlparse.sql.Token'> ttype: Token.Literal.String.Single value:'名前(カナ)' is_group: False
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:`dateOfBirth` is_group: True
  type:<class 'sqlparse.sql.IdentifierList'> ttype: None value:'生年月日',
  `createdDate` is_group: True
    type:<class 'sqlparse.sql.Token'> ttype: Token.Literal.String.Single value:'生年月日' is_group: False
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:`createdDate` is_group: True
  type:<class 'sqlparse.sql.IdentifierList'> ttype: None value:datetime(6),
  PRIMARY is_group: True
    type:<class 'sqlparse.sql.Function'> ttype: None value:datetime(6) is_group: True
      type:<class 'sqlparse.sql.Identifier'> ttype: None value:datetime is_group: True
      type:<class 'sqlparse.sql.Parenthesis'> ttype: None value:(6) is_group: True
    type:<class 'sqlparse.sql.Token'> ttype: Token.Keyword value:PRIMARY is_group: False
  type:<class 'sqlparse.sql.Parenthesis'> ttype: None value:(`userId`) is_group: True
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:`userId` is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: ENGINE, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Operator.Comparison, value: =, is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: InnoDB, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: DEFAULT, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: CHARSET, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Operator.Comparison, value: =, is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: utf8mb4, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: COMMENT, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Operator.Comparison, value: =, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Literal.String.Single, value: 'ユーザテーブル', is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Punctuation, value: ;, is_group: False
get_type: INSERT type: <class 'sqlparse.sql.Statement'> ==========
type:<class 'sqlparse.sql.Comment'> type: None, value: -- insert処理
, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Keyword.DML, value: INSERT, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: Users, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Values'> type: None, value: VALUES(
    'user1',
    '田中',
    '太郎',
    '2000-01-01',
    '2023-07-01 10:10:15'
), is_group: True
  type:<class 'sqlparse.sql.Parenthesis'> ttype: None value:(
    'user1',
    '田中',
    '太郎',
    '2000-01-01',
    '2023-07-01 10:10:15'
) is_group: True
    type:<class 'sqlparse.sql.IdentifierList'> ttype: None value:'user1',
    '田中',
    '太郎',
    '2000-01-01',
    '2023-07-01 10:10:15' is_group: True
      type:<class 'sqlparse.sql.Token'> ttype: Token.Literal.String.Single value:'user1' is_group: False
      type:<class 'sqlparse.sql.Token'> ttype: Token.Literal.String.Single value:'田中' is_group: False
      type:<class 'sqlparse.sql.Token'> ttype: Token.Literal.String.Single value:'太郎' is_group: False
      type:<class 'sqlparse.sql.Token'> ttype: Token.Literal.String.Single value:'2000-01-01' is_group: False
      type:<class 'sqlparse.sql.Token'> ttype: Token.Literal.String.Single value:'2023-07-01 10:10:15' is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Punctuation, value: ;, is_group: False
get_type: SELECT type: <class 'sqlparse.sql.Statement'> ==========
type:<class 'sqlparse.sql.Comment'> type: None, value: -- select処理
, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Keyword.DML, value: select, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Wildcard, value: *, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: from, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: Users, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Punctuation, value: ;, is_group: False
get_type: SELECT type: <class 'sqlparse.sql.Statement'> ==========
type:<class 'sqlparse.sql.Comment'> type: None, value: -- joinの処理
, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Keyword.DML, value: select, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.IdentifierList'> type: None, value: Users.id, Users.name, Codes.id, is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:Users.id is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:Users.name is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:Codes.id is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: from, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: Users, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: inner join, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: Codes, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: on, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Comparison'> type: None, value: Codes.id = Users.code, is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:Codes.id is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:Users.code is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Punctuation, value: ;, is_group: False
get_type: SELECT type: <class 'sqlparse.sql.Statement'> ==========
type:<class 'sqlparse.sql.Comment'> type: None, value: -- サブクエリ
, is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Keyword.DML, value: select, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Function'> type: None, value: count(*), is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:count is_group: True
  type:<class 'sqlparse.sql.Parenthesis'> ttype: None value:(*) is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: from, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: (
    select distinct Users.id from Users where Users.id > 10
) as t, is_group: True
  type:<class 'sqlparse.sql.Parenthesis'> ttype: None value:(
    select distinct Users.id from Users where Users.id > 10
) is_group: True
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:Users.id is_group: True
    type:<class 'sqlparse.sql.Identifier'> ttype: None value:Users is_group: True
    type:<class 'sqlparse.sql.Where'> ttype: None value:where Users.id > 10
 is_group: True
      type:<class 'sqlparse.sql.Comparison'> ttype: None value:Users.id > 10 is_group: True
        type:<class 'sqlparse.sql.Identifier'> ttype: None value:Users.id is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:t is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Punctuation, value: ;, is_group: False
SQLのフォーマット例
import sqlparse
sql = """
-- テーブルの作成
CREATE TABLE `Users` (
  `userId` varchar(12),
  `nameKanji` varchar(34) COMMENT '名前(漢字)',
  `nameKana` varchar(50) COMMENT '名前(カナ)',
  `dateOfBirth` date COMMENT '生年月日',
  `createdDate` datetime(6),
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザテーブル';
-- insert処理
INSERT Users VALUES(
    'user1',
    '田中',
    '太郎',
    '2000-01-01',
    '2023-07-01 10:10:15'
);
-- select処理
select * from Users;
-- joinの処理
select Users.id, Users.name, Codes.id from Users inner join Codes on Codes.id = Users.code;
-- サブクエリ
select count(*) from (
    select distinct Users.id from Users where Users.id > 10
) as t;
"""

result = sqlparse.format(
    sql,
    # https://sqlparse.readthedocs.io/en/latest/api/#formatting
    keyword_case = "capitalize",
    identifier_case = "lower",
    indent_width = 8,
    strip_comments = True
)
print(result)

出力

Create Table `users` (
  `userid` varchar(12),
  `namekanji` varchar(34) Comment '名前(漢字)',
  `namekana` varchar(50) Comment '名前(カナ)',
  `dateofbirth` date Comment '生年月日',
  `createddate` datetime(6),
  Primary Key (`userid`)
) Engine=innodb Default Charset=utf8mb4 Comment='ユーザテーブル';

Insert users Values(
    'user1',
    '田中',
    '太郎',
    '2000-01-01',
    '2023-07-01 10:10:15'
);

Select * From users;

Select users.id, users.name, codes.id From users Inner join codes On codes.id = users.code;

Select count(*) From (
    Select Distinct users.id From users Where users.id > 10
) As t;
独自のキーワードの追加例：
import sqlparse
from sqlparse import keywords
from sqlparse.lexer import Lexer

def dump_sub_list(token, indent=0):
    if not token.is_group:
        return
    sub_list = []
    if isinstance(token, sqlparse.sql.IdentifierList):
        sub_list = token.get_identifiers()
    else:
        sub_list = token.get_sublists()
    for sub_token in sub_list:
        print(f"{' '*indent}type:{type(sub_token)} ttype: {sub_token.ttype} value:{sub_token.value} is_group: {sub_token.is_group}")
        dump_sub_list(sub_token, indent+2)

sql = """select * from foo BACON SPAM EGGS;"""
print('**************:')
tokens = sqlparse.parse(sql)[0]
for token in tokens:
    print(f"type:{type(token)} type: {token.ttype}, value: {token.value}, is_group: {token.is_group}")
    dump_sub_list(token, 2)

print('**************:')
# BACON, SPAM, EGGSのtypeが表示されるはず
Lexer.get_default_instance().add_keywords(
    {
        "BACON": sqlparse.tokens.Name.Builtin,
        "SPAM": sqlparse.tokens.Keyword,
        "EGGS": sqlparse.tokens.Keyword,
    }
)
tokens = sqlparse.parse(sql)[0]
for token in tokens:
    print(f"type:{type(token)} ttype: {token.ttype}, value: {token.value}, is_group: {token.is_group}")
    dump_sub_list(token, 2)

出力

**************:
type:<class 'sqlparse.sql.Token'> type: Token.Keyword.DML, value: select, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Wildcard, value: *, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Keyword, value: from, is_group: False
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: foo BACON, is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:BACON is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> type: None, value: SPAM EGGS, is_group: True
  type:<class 'sqlparse.sql.Identifier'> ttype: None value:EGGS is_group: True
type:<class 'sqlparse.sql.Token'> type: Token.Punctuation, value: ;, is_group: False
**************:
type:<class 'sqlparse.sql.Token'> ttype: Token.Keyword.DML, value: select, is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Wildcard, value: *, is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Keyword, value: from, is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Identifier'> ttype: None, value: foo, is_group: True
type:<class 'sqlparse.sql.Token'> ttype: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Name.Builtin, value: BACON, is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Keyword, value: SPAM, is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Text.Whitespace, value:  , is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Keyword, value: EGGS, is_group: False
type:<class 'sqlparse.sql.Token'> ttype: Token.Punctuation, value: ;, is_group: False

使用感

容易にフォーマッタが使用できます。 また、字句解析の拡張が容易になっているので、特殊な構文をもつDBにも対応できるチャンスがあります。
ただし、木構造からのsqlの改変機能は存在せず、木構造の探索もnode-sql-parserより直感的にわかりづらいです。

TSqlParser

SQLServerの方言のT-SQLを解析するためのSQLパーサーのライブラリでMicrosoftが提供しています。
今回はC#での実験となりますが、.NETで使用できるので、VB.NETなどからも使用できると思います。

サイト
https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom.tsqlparser?view=sql-dacfx-161

インストール方法

NuGetパッケージ管理でMicrosoft.SqlServer.TransactSql.ScriptDomを参照する

NuGetパッケージ管理でMicrosoft.SqlServer.TransactSql.ScriptDomを参照する

サンプル

実験環境: .NET 5.0

TSQLの解析例：
using System;
using System.Collections.Generic;
using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace tsqlparser
{
    class SqlVisitor : TSqlFragmentVisitor
    {
        public override void Visit(TSqlFragment node)
        {
            Console.WriteLine("Type:{0}", node.GetType().Name);
            base.Visit(node);
        }
        public override void Visit(CreateTableStatement node)
        {
            Console.WriteLine("Create Table: " + node.SchemaObjectName.BaseIdentifier.Value);
            base.Visit(node);
        }
        public override void Visit(NamedTableReference node)
        {
            Console.WriteLine("Find Table Type:{0} Value:{1}", node.GetType().Name, node.SchemaObject.BaseIdentifier.Value);
            base.Visit(node);
        }
        public override void Visit(ColumnDefinition node)
        {
            Console.WriteLine("Find Column Type:{0} Value:{1}", node.GetType().Name, node.ColumnIdentifier.Value);
            base.Visit(node);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            string sql = @"--テスト
CREATE TABLE Users (
  userId varchar(12),
  nameKanji varchar(34),
  nameKana varchar(50),
  PRIMARY KEY (userId)
);
select * from Users;
-- TSQL方言
DECLARE @MyVar INT;
";
            // SQLServerのバージョンに対応するクラスを指定する
            // この例ではSQL Server 2016
            TSql130Parser parser = new TSql130Parser(true);
            IList<ParseError> errors;
            TSqlFragment fragment = parser.Parse(new System.IO.StringReader(sql), out errors);

            if (errors.Count > 0)
            {
                foreach (var error in errors)
                {
                    Console.WriteLine("{0}: {1}", error.Line , error.Message);
                }
            }
            else
            {
                // パースが成功した場合の処理を書く
                // 例えば、以下では全てのSQLトークンを出力します
                var visitor = new SqlVisitor();
                fragment.Accept(visitor);
            }
        }
    }
}

出力

Type:TSqlScript
Type:TSqlBatch
Type:CreateTableStatement
Create Table: Users
Type:SchemaObjectName
Type:Identifier
Type:TableDefinition
Type:ColumnDefinition
Find Column Type:ColumnDefinition Value:userId
Type:Identifier
Type:SqlDataTypeReference
Type:SchemaObjectName
Type:Identifier
Type:IntegerLiteral
Type:ColumnDefinition
Find Column Type:ColumnDefinition Value:nameKanji
Type:Identifier
Type:SqlDataTypeReference
Type:SchemaObjectName
Type:Identifier
Type:IntegerLiteral
Type:ColumnDefinition
Find Column Type:ColumnDefinition Value:nameKana
Type:Identifier
Type:SqlDataTypeReference
Type:SchemaObjectName
Type:Identifier
Type:IntegerLiteral
Type:UniqueConstraintDefinition
Type:ColumnWithSortOrder
Type:ColumnReferenceExpression
Type:MultiPartIdentifier
Type:Identifier
Type:SelectStatement
Type:QuerySpecification
Type:SelectStarExpression
Type:FromClause
Type:NamedTableReference
Find Table Type:NamedTableReference Value:Users
Type:SchemaObjectName
Type:Identifier
Type:DeclareVariableStatement
Type:DeclareVariableElement
Type:Identifier
Type:SqlDataTypeReference
Type:SchemaObjectName
Type:Identifier

使用感

T-SQL専用なので、MySQLの構文などはエラーとなります。
ただ、Microsoftが提供しているライブラリなので、外部ライブラリの使用の制限が厳しい条件下においては導入しやすいかもしれません。

まとめ

今回は様々なプログラミング言語で使用できるSQLパーサーについて紹介しました。
SQLパーサーを使用することで、独自の静的解析の作成や、クエリの依存関係の調査が容易になります。
学習コストも高くはないので、SQLをよく使用する環境では、導入を検討してみては如何でしょうか。

CareNet Engineers
CareNet EngineersPublication

株式会社ケアネットのエンジニアブログです。CareNetサービスの技術情報を中心に記事を投稿しております。 各記事の内容は個人の意見であり、企業を代表するものではございません。

Discussion

gekalgekal

参考になりました。ありがとうございます。