Creating Virtual Tables

Objective

After completing this lesson, you will be able to create and administer virtual tables in classic schemas and HDI Containers, and consume virtual tables from remote sources.

Virtual Tables

Accessing Remote Objects

To access data from specific objects of a remote system (remote objects), SAP HANA implements virtual tables. Virtual tables are catalog objects, and they are located in a schema.

A virtual table gives access to one remote object (a table or a view in the example below).

This slide explains the concept of Virtual Tables.

Note

Here we deliberately call artifacts on the remote system "objects", and not "tables", for at least two reasons:

First, the type of remote artifacts depends on the remote system. In a database system, of course, these could be tables. But in a file system, there are no tables, but files. A .csv or .txt file with a well-defined structure can most certainly be converted into a virtual table, but this depends on the capability of the adapter and requires a metadata definition to identify column types, among others.
Besides, even in a remote database system, there are classically tables and views. Most of the adapters can access remote tables and remote views in a similar way.

Once virtual tables are created, they can be consumed by the database processes as if they were physically located in the target database. However, the key principle of virtual tables is that they don’t store the data on the local database, but only metadata. Instead, they expose remote data through their supporting remote source whenever a query is executed on top of them.

Note

An alternative approach is possible, which is to consume remote data without virtual tables. It's called Linked Database, and is available only with Smart Data Access remote sources. The concept, benefits, and limitations are discussed later on.

Virtual Table Definition

A virtual table has two parts in its definitions:

The virtual table identifier
The remote object identifier, including the reference to the remote source

This slide gives an overview of the Virtual Table attributes.

Note

The name of the remote database can be <NULL> even if the remote system IS indeed a database. This is just because in some configurations, for example with SAP HANA as the remote system, the remote source object already determines the database with the host and port.

With other remote system types, a single remote source might reference a database server in which several databases can be reached. This is where the remote database setting is needed in a virtual table definition.

Security Consideration for Virtual Tables

When creating virtual tables, you need to fulfill a number of prerequisites regarding not only the availability of the remote source and remote system, but also the authorizations in place in both systems. Here they are.

On the Local System

To create a virtual table, the prerequisites for the local user are as follows:

The local user who created the remote source is automatically granted the privilege to create virtual tables on this remote source.
Any other use requires the CREATE VIRTUAL TABLE privilege on the remote source.
In any case, the local user needs CREATE ANY privileges on the schema where the virtual table will be stored.

On the Remote System

The credentials configuration of the remote source (or an existing secondary credential for your user on that remote source) determines which remote user is accessing the remote/source object.

Caution

If no credentials at all exist for the remote source (either technical user or secondary credentials), no access is possible to the remote objects. This can be worked around for some types of remote sources, but generally not recommended.

As a general rule, when you create a virtual table pointing to a remote database system, the remote user derived from the remote source definition (or an existing secondary credential associated to your user) only needs theCATALOG READprivilege (SAP HANA) or an equivalent, but NOT necessarily a SELECT privilege on the remote table or view (or its containing schema).

Note

This allows for a convenient security configuration when using secondary credentials; indeed, a user with no more than the CATALOG READ privilege (or equivalent) can create virtual tables without being able to select the data of the corresponding source objects.

How to Create a Virtual Table?

A virtual table can be created in two different ways:

From the SAP HANA Database Explorer, using the catalog
With an SQL statement

Virtual Table Setup with the Graphical User Interface

With the form-based editor, you use an existing remote source to list the objects it gives access to, and then create virtual objects for one or several of them.

This slide shows how to use the remote source object list.

With the form-based approach, you select remote objects first, and then define where to store the virtual table and how to name them.

So, let's discuss the different elements identifying remote objects first.

The Remote Source is a mandatory element, as discussed earlier.
The Database element is used for some remote sources types (that is, some adapters), when the remote source doesn't reference a specific database.
A common use case is when a remote database system can give access to more than one database from a single host and port.
For SAP HANA as a remote system, the remote source always specifies a single target database. As a consequence, the Database part is always <NULL>.
Schema
The schema is a classic artifact in database topology. In most of the database systems, including SAP HANA, tables, views, and many other objects, are located in schemas. However, the concept of schema doesn't always exist. For example, with the SDI File Adapter, there's no schema. So the Schema part is also <NULL>.
Object
Finally, the remote object is identified with its name. This element is mandatory in any virtual table definition.

Once you've chosen one or several objects to create a virtual table for, you need to define the identifiers of the virtual tables in your local SAP HANA system (target).

