3

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

 10 months ago
source link: https://blogs.sap.com/2023/05/25/creating-a-google-big-query-connection-in-sap-datasphere-dwc/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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

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

gcp.jpg

Give it a name

Next add a label (this is optional)

gcp1.jpg

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

gcp2.jpg

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

gcp3.jpg

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

gcp4-1.jpg

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

Next is security on this bucket

gcp5.jpg
I kept it as default.Next click Create

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

gcp6-1.jpg
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

gcp7.jpg

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

bigquery.jpg

Next Create data set and give a proper business name

bigquery1-2.jpg
bigquery2.jpg
bigquery3.jpg

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

bigquery4.jpg

Give table a name

bigquery5.jpg

Table is created and we can preview the data

bigquery6.jpg
bigquery7.jpg

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

datasphere.jpg
datasphere1.jpg

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

datasphere2.jpg
Next for Key we will go to APIs and services from the navigational menu
datasphere3.jpg

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

datasphere4.jpg

Next click on credentials

datasphere5.jpg

Now you need to have a service account.

datasphere6-1.jpg

Let’s create a Service Account

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

serviceaccnt.jpg

At the top click on Create Service account

serviceaccnt1.jpg

Give a relevant service account name

serviceaccnt2.jpg

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

serviceaccnt3.jpg

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

serviceaccount4.jpg

Click on it and go to Keys

serviceaccount5.jpg

Now click on Add key (recommended is JSON)

accesskey.jpg

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

accesskey1.jpg

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

datasphereconn.jpg

Click on Next step and give technical name

datasphereconn1.jpg

Validate the connection

datasphereconn2.jpg

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

importtable.jpg

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

importtable1.jpg

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

importtable2.jpg

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.


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK