Create and manage meta tables - Dataphin - Alibaba Cloud Documentation Center

Metatables are cross-storage tables that you can use to create and manage input, output, and dimension tables. This topic describes how to create and manage them.

Prerequisites

If your Dataphin instance is deployed in a public cloud dedicated instance (semi-managed) or a standalone deployment (private cloud or public cloud), you must purchase the Real-time Development value-added service. You must first enable Real-time Development for the current project to create and manage real-time metatables.

Benefits

Metatables provide the following benefits:

Security and reliability: Metatables prevent sensitive information leakage by disallowing native Flink DDL statements.
Improved efficiency and user experience: Create a table once and reuse it multiple times. This eliminates the need to write repetitive DDL statements or perform complex mappings, simplifying development and improving efficiency.
Data lineage: Metatables maintain upstream and downstream data lineage.

Use cases

You can use metatables in the following scenarios:

Platform-based management: Centrally maintain all real-time metatables and their related schema information.
Asset management: Uniformly configure and manage tables for real-time development.

Metatable page overview

Area	Description
① Toolbar	Provides controls to save, commit, publish, refresh, lock for editing, and locate the metatable.
② Basic information of the metatable	Displays the metatable's name, data source type, data source name, source table name, and connector name. Note If the data source of the metatable is set to Hive and the source table is a Hudi table, the connector is dp-hudi. If the data source of the metatable is set to Hive and the source table is a paimon table, the connector is paimon. If the data source of the metatable is set to Hive and the source table is an Iceberg table, the connector is iceberg.
③ Metatable structure operations	Use this area to search for table fields, add fields, export Flink DDL, sort, and parse. You can add fields in the following ways: Import by SQL: Import fields by using DDL statements. For more information, see Add fields by using SQL import. Batch Import: Import fields in batches from a text file. For more information, see Add fields by using batch import. Add a Single Row: Add a single field to the current metatable. For more information, see Add a single field by adding a row.
④ Metatable field list	Displays the fields parsed from the metatable. The list includes the sequence number, field name, metadata flag, Flink field type, raw field type, and description. You can also edit or delete fields.
⑤ Configure the metatable	Allows you to configure the properties of the metatable and view its version history. Metatable properties: Includes basic information, metatable parameter configurations, and debugging and testing configurations. For more information, see Configure metatable properties. History: Includes the version number, committer, commit time, and production status. For more information, see View the version history of a real-time compute table.

Procedure

Step 1: Create a metatable

On the Dataphin homepage, choose R&D > Data Development from the top navigation bar.
In the top navigation bar, select a Project. In the left-side navigation pane, choose Data Processing > Table Management.
Click the New icon in the Table Management list to open the New Table dialog box.

In the New Table dialog box, configure the parameters.

Parameter	Description
Table type	Select Metatable.
Metatable name	Enter a name for the metatable. The name must follow these rules: It can contain only uppercase letters, lowercase letters, digits, and underscores (_). It cannot start with a digit. It can be up to 64 characters long.
Data source	For more information about the real-time data sources that Dataphin supports and the table types you can create, see Real-time data sources supported by Dataphin. You can also create a custom real-time data source type. For more information, see Create a custom data source type. After you select a data source, you must configure its parameters based on the data source type. For more information, see Appendix: Configuration parameters for metatable data sources.
Source table	Enter or select a source table. Note When the data source is Hive, you can also select a Hudi, paimon, or Iceberg table. In the Source Table drop-down list, Hudi tables are marked with a icon, paimon tables with a icon, and Iceberg tables with an icon. You can read from, write to, and perform dimension table lookups on Iceberg tables only by using the Flink 1.15 engine. You cannot configure a source table if the data source is Log Service, DataHub, Kafka, Elasticsearch, Redis, or RabbitMQ.
Select directory	The default directory is Table Management. You can also create a target folder on the Table Management page and select it as the directory for the metatable. To do this, follow these steps: Above the Table Management list on the left side of the page, click the icon to open the New Folder dialog box. In the New Folder dialog box, enter a folder Name and select a directory as needed. Click OK.
Description	Enter a description (up to 1,000 characters).

Click OK to create the metatable.

