Delete Big query table using Apache Beam java

Question

Is it possible to delete a table available in bigQuery using Apache beam using Java?

p.apply("Delete Table name", BigQueryIO.readTableRows().fromQuery("DELETE FROM Table_name where condition"));

What is your use case? Because maybe a WRITE_TRUNCATE disposition could do the trick? — Juan Urrego, Aug 22 '19 at 19:54
Dependent on the use case you may also find the table TTL useful: https://cloud.google.com/bigquery/docs/managing-tables#updating_a_tables_expiration_time — Reza Rokni, Aug 23 '19 at 02:48
Also BeamSQL does not support DELETE DML yet. It's welcomed to contribute on this topic. — Rui Wang, Aug 23 '19 at 18:08

score 1 · Accepted Answer · answered Aug 29 '19 at 10:23

I resolved this using BQ API.

BigQuery bigquery = BigQueryOptions.newBuilder().setCredentials(credential).setProjectId(ProjectName).build().getService(); QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(Query).setUseLegacySql(false).build(); JobId jobId = JobId.of(UUID.randomUUID().toString()); Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

score 0 · Answer 2 · answered May 24 '23 at 07:33

import apache_beam as beam
from apache_beam.options.pipeline_options import PipelineOptions

# Define the project ID and table ID
project_id = 'your-project-id'
dataset_id = 'your-dataset-id'
table_id = 'your-table-id'

# Create a PipelineOptions object
options = PipelineOptions()
options.view_as(beam.options.pipeline_options.GoogleCloudOptions).project = project_id

# Delete the BigQuery table
with beam.Pipeline(options=options) as p:
    # Define a dummy element to create a PCollection
    dummy_element = p | 'Create dummy element' >> beam.Create([None])
    
    # Delete the BigQuery table
    dummy_element | 'Delete BigQuery table' >> beam.io.WriteToBigQuery(
        table=table_id,
        dataset=dataset_id,
        project=project_id,
        create_disposition=beam.io.BigQueryDisposition.CREATE_NEVER,
        write_disposition=beam.io.BigQueryDisposition.WRITE_EMPTY
    )

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, May 24 '23 at 12:46

score 0 · Answer 3 · answered May 24 '23 at 10:41

import apache_beam as beam

project_id = 'your-project-id'
dataset_id = 'your-dataset-id'
table_id = 'your-table-id'

table_spec = f'{project_id}:{dataset_id}.{table_id}'

# Define a pipeline
pipeline = beam.Pipeline()

# Delete the BigQuery table
(
    pipeline
    | 'Create dummy data' >> beam.Create([{}])
    | 'Delete BigQuery table' >> beam.io.WriteToBigQuery(
        table_spec,
        write_disposition=beam.io.BigQueryDisposition.DELETE,
        create_disposition=beam.io.BigQueryDisposition.CREATE_IF_NEEDED
    )
)

# Run the pipeline
pipeline.run()

There's only code. Make it with a description. – jett8998 May 24 '23 at 12:47 — jett8998, May 24 '23 at 12:47

Delete Big query table using Apache Beam java

3 Answers3