General Configuration - Auto Table Creation Settings

Version

Application Scenario

When you use Data Pipeline, Data Synchronization, and Data Transformation for cross-database data synchronization, the complexity of business data leads to mixed case usage in the source database and inconsistent default case rules across different source databases, resulting in inconsistent case usage in the target database, causing management and usage confusion.

When using FineDataLink to automatically create tables and synchronize databases to the target databases, you may want to standardize the case of table and field names to establish management specifications.

Function Description

Allowed toggling and correcting cases of table names and field names automatically if Auto Created Table was selected to synchronize data in the Pipeline Task Configuration tasks, and the Data Synchronization and Data Transformation nodes in FineDataLink.

Limitation

1. Types of data sources currently supported by General Configuration include: ClickHouse, GaussDB 200, Greenplum, Greenplum/Greenplum (parallel loading)/FineData, Hive, Hive (HDFS), HSQL, IBM DB2, Impala, Informix, MongoDB, MySQL, Oracle, PostgreSQL, Presto, SAP HANA, SQL Server, SQLite, Dameng, Huawei GaussDB 200, Gbase 8A (Nanda General), KingbaseES (Renmin Jincang), YMatrix, SelectDB, and InterSystems IRIS.

2. General Configuration can only be set by the super admin. Use and Management permissions on this function cannot be assigned to other users.

3. In FineDataLink of versions earlier than 4.2.2.1, the Auto Table Creation Configuration function is not supported in a cluster environment. In FineDataLink of 4.2.2.1 and later versions, this function is supported in a cluster environment.

Performing General Configuration

Choose System Management > General Configuration, and click Auto Table Creation Configuration, as shown in the following figure.

Select the specified data connection to batch modify the table names and field names in the corresponding database, as shown in the following figure.

Note:

The Applicable Object allows selection only from data connections already configured under System Management > Data Connection.

Effect Display

Create a Data Synchronization node in Data Development. Write the data into the fdl_demotest data connection for which Auto Table Creation Configuration has been set in "Performing General Configuration" to Table Name and Field NameAll Uppercase.

In this case, during the automatic creation of the target data table, the input table name will be automatically converted to uppercase according to General Configuration, and the target table field names in the field mapping will also be changed to uppercase, as shown in the following figure.

Similarly, for a Data Pipeline task, during the automatic creation of the target data table, the input table name will be automatically converted to uppercase according to General Configuration, and the target table field names in the field mapping will also be changed to uppercase, as shown in the following figure.

1. Data Pipeline - Compatibility when selecting an existing table/automatically creating a table.

Auto Created Table: When you configure a task, if you have selected case conversion and prefix/suffix settings, FineDataLink will first apply your case conversion and prefix/suffix configurations to the table and field names. Only after these operations are performed will the table be automatically created. If you manually modify the table or field names before applying the prefix/suffix settings, your changes will override any automatic operations on the names.

2. The current synchronization timestamp and Logical Deletion marker fields in Data Pipeline default to lowercase. These fields follow the same rules as other fields and change according to your case settings; if you enable the all-uppercase setting, the timestamp and Logical Deletion marker should also be uppercase.

3. Enable the case settings if the task already exists.

• Data Pipeline:

For tasks that are not running, when the case settings of the data connection are configured to all-uppercase or all-lowercase, the field and table names in those tasks will be set according to General Configuration.

For running tasks, General Configuration does not take effect.

• Scheduled Task:

Tables are automatically created strictly following the table and field names configured by you on the scheduled task page, and the General Configuration does not take effect.

When you select a new data destination (including reselection), table and field names will be adjusted to match the case settings of the chosen data connection. Field mappings will be cleared, and the configuration will be reset to a new node’s state.

After you click Reobtain on the field mapping page, field names will be standardized according to the case settings of the selected data connection.

Problem:

After the auto table creation process runs, the system prompts that the report does not exist. Additionally, the SQL statements generated during auto table creation fail to execute.

Cause:

Certain database types have specific limitations. For example, in MySQL, the combined length of all VARCHAR columns in a table cannot exceed 65,535 bytes.

Solution:

You need to check whether the database constraints have been violated and adjust the field type or length.