System and method for artificial intelligence based generation of database queries
A system and method for automatic generation of database queries using zero-shot, context-based machine learning may output and/or execute database queries and/or analytics insights or plots based on text prompts, and may include or involve: wrapping a text prompt to include database structure information; generating, by a large language model (LLM), a query based on the wrapped prompt, where the query may include one or more database operations; and extracting data or information items from a database based on the query. Some embodiments may include additional prompt or query processing operations such as, e.g., wrapping queries to include corresponding database operations, validating that queries do not include malicious or undesirable commands, and automatically performing appropriate computer actions based on generated queries. Some embodiments of the invention may relate to databases and text prompts describing user actions input to a computer and collected by a desktop data collection software.
1. A computerized method for automatic generation of database queries, the method comprising, using a computer processor:
wrapping a free text prompt, the free text prompt received from a remote computer system using a representational state transfer (REST) protocol, wherein the wrapped prompt includes database structure information, wherein the wrapping of the free text prompt comprises embedding the free text prompt in a first predefined string of a plurality of predefined strings, the first predefined string describing one or more of: table names, and column names;
generating, by a first large language model (LLM), a database query based on the wrapped prompt, the database query describing one or more database operations;
wrapping the database query, wherein the wrapped database query includes one or more instructions, the instructions corresponding to one or more of the database operations, wherein the wrapping of the database query comprises embedding the database query in a second predefined string of the plurality of predefined strings, the second predefined string including a text format representation of a chart's axis names and a chart's type;
generating, by a second LLM, one or more metadata items based on the wrapped database query:
extracting one or more data items from a database based on the database query; and
automatically plotting a chart on a user interface (UI) based on one or more of the extracted data items and one or more of the metadata items.
2. The computerized method of claim 1 , comprising:
calculating a count value based on the query; and
if the query includes one or more stop commands, or the count value is larger than a threshold, transmitting an alert to a remote computer.
3. The computerized method of claim 1 , wherein the query includes one or more structured query language (SQL) commands.
4. The computerized method of claim 1 , comprising:
selecting and executing a computer process based on one or more of the extracted data items.
5. The computerized method of claim 1 , wherein one or more of the database operations include at least one of: an identification of a chart type, and an identification of one or more axes.
6. The computerized method of claim 1 , comprising generating a create table prompt based on the wrapped prompts, wherein the create table prompt is input to the LLM for creating a table based on the query.
7. The computerized method of claim 1 , wherein the text prompt describes a plurality of user actions input to a computer, the user actions collected by a desktop data collection software.
8. A computerized system for automatic generation of database queries, the system comprising:
a memory,
and a computer processor configured to:
wrap a free text prompt, the free text prompt received from a remote computer system using a representational state transfer (REST) protocol, wherein the wrapped prompt includes database structure information, wherein the wrapping of the free text prompt comprises embedding the free text prompt in a first predefined string of a plurality of predefined strings, the first predefined string describing one or more of: table names, and column names;
generate, by a first large language model (LLM), a database query based on the wrapped prompt, the database query describing one or more database operations;
wrap the database query, wherein the wrapped database query includes one or more instructions, the instructions corresponding to one or more of the database operations, wherein the wrapping of the database query comprises embedding the database query in a second predefined string of the plurality of predefined strings, the second predefined string including a text format representation of a chart's axis names and a chart's type:
generate, by a second LLM, one or more metadata items based on the wrapped database query;
extract one or more data items from a database based on the database query; and
automatically plot a chart on a user interface (UI) based on one or more of the extracted data items and one or more of the metadata items.
9. The computerized system of claim 8 , wherein the processor is to:
calculate a count value based on the query; and
if the query includes one or more stop commands, or the count value is larger than a threshold, transmit an alert to a remote computer.
10. The computerized system of claim 8 , wherein the query includes one or more structured query language (SQL) commands.
11. The computerized system of claim 8 , wherein the processor is to select and execute a computer process based on one or more of the extracted data items.
12. The computerized system of claim 8 , wherein one or more of the database operations include at least one of: an identification of a chart type, and an identification of one or more axes.
13. The computerized system of claim 8 , wherein the processor is to generate a create table prompt based on the wrapped prompts, wherein the create table prompt is input to the LLM for creating a table based on the query.
14. The computerized system of claim 8 , wherein the text prompt describes a plurality of user actions input to a computer, the user actions collected by a desktop data collection software.
15. A computerized method for producing database queries, the method comprising, using a computer processor:
contextualizing a free text prompt, the free text prompt received from a remote computer system using a representational state transfer (REST) protocol, wherein the contextualized prompt includes database information, wherein the contextualizing of the free text prompt comprises embedding the free text prompt in a first predefined string of a plurality of predefined strings, the first predefined string describing one or more of: table names, and column names;
producing, by a first machine learning model, a database query based on the contextualized prompt, the database query including one or more database commands;
contextualizing the database query, wherein the contextualized database query includes one or more instructions, the instructions including one or more of the database commands, wherein the contextualizing of the database query comprises embedding the database query in a second predefined string of the plurality of predefined strings, the second predefined string including a text format representation of a chart's axis names and a chart's type;
producing, by a second machine learning model, one or more metadata items based on the contextualized database query:
output one or more data items from a database based on the query; and
automatically generating a chart on a user interface (UI) based on one or more of the outputted data items and one or more of the metadata items.