Google BigQuery as Remote Source for SAP HANA Cloud Smart Data Access

SAP HANA Smart Data Access | SDA

SAP HANA smart data access (SDA) allows you to access remote data as if the data were stored in local tables in SAP HANA, without copying the data into SAP HANA.

For the documentation, see

• SAP HANA Smart Data Access

The following databases are supported as remote sources for SAP HANA Cloud.

• SAP HANA Cloud
• SAP HANA (public cloud only)
• SAP IQ (public cloud only)
• Amazon Athena
• Google BigQuery

For more detailed information, see

• 2600176 – SAP HANA Smart Data Access Supported Remote Sources

The note specially mentions that remote sources to on-premise databases from SAP HANA Cloud is not supported (yet), nor is the SAP HANA Spark Controller.

Google BigQuery

If case you are new to BigQuery, here is a recently blog series that provides a good place to start.

• [New blog series] BigQuery explained: An overview

Dataset

For this exercise, we will be using a well-known (amongst BigQ aficionados and Qwiklabs pros) sample from the Google BigQuery documentation.

• Quickstart using the Cloud Console

Public datasets are not accessible from SAP HANA Cloud, so create the babynames dataset and the names_2014 table.

Here is a sample query, which we will execute also at the end but then from the SAP HANA Cloud side.

SELECT name, count
FROM `babynames.names_2014`
WHERE gender = 'M'
ORDER BY count DESC LIMIT 5

Service Account

The SAP HANA Cloud remote source for Google BigQuery uses a REST API to connect. For authentication, create a service account and grant the account the BigQuery User and BigQuery Data Viewer role (assuming read-only use case).

• Creating and managing service accounts

gcloud iam service-accounts create SERVICE_ACCOUNT_ID \
    --description="DESCRIPTION" \
    --display-name="DISPLAY_NAME"
gcloud projects add-iam-policy-binding PROJECT_ID \
    --member="serviceAccount:SERVICE_ACCOUNT_ID@PROJECT_ID.iam.gserviceaccount.com" \
    --role="ROLE_NAME"

Service Account Private Key

To authenticate the service user, a private key is required. Create a private key for the service account in JSON format.This file will be downloaded to your computer. When creating the remote data source, we will be using the private key (base-64 encoded) and client_email (service key name).

• Managing service account keys

Google Trust Services Certificate

Download Certificate

When we select the certificate from google.com in Chrome, the following chain is presented

• GlobalSign – Certificate authority (CA)
  • GTS CA 101 – Intermediate CA
    • *.google.com – web site

To establish the trust, we need to import the GTS CA 101 certificate into the SAP HANA Cloud database.

There are different ways how you can do this, below two examples for macOS and Windows.

macOS

On macOS, you can drag the certificate to the Desktop, import into Keychain Access (double-click) and then export as PEM file format.

When you open the certificate using a text editor, it should be base-64 encoded plain text.

Current certificate expires 15 Dec 2021.

-----BEGIN CERTIFICATE-----
MIIESjCCAzKgAwIBAgINAeO0mqGNiqmBJWlQuDANBgkqhkiG9w0BAQsFADBMMSAw
HgYDVQQLExdHbG9iYWxTaWduIFJvb3QgQ0EgLSBSMjETMBEGA1UEChMKR2xvYmFs
U2lnbjETMBEGA1UEAxMKR2xvYmFsU2lnbjAeFw0xNzA2MTUwMDAwNDJaFw0yMTEy
MTUwMDAwNDJaMEIxCzAJBgNVBAYTAlVTMR4wHAYDVQQKExVHb29nbGUgVHJ1c3Qg
U2VydmljZXMxEzARBgNVBAMTCkdUUyBDQSAxTzEwggEiMA0GCSqGSIb3DQEBAQUA
A4IBDwAwggEKAoIBAQDQGM9F1IvN05zkQO9+tN1pIRvJzzyOTHW5DzEZhD2ePCnv
UA0Qk28FgICfKqC9EksC4T2fWBYk/jCfC3R3VZMdS/dN4ZKCEPZRrAzDsiKUDzRr
mBBJ5wudgzndIMYcLe/RGGFl5yODIKgjEv/SJH/UL+dEaltN11BmsK+eQmMF++Ac
xGNhr59qM/9il71I2dN8FGfcddwuaej4bXhp0LcQBbjxMcI7JP0aM3T4I+DsaxmK
FsbjzaTNC9uzpFlgOIg7rR25xoynUxv8vNmkq7zdPGHXkxWY7oG9j+JkRyBABk7X
rJfoucBZEqFJJSPk7XA0LKW0Y3z5oz2D0c1tJKwHAgMBAAGjggEzMIIBLzAOBgNV
HQ8BAf8EBAMCAYYwHQYDVR0lBBYwFAYIKwYBBQUHAwEGCCsGAQUFBwMCMBIGA1Ud
EwEB/wQIMAYBAf8CAQAwHQYDVR0OBBYEFJjR+G4Q68+b7GCfGJAboOt9Cf0rMB8G
A1UdIwQYMBaAFJviB1dnHB7AagbeWbSaLd/cGYYuMDUGCCsGAQUFBwEBBCkwJzAl
BggrBgEFBQcwAYYZaHR0cDovL29jc3AucGtpLmdvb2cvZ3NyMjAyBgNVHR8EKzAp
MCegJaAjhiFodHRwOi8vY3JsLnBraS5nb29nL2dzcjIvZ3NyMi5jcmwwPwYDVR0g
BDgwNjA0BgZngQwBAgIwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly9wa2kuZ29vZy9y
ZXBvc2l0b3J5LzANBgkqhkiG9w0BAQsFAAOCAQEAGoA+Nnn78y6pRjd9XlQWNa7H
TgiZ/r3RNGkmUmYHPQq6Scti9PEajvwRT2iWTHQr02fesqOqBY2ETUwgZQ+lltoN
FvhsO9tvBCOIazpswWC9aJ9xju4tWDQH8NVU6YZZ/XteDSGU9YzJqPjY8q3MDxrz
mqepBCf5o8mw/wJ4a2G6xzUr6Fb6T8McDO22PLRL6u3M4Tzs3A2M1j6bykJYi8wW
IRdAvKLWZu/axBVbzYmqmwkm5zLSDW5nIAJbELCQCZwMH56t2Dvqofxs6BBcCFIZ
USpxu6x6td0V7SvJCCosirSmIatj/9dSSVDQibet8q/7UK4v4ZUN80atnZz1yg==
-----END CERTIFICATE-----

