SeaTable adds SQL query interface to make data analysis and query more convenient

SeaTable Developer Edition is a free, easy-to-use, scalable data center product. It combines the ease of use of collaborative tables with the powerful data processing capabilities of the 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 interface plug-in (as shown in the figure below), or it can be accessed through the API. Let's introduce the SQL query in detail below.

SQL query statement introduction

The syntax of SQL query statements in SeaTable is the same as 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]

The query result is returned in JSON format. Of course, there are still some limitations, mainly because it does not support multi-table query join statement.

special function

SeaTable's current main application scenario is to perform statistical analysis on data. So it supports some special functions to facilitate statistics on data:

• STARTOFWEEK(date, weekStart): Returns the week to which a date belongs, which is convenient for counting according to the week.
• Quarter(date): Returns the quarter to which a date belongs to facilitate statistics based on quarter.
• ISODate(date): Returns a date in ISO format, such as: "2020-09-08", which is convenient for counting by day.
• ISOMonth(date): Returns the month in ISO format, such as "07", which is convenient for counting according to the month.

For example, if we have a table that records the flow of orders, and we want to count the daily sales, we only need to use the following query statement:

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

If we want to get the data required by the following statistical graphs, we can also use SQL statements to get them easily:

How to use the query interface

SeaTable provides three methods for you to query data through the Internet from anywhere, including:

• Rest
• APIPython
• APISQL query plugin

Rest API

Using the Rest API is very simple, you don't need to assign and manage usernames and passwords, you only need to assign an API token. A form of API token can be generated through the web interface:

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>

one 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 result

{
    "metadata": [{
        "key": "0000",
        "name": "名称",
        "type": "text",
        "data": null
    }, ...
    ],
    "results": [{
        "0000": "fdddf",
        "_creator": "[e-mail]",
        "_ctime": "2021-07-14T09:10:35.225Z",
        "_id": "JkVwFfWMQ7Sfno1VAxHv8w",
        "_last_modifier": "[e-mail]",
        "_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')

Respectively return:

[
    {'_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 plugin

SeaTable provides a SQL query plug-in, users can query directly at the UI level, so that SQL statements can be easily debugged during development.

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

Summarize

Above, we have learned about the function and use of SeaTable's "SQL query", which makes it more convenient for internal data analysis and external users to query data. Of course, this is just one of the features. SeaTable Developer Edition as a free, easy-to-use, and scalable enterprise data middle-office product, has the ease of use of collaborative tables and powerful data processing capabilities of the database, from data collection, storage, automated processing, to visualization , Advanced statistical analysis, collaborative control and other aspects provide very useful functions.