Endpoints Objects

Description

This is an API to customise the NLSQL API. For the convenience of developers, NLSQL also offers a Python client called nlsql-api, which can be installed using the following command: [ shell> pip install nlsql-api ] Comprehensive documentation for the nlsql-api Python client, which is used to customise the NLSQL API, can be found at https://github.com/denissa4/nlsql-api

Base URL

The base URL for all API requests is: https://api.nlsql.com/v1/

Authentication

Get an authentication token. You must include the token in the Authorization header of all requests. Add the word Token and a space before the token itself:
'Authorization: Token {Your authentication token}'

Endpoints

GET /data-source/

Retrieves list of a data-sources names stored with a given user.

GET /data-source/{data_source_name}

Retrieves details of a data-sources stored with a given data-source name.

Path parameters
data_source_name string REQUIRED

The name of the data source.

Example: DataSource-1
GET /distinct_values

Retrieves details of a columns distinct values stored with a given table name and columns names.

Query parameters
table_name string REQUIRED

The table name.

Example: Table1
columns_names array[string] REQUIRED

Specifies the group of the columns names in database. Use ,| for separate values in array

Example: [ColumnName1,|ColumnName2,|ColumnName3]
POST /data-source/

Creates a new DataSource with data passed in the request body. In the response, it returns 201 status code and all DataSource names already existing.

Request body
name string REQUIRED

The name of the data source.

Example: DataSource-1
db_syntax integer REQUIRED

The type of database which is used.

Values:
1 - Google Spreadsheets,
2 - MySQL,
3 - MS SQL,
4 - CQL for Cassandra,
5 - PostgreSQL,
6 - Amazon Redshift SQL,
7 - BigQuery SQL,
8 - SQLite,
9 - Snowflake
platform integer

Represents how will use NLSQL

Values:
1 - Messenger's Bot,
2 - API default
suggestion_mode boolean

Activates the typing suggestion. Only available with custom web chat

default False
additional_regular_exp_symbols str

Additional special symbols contained in your database values. Written with a comma-separated. May be empty if you don't need them. Already-used symbols: , - /

Example for * and ^: *,^

default ""
storage_type str

Specify storage of DataBase distinct values

Values:
1 - Google Spreadsheet,
2 - Cosmos DB default
tables array[Obj]

List of table objects. See <obj:table>
joins array[Obj]

List of joins objects. See <obj:join>
PUT /data-source/

Recreates DataSource with a given DataSource name by passing values in the request body

Request body
name string REQUIRED

The name of the data source.

Example: DataSource-1
db_syntax integer REQUIRED

The type of database which is used.

Values:
1 - Google Spreasheets,
2 - MySQL,
3 - MS SQL,
4 - CQL for Cassandra,
5 - PostgreSQL,
6 - Amazon Redshift SQL,
7 - BigQuery SQL,
8 - SQLite,
9 - Snowflake
platform integer

Represents how will use NLSQL

Values:
1 - Messenger's Bot,
2 - API default
suggestion_mode boolean

Activates the typing suggestion. Only available with custom web chat

default False
additional_regular_exp_symbols str

Additional special symbols contained in your database values. Written with a comma-separated. May be empty if you don't need them. Already-used symbols: , - /

Example for * and ^: *,^

default ""
storage_type str

Specify storage of DataBase distinct values

Values:
1 - Google Spreadsheet,
2 - Cosmos DB default
tables array[Obj]

List of table objects. See <obj:table>
joins array[Obj]

List of joins objects. See <obj:join>
PUT /distinct_values

Creates/Updates Columns distinct values with a given Table table_name and Column label_name by passing values in the request body

Request body array[Obj]
table_name string REQUIRED

The table name

Example: test_table_name
columns array[Obj] REQUIRED

List of column distinct values objects. See <obj:distinct_value>

Objects


DataSource

Parameters
name string REQUIRED

The name of the data source.

Example: DataSource-1
db_syntax integer REQUIRED

The type of database which is used.

Values:
1 - Google Spreasheets,
2 - MySQL,
3 - MS SQL,
4 - CQL for Cassandra,
5 - PostgreSQL,
6 - Amazon Redshift SQL,
7 - BigQuery SQL,
8 - SQLite,
9 - Snowflake
platform integer

Represents how will use NLSQL

Values:
1 - Messenger's Bot,
2 - API default
suggestion_mode boolean

