Data Types for SAP HANA

Overview

Here are the data types that the SAP HANA engine supports.

• Character data:

  VARCHAR(n)

  NCHAR

  NVARCHAR(n)

  ALPHANUM

  SHORTTEXT

  Note: The SAS/ACCESS for SAP HANA engine calculates the length of SAP HANA NCHAR and NVARCHAR data types by multiplying the number of SAP HANA characters by 3 when reading data to a SAS session with UTF8. This calculation provides enough space to read an SAP character-based NCHAR or NVARCHAR type into a SAS byte-based character variable. For example, an SAP HANA character variable of 3 characters would be read into a SAS byte-based character variable calculated as length 9. When writing SAS data to SAP HANA, the engine needs to specify an NCHAR or NVARCHAR variable length that allows all of the SAS byte-based character column to the SAS HANA character column. For example, a SAS character variable that holds 9 characters might contain 9 single-byte characters. The engine ensures enough space in the SAP HANA variable length by writing the data using a length of 9 characters. To override the default type determined by the SAS/ACCESS engine for SAP HANA, use the DBTYPE= Data Set Option .
• Binary data: VARBINARY
• Large object (LOB) data:

  BLOB (binary large object)

  CLOB (character large object)

  NCLOB

  TEXT

• Numeric data:

  TINYINT	SMALLDECIMAL
  SMALLINT	REAL
  INTEGER	DOUBLE
  BIGINT	FLOAT(n)
  DECIMAL(precision,scale) or DEC(p,s)

  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

  SECONDDATE

  TIMESTAMP

SAP HANA Null Values

Many relational database management systems have a special value called NULL. A DBMS NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a DBMS NULL value, it interprets it as a SAS missing value.

In most relational databases, columns can be specified as NOT NULL so that they require data (they cannot contain NULL values). When a column is specified as NOT NULL, the DBMS does not add a row to the table unless the row has a value for that column. When creating a DBMS table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.

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

Geospatial Data

SAS/ACCESS Interface to SAP HANA can import and export geospatial data of the type ST_GEOMETRY. For example, you can export the following subtypes of geospatial data to SAP HANA tables:

ST_POINT

represents a single location (point) that is represented by a pair of latitude and longitude coordinates. Data of the geospatial type point in an SAP HANA table might look like the following example:

SHAPE.ST_ASGEOJSON()
{"type": "Point", "coordinates": [84.248239999999996,18.792193999999999]}

In a SAS table, the latitude and longitude coordinates are each stored in a numeric variable.

ST_POLYGON

represents a set of points that specify the perimeter of an area. The points specify the vertices of a closed polygon. A record for an item of type ST_POLYGON is made of multiple pairs of coordinates that specify the points (vertices) in the polygon. The first and last points must contain the same values. Geospatial data of type polygon in an SAP HANA table might look like the following example:

SHAPE.ST_ASWKT()
POLYGON ((
84.24824 18.79219, 84.25500 18.78855, 84.25984 18.78489, 84.28588 18.78654, 
84.29696 18.79453, 84.31144 18.79190, 84.32205 18.79154, 84.33023 18.80136, 
84.33841 18.80701, 84.34510 18.82981, 84.35423 18.84336, 84.35228 18.85309, 
84.34839 18.86280, 84.34835 18.87626, 84.35803 18.86846, 
...
83.93397 18.64837, 83.93369 18.68782, 83.94396 18.69438, 83.96128 18.67464, 
83.99588 18.64173, 84.01654 18.63514, 84.04403 18.63511, 84.05769 18.64825, 
84.10570 18.66136, 84.11241 18.68436, 84.11224 18.70737, 84.11905 18.71723, 
84.18427 18.72703, 84.19458 18.72702, 84.19444 18.74674, 84.22868 18.76315, 
84.24824 18.79219 ))

