CREATE CONTEXT - Kinetica Docs

Creates a new SQL-GPT context. The context can contain one or more base tables, a set of sample question/answer pairs, and a set of rules for the table to aid in generating appropriate SQL. While the tables, samples, and rules clauses are all optional, a context must contain at least one clause of any kind to be considered valid.

CREATE CONTEXT Syntax

CREATE [OR REPLACE] [TEMP] CONTEXT [<schema name>.]<context name>
[<table definition clause>[,...],]
[<sample definition clause>,]
[<rule definition clause>]
[WITH OPTIONS (ttl = '<ttl value>'[,...])]

Parameters

OR REPLACE

Any existing context reference with the same name will be dropped before creating this one

TEMP

The context will be a temporary one; which means it will not be persisted (if the database is restarted, the context will be removed) and, if not continually accessed within the specified TTL, will be removed.

Name of the schema containing the context

Name of the context

Optional clause containing the definitions of all tables to be used within the context

Optional clause containing sample question/answer pairs that can help SQL-GPT understand the relationships between natural language and the tables within the context

Optional clause containing global rules SQL-GPT should take into account when generating SQL from the tables within the context

WITH OPTIONS

Optional indicator that a comma-delimited list of option/value assignments will follow. The following options are available:

Option	Description
`ttl`	Time-to-live, in minutes, for the context. If the context is not accessed for this amount of time, it will be automatically removed from the database. If `TEMP` is not specified, this option will be ignored.

Table Definition Clause

The table definition clause provides information about a single table to SQL-GPT. All parameters are optional except for the table name. The table definition clause itself is also optional.

Kinetica will pass the all columns of the table to SQL-GPT regardless of whether they are named in the COMMENTS or RULES sections. To avoid consuming unnecessary space in the SQL-GPT context with the unused columns, create a view with descriptive column names containing only the columns needed for generating SQL.

Table Definition Clause Syntax

(
    TABLE = [<schema name>.]<table name>
    [COMMENT = '<table comment>']
    [
        COMMENTS =
        (
            <column name> = '<column comment>',
            ...
            <column name> = '<column comment>'
        )
    ]
    [
        RULES =
        (
            '<table rule>',
            ...
            '<table rule>'
        )
    ]
)

Parameters

TABLE

Specifier for the mandatory table definition table name

Name of the schema containing the table to include in the context

Name of the table to include in the context

COMMENT

Specifier for an optional table comment

Note about the table that can give SLQ-GPT information about its use and the data it holds

COMMENTS

Specifier for an optional comma-separated list of column name & comment pairs

Name of a column in the given table

Note about the column that can give SQL-GPT information about its use and the specific data it holds

RULES

Specifier for an optional comma-separated list of rules for SQL-GPT to use in employing this table to generate SQL

Rule for SQL-GPT to use in employing this table to generate SQL; e.g., which columns should be considered (or not considered) when generating SQL using the table

Sample Definition Clause

A sample definition contains a set of question & answer pairs used to train SQL-GPT for generating SQL. Few (if any) samples should be required to customize SQL-GPT for a use case. The sample definition clause is optional.

A sample definition should reference tables that are included in table definitions within either its own context or a separate context that is passed along with it into a GENERATE SQL or EXECUTE_AI_QUERY command. This ensures that SQL-GPT has a proper definition (and understanding) of the tables and columns referenced in each sample query.

Sample Definition Clause Syntax

(
    SAMPLES =
    (
        '<question>' = '<SQL answer>',
        ...
        '<question>' = '<SQL answer>'
    )
)

Parameters

SAMPLES

Sample definition specifier keyword

Natural-language question that will be answered by the corresponding <SQL answer> query

SQL query that answers <question> using the tables contained within the context

Quoted constants within the query will need to be escaped as per standard SQL convention; e.g., 'lamp' in the following:

'How many lamps?' = 'SELECT COUNT(*) FROM product WHERE type = ''lamp'';'

Rule Definition Clause

A rule definition contains a set of global rules that SQL-GPT should use in employing the various tables named in the context to generate SQL. By convention, rules that apply to a specific table belong in the RULES section of the table definition, while rules that apply across tables or independently from any tables belong here. The rule definition clause is optional.

A rule definition should reference tables that are included in table definitions within either its own context or a separate context that is passed along with it into a GENERATE SQL or EXECUTE_AI_QUERY command. This ensures that SQL-GPT has a proper definition (and understanding) of any tables and columns referenced in each rule.

Rule Definition Clause Syntax

(
    RULES =
    (
        '<global rule>',
        ...
        '<global rule>'
    )
)

Parameters

RULES

Global rule definition specifier keyword

Global rule for SQL-GPT to use in employing multiple tables to generate SQL (joins, etc.) or when referencing database features that are independent of any table (functions, etc.)

Example

The following example creates a SQL-GPT context with the following criteria:

Contains one table, atc_current_position, with:
- a description
- 4 column comments
- 1 rule suggesting avoidance of one of the columns
Contains one table, atc_points, with no extra definitions
Contains one table, airport_positions, with:
- a description
- 2 column comments
Contains 2 sample questions and their corresponding queries
Contains 1 global rule suggesting how the atc_current_position & atc_points tables should be joined together

CREATE CONTEXT Example

CREATE CONTEXT example.atc_ctx
(
	TABLE = example.atc_current_position
	COMMENT = 'Current position of aircraft.'
	COMMENTS =
	(
		callsign = 'Aircraft callsign',
		ts = 'timestamp of most recent data point.',
		x = 'longitude position',
		y = 'latitude position'
	)
	RULES =
	(
		'Do not use the id column of the atc_current_position table'
	)
),
(
	TABLE = example.atc_points
),
(
	TABLE = example.airport_positions
	COMMENT = 'International airports and the centroid of their locations'
	COMMENTS =
	(
		airport_code = 'Name of airport',
		airport_centroid = 'Center of location of airport'
	)
),
(
	SAMPLES =
	(
		'What are the tracks for callsign UAL246?' =
		'
			SELECT ts, x, y
			FROM example.atc_points
			WHERE callsign = ''UAL246''
		',

		'What is the centroid of DCA airport?' =
		'
			SELECT airport_centroid
			FROM example.airport_positions
			WHERE airport_code = ''DCA''
		'
	)
),
(
	RULES =
	(
		'
			Use the ST_CONTAINS() function to join the airport_centroid column of the
			airport_positions table with the x and y columns of the atc_current_position table
		'
	)
)

​Parameters

​Table Definition Clause

​Parameters

​Sample Definition Clause

​Parameters

​Rule Definition Clause

​Parameters

​Example

Parameters

Table Definition Clause

Parameters

Sample Definition Clause

Parameters

Rule Definition Clause

Parameters

Example