Command Line | openSSL

Alternatively, you can use the openssl command using the showcerts flag to display the certificate chain and copy the second certificate to clipboard (or redirect to file).

openssl s_client -connect google.com:443 -showcerts 2>&1 < /dev/null

Microsoft Windows

On Windows, you can use the Certificate utility and export as Base-64 encoded X.509 format. The extension (.cer or .pem) is not relevant but as shown above, the file should be text and not binary.

Create Certificate Store and Collection 

We need to create a certificate collection with purpose remote connection. For this we can either use the SAP HANA cockpit or the SAP HANA database explorer SQL Console.

Launch SAP HANA Cockpit from the SAP HANA Cloud instance card of the SAP Cloud Platform.

Certificate Store

Import the Base-64 certificate into the Certificate Store from the file or by copy/paste from clipboard.

Certificate Collection (PSE)

Navigate to the Certificate Collection app.

• Select Add Collection and create a new collection (corresponds to PSE). Use UPPERCASE
• Select Add Certificate and select the imported GTS-CA-101 certificate
• Select Edit Purpose and select REMOTE SOURCE

When a remote source is created, you can optionally restrict the collection for usage of selected remote sources.

Create Remote Source and Virtual Objects

Launch SAP HANA Database Explorer from the SAP HANA Cloud instance card of the SAP Cloud Platform.

Create Remote Source

Using the context menu (right-click) on the Remote Source entry of the Catalog, select Add Remote Source.

• Give the source a name
• Adapter name = BIGQUERY (REST API)
• Credentials Mode = Technical User
• Account name = client_email
• Private key = private_key

The service account and private key are taken from the service key in JSON format downloaded from the Google Cloud IAM.

Create Virtual Objects

Edit the remote source and select database (Google Project ID) and Schema (Dataset) for Search.

Select Create Virtual Objects to create virtual tables and views.

Select the virtual table to view metadata and data.

SQL

SAP HANA Database Explorer

Alternatively, you can use SQL to import the GTS-CA-101 certificate, create a collection (PSE), add the certificate, set the purpose, and create the remote source.

