Data types

Basic data types

The following table lists the basic data types supported by MaxCompute 2.0. To use a new data type, you must configure the set odps.sql.type.system.odps2=true; or setproject odps.sql.type.system.odps2=true; statement as needed, and pay attention to the impact on the existing tasks. If you do not set the statement, an error xxxx type is not enabled in current mode will occur.

When you use new data types in MaxCompute, pay attention to the following information:

• Applicable scenarios of new data types:
  • MaxCompute SQL
  • The latest version of MapReduce
  • SDK 0.27.2 (public and later versions), and client 0.27.0 and later versions
• New data type flag
  Currently, new data types cannot be directly used by default. To use a new data type, run a flag command. Note that only lowercase letters are supported in the flag command.

  • Session level: To use new data types (such as TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY) in SQL or MapReduce (the latest version), add a set statement before the table statement set odps.sql.type.system.odps2=true; and submit the two statements. The SQL statements submitted through MaxCompute Studio are performed automatically. For more information, see Create and view a table.
  • Project level: New data types at the project level can be used. To use the new data types at the project level, the project owner must run the following command:

    setproject odps.sql.type.system.odps2=true;

    For more information about the setproject command, see Other operations.

• Impact of using the new data type odps.sql.type.system.odps2 :
  • Note 1:

    If the new data type flag is enabled, INT in the SQL statement indicates a 32-bit integer. For example, if you run the cast(a as INT) command, type a is converted to a 32-bit integer. If the new data type flag is not enabled, type a is converted to BIGINT (64-bit).

    For example, the cast(a as INT) command is equivalent to cast(a as BIGINT), create table t(a INT), and create table a (BIGINT). If you do not set odps.sql.type.system.odps2 to true, you can also convert data types in MaxCompute. However, a warning will be prompted, indicating that INT is processed as BIGINT. Therefore, we recommend that you write BIGINT in your scripts.

  • The syntax of an integer constant changes.
    In SELECT 1 + a;, the integer constant is 1.

    • If the new data type flag is not enabled, the integer constant is processed as BIGINT. If the constant length (such as 1000000000000000000000000) exceeds the value range of BIGINT, the integer constant is processed as DOUBLE.
    • If the new data type flag is enabled, the integer constant is expressed as 1 (a 32-digit integer). If the constant value is greater than the maximum INT value but smaller than the maximum BIGINT value, the integer is converted to BIGINT. If the constant value is greater than the maximum BIGINT value, the integer is converted to DOUBLE.
    • Possible compatibility issues: The INT type may lead to inconsistencies in function prototypes during subsequent operations. For example, the actions of peripheral tools and subsequent jobs might be changed by new type tables generated after data is written to disk.
  • The rules for converting implicit data types change.

    If the new data type flag is enabled, some implicit types may not be converted. For example, the precision of conversions from STRING to BIGINT, STRING to DATETIME, DOUBLE to BIGINT, DECIMAL to DOUBLE, and DECIMAL to BIGINT might be reduced. In this case, you can use the CAST function to convert the data type.

    Converting implicit data types affects insert and the calling of functions. You can run the SQL statements that meet the requirements before enabling the new data type flag. However, an error might be displayed after the new data type flag is enabled.

  • The supported operations, built-in functions, and UDFs are different.
    If the new type flag is not enabled, some operations and built-in functions that use new types as parameters and response values are not available.

    1. Some built-in functions can be performed only after the new type flag is enabled, for example, most of the functions that use INT as parameters have BIGINT overloading. Functions such as YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, NANOSECOND, DAYOFMONTH, and WEEKOFYEAR can be implemented by using DATEPART built-in functions.
    2. The UDF resolutions are different. For example, a UDF contains both BIGINT and INT. Before the new type flag is enabled, the resolution is BIGINT. After the new type flag is enabled, the resolution is INT.
  • The BIGINT resolutions are different.

    An integer constant, such as 123, is of the BIGINT type before a new data type is used. The integer constant becomes INT type after a new data type is used.

    Possible compatibility issues: The INT type may lead to inconsistencies in function prototypes during subsequent operations. For example, the actions of peripheral tools and subsequent jobs might be changed by new type tables generated after data is written to disk.

  • Types of supported partition columns are different.
    • Before the new type flag is enabled, only STRING is supported by partition columns.
    • After the new type flag is enabled, data types such as STRING, VARCHAR, CHAR, TINYINT, SMALLINT, INT, and BIGINT are supported by partition columns.
    • If the new type flag is not enabled, partition fields in INSERT operations must be processed as STRING. For example, you can enter insert overwrite table t partition (pt = 045) select .... Note that 045 is not enclosed by quotation marks ("") and is resolved as an integer (45).
  • Action of the LIMIT statement changes.
    Take the SELECT * FROM t1 UNION ALL SELECT * FROM t2 limit 10; statement as an example:

    • Before the new type flag is enabled, the statement is SELECT * FROM t1 UNION ALL SELECT * FROM ( SELECT * FROM t2 limit 10) t2;.
    • After the new type flag is enabled, the statement is SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2 ) t limit 10;.

    Actions of the UNION, INTERSECT, and EXCEPT, INTERSECT, EXCEPT, LIMIT, ORDER BY, DISTRIBUTE BY, SORT BY, and CLUSTER BY statements are also changed if the new type flag is enabled.

  • The resolutions of the IN expression are different.

    Take the IN expression, such as a in (1, 2, 3), as an example:

    • Before the new type flag is enabled, all the values in the parentheses () must be of the same type.
    • After the new type flag is enabled, all the values in the parentheses () can be converted to the same implicit type.
• Note 2: If a constant value is greater than the maximum INT value but smaller than the maximum BIGINT value, the integer is converted to BIGINT. If the constant value is greater than the maximum BIGINT value, the integer is converted to DOUBLE. In MaxCompute versions earlier than 2.0, all INT types in SQL scripts are converted to BIGINT. The following is an example:

  create table a_bigint_table(a int); -- Here INT is processed as BIGINT.
  select cast(id as int) from mytable; -- Here INT is processed as BIGINT.

• Note 3: VARCHAR constants can be expressed through implicit transformation of STRING constants.
• Note 4: STRING constants can be connected to different strings, for example, abc and xyz are resolved as abcxyz, and can be written into different row.
• Note 5:: When you insert a constant to the DECIMAL field, ensure that the constant is in the same format as that in the constant definition, for example, 3.5BD in the following command:

  insert into test_tb(a) values (3.5BD)

• Note 6: The displayed time value does not contain milliseconds. You can use the tunnel command and -dfp to specify the time format, for example, tunnel upload -dfp 'yyyy-MM-dd HH:mm:ss.SSS'. For more information about tunnel commands, see Tunnel commands.

Complex data types

The following table lists the complex data types that are supported by MaxCompute 2.0.