DocsICP FilingConsole
Documentation
Home Page Dataphin Intelligent Data construction and governance Dataphin-sharing mode (fully managed version) User Guide Data development Coding and development Table management Create and edit offline physical tables

Create and edit offline physical tables

Updated at:
Copy as MD
Product
My Favorites

Offline physical tables allow you to centrally configure and manage the tables used in compute task development, which improves development efficiency.

Limitations

  • You must purchase the data standard module to set the data standard field in a table.

  • You must purchase the asset security module to set the data classification and data categorization fields in a table.

  • Only the MaxCompute, , and compute engines are supported.

Offline physical table

Step 1: Configure basic information

  1. In the top navigation bar of the Dataphin homepage, choose Develop > Data Development.

  2. In the top navigation bar, select a Project. In Dev-Prod mode, also select an Environment.

  3. In the left navigation pane, choose Data Processing > Table Management.

  4. In the Table Management list, click the image icon and select Offline Physical Table.

  5. In the Create Physical Table wizard, configure the following parameters. The required parameters vary depending on the computing engine.

    MaxCompute

    Parameter

    Description

    Table name

    Enter a name for the offline physical table. The name can contain only letters, digits, and underscores (_) and be up to 128 characters long.

    Directory

    Select the directory to store the offline physical table.

    If a required directory does not exist, click New Folder to create it. Follow these steps:

    1. Click the image icon above the list of compute tasks on the left to open the New Folder dialog box.

    2. In the New Folder dialog box, enter a Name for the folder, set Type to Offline, and, as needed, select a location under Select Directory.

    3. Click OK.

    Data domain (Optional)

    Select the data domain for the table. If no data domains are available, you can create one. For more information, see Create a data domain.

    Description (Optional)

    Enter a description of up to 1,000 characters.

  6. After configuring the parameters, click Next.

Step 2: Configure field list

Supported storage formats vary by compute engine. For details, see table management settings. Field list configurations also vary by storage format. See the following table for details.

Other storage formats

  1. On the Field list page, configure the schema for the current physical table, including its fields, data types, and data classification.

    image

    Section

    Description

    ① Field list operations

    • Search: Search for fields by name.

    • Edit DDL: Edit the DDL statement for the physical table.

    • Add data fields: Click Add Field, select Data Field, Partition Field, or Quick Add Date Partition, and in the new field row, enter the Field Name, Data Type, and Description.

      • Add Data Field: Adds a regular data field.

      • Add Partition Field: Adds a partition field.

      • Quick Add Date Partition: Adds a date partition field that defaults to ds.

    • Import from a DDL statement: Import new fields by using a DDL statement. Click Import from DDL statement. In the Import from DDL statement dialog box, follow the instructions in the figure below: enter a DDL statement, click Parse SQL, select the fields that you want to import from the parsed results, and then click Add.image..png

    • Import from table: Click Import from table. In the Import from table dialog box, select the source table, choose the fields to import, and then click Add.

    ② Field list

    The field list displays detailed information about each field, including No., Field name, Data type, Description, Data standard, Data classification, and Data security level.

    • No.: The sequence number of the field, which auto-increments for each new field.

    • Field name: The name of the table field. You can search for fields by entering a full root word. The system automatically matches root words configured in Data Governance > Data Standard > Root Word.

    • Data type: Supported data types include string, bigint, double, timestamp, decimal, Text, Numeric, Date/Time, and Other.

      • Text: varchar, char.

      • Numeric: int, smallint, tinyint, float.

      • Date/Time: date. The MaxCompute compute engine supports datetime.

      • Other: boolean, binary.

    • Description: The description of the field, limited to 512 characters.

    • Data standard: Select a data standard for the field. To create a data standard, see Create and manage data standards.

    • Data classification: Select a data classification for the field. To create a data classification, see Create a data classification.

    • Data security level: The system automatically assigns the data security level after you select a data classification.

    You can also Delete a field in the Actions column.

    Note

    This action cannot be undone.

    ③ Batch operations

    Select multiple fields to perform the following batch operations:

    • Delete: Click the image icon to delete the selected data fields.

    • Root word-based naming: Click the image icon. The system tokenizes the content in the Description field, matches the tokens with existing root words, and recommends field names. In the Root word-based naming dialog box, you can replace the names of the selected fields with the recommended names.

      Note

      • If a recommended name is unsuitable, you can modify it in the Corrected field name input box.

      • Click Reset to revert the Modified field name to the system-recommended value.

    • Data standard: Click the image icon. The system recommends data standards based on the field names. In the Data standard dialog box, you can apply the recommended data standards to the fields.

  2. After adding the fields, click Next.

