MaxCompute:Manage internal tables, external tables, or views in a visualized manner

Background information

Create an internal table, an external table, or a view

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

2. In the Create New Table/View dialog box, configure the required information to create an internal table, an external table, or a view based on your business requirements.

   • Create an internal table or an external table

     You can use one of the following methods to create an internal table or an external table:

     • (Recommended) Method 1: Use an SQL script to create an internal table or an external table. The following table describes how to use this method.

       Method	Description
       Write a script in SQL DDL SCRIPT.	If you want to create a table by using an SQL script, write the SQL script in the SQL DDL SCRIPT section and execute the script. You do not need to configure other parameters. For more information about the table creation syntax, see the "Create a table" section in Table operations.

     • Method 2: Create a table by configuring parameters. The following table describes the parameters that you must configure.

       Parameter	Description
       Create Table	Enter the name of the internal table or external table that you want to create. If you select External, an external table is created. If you do not select External or View, an internal table is created. If you select If not exists, a success message is returned when you create an internal table or external table that has the same name as an existing table, even if the schema of the existing table is different from that of the new table. However, the metadata of the existing table with the same name remains unchanged.
       Comment	Enter a comment on the internal table or external table.
       Lifecycle	Enter a value to specify the lifecycle of the internal table or external table. Unit: days.
       Stored as AliOrc	If you select Stored as AliOrc, the storage format of the internal table or external table is AliORC.
       Like Table	If you select Like Table, you can create a table based on an existing table in a MaxCompute project. The new table and the existing table have the same schema, but the data and lifecycle of the existing table are not replicated to the new table. You can select an existing table from the drop-down list. The table creation operation is equivalent to the following statement: create table table_name like model_table;
       Columns	Add non-partition key columns to the internal table or external table. Click the icon on the right side of the Columns tab to add a non-partition key column. Values of the name, type, not null, and comment parameters are automatically generated for the column. You need to manually modify the settings based on your business requirements.
       Partitions	Add partition key columns to the internal table or external table. Click the icon on the right side of the Partitions tab to add a partition key column. Values of the name, type, and comment parameters are automatically generated for the column. You need to manually modify the settings based on your business requirements.
       Indices	Configure this parameter if the hash or range property is required for the internal table or external table.
       External Table	Configure the Storage Handler, SERDEPROPERTIES, Location, and Using properties of the external table. For more information about the properties of external tables, see Create an OSS external table.
       AS SELECT	Create an internal table or an external table based on an existing table and replicate data from the existing table to the new table. However, partition key columns and the lifecycle of the existing table are not replicated to the new table. Partition key columns are changed to common columns in the new table. Sample statement: select col1, col2 from sale_detail; The table creation operation is equivalent to the following statement: create table table_name (colname1, colname2) as select col1, col2 from sale_detail;
       Table Properties	Add other properties for the internal table or external table. For more information about properties, see Properties.
       SQL DDL SCRIPT	After you configure the preceding parameters, an SQL script is automatically generated in the SQL DDL SCRIPT section. Note If you manually modify the script in the SQL DDL SCRIPT section, MaxCompute Studio uses the updated script in the SQL DDL SCRIPT section to create a table.

   • Create a view

     You can use one of the following methods to create a view:

     • (Recommended) Method 1: Use an SQL script to create a view. The following table describes how to use this method.

       Method	Description
       SQL DDL SCRIPT	If you want to create a view by using an SQL script, write the SQL script in the SQL DDL SCRIPT section and execute the script. You do not need to configure other parameters. For more information about the view creation syntax, see the "Create or update a view" section in View-related operations.

     • Method 2: Create a view by configuring parameters. The following table describes the parameters that you must configure.

       Parameter	Description
       Create Table	Enter the name of the view that you want to create. If you select View, a view is created. If you select if not exists, a success message is returned when you create a view that has the same name as an existing view, even if the schema of the existing view is different from that of the new view. The existing view remains unchanged. If you select Replace if exists and a view that has the same name already exists, a new view is created to replace the existing view.
       Comment	Enter a comment on the view.
       Columns	Add columns to the view. Click the icon on the right side of the Columns tab to add a column. The value of the name parameter is automatically generated for the column. You need to manually change the setting based on your business requirements.
       AS SELECT	Create a view based on an existing table or view. The view creation operation is equivalent to the following statement: create table view_name (colname1, colname2) as select col1, col2 from sale_detail;
       SQL DDL SCRIPT	After you configure the preceding parameters, an SQL script is automatically generated in the SQL DDL SCRIPT section. Note If you manually modify the script in the SQL DDL SCRIPT section, MaxCompute Studio uses the updated script in the SQL DDL SCRIPT section to create a view.

3. In the Create New Table/View dialog box, click Execute. If SUCCESS is displayed, click OK.

   Note

   When you create an internal table, an external table, or a view in a visualized manner, the following global properties are used by default:

   • odps.sql.submit.mode=script: The statement used to create a table is submitted by using scripts.

   • odps.sql.type.system.odps2=true: The MaxCompute V2.0 data type edition is enabled.

4. Right-click Tables & Views in the destination MaxCompute project and select Refresh meta. Then, you can view the information about the internal table, external table, or view that you created.

Modify an internal table or external table

1. In the left-side navigation pane of IntelliJ IDEA, click Project Explorer. Right-click an internal table or external table that you want to modify in Tables & Views of the destination MaxCompute project and select Open table editor.

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

   You can modify the table name, table comment, lifecycle, column name, and column comment. You can also add columns, indexes, and other table properties. For more information about the parameters, see Create an internal table, an external table, or a view in this topic.

3. In the Modify Table dialog box, click Execute.

4. Right-click Tables & Views in the destination MaxCompute project and select Refresh meta. Then, you can view the internal table or external table that you modified in the list.

View the details of an internal table, an external table, or a view

• View the details of an internal table or external table

  In the Tables & Views folder of the MaxCompute project, double-click the table name or right-click the table name and select show table detail to view details of an internal table or an external table in the following sections:

  • Table information: displays information such as the MaxCompute project to which the table belongs, the owner of the table, the time when the table was created, and the time when the table was last updated.

  • Table schema: displays information such as table fields, field types, non-null properties, and default values.

  • Table data: In the lower part of the page that appears, specify Partitions and Preview rows, and click Data Preview to preview the table data. You need to specify Partitions only for partitioned tables. Preview rows specifies the number of data rows that you want to preview.

• View the details of a view

  In the Tables & Views folder of the destination MaxCompute project, double-click the name of the view or right-click the name of the view and select show table detail to view the query statement that corresponds to the view.

Clear table data or delete partitions

In the Tables & Views folder of the destination MaxCompute project, right-click the name of the table and select Truncate table/Delete partition to clear data of a non-partitioned table or delete a specific partition from a partitioned table.

Delete an internal table, an external table, or a view

1. In the left-side navigation pane of IntelliJ IDEA, click Project Explorer. In the Tables & Views folder of the destination MaxCompute project, 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 message, click OK to delete the internal table, external table, or view from the MaxCompute project.

3. Right-click Tables & Views of the destination MaxCompute project and select Refresh meta. Then, the deleted internal table, external table, or view is no longer displayed in the list.