Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
shrgupta28 opened this issue Jun 4, 2020 · 13 comments · Fixed by #424
Closed

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

shrgupta28 opened this issue Jun 4, 2020 · 13 comments · Fixed by #424
Assignees
Labels
api: bigquery type: feature request

Comments

@shrgupta28
Copy link

@shrgupta28 shrgupta28 commented Jun 4, 2020

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
Copy link
Contributor

@pmakani pmakani commented Jun 4, 2020

@shrgupta28 samples on README please check on that repo.

@shrgupta28
Copy link
Author

@shrgupta28 shrgupta28 commented Jun 4, 2020

@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 stephaniewang526 transferred this issue from googleapis/google-api-java-client-services Jun 4, 2020
@product-auto-label product-auto-label bot added the api: bigquery label Jun 4, 2020
@stephaniewang526
Copy link
Member

@stephaniewang526 stephaniewang526 commented Jun 4, 2020

@yoshi-automation yoshi-automation added the triage me label Jun 5, 2020
@shrgupta28
Copy link
Author

@shrgupta28 shrgupta28 commented Jun 7, 2020

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
Copy link
Member

@stephaniewang526 stephaniewang526 commented Jun 8, 2020

PTAL sample linked above.

@shrgupta28
Copy link
Author

@shrgupta28 shrgupta28 commented Jun 9, 2020

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.

@pmakani pmakani reopened this Jun 9, 2020
@pmakani pmakani self-assigned this Jun 9, 2020
@pmakani pmakani added type: feature request and removed triage me labels Jun 9, 2020
@stephaniewang526 stephaniewang526 removed their assignment Jun 9, 2020
@shrgupta28
Copy link
Author

@shrgupta28 shrgupta28 commented Jun 9, 2020

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

gcf-merge-on-green bot pushed a commit that referenced this issue Jun 9, 2020
🤖 I have created a release \*beep\* \*boop\* 
---
### [1.116.2](https://www.github.com/googleapis/java-bigquery/compare/v1.116.1...v1.116.2) (2020-06-09)


### Documentation

* **samples:** add load CSV from GCS sample ([#426](https://www.github.com/googleapis/java-bigquery/issues/426)) ([3810366](https://www.github.com/googleapis/java-bigquery/commit/3810366451097a7f14db11504103865540ac242a))
* **samples:** add load CSV from GCS to overwrite table sample ([#428](https://www.github.com/googleapis/java-bigquery/issues/428)) ([21a3606](https://www.github.com/googleapis/java-bigquery/commit/21a3606f5fb65287f808b12a6fef65817c8a8ba6))
* **samples:** add update table using dml query sample ([#424](https://www.github.com/googleapis/java-bigquery/issues/424)) ([3902ba1](https://www.github.com/googleapis/java-bigquery/commit/3902ba1cf0d8a88d3e6f30b6606067503487c77d)), closes [#413](https://www.github.com/googleapis/java-bigquery/issues/413)
* **samples:** added copy table and accompanying test ([#414](https://www.github.com/googleapis/java-bigquery/issues/414)) ([de0d97f](https://www.github.com/googleapis/java-bigquery/commit/de0d97f2f940c9cf507d19c5595e1a0e819ef19c))
* **samples:** added extract to json and accompanying test ([#416](https://www.github.com/googleapis/java-bigquery/issues/416)) ([16a956d](https://www.github.com/googleapis/java-bigquery/commit/16a956db0aa545df84f7885ffb4425460cf55a16))
* **samples:** adding browse table sample and test ([#422](https://www.github.com/googleapis/java-bigquery/issues/422)) ([dff4e5f](https://www.github.com/googleapis/java-bigquery/commit/dff4e5f86764b1c779c2ef131182483e2ffa1c1b))
* **samples:** adding destination query sample and test ([#418](https://www.github.com/googleapis/java-bigquery/issues/418)) ([0f50961](https://www.github.com/googleapis/java-bigquery/commit/0f50961aaf1092f3ecc4e02fa9cebb50f6d45e90))
* **samples:** adding simple query example for completeness ([#417](https://www.github.com/googleapis/java-bigquery/issues/417)) ([59426df](https://www.github.com/googleapis/java-bigquery/commit/59426df912f743b7927deb562366b625aba6f087))
* **samples:** rename extract table json to extract table csv ([#415](https://www.github.com/googleapis/java-bigquery/issues/415)) ([c1f21e6](https://www.github.com/googleapis/java-bigquery/commit/c1f21e6c16df40bb3c71610f9d5b4fb4855b28fb))


### Dependencies

* update dependency com.google.apis:google-api-services-bigquery to v2-rev20200523-1.30.9 ([#409](https://www.github.com/googleapis/java-bigquery/issues/409)) ([d30c823](https://www.github.com/googleapis/java-bigquery/commit/d30c823c5a604b195f17d8ac33894107cdee967e))
---


This PR was generated with [Release Please](https://github.com/googleapis/release-please).
@pmakani
Copy link
Contributor

@pmakani pmakani commented Jun 10, 2020

@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
Copy link
Author

@shrgupta28 shrgupta28 commented Jun 10, 2020

@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
Copy link
Contributor

@pmakani pmakani commented Jun 10, 2020

@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
Copy link
Author

@shrgupta28 shrgupta28 commented Jun 10, 2020

@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
Copy link
Author

@shrgupta28 shrgupta28 commented Jun 11, 2020

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

@stephaniewang526
Copy link
Member

@stephaniewang526 stephaniewang526 commented Jun 11, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
api: bigquery type: feature request
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants