Basic data types
- Session Level: To use the new data types (Tinyint, Smallint, Int, Float, Varchar, TIMESTAMP BINARY), add a set statement before the table statement
And submit the execution together with the table statement.
- Project level: that is to support new types of project level open. The Owner of project can set up project as required.
For a detailed description of setproject, please see:Other operations.
- When it comes to INT types, 32 bits are added to the set statement and converted to BIGINT, 64 bits, if not added.
- SDK 0.27.2-public version and above, Client 0.27.0 version and above support new data type.
|TINYINT||Yes||1Y，-127Y||8-bit signed integer, range -128 to 127|
|SMALLINT||Yes||32767S, -100S||16-bit signed integer, range -32768 to 32767|
|INT||Yes||1000,-15645787 (Note2)||32 bit signed shaping, the range is -231 to 231 - 1.|
|BIGINT||No||100000000000L, -1L||64 bit signed shaping, the range is -263 + 1 to 263 - 1.|
|FLOAT||Yes||None||32-bit binary floating point|
|DOUBLE||No||3.1415926 1E+7||64-bit binary floating point|
|DECIMAL||No||3.5BD， 99999999999.9999999BD(Note1)||Decimal precision number type, shaping part range -1036 + 1 to 1036 - 1, decimal part accurate to 10-18|
|VARCHAR||Yes||None (Note3)||Variable-length character type, n is the length, and the range is 1 to 65535.|
|STRING||No||“abc”,’bcd’,”alibaba” ‘inc’ (Note4)||A single string length can be up to 8M|
|BINARY||Yes||None||Binary data type, a single string length can be up to 8M|
|DATETIME||No||DATETIME ‘2017-11-11 00:00:00’||Date-time type, range from December 31, 999 to January 1-9, 0000, exact to milliseconds (note 5)|
|TIMESTAMP||Yes||TIMESTAMP ‘2017-11-11 00:00:00.123456789’||It is independent of the time zone and ranges from January 1st 0000 to December 31, 9999 23.59:59.999999999, and is accurate to nanosecond-level.|
|BOOLEAN||No||TRUE，FALSE||True/False, Boolean type|
- NOTE 1:When insert is constant to decimal field, pay attention to the writing of constants. For example,3.5BD in the definition of constants.
insert into test_tb(a) values (3.5BD);
The a field is decimal type.
- NOTE 2: For INT constant, if the range of INT is exceeded, INT is converted into BIGINT; if the range of BIGINT is exceeded, it is converted into DOUBLE.
In MaxCompute versions earlier than 2.0, all INT types in SQL script are converted to BIGINT , for example:
create table a_bigint_table(a int); -- the int here is actually treated as a bigint select cast(id as int) from mytable; -- the int here is actually treated as a bigint
To be compatible with earlier MaxCompute versions, MaxCompute 2.0 retains this conversion without setting odps.sql.type.system.odps2 as True. However, a warning is triggered when INT is treated as BIGINT. In this case, we recommend that you change an Int to a Bigint to avoid confusion.
- NOTE 3: VARCHAR constants can be expressed by STRING constants of implicit transformation.
- NOTE 4: STRING constants support connections, for example,
xyzis parsed as
abcxyz, and different parts can be written on different lines.
- NOTE 5: The time value displayed by the current query does not contain milliseconds. The tunnel command specifies the time format through
-dfp, and can be specified in milliseconds, such as
tunnel upload -dfp 'yyyy-MM-dd HH:mm:ss.SSS', for more information about tunnel commands, refer toTunnel commands.
set odps.sql.type.system.odps2=true;must be added before the SQL command. The set command and SQL command are then submitted simultaneously.
|Type||Definition method||Construction method|
|ARRAY||array< int >;array< struct< a:int, b:string >>||array(1, 2, 3); array(array(1, 2); array(3, 4))|
|MAP||map< string, string >;map< smallint, array< string>>||map(“k1”, “v1”, “k2”, “v2”); map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y))|
|STRUCT||struct< x:int, y:int>;struct< field1:bigint, field2:array< int>, field3:map< int, int>>||named_struct(‘x’, 1, ‘y’, 2); named_struct(‘field1’, 100L, ‘field2’, array(1, 2), ‘field3’, map(1, 100, 2, 200)|