How to append data from a DataFrame to BigQuery without needing a "bigquery.table.create" role

Google Cloud Data Analytics
1

Hello Everyone!!

I am facing a issue in a solution that I am developing, let me explain my scenario.

I need to append in an existing table information from a csv, I created a cloud function triggered by eventarc which runs when a Object is finalized in a storage bucket.

The problem is that when attempting to append the new data, an error occurs stating that my service account needs the `bigquery.table.create` role/permission on the dataset. However, in my company, in production environment, that role is prohibited because It is considered as a high-level permission.

I have already tried using load_job and pandas.togbq. Unfortunately, in both cases, the error is the exact same.

Does anyone have any suggestions or alternative solutions to bypass or solve this issue? Any advice would be greatly appreciated!

Thanks

2

Hi @Julio_Cesar_Cruz_Con ,
Can you check if this works ? So basically CreateDisposition.CREATE_NEVER skipps that permission

from google.cloud import bigquery

def load_csv_to_bq(event, context):
    client = bigquery.Client()
    
    # Construct the GCS URI from the Eventarc payload
    bucket_name = event['bucket']
    file_name = event['name']
    uri = f"gs://{bucket_name}/{file_name}"
    
    table_id = "your_project.your_dataset.your_table"

    job_config = bigquery.LoadJobConfig(
        write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
        create_disposition=bigquery.CreateDisposition.CREATE_NEVER, # The crucial fix
        source_format=bigquery.SourceFormat.CSV,
        skip_leading_rows=1, # Assuming your CSV has a header
    )

    # Start the load job
    load_job = client.load_table_from_uri(uri, table_id, job_config=job_config)
    
    # Wait for the job to complete
    load_job.result()
    print(f"Loaded {load_job.output_rows} rows into {table_id}.")