Introducing the prompt() Function: Use the Power of LLMs with SQL!

Use Case: Text Summarization

The prompt() function is a straightforward and intuitive scalar function.

Consider the following use case where we want to get summaries of a text column from our hacker_news example dataset. The following query will return a 5 word summary for each text in the table.

Copy code
SELECT by, text, timestamp, 
       prompt('summarize the comment in 5 words: ' || text) AS summary 
FROM hacker_news.hn

Note that we’re applying the prompt function to 100 rows and the processing time is about 2.8s. We run up to 256 requests to the model provider concurrently which significantly speeds up the processing compared to calling the model in an unparallelized Python loop.

The runtime scales linearly from here - expect 10k rows to take between 5-10 minutes in processing time and to consume ~10 compute units. This might appear slow relative to other SQL functions, however looping over the same data in Python without concurrency would take about 5 hours instead.

Use Case: Unstructured to Structured Data Conversion

The prompt() function can also generate structured outputs, using the struct and struct_descr parameters. This enables users to specify a struct of typed return values for the output, facilitating the integration of LLM-generated data into analytical workflows. The adherence to the provided struct schema is guaranteed - as we leverage OpenAI’s structured model outputs which use constrained decoding to constrain the model’s output to only valid tokens.

Below is an example that leverages this functionality to extract structured information, like topic, sentiment and a list of mentioned technologies from each comment in our sample of the hacker_news table. The result is stored as STRUCT type, which makes it easy to access each individual field in SQL.

Copy code
SELECT by, text, timestamp,
prompt(text,
  struct:={topic: 'VARCHAR', sentiment: 'INTEGER', technologies: 'VARCHAR[]'},
  struct_descr:={topic: 'topic of the comment, single word',
                 sentiment: 'sentiment of the post on a scale from 1 (neg) to 5 (pos)',
                 technologies: 'technologies mentioned in the comment'}) as my_output
FROM hn.hacker_news
LIMIT 100

In this query, the prompt function is applied to the text column from the dataset without contextualizing it in a prompt. Instead, it uses the struct and struct_descr parameter as follows:

• struct:={...}: Specifies the structure of the output, which includes:
  • topic: A string (VARCHAR) representing the main topic of the comment.
  • sentiment: An integer indicating the sentiment of the comment on a scale from 1 (negative) to 5 (positive).
  • technologies: An array of strings listing any technologies mentioned in the comment.
• struct_descr:={...}: While the model infers meaning from the struct field names above, struct_descr can be used optionally to provide more detailed field descriptions and guide the model into the right direction.

The final result includes the comment's main topic, sentiment score (ranging from 1 to 5), and any mentioned technologies. The resulting column can subsequently be unfolded super easily into individual columns.

Copy code
SELECT by, text, timestamp, my_output.* FROM my_struct_hn_table

For more advanced users that want to have full control over the JSON-Schema that is used to constrain the output, we provide the json_schema parameter, which will result in JSON-typed results rather than STRUCT-typed results.