Step 2: Add fields

Dataphin metatables support three methods for adding fields:

Note

If the metatable's data source is Hive and the source table is a paimon or Iceberg table, the field list is retrieved from metadata and is read-only.

SQL import

On the metatable configuration page, click +Add Field and select Import by SQL.
In the Import by SQL dialog box, enter your SQL code.
Note
- Dataphin provides code examples based on your data source type. Click the Reference Example icon in the window to view the corresponding sample code.
- After you write the code, click the Format icon to automatically format your code.
- If you select Also import parameter values from the WITH clause, Dataphin also imports the parameter values in the WITH clause.
The following code is an example for a MySQL data source:
```
create table import_table (
  retailer_code INT comment ''
  ,qty_order VARCHAR comment ''
  ,cig_bar_code INT comment ''
  ,org_code INT comment ''
  ,sale_reg_code INT comment ''
  ,order_date TIMESTAMP comment ''
  ,PRIMARY KEY(retailer_code)
) with (
  'connector' = 'mysql'
  ,'url' = 'jdbc'
  ,'table-name' = 'ads'
  ,'username' = 'dataphin'
);
```
Click OK to add the fields.

Batch import

On the metatable configuration page, click +Add Field and select Batch Import.

In the Batch Import dialog box, enter the field information according to the specified format.

Batch import format

Field name||Field type||Description||Is primary key||Is metadata

Example

ID||INT||Description||false||false
name||INT||Description||false||false

Click OK to add the fields.

Add a single row

On the metatable configuration page, click +Add Field and select Add a Single Row.

In the Add a Single Row dialog box, configure the parameters.

Parameter	Description
Is metadata	The default value is No. If you select Yes, you do not need to specify whether the field is a primary key or its raw field type. You only need to select the Flink SQL field type.
Field name	Enter a name for the field. The name can contain only uppercase letters, lowercase letters, digits, underscores (_), and periods (.). It cannot start with a digit.
Is primary key	Select whether this field is a primary key. Note If your data source is Kafka and the connector is Kafka, this setting indicates whether the field is a message key. If your data source is HBase, this setting indicates whether the field is a RowKey.
Field type and raw field type	For ApsaraDB for HBase data sources, fields do not have a raw field type. You only need to select the Flink SQL field type. If the field is not a RowKey, you must also specify the column family. For some data sources like Kafka, the raw field type is automatically inferred from the Flink SQL field type. In this case, you only need to select the Flink SQL field type. For other data sources like MySQL and Oracle, multiple raw field types might map to a single Flink SQL field type. In this case, you must select the raw field type first. You can then edit it to add precision.

Click OK to add the field.

Step 3: Configure metatable properties

After creating the metatable, click the Properties tab on the right side. You can then configure Basic information, Metatable parameters, and Reference information, and modify the data tables for debugging and testing.

Parameter		Description
Basic information	Metatable name	The name of the metatable you created. This parameter cannot be modified.
	Data source	The type of data source you selected during creation.
	Data source parameters	Each data source requires specific parameters, which may vary by computing engine. For more information, see Appendix: Configuration parameters for metatable data sources.
	Description	Enter a description for the metatable, up to 1,000 characters.
Metatable parameters	Parameter name	Different metatable parameters are available for different data source types. You can select a supported parameter and its description from the drop-down list or enter one manually. To add a new parameter, click Add Parameter. You can add up to 50 parameters. A parameter name can contain only letters, digits, underscores (_), hyphens (-), periods (.), colons (:), and forward slashes (/).
	Parameter value	The available options for the parameter value depend on the parameter type. If no options are available, you must enter a value manually. Single quotation marks are not supported. For example: Parameter Name:`address`, Parameter Value:`Ningbo`.
	Actions	Click the icon to delete the corresponding parameter.
