{"id":843,"date":"2023-08-04T10:23:54","date_gmt":"2023-08-04T17:23:54","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/semantic-kernel\/?p=843"},"modified":"2023-09-28T07:17:51","modified_gmt":"2023-09-28T14:17:51","slug":"use-natural-language-to-execute-sql-queries","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/agent-framework\/use-natural-language-to-execute-sql-queries\/","title":{"rendered":"Use natural language to execute SQL queries"},"content":{"rendered":"<h2><a href=\"https:\/\/devblogs.microsoft.com\/semantic-kernel\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternlarge.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-89\" src=\"https:\/\/devblogs.microsoft.com\/semantic-kernel\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternlarge.png\" alt=\"Image skpatternlarge\" width=\"1638\" height=\"136\" srcset=\"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternlarge.png 1638w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternlarge-300x25.png 300w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternlarge-1024x85.png 1024w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternlarge-768x64.png 768w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternlarge-1536x128.png 1536w\" sizes=\"(max-width: 1638px) 100vw, 1638px\" \/><\/a><\/h2>\n<p>We&#8217;ve heard from many in the community who want to use Semantic Kernel to query their relational database using natural language expressions. We are excited to share this sandbox that enables you explore the capabilities of LLM to generate SQL queries (or SELECT statements): <em>NL2SQL<\/em>. This has been an area of interest for years (<a href=\"https:\/\/paperswithcode.com\/dataset\/wikisql\">WikiSQL<\/a>, <a href=\"https:\/\/yale-lily.github.io\/spider\">Spider<\/a>, etc.). While alternative approaches such as local models or semantic search services exist, the focus here is to zoom into the natural abilities (and limitations) of GPT-4 to produce relevant SQL queries. We will share our approach, learnings and some best practices.<\/p>\n<p>Let&#8217;s dive right in, check out the <a href=\"https:\/\/aka.ms\/sk-nlsql-blog-video\">video<\/a> to view the sandbox in action.\n<iframe src=\"https:\/\/aka.ms\/sk-nlsql-blog-video\" width=\"600px\" height=\"400px\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" data-linktype=\"external\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<h3><strong>Approach<\/strong><\/h3>\n<p>Some basic principles that informed our approach:<\/p>\n<ul>\n<li>Synchronizing an existing database to vector-storage is a non-starter as there is no desire to introduce consistency considerations or any type of data-movement.<\/li>\n<li>Injecting data into the prompt-frame is also a non-starter (due to the token limit).<\/li>\n<li>Prompts cannot be hardcoded to a specific database schema or platform.<\/li>\n<li>Must discriminate across multiple schemas (in order to support multiple data-sources or to decompose a large schema).<\/li>\n<\/ul>\n<p>Since GPT-4 demonstrates significantly higher capability for SQL generation (GPT-3.5-turbo generated valid SQL ~70% less than GPT-4 in our limited testing), this is the model we\u2019ve targeted.<\/p>\n<h5><strong>Schema<\/strong><\/h5>\n<p>To start, we use an abbreviated object model to express schema meta-data for the model to reason over:<\/p>\n<p><a href=\"https:\/\/github.com\/microsoft\/semantic-sql-sandbox\/tree\/main\/nl2sql.library\/Schema\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-862\" src=\"https:\/\/devblogs.microsoft.com\/semantic-kernel\/wp-content\/uploads\/sites\/78\/2023\/07\/sql-sandbox-schema.png\" alt=\"Image sql sandbox schema\" width=\"612\" height=\"171\" srcset=\"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/07\/sql-sandbox-schema.png 612w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/07\/sql-sandbox-schema-300x84.png 300w\" sizes=\"(max-width: 612px) 100vw, 612px\" \/><\/a><\/p>\n<p>A well-designed schema is generally imbued with semantic intention via the table and column names. If needed, an optional description can be included for additional semantic clarity.<\/p>\n<p>Column meta-data is highly abbreviated to include just name, reference (Foreign Key) and description. Providing a full-fidelity schema model, besides being incredibly token rich, just doesn\u2019t demonstrate functional improvement for the classes of objectives we explored.<\/p>\n<p>The schema model includes platform direction in order for the model to produce the correct SQL variant (since SQL is notoriously vendor specific). Platforms explored include:<\/p>\n<ul>\n<li>Microsoft SQL Server<\/li>\n<li>MySql<\/li>\n<li>PostgreSQL<\/li>\n<li>SqlLite<\/li>\n<\/ul>\n<p>Selecting a compact and expressive format to express this schema within the prompt is critical since everything in the prompt counts towards the token-limit. We ended up favoring YAML since it lends itself to the inclusion of description meta-data and appears to be well understood by the model.\u00a0 (Example: <a href=\"https:\/\/github.com\/microsoft\/semantic-memory\/blob\/main\/samples\/008-dotnet-nl2sql\/nl2sql.config\/schema\/AdventureWorksLT.yaml\">AdventureWorksLT.yaml<\/a>)<\/p>\n<h5><strong>Query Generation<\/strong><\/h5>\n<p><a href=\"https:\/\/github.com\/microsoft\/semantic-sql-sandbox\/blob\/main\/nl2sql.library\/SqlQueryGenerator.cs\"><img decoding=\"async\" class=\"size-full wp-image-850 alignright\" src=\"https:\/\/devblogs.microsoft.com\/semantic-kernel\/wp-content\/uploads\/sites\/78\/2023\/07\/sql-sandbox-process-1.png\" alt=\"Image sql sandbox process\" width=\"281\" height=\"420\" srcset=\"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/07\/sql-sandbox-process-1.png 281w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/07\/sql-sandbox-process-1-201x300.png 201w\" sizes=\"(max-width: 281px) 100vw, 281px\" \/><\/a><\/p>\n<p>Schema expressions are stored in volatile semantic memory. We use the objective to retrieve the top-ranked schema for use in two sequential prompts:<\/p>\n<ol>\n<li><a href=\"https:\/\/github.com\/microsoft\/semantic-memory\/tree\/main\/examples\/200-dotnet-nl2sql\/nl2sql.config\/nl2sql\/isquery\"><em>IsQuery<\/em><\/a>: Screen whether objective is solvable with a SELECT statement against the schema.<\/li>\n<\/ol>\n<ol start=\"2\">\n<li><a href=\"https:\/\/github.com\/microsoft\/semantic-memory\/tree\/main\/examples\/200-dotnet-nl2sql\/nl2sql.config\/nl2sql\/generatequery\"><em>GenerateQuery<\/em><\/a>: Performs the query generation given the schema and objective.<\/li>\n<\/ol>\n<p>Both prompts are intentionally low\/no-shot approaches in the interest of exposing core model capability. Attempting to marginally tune the prompts with additional training shots doesn\u2019t further this goal (at the cost of consuming tokens that are needed for schema expression).<\/p>\n<p>Attempts to consolidate screening and query generation into a single prompt invariably resulted in failed screening for reference objectives such as:<\/p>\n<pre style=\"padding-left: 40px;\">list all databases<\/pre>\n<p>Experimenting with more complex prompting resulted in significant latency, increases cost, with marginal functional impact on query generation.<\/p>\n<h3><strong>Usage<\/strong><\/h3>\n<p>As with any LLM processing, it is possible to experience false-positive (bad query) and false-negative (no query) behaviors particularly in response to ambiguous or conflicting direction.<\/p>\n<p>One aspect that comes to light rather quickly is how the model (naturally) responds to semantic nuance and clarity.<\/p>\n<p>An objective such as:<\/p>\n<pre style=\"padding-left: 40px;\">what year had the <strong>most sales\r\n<\/strong><\/pre>\n<p>evaluates the COUNT of product sales:<\/p>\n<pre style=\"padding-left: 40px;\">SELECT TOP 1 YEAR(OrderDate) AS SaleYear, <strong>COUNT(SalesOrderID)<\/strong> AS TotalSales\r\nFROM SalesLT.SalesOrderHeader\r\nGROUP BY YEAR(OrderDate)\r\nORDER BY TotalSales DESC;<\/pre>\n<p>Where:<\/p>\n<pre style=\"padding-left: 40px;\">what year had the <strong>highest sales amount\r\n<\/strong><\/pre>\n<p>provides the semantic cue that SUM is appropriate.<\/p>\n<pre style=\"padding-left: 40px;\">SELECT TOP 1 YEAR(OrderDate) AS Year, <strong>SUM(TotalDue)<\/strong> AS TotalSales\r\nFROM SalesLT.SalesOrderHeader\r\nGROUP BY YEAR(OrderDate)\r\nORDER BY TotalSales DESC;<\/pre>\n<p>Note: objective need not be expressed as a question:<\/p>\n<pre style=\"padding-left: 40px;\">year with <strong>highest sales amount<\/strong><\/pre>\n<p>Clarifying implied context can also increase clarity:<\/p>\n<pre style=\"padding-left: 40px;\">list categories for <strong>May<\/strong><\/pre>\n<p>is rejected, versus:<\/p>\n<pre style=\"padding-left: 40px;\">list categories for <strong>user named May\r\n<\/strong><\/pre>\n<pre style=\"padding-left: 40px;\">SELECT B.B1\r\nFROM dbo.A\r\nJOIN dbo.C ON dbo.A.A0 = dbo.C.C1\r\nJOIN dbo.B ON dbo.C.C2 = dbo.B.B0\r\nWHERE dbo.A.A1 = 'May'\r\n<\/pre>\n<p>This principal extends to describing the shape of the resulting dataset\u2026inviting a pseudo-query language of sorts:<\/p>\n<pre style=\"padding-left: 40px;\">list categories for user named May include id and full user name<\/pre>\n<pre style=\"padding-left: 40px;\">SELECT \r\n  A.A0 AS UserID, A.A1 + ' ' + A.A2 AS FullName,\r\n  B.B0 AS CategoryID,\r\n  B.B1 AS CategoryName\r\nFROM dbo.A\r\nJOIN dbo.C ON A.A0 = C.C1\r\nJOIN dbo.B ON B.B0 = C.C2\r\nWHERE A.A1 = 'May'<\/pre>\n<p>which shows the previous result hid two different users named \u201cMay\u201d.<\/p>\n<p>How deeply is the model able to reason over the schema:<\/p>\n<pre style=\"padding-left: 40px;\">Summarize sales by month <strong>before tax<\/strong> and <strong>after tax<\/strong> (show name of month)<\/pre>\n<pre style=\"padding-left: 40px;\">SELECT\r\n  MONTH(OrderDate) AS MonthNumber,\r\n  DATENAME(MONTH, OrderDate) AS MonthName,\r\n  SUM(<strong>SubTotal<\/strong>) AS SalesBeforeTax,\r\n  SUM(<strong>TotalDue<\/strong>) AS SalesAfterTax\r\nFROM SalesLT.SalesOrderHeader\r\nGROUP BY MONTH(OrderDate), DATENAME(MONTH, OrderDate)\r\nORDER BY MonthNumber;<\/pre>\n<p>This is quite impressive; all based on semantic inference of schema naming. This also implies that a database with unusual or non-intuitive design aspects may reduce model clarity. For example, should a \u2018SubTotal\u2019 or &#8216;TotalDue&#8217; column be defined as text (instead of numeric) the model may apply numerical functions such as SUM based on semantic understanding (and provide an invalid query).<\/p>\n<p>What about type? We don&#8217;t include type meta-data in the YAML.<\/p>\n<pre style=\"padding-left: 40px;\">list all products with price greater than <strong>$3<\/strong><\/pre>\n<pre style=\"padding-left: 40px;\">SELECT ProductID, Name, ListPrice\r\nFROM SalesLT.Product\r\nWHERE ListPrice &gt; <strong>3<\/strong>;\r\n<\/pre>\n<p>The model is savvy enough to not include the dollar sign.\u00a0 The following is equivalent:<\/p>\n<pre style=\"padding-left: 40px;\">list all products with price greater than <strong>3 dollars<\/strong><\/pre>\n<p>but this objective:<\/p>\n<pre style=\"padding-left: 40px;\">list all products with price greater than abc<\/pre>\n<p>is rejected. For this case, the model reasons that this objective just doesn\u2019t make sense.\u00a0 However, we are able to force the model to generate invalid SQL with:<\/p>\n<pre style=\"padding-left: 40px;\">list all products with price &gt; abc<\/pre>\n<pre style=\"padding-left: 40px;\">SELECT * FROM SalesLT.Product WHERE ListPrice &gt; abc;<\/pre>\n<p>Curiously, adding literal delimeters is sufficient for the model to again reject:<\/p>\n<pre style=\"padding-left: 40px;\">list all products with price &gt; 'abc'<\/pre>\n<p>The model is limited to reasoning over the shape of the data and not able to semantically reason over the data itself. For instance, an objective such as:<\/p>\n<pre style=\"padding-left: 40px;\">list interest category related to food include id and name<\/pre>\n<pre style=\"padding-left: 40px;\">SELECT B0, B1\r\nFROM dbo.B\r\nWHERE B1 LIKE '%food%'<\/pre>\n<p>is not able to recognize that \u201cDiners\u201d and \u201cIce Cream\u201d are indeed related to food.<\/p>\n<p>Solving complex data objectives exceeds the capability of query generation alone and invites multi-step strategy (planner) approach.<\/p>\n<h3><strong>Best practices<\/strong><\/h3>\n<p>Standard best practices apply (as with any application\/service data access pattern):<\/p>\n<ul>\n<li>Least privilege \u2013 Restrict to read-only access on relevant tables or views and utilize column and row-level security as appropriate.<\/li>\n<li>Credential management \u2013 Do not expose secrets and connection strings.<\/li>\n<li>Injection prevention \u2013 Never directly inject user-input into SQL statements.<\/li>\n<\/ul>\n<p>Avoid inadvertent disclosure by capturing\/describing database schema at design-time to allow for review\/refinement.\u00a0 This approach aligns with least privilege as describing schema requires higher elevation than those needed to query data.<\/p>\n<p>Restrict access only to the desired data.\u00a0 Do not rely on schema definition or query criteria to control access.<\/p>\n<h2>Next Steps:<\/h2>\n<p>Get started exploring this space in the sandbox:<a href=\"http:\/\/aka.ms\/sk-nl2sql\"> aka.ms\/sk-nl2sql<\/a><\/p>\n<p>Join the community and let us know what you think: <a href=\"http:\/\/aka.ms\/sk-community\">aka.ms\/sk-community<\/a><\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-92\" src=\"https:\/\/devblogs.microsoft.com\/semantic-kernel\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternsmallbw.png\" alt=\"Image skpatternsmallbw\" width=\"1211\" height=\"137\" srcset=\"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternsmallbw.png 1211w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternsmallbw-300x34.png 300w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternsmallbw-1024x116.png 1024w, https:\/\/devblogs.microsoft.com\/agent-framework\/wp-content\/uploads\/sites\/78\/2023\/03\/skpatternsmallbw-768x87.png 768w\" sizes=\"(max-width: 1211px) 100vw, 1211px\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>We&#8217;ve heard from many in the community who want to use Semantic Kernel to query their relational database using natural language expressions. We are excited to share this sandbox that enables you explore the capabilities of LLM to generate SQL queries (or SELECT statements): NL2SQL. This has been an area of interest for years (WikiSQL, [&hellip;]<\/p>\n","protected":false},"author":124422,"featured_media":988,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,1],"tags":[21,9,22,23],"class_list":["post-843","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-samples","category-semantic-kernel","tag-data","tag-semantic-kernel","tag-sql","tag-the-schillace-laws"],"acf":[],"blog_post_summary":"<p>We&#8217;ve heard from many in the community who want to use Semantic Kernel to query their relational database using natural language expressions. We are excited to share this sandbox that enables you explore the capabilities of LLM to generate SQL queries (or SELECT statements): NL2SQL. This has been an area of interest for years (WikiSQL, [&hellip;]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/posts\/843","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/users\/124422"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/comments?post=843"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/posts\/843\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/media\/988"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/media?parent=843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/categories?post=843"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/agent-framework\/wp-json\/wp\/v2\/tags?post=843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}