Primitive Data Types
Table 1 lists the primitive data types supported by DLI.
Data Type |
Description |
Storage Space |
Value Range |
Support by OBS Table |
Support by DLI Table |
---|---|---|---|---|---|
INT |
Signed integer |
4 bytes |
–2147483648 to 2147483647 |
Yes |
Yes |
STRING |
String |
- |
- |
Yes |
Yes |
FLOAT |
Single-precision floating point |
4 bytes |
- |
Yes |
Yes |
DOUBLE |
Double-precision floating-point |
8 bytes |
- |
Yes |
Yes |
DECIMAL(precision,scale) |
Decimal number. Data type of valid fixed places and decimal places, for example, 3.5.
|
- |
1<=precision<=38 0<=scale<=38 If precision and scale are not specified, DECIMAL (38,38) is used by default. |
Yes |
Yes |
BOOLEAN |
Boolean |
1 byte |
TRUE/FALSE |
Yes |
Yes |
SMALLINT/SHORT |
Signed integer |
2 bytes |
-32768~32767 |
Yes |
Yes |
TINYINT |
Signed integer |
1 byte |
-128~127 |
Yes |
No |
BIGINT/LONG |
Signed integer |
8 bytes |
–9223372036854775808 to 9223372036854775807 |
Yes |
Yes |
TIMESTAMP |
Timestamp in raw data format, indicating the date and time Example: 1621434131222 |
- |
- |
Yes |
Yes |
CHAR |
Fixed-length string |
- |
- |
Yes |
Yes |
VARCHAR |
Variable-length string |
- |
- |
Yes |
Yes |
DATE |
Date type in the format of yyyy-mm-dd, for example, 2014-05-29 |
- |
DATE does not contain time information. Its value ranges from 0000-01-01 to 9999-12-31. |
Yes |
Yes |
- VARCHAR and CHAR data is stored in STRING type on DLI. Therefore, the string that exceeds the specified length will not be truncated.
- FLOAT data is stored as DOUBLE data on DLI.
INT
Signed integer with a storage space of 4 bytes. Its value ranges from –2147483648 to 2147483647. If this field is NULL, value 0 is used by default.
STRING
String.
FLOAT
Single-precision floating point with a storage space of 4 bytes. If this field is NULL, value 0 is used by default.
Due to the limitation of storage methods of floating point data, do not use the formula a==b to check whether two floating point values are the same. You are advised to use the formula: absolute value of (a-b) <= EPSILON. EPSILON indicates the allowed error range which is usually 1.19209290E-07F. If the formula is satisfied, the compared two floating point values are considered the same.
DOUBLE
Double-precision floating point with a storage space of 8 bytes. If this field is NULL, value 0 is used by default.
Due to the limitation of storage methods of floating point data, do not use the formula a==b to check whether two floating point values are the same. You are advised to use the formula: absolute value of (a-b) <= EPSILON. EPSILON indicates the allowed error range which is usually 2.2204460492503131E-16. If the formula is satisfied, the compared two floating point values are considered the same.
DECIMAL
Decimal(p,s) indicates that the total digit length is p, including p – s integer digits and s fractional digits. p indicates the maximum number of decimal digits that can be stored, including the digits to both the left and right of the decimal point. The value of p ranges from 1 to 38. s indicates the maximum number of decimal digits that can be stored to the right of the decimal point. The fractional digits must be values ranging from 0 to p. The fractional digits can be specified only after significant digits are specified. Therefore, the following inequality is concluded: 0 ≤ s ≤ p. For example, decimal (10,6) indicates that the value contains 10 digits, in which there are four integer digits and six fractional digits.
BOOLEAN
Boolean, which can be TRUE or FALSE.
SMALLINT/SHORT
Signed integer with a storage space of 2 bytes. Its value ranges from –32768 to 32767. If this field is NULL, value 0 is used by default.
TINYINT
Signed integer with a storage space of 1 byte. Its value ranges from –128 to 127. If this field is NULL, value 0 is used by default.
BIGINT/LONG
Signed integer with a storage space of 8 bytes. Its value ranges from –9223372036854775808 to 9223372036854775807. It does not support scientific notation. If this field is NULL, value 0 is used by default.
TIMESTAMP
Legacy UNIX TIMESTAMP is supported, providing the precision up to the microsecond level. TIMESTAMP is defined by the difference between the specified time and UNIX epoch (UNIX epoch time: 1970-01-01 00:00:00) in seconds. The data type STRING can be implicitly converted to TIMESTAMP, but it must be in the yyyy-MM-dd HH:mm:SS[.ffffff] format. The precision after the decimal point is optional.)
CHAR
String with a fixed length. In DLI, the STRING type is used.
VARCHAR
VARCHAR is declared with a length that indicates the maximum number of characters in a string. During conversion from STRING to VARCHAR, if the number of characters in STRING exceeds the specified length, the excess characters of STRING are automatically trimmed. Similar to STRING, the spaces at the end of VARCHAR are meaningful and affect the comparison result. In DLI, the STRING type is used.
DATE
DATE supports only explicit conversion (cast) with DATE, TIMESTAMP, and STRING. For details, see Table 2.
Explicit Conversion |
Conversion Result |
---|---|
cast(date as date) |
Same as value of DATE. |
cast(timestamp as date) |
The date (yyyy-mm-dd) is obtained from TIMESTAMP based on the local time zone and returned as the value of DATE. |
cast(string as date) |
If the STRING is in the yyyy-MM-dd format, the corresponding date (yyyy-mm-dd) is returned as the value of DATE. If the STRING is not in the yyyy-MM-dd format, NULL is returned. |
cast(date as timestamp) |
Timestamp that maps to the zero hour of the date (yyyy-mm-dd) specified by DATE is generated based on the local time zone and returned as the value of DATE. |
cast(date as string) |
A STRING in the yyyy-MM-dd format is generated based on the date (yyyy-mm-dd) specified by DATE and returned as the value of DATE. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot