odps-table

Access and manage ODPS table data

TLDR

Create a table with partition and lifecycle

$ create table [table_name] ([col] [type]) partitioned by ([col] [type]) lifecycle [days];

Create a table based on the definition of another table

$ create table [table_name] like [another_table];

Add partition to a table

$ alter table [table_name] add partition ([partition_spec]);

Delete partition from a table

$ alter table [table_name] drop partition ([partition_spec]);

Delete table

$ drop table [table_name];

SYNOPSIS

odps-table [arguments] [options]
Example: odps-table desc my_table
Example: odps-table create new_table (id BIGINT, name STRING) PARTITIONED BY (dt STRING)

list
    Lists all tables available in the current MaxCompute project.

desc <table_name>
    Displays detailed information about a specified table, including its schema, partition keys, lifecycle, comment, and other metadata.

create <table_name> <column_definitions> [<properties>]
    Creates a new MaxCompute table. <column_definitions> specifies column names and data types. Optional <properties> include PARTITIONED BY for partitioning, LIFECYCLE for data retention, and COMMENT for description.

drop <table_name> [IF EXISTS]
    Deletes a specified table from MaxCompute. The IF EXISTS clause prevents an error if the table does not exist.

alter table <table_name> <operation>
    Modifies an existing table. Common <operation>s include:
  add column (col_name type[, ...])
  add partition (pt_col='pt_val'[, ...])
  set lifecycle <days>
  set comment '<comment_string>'
  rename to <new_table_name>

DESCRIPTION

odps-table is a command-line utility used to manage tables within Alibaba Cloud's MaxCompute, a serverless data warehousing platform. It provides a comprehensive set of operations for database administrators and developers to interact with MaxCompute tables directly from a terminal. With odps-table, users can perform essential table management tasks such as listing existing tables, creating new tables with specified schemas and properties, describing table details (including columns, partitions, and metadata), dropping tables, and altering table structures or properties. It's an integral part of the MaxCompute client tools (odpscmd) designed for efficient data governance and schema evolution.

The tool facilitates scripting and automation of data warehousing operations, allowing for programmatic control over table lifecycles and metadata. While odps-table itself doesn't offer many direct options, its functionality is primarily exposed through a series of subcommands, each dedicated to a specific table operation. These subcommands are invoked after odps-table and often accept their own set of options and arguments to refine their behavior.

CAVEATS

odps-table requires a configured Alibaba Cloud MaxCompute client (odpscmd) and appropriate network connectivity to the MaxCompute service endpoints.
Users must have the necessary IAM permissions within MaxCompute to perform table operations (e.g., Create Table, Drop Table, Alter Table).
Syntax for table properties (like PARTITIONED BY, LIFECYCLE) and data types (e.g., BIGINT, STRING, DATETIME) must conform to MaxCompute SQL specifications, which may differ from standard SQL dialects.
Operations like dropping or altering tables can have irreversible consequences on data. Always exercise caution and verify commands before execution.

AUTHENTICATION

Access to MaxCompute via odps-table requires proper authentication. This is typically managed through AccessKey ID and AccessKey Secret credentials, or RAM roles, configured in the odpscmd client's configuration file (usually ~/.odps_config). Ensure your credentials are up-to-date and have the necessary permissions for the desired operations.

PROJECT CONTEXT

All odps-table operations are performed within a specific MaxCompute project. The active project is usually determined by the odpscmd client's configuration. You can switch projects using the set project command within the interactive odpscmd shell, or by configuring the default project in your client settings.

HISTORY

odps-table is an integral part of the MaxCompute client tools provided by Alibaba Cloud. MaxCompute, formerly known as ODPS (Open Data Processing Service), was developed by Alibaba Group to handle massive-scale data processing and analytics. The odps-table command has evolved alongside MaxCompute to provide robust command-line capabilities for managing the metadata and structure of tables, adapting to new features and data types introduced in the platform. Its development has focused on providing a streamlined interface for programmatic interaction with the MaxCompute data warehouse.