Insert data into BigQuery using native Insert query using Java

I Insert rows into BigQuery with the InsertAll method using JAVA. It is working always fine. But when we try to update the same row from the JAVA code am getting the below error,

> com.google.cloud.bigquery.BigQueryException UPDATE or DELETE DML statements over table project123:mydataset.test would affect rows in the streaming buffer, which is not supported

So I tried from BigQueryConsole.

I inserted a row using the INSERT query then immediately UPDATE the same row. It worked fine.

When I read the articles of BIGQUERY, they are mentioning both InsertAll from JAVA and INSERT query from Console using Streaming Buffer. In that case, the console query execution should be got failed.

Why Console query is working fine? But from Java InsertAll it is throwing me an exception.

It will be really helpful if anyone helps me to know the exact details.

If any suggestions to use Native insert query insertion from Java instead of InsertAll to BigQuery, It will be a great help.

Please find the code snippet

First am inserting the values to the BigQuery using the below code snippet

Map<String, Object> map = new HashMap<>();
map.put("1", "name");
map.put("2", "age");

BigQuery bQuery = BigQueryOptions.newBuilder().setCredentials(credentials).setProjectId(id)
			.build().getService();
InsertAllResponse response = bQuery .insertAll(InsertAllRequest.newBuilder(tableId).addRow(map).build());

Once it is getting inserted, am trying to update the row in that table with the following code snippet

String updateQuery = String.format( "UPDATE `%s` SET name = \"%s\" WHERE age = \"%s\")", name, age);
QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();
bQuery.query(queryConfig);

Insert is working fine. when I tried to update the same inserted row am getting the streaming buffer error.

Thanks in advance.

When you read the documentation, it's clear that the insertAll perform a stream write into BigQuery.

When you use INSERT DML (INSERT INTO <table> [VALUES....|SELECT...]), you perform a query, not a stream write. So, the data management isn't the same. The performance are also different (Stream write can write up to 1 million of rows per seconds, the DML is query by query, and took more time for less data).

So, I don't know your code and what you want to achieve. But if you want to use usual query (INSERT, UPDATE, DELETE), use query API.

EDIT

I tried to adapt your code (but it was wrong, I took some assumptions) and I can propose you this. Simply perform a QUERY, not a Load Job or a Streaming write.

        String tableName = "YOUR_TABLE_NAME";
        
        String insertQuery = String.format("INSERT INTO %s(name, age) VALUES (1,2)", tableName);
        QueryRequest queryRequest = QueryRequest.builder(insertQuery).build();
        bQuery.query(queryRequest);


        String updateQuery = String.format( "UPDATE `%s` SET name = \"%s\" WHERE age = \"%s\")", tableName, name, age);
        queryRequest = QueryRequest.builder(updateQuery).build();
        bQuery.query(queryRequest);