How to manage the data type editions for a project - MaxCompute

MaxCompute supports three data type editions: MaxCompute V1.0, MaxCompute V2.0, and Hive-compatible. Each edition is controlled by a set of project-level parameters. This topic describes each edition, explains when to use it, and shows how to view and change the active edition.

Choose a data type edition

You can select the data type version directly when you create a workspace in the DataWorks console.

The active data type edition affects:

The data types available in tables
The behavior of DML statements and built-in functions
The compatibility of development components connected to MaxCompute

Before you set an edition, read the differences between the MaxCompute V2.0 data type edition and other data type editions to understand the impact.

The following table summarizes when to use each edition:

Edition	Use when
MaxCompute V1.0	Your project is an early MaxCompute project whose dependent components do not support the MaxCompute V2.0 data type edition.
MaxCompute V2.0	Your project contains no data generated before April 2020, and all dependent components support the MaxCompute V2.0 data type edition.
Hive-compatible	Your project is migrated from Hadoop, and dependent components support the MaxCompute V2.0 data type edition.

Data type edition reference

Each edition maps to a fixed combination of three project parameters:

Parameter	Description	Valid values
`odps.sql.type.system.odps2`	Enable or disable the MaxCompute V2.0 data type edition.	`true` or `false`
`odps.sql.decimal.odps2`	Enable or disable the DECIMAL type in MaxCompute V2.0.	`true` or `false`
`odps.sql.hive.compatible`	Enable or disable the Hive-compatible data type edition. In the Hive-compatible edition, some data types and SQL statements are compatible with Hive.	`true` or `false`

MaxCompute V1.0 data type edition

setproject odps.sql.type.system.odps2=false; -- Disable the MaxCompute V2.0 data type edition.
setproject odps.sql.decimal.odps2=false; -- Disable the DECIMAL type in MaxCompute V2.0.
setproject odps.sql.hive.compatible=false; -- Disable the Hive-compatible data type edition.

MaxCompute V2.0 data type edition

setproject odps.sql.type.system.odps2=true; -- Enable the MaxCompute V2.0 data type edition.
setproject odps.sql.decimal.odps2=true; -- Enable the DECIMAL data type in MaxCompute V2.0.
setproject odps.sql.hive.compatible=false; -- Disable the Hive-compatible data type edition.

Hive-compatible data type edition

setproject odps.sql.type.system.odps2=true; -- Enable the MaxCompute V2.0 data type edition.
setproject odps.sql.decimal.odps2=true; -- Enable the DECIMAL data type in MaxCompute V2.0.
setproject odps.sql.hive.compatible=true; -- Enable the Hive-compatible data type edition.

For information about which Alibaba Cloud services and components are compatible with each edition, see Compatibility between components and data types.

View the data type edition of a project

Run the following command on the MaxCompute client:

setproject;

Check the values of odps.sql.type.system.odps2, odps.sql.decimal.odps2, and odps.sql.hive.compatible in the output to determine the active edition.

Change the data type edition of a project

To change the data type edition, you must be the project owner or hold the Super_Administrator role.

Run the following commands on the MaxCompute client, in the DataWorks console, or in MaxCompute Studio:

-- View the data type edition of a project.
setproject;
-- Enable or disable the MaxCompute V2.0 data type edition.
setproject odps.sql.type.system.odps2=true/false;
-- Enable or disable the DECIMAL type in MaxCompute V2.0.
setproject odps.sql.decimal.odps2=true/false;
-- Enable or disable the Hive-compatible data type edition.
setproject odps.sql.hive.compatible=true/false;

Important

On an ODPS SQL node of DataStudio in the DataWorks console, setproject commands cannot be run alone. Run them together with a SELECT or INSERT SQL statement.

Handle compatibility issues when switching editions

Problem: The project uses V2.0, but some components require V1.0.

Options:

Change the project-level edition to MaxCompute V1.0.
Keep the project on V2.0 and set a session-level override for the affected components:
```
set odps.sql.type.system.odps2=false;
```
Note
Commit this command together with the other commands in the same session. The command must be in lowercase.

Problem: Some SQL statements require V1.0, while others rely on V2.0 features such as `current_timestamp`.

Options:

Split the V1.0-dependent statements into separate sessions and set the session-level data type edition to V1.0 for those sessions.
Rewrite the SQL statements to remove the version dependency.

Problem: The project used V2.0 initially but must revert to V1.0.

Before switching, convert affected columns in tables that use V2.0 data types:

TINYINT, SMALLINT, or INT → BIGINT
CHAR or VARCHAR → STRING

Alternatively, create a new table using V1.0 data types and use the CAST function to migrate data from the V2.0 table into it.