importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.FormatOptions;importcom.google.cloud.bigquery.Job;importcom.google.cloud.bigquery.JobId;importcom.google.cloud.bigquery.QueryJobConfiguration;importcom.google.cloud.bigquery.TableDataWriteChannel;importcom.google.cloud.bigquery.TableId;importcom.google.cloud.bigquery.TableResult;importcom.google.cloud.bigquery.WriteChannelConfiguration;importjava.io.IOException;importjava.io.OutputStream;importjava.nio.channels.Channels;importjava.nio.file.FileSystems;importjava.nio.file.Files;importjava.nio.file.Path;importjava.util.UUID;// Sample to update data in BigQuery tables using DML querypublicclassUpdateTableDml{publicstaticvoidmain(String[]args)throwsIOException,InterruptedException{// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";updateTableDml(datasetName,tableName);}publicstaticvoidupdateTableDml(StringdatasetName,StringtableName)throwsIOException,InterruptedException{try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();// Load JSON file into UserSessions tableTableIdtableId=TableId.of(datasetName,tableName);WriteChannelConfigurationwriteChannelConfiguration=WriteChannelConfiguration.newBuilder(tableId).setFormatOptions(FormatOptions.json()).build();// Imports a local JSON file into a table.PathjsonPath=FileSystems.getDefault().getPath("src/test/resources","userSessionsData.json");// The location and JobName must be specified; other fields can be auto-detected.StringjobName="jobId_"+UUID.randomUUID().toString();JobIdjobId=JobId.newBuilder().setLocation("us").setJob(jobName).build();try(TableDataWriteChannelwriter=bigquery.writer(jobId,writeChannelConfiguration);OutputStreamstream=Channels.newOutputStream(writer)){Files.copy(jsonPath,stream);}// Get the Job created by the TableDataWriteChannel and wait for it to complete.Jobjob=bigquery.getJob(jobId);JobcompletedJob=job.waitFor();if(completedJob==null){System.out.println("Job not executed since it no longer exists.");return;}elseif(completedJob.getStatus().getError()!=null){System.out.println("BigQuery was unable to load local file to the table due to an error: \n"+job.getStatus().getError());return;}System.out.println(job.getStatistics().toString()+" userSessionsData json uploaded successfully");// Write a DML query to modify UserSessions table// To create DML query job to mask the last octet in every row's ip_address columnStringdmlQuery=String.format("UPDATE `%s.%s` \n"+"SET ip_address = REGEXP_REPLACE(ip_address, r\"(\\.[0-9]+)$\", \".0\")\n"+"WHERE TRUE",datasetName,tableName);QueryJobConfigurationdmlQueryConfig=QueryJobConfiguration.newBuilder(dmlQuery).build();// Execute the query.TableResultresult=bigquery.query(dmlQueryConfig);// Print the results.result.iterateAll().forEach(rows->rows.forEach(row->System.out.println(row.getValue())));System.out.println("Table updated successfully using DML");}catch(BigQueryExceptione){System.out.println("Table update failed \n"+e.toString());}}}

importpathlibfromtypingimportDict,Optionalfromgoogle.cloudimportbigqueryfromgoogle.cloud.bigqueryimportenumsdefload_from_newline_delimited_json(client:bigquery.Client,filepath:pathlib.Path,project_id:str,dataset_id:str,table_id:str,)->None:full_table_id=f"{project_id}.{dataset_id}.{table_id}"job_config=bigquery.LoadJobConfig()job_config.source_format=enums.SourceFormat.NEWLINE_DELIMITED_JSONjob_config.schema=[bigquery.SchemaField("id",enums.SqlTypeNames.STRING),bigquery.SchemaField("user_id",enums.SqlTypeNames.INTEGER),bigquery.SchemaField("login_time",enums.SqlTypeNames.TIMESTAMP),bigquery.SchemaField("logout_time",enums.SqlTypeNames.TIMESTAMP),bigquery.SchemaField("ip_address",enums.SqlTypeNames.STRING),]withopen(filepath,"rb")asjson_file:load_job=client.load_table_from_file(json_file,full_table_id,job_config=job_config)# Wait for load job to finish.load_job.result()defupdate_with_dml(client:bigquery.Client,project_id:str,dataset_id:str,table_id:str)->int:query_text=f"""    UPDATE `{project_id}.{dataset_id}.{table_id}`    SET ip_address = REGEXP_REPLACE(ip_address, r"(\\.[0-9]+)$", ".0")    WHERE TRUE    """query_job=client.query(query_text)# Wait for query job to finish.query_job.result()assertquery_job.num_dml_affected_rowsisnotNoneprint(f"DML query modified{query_job.num_dml_affected_rows} rows.")returnquery_job.num_dml_affected_rowsdefrun_sample(override_values:Optional[Dict[str,str]]=None)->int:ifoverride_valuesisNone:override_values={}client=bigquery.Client()filepath=pathlib.Path(__file__).parent/"user_sessions_data.json"project_id=client.projectdataset_id="sample_db"table_id="UserSessions"load_from_newline_delimited_json(client,filepath,project_id,dataset_id,table_id)returnupdate_with_dml(client,project_id,dataset_id,table_id)