Can you help me the JAVA API to update the table rows using google cloud bigquery

[shrgupta28]

Hi Team,

We are doing the POC with the new Bigquery API https://github.com/googleapis/java-bigquery

Can you please help me how I can update the table rows using this.

Env:
Java 1.8
Google Cloud Bigquery

Thanks,
Shruti

[pmakani]

@shrgupta28 samples on README please check on that repo.

[shrgupta28]

@pmakani There is no sample for updating the table content.
There are samples to update the dataset structure and table structure.
I am looking for the code using which I can perform update operation on the table data, like updating the record.
I basically need to execute the below operation using the API:
update dataset.Student set Stud_Name = "NewName11", Stud_Class="II" where Stud_Id = 2002

can you help me with this, please.

Thanks.

[stephaniewang526]

we will add samples. meanwhile, PTAL https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language

[shrgupta28]

Hi @stephaniewang526 , Any update on how we can perform the Update Operation using the cloud bigquery API.
We are in the final development phase, and this is blocking us. I request your help. Thanks.

[stephaniewang526]

PTAL sample linked above.

[shrgupta28]

Hi @stephaniewang526
The sample UpdateTableDML.java that you provided updates the data in the table..
But is there an API that tells me the number of rows updated in the DB..

While using the BigQuery V2 API, I was able to trace the number of rows updated using the queryResponse.getNumDmlAffectedRows()

Can you tell me the similar API here.. We need to know the number of Affected Rows.

Also, in case of any errors, the BigQuery V2 API used to return the queryResponse.getErrors()
Can you help with this as well.

Thanks.

[shrgupta28]

Hi @stephaniewang526 @pmakani Can you help me with the requirement stated above.

[pmakani]

@shrgupta28 when you are executing query job, it's return QueryStatistics, then you will able to get
getNumDmlAffectedRows(), please take a look below example.

JobStatistics.QueryStatistics statistics = job.getStatistics();
long numDmlAffectedRows = statistics.getNumDmlAffectedRows();

[shrgupta28]

@pmakani PFA the sample I am using to update the DML. I am not able to work with this API here in my code. Can you please help me.. Thanks.
SampleUpdateDML.txt

[pmakani]

@shrgupta28 please use this example.

    String tableName = "test_table";
    String dataset = "test_dataset";
    String query = "SELECT TimestampField, StringField, BooleanField FROM test_dataset.test_table";
    TableId destinationTable = TableId.of(dataset, tableName);
    QueryJobConfiguration configuration =
        QueryJobConfiguration.newBuilder(query)
            .setDefaultDataset(DatasetId.of(DATASET))
            .setDestinationTable(destinationTable)
            .build();
    Job job = bigquery.create(JobInfo.of(configuration));
    JobStatistics.QueryStatistics statistics = job.getStatistics();
    long numDmlAffectedRows = statistics.getNumDmlAffectedRows();

[shrgupta28]

@pmakani The code snippet you have shared above is for Select statement. I am trying to perform the update operation. I am trying to do something similar as you said. Please check the below code - Does this seem okay? Still, I am not getting the statistics.getNumDmlAffectedRows(); It is null every time.

String dmlQuery = String.format("update dataset.Student  set Stud_Name = \"Nalini\", Stud_Class=\"II\" where Stud_Id = 300", datasetName, tableName);

			QueryJobConfiguration configuration =
					QueryJobConfiguration.newBuilder(dmlQuery)
					.build();
			Job job = bigquery.create(JobInfo.of(configuration));
			JobStatistics.QueryStatistics statistics = job.getStatistics();
			TableResult result = bigquery.query(configuration);
			System.out.println(statistics.getNumDmlAffectedRows().intValue());

[shrgupta28]

Hi, @pmakani Could you please help with this. This is at a high priority from our development perspective.

[stephaniewang526]

Please use Github to raise bug report or feature request only.