SmartMind

ThanoSQL

GitHub

Use Cases

Learn

API Reference

FAQs

Get Started

Support

File Manager

Immediately deploy your AI models to your application with SQL

Introduction

Quickstart

Generate

Embed

Predict

Cleanup Resources

Overview

Query Manager

Settings

Python SDK

AICC Scenario

Image Retrieval

Review Analysis and Sales Data Integration

Review Analytics

Learn how to use objects from S3 in ThanoSQL functions

Using S3

Why ThanoSQL?

Processes queries using either a query string or a query template. The
query_string field holds direct queries or templates that can be modified
with specified parameters. You can also reference saved query templates by
their template_id or template_name. These templates, regardless of how
they're called, can be used with or without parameters. If the query produces results, 
the results are stored in a table. If the table_name and schema are provided, a table 
is created and the results are stored in that table. Overriding an existing table is 
also possible if specified. If no table_name or schema is provided, the table 
is stored in the 'qm' schema with a randomly generated name. The endpoint returns 
a QueryLog as a response.

Request
----------
__Request Body__

    query_string: Optional[str]
    template_id: Optional[int]
    template_name: Optional[str]
    parameters: Optional[dict]

__Request Parameters__

    schema: str, default: "qm"
        The schema to retrieve the tables from. If no parameter is
        provided, defaults to "qm".
    table_name: str, default: random generated name
        the name that will be used to create the table
    overwrite: bool, default: False
        determines if the table shall be overwritten or not
    max_results: int, default: 0
        specifies the maximum number of records that should be returned in the result set (max: 100)

Response
-------
__QueryLogResponse__

    query_id: str
        A unique ID that identifies the query.
    statement_type: str
        The query type (ex. SELECT, BUILD, etc.).
    start_time: datetime
        The start time of the query execution.
    end_time: datetime
        The end time of the query execution.
    query: str
        The query string that was executed.
    referer: str
        Indicates where the REST API call was made from.
    state: str
        Shows the state of the query execution (Either RUNNING or COMPLETE).
    destination_table_name: str
        Shows table where the results are stored in (If the statement_type is a SELECT, a new destination_table is created. Otherwise, the destination_table_name shows the affected table).
    destination_schema: str
        Shows in which schema the destination_table is stored in.
    error_result: str
        Stores the error message if there was an error executing the query.
    created_at: datetime
        Shows the timestamp of when the query log was created.
    records: dict list
        Returns a sample of the data (max. 100)

Post Query

Returns a list of Query Logs and the total number/count of Query Logs. This endpoint is paginated.

Request
----------
__Request Parameters__

    offset: int, default: 0
        The offset to where the pagination count will start from
    limit: int, default: 0
        The number of items to retrieve starting from the offset.
        Max value is 100

Response
-------
__QueryLogReponse__

    query_logs: List[QueryLog]
        A list of QueryLogs
    total_count: Total number of QueryLogs in the database.

Get Query Logs

Gets the paginated list of all the query templates.
The API can only return 100 tables at a time,
starting from the offset number specified.

Parameters
----------
- offset: int, default 0
    The offset position of where to select the templates from.
- limit: int, default 100
    The number of templates to retrieve. This value must range from 0 and 100.
- search: str, default: empty
    Substring to look for in query template names
- order_by: str, default: "recent"
    How the query template results should be ordered, there are three possible values:
    - recent: based on the date of creation, from most recent to oldest
    - name_asc: based on the name of the template, from A to Z
    - name_desc: based on the name of the template, from Z to A

Returns
-------
A QueryTemplates Pydantic object containing a List of QueryTemplate objects.

Get Templates

Creates a new query template with a certain name and query string.

Parameters
----------
body: CreateQueryTemplateRequest
  The body of the template creation request, consisting of
  the template name and query string.

Returns
-------
A QueryTemplate Pydantic object containing the recently created query template object.

Create Template

Gets the query template object with the specified template_name.

Parameters
----------
template_name: str
  The name of the template user wants to get.

