Creating a Google Big Query connection in SAP Datasphere (DWC)

[mellinda_pereira]

Scenario: We wanted to show how we can upload a csv file to Google cloud storage and then create a table based on it in Big Query and then import this table in SAP Datasphere via Import Remote tables

 

1) In GCP cloud storage we need to create a bucket

 

Give it a name

Next add a label (this is optional)

Choose where you want this bucket to be stored, I kept it as default what was selected

 

Choose storage class for your data (hot, cold, nearline) I kept it as standard

Choose how to control access on this bucket (like if you don’t want to make it public)

I uncheck the above highlighted since I just wanted to try if anyone can access this bucket.

Next is security on this bucket

I kept it as default.Next click Create

When you click on Buckets you see the bucket has been created.

Next, we will upload a .csv file to this bucket. (we can load any format file and then we need to write a script to convert any incoming file format to convert it into csv) this I will cover in later part.

I uploaded a simple .csv file

Now we will access this csv file from this cloud storage into Big Query

In the Big Query under explorer, I will create a dataset (this is like a schema where you will store all the tables) Click on Project

 

Next Create data set and give a proper business name

Data set (schema) is created. Now we will create a table via the cloud storage csv file.

Select the create table from Google cloud storage, Next select the bucket and then the file

Give table a name

 

Table is created and we can preview the data

 

 

Now we are going to create a connection in SAP Datasphere for Big Query.

In SAP Datasphere under connections click create and select Google BigQuery

 

We need to give Project name and Key (Access Key which we need to generate via API)

You will get the project id when you click on My First Project

Next for Key we will go to APIs and services from the navigational menu

Next click on Enabled APIs and services, scroll down you and click on BigQuery Connection API

 

Next click on credentials

Now you need to have a service account.

Let’s create a Service Account

Go to IAM and admin in Google console from the Navigational menu and click on Service accounts

At the top click on Create Service account

Give a relevant service account name

Click on create and you get a pop-up service account is created

Now we will see this service account In the BigQuery connection API

Click on it and go to Keys

Now click on Add key (recommended is JSON)

Click on Create, a key file will get downloaded. Store it securely since we will use it in the connection

Now under create connection in SAP Datasphere (DWC) Enter the project id and select the key document.

Click on Next step and give technical name

Validate the connection

Now go to Data Builder in Datasphere and click on Import Remote tables

 

Select the connection and then browse your project and select the table

 

 

And click Import and deploy and there it is the table is imported.

 

And finally, the table has been imported successfully in Datasphere. Further you can create Graphical view, Entity Relationship, Analytical model etc.

Hope this article was helpful.

[mellinda_pereira]

2 Important observation which I missed to mention in the above blog

1. You need to upload a google server certificate which you can download from https://www.google.com -> next click on the padlock and then connection is secure --> certificate is valid -->details -->export.
   
   


2. Upload the certificate in SAP Datashpere under System--> configuration-->security --> Add Certificate.


3. Next in IAM and Admin in GCP (Google Cloud Platform) for the service account that we have create we need to add BigQuery Admin role

Click on grant access --> select the service account --> in Roles --> BigQuery Admin

       Next Go to service account and generate the key (steps above)

[bpatel_clarivos]

Excellent blog Mellinda, very detailed. I have added the certificate in SAP Datasphere, however I get the following (attached) error when I go to validate my connection to Google BigQuery. I have exported the certificate as per the above comment. Any idea what I could have done incorrectly?

Validation Error

[ydwouter]

Hi Bhavin, Did you manage to solve this error? Could you provide me with some additional details? We currently face the same issue. Thx a lot for your help!

Ysaline

[staerk]

I got the same error and could fix it by making the service account "BigQuery Admin" as described in the previous comment.

[staerk]

Hi Mellinda,

your post was super-useful as it really shows the core of how to create a connection to BigQuery from your cool Datasphere 🙂

My remarks:

• the "guided experience" of SAP Datasphere is enough to follow your post. This is great, as it shows the power of Datasphere (and BigQuery) even to newcomers who just want to explore.
• I did deliberately NOT follow your advice to upload a Google server certificate and it works nevertheless.
• In a production environment, I would not make the service account "BigQuery Admin", but restrain it to the least needed privileges.
• If you have an organizational policy in place that forbids the creation of a service account key (constraints/iam.disableServiceAccountKeyCreation), you will get an error message when trying to create the service account key "The organization policy constraint 'iam.disableServiceAccountKeyCreation' is enforced." and a pointer to the solution:
  https://cloud.google.com/resource-manager/docs/organization-policy/restricting-service-accounts#disa...
• I really think there should be more blog posts like yours 🙂

[pgumbhir]

I am still getting the below error even after uploading google secure certificate in Datasphere & making the service account "BigQuery Admin".

Can someone please help if there is something else that needs to be done to fix this error?

[matthias_klocke]

Hi Melinda,

thanks for this blog which I found very useful while I was trying the same...

One small addition:
From the "validate" connection error message my user received I figured, that the service account actually needs two roles, which can be easiest assigned right in the beginning upon creation:

bigquery.jobUser

bigquery.admin

Maybe Google Cloud changed the role permissions or the Datasphere connection agent now requests different functions upon connection.

[AleGuarneri]

To fix the issue about drivers you need to upload Simba ODBC drivers in System->Configuration->Data Integration:

For the certificates, upload all the 3 Google certs (CA, Interm. and *google.com) in System->Configuration->Security:

Cheers

Alex