Activates the typing suggestion. Only available with custom web chat

default False
additional_regular_exp_symbols str

Additional special symbols contained in your database values. Written with a comma-separated. May be empty if you don't need them. Already-used symbols: , - /

Example for * and ^: *,^

default ""
storage_type str

Specify storage of DataBase distinct values

Values:
1 - Google Spreadsheet,
2 - Cosmos DB default
Table

Parameters
table_name string REQUIRED

The table name

Example: test_table_name
spreadsheet_link string

Link of the spreadsheet. Available only for DataSource.storage_type is 1 (Google Spreadsheet)

Example: 1C2GyVOR_X8dp4YIQ-_IbZ4cw5ClF7JCNcB
spreadsheet_sheet string

Sheet name which is used. Available only for DataSource.storage_type is 1 (Google Spreadsheet)

Example: Sheet1
is_first_line boolean

If the first line of example data in spreadsheets is a column name

defaultFalse
db_schema string

Database schema of used table

Example: test_schema

default""
columns array[Obj]

List of columns objects contains in the table. See <obj:column>
complex_arguments array[Obj]

List of complex arguments objects needed for the table. See <obj:complex_arguments>
column_arg array[Obj]

List of column arguments objects needed for the table. See <obj:column_arg>
Column

Parameters
label_name string REQUIRED

Name of the column in database

Example: ColumnName
default_label_name string

Name of the column in SpreadSheet. Available only for DataSource.storage_type is 1 (Google Spreadsheet)

Example: A
column_other_params [Obj]

Contains other column params. See <obj:column_other_params>
Column other params

Parameters
arguments array[Obj]

List of arguments objects. See <obj:arguments>
dates array[Obj]

List of dates objects. See <obj:dates>
top_arguments array[Obj]

List of top arguments objects. See <obj:top_arguments>
filters array[Obj]

List of filters objects. See <obj:filters>
maps_column array[Obj]

List of maps column objects. See <obj:maps_column>
Arguments

Parameters
argument string REQUIRED

Name of the argument

Example: sales
operation integer REQUIRED

The type of sql operation used for this argument.

Values:
1 - Total sum (SUM),
2 - Average value (AVG),
3 - Number of rows (COUNT),
4 - Value,
5 - Maximum value (MAX),
6 - Minimum value (MIN)
Dates

Parameters
date_argument string

Name of the date argument

Example: estimated date
is_default boolean REQUIRED

If set to True used this column by default for question with date. Only one default date column possible per table.

Top arguments

Parameters
top_argument string REQUIRED

Name of the top argument

Example: customer
Filters

Parameters
argument string

Name of the filter argument. If it not exists, the filter will be used for all queries

Example: closed
data_type integer REQUIRED

The data type of filter column

Values:
1 - String,
2 - Number,
3 - Date,
4 - List of strings,
5 - List of numbers
operator integer REQUIRED

Type of comparison operator

Values:
1 - Equal (=),
2 - Not equal (!=),
3 - Over than (>),
4 - Lower than (<),
value string REQUIRED

The value to be compared to the column values

Example: estimated date
Maps Column

Parameters
format integer REQUIRED

The type of data in map column

Values:
1 - Country names,
2 - USA-states,
3 - ISO-3
Complex Arguments

Parameters
argument string REQUIRED

Name of the complex argument.

Example: growth
column_1 string REQUIRED

The name of an existing column in the table.
column_2 string REQUIRED

The name of an existing column in the table.
operation_1 integer REQUIRED

The type of sql operation used for this column.
Represents in SQL operation_1(column_1)

Values:
1 - Total sum (SUM),
2 - Average value (AVG),
3 - Number of rows (COUNT),
4 - Maximum value (MAX),
5 - Minimum value (MIN)
6 - Value
operation_2 integer REQUIRED

The type of sql operation used for this column.
Represents in SQL operation_2(column_2)

Values:
1 - Total sum (SUM),
2 - Average value (AVG),
3 - Number of rows (COUNT),
4 - Maximum value (MAX),
5 - Minimum value (MIN)
6 - Value
operator integer REQUIRED

Type of operator
Represents in SQL operation_1(column_1) operator operation_2(column_2)
values 5 and 6 available only for columns with Date data type

Values:
1 - Division (/),
2 - Multiplication (*),
3 - Subtraction (-),
4 - Addition (+),
5 - Difference between two date values in days (DATEDIFF),
6 - Difference between two date values in hours (DATEDIFF),
filter_column_1 string