Step 3: Storage configuration

Supported storage configurations for the MaxCompute vary by storage format. See the following table for details.

MaxCompute

  • MaxCompute internal table

    Parameter

    Description

    Storage type

    Select internal table.

    Is transactional table

    Select Yes or No. If you select Yes, you can configure the primary key to create a Delta table.

    Primary key (Optional)

    Select one or more primary keys. All fields in the field list are available for selection. If you select multiple primary keys, they are sorted in the order of selection.

    Lifecycle (Optional)

    Specifies the table's retention period. Enter a positive integer for the number of days, or select a preset value of 7, 14, 30, or 360.

  • MaxCompute external table

    Parameter

    Description

    Storage type

    Select external table.

    Storage format

    Select a storage format. It defaults to the format specified for external tables in the platform's Table Management settings. You can select parquet, avro, rcfile, orc, textfile, or sequencefile.

    Location

    Enter the Location (storage location). Global variables are supported. The value can be up to 512 characters long.

After you configure the parameters, click Submit. In the Submit dialog box, review the SQL statement for creating the table and click Confirm and Submit.

Import data

After submitting an offline physical table, you can import data into it.

  1. In the Table Management list, click the target offline physical table. You can also find the table by searching for its name.

  2. On the Table Details page, click Import Data to open the Import Data dialog box.

  3. In the Basic Configuration step of the Import Data dialog box, upload your data and configure the import parameters.

    Parameter

    Description

    Upload file

    Click Select File to upload your data file. Only .txt and .csv files are supported. The file size cannot exceed 10 MB.

    Delimiter

    Specifies the delimiter used in your data file. Supported delimiters include comma (,), tab (\t), vertical bar (|), and forward slash (/). You can also specify a custom delimiter.

    Character encoding

    Select the character encoding of the uploaded data file. Supported encodings include UTF-8 (without BOM), UTF-8 (with BOM), GBK, Big5, GB2312, ASCII, and UTF-16.

    First row as header

    Select whether the first row of your data file is a header.

    Target partition

    If the table is a partitioned table, enter the target partition name.

  4. Click Next.

  5. In the Import Data step, configure the column mappings.

    • Mapping:

      • Map by Row: Maps columns based on their order.

      • Map by Name: Maps columns based on matching names.

    • Imported file data column: Map each source column to a target column, a null value, or a fixed value.

  6. Click Start import to import the data into the table.

Edit an offline physical table

After submitting an offline physical table, you can edit its properties.

  1. In the table management list, click the offline physical table. You can also search for the table by entering a keyword from its name.

  2. On the table details page, click Edit to open the Edit Physical Table page.

    The editable parameters depend on the storage format. See the following tables for details.

    MaxCompute

    • MaxCompute internal table

      Tab

      Description

      Basic information

      You can only edit the Table Name and Description. The parameter requirements are the same as when creating an offline physical table.

      Field list

      You can edit, delete, and add fields. The field requirements are the same as when creating an offline physical table. A DDL statement is generated when you modify a field's data type.

      You can deselect the Not Null option for a field.

      • If the table is an internal transactional table, you cannot deselect the Not Null option for fields that are set as the primary key.

      • You can deselect the Not Null option for a field only if it is already selected.

      Storage configuration

      You can edit the lifecycle. The requirements are the same as when creating an offline physical table.

    • MaxCompute external table

      Tab

      Description

      Basic information

      You can only edit the Table Name and Description. The parameter requirements are the same as when creating an offline physical table.

      Field list

      You can edit, delete, and add fields. The field requirements are the same as when creating an offline physical table. A DDL statement is generated when you modify a field's data type.

      You can deselect the Not Null option for a field only if it is already selected.

      Storage configuration

      You can edit the storage location (the Location parameter). The requirements are the same as when creating an offline physical table.

    After you finish editing, click Submit. In the Submit dialog box, review the SQL statement for creating the table, and then click Confirm and Submit.

    Note

    If the changes include operations such as deleting a field or modifying a field data type, you must first enable schema evolution at the project level in MaxCompute by running the command setproject odps.schema.evolution.enable=true;.

Next steps

  • In Dev-Prod mode, you need to publish the offline physical table. For more information, see Manage publish tasks.

  • In Basic mode, the submitted offline physical table can be managed in data assets. For more information, see View data assets.

Previous：Table managementNext：View and manage physical table-related objects