Accessing Remote Tables using the Linked Database Feature

Objectives

After completing this lesson, you will be able to:

  • Access remote tables without creating virtual tables

Linked Database

Consuming Remote Tables or Views with the Linked Database Feature

With SAP HANA smart data access connections based on ODBC adapters, there's a way to consume remote tables and views that doesn’t require the creation of virtual tables. It's called linked database.

A benefit of the linked database feature is that it reduces the maintenance of database objects on the local system. You can directly consume remote tables or views, for example, in SQL queries, without creating a virtual table for each of them.

Linked database only works in data virtualization scenarios. In a configuration where you need to implement data replication, you need to create virtual tables the classic way.

Caution
The following use cases are NOT supported:
  • Smart data access remote sources with REST API adapters (for example: Amazon Athena and Google BigQuery)
  • Smart data integration remote sources

In particular, the linked database feature is available between two SAP HANA systems, either cloud or on-premise, as shown in the figure.

The concept of the linked database is that you access an object (table or view) on the remote system by referencing it directly in a SQL statement.

In this SQL statement, as shown in the figure, the classical "<schema_name>"."<object_name>" notation is replaced by a three-part notation: "remote_source"."remote_schema"."remote_object".

Note
Compared with the four-part identifier in a virtual table, there's no "<database_name>" part in the remote object identifier.

When you access a remote table or view with the linked database feature, SAP HANA creates a hidden virtual table located in the schema _SYS_LDB.

This hidden virtual table is used to store the metadata of the remote object and isn’t meant to be queried.

Note

There is, however, an optimized mode available for the linked database feature when deployed between two SAP HANA systems. This mode doesn’t require hidden virtual tables.

How to Use the Linked Database Feature

To use the linked database feature, a specific privilege LINKED DATABASE on the remote source must be granted to the consumer of remote objects.

Note
This is all the more essential as there's not a (real) virtual object on which individual privileges such as SELECT, INSERT, and so on, could be given only to users who need access.

Launch this video to learn how to access remote tables and views using the linked database feature.

Linked Database – Optimized Mode

In scenarios where both the source and target systems are SAP HANA databases (and connected via an SAP HANA smart data access remote source), it's possible to use an optimized version of the linked database feature.

Compared with the standard linked database feature, this one does NOT create hidden virtual tables, but caches locally the metadata of the remote objects you select data from. So there's no housekeeping needed (no hidden virtual tables) and no need to refresh statistics for remote objects: They're fetched automatically when needed.

There are a few limitations to SAP HANA features with the linked database – optimized mode. Please refer to SAP Note 2605574.

How to Enable Linked Database – Optimized Mode

The optimized mode is disabled by default. To enable it, you add an additional setting in the configuration string of your remote source: linkeddatabase_mode=optimized.

Here's an example of a remote source definition

Code snippet
CREATE REMOTE SOURCE MY_HANA1
ADAPTER "hanaodbc"
CONFIGURATION 'Driver=libodbcHDB.so;ServerNode=myserver:30115;linkeddatabase_mode=optimized'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=user1;password=Test1234';
Expand

Maintaining Linked Database Artifacts

On remote sources that do NOT use the optimized mode, hidden virtual tables can be maintained with SQL statements. The objective is to either refresh them to keep the metadata in sync with the state of the remote objects, or to delete them when they're not needed any longer.

To refresh the metadata, you can run one of the following ALTER REMOTE SOURCEstatements:

  • To refresh the metadata for all linked objects:
    Code snippet
    ALTER REMOTE SOURCE <remote_source> REFRESH LINKED OBJECTS;
    Expand
  • To refresh the metadata for a single object:
    Code snippet
    ALTER REMOTE SOURCE <remote_source>
    REFRESH LINKED TABLE <remote_source>.<remote_schema>.<table_name>;
    Expand
Note
The LINKED DATABASE privilege is needed to run these statements.

To delete the internally generated objects associated with linked database access, you execute an ALTER REMOTE SOURCE statement as follows:

Code snippet
ALTER REMOTE SOURCE <remote_source_name>
DROP LINKED OBJECTS [CASCADE | RESTRICT];
Expand
  • The statement without option deletes linked objects on which no other database objects depend but keeps linked objects that have dependencies.
  • With the CASCADE option, the statement deletes all linked objects as well as any database object that depend on them.
  • With the RESTRICT option, the statement deletes all linked objects ONLY if no database object at all depend on them. If there's even a single dependency on a single linked object, no linked object at all is deleted.

Log in to track your progress & complete quizzes