Returns
-------
A QueryTemplate Pydantic object containing the target query template.

Get Template

Updates the query template with the specified template_name.

Parameters
----------
template_name: str
  The name of the template user wants to update.
body: UpdateQueryTemplateRequest
  The body of the template creation request, consisting of
  the template name and query string.

Returns
-------
A QueryTemplate Pydantic object containing the target query template.

Update Template

Deletes the query template with the specified template_name.

Parameters
----------
template_name: str
  The name of the template user wants to update.

Returns
-------
A QueryTemplateMessage Pydantic object containing a success message.

Delete Template

Gets the paginated list of all the tables from the given schema. If no
schema is provided then the function retreives all of the tables from
all of the schemas. pg_catalog and information_schema related tables are
exempted, unless specified. The API can only return 100 tables at a time,
starting from the offset number specified.

Parameters
----------
- schema : str, default None
    The schema to retrieve the tables from. If None is
    provided, refers to all schemas.
- verbose: bool, default False
    Whether to include only base information or verbose information on the tables.
- offset: int, default 0
    The offset position of where to select the tables from.
- limit: int, default 100
    The number of tables to retrieve. This value must range from 0 and 100.

Returns
-------
A TablesResponse Pydantic object containing a List of
either Table Pydantic or Base Table Pydantic models depending
on the verbose parameter.

Get Tables

Gets the Table object with the specified table_name and
schema. If the schema is not specified, it automatically
defaults to public.

Parameters
----------
table_name: str
schema : str, default public
    The schema to retrieve the tables from. If no parameter is
    provided, assigned as public.

Returns
-------
A TableResponse Pydantic object containing a Table Pydantic object.

Get Table

Alters the table specified with the table_name and schema to the
Table object specified in the body parameter.

Instructions
------------
In order to update an object, simply change the values of the Table objects
in the request body. To delete, simply remove the object from the request body.
To add, add the desired object to the request body.

The query is built in the following order:

DROP UNIQUE CONSTRAINT -> DROP PRIMARY KEY -> DROP FOREIGN KEY -> DROP COLUMN -> ADD COLUMN -> ALTER COLUMN ->
RENAME COLUMN -> ADD UNIQUE CONSTRAINT -> ADD PRIMARY KEY -> ADD FOREIGN KEY -> RENAME TABLE -> SET SCHEMA

### Caution:
For the columns, it is important to note that the id is the
unique key used to identify the column. If you remove the column_id key
from the Column object, it will be dropped from the database and be recreated.

Since there is a given order for execution, if your alter requires to not follow the
order above, make sure to separate the API calls.


Parameters
----------
- table_name: str,
- schema : str, default "public"
    The schema to retrieve the tables from. If no parameter is
    provided, defaults to "public".
- body: AlterTableRequest, default None

Returns
-------
A Table Pydantic object.

Update Table

Creates the table in the database using the BaseTableObjects object
provided in the request body, the table_name, and schema.

*Note*: When adding Column objects to the list of columns,
there is no need to specify the id since the id just refers
to the ordinal position of the column. Additionally if
the table is created with an empty body, an empty table will
be created. If no table_name is specified, the table will be
created with a random uuid string.

Parameters
----------
- table_name: str
- schema : str, default "public"
    The schema to retrieve the tables from. If no parameter is
    provided, defaults to "public".
- body: CreateTableRequest, default None
Returns
-------
A Table Pydantic object.

Create Table

Deletes the table specified by the table_name and schema.

Parameters
----------
- table_name: str
- schema : str, default "public"
    The schema to retrieve the tables from. If no parameter is
    provided, defaults to "public".

Returns
-------
A TableMessage Pydantic object containing a success message and the table_name.

Delete Table

Returns the paginated records of the given table specified by the table_name and
schema. The API can only return 100 records at a time, starting from the offset
number specified.

Parameters
----------
- table_name: str
- schema : str, default "public"
    The schema to retrieve the tables from. If no parameter is
    provided, defaults to "public".
- offset: int, default 0
    The offset position of where to select the records from.
