Create table and delete table

Last Updated: Aug 07, 2017

After a user is added to the project and granted tabulation permission, among other permissions, he or she can then perform operations on MaxCompute through the development kit. Since the operation objects in the underlying MaxCompute (input and output) are all tables, we should first create tables and partitions before processing the data. For detailed syntax on creating MaxCompute tables, see MaxCompute Introduction Documents.

Create a table

You can use the New Table function in the New Script File and Data Management modules in the Data IDE Kit to create a MaxCompute table.

Taking, for example, the creation of a new table of tmall_user_brand (Tmall brand access log), the steps are as follows:

Tabulation statements:

  1. DROP TABLE IF EXISTS tmall_user_brand;
  2. CREATE TABLE tmall_user_brand (
  3. user_id STRING COMMENT 'User ID',
  4. brand_id STRING COMMENT 'Brand ID',
  5. type STRING COMMENT 'Type of user actions of the brand: click-0, purchase-1, add to favorites-2, add to shopping cart-3',
  6. visit_datetime STRING COMMENT 'Time of action'
  7. )
  8. COMMENT 'Tmall brand access log'
  9. PARTITIONED BY (
  10. dt STRING COMMENT 'Time range'
  11. )
  12. LIFECYCLE 10;

Method 1: Quick tabulation

Step 1: Go to Alibaba Cloud Dataplus platform > Data IDE Kit > Console as a developer, click the Enter Work Zone in the action bar of the corresponding project.

1

Step 2: Click New > New Table to pop up the New Table box.

Step 3: Fill in the tabulation statement, and click OK to complete the tabulation.

1

Method 2: Tabulation through script files

Step 1: Go to Alibaba Cloud Dataplus platform > Data IDE Kit > Console as a developer, click the Enter Work Zone in the action bar of the corresponding project.

Step 2: Create a script file. Click Data Development in the top menu bar, click “New” to create a new script, or you can click the “New Script” task box directly.

Step 3: Edit the tabulation statement.

1

Step 4: Click the Run_Button button to run the tabulation DDL statement.

Step 5: If the statement is run successfully, it will indicate that the table has been created successfully.

1

Method 3: Tabulation through Data Management module

Step 1: Enter the Data IDE Kit.

Step 2: Click Data Management in the top menu bar and navigate to Manage Data Tables.

Step 3: Click New Table.

1

Step 4: Fill in the basic information configuration items on the New Table page.

1

Specific descriptions on the configuration items on the Basic Info page are as follows:

  • Project Name: The list shows the MaxCompute projects that the user is currently in.
  • Table Name: A table name may contain letters, numbers, and underscores.
  • Alias: The Chinese name of the table.
  • Category: The category of the current table. Up to four category levels are supported. For details about the configuration of existing category navigation, see Category Navigation Configuration.
  • Description: A brief description of the current table.
  • Lifecycle: The lifecycle function of MaxCompute. Data in the table (or partition) that has not been updated within the period of time specified by “Lifecycle” (in the unit of days) will be cleared. Five options are available, including “1 day”, “7 days”, “32 days”, “Permanent”, and “User-defined”.

Step 5: Click Next.

Step 6: Fill in the configuration items of the field and partition information on the New Table page.

1

The configuration items on the field and partition pages are described as follows:

  • English Name of the Field: The English name of a field, which may contain letters, numbers, and underscores.
  • Field Type: The MaxCompute data type (string, bigint, double, datetime, or boolean).
  • Description: Detailed description of a field.
  • Action: The options include “Move Up”, “Move Down”, and “Delete”.
  • Whether to Set Partitions: If you select to set partitions, you need to configure the partitioning key information. String and bigint data types are supported.

[Description] The sensitivity level tag of the field, with a value range of 0-9, indicating the sensitivity level from low to high. After a new user enters the project, the default security permission tag is 0, so the user can only view fields with a sensitivity level of 0 in the table. The user needs to get the authorization for viewing fields with a higher sensitivity level (that is, project members can only view data with a sensitivity level not higher than the member’s security permission tag level).

Step 7: Click Submit.

After the newly created table has been submitted successfully, the system will automatically jump back to the Manage Data Tables page. Click My Managed Tables and you will be able to see the newly created table.

Get table information

After the table is created successfully, we can get the table information by writing the following command in the script file and then clicking the “Run” button:

Method 1: Query through the script file

  1. desc <tablename>

1

Method 2: Query through the table details

Click the table name to enter the table details page:

1

Delete a table

The operations for deleting a table are the same as for creating a table. You can delete a table by compiling DDL statements in the script file, or you can delete the table in the Data Management module.

Method 1: Query through the script file

Execute the table drop command through the SQL statement.

  1. DROP TABLE [IF EXISTS] table_name;

Method 2: Query through Manage Data Tables

[Description] You can delete a table on the Data Management > Manage Data Tables > My Managed Tables page.

1

Thank you! We've received your feedback.