Create, view, and delete a table

Last Updated: May 07, 2018

A user can use MaxCompute services once they are added to a project, and granted the corresponding privileges. Because the operation objects of MaxCompute (input and output) are performed on tables, you must create tables and partitions before processing data.

You can create or delete tables using the following methods:

The following section introduces how to create, view, and delete tables using commands through the DTplus console. For more information about console installation, see Console.

Create table

The command format is shown as follows.

  1. CREATE TABLE [IF NOT EXISTS] table_name
  2. [(col_name data_type [COMMENT col_comment], ...)]
  3. [COMMENT table_comment]
  4. [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  5. [LIFECYCLE days]
  6. [AS select_statement]
  7. CREATE TABLE [IF NOT EXISTS] table_name
  8. LIKE existing_table_name

Command descriptions:

  • The table name and column name are both case insensitive.

  • An exception is thrown if duplicate table name creation is attempted. User must specify the option [if not exists] to override the error. If the option [if not exists] is specified, regardless of if there are tables with the same name, and even if the source table structure and the target table structure are inconsistent, all returns are successful. The Meta information of existing table does not change.

  • Only the data types BIGINT, DOUBLE, BOOLEAN, DATETIME, and STRING are supported.

  • A table name and column name obey the same naming conventions as follows: The name can be up to 128 bytes in length and can contain letters, numbers, and underscores ‘_’.

  • Use ‘Partitioned by’ to specify the partition. Only STRING and BIGINT are supported. The conventions of the partition value are as follows: The value can be up to 128 bytes in length and can contain letters, numbers, and special characters space ‘ ’, colon ‘:’, underscore ‘_’, dollar sign ’$’, hash sign ’#’, dot ‘.’, exclamation point ‘!’ and at symbol ‘@’. Other characters are considered as undefined characters, such as ‘\t’, ‘\n’, and ‘/’. If you are using partition fields in the partition table, a full table scan is not need when adding partitions, or when updating data in the partition and then reading the partition.

  • The comment content is the effective string, and it can be up to 1024 bytes in length.

  • Lifecycle indicates the lifecycle of the table. The unit is ‘day’. The statement ‘CREATE TABLE…LIKE’ does not copy the lifecycle attribute from source table.

  • Currently, the partition hierarchy cannot exceed 6 levels. In a project, the maximum partition number of a table can be configured. The maximum number of tables is 60,000.

Notes:

The following example shows how to create a table:

  1. create table test1 (key string); -- create a no-partition table.
  2. create table test2 (key bigint) partitioned by (pt string, ds string); --Create a partition table.
  3. create table test3 (key boolean) partitioned by (pt string, ds string) lifecycle 100; -- Create a table with lifecycle.
  4. create table test4 like test3; -- Except for the lifecycle property, other properties of test3 (field type, partition type) are completely consistent with test4.
  5. create table test5 as select * from test2;
  6. -- This operation will create test5, but the partition and lifecycle information will not be copied to the object table.
  7. -- This operation will copy the data of test2 to the table test5.

In the preceding example, an instance is used to create a table. Next, create a table named user that includes the following information:

  • user_id: BIGINT, user identifier, to identify a user.

  • gender: BIGINT type, sex (0, unknown; 1, male; 2, female).

  • age: BIGINT, the age of a user.

It must be partitioned by region and dt and the lifecycle is 365 days.

An example of table creation is as follows:

  1. CREATE TABLE user (
  2. user_id BIGINT, gender BIGINT COMMENT '0 unknow,1 male, 2 Female', age BIGINT)
  3. PARTITIONED BY (region string, dt string) LIFECYCLE 365;

Add partition

After creating a partition table, in order to import data into different partitions, a partition must be created. The statement format is as follows:

  1. alter table table_name add [if not exists] partition partition_spec
  2. partition_spec:
  3. : (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

In the preceding example, partitions must be added for the table user (region is ‘hangzhou’ and dt is ‘20150923’). The statement is as follows:

  1. Alter table user add if not exists partition(region='hangzhou',dt='20150923');

View table

View table information by using the following command:

  1. desc <table_name>;

For example, get information from test3:

  1. desc test3;

The results are as follows:

  1. odps@ $odps_project>desc test3;
  2. +------------------------------------------------------------------------------------+
  3. | Owner: ALIYUN$maojing.mj@alibaba-inc.com | Project: $odps_project
  4. | TableComment: |
  5. +------------------------------------------------------------------------------------+
  6. | CreateTime: 2015-09-18 12:26:57 |
  7. | LastDDLTime: 2015-09-18 12:26:57 |
  8. | LastModifiedTime: 2015-09-18 12:26:57 |
  9. | Lifecycle: 100 |
  10. +------------------------------------------------------------------------------------+
  11. | InternalTable: YES | Size: 0 |
  12. +------------------------------------------------------------------------------------+
  13. | Native Columns: |
  14. +------------------------------------------------------------------------------------+
  15. | Field | Type | Label | Comment |
  16. +------------------------------------------------------------------------------------+
  17. | key | boolean | | |
  18. +------------------------------------------------------------------------------------+
  19. | Partition Columns: |
  20. +------------------------------------------------------------------------------------+
  21. | pt | string | |
  22. | ds | string | |
  23. +------------------------------------------------------------------------------------+

Get information from test4:

  1. desc test4;

The results are as follows:

  1. odps@ $odps_project>desc test4;
  2. +------------------------------------------------------------------------------------+
  3. | Owner: ALIYUN$maojing.mj@alibaba-inc.com | Project: $odps_project
  4. | TableComment: |
  5. +------------------------------------------------------------------------------------+
  6. | CreateTime: 2015-09-18 12:27:09 |
  7. | LastDDLTime: 2015-09-18 12:27:09 |
  8. | LastModifiedTime: 2015-09-18 12:27:09 |
  9. +------------------------------------------------------------------------------------+
  10. | InternalTable: YES | Size: 0 |
  11. +------------------------------------------------------------------------------------+
  12. | Native Columns: |
  13. +------------------------------------------------------------------------------------+
  14. | Field | Type | Label | Comment |
  15. +------------------------------------------------------------------------------------+
  16. | key | boolean | | |
  17. +------------------------------------------------------------------------------------+
  18. | Partition Columns: |
  19. +------------------------------------------------------------------------------------+
  20. | pt | string | |
  21. | ds | string | |
  22. +------------------------------------------------------------------------------------+

Except for the lifecycle property, other properties of test3 (field type, partition type) are completely consistent with test4. For more information about describing table, see Describe Table.

To view the information of test5, the two fields ‘pt’, ‘ds’ only exist as two common columns, rather than the table partitions.

Drop partition

An example of how to drop a partition is as follows:

  1. alter table table_name drop [if exists] partition_spec;
  2. partition_spec:
  3. : (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

For example, to delete the partitions of region ‘hangzhou’ and dt ‘20150923’, the statement is as follows:

  1. Alter table user drop if exists partition(region='hangzhou',dt='20150923');

Drop table

  1. DROP TABLE [IF EXISTS] table_name;

For example, to delete the table ‘test2’:

  1. drop table test2;

For more information, see Drop Table.

Thank you! We've received your feedback.