How to modify the columns

Jerry Vfc 0
Viewed 100

Using the code below, I extract data from bq table and in that, I have done some data cleaning(updating null values)

    query3 ="""
UPDATE pvr-data-project.testing_dataset.movie_metadata_v3a t
SET t.movie_buff_uuid = u.movie_buff_uuid
FROM pvr-data-project.testing_dataset.movie_metadata_v3a u
WHERE t.movie_name=u.movie_name
;"""
google-bigquery python
2 Answers

The simplest way to approach this would be to load your dataset into a staging table into BigQuery using the load_table_from_dataframe() method.

example:

client.load_table_from_dataframe(dataframe=df1, destination='project_id.dataset_id.updated_dataset')

The result of the above will create a table in BigQuery with your the data from your dataframe which contains the updated ID values.

Once within BigQuery you can issue a update statement similar to this:

UPDATE project_id.dataset_id.testing_dataset t
SET t.id = u.id
FROM project_id.dataset_id.updated_dataset u
WHERE t.movie_name=u.movie_name

This will replace the IDs in your testing_dataset with all matches from staging table you updated the IDs on in python.

Once the data is updated you can drop the updated_dataset table.

Daniel Zagales0

You may update rows in table using UPDATE SQL like this

UPDATE `testing_dataset` SET movie_name = xxx WHERE id = yyy

Check Big Query DML Documenntation document here.

user7JCl10
Related