Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
dvankempen
Product and Topic Expert
Product and Topic Expert










In this blog series you will find hands-on information about creating SAP HANA smart data access remote sources.

In this blog post we cover Google BigQuery.

Questions? Post as comment.

Useful? Give a like and share on social media. Thanks!



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

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

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.

Dataset


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

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).

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).




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,

SAP HANA Cloud Documentation


For the documentation, see the SAP Help Portal.


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.



There is More Coming Up


SAP HANA Cloud


Together with rajaprasad.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

For the author page of SAP PRESS, visit








Over the years, for the SAP HANA Academy, SAP’s Partner Innovation Lab, and à titre personnel, I have written a little over 300 posts here for the SAP Community. Some articles only reached a few readers. Others attracted quite a few more.

For your reading pleasure and convenience, here is a curated list of posts which somehow managed to pass the 10k-view mile stone and, as sign of current interest, still tickle the counters each month.


2 Comments