Configure data tables with FML in Script mode modeling - DataWorks

Fast Modeling Language (FML) is a SQL-like language for defining data tables in dimensional modeling. In DataWorks, FML decouples design from implementation: during the design phase, you define table structure without specifying the underlying engine. When you materialize a logical table into a physical table, the modeling engine translates FML into engine-specific SQL and submits it for execution.

Limitations

No table creation or renaming. FML can only edit existing tables — modifying columns, configuring relationships, and setting up partitions. To create a table first, see Prerequisites.
Supported target engines. FML tables can only be materialized to MaxCompute, Hologres, and Hive.
Reserved words. FML treats SQL keywords as reserved words. Enclose table or column names that conflict with keywords in backticks (` ``) to avoid errors.

Prerequisites

Before you begin, ensure that you have created a logical table in the visual interface:

Open the FML editor

On the Dimensional Modeling page, double-click the target table name in the directory tree.
On the Field Management page, click Script Mode. The FML editor opens on the Script Mode tab, showing the FML statement for the current table. You can view, configure, or modify the table's columns here.

FML syntax

The following shows the complete CREATE TABLE syntax for reference. Because DataWorks does not support creating tables with FML, use only the clauses that define table content: columns, constraints, and partitions. The CREATE <table_type> TABLE and IF NOT EXISTS lines are included for syntax completeness only — do not use them in the FML editor.

-- For reference only: table creation is not supported in DataWorks
CREATE <table_type> TABLE
IF NOT EXISTS

-- Table name and optional display name
<table_name> [ALIAS <alias>]

-- Column definitions
<col_name> [ALIAS <alias>] <datatype> [<category>] [COMMENT <comment>] [WITH (<key>=<value>,...)]

-- Constraints
PRIMARY KEY (<col_name>),
CONSTRAINT <constraint_name> DIM KEY (<col_name>) REFERENCES <ref_table_name> (<ref_table_col_name>),
CONSTRAINT <constraint_name> LEVEL <col_name:(<col_name>)>,
CONSTRAINT <constraint_name> COLUMN_GROUP(<col_name>,...),

-- Table comment
COMMENT 'comment'

-- Partitions
PARTITION BY (col DATATYPE COMMENT 'comment' WITH ('key'='value',...), ...)

-- Extended properties
WITH ('key'='value', 'key1'='value1', ...)
;

Table type grammar:

tableType
    : dimDetailType? DIM
    | factDetailType? FACT
    | CODE
    | DWS
    ;

dimDetailType
    : NORMAL
    | LEVEL
    | ENUM
    ;

factDetailType
    : TRANSACTION
    | AGGREGATE
    | PERIODIC_SNAPSHOT
    | ACCUMULATING_SNAPSHOT
    | CONSOLIDATED
    ;

Parameters

tableName

The table name. Valid names contain only letters, digits, and underscores (_) and are no more than 128 characters long.

if not exists

Without this clause, CREATE TABLE reports an error if a table with the same name already exists in the target engine. With this clause, the operation succeeds regardless.

alias

An optional alias for a table or column, typically used as a display name.

tableType

The table type. Each type maps to a dimensional modeling concept:

Dimension tables (DIM)

Type	FML keyword	Use when
Common dimension table	`NORMAL`	Default type for most dimension tables.
Hierarchy dimension table	`LEVEL`	Data has hierarchical relationships, such as provinces, cities, and districts.
Enumeration dimension table	`ENUM`	Stores frequently used enumerated values, such as gender.

Fact tables (FACT)

Type	FML keyword	Use when
Transaction fact table	`TRANSACTION`	Default type. Records one row per transaction at the most atomic level.
Periodic snapshot fact table	`PERIODIC_SNAPSHOT`	Captures aggregated metrics at fixed intervals, such as daily account balances or year-to-date sales. Data is typically updated incrementally.
Accumulating snapshot fact table	`ACCUMULATING_SNAPSHOT`	Tracks a record through an unpredictable lifecycle — for example, an order fact table that captures timestamps for payment, shipping, and delivery, updated each time the order progresses.

Other table types

Type	FML keyword	Description
Data Warehouse Summary (DWS) table	`DWS`	A logical aggregate table that combines specific metrics. Definition syntax is similar to dimension and fact tables.
Lookup table	`CODE`	Also called a standard code table. Stores industry-specific codes, such as contract types or power supply classifications.

comment

The table comment. The recommended maximum length is 1,024 characters.

columnDefinition

Defines the columns in the table. Each column uses the following sub-parameters:

Sub-parameter	Required	Description
`col_name`	Yes	The column name. Valid names contain only letters, digits, and underscores. Enclose in backticks (` ``) if the name is an FML reserved word.
`alias`	No	A display name for the column.
`datatype`	Yes	The column data type. Supported types include `BIGINT`, `STRING`, `VARCHAR`, `CHAR`, `DECIMAL`, and `DATETIME`.
`category`	No	The column category in dimensional modeling: `ATTRIBUTE`, `MEASUREMENT`, or `CORRELATION`.
`comment`	No	A description of the column.
`WITH (key=value, ...)`	No	Additional column properties in key-value format.

Because FML separates design from materialization, you can define a table without specifying any columns.

PARTITION BY

Defines the table's partition columns. Partitions let you organize data by a column value, such as date, to improve query performance.

PARTITION BY (ds STRING COMMENT 'date partition' WITH ('key'='value'), ...)

WITH

Specifies extended properties in 'key'='value' format. Both the key and value must be enclosed in single quotes to prevent conflicts with FML keywords. The engine that materializes the FML statement parses and processes these properties.

WITH ('lifecycle'='7', 'storage_format'='orc')