The name of an existing column in the table that will be filtered
filter_column_2 string

The name of an existing column in the table that will be filtered
data_type_1 integer

Data type of filter column

Values:
1 - String,
2 - Number,
3 - Date,
4 - Date Quarter,
5 - Date Month
data_type_2 integer

Data type of filter column

Values:
1 - String,
2 - Number,
3 - Date,
4 - Date Quarter,
5 - Date Month
filter_operation_1 integer

Used only when data_type_1 is 3(Date) Extracts the selected part of the date from column filter_column_1

Values:
1 - None,
2 - Month,
3 - Year
filter_operation_2 integer

Used only when data_type_2 is 3(Date) Extracts the selected part of the date from column filter_column_2

Values:
1 - None,
2 - Month,
3 - Year
filter_value_1 string

Used only when filter_operation_1 is 2(Month) or 3(Year)
Represents in SQL filter_operation_1(filter_column_1) = filter_value_1

filter_value_1_end string

Used only when filter_operation_1 is 2(Month) or 3(Year). If specified, create a period filter.
Represents in SQL filter_operation_1(filter_column_1) BETWEEN filter_value_1 AND filter_value_1_end

filter_value_2 string

Used only when filter_operation_2 is 2(Month) or 3(Year)
Represents in SQL filter_operation_2(filter_column_2) = filter_value_2

filter_value_2_end string

Used only when filter_operation_2 is 2(Month) or 3(Year). If specified, create a period filter.
Represents in SQL filter_operation_2(filter_column_2) BETWEEN filter_value_2 AND filter_value_2_end

filter_value_quarter_1 integer

Used only when data_type_1 is 4(Date Quarter) Compares the selected part of the date for column filter_column_1

Values:
1 - This quarter,
2 - Previous quarter,
3 - Q1,
4 - Q2,
5 - Q3,
6 - Q4,
7 - None
filter_value_quarter_2 integer

Used only when data_type_1 is 4(Date Quarter) Compares the selected part of the date for column filter_column_1

Values:
1 - This quarter,
2 - Previous quarter,
3 - Q1,
4 - Q2,
5 - Q3,
6 - Q4,
7 - None
filter_value_month_1 integer

Used only when data_type_1 is 5(Date Month) Compares the selected part of the date for column filter_column_1

Values:
1 - This month,
2 - Previous month,
3 - None
filter_value_month_2 integer

Used only when data_type_1 is 5(Date Month) Compares the selected part of the date for column filter_column_1

Values:
1 - This month,
2 - Previous month,
3 - None
filter_value_year_1 integer

Used only when data_type_1 is 4(Date Quarter) or 5(Date Month)
Represents in SQL QUARTER|MONTH(filter_column_1) = filter_value_month_1 AND YEAR(filter_column_1) = filter_value_year_1

Values:
1 - This year,
2 - Previous year,
3 - None
filter_value_year_2 integer

Used only when data_type_2 is 4(Date Quarter) or 5(Date Month)
Represents in SQL QUARTER|MONTH(filter_column_2) = filter_value_month_2 AND YEAR(filter_column_2) = filter_value_year_2

Values:
1 - This year,
2 - Previous year,
3 - None
Column Arguments

Represents in SQL operation(column_value) WHERE column = argument

Parameters
argument string REQUIRED

Name of the column argument

Example: customer
column string REQUIRED

The name of an existing column in the table.
column_value string REQUIRED

The name of an existing column in the table.
operation integer REQUIRED

The type of sql operation used for this column.
Represents in SQL operation(column_value)

Values:
1 - Total sum (SUM),
2 - Average value (AVG),
3 - Number of rows (COUNT),
4 - Value,
5 - Maximum value (MAX),
6 - Minimum value (MIN)
Joins

Represents in SQL master_table.master_column = join_table.join_column

Parameters
master_table string REQUIRED

The name of an existing table in DataSource

Example: table1
master_column string REQUIRED

The name of an existing column in the table.
join_table string REQUIRED

The name of an existing table in DataSource
join_column string REQUIRED

The name of an existing column in the table.
Distinct Values

Parameters
label_name string REQUIRED

The name of the column in database

Example: ColumnName
values array[string] REQUIRED

The distinct values stored in specified the column

Example: ["Coca-Cola", "Cola"]