Introducing the new Oracle Digital Assistant SQL Dialog!

Oracle Digital Assistant release 22.12 brings us a ground-breaking new capability – SQL Dialog – which can translate a user’s natural language input into SQL queries, send the queries to a backend data source, and display the response. All of these without the need to create intents, utterances, or custom components!

How SQL Dialogs Work

SQL Dialogs mechanism is fundamentally different from the standard skill development, where we anticipate what the user may say by pre-defining intents, utterances, entities, and dialog flows and creating components for backend connectivity.

With SQL Dialogs we do not require any of the above! Surprising, right?

An SQL Dialog skill connects to a database to extract schema information and creates query entities, which are the new type of entities that model the SQL Dialog.

Each database table is then mapped to a query entity that contains attributes modeled after the table columns.

Once the entities are trained, we can perform queries using natural language. Those queries (utterances) resolve into Oracle meaning representation query language (OMRQL) queries. OMRQL queries are like SQL queries but are based on the canonical names of the object models (the query entities).

For example:

Let’s say that you import Emp and Dept tables from a data service, which results in Emp and Dept query entities. Immediately after you import the tables and train the skill, you can query the query entities using utterances like the following:

Show all Emp in dept 10

The above utterance is interpreted as an OMRQL.

The OMRQL is then converted into an SQL query

That will result in the data being fetched from the data source and presented to the user as part of the dialog.

SQL Dialogs brings an easy-to-add quick conversational layer on top of DB-based applications. It supports a large number of queries without having to create individual intents for each of them and reduces skill creation time from days to hours.

How to create an SQL Dialog Skill

Pre-Requisites

You need an existing database* and access to it, whether with basic authentication, or Oracle Wallet credentials.

Note: For Oracle Digital Assistant integration, a password must be at least 14 characters and no more than 64 characters, and it must contain at least one uppercase character, one lowercase character, and one number. It also can’t start with a digit.

* This version of SQL Dialogs supports integration only with Oracle database services, such as Oracle Enterprise Database Service or Oracle Autonomous Database.

Connect Digital Assistant to the Data Service

Before you can access a data service from any SQL Dialogs skill, you need to add a data service integration that enables Oracle Digital Assistant to access the data service. You only need one integration per data service.

In Oracle Digital Assistant, open the side menu, click Settings, click Additional Services, and click the Data tab. Click + Add Service.

For more details on the data service connection please check the documentation.

Create the SQL Dialog Skill

For SQL Dialog you need a skill with the visual dialog mode. YAML is not supported.

You now can import the database schema into SQL skills to create query entities, which enable users to query the database using natural language.

Create Query Entities

A query entity is a new type of entity that allows us to define the model that supports the SQL Dialog. The great thing about it is that we can automatically create them with Import from Data Service.

When we do this, we can select the data service, and then select the tables and attributes that we want to use in the skill. This will automatically create query entities for the tables we selected.

In the below case, we have 2 entities (Emp and Dept).

It sets the entity and attributes primary names based on the canonical names. The primary name is Emp (short for employee, but that is the actual database table name), and the same with Dept for Department.

This is an early model (requires more training data) that needs improvement but is enough to test the queries using the primary names for the entities and attributes.

But first, you must Train the model, and then, after it completes, you can click Test Queries to try out utterances, or click Preview to test in the conversation tester.

Because you are working with a minimal SQL dialog skill, you can train with either Trainer Ht or Trainer Tm.

However, after you add autocomplete suggestions, routing data, and custom training data, Trainer Tm produces more accurate results.

Check the below video to see the entire process!

Improve the model by adding names and synonyms

At this, moment the model allows us to perform basic queries where we use the canonical names, for example:

Show all Emp in dept 10

But in reality, people would probably use words like an employee, staff, team members, departments, offices, etc to refer to Emp and Dept.

To help SQL Dialogs skill associate natural language phrases with the underlying data structure we should add synonyms to the entity’s names and attributes.

This topic will be covered more in detail in an upcoming post, but you can have a look at the documentation for more info.

The dialog at work

As you can see below, once we improve the model, we can try more natural queries.

can you show me all the employees in the big apple?

The above layout is the default one, but we can fully customize it. The query entity has a configuration tab called Presentation where we can configure how to present the data.

Notice that on the SQL Dialogs tab, you can see the generated OMRQL and SQL for the utterance.

Up until now, there was no mention of Dialog flows! That is because there is no need to create flows, the SQL Dialog has an automated flow that does all the work.

Conclusion

SQL Dialogs brings us an intent-less framework where we can easily add a conversational layer on top of a database. This will open a wide variety of new possibilities, closing a gap between business and data, by allowing users to use natural language for their queries!