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 |
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 defaultFalse
|
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:
* 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 defaultFalse
|
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:
* 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 defaultFalse
|
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:
* 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_schemadefault ""
|
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 |
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.
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.
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
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 1 - None, 2 - Month, 3 - Year |
filter_operation_2 |
integer
Used only when data_type_2 is 1 - None, 2 - Month, 3 - Year |
filter_value_1 |
string
Used only when filter_operation_1 is |
filter_value_1_end |
string
Used only when filter_operation_1 is |
filter_value_2 |
string
Used only when filter_operation_2 is |
filter_value_2_end |
string
Used only when filter_operation_2 is |
filter_value_quarter_1 |
integer
Used only when data_type_1 is 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 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 1 - This month, 2 - Previous month, 3 - None |
filter_value_month_2 |
integer
Used only when data_type_1 is 1 - This month, 2 - Previous month, 3 - None |
filter_value_year_1 |
integer
Used only when data_type_1 is 1 - This year, 2 - Previous year, 3 - None |
filter_value_year_2 |
integer
Used only when data_type_2 is 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.
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"] |