Table Operations

The command format is 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

Create a table.

Examples:

CREATE TABLE IF NOT EXISTS sale_detail(
 shop_name STRING,
 customer_id STRING,
 total_price DOUBLE)
PARTITIONED BY (sale_date STRING,region STRING); --Create a partition table sale_detail.

Statement format:

DROP TABLE [IF EXISTS] table_name; -- Table name to be deleted.

Action:

• Delete a table.
• If the option [IF EXISTS] is specified, regardless of whether the table exists or not, the return is successful. If the option [IF EXISTS] is not specified, and the table does not exist, an exception is returned.

Examples:

DROP TABLE sale_detail; -- If the table exists, success returns.
DROP TABLE IF EXISTS sale_detail; -- No matter whether the table sale_detail exists or not, success returns.

The command format is as follows:

DESC <table_name>; -- Table name or view name.
DESC extended <table_name>; -- View the extended table information.

Return information of a specified table, include:

• Owner: The owner of the table.
• Project: The project to which a table belongs.
• CreateTime: The creation time of the table.
• LastDDLTime: The last DDL operation.
• LastModifiedTime: The last time of table modification.
• InternalTable: Indicates the object to be described is table. The value is ‘YES’ by default.
• Size: Storage size occupied by table data, usually the compression ratio is 5. The unit is Byte.
• Native Columns: Non-partition column information, including column name, type, comment.
• Partition Columns: Partition column information, including partition name, type, and comment.
• Extended Info: The information of extended table, such as StorageHandler and Location.

Examples:

odps@ project_name>DESC sale_detail; -- Describe a partition table.
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$odpsuser@aliyun.com | Project: test_project |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2014-01-01 17:32:13 |
| LastDDLTime: 2014-01-01 17:57:38 |
| LastModifiedTime: 1970-01-01 08:00:00 |
+------------------------------------------------------------------------------------+
| Internaltable: Yes | size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Comment |
+------------------------------------------------------------------------------------+
| shop_name | string | |
| customer_id | string | 
| total_price | double | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| sale_date | string | |
| region | string | |
+------------------------------------------------------------------------------------+

The command format is as follows:

desc table_name partition(pt_spec

Examples:

odps@ project_name>desc meta.m_security_users partition (ds='20151010');
+------------------------------------------------------------------------------------+
| PartitionSize: 2109112 |
+------------------------------------------------------------------------------------+
| CreateTime: 2015-10-10 08:48:48 |
| LastDDLTime: 2015-10-10 08:48:48 |
| LastModifiedTime: 2015-10-11 01:33:35 |
+------------------------------------------------------------------------------------+
OK

Command Format:

SHOW TABLES;
SHOW TABLES like 'chart';

Action:

• SHOW TABLES：List all tables of current project.
• SHOW TABLES like 'chart': Lists the tables on which the following table names of the current project match the 'chart'. Regular expressions are supported.

Examples:

odps@ project_name>show tables;
odps@ project_name>show tables like 'ods_brand*';
ALIYUN$odps_user@aliyun.com:table_name
......

The command format is as follows:

SHOW PARTITIONS ;  -- table_name: Specify the table to be queried. If the table does not exist or it is not a partition table, an exception is thrown.

Examples:

odps@ project_name>SHOW PARTITIONS table_name;
partition_col1=col1_value1/partition_col2=col2_value1
partition_col1=col1_value2/partition_col2=col2_value2
…