Use FML statements to configure and manage data tables - DataWorks

After a data table is created, you can execute fast modeling language (FML) statements in the code editor of the table to configure fields and partitions for the table. This topic describes how to use FML statements to configure a data table.

Prerequisites

A data table is created by using the codeless user interface (UI). In DataWorks, FML statements cannot be used to create data tables.

For more information about how to create a dimension table, see Create a logical model: dimension table.
For more information about how to create a fact table, see Create a logical model: fact table.
For more information about how to create an aggregate table, see Create a logical model: aggregate table.

Background information

FML is an SQL-like language that is used to design data tables for dimensional modeling. FML statements use the standard SQL-like syntax. The design and materialization of a DataWorks data model (logical table) are decoupled. During the design of a logical table, you do not need to consider the materialization of the logical table on each underlying big data engine. The modeling engine drives the operations of each underlying big data engine based on the table schema defined by FML. During the materialization of the logical table, the modeling engine converts the logical table into a physical table for the underlying big data engine that you specify. During the conversion, the modeling engine converts FML statements for the logical table into SQL statements that can be identified by the specified big data engine and commits and executes the SQL statements.

Limits

In DataWorks, FML statements cannot be used to create data tables or change the names of data tables. You can use FML statements only to perform other configuration operations on data tables. For example, you can use FML statements to configure table fields, configure table associations, and configure table partitions.
In DataWorks, you can use FML statements to materialize data tables only to MaxCompute, Hologres, or Hive.
FML statements use SQL keywords as reserved words. If the name of your data table or the name of a field in the data table contains the keywords, you must enclose the name in a pair of grave accents (`) to escape the name. Otherwise, an error is reported when you configure the table.

Go to the Script Mode panel of a data table

In the left-side navigation tree of the Dimensional Modeling page, double-click the name of a data table.
In the Field Management section of the configuration tab of the table, click Script Mode.
In the code editor of the Script Mode panel, you can view the FML CREATE TABLE statement for the table. You can also configure fields for the table or modify existing fields in the table. For more information, see Configure a data table.

Configure a data table

In DataWorks, FML statements cannot be used to create data tables. However, when you configure a data table in FML, you must configure the related parameters in the CREATE TABLE statement. This helps you obtain all the configurations of the data table. When you configure a data table, you need to refer only to the content that defines the table, such as the constraints and partitions, in the CREATE TABLE syntax. The following CREATE TABLE syntax shows how to configure a data table:

-- Configure a table.
      CREATE <table_type> TABLE
      IF NOT EXISTS
      -- Specify the table name.
      <table_name> [ALIAS <alias>]
      -- Define field attributes.
      <col_name> [ALIAS <alias>] <datatype> [<category>] [COMMENT <comment>] [WITH (<key>=<value>,....)]
      -- Define constraints.
      PRIMARY KEY (<col_name>),
      -- Dimension constraint
      CONSTRAINT <constraint_name> DIM KEY (<col_name>) REFERENCES <ref_table_name> (<ref_table_col_name>),
      -- Hierarchy constraint
      CONSTRAINT <constraint_name> LEVEL <col_name:(<col_name>)>,
       
      CONSTRAINT <constraint_name> COLUMN_GROUP(<col_name>,...), 
      -- Specify the comment for the table.
      COMMENT 'comment'
      -- Define partitions.
      PARTITION BY (col DATATYPE COMMENT 'comment' WITH ('key'='value',...), ...)
      -- Define other attributes.
      WITH ('key'='value', 'key1'='value1', ...)
      ;
      
tableType
    : dimDetailType? DIM
    | factDetailType? FACT
    | CODE
    | DWS
    ;
    
 dimDetailType
    : NORMAL
    | LEVEL
    | ENUM
    ;
    
 factDetailType
    : TRANSACTION
    | AGGREGATE
    | PERIODIC_SNAPSHOT
    | ACCUMULATING_SNAPSHOT
    | CONSOLIDATED
   ;
    
 
comment 
    : COMMENT 'comment'
    ;

Parameter	Description
table_name	The name of the table. The name can be a maximum of 128 characters in length and can contain letters, digits, and underscores (_).
IF NOT EXISTS	When you create a table, if you specify `IF NOT EXISTS`, the table can be created regardless of whether a table with the same name exists in the big data engine that you select. If a table with the same name exists, this table is replaced by the new table. If you do not specify `IF NOT EXISTS`, the table fails to be created when a table with the same name exists in the big data engine that you select.
alias	The alias of the table or a field in the table. For a data table, this parameter specifies the display name of the table. This parameter is optional.
table_type	The type of the table. The following types of tables are supported: Dimension table NORMAL: indicates a common dimension table. This is the default type that is specified when you create a dimension table. LEVEL: indicates a hierarchy dimension table. This type of table is used to store data that has hierarchical relationships, such as provinces, cities, and districts. ENUM: indicates an enumeration dimension table. This type of table is used to store common enumeration values, such as male and female. Fact table TRANSACTION: indicates a transaction fact table. This type of table is used to store facts at the transaction level. This is the default type that is specified when you create a fact table. A transaction fact table stores the most atomic data. PERIODIC_SNAPSHOT: indicates a periodic snapshot fact table. This type of table is used to store facts that have regularity and predictable time. A periodic snapshot fact table summarizes measurement events over a specific period, such as days before the current day and days before the current day within the current calendar year. Data in a periodic snapshot fact table is incrementally updated. ACCUMULATING_SNAPSHOT: indicates an accumulating snapshot fact table. This type of table is used to store the snapshot information of transaction data and accumulate data of uncertain periods. For example, an accumulating snapshot fact table for order records can contain payment dates, shipment dates, and receipt dates. DWS: indicates a logical aggregate table. This type of table is used to merge specific metrics. The syntax that is used to define this type of table is the same as the syntax that is used to define a dimension table or fact table. CODE: indicates a lookup table. This type of table is used to store codes of a specific industry attribute. For example, a lookup table for the hydropower industry may contain whether a power supply contract is signed or the types of power supply contracts.
comment	The comment for the table. The value of this parameter can be a maximum of 1,024 characters in length.
columnDefinition	The definitions of fields in the table. The following parameters can be contained: col_name: the name of a field. The name can contain letters, digits, and underscores (_). If the field name contains FML keywords, you must enclose the name in a pair of grave accents (`) to escape the name. alias: the alias of a field. This parameter is used to define the display name of a field. This parameter is optional. dataType: the data type of a field. The following data types are supported: BIGINT, STRING, VARCHAR, CHAR, DECIMAL, and DATETIME. category: the category of a field. The following categories are supported in dimensional modeling: ATTRIBUTE, MEASUREMENT, and CORRELATION. Note FML statements allow you to materialize tables after the tables are designed. Therefore, new tables can contain no fields.
constraint	The constraints of the table schema. You can define the following types of constraints: Primary key constraint: Define this type of constraint in the format of `PRIMARY KEY(col1, col2)`. The fields that you specify by using `col1, col2` must already exist. Dimension constraint: Define this type of constraint in the format of `DIM KEY(col1, col2) REFERENCES table_name(ref1, ref2)`. Hierarchy constraint: Define a hierarchy for a hierarchy dimension table. This type of constraint takes effect only on hierarchy dimension tables.
PARTITION BY	The partitions of the table.
WITH	The custom attributes of the table. Configure each attribute in the `'key'='value'` format. Separate multiple attributes with commas (,). You must enclose each `key` and each `value` in a pair of single quotation marks (') to avoid conflicts between custom attributes and FML keywords. The custom attributes that you configure in the `WITH` clause can be parsed and processed by the engine that is used to materialize FML statements.