This data would be read from multiple records in a SAS data set into a single record in the SAP HANA table. Each row in the SAS data set contains the coordinates from one point (vertex) of a polygon. Multiple rows from the SAS data set are combined and exported into SAP HANA into a variable of data type ST_POLYGON.

Other SAP HANA ST_GEOMETRY subtypes, in addition to the examples shown here, can be supported.

Overview of Importing Geospatial Data into SAS

In SAP HANA, geospatial data (data of type ST_GEOMETRY) can take a number of subtypes, including point (ST_POINT), polygon (ST_POLYGON), and multi-polygon (ST_MULTIPOLYGON). Geospatial data consists of pairs of latitude and longitude coordinate values. These coordinates can be read into numeric variables, such as X and Y, that respectively store the latitude and longitude values for each point. Typically, you must convert relevant parts of geospatial data input into numeric or character data in SAS. If multiple geospatial points (representing longitude and latitude coordinates) are imported, such as for the subtype ST_POLYGON, the best practice is to import each into separate rows. You can combine the rows before exporting the data back to SAP HANA.

To import geospatial data, import the values by using PROC SQL. Alternatively, you can import SHP files by using PROC MAPIMPORT. For more information, see SAS SQL Procedure User’s Guide, SAS/GRAPH: Reference, and your SAP HANA documentation.

Example 1: Export Point Data into SAP HANA

The following code shows how to export point coordinates into a table in SAP HANA:

execute (insert into "MY_GEOSPATIAL_FROM_SAS"(segment, area, flag, 
                 party, pc_code, pc_hname, pc_name, pc_no, pc_type, st_code, 
                 st_name , shape)
   (select segment, area, flag, party, pc_code, pc_hname, pc_name, pc_no, 
           pc_type, st_code, st_name,  new ST_POINT('POINT('||x||' '||y||')') 
           from #temp_a))
    by saphana;

In this example, the numeric variables X and Y in the SAS data set specify the latitude and longitude coordinates for a point. The data from table SAS data set TEMP_A is exported into an SAP HANA table MY_GEOSPATIAL_FROM_SAS that contains geospatial data of type ST_POINT.

Example 2: Export Polygon Data into SAP HANA

In SAS, data for a polygon that represents a city is stored in multiple rows. Each row contains the latitude and longitude coordinates for a point (vertex) of the polygon that specifies the city’s perimeter. All of the rows for one city must be combined into a single row in the target SAP HANA table. Add the coordinates to a single instance of a variable of type ST_POLYGON.

Before exporting coordinates for geospatial data of type ST_POLYGON, generate an intermediate SAS data set that combines the coordinate data from each row into a single record for each polygon. You can do this using FIRST. and LAST. processing and concatenation functions.

In this example, the coordinates for each vertex of the polygon were originally stored in separate variables for the latitude and longitude. These pairs of values are combined into a variable, ST_POLY, that lists the pairs of coordinates separated by commas. A row in the intermediate data set might contain data that looks similar to this:

ST_POLY = 84.248239999999996  18.792190000000002, 84.228679999999997  
                     18.763150000000000, 84.194440000000000 18.746739999999999
City = Cary
Zip = 27513 
State = NC

The following code shows how to export polygon data from the intermediate SAS data set into a table in SAP HANA:

execute (insert into "MY_GEOSP_POLYGON_FROM_SAS"(segment, area, 
                 flag, party, pc_code, pc_hname, pc_name, pc_no, pc_type, 
                 st_code, st_name , shape)
   (select segment, area, flag, party, pc_code, pc_hname, pc_name, pc_no, 
           pc_type, st_code, st_name,  
           new ST_POLYGON('POLYGON(('||to_char(ST_POLY)||'))') 
           from #temp_a))
    by saphana;

In this example, the data in the ST_POLY variable in the intermediate SAS data set TEMP_A is exported into an SAP HANA table called MY_GEOSP_POLYGON_FROM_SAS. This table contains geospatial data of type ST_POLYGON.