Language

For the current JoinBase's query language, an ANSI SQL compatible syntax has been implemented.

We also try to learn the great part of PostgreSQL/TimescaleDB, MySQL et. al. in the language side. If you feel that there are good representations, functions and designs in these dialects, don't hesitate to come to the community to give your suggestions.

Here, we mainly show the differences and extensions to the ANSI SQL syntax which needing attention. Feel free to ask help from us and the community.

Finally, JoinBase is evolving rapidly. This language document is continuing to be improved based on the latest progress.

Syntax

Data Types

Typed Literals

Literals for primary types like integer or string is well established. For advanced types, like datetime, the traditional implicit pure string representations are subtle and error-prone.

To avoid ambiguity and enhance the maintainability of the language, not like some SQL dialects, single quoted string literal in the JoinBase is just for the String type. On the contrary, we favor typed literals for advanced literal, that is: type prefix + string representation of that type.

Note

• Not like Postgresql, JoinBase only have one timezone-wared DateTime type. It is recommended that you use the default timezone of JoinBase, which is the time zone of the JoinBase server. But you can specify the time zone in anywhere needed.
• Date and Time type are not related the time zones, it just plain date and time. If you need timezone wared behavior, you just use DateTime type.
• FixedString data in the JoinBase is fixed length. For the shorter string representation, it is necessary to pad the representation to the fixed length (with zero). For the longer string representation, the excess trailing will be truncated away. To enhance the ergonomics, for the representation which you can not need to pad, you can omit the width parameter, like fs'abc123'.

Data Definition, Manipulation and Management

• create database

CREATE DATABASE IF NOT EXISTS db_name

• create table

CREATE TABLE IF NOT EXISTS [db_name.]table_name
(
    a Nullable(UInt32),
    b Int64
)
PARTITION BY toYYYYMM(ts)

Allowed specific partition functions:

NOTE

For more performance-ergonomic, all no-nullable types (this is the default case) in the CREATE TABLE statement has a default value: empty string for String, 0 for int-like and float-like, false for boolean, and unix epoch timestamp 0 (ISO 8601: 1970-01-01T00:00:00Z) for Date and DateTime. It is allow to use default constraint to change the default value if necessary. See more for performance tunning.

• show databases

SHOW DATABASES

• show tables

SHOW TABLES IN db_name

• show create table

SHOW CREATE TABLE [db_name.]table_name

• desc table

DESC TABLE [db_name.]table_name

• drop database

DROP DATABASE IF EXISTS db_name

• drop table

DROP TABLE IF EXISTS [db_name.]table_name

• truncate table

TRUNCATE TABLE IF EXISTS [db_name.]table_name

• insert into

INSERT INTO [db_name.]table VALUES (v11, v12, v13), (v21, v22, v23), ...

• use database

USE db_name

Query

• general form

SELECT expr_list
[FROM [db.]table]
[WHERE PARTS range_list/last_subclause]
[WHERE expr]
[GROUP BY expr_list]
[ORDER BY expr_list]
[LIMIT n]

• Currently, WHERE PARTS supports two subclause forms:

  1. range_list. The range_list consists of one or more comma separated ranges. A range is one of as follow:

     one number, like 123;

     half open number interval, start..end, start..end contains all values with start <= x < end, like 123..456;

     close number interval, start..=end, start..=end contains all values with start <= x <= end, like 123..=456;

  2. last_subclause. The last_subclause is used to specify the last number of partitions which you can query against without providing latest partition key because it is varying with time, like last or last 10. This form is very useful for querying latest data in the time based partitions. Note, last is just a short form to last 1.

Function Reference

Aggregation

Math

Primitive

String

Blob(Binary)

DateTime

Other Test