SAP HANA

Driver short name

HANA

Build dependencies

odbc-cpp-wrapper

This driver implements read and write access for spatial data stored in an SAP HANA database.

Driver capabilities

Supports Create()

This driver supports the GDALDriver::Create() operation

Supports Georeferencing

This driver supports georeferencing

Connecting to a database

To connect to an SAP HANA database, use a connection string specifying the database name, with additional parameters as necessary. The HANA: prefix is used to mark the name as a HANA connection string.

HANA:"DRIVER=HDBODBC;DATABASE=HAN;HOST=localhost;PORT=30015;USER=mylogin;PASSWORD=mypassword;SCHEMA=MYSCHEMA"

In this syntax each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value, or a value containing spaces, surround it with single quotes, e.g., keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslash, i.e., ' and \.

SQL statements

The HANA driver passes SQL statements directly to HANA by default, rather than evaluating them internally when using the ExecuteSQL() call on the OGRDataSource, or the -sql command option to ogr2ogr. Attribute query expressions are also passed directly through to HANA. It's also possible to request the OGR HANA driver to handle SQL commands with the OGR SQL engine, by passing "OGRSQL" string to the ExecuteSQL() method, as the name of the SQL dialect.

The HANA driver in OGR supports the OGRDataSource::StartTransaction(), OGRDataSource::CommitTransaction() and OGRDataSource::RollbackTransaction() calls in the normal SQL sense.

Creation Issues

The HANA driver does not support creation of new schemas, but it does allow creation of new layers (tables) within an existing schema.

Dataset Open options

Open options can be specified in command-line tools using the syntax -oo <NAME>=<VALUE> or by providing the appropriate arguments to GDALOpenEx() (C) or gdal.OpenEx (Python). The following open options are supported:

• DSN=value: Data source name.

• DRIVER=value: Name or a path to a driver. For example, DRIVER=HDBODBC (Windows) or DRIVER=/usr/sap/hdbclient/libodbcHDB.so (Linux/MacOS).

• HOST=value: Server host name.

• PORT=value: Port number.

• USER=value: User name.

• PASSWORD=value: User password.

• DATABASE=value: Database name.

• USER_STORE_KEY=value: (GDAL >= 3.8) Key defined in the SAP HANA user store (hdbuserstore).

• SCHEMA=value: Specifies schema used for tables listed in TABLES option.

• TABLES=value: Restricted set of tables to list (comma separated).

• ENCRYPT=[YES/NO]: Defaults to NO. Enables or disables TLS/SSL encryption.

• SSL_CRYPTO_PROVIDER=[commoncrypto/sapcrypto/openssl]: Cryptographic library provider used for SSL communication.

• SSL_KEY_STORE=value: Path to the keystore file that contains the server's private key.

• SSL_TRUST_STORE=value: Path to trust store file that contains the server's public certificate(s) (OpenSSL only).

• SSL_VALIDATE_CERTIFICATE=[YES/NO]: Defaults to YES. If set to true, the server's certificate is validated.

• SSL_HOST_NAME_IN_CERTIFICATE=value: Host name used to verify server's identity validated.

• CONNECTION_TIMEOUT=<milliseconds>: Defaults to 0. Connection timeout measured in milliseconds. The default value is 0 (disabled).

• PACKET_SIZE=<bytes>: Defaults to 1 MB. Sets the maximum size of a request packet sent from the client to the server, in bytes. The minimum is 1 MB.

• SPLIT_BATCH_COMMANDS=[YES/NO]: Defaults to YES. Allows split and parallel execution of batch commands on partitioned tables.

• DETECT_GEOMETRY_TYPE=[YES/NO]: Defaults to YES. Specifies whether to detect the type of geometry columns. Note, the detection may take a significant amount of time for large tables.

Dataset Creation Options

None

Layer Creation Options

Layer creation options can be specified in command-line tools using the syntax -lco <NAME>=<VALUE> or by providing the appropriate arguments to GDALDatasetCreateLayer() (C) or Dataset.CreateLayer (Python). The following layer creation options are supported:

• OVERWRITE=[YES/NO]: Defaults to NO. This may be "YES" to force an existing layer of the desired name to be destroyed before creating the requested layer.

• LAUNDER=[YES/NO]: Defaults to YES. This may be "YES" to force new fields created on this layer to have their field names "laundered" into a form more compatible with HANA. This converts to upper case and converts some special characters like "-" and "#" to "_". If "NO" exact names are preserved. If enabled the table (layer) name will also be laundered.

• PRECISION=[YES/NO]: Defaults to YES. This may be "YES" to force new fields created on this layer to try and represent the width and precision information, if available using DECIMAL(width,precision) or CHAR(width) types. If "NO" then the types REAL, INTEGER and VARCHAR will be used instead.

