Modeling in Script Mode

After creating a data table, use FML statements in Script Mode to configure its fields and partitions.

Introduction

FML (fast modeling language) is an SQL-like language that helps you quickly build data tables for dimensional modeling. Its syntax is based on standard SQL. In DataWorks, data modeling decouples the design of a logical table from its implementation. This means you can design your model without considering the specifics of the underlying big data engine. The modeling engine uses the schema defined in FML to drive operations on the engine. To materialize a logical table into a physical table, the modeling engine converts the FML into the specific SQL dialect of the target engine and then runs the statements.

Prerequisites

DataWorks does not currently support creating data tables using FML. You must first create a logical table in the visual interface. For more information, see the following topics:

Limitations

DataWorks does not support creating data tables or modifying table names using FML statements. You can only edit existing data tables, for example, by editing table fields, configuring associations, or configuring partitions.
You can use FML statements to materialize data tables to MaxCompute, Hologres, or Hive engines only.
FML uses SQL keywords as reserved words. If your table name or a column name is a reserved word, you must enclose it in backticks (``) to escape it. Otherwise, an error occurs when you create the table.

Go to the FML editor

On the Dimensional Modeling page, double-click the name of the target table in the directory tree.

On the Table Details page, click Script Mode.

In the FML editor on the Script Mode tab, you can view the FML statement used to create the current table. You can also configure or modify the table's fields. For more information, see Configure a target data table.

-- After a model is published to a physical table, the table name cannot be modified. This applies during pre-approval, publishing, and post-publishing stages.
CREATE DIM TABLE dim_ec_pub_staff_df ALIAS 'Geographic administrative region dimension table'
(
    id           ALIAS 'Administrative region ID' STRING COMMENT 'Administrative region ID',
    name         ALIAS 'Administrative region name' STRING COMMENT 'Administrative region name',
    `level`      ALIAS 'Administrative region level. 1: Country; 2: Province/Autonomous Region/Municipality; 3: City; 4: District/County' INT COMMENT 'Administrative region level. 1: Country; 2: Province/Autonomous Region/Municipality; 3: City; 4: District/County',
    country_id   ALIAS 'Country ID' STRING COMMENT 'Country ID',
    country_name ALIAS 'Country name' STRING COMMENT 'Country name',
    province_id  ALIAS 'Province/Autonomous Region/Municipality ID' STRING COMMENT 'Province/Autonomous Region/Municipality ID',
    province_name ALIAS 'Province/Autonomous Region/Municipality name' STRING COMMENT 'Province/Autonomous Region/Municipality name',
    city_id      ALIAS 'City ID' STRING COMMENT 'City ID',
    city_name    ALIAS 'City name' STRING COMMENT 'City name',
    district_id  ALIAS 'District/County ID' STRING COMMENT 'District/County ID',
    district_name ALIAS 'District/County name' STRING COMMENT 'District/County name',
    ds           ALIAS 'Business date, yyyymmdd' STRING COMMENT 'Business date, yyyymmdd'
)
COMMENT 'Geographic administrative region dimension table'
WITH('life cycle'='1000');

Configure a target data table

For your convenience, all statements for configuration tables are presented in the format of a CREATE TABLE statement. However, the data modeling feature of DataWorks does not support creating tables by using FML statements. In practice, you only need to refer to the statements that define the table content, such as constraints and partitions. The format of an FML statement for configuring a target table is as follows.

--Create a new table
      CREATE <table_type> TABLE
      IF NOT EXISTS
      --Table name
      <table_name> [ALIAS <alias>]
      --Define column 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>)>, --Grouping constraint
      CONSTRAINT <constraint_name> COLUMN_GROUP(<col_name>,...), 
      --Define a 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. It should contain only letters, digits, and underscores (_) and be no longer than 128 characters.
if not exists	If a table with the same name already exists in the target engine, the `CREATE TABLE` statement reports an error if you do not specify `if not exists`. If you specify `if not exists`, the statement succeeds regardless of whether a table with the same name already exists.
alias	An optional alias for a data table or column, typically used as its display name.
tableType	The type of the table to create. You can use FML statements to create the following types of tables: Dimension table Common dimension table (NORMAL): The default type 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 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): Stores fact records that are regular and predictable over time. It aggregates measurements over a specific interval, such as year-to-date or all-time. The data in this table is typically updated incrementally. Accumulating snapshot fact table (ACCUMULATING_SNAPSHOT): Stores transaction snapshots to track data through a lifecycle of unpredictable duration. DWS table: A logical aggregate table used to merge specific metrics. Its definition syntax is similar to that of dimension and fact tables. Lookup table: Also known as a code table, it stores codes for standard, industry-specific attributes. For example, a utility might use a lookup table to standardize codes for power supply contract types.
comment	The table comment, with a recommended maximum length of 1,024 characters.
columnDefinition	The definition of a table column. It can include the following parameters: col_name: The name of the column. It can contain letters, digits, and underscores (_). If the column name is an FML reserved word, you must enclose it in backticks (``). alias: An optional alias for the column, typically used as its display name. dataType: The data type. FML supports data types such as BIGINT, STRING, VARCHAR, CHAR, DECIMAL, and DATETIME. category: The category of the column. In dimensional modeling, a column can be an attribute (ATTRIBUTE), a measurement (MEASUREMENT), or a correlation (CORRELATION). Note Because FML supports designing tables before materializing them, you can create a table without defining any columns.
constraint	Defines constraints on the table structure. Valid values are: Primary key constraint (PrimaryConstraint): The format is `PRIMARY KEY(col1, col2)`. The columns `col1, col2` must be previously defined fields. Dimension constraint (DimConstraint): The format is `DIM KEY(col1, col2) REFERENCES table_name(ref1, ref2)`. Hierarchy constraint (LevelConstraint): Takes effect only in a hierarchy dimension table. It defines the levels within the hierarchy.
Partitioned BY	Creates table partitions.
WITH	When you create a table, you can specify custom information in the `key=value` format. The `key` and `value` must be enclosed in single quotes to prevent conflicts with FML keywords. The extended properties in the `WITH` clause are parsed by the engine that materializes the FML statement.