Inference script

[inesriahi]

Hi!

I would like to try your repo with my own queries. I can see that there are inference scripts for the datasets you are supporting. I can't see a script that accepts queries from users. Is there sth like this or should I write it myself?

Thanks

[lihaoyang-ruc]

Yes, in order to run inference on your data, you should prepare your data in the form of the Spider dataset (https://yale-lily.github.io/spider), which requires the following files:

• tables.json: a JSON file describing the schema of your databases;
• database: a folder containing your databases (The DB engine should be SQLite);
• dev.json: a JSON file containing the queries from users;

In addition, if you already have gold SQLs and want to evaluate the performance of the model, a dev_gold.sql file is also needed to run the evaluation script.

The point I want to emphasize is that RESDSQL generates SQL based on the questions (i.e., users' queries) and database schema. If you only have the questions, RESDSQL will not work.

I hope my answer can help you.

[inesriahi]

Thank you for your response.

I want to give it the user's query and the tables schema as strings withough giving the database itself. Is it possible?

[lihaoyang-ruc]

Unfortunately, we don't provide such a script in this repo because our scripts combine preparing data, SQL decoding, and result evaluation.

However, it's possible and I will show you how to do it. Before you start, I don't recommend you use the NatSQL version of RESDSQL, because NatSQL is an intermediate presentation of SQL. You have to make an extra effort to translate the generated NatSQL into SQL, which requires some special post-processes.

First, prepare the input sequences in the following format: [Question][Tables + Columns][Foreign keys]. Here is an example input:

How many singers do we have? | singer : singer.singer_id , singer.name , singer.country , singer.age , singer.song_name | stadium : stadium.location , stadium.name , stadium.capacity , stadium.highest , stadium.lowest | concert : concert.theme , concert.year , concert.concert_id , concert.concert_name , concert.stadium_id | singer_in_concert : singer_in_concert.concert_id , singer_in_concert.singer_id | concert.stadium_id = stadium.stadium_id | singer_in_concert.singer_id = singer.singer_id | singer_in_concert.concert_id = concert.concert_id

Then, write a script to perform seq2seq inference using T5. Of course, you should load the tokenizer and model weights from our released T5 checkpoints (for more details about how to perform inference, please refer to the T5 document on HuggingFace: https://huggingface.co/docs/transformers/model_doc/t5).

Finally, the output sequence of T5 consists of two parts: the SQL skeleton and the actual SQL query, which are split by a delimiter |. Thus, the string after | is the predicted SQL. Here is an example output:

select count ( _ ) from _ | select count ( * ) from singer

[PoseidomWong]

I also need this inference script, strongly recommend adding it into the source code.

[PiranavS]

Hi @lihaoyang-ruc , as you had mentioned in the previous comment regarding running inference without script, if I just run seq2seq inference with your released t5 checkpoints I will not be using the cross encoder to rank and send in the relevant schema according to the paper. Is that the case?

[lihaoyang-ruc]

@PiranavS Yes, but I really wouldn't recommend just running T5. The main component (or contribution) of RESDSQL is the cross-encoder, which inherently improves performance. Therefore, it is better to run both stages during inference.

[PiranavS]

@lihaoyang-ruc Thanks for the reply. Will do.

[PiranavS]

Hi @lihaoyang-ruc , another small doubt. As you had mentioned earlier, the inference part for RESDSQL+RATSQL remains the same except for the fact that the output will be in RATSQL format and post processing must be done to convert it into executable format. I just wanted to confirm whether the input will be in the same format as mentioned earlier for just RESDSQL :[Question][Tables + Columns][Foreign keys]

[lihaoyang-ruc]

@PiranavS Hi, the NatSQL version does not add foreign keys in the input sequence because the JOIN ON clauses are removed in NatSQL. Here is an input example:

How many singers do we have? | singer : singer.singer_id , singer.name , singer.country , singer.age , singer.song_name , singer.* | singer_in_concert : singer_in_concert.singer_id , singer_in_concert.concert_id , singer_in_concert.* | stadium : stadium.location , stadium.name , stadium.capacity , stadium.highest , stadium.lowest , stadium.* | concert : concert.theme , concert.year , concert.concert_id , concert.concert_name , concert.stadium_id , concert.*

[PiranavS]

@lihaoyang-ruc Thank you !

[mellahysf]

@PiranavS @inesriahi Hey!
Can you share please (if DONE of course) the corresponding script for inference giving the user question and database, and that predicts the SQL query? (using RESDSQL + NatSQL AND/OR just RESDSQL).
Thank you so much!

[PiranavS]

I'm sorry, I didn't make any inference scripts @mellahysf.

[vaib26]

Can i expose it as a rest api service, I want to run both the stages(rank-cross encoder, text2sql model) together? if yes, then how?

[prattsoroco]

If someone has already written the script can they link it here pl? Could save a lot of people effort since all of us seem to be looking for the same thing.

[CyanMystery]

只支持sqlite数据库？？？像mysql这种都不行吗？还有mysql 的时间类型datetime这种好像也训练不了啊