Reference information	Task name	Displays the names of the Flink tasks that reference this metatable. Note Tasks in a draft state are not included in the reference information.
Default read for task debugging		Sets the default data table to read during task debugging. You can choose between a production table and a development table. If you choose to read from a production table, data from that table can be accessed during debugging. This poses a data security risk, so proceed with caution. If you set the default read table for debugging to a production table, you must apply for development and production data source permissions for your personal account. For more information about how to apply for data source permissions, see Apply for data source permissions. Note Debugging is not supported for Hive tables and paimon tables.
Read for development environment testing		Sets the default data table to read during task testing. You can choose between a production table and a development table. If you choose to read from a production table, data from that table can be accessed during testing. This poses a data security risk, so proceed with caution. If you set the default read table for testing to a production table, you must apply for development and production data source permissions for your personal account. For more information about how to apply for data source permissions, see Apply for data source permissions.
Write for development environment testing		You can choose to write to the current source table or another test table. If you choose another test table, you must select the appropriate table.

Click OK.

Step 4: Commit or publish the metatable

In the toolbar at the upper-left of the metatable page, click Commit.
In the Commit Note dialog box, enter a note.
Click OK and Commit.

If your project is in Dev-Prod mode, you must publish the metatable to the production environment. For more information, see Manage publishing tasks.

Appendix: Configuration parameters for metatable data sources

Data source	Configuration	Description
MaxCompute	Source table blinkType	Source table: The source table. blinkType: You can select `odps` or `continuous-odps`. odps: When used as a source table, it performs a full read. It can also be used as a sink table. continuous-odps: When used as a source table, it performs an incremental read. It cannot be used as a sink table.
Tablestore StarRocks Lindorm (wide column) Hologres ClickHouse AnalyticDB for PostgreSQL AnalyticDB for MySQL 3.0 Doris Hive paimon RocketMQ PolarDB-X ApsaraDB for HBase	Source table	Source table: The source table.
SAP HANA	Source table Update time field	Source table: The source table. Update time field: From the drop-down list, select the timestamp field in the SAP HANA table that indicates the update time, or enter a HANA SQL time string expression, such as `concat(column_date,column_time)`.
Log Service DataHub	Source topic	Source topic: The source topic.
MySQL PostgreSQL Oracle TiDB OceanBase MongoDB Microsoft SQL Server	Source table Read mode	Source table: The source table. Read mode: You can select JDBC read and write table or CDC Source table. JDBC read and write table: Uses JDBC to support table queries and writes, suitable for stream writing and batch read/write operations. CDC Source table: Performs incremental stream reading.
Kafka	Source topic Connector Message body format	Source topic: The source topic. Connector: You can select `kafka` or `upsert-kafka`. Message body format: Supported formats include `csv`, `json`, `avro`, `avro-confluent`, `debezium-json`, `canal-json`, `canal-json-insert`, `maxwell-json`, `ogg-json`, `dataphin-canal-json`, `raw`, `protobuf`, or a custom format. Primary key message format: If you select `upsert-kafka` as the connector, you must configure the primary key message format. Supported formats include `csv`, `json`, `avro`, `avro-confluent`, `debezium-json`, `protobuf`, or a custom format. Important Use `dataphin-canal-json` to process data that is integrated into Kafka in real time. If you use a custom format, ensure that it is packaged into a JAR file and uploaded as an additional dependency to the Flink task. Otherwise, your code may fail to reference or run correctly.
Hudi	Source table Hudi table type	Source table: The source table. Hudi table type: You can select `MERGE_ON_READ` or `COPY_ON_WRITE`. MERGE_ON_READ: Provides lower latency for write updates but higher latency for reads. COPY_ON_WRITE: Provides higher latency for write updates but lower latency for reads.
Elasticsearch	Connector Index Type name	Connector: You can select `Elasticsearch`, `Elasticsearch-6`, or `Elasticsearch-7`. Select `Elasticsearch` when using it as a source table or dimension table. Note Only Alibaba Cloud Elasticsearch is supported. Select `Elasticsearch-6` or `Elasticsearch-7` when using it as a sink table. Index: Enter or select the source index. Type name: Enter or select the type name.
Redis	None
RabbitMQ	Exchange Queue Routing Key	Exchange: Enter or select an exchange. Queue: Enter or select a queue. Routing Key (optional): Enter the routing key.
TDH Inceptor	Source table connector	Source table: The source table. connector: Select `dp-inceptor`.

Next steps

After creating a metatable, you can use it to develop real-time tasks. For more information, see the following topics: