Seatable adds a new SQL query interface to make data analysis and query more convenient

Time:2021-10-20

Seatable Developer EditionIt is a free, easy-to-use and scalable data middle platform product. It combines the ease of use of collaborative tables and the powerful data processing ability of database. Since version 2.3, seatable has another major enhancement in data analysis, that is, support for SQL queries. The SQL query function can be used through the plug-in of the interface (as shown in the figure below) or accessed through the API. Let’s introduce SQL query in detail.

Seatable adds a new SQL query interface to make data analysis and query more convenient

Introduction to SQL query statements

The syntax of SQL query statements in seatable is consistent with MySQL, which is convenient for everyone to get started quickly. The syntax is as follows:

SELECT [DISTINCT] fields FROM table_name [WhereClause] [OrderByClause] [GroupByClause] [Limit Option]

Query results are returned in JSON format. Of course, there are still some limitations, mainly that multi table query join statements are not supported.

Characteristic function

At present, the main application scenario of seatable is statistical analysis of data. Therefore, it supports some special functions to facilitate data statistics:

  • Start of week (date, weekstart): returns the week to which a date belongs, which is convenient for statistics by week.
  • Quarter (date): returns the quarter to which a date belongs, which is convenient for statistics by quarter.
  • Isodate (date): returns a date in ISO format, such as “2020-09-08”, which is convenient for statistics by day.
  • Isomonth (date): returns the month in ISO format, such as “07”, which is convenient for statistics by month.

For example, if we have a table recording the order flow, we only need to use the following query statement to count the daily sales:

select sum(sale) from SalesRecord group by ISODate(SalesTime)

If we want to get the data required by the following statistical chart, we can also easily get it with SQL statement:

Seatable adds a new SQL query interface to make data analysis and query more convenient

How to use query interface

Seatable provides three methods to facilitate you to query data through the network anywhere, including:

  • Rest
  • APIPython
  • Apisql query plug-in

Rest API

Using the rest API is very simple. You don’t need to assign and manage user names and passwords. You just need to assign an API token. The API token of a table can be generated through the web interface:

Seatable adds a new SQL query interface to make data analysis and query more convenient

Use this API token to obtain a temporary secret key, and then access the following interface

POST https://dtable-db.seatable.cn/api/v1/query/<dtable-uuid>

An example

curl -X POST \
  https://dtable-db.seatable.cn/api/v1/query/4c4ef1ee-86cf-4a53-bd02-2cb7b1662a11/ \
  -H 'Authorization: Token eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJleHAiOjE2MzAyOTA3NjMsImR0YWJsZV91dWlkIjoiNGM0ZWYxZWUtODZjZi00YTUzLWJkMDItMmNiN2IxNjYyYTExIiwidXNlcm5hbWUiOiJqaXdlaS5yYW5Ac2VhZmlsZS5jb20iLCJpZF9pbl9vcmciOiJXLTAwMDI2IiwicGVybWlzc2lvbiI6InJ3In0.KG5WQEduNsC8-k61oAcby7bhF6seVXrjnG7rGLsHQds' \
  -H 'Content-Type: application/json' \
  -d '{
    "sql": "select * from Table2 limit 1"
}'

Returned results

{
    "metadata": [{
        "key": "0000",
        "Name": "name",
        "type": "text",
        "data": null
    }, ...
    ],
    "results": [{
        "0000": "fdddf",
        "_creator": "[email protected]",
        "_ctime": "2021-07-14T09:10:35.225Z",
        "_id": "JkVwFfWMQ7Sfno1VAxHv8w",
        "_last_modifier": "[email protected]",
        "_mtime": "2021-07-23T01:44:32.507Z",
        "_participants": [],
        "qi70": "711776",
        "wcls": "sdf"
    }],
    "success": true
}

Python SDK

The above rest API has been packaged in the python SDK and can be easily called;

base.query('select name, price, year from Bill')
base.query('select name, sum(price) from Bill group by name')

Return respectively:

[
    {'_id': 'PzBiZklNTGiGJS-4c0_VLw', 'name': 'Bob', 'price': 300, 'year': 2019},
    {'_id': 'Ep7odyv1QC2vDQR2raMvSA', 'name': 'Bob', 'price': 300, 'year': 2021},
    {'_id': 'f1x3X_8uTtSDUe9D60VlYQ', 'name': 'Tom', 'price': 100, 'year': 2019},
    {'_id': 'NxeaB5pDRFKOItUs_Ugxug', 'name': 'Tom', 'price': 100, 'year': 2020},
    {'_id': 'W0BrjGQpSES9nfSytvXgMA', 'name': 'Tom', 'price': 200, 'year': 2021},
    {'_id': 'EvwCWtX3RmKYKHQO9w2kLg', 'name': 'Jane', 'price': 200, 'year': 2020},
    {'_id': 'BTiIGSTgR06UhPLhejFctA', 'name': 'Jane', 'price': 200, 'year': 2021}
]

[
    {'SUM(price)': 600, 'name': 'Bob'},
    {'SUM(price)': 400, 'name': 'Tom'},
    {'SUM(price)': 400, 'name': 'Jane'}
]

SQL query plug-in

Seatable provides a SQL query plug-in. Users can query directly at the UI level, so that they can easily debug SQL statements during development.

The query results will be directly presented in the form of seatable table table:

Seatable adds a new SQL query interface to make data analysis and query more convenient

summary

Above, we have learned about the function and use of “SQL query” of seatable, which makes it more convenient for internal analysis data and external users to query data. Of course, this is only one function.Seatable Developer EditionAs a free, easy-to-use and scalable enterprise data middle platform product, it has the ease of use of collaborative tables and the powerful data processing ability of the database. It provides very useful functions from data collection, storage and automatic processing to visualization, advanced statistical analysis, collaborative control and so on.