Command Format:

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

Action: Create a table.

Notes:

• The table name and column name are both case insensitive.
• The table name and column name cannot have special characters. It can only begin with a letter and include a-z, A-Z, digits and underline_. The name length cannot exceed 128 bytes.
• The comment content is the effective string which length can not exceed 1024 bytes.
• For more details of this command, refer to CREATE TABLE.

Example:

CREATE TABLE IF NOT EXISTS sale_detail(
 shop_name     STRING,
 customer_id   STRING,
 total_price   DOUBLE)
PARTITIONED BY (sale_date STRING,region STRING);    ---If there is no same name table existing, create a partition table.

Drop Table

Command Format:

DROP TABLE [IF EXISTS] table_name;

Action:

• Delete a table.
• If the option [if exists] is not specified and the table does not exist, then return exception. If this option is specified, no matter whether the table exists or not, all return success.

Description:

• table_name: the table name to be deleted.

Example:

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

Describe Table

Command Format:

DESC <table_name>;

Action:

• Return the information of specified table, including Owner, Project, CreateTime, LastDDLTime, LastModifiedTime, InternalTable (it indicates the object to be described is table and always shows YES), Size (storage size occupied by table data, unit is Byte), Native Columns (no-partition column information, incuding column name, type, comment), Partition Columns (partition column information, including partition name, type, comment).Parameter:
• table_name: table name or view name.

Example:

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     |                                                     |
+------------------------------------------------------------------------------------+

Notes:

• The example shown above is executed in ODPS client.
• If the table has no partition, the information of Partition Columns will not be displayed.
• To describe a view, the option ‘InternalTable’ will not be displayed but the option ‘VirtualView’ will be displayed and its value is always YES. Similarly, the option ‘Size’ will be replaced by ViewText. For example: select * from src. For the introduction of view, refer to Create View.

Show Tables

Command Format:

SHOW TABLES;

Action: List all tables of current project.

Example:

odps@ project_name>show tables;
ALIYUN$odps_user@aliyun.com:table_name
......

Notes:

• The example shown above is to be executed in ODPS client.
• ALIYUN is system prompt, indicating the ALIYUN user.
• odps_user@aliyun.com is user name, indicating the creator of the table.
• Table_name refers to table name.

Show Partitions

Command Format:

SHOW PARTITIONS <table_name>;

Action: List all partitions of a table.

Parameter:table_name: Specify the table to be queried. If the table does not exist or it is not a partition table, the exception will be thrown.

Example:

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

Notes:

• The example shown above is to be executed in ODPS client.
• partition_col1 and partition_col2 indicate the partition columns.
• col1_value1, col2_value1, col1_value2, and col2_value2 indicate corresponding partition values.