Model in code editor - DataWorks - Alibaba Cloud Documentation Center

After you create a data table, you can use Fast Modeling Language (FML) statements in the code editor to configure table fields and partitions. This topic describes how to use FML statements to configure a data table.

Features

Fast Modeling Language (FML) is a language that uses standard SQL-like syntax to design data tables in dimensional modeling. In DataWorks, the design of a data model (a logical table) is decoupled from its materialization. When you design a logical table, you do not need to consider how it will be materialized on different big data engines. The modeling engine uses the schema defined in FML to perform operations on the underlying big data engines. During materialization, the modeling engine converts the logical table into a physical table for your selected engine. The engine then translates the FML statements into SQL syntax that the target engine can recognize, and submits and runs the statements.

Preparations

DataWorks does not support creating data tables using FML. You must first create a logical table using the visual interface. You can create the following types of tables:

Limits

DataWorks does not support using FML statements to create data tables or change table names. You can use FML statements only for editing operations on existing tables, such as editing fields, configuring associations, and configuring partitions.
You can use FML statements to materialize data tables only to the MaxCompute, Hologres, and Hive engines.
FML uses SQL keywords as reserved words. If your table name or a field name is a keyword, you must enclose it in backticks (``). Otherwise, an error occurs when you configure the table.

Go to the FML editor of a table

On the Dimensional Modeling page, double-click the name of the table in the directory tree.
On the Table Details page, click Code Editor.
In the FML editor on the Code Editor tab, you can view the FML CREATE TABLE statement for the table. You can also configure or modify the fields of the table. For more information, see Configure a table.

Configure a table

For ease of reference, all statements for configuring tables are presented in the format of a CREATE TABLE statement. Note that data modeling in DataWorks does not support creating tables using FML statements. When you use this feature, you need to refer only to the statements that define table content, such as constraints and partitions. The format of the FML statement for configuring the target table is as follows:

--Create a new table
      CREATE <table_type> TABLE
      IF NOT EXISTS
      --Table name
      <table_name> [ALIAS <alias>]
      --Define column properties
      <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>)>, --Group constraint
      CONSTRAINT <constraint_name> COLUMN_GROUP(<col_name>,...), 
      --Define comment
      COMMENT 'comment'
      --Define partitions
      PARTITION BY (col DATATYPE COMMENT 'comment' WITH ('key'='value',...), ...)
      --Define properties
      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
tableName	The name of the data table. We recommend that the name consists of letters, digits, and underscores (_) and is no more than 128 characters long.
if not exists	If a table with the same name already exists in the target engine and you do not specify `if not exists`, an error occurs. If you specify `if not exists`, the table is created successfully, regardless of whether a table with the same name exists.
alias	The alias of the table or a field in the table. This parameter is usually used to define the Chinese name of the data table. This parameter is optional.
tableType	The type of table to create. You can use FML statements to create the following types of tables: Dimension table Common dimension table (NORMAL): The default type specified when you create a dimension table. Hierarchy dimension table (LEVEL): Stores data with hierarchical relationships, such as provinces, cities, and districts. Enumeration dimension table (ENUM): Stores common enumerable values, such as male and female. Fact table Transaction fact table (TRANSACTION): The default type specified when you create a fact table. It records facts at the transaction level and stores the most atomic data. Periodic snapshot fact table (PERIODIC_SNAPSHOT): Saves fact records with regularity and predictable timing. It calculates statistics for measures over a time interval, such as from the beginning of history to the present or from the beginning of the calendar year to the present. It updates table data using methods such as incremental updates. Accumulating snapshot fact table (ACCUMULATING_SNAPSHOT): Stores snapshot information of transactional data to accumulate data over an uncertain period. For example, an order record snapshot fact table can include payment date, shipping date, and receipt date time points. DWS table: A logical aggregate table used to merge specific metrics. The table definition syntax is similar to that of dimension and fact tables. Lookup table: Also known as a standard code table. This is a code table that contains industry-specific attributes. For example, in the hydropower industry, you might create standard codes for whether a contract exists or for power supply contract types.
comment	The comment for the table. We recommend that the comment is no more than 1,024 characters long.
columnDefinition	The definition of a table column. It can include the following parameters: col_name: The name of the table column. It consists of letters, digits, and underscores (_). If the column name is an FML keyword, you must enclose it in backticks (``). alias: The alias of a column in the table. This parameter is usually used to define the Chinese name of the column. This parameter is optional. dataType: FML data types include BIGINT, STRING, VARCHAR, CHAR, DECIMAL, and DATETIME. category: Used to classify table columns. In dimensional modeling, columns can be classified as ATTRIBUTE, MEASUREMENT, or CORRELATION. Note FML statements support designing a table first and materializing it later. Therefore, a newly created table does not need to have column information.
constraint	Defines the constraints of the table schema. Valid values are: PRIMARY KEY constraint (PrimaryConstraint): The format is `PRIMARY KEY(col1, col2)`. The fields in the `col1, col2` list must be predefined. Dimension constraint (DimConstraint): The format is `DIM KEY(col1, col2) REFERENCES table_name(ref1, ref2)`. Hierarchy constraint (LevelConstraint): This constraint takes effect only in a hierarchy dimension table. It defines the levels within the hierarchy dimension.
Partitioned BY	Creates partitions for the table.
WITH	Custom information that you can define when you create a table. Use the `key=value` format. The `key` and `value` must be enclosed in single quotation marks to prevent conflicts between the custom information and FML keywords. The extension properties in the `WITH` clause can be parsed and processed by the engine that materializes the FML statements.