If you create a single virtual table, the dialog box asks you for the object name. On the contrary, if you create several virtual tables, the dialog box – as shown in the example– asks you for a prefix that will be added to all the remote objects' names.
Hint
In general, it's recommended to use a prefix that helps identify virtual tables. As a reminder, even if virtual tables have a dedicated icon in the catalog explorer, they're still listed with other classic row and column tables, so a specific prefix can help.
Last, but not least, the schema where to create the new virtual tables must be specified. By default, it defaults to your user's schema.

Launch this video to learn how to create a virtual table with the graphical user interface of the SAP HANA Database Explorer.

Virtual Table Setup with SQL

In addition to the remote source objects list in the SAP HANA Database Explorer, you can create virtual tables with SQL statements. This is handy when you already know the remote objects identifiers, especially if you want to create virtual tables in bulk.

The statement is as follows:

Code Snippet

12
CREATE VIRTUAL TABLE <schema>.<virtual_table_name>
AT <remote_source>.<database>.<schema>.<object_name>;

The information already discussed about how to define the remote objects when creating a virtual table from the graphical user interface, applies the same way. Like in any SQL statement in SAP HANA, identifiers must be enclosed in double quotes, or they're automatically interpreted as case-insensitive (and stored in upper-case).

In some scenarios, the remote <database> and/or <schema> might need to be set to "<NULL>".

Caution

Even if you use unquoted identifiers for the remote source or object name, the <NULL> value for a database or schema, when applicable, must ALWAYS be quoted. Without the double quotes, the SQL statement will fail.

Here's an example of two SQL statements to create the same virtual tables as the ones we created earlier based on objects in the remote EPM_MODEL schema (source CL_RS_OP_SDA):

Code Snippet

12345
CREATE VIRTUAL TABLE "CL_DATA_ENGINEER_FED"."VT_SNWD_PO"
AT "CL_RS_OP_SDA"."<NULL>"."EPM_MODEL"."SNWD_PO";

CREATE VIRTUAL TABLE "CL_DATA_ENGINEER_FED"."VT_SNWD_PO_I"
AT "CL_RS_OP_SDA"."<NULL>"."EPM_MODEL"."SNWD_PO_I";

If the session user is CL_DATA_ENGINEER_FED, the mention of this schema in the virtual table identifier could be omitted.

Launch this video to learn how to create a virtual table with an SQL statement.

Virtual Table Definition in an SAP Business Application Studio Project

Accessing Remote Objects from an HDI Container

When you work in development or modeling projects in SAP Business Application Studio, you have the possibility to consume remote sources and create virtual tables from within an SAP HANA Database module (containing design-time artifacts), which is deployed to a HDI Container (runtime artifacts).

A dedicated design-type artifact, .hdbvirtualtable, is used for that purpose.

As a reminder, a remote source in SAP HANA isn't a schema object. So, you can’t create a remote source inside the HDB module of a project. However, you can set up your project so that it consumes an existing remote source.

Let's see how to do this.

How to Consume Remote Objects

Consuming remote objects from within an HDI container relies on the following prerequisites:

