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 power of databases. Starting with version 2.3, SeaTable has another major enhancement for data analysis, support for SQL queries. The SQL query function can be used through a plug-in for the interface (figure below) or accessed through the API. Let’s take a closer look at SQL queries.

SQL query statements

The syntax of SQL query statements in SeaTable is consistent with that of MySQL. The syntax is as follows:

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

The query results are returned in JSON format. Of course, there are some limitations, mainly that the multi-table query JOIN statement is not supported.

The characteristic function

Currently, SeaTable is mainly used for statistical analysis of data. Therefore, it supports some special functions to facilitate data statistics:

  • STARTOFWEEK(Date, weekStart) : Returns the week of a date, facilitating statistics by week.
  • Quarter(Date) : Returns the Quarter to which a date belongs, facilitating statistics by Quarter.
  • ISODate(date) : Returns the date in ISO format, for example, “2020-09-08”, to facilitate statistics by day.
  • ISOMonth(date) : Returns the month in ISO format, such as 07, to facilitate statistics by month.

For example, if we have a table that records the order flow, and we want to count the daily sales, we simply use the following query statement:

select sum(sale) from SalesRecord group by ISODate(SalesTime)
Copy the code

If we want to obtain the data needed for the following statistics, we can also use SQL statements to easily obtain:

How do I use the query interface

SeaTable provides three ways to query data from anywhere on the web, including:

  • Rest
  • APIPython
  • APISQL query plug-in

Rest API

Using the Rest API is very simple. Instead of assigning and managing usernames and passwords, you just need to assign an API token. A tabular API token can be generated from the web interface:

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

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

A case in point

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

Return result

{" metadata ": [{" key" : "0000", "name" : "name", "type" : "text", "data" : null},... , "results" : [{" 0000 ":" FDDDF ", "_creator" : "[email protected]", "_ctime" : "the 2021-07-14 T09: would. 225 z", "_id" : "JkVwFfWMQ7Sfno1VAxHv8w", "_last_modifier": "[email protected]", "_mtime": "The 2021-07-23 T01: bear. 507 z", "_participants" : [], "qi70" : "711776", "WCLS" : "SDF}]", "success" : true}Copy the code

Python SDK

The Rest API above is wrapped 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')
Copy the code

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'}
]
Copy the code

SQL query plug-in

SeaTable provides SQL query plug-ins that users can query directly at the UI level, making it easy to debug SQL statements during development.

The query results are presented directly in the SeaTable table format:

conclusion

Above, we learned about the capabilities and use of SeaTable’s “SQL query”, which makes it easier to analyze data internally as well as to query data externally. Of course, that’s just one feature. SeaTable Developer edition is a free, easy-to-use, scalable enterprise data center product with easy-to-use collaborative tables and powerful data processing capabilities, from data collection, storage and automation to visualization, advanced statistical analysis and collaborative governance.