QR: Big Query - Writing to a different project than the credentials

[Jon_Donker]

Hi Team,

We're trying to get a Qlik Replicator connector up to write to Big Query.

Our SA credentials that QR exists in project_x; while the dataset which we're trying to write to exists in project_y.

The SA have the correct permissions to write to project_y.

How can I configure the task and endpoint to write to project_y when by default it is trying to write to a dataset in project_x?

[melwin62carl]

To write into a dataset in project_y, you need to explicitly override that default behavior in the target endpoint configuration. In the BigQuery target endpoint settings within Qlik Replicate,  changehealthcare specify the Project ID (project_y) along with the correct dataset name. This ensures that the connector directs writes to the intended project rather than relying on the service account’s home project.

[john_wang]

Hello @Jon_Donker ,

We may be able to try a workaround. Could you please confirm the platform where Replicate is running? The approach differs between Windows and Linux.

Regards,

John.

[Jon_Donker]

QR is running from a Windows machine

[john_wang · Accepted solution]

Hello @Jon_Donker ,

Please add an internal parameter in target EndPoint
$info.query_syntax.load_data_exe_params

set its value to
--project_id <MyProjID> --dataset_id ${TABLE_OWNER} load ${CA_PATH} --allow_quoted_newlines=false --format=csv --null_marker=\"attNULL\" \"${TABLE_NAME}\" \"${FILENAME}\"

where <MyProjID> is your desired project ID.

Hope this helps.

John.

[Jon_Donker]

Cheers - I'll give it a try

[Jon_Donker · Accepted solution]

Hi - after a bit of trial and error; got it to work.

This is our (mocked up) credentials that I downloaded from project_x's secret manager for QR to use to connect to BQ:

{
  "type": "service_account",
  "project_id": "project_x",
  "private_key_id": "1234567890abcdef",
  "private_key": "-----BEGIN PRIVATE KEY-----\nLongPrivateKey\n-----END PRIVATE KEY-----\n",
  "client_email": "myaccount@project_x.iam.gserviceaccount.com",
  "client_id": "123456789012345678900",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/myaccount%40project_x.iam.gserviceaccount.com",
  "universe_domain": "googleapis.com"
}

By modifying project_id in the connect string; I could force it to use "project_x" instead of "project_x".

Also as a side note I had to manually add the project and schema to the control table:

[john_wang]

Perfect! Thank you so much for your outstanding support! @Jon_Donker