1. Overview

Data table management supports the creation of physical tables and visual charts through SQL. The detailed syntax is described below.

2. Create data table

2.1.  Format requirement

2.1.1. Table name

The data table name must meet the following conditions:

  • The value can start with only lowercase letters, digits, and underscores.
  • The value contains a maximum of 100 characters.
  • Table names cannot be repeated in the current database.

2.1.2. Field name

The name of each field in the data table needs to meet the following conditions:

  • The value can start with only lowercase letters, digits, and underscores.
  • The value contains a maximum of 100 characters.
  • Field names cannot be repeated in the current data table.

2.1.3. Field data type

When creating a data table, the following data types are supported:

  • <bool Bool>
  • <integer Int>
  • <long integer Bigint>
  • <numerical value Number>
  • <string String>
  • <timestamp Timestamp>
  • <set List>

2.2. Create a physical table

You can create a physical table with the following statement:

CREATE TABLE MyTable ( `user_id` BIGINT, `name` STRING ) WITH ( ... )
CODE

There are two main categories of physical tables:

  • Primary key table
  • Non-primary key table

Can use WITH clause to appoint:

  • The primary key table table_engine is SDW_MUTABLE
  • Non-primary table table_engine is SDW_IMMUTABLE

More on that below.

2.2.1. Primary key table

When creating a primary key table, you need to include the following information:

  • Primary key field：
    • The primary key table must specify a primary key field.
    • The PRIMARY KEY field must be a defined data table field in the format: PRIMARY KEY(' xxx ') is NOT ENFORCED, multiple fields can be filled, multiple fields are separated by commas, such as primary key (' aaa ', 'bbb') is NOT ENFORCED.
    • You are advised to contain a maximum of three primary key fields.You can only select String, Bigint, or Int as the primary key field.
  • WITH clause： required, used to specify a table type, such as:'table_engine'='SDW_MUTABLE'  indicates that this table is the primary key table.

For example

CREATE TABLE sensors_mutable_table (  `customer_id` STRING, col1 LIST, col2 BIGINT,  PRIMARY KEY(`customer_id`) NOT ENFORCED ) WITH (  'table_engine'='SDW_MUTABLE' )
CODE

2.2.2. Non-primary key table

To create a non-primary key table, include the following information:

  • Partition field:
    • Non-primary key tables can be configured with additional partition fields according to their requirements.
    • The partition field must be a defined field in the format PARTITIONED BY(`xxx`), can specify multiple fields separated by commas. Ensure that the fields are in the correct order, for example PARTITIONED BY(`year`, `month`)
    • You are advised to contain a maximum of five fields in the partition field. Only String can be used as the partition field.
  • WITH clause：required, used to specify table types and file formats:
    • 'table_engine'='SDW_IMMUTABLE'  indicates that this table is a non-primary key table.
    • 'file_format'='PARQUET' File format, currently only PARQUET is supported.

For Example

CREATE TABLE sensors_immutable_table ( `date` BIGINT, col1 STRING, col2 NUMBER ) PARTITIONED BY(`date`) WITH (  'table_engine'='SDW_IMMUTABLE',  'file_format'='PARQUET' )
CODE

2.3. Create visual chart

You can create a visual chart with the following statement:

CREATE VIEW sensorsdb.myview AS SELECT * FROM sensorsdb.table1 
CODE

For Example

CREATE VIEW sensors_event_view AS SELECT * FROM sensors_test.mutable_table
CODE

可以在 Create data table page SQL Logical configuration Field in the table of contents to the left of the input box to find the data tables and table fields needed to create the visual chart.

If a data table without table data permissions is referenced, errors will occur in the validation SQL and commit.

2.4.  SQL Statement Verify

In Create data table page SQL Logical configuration area, we provide format andverify SQL function.

  • Format : When you finish building the statement, you can click the Format button in the upper right corner to automatically organize the SQL statement format, so as to better read and check the SQL syntax.
  • Verify SQL : When you are finished building the statement, click the Check SQL button in the upper right corner to verify that your statement is syntactic. If the verification fails, the error log is displayed below. Please correct and try again to verify or submit.