Skip to main content

Using AI to query data (Alpha)

note

This is an Alpha feature. We encourage you to provide feedback via Slack or by emailing support@motherduck.com.

You can leverage MotherDuck's new AI capabilities to assist you in data analysis. You can now ask MotherDuck to:

These capabilities are provided by MotherDuck's integration with OpenAI.

MotherDuck AI operates on your current database by evaluating the schemas and contents of the database. To point MotherDuck AI at a specific database, execute the use database command (learn more about switching databases).

We use MotherDuck's sample Hacker News dataset to demonstrate these capabilities.

Answer questions about your data​

You can ask MotherDuck business questions about your data in plain English using the prompt query pragma. Under the hood, MotherDuck generates, iteratively corrects, and executes SQL on your behalf, returning results of the query.

Syntax​

pragma prompt_query("<natural language question>")

Example usage​

pragma prompt_query("what are the top domains being shared on hacker_news?")

Generate SQL​

You can ask MotherDuck AI in plain English to write SQL for you using the prompt_sql table function.

Syntax​

CALL prompt_sql("<natural language question>");

Example usage​

CALL prompt_sql("what are the top domains being shared on hacker_news?");

Output of this SQL statement is (formatted):

SELECT COUNT(*) as domain_count, 
SUBSTRING(SPLIT_PART(url, '//', 2), 1, POSITION('/' IN SPLIT_PART(url, '//', 2)) - 1) as domain 
FROM hn.hacker_news
WHERE url IS NOT NULL GROUP BY domain ORDER BY domain_count DESC LIMIT 10;

Fix up your query​

You can ask MotherDuck AI to correct and fix up your query using the prompt_fixup table function. This feature is especially useful to fix SQL statements that might not comform to DuckDB SQL standards or may not be using correct metadata (e.g. misspelled table names).

Syntax​

CALL prompt_fixup("<SQL query>");

Example usage​

CALL prompt_fixup("SEELECT COUNT(*) as domain_count FROM hn.hackers");

Output of this SQL statement is:

SELECT COUNT(*) as domain_count FROM hn.hacker_news

Explain a query​

You can ask MotherDuck AI to explain a query using the prompt_explain table function.

Syntax​

CALL prompt_explain("<SQL query>");

Example usage​

CALL prompt_explain("
SELECT COUNT(*) as domain_count, 
SUBSTRING(SPLIT_PART(url, '//', 2), 1, POSITION('/' IN SPLIT_PART(url, '//', 2)) - 1) as domain 
FROM hn.hacker_news
WHERE url IS NOT NULL GROUP BY domain ORDER BY domain_count DESC LIMIT 10;
");

The generated explanation for the query above is:

The query is running against a table called `hacker_news` under the schema `hn`. This table has a column named `url`.
The purpose of the query is to find the top 10 most frequently mentioned domains in the URLs present in the `hacker_news` table.

Describe contents of a database​

You can ask MotherDuck AI to describe to you contents of your current database using the prompt_schema table function.

Syntax​

CALL prompt_schema();

Running the above query while the current/active database is MotherDuck's sample data database returns:

The database contains data related to various domains such as hacker news, ambient air quality, yellow cab trips in NYC, FHVHV trip data in NYC, and service requests to the 311 system in NYC.