Introduction to the SAP HANA smart data access linked database feature

The linked database feature was introduced with SAP HANA 2.0 SPS 01, and greatly simplifies the workflow for accessing remote data sources. With the new linked database feature users can directly access remote tables and views without having to manually define virtual tables.

The standard SAP HANA smart data access (SDA) workflow requires users to explicitly define virtual tables.

Image 1: Adding a virtual table in SAP HANA studio

Linked database allows you to skip this step, and directly perform DML queries on any remote table or SAP HANA calculation view with a three part name (remote_source.schema.table). Linked database is not intended to replace the use of traditional SDA virtual tables, but rather to provide more streamlined method for accessing remote data in certain circumstances.

It is important to understand the key differences between using linked database versus the traditional SDA virtual tables. With this initial release of linked database, the target is for use in basic SAP HANA to SAP HANA scenarios.  Additional sources and scenario support is planned for subsequent releases. The following SQL statements are supported through linked database currently:

  • CREATE/ALTER/DROP/REFRESH STATISTICS
  • CREATE SYNONYM
  • DELETE/INSERT/SELECT/ UPDATE
  • REPLACE/UPSERT
  • TRUNCATE

If you would like to perform other queries, virtual tables must still be used. Please see the following SAP notes for specifics to see if your target use case fits the current capabilities of the linked database feature: SAP Note 2450518, SAP Note 2450517, and SAP Note 2450503.

Getting started with linked database

There are a couple of security considerations that you need to keep in mind when leveraging the linked database feature. First, any user other than the owner of the remote source needs newly introduced LINKED DATABASE privilege to access data via linked database. This privilege must be granted to the user(s) who will be accessing the remote source.  Additionally, when configuring the linked database connections, the recommendation is to use secondary credentials rather than a technical user. This delegates access control to the remote system which owns the data.

Configuring linked database is straightforward, there are three primary steps:

  1. Create the remote source and grant the LINKED DATABASE privilege to the user(s) who will be accessing data
  2. Next, create the secondary credential for the user
  3. The user you gave the LINKED DATABASE privilege to can now query remote tables and views by using three part table names

Just as with SDA virtual tables, statistics can also be generated for linked database connections. Simply use the CREATE STATISTICS command and use the three-part table name to generate statistics. Keep in mind also that remote table schemas can change, and to account for this change linked database objects can be refreshed both individually and all at once. To refresh a single linked table, use the following SQL statement:

To refresh all linked objects, use this statement:

One final housekeeping item to keep in mind, linked database creates internally generated virtual tables. These tables can grow over time, and you may want to clean out tables that are no longer being used. Below is an example of a statement you can use to drop unused internally generated virtual tables:

Additionally, you may specify the CASCADE option to drop all internally generated tables and any dependent objects or use the RESTRICT option to stop the cleanup if there are any dependent objects.

Please note, it is possible to automate this task using pre-defined retention periods. By default, the cleanup task is disabled. To enable the task, define the number of seconds for the “linked_database_cleanup_interval” parameter in the indexserver.ini file. Below is an example for how to set the cleanup function to wake up once a day:

Thanks for reading the introduction to linked database blog. For more information on configuring and using the new SAP HANA smart data access linked database feature please refer to the SAP HANA Administration guide. If you are interested in learning more about What’s new in SAP HANA 2.0 SPS 01 smart data access in general, please refer to this blog.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Benedict Venmani Felix

    Hi Aleks,

    For virtual tables, once the table is added to a schema we can see the table in the Catalog. How will a user know what tables are made available as linked tables? Will the user be able to see the system or schema in his Catalog for linked tables?

    (0) 
    1. Aleks Aleksic Post author

      Hi Benedict,

      With the linked database feature, there is no longer a need to manually add virtual tables to a schema in order to access them as is traditionally done with SDA.  Once a SDA connection is established using linked database, any user (provided they have the linked database privilege) will be able to directly query the remote tables using three-part table names. The SDA linked database connection itself will still appear as a “remote connection,” so the user will be able to see that there is a connection to the remote source. Although the linked database feature generates virtual tables under the hood, users are not able to access these virtual tables directly so they will not be able to see them. However, subsequent queries to the same remote table are automatically redirected to the internal virtual tables.

      Regards,

      Aleks

       

      (1) 

Leave a Reply