CREATE

The CREATE statement is mostly used in creating table. The general syntax for creating a table is

CREATE TABLE table_name (column1 data-type, [column2 data-type], ...)

The general syntax for creating a dimension table with unique key is

CREATE TABLE table_name (column1 data-type, [column2 data-type], .., KEY(column1...))

To create a table with non-unique index, the general syntax is

CREATE TABLE table_name (column1 data-type, [column2 data-type], .., INDEX(column1...))

BigObject supports a special index to identify the lastest record among all records with the same index value in the table. The syntax to create a table with such kind of index is

CREATE TABLE table_name (column1 data-type, [column2 data-type], .., LAST INDEX(column1...))

Default Table

Tables created by the CREATE TABLE statement are immune to the DELETE command. However, you can use CREATE DEFAULT TABLE to create a table with deletable rows.

The general syntax are

CREATE DEFAULT Table table_name (column1 data-type, [column2 data-type], ...)

Row-based / Column-based Table

BigObject supports both row-based and column-based tables. You can specify the table type within the CREATE TABLE statement by ROW or COLUMN keyword.

The general syntax are

CREATE ROW TABLE table_name (column1 data-type, [column2 data-type], ...)   

and

CREATE COLUMN TABLE table_name (column1 data-type, [column2 data-type], ...)   

Time-bounded Sliding Table

BigObject supports a special kind of time-bounded sliding tables. Each record inserted to a time-bounded sliding table is automatically tagged with a timestamp. Any query command issued on a time-bounded sliding table will only consider those records whose timestamps are within the specified time window.

The syntax to create a time-bounded sliding table are

Create SLIDING Table table_name (column1 data-type, [column2 data-type], .... TIMEBOUND(time_window))

The length of a time window can be described by a number and a time window specifier as shown below:

In addition, you can also combine several time window specifiers to denote a time window. For example, "2h30m" represents a time window of two hours and 30 minutes

The user can combine various create keywords in a CREATE statement. The general syntax of a CREATE statement is as follows:

CREATE [DEFAULT] [COLUMN | ROW] TABLE table_name (
    column1 data-type, [column2 data-type], ... 
    [, TIMEBOUND(time window)]
    [,(KEY | INDEX | LAST INDEX)(column...)]
)

Cases Example:

ABC company just adopted BigObject solution. The staff wants to record its business information by creating the database first. Based on ABC company's business nature, the staff decides to create a dimension table to store products information and a dimension table to store customers information. He will also create a fact table to record all the sales transactions. Therefore, the staff uses following commands to create the tables: Product, Customer and sales.

CREATE TABLE Product ('id' INT32, 'name' STRING(63), 'brand' STRING(63), 'brandOwner' STRING(63), 'weightGrams' FLOAT, 'weightOunce' FLOAT, 'category' STRING(63), 'price' FLOAT, 'cost' FLOAT, 'profit' FLOAT, KEY (id))

CREATE TABLE Customer ('id' INT32, 'name' STRING, 'language' STRING, 'state' STRING, 'company' STRING, 'gender' STRING, 'age' INT32, KEY (id))

CREATE TABLE sales ('order_id' INT32, 'Customer.id' INT32, 'Product.id' INT32, 'channel_name' STRING, 'Date' DATETIME32, 'qty' INT64, 'total_price' DOUBLE)