Create and use MaxCompute tables

Go to DataStudio

Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.

Create a MaxCompute table

Entry points

You can create a table in the Table Management directory in DataWorks or create a table for a specific business process.

• Create from Table Management

  In the left-side navigation pane of DataStudio, choose Table Management and click the icon to create a table. If Table Management is not displayed in the left-side navigation pane, see Customize the UI to add it.

• Create from a business process

  DataWorks uses business processes to organize and manage code, and you can create tables relevant to them. If you do not have a business process, create one first. For more information, see Business processes. If a business process exists, you can right-click the business process and choose Create Table > MaxCompute > Table.

  Important

  If you cannot find the button, make sure that a MaxCompute computing resource is bound to the workspace.

Configure basic table information

After you create a table, the table editing page appears. On this page, DataWorks supports two methods for configuring table information: DDL mode and GUI mode.

• Use DDL mode to configure the table

  On the table editing page, click the icon and use a standard MaxCompute table creation statement to generate the table schema. After you use Data Definition Language (DDL) to generate the schema, DataWorks automatically populates the configurations on the page. You can use this method if you are accustomed to writing code for tasks.

  Important

  • The table name is defined before you go to the table editing page. You cannot change the table name in the DDL statement. Otherwise, an error is reported.

  • DDL mode allows you to define only the physical properties of a table using commands. After the DDL statement runs and populates the page, you can edit the table's business properties on the table editing page.

• Use the GUI to configure the table

  Configure the table properties based on the instructions on the table editing page. You can use this method if you want to perform convenient visual operations. The following example describes how to configure table information in GUI mode.

  1. Configure General. The following table describes the main parameters.

     Parameter	Description
     Display Name	The display name of the table.
     Theme	In DataWorks, a theme functions like a folder, defining a table's level-1 and level-2 location. You can classify tables based on business purposes and add the tables of the same type to the same folder. Note Level-1 and level-2 themes are presented as folders on the table management page in DataWorks to facilitate table management. You can quickly find the current table by theme on the Table Management page. If no themes are available, you can create one. For more information, see Define table themes.

  2. Physical Model. The following table describes the parameters.

     Parameter	Description
     Layer	Specifies the data warehouse layer to which the table belongs. Layers help define and manage the data warehouse structure. The layers can be divided into an Operational Data Store (ODS) layer, a Dimension (DIM) layer, a Data Warehouse Detail (DWD) layer, a Data Warehouse Summary (DWS) layer, and an Application Data Service (ADS) layer. You can add the table to a proper layer based on its business purpose. Note You can also customize layers. For more information, see Manage layers.
     Physical category	The physical category of the table. This parameter is used to classify tables in more detail from a business perspective. The categories include Basic Business Layer, Advanced Business Layer, and Others. Note You can also customize physical categories. For more information, see Manage categories.
     Lifecycle	The lifecycle of the table. For more information about the lifecycle of a MaxCompute table, see lifecycle. For more information about lifecycle operations for a MaxCompute table, see lifecycle operations.
     Partition Type	Specifies whether the table is a partitioned table or a 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 contain data managed by MaxCompute, which typically results in faster queries. External tables reference data in other locations, allowing you to query the data without importing it, which saves storage space. For more information about internal and external tables in MaxCompute, see Table.

  3. Schema. The Design the table schema section contains areas for field definition and partition configuration. The field definition area provides the Add Field, Move Up, and Move Down buttons. The columns in this area include Field Name, Display Name, Length/Settings, Description, and Actions (Save and Cancel). In the partition configuration area, you can click Add Partition to add a partition key and set its Date Partition Format, such as yyyymmdd, and Date Partition Granularity. The following table describes the main parameters.

     Parameter	Description
     Field Type	The data type of the field. You can select only 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 available only when 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	Specifies the business primary key. Note that MaxCompute tables do not enforce primary keys; this parameter is for business-level management and data modeling purposes only.

Commit and publish the table

After defining the table schema, you must commit the table to the development environment and then publish it to the production environment.

Write to and export from tables

You can write data to and export data from MaxCompute tables as described in the following sections.

Write data

You can write data to a MaxCompute table using a data integration task or a MaxCompute node task. In addition, DataWorks allows you to use the Upload Data feature to import local data into a MaxCompute table.

Export data

After you query data in DataStudio, you can click Download on the query result page to export the data to your local computer. By default, you can download a maximum of 10,000 records at a time. Tenant administrators and tenant security administrators can go to the Data Query and Analysis Control page in the Security Center to set the maximum number of records that can be downloaded at a time.

Query data in a MaxCompute table

You can use an ODPS SQL node or the ad hoc query feature to run SQL statements and query data in a MaxCompute table.

Default permissions

Workspaces in basic mode lack fine-grained permission control and environment isolation. The following examples assume a workspace in standard mode.

Data access behavior

MaxCompute supports cross-project table queries. Therefore, you can specify a project name on the DataStudio page to query production data across projects in a DataWorks workspace. The following table describes the methods for accessing tables across projects and the accounts used for execution on different pages.

select col1 from projectname_dev.tablename;

select col1 from projectname.tablename;

select col1 from tablename;

View MaxCompute data assets

You can view MaxCompute data assets as described in the following sections.

View production tables in a tenant

In the Tenant Tables module of Data Development, you can view all production tables in the current region that belong to your Alibaba Cloud account.

View metadata

You can go to DataMap to view the details and metadata of MaxCompute tables.

View table lineage

On the table details page in DataMap, you can view the upstream and downstream lineage of the table.

Manage tables in batches

You can manage MaxCompute tables in batches as described in the following sections.

Delete tables in batches

Change table owners in batches

You can go to My Data in DataMap to change the owners of tables that you own in batches.

Modify table lifecycles in batches

You can go to My Data in DataMap to modify the lifecycles of tables that you own in batches.