- limit: int, default 100
    The number of records to retrieve. This value must range from
    0 and 100.

Returns
-------
A TableRecordsResponse Pydantic object containing
the records and total number of records.

Get Records

Inserts the records in the input body into a table in a specified schema.

Parameters
----------
- table_name: str
- records: list[dict]
    The list of key-value pairs to be inserted to the table
- schema : str, default "public"
    The schema to retrieve the tables from. If no parameter is
    provided, defaults to "public".

Returns
-------
A Table Pydantic object.

Insert Records

Returns the csv of the given table specified by the table_name and
schema.

Parameters
----------
- table_name: str
- schema : str, default "public"
    The schema to retrieve the tables from. If no parameter is
    provided, defaults to "public".
- timezone_offset: int
    The timezone offset applied to the datetime formats. Defaults to 9 (KST).

Returns
-------
A StreamingResponse Pydantic object that iterates through the csv.
If the table is empty, returns a Response object with code 204.

Get records as a CSV file

Inserts records from a CSV file into a table previously created using create_table,
or creates a new table from it.

Parameters
----------
- table_name: the name of the table created by create_table
- schema: database schema, default "public"
- if_exists: what to do if table of the same name already exists, default "fail". available options:
  - "fail" -> creates a new table, only possible if no table of the same name exists (fails otherwise)
  - "append" -> appends records into an existing table (columns must match in order to not generate an exception)
  - "replace" -> deletes an existing table and creates a new one with the same name
  - if user inputs anything other than these three, the server will throw an exception
- file: .csv file to be inserted
- body: the request body if table template columns were to be added manually, optional

Returns
-------
A Table Pydantic object.

Upload Table From Csv

Inserts records from a type of Excel file into a table previously created using create_table,
or creates a new table from it.

Parameters
----------
- table_name: the name of the table created by create_table
- schema: database schema, default "public"
- if_exists: what to do if table of the same name already exists, default "fail". available options:
  - "fail" -> creates a new table, only possible if no table of the same name exists (fails otherwise)
  - "append" -> appends records into an existing table (columns must match in order to not generate an exception)
  - "replace" -> deletes an existing table and creates a new one with the same name
  - if user inputs anything other than these three, the server will throw an exception
- file: Excel file to be inserted
- body: the request body if table schema columns were to be added manually, optional

Returns
-------
A Table Pydantic object.

Upload Table From Excel

Inserts records from JSON into a table previously created using create_table,
or creates a new table from it.

Parameters
----------
- table_name: the name of the table created by create_table
- schema: database schema, default "public"
- if_exists: what to do if table of the same name already exists, default "fail". available options:
  - "fail" -> creates a new table, only possible if no table of the same name exists (fails otherwise)
  - "append" -> appends records into an existing table (columns must match in order to not generate an exception)
  - "replace" -> deletes an existing table and creates a new one with the same name
  - if user inputs anything other than these three, the server will throw an exception
- body:
  - "table": optionally specify a table object is to be added manually.
  - "data": where the json object with the records format (list of json objects) is specified.

Returns
-------
A TableResponse Pydantic object.

Upload Table From Json

Retrieves all available table templates

Parameters
----------
- search: str, default: empty
    Substring to look for in table template titles
- order_by: str, default: "recent"
    How the query results should be ordered, there are three possible values:
    - recent: based on the date of creation, from most recent to oldest
    - name_asc: based on the name of the table template, from A to Z
    - name_desc: based on the name of the table template, from Z to A
- latest: bool, default: False
    If set to True, returns all of the latest table templates

Returns
-------
A TableTemplates Pydantic object containing the table template objects.

Get All Table Templates

Retrieves table template(s) by name. User may choose to request only the
latest table template or a template with a certain version.

Parameters
----------
- table_template_name: the name of the table template
- version: the version of the table template that the user wants to get
    User can fill in 'latest' if they want to retrieve the latest version of the table template
    User can leave the version parameter blank if they want to get all versions of the table template

Returns
-------
A TableTemplates Pydantic object containing the table template objects.

Get Table Template

