paulfitz/mlsql

inferring sql queries from plain-text questions about tables

Infer SQL queries from plain-text questions and table headers.

Requirements:

• install docker
• install curl
• Make sure docker allows at least 3GB of RAM (see Docker>Preferences>Advanced or equivalent) for sqlova, or 5GB for irnet.

sqlova

This wraps up a published pretrained model for Sqlova (https://github.com/naver/sqlova/).

Fetch and start sqlova running as an api server on port 5050:

docker run --name sqlova -d -p 5050:5050 paulfitz/sqlova

Be patient, the image is about 4.2GB. Once it is running, it’ll take a few seconds to load models and then you can start asking questions about CSV tables. For example:

curl -F "csv=@bridges.csv" -F "q=how long is throgs neck" localhost:5050
# {"answer":[1800],"params":["throgs neck"],"sql":"SELECT (length) FROM bridges WHERE bridge = ?"}

This is using the sample bridges.csv included in this repo.

(For Postman users, the same request/reply would be sent/received like this)

Here are some examples of the answers and sql inferred for plain-text questions about this table:

With the players.csv sample from WikiSQL:

Some questions about iris.csv:

There are plenty of types of questions this model cannot answer (and that aren’t covered in the dataset it is trained on, or in the sql it is permitted to generate).

irnet

This wraps up a published pretrained model for IRNet (https://github.com/microsoft/IRNet). The model released so far isn’t Bert-flavored, and I haven’t completely nailed down all the details of running it, so don’t judge the model by playing with it here.

Fetch and start irnet running as an api server on port 5050:

docker run --name irnet -d -p 5050:5050 -v $PWD/cache:/cache paulfitz/irnet

Be super patient! Especially on the first run, when a few large models need to be downloaded and unpacked.

You can then ask questions of individual csv files as before, or several csv files (just repeat -F "csv=@fileN.csv") or a simple sqlite db with tables related by foreign keys. In this last case, the model can answer using joins.

curl -F "sqlite=@companies.sqlite" -F "q=what city is The Firm headquartered in?" localhost:5050
# Answer: SELECT T1.city FROM locations AS T1 JOIN organizations AS T2 WHERE T2.company = 1
curl -F "sqlite=@companies.sqlite" -F "q=who is the CEO of Omni Cooperative" localhost:5050
# Answer: SELECT T1.name FROM people AS T1 JOIN organizations AS T2 WHERE T2.company = 1
curl -F "sqlite=@companies.sqlite" -F "q=what company has Dracula as CEO" localhost:5050
# Answer: SELECT T1.company FROM organizations AS T1 JOIN people AS T2 WHERE T2.name = 1

(Note there’s no value prediction, so e.g. the where clauses are = 1 rather than something more useful).

Other models

I hope to track research in the area and substitute in models as they become available:

• WikiSQL leaderboard
• Spider leaderboard
• RAT-SQL
• Spider Schema GNN
• Is there any code for X-SQL?
• SyntaxSQL
• 2019 NL2SQL Challenge
• A term paper including a Sqlova reimplementation with tweaks: Search Like a Human: Neural Machine Translation for Database Search
• NL2SQL-BERT gives an example of how to add features derived from the table content to improve results.