CREATE CERTIFICATE FROM
'-----BEGIN CERTIFICATE-----
MIIESjCCAzKgAwIBAgINAeO0mqGNiqmBJWlQuDANBgkqhkiG9w0BAQsFADBMMSAw
HgYDVQQLExdHbG9iYWxTaWduIFJvb3QgQ0EgLSBSMjETMBEGA1UEChMKR2xvYmFs
U2lnbjETMBEGA1UEAxMKR2xvYmFsU2lnbjAeFw0xNzA2MTUwMDAwNDJaFw0yMTEy
MTUwMDAwNDJaMEIxCzAJBgNVBAYTAlVTMR4wHAYDVQQKExVHb29nbGUgVHJ1c3Qg
U2VydmljZXMxEzARBgNVBAMTCkdUUyBDQSAxTzEwggEiMA0GCSqGSIb3DQEBAQUA
A4IBDwAwggEKAoIBAQDQGM9F1IvN05zkQO9+tN1pIRvJzzyOTHW5DzEZhD2ePCnv
UA0Qk28FgICfKqC9EksC4T2fWBYk/jCfC3R3VZMdS/dN4ZKCEPZRrAzDsiKUDzRr
mBBJ5wudgzndIMYcLe/RGGFl5yODIKgjEv/SJH/UL+dEaltN11BmsK+eQmMF++Ac
xGNhr59qM/9il71I2dN8FGfcddwuaej4bXhp0LcQBbjxMcI7JP0aM3T4I+DsaxmK
FsbjzaTNC9uzpFlgOIg7rR25xoynUxv8vNmkq7zdPGHXkxWY7oG9j+JkRyBABk7X
rJfoucBZEqFJJSPk7XA0LKW0Y3z5oz2D0c1tJKwHAgMBAAGjggEzMIIBLzAOBgNV
HQ8BAf8EBAMCAYYwHQYDVR0lBBYwFAYIKwYBBQUHAwEGCCsGAQUFBwMCMBIGA1Ud
EwEB/wQIMAYBAf8CAQAwHQYDVR0OBBYEFJjR+G4Q68+b7GCfGJAboOt9Cf0rMB8G
A1UdIwQYMBaAFJviB1dnHB7AagbeWbSaLd/cGYYuMDUGCCsGAQUFBwEBBCkwJzAl
BggrBgEFBQcwAYYZaHR0cDovL29jc3AucGtpLmdvb2cvZ3NyMjAyBgNVHR8EKzAp
MCegJaAjhiFodHRwOi8vY3JsLnBraS5nb29nL2dzcjIvZ3NyMi5jcmwwPwYDVR0g
BDgwNjA0BgZngQwBAgIwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly9wa2kuZ29vZy9y
ZXBvc2l0b3J5LzANBgkqhkiG9w0BAQsFAAOCAQEAGoA+Nnn78y6pRjd9XlQWNa7H
TgiZ/r3RNGkmUmYHPQq6Scti9PEajvwRT2iWTHQr02fesqOqBY2ETUwgZQ+lltoN
FvhsO9tvBCOIazpswWC9aJ9xju4tWDQH8NVU6YZZ/XteDSGU9YzJqPjY8q3MDxrz
mqepBCf5o8mw/wJ4a2G6xzUr6Fb6T8McDO22PLRL6u3M4Tzs3A2M1j6bykJYi8wW
IRdAvKLWZu/axBVbzYmqmwkm5zLSDW5nIAJbELCQCZwMH56t2Dvqofxs6BBcCFIZ
USpxu6x6td0V7SvJCCosirSmIatj/9dSSVDQibet8q/7UK4v4ZUN80atnZz1yg==
-----END CERTIFICATE-----'
COMMENT 'GTS-CA-101';

SELECT CERTIFICATE_ID FROM CERTIFICATES WHERE COMMENT = 'GTS-CA-101';

CREATE PSE GOOGLE;
ALTER PSE GOOGLE ADD CERTIFICATE <CERTIFICATE_ID>;
SET PSE GOOGLE PURPOSE REMOTE SOURCE;

--DROP REMOTE SOURCE BIGQUERY CASCADE;
CREATE REMOTE SOURCE BIGQUERY ADAPTER bigqueryrest
CONFIGURATION 'server=www.googleapis.com'
WITH CREDENTIAL TYPE 'OAUTH' USING 
'user=bigquery@<GCP Project ID>.iam.gserviceaccount.com;password=-----BEGIN PRIVATE KEY-----\n<string>\n-----END PRIVATE KEY-----\n';

CREATE SCHEMA BABYNAMES;
SET SCHEMA BABYNAMES;

CREATE VIRTUAL TABLE BABYNAMES.names_2014 
 at "BIGQUERY"."<GCP Project ID>"."babynames"."names_2014";

SELECT "name", "count" 
  FROM "BABYNAMES"."names_2014"
 WHERE "gender" = 'M'
 ORDER BY "count" DESC LIMIT 5

References

For another blog post on the topic see,

• Connect Google BigQuery to SAP HANA Cloud as a Remote Source by Neelesh Kumar Jain

SAP HANA Cloud Documentation

For the documentation, see the SAP Help Portal.

• Create a Google BigQuery Remote Source

About the Lighthouse

The lighthouse picture above from the SAP Image Library is from the Noodertoren on the island of Schiermonnikoog in the Netherlands. The island is a national park and known for its beautiful beaches.

• North Tower, Schiermonnikoog | Google Maps
• Visit Schiermonnik­oog

There is More Coming Up

SAP HANA Cloud

Together with Raja Prasad Gupta currently working to wrap up an e-book (e-bite) about SAP HANA Cloud, which should also be available soon.

When this is done, there will be a few more blogs coming up on C_HANATEC. Promised.

Share and Connect

Questions? Please post as comment.

Useful? Give us a like and share on social media.

Thanks!

If you would like to receive updates, connect with me on

• LinkedIn > linkedin.com/in/dvankempen
• Twitter > @dvankempen

For the author page of SAP PRESS, visit

• Denys van Kempen