Connect to SAP Datasphere

As a BigQuery administrator, you can create a connection to access SAP Datasphere data. This connection enables data analysts to query data in SAP Datasphere.

Before you begin

Enable the BigQuery Connection API.

Enable the API
Create a SAP Datasphere database user. Note the username, password, hostname, and port for BigQuery to connect to.
Configure your SAP Datasphere tenant to accept traffic from your selected IP addresses by doing one of the following:
- Add all Google IP address ranges to the "Trusted IPs" allowlist in SAP Datasphere.
- Open the SAP Datasphere tenant to connections from all IP addresses by adding 0.0.0.0/0 to the allowlist.
- Configure your connection with network attachments so that BigQuery opens the connection from a static IP address.
  
  Note: If your configured network attachment and VM are located in different regions, there might be cross-region data movement when you use this connection to query SAP Datasphere data.
For more information about configuring your SAP Datasphere tenant, see Add IP address to IP Allowlist.

Required roles

To get the permissions that you need to connect to SAP Datasphere, ask your administrator to grant you the following IAM roles:

To create a connection: BigQuery Connection Admin (roles/bigquery.connectionAdmin) on the project
To configure network attachments: Compute Admin (roles/compute.admin) on the project

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Configure a network attachment

Because federated queries establish a direct connection to your database using the BigQuery Connection API, you must allow traffic from Google Cloud to your database engine. To increase security, you should only allow traffic that comes from your BigQuery queries. This traffic restriction can be accomplished in one of two ways:

By defining a static IP address that is used by a BigQuery connection and adding it to the firewall rules of the external data source.
By creating a VPN between BigQuery and your internal infrastructure, and using it for your queries.

Both of these techniques are supported through the use of network attachments.

Limitations

Connections with network attachments are subject to the following limitations:

For standard regions, network attachments must be located in the same region as the connection. For connections in the US multi-region, the network attachment must be located in the us-central1 region. For connections in the EU multi-region, the network attachment must be located in the europe-west4 region.
You can't edit your network attachment after you create it. To change your configuration, you need to recreate the network attachment.
Network attachments can't be deleted unless BigQuery deletes the allocated resources. To initiate the deletion process, contact BigQuery support.

Create a network attachment

When you create a connection for query federation, you can specify an optional network attachment that provides connectivity to your database's network. You can create a network attachment by either defining a static IP address or creating a VPN. For either option, do the following:

If you don't already have one, create a VPC network and subnet.
Depending on your chosen routing method, do one of the following:
- For a static IP address: Create a Cloud NAT gateway with a static IP address, using the network, region, and subnet that you created.
- For a VPN: Create a VPN that is connected to your private network.
Create a network attachment using the network, region, and subnet that you created.
Optional: Depending on your organization's security policies, you might need to configure your Google Cloud firewall to allow egress by creating a firewall rule with the following settings:
- Set Targets to All instances in the network.
- Set Destination IPv4 ranges to the entire IP address range.
- Set Specified protocols and ports to the port that is used by your database.
Configure your internal firewall to allow ingress from the static IP address that you created. This process varies by data source.

Connect BigQuery to SAP Datasphere

You can connect BigQuery to SAP Datasphere in the Google Cloud console or the bq command-line tool.

Console

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery
In the Explorer pane, click Add data.

The Add data dialog opens.
In the Filter By pane, in the Data Source Type section, select Databases.

Alternatively, in the Search for data sources field, you can enter SAP HANA.
In the Featured data sources section, click SAP HANA.
Click the SAP HANA: BigQuery Federation solution card.
In the External data source dialog, do the following:
- For Connection type, select SAP HANA.
- For Connection ID, enter a connection ID to identify this connection.
- For Location type, specify a region of the BigQuery dataset to be combined with the data from SAP Datasphere. Queries that use this connection must be run from this region.
- Optional: For Friendly name, enter a user-friendly name for the connection, such as My connection resource. The friendly name can be any value that helps you identify the connection resource if you need to modify it later.
- Optional: For Description, enter a description for this connection resource.
- For Encryption, select either Google-managed encryption key or Customer-managed encryption key (CMEK). The use of a CMEK is optional.
- For Host:port: enter the host and port of the SAP database instance, as shown in the Database User Details in the SAP Datasphere web console, in the format HOST:PORT.
- Optional: For Network attachment, enter a path to the network attachment that defines the network configuration that is used for establishing a connection to SAP Datasphere.
- For Username: enter the database username from Database User Details in the SAP Datasphere web console. For example, MY_SPACE#BIGQUERY.
- For Password: enter the database user's password.
Click Create connection.

