Introduction to the SAP HANA smart data access linked database feature
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:
- Create the remote source and grant the LINKED DATABASE privilege to the user(s) who will be accessing data
- Next, create the secondary credential for the user
- 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.
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?
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
Hi Aleks,
wondering what would be the result of running alter statement for refreshing the linked database... I mean the list of updates/list of conflicts/... what it means to the administrators/developers from the workflow perspective.
It succeeds and they do what?
It fails and they do what?
I got this question from a potential customer I was not able to react.
but many thanks for these blogs anyway, helped a lot.
Michal
Hi Aleks,
How can I use a Linked Database object in a calc view. The above example is for SQL query, but how to add the linked database remote objects in a Graphical calc view in HANA studio.
Regards,
Ravindra Channe
is it possible to use Linked database with Remote Provisioning agent ?
How can I know which objects have the remote connection dynamically, is there a system table?
Thank you
Can we use a linked database in a HANA CDS view?
If we have to hardcode the the three part table name, prefixing the table name with the database, how does this work when transporting the SQL statements from development to QAS to production environments, where for each the database name is different? These will not work once transported as they will then point to the wrong database. What is the database setup that allows for correct transportation where the hardcoded values work, or is it possible to use substitution variables that retrieve the associated linked database from a table?
Hello,
this is an old question but I had the same question and I found this blog that can help others :
https://sapyard.com/abap-for-sap-hana-part-23-how-to-access-database-schema-dynamically-using-amdp/