• DEFAULT_STRING_SIZE=value: Defaults to 256. Specifies default string column size.

• GEOMETRY_NAME=value: Defaults to GEOMETRY. Specifies the name of the geometry column in new table.

• GEOMETRY_NULLABLE=[YES/NO]: Defaults to YES. Specifies whether the values of the geometry column can be NULL or not.

• SRID=value: Specifies the SRID of the layer.

• FID=value: Defaults to OGR_FID. Specifies the name of the FID column to create.

• FID64=[YES/NO]: Defaults to NO. Specifies whether to create the FID column with BIGINT type to handle 64bit wide ids.

• COLUMN_TYPES=value: Specifies a comma-separated list of strings in the format field_name=hana_field_type that define column types.

• BATCH_SIZE=<bytes>: Defaults to 4194304 (4MB). Specifies the number of bytes to be written per one batch.

Multitenant Database Containers

In order to connect to a tenant database, you need to specify a port number assigned exactly to a desired instance. This port number can be determined by executing the following query from the tenant database.

SELECT SQL_PORT FROM SYS.M_SERVICES WHERE ((SERVICE_NAME='indexserver' and COORDINATOR_TYPE= 'MASTER') or (SERVICE_NAME='xsengine'))

For more details, see Section 2.9 Connections for Multitenant Database Containers in SAP HANA Multitenant Database Containers.

Examples

• This example shows how to list HANA layers on a specified host using ogrinfo command.

  ogrinfo -ro HANA:"DRIVER=HDBODBC;DATABASE=HAN;HOST=localhost;PORT=30015;USER=mylogin;PASSWORD=mypassword;SCHEMA=MYSCHEMA"
  

  or

  ogrinfo -ro HANA:"DSN=MYHANADB;USER=mylogin;PASSWORD=mypassword;SCHEMA=MYSCHEMA"
  

  or

  ogrinfo -ro HANA:"DRIVER=HDBODBC;USER_STORE_KEY=mykey;SCHEMA=MYSCHEMA"
  

• This example shows how to print summary information about a given layer, i.e. 'planet_osm_line', using ogrinfo.

  ogrinfo -ro HANA:"DRIVER=HDBODBC;DATABASE=HAN;HOST=localhost;PORT=30015;USER=mylogin;PASSWORD=mypassword;SCHEMA=MYSCHEMA" -so "planet_osm_line"
  
  Layer name: planet_osm_line
  Geometry: Line String
  Feature Count: 81013
  Extent: (732496.086304, 6950959.464783) - (1018694.144531, 7204272.976379)
  Layer SRS WKT:
  PROJCS["WGS 84 / Pseudo-Mercator",
      GEOGCS["WGS 84",
          DATUM["WGS_1984",
              SPHEROID["WGS 84",6378137,298.257223563, AHORITY["EPSG","7030"]],
              AUTHORITY["EPSG","6326"]],
              PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]],
              UNIT["degree",0.0174532925199433, AUTHORITY["EPSG","9122"]],
              AUTHORITY["EPSG","4326"]],
          PROJECTION["Mercator_1SP"],
          PARAMETER["central_meridian",0],
          PARAMETER["scale_factor",1],
          PARAMETER["false_easting",0],
          PARAMETER["false_northing",0],
          UNIT["metre",1,AUTHORITY["EPSG","9001"]],
          AXIS["X",EAST],
          AXIS["Y",NORTH],
          AUTHORITY["EPSG","3857"]]
  Geometry Column = way
  osm_id: Integer64 (0.0)
  access: String (4000.0)
  addr:housename: String (4000.0)
  addr:housenumber: String (4000.0)
  addr:interpolation: String (4000.0)
  admin_level: String (4000.0)
  aerialway: String (4000.0)
  aeroway: String (4000.0)
  

• This example shows how to export data from the 'points' table to a shapefile called 'points_output.shp'.

  ogr2ogr -f "ESRI Shapefile" "D:\\points_output.shp" HANA:"DRIVER=HDBODBC;DATABASE=HAN;HOST=localhost;PORT=30015;USER=mylogin;PASSWORD=mypassword;SCHEMA=GIS;TABLES=points"
  

• This example shows how to create and populate a table with data taken from a shapefile.

  ogr2ogr -f HANA HANA:"DRIVER=HDBODBC;DATABASE=HAN;HOST=localhost;PORT=30015;USER=mylogin;PASSWORD=mypassword;SCHEMA=MYSCHEMA" myshapefile.shp
  

For developers

To compile the SAP HANA driver, odbc-cpp-wrapper library needs to be linked or installed. For more details, see comments in nmake.opt or configure.ac files to build the driver for Windows or Linux/MacOS correspondingly.

See Also

• SAP HANA Home Page

• SAP HANA Spatial Reference

• SAP HANA ODBC Connection Properties