Setting up Remote Sources

Objectives

After completing this lesson, you will be able to:

Create a remote source with the user interface.
Create a remote source using SQL.

Remote Source Setup with the Graphical User Interface

When the technical and security-related prerequisites are in place, you're ready to create a remote source. There are two main ways to do that:

In the catalog of the SAP HANA Database Explorer, with the graphical user interface
In any SQL console connected to your local/target database, with an SQL statement

Let's discuss the graphical user interface-based creation process first.

To Create a Remote Source with the Graphical User Interface

Navigate the Catalog to the Remote Sources section.
Right-click Remote Sources, and choose Add Remote Source.
Define the remote source name.
Choose the relevant Adapter.
For SDI remote sources, specify an adapter location ("Source Location").
Fill in the remote source property.
Choose Create.

Launch this video to learn how to create a Smart Data Access remote source with the form-based editor in SAP HANA Database Explorer.

Creating remote sources for Smart Data Integration scenarios with the graphical user interface is very similar to what we have just discussed. One key difference is that, because the adapters aren't running on the indexserver of the SAP HANA database instance, it's necessary to specify where the adapters are located. Here's a reminder:

OData adapters run on the data provisioning server (inside the database).
The location dpserver is automatically assigned and can’t be changed.
All others Smart Data Integration adapters run on a data provisioning agent
You specify either a data provisioning agent, either a data provisioning agent group in case several agents are part of a high-availability agent group.
Note
The configuration of agents is determined by the system administration team. What you just need from them is the information on which agent or agent group should be used in a particular scenario.

Launch this video to learn how to create a Smart Data Integration remote source with the form-based editor in SAP HANA Database Explorer.

Checking a Remote Source

When you create a remote source in the SAP HANA Database Explorer with a form, two successive automatic checks are executed.

Remote source consistency – Blocking
The remote source definition must be "syntactically" correct with all the required parameters filled out.
The check might fail, for example, if you forgot the server name or didn’t provide the user name and password for a technical user.
In that case, the remote source can't be created. You must modify the definition of the remote source and try to save it again.
Note
In the remote source definition form, all mandatory parameters are identified with a red asterisk.
Remote source connectivity – Non-blocking
This second-level check, automatically triggered in the SAP HANA database explorer form, verifies whether the remote source definition allows SAP HANA to connect to the remote system and authenticate the remote user.
If this check isn’t passed, you only receive a warning, which does NOT prevent the creation of the remote source.
The check might fail, for example, due to an error in the server name or in the SAP Cloud Connector configuration (if applicable), or simply because the remote server isn't currently available. Another reason could be because the remote source is defined with secondary credentials but no credential currently exists for your user.
In that case, only a warning is issued. You can modify the definition of the remote source immediately (for example, if you identify an obvious error), but you can also save the remote source definition as is, and modify it later on.

Remote Source Setup with SQL

As an alternative to using the SAP HANA Database Explorer graphical user interface to create remote sources, you can execute SQL statements. One of the key benefits is that you can batch-create multiple remote sources very quickly.

The SQL statement is structured as follows:

Code Snippet

12345678

CREATE REMOTE SOURCE "<remote_source_name>"
ADAPTER "<adapter_name>"
AT LOCATION <agent_or_agent_group_name>
CONFIGURATION '<configuration_string>' 
WITH CREDENTIAL TYPE '<credential_type>'
                USING '<credential_definition_string>';

Let's take a simple example where we will define a Smart Data Access remote source pointing to an SAP HANA On-Premise source system.

Code Snippet

12345

CREATE REMOTE SOURCE "RS1"
ADAPTER "hanaodbc" 
CONFIGURATION 'ServerNode=hana_cloud_src1.mycompany.com:30115' 
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=myuser;password=MyPassword';

Note

Note that, because it is an SAP HANA smart data access remote source, there is no AT LOCATION clause.

For SAP HANA smart data access data sources, there are two flavors to define the configuration string. The one above is more compact. The lengthier version below passes the configuration as XML. They both achieve the same outcome.

Code Snippet

123456789101112

CREATE REMOTE SOURCE "RS1"
ADAPTER "hanaodbc"
CONFIGURATION
'<?xml version="1.0" encoding="UTF-8"?>
<ConnectionProperties name="connectionproperties">
  <PropertyEntry name="adapterversion">1.0</PropertyEntry>
  <PropertyEntry name="connectionmode">Adapter Properties</PropertyEntry>
  <PropertyEntry name="server">hana_cloud_src1.mycompany.com</PropertyEntry>
  <PropertyEntry name="port">30015</PropertyEntry>
</ConnectionProperties>'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=myuser;password=MyPassword';

Note

This lengthier form is optional for SAP HANA smart data access source. It is presented here because this is what you get when you choose GENERATE CREATE STATEMENT from an existing remote source in the SAP HANA Database Explorer catalog.

Launch this video to learn how to create a remote source with an SQL statement.

Matching Adapter Name (SQL) with Adapter Display Name (Graphical User Interface)

When you work with both SQL and the SAP HANA Database Explorer to create remote sources, you can find in the system table "SYS"."ADAPTERS" the list of adapters currently running on the SAP HANA Database. This allows you to match the name of an adapter that is used in a CREATE REMOTE SOURCE SQL statement with the display name that is shown in SAP HANA Database Explorer when you create or edit a remote source definition.

The table can be queried as follows:

Code Snippet

1
SELECT * FROM "SYS"."ADAPTERS";

The result shows all the system adapters (SDA), plus all the adapters that are currently registered on any of the data provisioning agents connected to your local SAP HANA database.

Checking a Remote Source

When you create a remote source with SQL, two levels of check are available.

Remote source consistency – Blocking
This check is done as part of the SQL statement CREATE REMOTE SOURCE.
The remote source definition must be "syntactically" correct, with all the required parameters filled out.
The check might fail, for example, if you forgot the server name, or did not provide a valid agent name in the AT LOCATION clause (when applicable).
In that case, the SQL statement fails and the remote source cannot be created. You must modify the SQL statement and execute it again.
Remote source connectivity – Non-Blocking
This check is optional, not part of the CREATE REMOTE SOURCE statement.
You can run an additional SQL statement to verify whether the remote source definition allows SAP HANA to connect to the remote system and authenticate the remote user.
Code Snippet
Copy code
Switch to dark mode
```
1
CALL CHECK_REMOTE_SOURCE('<remote_source_name>');
```
The execution of this procedure is either successful (check passed), or if it fails you get some details about the root cause in the SQL console.
This could be an error in the server name, or in the SAP Cloud Connector configuration if applicable, or simply the fact that the remote server is not currently available. In other contexts, this could be because the remote source is defined with secondary credentials but no such credential exist for your user.

Next lesson