Data type | Constant example | Description |
TINYINT | 1Y and -127Y | The 8-bit signed integer type. Valid values: -128 to 127. |
SMALLINT | 32767S and -100S | The 16-bit signed integer type. Valid values: -32768 to 32767. |
INT | 1000 and -15645787 | The 32-bit signed integer type. Valid values: -231 to 231 -1. |
BIGINT | 100000000000 L and -1L | The 64-bit signed integer type. Valid values: -263 + 1 to 263 -1. |
BINARY | | A binary number. The maximum length is 8 MB. Note num in X'num [...]' is a hexadecimal value, which can be 0 to 9 or A to F . For example, X'616263' represents abc because a is 0x61 , b is 0x62 , and c is 0x63 in ASCII. X'616263' is semantically equivalent to unhex('616263') .
If the length of the string is not an even number, the system adds 0 in front of the string. For example, X'616' is equivalent to X'0616' . You must enclose the string in single quotation marks (') instead of double quotation marks ("). For example, X"616263" is not interpreted as a constant of the BINARY type.
|
FLOAT | 3.14F and cast(3.14159261E+7 as float) | The 32-bit binary floating point type. Note A loss of precision occurs when data of the FLOAT type is calculated due to the computer storage and internal computing logic. In scenarios where high precision is required, you can convert data of the FLOAT type into data of the DECIMAL type. |
DOUBLE | 3.14D and 3.14159261E+7 | The 64-bit binary floating point type. Note A loss of precision occurs when data of the DOUBLE type is calculated due to the computer storage and internal computing logic. In scenarios where high precision is required, you can convert data of the DOUBLE type into data of the DECIMAL type. |
DECIMAL(precision,scale) | 3.5BD and 99999999999.9999999BD | The precise numeric type based on the decimal system. The default expression of this data type is decimal(38,18) . You can specify the precision and scale values. precision: indicates the maximum number of digits in a value. Valid values: 1 to 38 . scale: indicates the number of digits to the right of the decimal point in a value. Valid values: 0 to 18 . If a higher scale is needed, you can run the set odps.sql.decimal2.extended.scale.enable=true; command to extend the range of scale values. After you set this flag parameter to true, the range of scale values becomes 0 to 38.
Note DECIMAL of the old and new editions cannot exist in the same table. The Hive-compatible data type edition is enabled by running the setproject odps.sql.hive.compatible=true; command. In this case, if the number of digits to the right of the decimal point in a value of the Decimal(precision,scale) type exceeds the value of the scale parameter during Tunnel-based data uploads or SQL operations, the value is rounded. If the integer part exceeds the limit, an error is reported. If the odps.sql.decimal.tostring.trimzero parameter is set to true , trailing zeros after the decimal point are removed. If this parameter is set to false , trailing zeros after the decimal point are retained. The default value is true . This parameter takes effect only when data is read from a table. This parameter does not take effect on static values.
|
VARCHAR(n) | No default value | The variable-length character type, in which n specifies the length. Valid values: 1 to 65535. |
CHAR(n) | No default value | The fixed-length character type, in which n specifies the length. The maximum value is 255. If the length does not reach the specified value, extra spaces are automatically filled but are not involved in the comparison. |
STRING | "abc", 'bcd', "alibaba", and 'inc' | The string type. The maximum length is 8 MB. |
DATE | DATE'2017-11-11' | The date type in the yyyy-mm-dd format. Valid values: 0001-01-01 to 9999-12-31. |
DATETIME | DATETIME'2017-11-11 00:00:00' | The DATETIME type. Valid values: 0001-01-01 00:00:00.000 to 9999-12-31 23:59:59.999, accurate to the millisecond. |
TIMESTAMP | TIMESTAMP'2017-11-11 00:00:00.123456789' | The TIMESTAMP type. Valid values: 0001-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999, accurate to the nanosecond. Note The timestamp is independent of time zones. In any time zone, the timestamp stores a date offset value from Epoch (UTC 1970-01-01 00:00:00). You can use built-in functions to perform time zone-related computing on the data of the TIMESTAMP type. For example, you can use cast(<a timestamp> as string) to convert data of the TIMESTAMP type into the STRING type based on the current time zone. |
TIMESTAMP_NTZ | TIMESTAMP_NTZ '2017-11-11 00:00:00.123456789' | TIMESTAMP data type, which is independent from time zones. Valid values: 0000-01-01 00:00:00.000000000 to 9999-12-31 23:59:59.999999999. |
BOOLEAN | True and False | The BOOLEAN type. Valid values: True and False. |
INTERVAL | | The INTERVAL type represents a time period and is used to express the interval between two dates or times. It includes two types: INTERVAL_YEAR_MONTH and INTERVAL_DAY_TIME. |