MaxCompute:Manage internal tables, external tables, and views

Background

Create tables or views

1. In the left-side navigation pane of IntelliJ IDEA, click Project Explorer. Right-click Tables & Views in your target MaxCompute project, and select Create new table.

2. In the Create New Table/View dialog box, configure the settings to create an internal table, an external table, or a view.

   • Create an internal table or an external table

     You can create an internal or external table in two ways:

     • (Recommended) Method 1: Create a table using an SQL script. The following table describes the parameter.

       Parameter	Description
       SQL DDL SCRIPT	To create a table using an SQL script, write the script in the SQL DDL SCRIPT area. No other parameters are required. For more information about the table creation syntax, see Create tables.

     • Method 2: Create a table by configuring parameters in the GUI. The following table describes the parameters.

       Parameter	Description
       Create Table	The name of the new internal or external table. Select the External Tables checkbox to create an external table. If you do not select the External Tables and View checkboxes, an internal table is created. If you select the if not exists checkbox, the operation succeeds even if a table with the same name already exists, regardless of whether the schema of the existing table is different. The existing table and its metadata are not changed.
       Comment	The comment for the internal or external table.
       Lifecycle	The lifecycle of the internal or external table, in days.
       Stored as AliOrc	The storage format of the internal or external table.
       Like table	This option creates a new table that inherits the schema of an existing table but not its data or lifecycle information. You can select a source table from the drop-down list. This is equivalent to the following statement: create table table_name like model_table;
       Data Column	Manually add non-partition key columns to the internal or external table. To the right of the Data Column area, click the icon to add a non-partition key column. MaxCompute Studio automatically generates the column name, type, not null attribute, and comment. You must then adjust these values as needed.
       Partition Key Column	Manually add partition key columns to the internal or external table. To the right of the Partition Key Column area, click the icon to add a partition key column. MaxCompute Studio automatically generates the column name, type, and comment. You must then adjust these values as needed.
       Index	Configure this parameter if the internal or external table requires hash or range properties.
       External Tables	Configure the Storage Handler, SERDEPROPERTIES, Location, and Using properties for the external table. For more information about these properties, see Create an OSS external table.
       AS SELECT	Creates an internal or external table from another table and copies its data. It does not copy the source table's partitions or lifecycle information. The partitions are converted to regular columns. The following code provides an example: select col1, col2 from sale_detail; This is equivalent to the following statement: create table table_name (colname1, colname2) as select col1, col2 from sale_detail;
       Additional Attribute	Manually add other table properties for the internal or external table. For a detailed list of properties, see Property list.
       SQL DDL SCRIPT	After you configure the parameters in the GUI, the GUI automatically generates an SQL script in the SQL DDL SCRIPT area. Note If you manually modify the script in the SQL DDL SCRIPT area, MaxCompute Studio uses the modified script to create the table.

   • Create a view

     You can create a view in two ways:

     • (Recommended) Method 1: Create a view using an SQL script. The following table describes the parameter.

       Parameter	Description
       SQL DDL SCRIPT	To create a view using an SQL script, write the script in the SQL DDL SCRIPT area. No other parameters are required. For more information on view creation syntax, see Create or update views.

     • Method 2: Create a view by configuring parameters in the GUI. The following table describes the parameters.

       Parameter	Description
       Create view	The name of the new view. Select the View checkbox to create a view. If you select the if not exists checkbox, the operation succeeds even if a view with the same name already exists, regardless of whether the schema of the existing view is different. The existing view is not changed. Select the Replace if exists checkbox to replace the view if one with the same name already exists.
       Comment	The comment for the new view.
       Data Column	Manually add columns to the view. To the right of the Data Column area, click the icon to add a column. MaxCompute Studio automatically generates the column name. You must then adjust it as needed.
       AS SELECT	Creates a view from an existing table or view. This is equivalent to the following statement: create view view_name (colname1, colname2) as select col1, col2 from sale_detail;
       SQL DDL SCRIPT	After you configure the parameters in the GUI, the GUI automatically generates an SQL script in the SQL DDL SCRIPT area. Note If you manually modify the script in the SQL DDL SCRIPT area, MaxCompute Studio uses the modified script to create the view.

3. In the Create New Table/View dialog box, click Execution. After a SUCCESS message appears, click OK.

   Note

   When you create an internal table, external table, or view by using the GUI, the following global properties are used by default:

   • odps.sql.submit.mode=script: Indicates that the creation command is submitted in script mode.

   • odps.sql.type.system.odps2=true: Indicates that MaxCompute V2.0 data types are used.

4. Right-click Tables & Views in the target MaxCompute project and select Refresh metadata. The new internal table, external table, or view then appears in the list.

Modify tables

1. In the left-side navigation pane, click Project Explorer. In your target MaxCompute project, expand Tables & Views, right-click the internal table or external table that you want to modify, and select Open table editor.

2. In the Modify Table dialog box, edit the table.

   You can modify the table name, table comment, lifecycle, column names, and column comments. You can also add columns, an index, and other table properties. For detailed parameter descriptions, see Create an internal table, an external table, or a view. At the top of the dialog box, you can view and modify basic information such as the table name, Comment, and Lifecycle. The middle section provides four tabs: Columns, Partitions, Indices, and Table Properties. On the Columns tab, you can add, delete, or reorder columns. MaxCompute Studio automatically generates the corresponding DDL statement, such as ALTER TABLE table_2 ADD COLUMNS (col5 BIGINT);, in the SQL DDL SCRIPT area at the bottom. After you finish editing, click the Submit to MaxCompute button.

3. In the Modify Table dialog box, click Execution to apply the changes.

4. Right-click Tables & Views in the target MaxCompute project and select Refresh metadata. The updated table appears in the list.

Table and view details

• View the details of an internal table or an external table

  In the target MaxCompute project, under Tables & Views, double-click a table name or right-click it and select Show table detail. You can then view the following information about the table:

  • Table information: Includes the table's MaxCompute project, owner, creation time, and last update time.

  • Table schema: Includes columns, data types, not null properties, and default values.

  • Table data: In the area at the bottom, specify Partitions (for partitioned tables only) and the number of Preview rows. Then, click Data Preview to view the table data.

• View the details of a view

  In the target MaxCompute project, under Tables & Views, double-click a view name or right-click it and select Show table detail to view the query statement that defines the view.

Clear table data or delete partitions

In the target MaxCompute project, under Tables & Views, right-click the target table name and select Truncate table/Delete partition. This option clears the data from a non-partitioned table or deletes a specified partition from a partitioned table.

Delete tables or views

1. In the left-side navigation pane of IntelliJ IDEA, click Project Explorer. In your target MaxCompute project, under Tables & Views, right-click the internal table, external table, or view that you want to delete, and select Drop table from server.

2. In the Confirmation Required dialog box, click OK to confirm the deletion.

3. Right-click Tables & Views in the target MaxCompute project and select Refresh metadata. The deleted object no longer appears in the list.