Create/View/Delete a table

The command format is shown as follows.

CREATE TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[LIFECYCLE days] 
[As select_statement]
CREATE TABLE [IF NOT EXISTS] table_name 
LIKE existing_table_name

Command descriptions:

• The table name and column name are both case insensitive.
• If you do not specify IF NOT EXISTS when creating a table and a table with the same name exists, an error is returned. If the option is specified then all returns are successful, regardless of whether there are tables with the same name, and regardless of whether the source table structure and the target table structure are inconsistent. The Meta information of the 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 ‘_’.
• Partitioned Partitioned by: Use PARTITIONED BY to specify the partition. Only String is supported. The value can be up to 128 bytes in length and can contain letters, numbers, and the 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 needed when adding partitions, or when updating data in the partition and then reading the partition.
• A comment must be a valid string within 1024 bytes.
• Lifecycle indicates the lifecycle of the table. The unit is days. 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.
  Note

  • For more information about creating a table, seeTable Operations.
  • For more information about the partition operation, see Add/Remove Partition.
  • For more information about the lifecycle operation, see Modify Lifecycle for a Table.

The following example shows how to create a table:

create table test1 (key string); -- create a no-partition table. table name is test 1,  field name is key, data type is string. 
create table test2 (key bigint) partitioned by (pt string, ds string);  --Create a partition table. 
create table test3 (key boolean) partitioned by (pt string, ds string) lifecycle 100;  -- Create a table with lifecycle. 
create table test4 like test3;  -- Except for the lifecycle property, other properties of test3 (field type, partition type) are completely consistent with test4. 
create table test5 as select * from test2;  -- This operation will create test5, but the partition and lifecycle information will not be copied to the object table. 
-- This operation will copy the data of test2 to the table test5.If test2 has data, the test2 in this example is an empty table. Subsequent chapters will introduce data import.

Create a table named user that includes the following information:

• user_id: Bigint type, user identifier, to identify a user.
• gender: Bigint type, sex (0, unknown; 1, male; 2, female).
• age: Bigint, the age of a user.

An example of table creation is as follows:

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

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

alter table table_name add [if not exists] partition partition_spec partition_spec: 
: (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:

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

The results are as follows:

odps@ $odps_project>desc test3;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$maojing.mj@alibaba-inc.com | Project: $odps_project
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2015-09-18 12:26:57 |
| LastDDLTime: 2015-09-18 12:26:57 |
| LastModifiedTime: 2015-09-18 12:26:57 |
| Lifecycle: 100 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| key | boolean | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| pt | string | |
| ds | string | |
+------------------------------------------------------------------------------------+

Get information from test4: desc test4;

odps@ $odps_project>desc test4;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$maojing.mj@alibaba-inc.com | Project: $odps_project
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2015-09-18 12:27:09 |
| LastDDLTime: 2015-09-18 12:27:09 |
| LastModifiedTime: 2015-09-18 12:27:09 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| key | boolean | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| pt | string | |
| ds | string | |
+------------------------------------------------------------------------------------+

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

alter table table_name drop [if exists] partition_spec; partition_spec: 
: (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:

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

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

DROP TABLE [IF EXISTS] table_name;

For example, to delete the table test2:

drop table test2;