Introduction to the SAP HANA smart data access linked database feature

[former_member231160]

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.