Using Private Service Connect with BigQuery Writer

This topic describes how to configure BigQuery Writer to reach BigQuery through a Private Service Connect endpoint.

Private Service Connect keeps traffic to BigQuery on the Google network instead of the public internet. BigQuery Writer supports Private Service Connect with all of its write methods, including the Storage Write API, which is the highest-throughput method the writer supports. You do not have to choose between write performance and private connectivity.

Required configuration

Set the following BigQuery Writer property to route traffic through a Private Service Connect endpoint.

Property	Value
Private Service Connect Endpoint	The name of an existing Private Service Connect endpoint, or the full DNS name of the endpoint for the service.

Note

Google Cloud does not allow special characters in the endpoint name for Google-managed services, so do not use hyphens in a Private Service Connect endpoint name.

No other property changes are required. The endpoint applies to whichever write method the target is configured to use.

Write methods over Private Service Connect

The write method is controlled by the Streaming Upload and Streaming Configuration properties, and is independent of the Private Service Connect configuration.

Write method	Property settings
Load	Set Streaming Upload to `False`.
Legacy streaming API	Set Streaming Upload to `True` and include `UseLegacyStreamingAPI=true` in Streaming Configuration.
Storage Write API	Set Streaming Upload to `True` and include `UseLegacyStreamingAPI=false` in Streaming Configuration.

How Striim authenticates Storage Write API connections over Private Service Connect

When you configure a Private Service Connect endpoint, BigQuery Writer routes traffic through the custom endpoint hostname, and Striim requests credentials that are explicitly scoped to the BigQuery API.

Scoping the credentials this way means that Google issues a standard OAuth2 access token that the Storage Write API accepts, regardless of which endpoint hostname carries the traffic. Striim applies this automatically. No property change is required, and the behavior does not affect the load or legacy streaming methods.

Sample application

The following target writes to BigQuery through the Storage Write API using the Private Service Connect endpoint bqpscendpoint.

CREATE OR REPLACE TARGET BQ_PSC_Target USING BigQueryWriter (
  serviceAccountKey: '/opt/striim/keys/my-service-account.json',
  projectId: 'my-gcp-project',
  PrivateServiceConnectEndpoint: 'bqpscendpoint',
  streamingUpload: 'true',
  StreamingConfiguration: 'MaxRequestSizeInMB=5, MaxParallelRequests=10,
    ApplicationCreatedStreamMode=None,
    UseLegacyStreamingAPI=false',
  Tables: 'myschema.mytable',
  Mode: 'MERGE',
  BatchPolicy: 'eventCount:10000, Interval:30',
  NullMarker: 'NULL',
  ColumnDelimiter: '|',
  ConnectionRetryPolicy: 'retryInterval=30, maxRetries=3'
) INPUT FROM MyStream;

To use a different write method, change the streamingUpload and UseLegacyStreamingAPI settings as described above. The PrivateServiceConnectEndpoint setting is the same in all cases.

Verifying that traffic uses the private endpoint

Confirm from the host running the Striim application that the BigQuery DNS names resolve to the internal IP address of your Private Service Connect endpoint.

BigQuery operations use the bigquery service name. The Storage Write API additionally uses the bigquerystorage service name, so check both names when the target uses that method.

nslookup bigquery-<psc-endpoint-name>.p.googleapis.com
nslookup bigquerystorage-<psc-endpoint-name>.p.googleapis.com

Each name you check must resolve to the internal IP address that you reserved for the endpoint. If a name resolves to a public address, traffic for that service does not use the private endpoint.

Troubleshooting

The following symptom indicates that BigQuery rejected the credentials used for the connection.

Symptom: the application terminates and the exception reports UNAUTHENTICATED for the BigQuery Writer target.

Resolution:

Verify that the service account has the permissions required to write to the target tables.
Verify that the BigQuery DNS names resolve to the endpoint's internal IP address, as described above.
Verify that the endpoint status in Google Cloud is Accepted.