Data Types for HAWQ

Overview

Every column in a table has a name and a data type. The data type tells HAWQ how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about HAWQ data types, null and default values, and data conversions.

SAS/ACCESS Interface to HAWQ does not directly support any data types that are not listed below. Any columns using these types are read into SAS as character strings.

For more information about HAWQ data types and to determine which data types are available for your version of HAWQ, see your HAWQ documentation.

Supported HAWQ Data Types

Here are the data types that SAS/ACCESS Interface to HAWQ supports:

• Character data:

  CHAR(n)

  VARCHAR(n)

  TEXT

• Numeric data:

  BIGINT	REAL
  SMALLINT	FLOAT
  INTEGER	DECIMAL | DEC | NUMERIC
  DOUBLE PRECISION

  Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data. For more information, see Your Options When Choosing Your Needed Degree of Precision.
• Date, time, and timestamp data:

  DATE

  TIME

  TIMESTAMP

  Note: Be aware that columns of these data types can contain data values that are out of range for SAS.
• Binary data: BYTEA

HAWQ Null Values

HAWQ has a special value called NULL. A HAWQ NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a HAWQ NULL value, it interprets it as a SAS missing value. When loading SAS tables from HAWQ sources, SAS/ACCESS stores HAWQ NULL values as SAS missing values.

In HAWQ tables, NULL values are valid in all columns by default. There are two methods to specify a column in a HAWQ table so that it requires data:

• Using SQL, you specify a column as NOT NULL. This tells SQL to allow only a row to be added to a table if a value exists for the field. Rows that contain NULL values in that column are not added to the table.
• Another approach is to assert NOT NULL DEFAULT.

When creating HAWQ tables with SAS/ACCESS, you can use the DBNULL= data set option to specify the treatment of NULL values. For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.

Once you know whether a HAWQ column enables NULLs or the host system provides a default value for a column that is specified as NOT NULL WITH DEFAULT, you can write selection criteria and enter values to update a table. Unless a column is specified as NOT NULL or NOT NULL DEFAULT, it enables NULL values.

To control how the DBMS handles SAS missing character values, use the NULLCHAR= and NULLCHARVAL= data set options.

LIBNAME Statement Data Conversions

The following table shows the default formats that SAS/ACCESS Interface to HAWQ assigns to SAS variables when using the LIBNAME statement to read from a HAWQ table.

These default formats are based on HAWQ column attributes.

The next table shows the default HAWQ data types that SAS/ACCESS assigns to SAS variable formats during output operations when you use the LIBNAME statement.