bq

Enter the bq mk command with the following flags:

  bq mk \
  --connection \
  --location=LOCATION \
  --project_id=PROJECT_ID \
  --connector_configuration '{
    "connector_id": "saphana",
    "endpoint": {
      "host_port": "HOST_PORT"
    },
    "authentication": {
      "username_password": {
        "username": "USERNAME",
        "password": {
          "plaintext": "PASSWORD"
        }
      }
    },
    "network": {
      "private_service_connect": {
        "network_attachment": "NETWORK_ATTACHMENT"
      }
    }
  }' \
  CONNECTION_ID

Replace the following:

LOCATION: specify a region of the BigQuery dataset to be combined with the data from SAP Datasphere. Queries that use this connection must be run from this region.
PROJECT_ID: enter your Google Cloud project ID.
HOST_PORT: enter the host and port of the SAP database instance, as shown in the Database User Details in the SAP Datasphere web console, in the format HOST:PORT.
NETWORK_ATTACHMENT (optional): enter the network attachment in the format projects/{project}/regions/{region}/networkAttachments/{networkattachment}. With this field, you can configure the SAP Datasphere connection so that BigQuery opens the connection from a static IP address.
USERNAME: enter the database username from Database User Details in the SAP Datasphere web console. For example, MY_SPACE#BIGQUERY.
PASSWORD: enter the database user's password.
CONNECTION_ID: enter a connection ID to identify this connection.

Optional flag:

--kms_key_name: A customer-managed encryption key. If omitted, credentials are protected by the default Google-owned and Google-managed encryption key.

Note: If you configured your connection to use a network attachment, you must run an initial federated query to synchronize your project with the network attachment before it becomes fully active.

Share connections with users

You can grant the following roles to let users query data and manage connections:

roles/bigquery.connectionUser: enables users to use connections to connect with external data sources and run queries on them.
roles/bigquery.connectionAdmin: enables users to manage connections.

For more information about IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Select one of the following options:

Console

Go to the BigQuery page.

Go to BigQuery

Connections are listed in your project, in a group called Connections.
In the left pane, click Explorer:

If you don't see the left pane, click Expand left pane to open the pane.
Click your project, click Connections, and then select a connection.
In the Details pane, click Share to share a connection. Then do the following:
1. In the Connection permissions dialog, share the connection with other principals by adding or editing principals.
2. Click Save.

bq

You cannot share a connection with the bq command-line tool. To share a connection, use the Google Cloud console or the BigQuery Connections API method to share a connection.

API

Use the projects.locations.connections.setIAM method in the BigQuery Connections REST API reference section, and supply an instance of the policy resource.

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import com.google.api.resourcenames.ResourceName;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import com.google.iam.v1.Binding;
import com.google.iam.v1.Policy;
import com.google.iam.v1.SetIamPolicyRequest;
import java.io.IOException;

// Sample to share connections
public class ShareConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    shareConnection(projectId, location, connectionId);
  }

  static void shareConnection(String projectId, String location, String connectionId)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ResourceName resource = ConnectionName.of(projectId, location, connectionId);
      Binding binding =
          Binding.newBuilder()
              .addMembers("group:example-analyst-group@google.com")
              .setRole("roles/bigquery.connectionUser")
              .build();
      Policy policy = Policy.newBuilder().addBindings(binding).build();
      SetIamPolicyRequest request =
          SetIamPolicyRequest.newBuilder()
              .setResource(resource.toString())
              .setPolicy(policy)
              .build();
      client.setIamPolicy(request);
      System.out.println("Connection shared successfully");
    }
  }
}

Pricing

Standard federated query pricing applies.
Using VPC is subject to Virtual Private Cloud pricing.
Using Cloud VPN is subject to Cloud VPN pricing.
Using Cloud NAT is subject to Cloud NAT pricing.

What's next

Learn about different connection types.
Learn about managing connections.
Learn about federated queries.
Learn how to query SAP Datasphere data.