The CREATE statement is mostly used in creating table. As mentioned earlier, BigObject supports dimension table and fact table.

The general syntax for creating a fact table is

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

The general syntax for creating a dimension table is

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

The subtle difference is that the user needs to specify the key column(s) within the dimension table by attribute name in the column after "KEY" word. The purpose of the key attribute is to identify each record in the dimension table.

Note
1. The maximum length of table name and simple column name is 30. Characters over the limit will be ignored without notice.

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], ...)   

Deletable Table

Tables created by the CREATE TABLE statement are immune to the DELETE statement. If the user wants a table with deletable rows, he needs to use CREATE DEFAULT TABLE to create the table.

The general syntax are

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

and

Create DEFAULT Table table_name (column1 data-type, [column2 data-type], .., KEY(column...))

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 time bound, an effect similar to a recent time window over the data.

The syntax to create a time-bounded sliding table is

    Create SLIDING Table table_name (column1 data-type, [column2 data-type], .... TIMEBOUND(seconds))
Note
Sliding tables do not support VARSTRING.

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)