Google BigQuery to SAP Data Warehouse Cloud DWC
Clients who have an investment in Google Big Query and SAP related technologies (including ECC, SOH, BW, BW/4HANA, S/4HANA, SuccessFactors, Ariba and FieldGlass as some examples) can easily integrate the datasets, while not having to duplicate the datasets further by leaving them in their respective environments.
The SAP Data Warehouse Cloud Big Query connection type provides the ability to link BigQuery Datasets to SAP technologies and to provide a virtualisation layer that can blend the data sets to provide extra business insights across the different systems.
The integration with SAP technologies in Data Warehouse Cloud is well tested and proven, this blog will show the extra steps in integrating Google Big Query in with Data Warehouse Cloud.
Google Big Query setup requires the following;
An account with tables loaded /accessible:
I have uploaded 2 tables, planning and sales to the AUSTRALIA dataset in the project sap-anz-dto. Nothing special, just csv files from a previous demo.
External Access also requires a Service Account:
A Google Cloud certificate is also required in Data Warehouse Cloud to connect to the Google BigQuery landscape:
- GSR2 : Located at: https://pki.goog/repository/, under the section “Download CA certificates”, expand “Root CAs”, for “gsr2” first entry, click action button at the right, click “Certificate (.PEM)”, which should download the gs2 pem certificate.
Within the Space Connections, add the BigQuery Connection Type:
Entering the requirements values:
From the BigQuery Service Account:
Test the Connection. If you have any issues – post them below. I had a few certificate issues on the way to getting the above working.
Finally, you should be able to see the big query connection tables:
And then view the data:
Next step is to test some larger datasets and test the different virtualisation / replication options…
Pretty easy in the end really.
Thanks a lot for this blog. This is exactly what I am looking for. Yes I do have an issue.
I downloaded the certificate pem file exactly like you described.
When trying to upload the certificate pem file I get a message "Error, Certificate could not be uploaded". No further hint.
The certificate file looks like described here https://help.sap.com/viewer/9f804b8efa8043539289f42f372c4862/cloud/en-US/46f5467adc5242deb1f6b68083e72994.html
namely "-----BEGIN CERTIFICATE-----" and "-----END CERTIFICATE-----".
Any idea what I am doing wrong?
I assume you are having issues with the google gsr2 pem cert? from https://pki.goog/repo/certs/gsr2.pem
That is the file you used?
meanwhile we found out that there is a problem with technical user "User dis-user does not have the required privileges"
Our system admin takes care of this.
HI Klaus, Jon,
One remark for future people that run into the same issue (we did last week) and land here.
This error is something that can only be fixed in the back-end of DI.
You will need an OSS ticket to get it fixed.
meanwhile the issue is fixed, certificate can be uploaded and connection can be established. Hoewever I get this message
And indeed. It works fine in view building. Connection does not show up in data flow. Do you know why?
Hi Klaus Freyburger ,
Good progress. Yes Google Big Query is not supported as a direct Data Flow option as yet. But as a workaround;
Hope that helps
thanks for your reply. Maybe you are interested in the workaround I found in the meantime.
This works fine without an extra view. The essential hint came from your blog. So thanks again!
Best regards Klaus