You can create and manage MaxCompute tables in DataWorks using either MaxCompute SQL statements or the visual interface. The visual interface is more convenient. This topic describes how to use the visual interface to create and manage a table.
Notes
-
Table operation principles
Follow the basic operational requirements for MaxCompute when managing its tables. For example, you cannot delete fields from a table after it is created. For more information, see Usage limits on table operations.
-
Changes to physical table properties
You can change the physical properties of a MaxCompute table by running MaxCompute SQL commands. For more information, see Table operations. Due to DataWorks permission controls, a RAM user may encounter errors when running table operation commands. For more information, see MaxCompute data permission control details.
-
Metadata update latency
Metadata operations can be delayed. If you cannot find a table after creating or updating it, or if a table is still visible after you delete it, you can refresh the table metadata in Data Map to manually synchronize the changes.
Prerequisites
You have bound a MaxCompute compute engine.
Go to DataStudio
Log on to the DataWorks console. In the target region, click in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.
Create a MaxCompute table
Ways to create a table
You can create a table on the Table Management page or within a specific workflow.
-
Create from Table Management
In the left-side navigation pane of DataStudio, choose Table Management and click the
icon to create a table. If the Table Management module is not displayed in the navigation pane, see Customize module display in DataStudio to add it. -
Create within a workflow
DataWorks uses workflows to organize and manage code. You can create tables related to your business within a workflow. If you do not have a workflow, create one first. If you have a workflow, right-click it and choose .
ImportantIf you cannot find the button, check whether a MaxCompute compute engine is bound to the workspace.
Configure basic table information
After you create a table, the table editor tab opens. On this tab, you can configure the table information by using either the DDL mode or the visual interface.
-
Configure the table in DDL mode
Click the
icon on the table editor tab and use a standard MaxCompute table creation statement to generate the MaxCompute table schema. After you use a DDL statement to generate the schema, DataWorks automatically populates the configuration fields on the editor tab. This method is ideal for users who prefer writing code.Important-
The table name is defined before you open the editor tab. You cannot change the table name in the DDL statement; otherwise, an error occurs.
-
DDL mode supports defining only the physical properties of a table. You can edit the business properties on the table editor tab after the statement populates the UI.
-
-
Configure the table by using the visual interface
You can follow the guides on the table editor tab to configure the table properties. This method is convenient if you prefer a visual interface. The following example shows how to configure a table by using the visual interface.
-
Configure the General properties. The main parameters are described below.
Parameter
Description
Display name
The display name of the table.
Theme
In DataWorks, themes function like folders, allowing you to categorize tables by business purpose. You can define level-1 and level-2 themes to organize tables.
NoteThese themes help you organize and find tables on the Table Management page. If no suitable themes are available, you can create one. For more information, see Define table themes.
-
Design the Physical Model. The parameters are described below.
Parameter
Description
Level
The data warehouse layer for the table. Levels define data warehouse layers, which are typically divided into Operational Data Store (ODS), Dimension (DIM), Data Warehouse Detail (DWD), Data Warehouse Summary (DWS), and Application Data Service (ADS). You can assign the table to an appropriate level based on its business purpose.
NoteYou can also customize levels. For more information, see Level management.
Category
Defines the physical category of the table, which provides a more detailed classification from a business perspective. Common categories include Basic Business, Advanced Business, and Other.
NoteYou can also customize physical categories. For more information, see Category management.
Lifecycle
The lifecycle of the table.
-
For more information about the lifecycle of a MaxCompute table, see Lifecycle.
-
For information about lifecycle operations on a MaxCompute table, see Lifecycle operations.
Partition type
Specifies whether the table is a partitioned or non-partitioned table. For more information about partitioned and non-partitioned tables in MaxCompute, see Partition.
Table type
Specifies whether the table is an internal table or an external table. Internal tables manage their own data within MaxCompute, often leading to faster queries. External tables reference data stored outside MaxCompute, which can save storage space. For more information about internal and external tables in MaxCompute, see Table.
-
-
Design the Schema. The schema design page is divided into two sections: field definition and partition definition. The field definition section provides Add Field, Move Up, and Move Down buttons at the top. You can add a field by clicking the + Add Field link. The partition definition section includes additional parameters for Date Partition Format (for example, yyyymmdd) and Date Partition Granularity. The main parameters are described below.
Parameter
Description
Data type
The data type of the field. You can only select data types supported by MaxCompute from the drop-down list. For more information about MaxCompute data types, see Data type editions.
Field security level
The security level of the field. This parameter is visible only if label-based access control is enabled for the MaxCompute project. For more information about field security levels in MaxCompute, see Label-based access control.
Primary key
The business primary key for the table. MaxCompute does not enforce primary keys; this setting is for business management purposes only.
-
Commit and deploy the MaxCompute table
After you define the table schema, you must commit it to the development and production environments.
The process for committing and deploying MaxCompute tables varies based on the workspace mode:
-
basic mode: You only need to commit the table to the production environment.
-
standard mode: Only users with the Workspace Administrator or O&M role can deploy tables to the production environment. For more information about how to grant permissions, see Add workspace members and manage their roles and permissions.
|
Actions |
Description |
|
Load from development environment |
Loads the table definition from the development environment and populates the current editor. Important
This operation is available only after the table has been committed to the development environment. This action overwrites your current configuration with the committed definition. |
|
Commit to development environment |
Commits the current table configuration to the DataWorks development environment, which creates the table in the associated MaxCompute project. After a successful commit, you can:
|
|
Load from production environment |
Loads the table definition from the production environment and populates the current editor. Important
This operation is available only after the table has been committed to the production environment. This action overwrites your current configuration with the committed definition. |
|
Commit to production environment |
Commits the current table configuration to the DataWorks production environment, which creates the table in the associated MaxCompute project. After a successful commit, you can:
|
Write to and export from MaxCompute tables
This section describes how to import and export data for your MaxCompute table.
Write data to a MaxCompute table
You can use a data synchronization task or a MaxCompute node to write data into a MaxCompute table. DataWorks also supports uploading local data into a MaxCompute table.
Export data from a MaxCompute table
After querying data in DataStudio, you can export the data to a local file by clicking the Download button in the results panel. By default, you can download a maximum of 10,000 records at a time. Tenant administrators can change this limit on the Data Query and Analysis Control page in the Security Center.
To export more than 10,000 records, you can use the MaxCompute client and run a Tunnel command.
Query data in a MaxCompute table
You can use an ODPS SQL node or the ad hoc query feature to query data in a MaxCompute table by running SQL statements.
Default permissions
Workspaces in basic mode do not support fine-grained permission control or isolation between development and production environments. The following examples assume a workspace in standard mode.
When a RAM user is added as a member to a workspace, the following data access permissions are granted by default.
|
Permission type |
Description |
|
MaxCompute development project permissions |
DataWorks maps workspace-level roles to MaxCompute engine roles. A RAM user with a workspace role automatically receives the corresponding permissions in the development engine. By default, they have permissions on the project in the development environment but not the production environment. |
|
MaxCompute production project permissions |
By default, only the scheduling access identity has permissions on the production project. Other RAM users must request permissions in the Security Center to operate on production tables. DataWorks provides a default approval workflow and also allows administrators to customize it. |
For more information about MaxCompute data access control, see MaxCompute data permission control details.
Data access behavior
In DataStudio, you can query data in other projects by prefixing the table name with the project name. The following table describes cross-project access methods and the accounts used in different environments.
-
To view the MaxCompute compute engines and execution accounts configured for each environment, see the compute resource bindings in DataStudio. For more information, see Manage compute engines.
-
In a DataWorks workspace in standard mode, the development environment uses the personal identity of the task executor by default. The production scheduling environment uses a specific cloud account, known as the scheduling access identity. For more information, see Bind a MaxCompute compute engine.
|
Sample code |
Execution in development environment |
Execution in production environment |
|
Access a development table in the development project: |
The query runs using the personal Alibaba Cloud account of the task executor.
|
The query runs using the scheduling access identity. |
|
Access a production table in the production project: |
The query runs using the personal Alibaba Cloud account of the task executor. Note
Due to security controls on production data, personal Alibaba Cloud accounts do not have default access to production tables. You must request permissions in the Security Center. DataWorks provides a default approval workflow and also allows administrators to customize it. |
The query runs using the scheduling access identity. |
|
Run a statement in a target environment (for example, development) to access a table in that same environment: |
When run in the development environment, the query uses the personal Alibaba Cloud account of the task executor to access the target table in the development engine. |
When run in the production environment, the query uses the scheduling access identity to access the target table in the production engine. |
View MaxCompute data assets
You can view MaxCompute data assets in the following ways.
View production tables in a tenant
The Tenant Tables module in DataStudio lists all production tables in the current region that belong to your Alibaba Cloud account.
View metadata
Go to Data Map to view the details and metadata of a MaxCompute table.
View table lineage
On the table details page in Data Map, you can view the upstream and downstream lineage of the table.
To locate tasks in the workspace that operate on this table, you can use the code search feature.
Manage MaxCompute tables in batches
You can manage multiple MaxCompute tables at a time in the following ways.
Delete MaxCompute tables in batches
Go to My Data in Data Map to delete multiple tables that you own.
Due to security controls on production data, you cannot directly delete production tables by using a command. To delete tables you do not own, you must request table permissions in the Security Center.
Transfer ownership of tables in batches
Go to My Data in Data Map to transfer ownership of multiple tables that you own.
Modify the lifecycle of tables in batches
Go to My Data in Data Map to modify the lifecycle of multiple tables that you own.
The lifecycle of a single table can be modified in Table Management. For more information, see Create a MaxCompute table.