Creates table template from an input and saves it into
schema registry (table_template table in user database).

Parameters
----------
- table_template_name: the name of the table template
- body: the body of the request, consisting of the table template body

Returns
-------
A TableTemplate Pydantic object.

Create Table Template

Deletes table template of a certain name and version from the database.

Parameters
----------
- table_template_name: the name of the table template
- version: the version of the table template that the user wants to delete
    User can leave the version parameter blank if they want to delete all versions of the table template

Returns
-------
A TableTemplateMessage Pydantic object containing a
success message and the table template name.

Delete Table Template

Gets the paginated list of all the views from the given schema. If no
schema is provided then the function retreives all of the views from
all of the schemas. pg_catalog and information_schema related views are
exempted, unless specified. The API can only return 100 views at a time,
starting from the offset number specified.

Parameters
----------
- schema : str, default None
    The schema to retrieve the views from. If None is
    provided, refers to all schemas.
- verbose: bool, default False
    Whether to include only base information or verbose information on the views.
- offset: int, default 0
    The offset position of where to select the views from.
- limit: int, default 100
    The number of views to retrieve. This value must range from 0 and 100.

Returns
-------
A ViewsResponse Pydantic object containing a List of
either View Pydantic or Base Table Pydantic models depending
on the verbose parameter.

Get Views

Gets the View object with the specified view_name and
schema. If the schema is not specified, it automatically
defaults to public.

Parameters
----------
view_name: str
schema : str, default public
    The schema to retrieve the views from. If no parameter is
    provided, assigned as public.

Returns
-------
A ViewResponse Pydantic object containing a View Pydantic object.

Get View

Deletes the view specified by view_name and schema.

Parameters
----------
- view_name: str
- schema : str, default "public"
    The schema to delete the view from. If no parameter is
    provided, defaults to "public".

Returns
-------
A ViewMessage Pydantic object containing a success message and the view_name.

Delete View

Gets all the schemas from the database.

Returns
-------
A SchemasResponse Pydantic object containing a List of
Table Schema objects.

Get Schemas

Create Schema

Retrieves file/directory information or downloads a file.

Path parameter
--------------
- path: the path to the file/directory relative to the user data root
        (default value is "/", the user data root directory itself)

Query parameters
----------------
- option: controls the behavior of the API
    - default (None): retrieves file/directory information
        Returns a Content Pydantic object containing information on the target path
    - "download": downloads a file (directory download is not possible)
        Streams the file content or does the download and returns nothing,
        depending on the client request

Deletes a file or directory permanently.

Path parameter
--------------
- path: the path to the file/directory to be deleted
        relative to the user data root (no default value)

Return
------
No content (nothing is returned)

Delete

Uploads a file or creates an empty folder.

Path parameter
--------------
- destination: the destination path of the upload/folder creation relative to the user data root.
        destination should point to a (would-be) directory, otherwise NotADirectoryError will occur.
        (default value is "/", the user data root directory itself)

Additional input
----------------
- file: the file to be uploaded (optional)
    - if a file is attached, uploads the file to the destination path
        Returns a Content Pydantic object containing
        information on the file uploaded
    - if no file is attached, creates an empty folder following the destination path
        Returns a Content Pydantic object containing
        information on the newly-created folder

Create

Moves, renames, or copies a file or directory.

Path parameter
--------------
- source: the path to the file/directory to be moved/renamed/copied
        relative to the user data root (no default value)

Query parameters
----------------
- destination: the path to the destination file/directory (no default value)
- option: controls the behavior of the API
    - default (None): moves or renames a file or directory. If the same
        file/directory already exists in the destination, the API behavior
        follows the behavior of Linux file systems.
        Returns a Content Pydantic object containing information on the
        destination file/directory
    - "copy": copies a file or directory. destination should be a directory.
        If the same file/directory already exists in the destination, the
        copied file/directory will be automatically renamed.
        Returns a Content Pydantic object containing information on the
        destination file/directory

Get Started

ThanoSQL Functions

Workspace

File Manager