Is there a way to pass credentials object to BigQueryOperator(s) in place of connectionId?

[Bikram Sisodia]

Our organization is using GCP service account keys. For security reasons we rotate the keys very often so one key file will not last more than few days. Also, key files are not directly accessible to individuals but only via a library that returns a 'credentials' object using most/valid key file.  This make is it difficult to use in BigQueryOperator or BigQueryToCloudStorageOperator as I can not create a connection in Airflow with fixed file path or fixed JSON. Is there a way to pass credentials object as a parameter to these operators? OR dynamically update a Airflow connection based on most recent service key file?

Is it possible something as shown below (myCredentials is of type google.oauth2.service_account.Credentials)?

myCredentials=fetchCredentialsFromVault(ServiceAccountKeyID)

export_to_gcs = BigQueryToCloudStorageOperator(

                        task_id='bq_gcs_task',

                        source_project_dataset_table= PROJECT_ID+'.'+schema+'.'+destination_table,

                        destination_cloud_storage_uris=destination_list,

                        #bigquery_conn_id='my-service-account-connection',

                        credentials=myCredentials,

                        dag=dag)

[Cedrik Neumann]

Hey, we also rotate our SA keys. To work around this, we have a daily DAG with PythonOperators which fetch the keys and update the corresponding GCP connections, storing the key in the keyfile dict.

 

conn.set_extra(json.dumps({

    'extra__google_cloud_platform__project': project,

    'extra__google_cloud_platform__keyfile_dict': str(base64.b64decode(key['privateKeyData']), 'utf-8'),

}))

-------------------------------------------------------

essenceglobal.com [essenceglobal.com]
Facebook [facebook.com] • Twitter [twitter.com] • YouTube [youtube.com] • Instagram [instagram.com]

--
You received this message because you are subscribed to the Google Groups "cloud-composer-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cloud-composer-di...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/cloud-composer-discuss/354d08e6-ed89-4dfe-9654-e22c09a6ebd9%40googlegroups.com [groups.google.com].

[Bikram Sisodia]

Cool! We will try to implement something on similar lines. Thanks for sharing.

To unsubscribe from this group and stop receiving emails from it, send an email to cloud-composer-discuss+unsub...@googlegroups.com.

[Bikram Sisodia]

Hi Cedrik,

I tried your solution but realized that it is not persisting the new data in 'extra' property of my connection. Next time when I am fetching the same connection, I am getting original value. Do you need to do something afterwards to persist in the database? If not, how do you use the newly updated 'extra' in your BigQueryOperator?

Thanks

Bikram Sisodia

--

Bikram Sisodia

Analytics Director

130 Sutter St • 7th Floor • San Francisco • CA • 94104

-------------------------------------------------------

essenceglobal.com
Facebook • Twitter • YouTube • Instagram

[Cedrik Neumann]

Hey Bikram,

 

Yes, Connection is a sqlalchemy object and you need to commit the changes at the end with `session.commit()`

 

I attached a full example.

 

PS: keep in mind the Web UI will display the field as empty either way and - even worse - will clear the field in the DB if you press save.

 

Best

Cedrik

[Bikram Sisodia]

Hi Cedrik,

Thanks for sharing complete example. It is working perfectly.

Best, 

Bikram Sisodia

essenceglobal.com
Facebook • Twitter • YouTube • Instagram