Use FML statements to configure and manage data tables

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

1. In the left-side navigation tree of the Dimensional Modeling page, double-click the name of a data table.
2. 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

-- 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'
    ;