CODEWITHSUNDEEP
javasqlgoogle-bigquerysql-delete
Mohamed Haydar
Mohamed Haydar

Reputation: 423

Why UPDATE or DELETE statement over table would affect rows in the streaming buffer, is not supported and how to fix it?

I'm writing data has read from datastore to bigquery using cloud function will that run every 1 hour, but I need to delete all rows before rewriting which is not allowed because of buffering:

BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
            //Table table = bigquery.getTable(TableId.of(DATASET, TABLE_NAME));
            if (bigquery.getTable(TableId.of(DATASET, TABLE_NAME)) != null) {
                QueryJobConfiguration queryConfig =
                        QueryJobConfiguration.newBuilder(
                                        "DELETE FROM `" + DATASET + "." + TABLE_NAME + "` WHERE true;")
                                .build();
                JobId jobId = JobId.of(UUID.randomUUID().toString());
                Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());
                queryJob = queryJob.waitFor();
                // Check for errors
                if (queryJob == null) {
                    throw new RuntimeException("Job no longer exists");
                } else if (queryJob.getStatus().getError() != null) {
                    // You can also look at queryJob.getStatus().getExecutionErrors() for all
                    // errors, not just the latest one.
                    throw new RuntimeException(queryJob.getStatus().getError().toString());
                }
            } else {
                Field id = Field.of("id", StandardSQLTypeName.INT64);
                Field name = Field.of("name", StandardSQLTypeName.STRING);
                Schema schema = Schema.of(id, name);
                TableId tableId = TableId.of(DATASET, TABLE_NAME);
                TableDefinition tableDefinition = StandardTableDefinition.of(schema);
                TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();
                bigquery.create(tableInfo);
            }

            TableRow row = new TableRow();
            for (Map.Entry<String, Object> entry : campaign.entrySet()) {
                row.set("id", entry.getKey()).set("name", entry.getValue());
                bigquery.insertAll(InsertAllRequest.newBuilder(TableId.of(DATASET, TABLE_NAME)).addRow(row).build());
            }

This excutes runtime error :

"UPDATE or DELETE statement over table would affect rows in the streaming buffer, which is not supported"

Upvotes: 3

Views: 5572

Answers (1)

Mohammed Asim
Mohammed Asim

Reputation: 2211

To delete table in BigQuery try this DELETE [table] WHERE TRUE

DELETE `android-app-Xbea8.analytics_42634008452.events_intraday_2024090599` WHERE TRUE

Upvotes: 0

Related Questions