Supported Data Types

In the big data field, the mainstream file formats are ORC and Parquet. You can use Hive to export data to an ORC or Parquet file and use DWS to query and analyze the data in the ORC or Parquet file through a read-only foreign table. Therefore, you need to map the data types supported by the ORC or Parquet file format with the data types supported by DWS. For details, see Table 1. Similarly, DWS exports data through a write-only foreign table, and stores the data in the ORC or Parquet format. Using Hive to read the ORC or Parquet file also requires matched data types. Table 2 shows the matching relationship.

**Table 1** Mapping between ORC or Parquet read-only foreign tables and Hive data types
Type	DWS Foreign Table Type	Hive Table Type
1-byte integer	TINYINT (not recommended)	TINYINT
1-byte integer	SMALLINT (recommended)	TINYINT
2-byte integer	SMALLINT	SMALLINT
4-byte integer	INTEGER	INT
8-byte integer	BIGINT	BIGINT
Single-precision floating point number	FLOAT4 (REAL)	FLOAT
Double-precision floating point number	FLOAT8(DOUBLE PRECISION)	DOUBLE
Scientific data type	DECIMAL[p (,s)] (The maximum precision can reach up to 38.)	DECIMAL (The maximum precision can reach up to 38.) (HIVE 0.11)
Date type	DATE	DATE
Time type	TIMESTAMP	TIMESTAMP
Boolean type	BOOLEAN	BOOLEAN
CHAR type	CHAR(n)	CHAR (n)
VARCHAR type	VARCHAR(n)	VARCHAR (n)
String (large text object)	TEXT(CLOB)	STRING
Binary type	BYTEA	BINARY

Binary types are available only for clusters of version 9.1.0.100 or later.

**Table 2** Mapping between ORC or Parquet write-only foreign tables and Hive data types
Type	DWS Internal Table Type (Data Source Table)	DWS Write-Only Foreign Table Type	Hive Table Type
1-byte integer	TINYINT	TINYINT (not recommended)	SMALLINT
1-byte integer	TINYINT	SMALLINT (recommended)	SMALLINT
2-byte integer	SMALLINT	SMALLINT	SMALLINT
4-byte integer	INTEGER, BINARY_INTEGER	INTEGER	INT
8-byte integer	BIGINT	BIGINT	BIGINT
Single-precision floating point number	FLOAT4, REAL	FLOAT4, REAL	FLOAT
Double-precision floating point number	DOUBLE PRECISION, FLOAT8, BINARY_DOUBLE	DOUBLE PRECISION, FLOAT8, BINARY_DOUBLE	DOUBLE
Scientific data type	DECIMAL, NUMERIC	DECIMAL[p (,s)] (The maximum precision can reach up to 38.)	precision ≤ 38: DECIMAL; precision > 38: STRING
Date type	DATE	TIMESTAMP[(p)] [WITHOUT TIME ZONE]	TIMESTAMP
Time type	TIME [(p)] [WITHOUT TIME ZONE], TIME [(p)] [WITH TIME ZONE]	TEXT	STRING
	TIMESTAMP[(p)] [WITHOUT TIME ZONE], TIMESTAMP[(p)][WITH TIME ZONE], SMALLDATETIME	TIMESTAMP[(p)] [WITHOUT TIME ZONE]	TIMESTAMP
	INTERVAL DAY (l) TO SECOND (p), INTERVAL [FIELDS] [(p)]	VARCHAR(n)	VARCHAR(n)
Boolean type	BOOLEAN	BOOLEAN	BOOLEAN
CHAR type	CHAR(n), CHARACTER(n), NCHAR(n)	CHAR(n), CHARACTER(n), NCHAR(n)	n ≤ 255: CHAR(n); n > 255: STRING
VARCHAR type	VARCHAR(n), CHARACTER VARYING(n), VARCHAR2(n)	VARCHAR(n)	n ≤ 65535: VARCHAR(n); n > 65535: STRING
VARCHAR type	NVARCHAR2(n)	TEXT	STRING
String (large text object)	TEXT, CLOB	TEXT, CLOB	STRING
Binary type	BYTEA	BYTEA	BINARY
Monetary type	MONEY	NUMERIC	BIGINT

The DWS foreign table supports the NULL definition, and the Hive data table supports and uses the corresponding NULL definition.
The TINYINT value range in a Hive data table is [-128, 127] while in DWS it is [0, 255]. To avoid discrepancies between the read and actual values, it is recommended to use the SMALLINT type when creating a DWS read-only foreign table for TINYINT in the Hive table. Similarly, when exporting data of the TINYINT type from DWS, you are advised to use the SMALLINT type for write-only foreign tables and Hive tables.
The time zone definition is not supported by the date and time types of the DWS foreign table, or by the Hive table.
The DATE type in Hive contains only date. The DATE type in DWS contains date and time.
In DWS, ORC files can be compressed in ZLIB, SNAPPY, LZ4, or NONE mode. The FLOAT4 format itself is not accurate, and the sum operation results in different effect in various environments. You are advised to use the DECIMAL type in the high-precision scenarios.
In Teradata-compatible mode, foreign tables do not support the DATE type.
Binary types are available only for clusters of version 9.1.0.100 or later.