A User-Provided Service is running in the Cloud Foundry space where you plan to deploy your HDI container service.
This user-provided service must be assigned a user with the following privileges:
CREATE VIRTUAL TABLE on the remote source you want to use.
The user-provided service is bound to your project in the dependency definition of the mta.yaml file
A .hdbgrants file gives the relevant privileges to the HDI Object Owner (#OO) user who will be owning the HDI container objects.

Note

The project must be successfully deployed after making these changes, so that the object owner is actually given the relevant privileges, allowing you to create virtual tables.

Creating Virtual Tables in SAP Business Application Studio

To create a virtual table in SAP Business Application Studio, you create a corresponding .hdbvirtualtable design-time file. This file can be edited either in a text editor or in a dedicated editor. The latter brings one benefit, which is the ability to read the remote source object hierarchy.

This slide shows how to create a virtual table in SAP Business Application Studio.

In a text editor, the same design-time object will look as follows:

Code Snippet

12
VIRTUAL TABLE "CL_V_SNWD_PO"
AT "CL_RS_OP_SDA"."<NULL>"."EPM_MODEL"."SNWD_PO"

Using the double quotes is recommended to avoid inconsistencies in the use of upper and lower case. Depending on the naming convention you've chosen, it might also be mandatory. For example, if you plan to use object names mixing lower- and upper-case, if you use namespaces in object identifiers, and/or these identifiers include dots.

Note

Whether you use double quote delimiters or not in general, the "<NULL>" keyword must always be quoted whenever it’s used in a virtual table definition. This is illustrated in the Database Name field in the example above.

Compared with virtual tables created in a classic schema, the ones you define with a design-time file in an SAP Business Application project do NOT specify a schema, as is the case in SAP Business Application Studio projects for any other objects such as tables, column views, and so on.

In case your naming convention includes a namespace in the object identifier, you must add this namespace as a prefix in the Virtual table name field, as follows: <name.space.specification>::<virtual_table_name>

Launch this video to learn how to create virtual tables with design-time files in an SAP Business Application Studio project .

Consuming a Virtual Table

Once a virtual table is successfully created, it can be consumed by any user with appropriate privileges. Let's take a look at the required privileges:

On the local system: SELECT privilege on the virtual object.
This is the classical privilege needed to access data from a table. It can be granted to a user or (preferably) a role, either on a specific virtual table, or on the entire schema.
On the remote system: SELECT on the remote object
Consuming a virtual table requires that the remote user derived from the remote source definition (technical user, or a secondary credential assigned to you for this remote source) give access to the remote object. In a remote database system, this is generally granted via a SELECT privilege.
Note
On sources systems that are not databases, for example a file system, there might be a different way to set up data access. Using secondary credentials is not always possible in these cases. For example, the FileAdapter of SAP HANA smart data integration can store a token to secure access to a file repository. This token is then used in the remote source security definition.

Remote Source Administration

Beyond the creation of a remote source, a data provisioning administrator is responsible for its maintenance. This includes, for example, remote source privileges, modifications to the remote source definition or to the security configuration. This can also be the deletion of a remote source when it is no longer needed.

Let's stress a very important aspect of this maintenance. Virtual tables depend on a remote source. So, as soon as a virtual table is defined on top of a remote source, you CANNOT delete the remote source while keeping the virtual tables.

Fortunately, you can modify the remote source definition even if it has virtual tables defined on it.

Maintaining Privileges on a Remote Source

When a remote source is created, the user who created it is initially the only one with privileges on this remote source. These privileges can or must be granted to other users, depending on how the authorization tree is set up in your organization.

The remote source privileges are object privileges (they refer to a specific remote source object) and they can be classified into two categories:

Administration privileges:
- ALTER
- DROP
Privileges to operate the remote source:
- CREATE VIRTUAL TABLE
- LINKED DATABASE
- CREATE REMOTE SUBSCRIPTION
- PROCESS REMOTE SUBSCRIPTION EXCEPTION

Another privilege is CREDENTIAL ADMIN, which is required to administer secondary credentials on a remote source that you did NOT create.

All these privileges are classically administered in the SAP HANA Cockpit or with GRANT and REVOKE statements in an SQL console.

Modifying a Remote Source

To modify a remote source, you use the graphical user interface in SAP Database Explorer, or run an ALTER REMOTE SOURCE statement in an SQL console.

This allows you to modify all the elements of a remote source definition, EXCEPT its name.

Note

Indeed, the name of the remote source is what is used to keep the relationship with the virtual tables created on top of the remote source.

A typical scenario could be a remote system moving to a different host, the modification of a setting such as the DML mode (read or read/write), or an alias schema. This could also be a change in the security definition, for example moving from technical user to secondary credentials to enable more fine-grained access to remote objects.

Deleting a Remote Source

With the difference between modifying and deleting a remote source in mind, let's discuss how to delete a remote source.

Deleting a Remote Source Using the Graphical User Interface

In SAP HANA Database Explorer, navigate the catalog to the Remote Sources section.
Right-click the remote source and choose Delete.
Choose the relevant option:
- Drop remote source if no other objects depend in it
- Drop remote source and any other objects that depend on it
Choose Delete.

Caution

Use the second option very carefully, because you could lose a lot of virtual tables if you delete the wrong remote source.

Deleting a Remote Source with SQL

In an SQL console, you delete a remote source by running one of the following statements:

To delete the remote source if no other objects depend in it:
Code Snippet
```
1
DROP REMOTE SOURCE <remote_source_name>;
```
To delete the remote source and any other objects that depend on it:
Code Snippet
```
1
DROP REMOTE SOURCE <remote_source_name> CASCADE;
```

Caution

Again, you should use the CASCADE option very carefully.

Next lesson