BigQuery Python API copy_table copies schema but not data

Question

I'm trying to copy one BigQuery table to another, within the same dataset, working off of the example at https://cloud.google.com/bigquery/docs/managing-tables#copy-table

I've created a function to do the copy, as follows:

def copy_table (source_table,dest_table):
    client = bigquery.Client()
    source_table_ref="my_project.my_dataset."+source_table
    dest_table_ref="my_project.my_dataset."+dest_table

    job = client.copy_table(
        source_table_ref,
        dest_table_ref)  # API request
    job.result()

But when I do the copy, the dest_table is created, with the same schema as the source_table, but none of the data is copied from the source_table to the dest_table.

Here's the overall sequence of what I'm doing:

Create source_table
Insert rows into source_table
Do query to check that the rows are in the source_table (they are -- SELECT COUNT(*) returns the right number of rows)
Copy source_table to dest_table using the function above
Do query to check that the rows are in the dest_table (they are NOT -- SELECT COUNT(*) returns zero rows)

My guess is that somehow these jobs are being executed asynchronously, but I can't figure out how to make them execute synchronously. Any ideas would be greatly appreciated.

If it helps any, my overall goal is to create and fill a new table with the results of a daily batch job (get_user_info_2020-06-27, for example), then copy it to a get_user_info_current table that always contains the current day's user info.

Edit:

Some more info, based on testing:

On the page at https://cloud.google.com/bigquery/streaming-data-into-bigquery#dataavailability, it says: "Data can take up to 90 minutes to become available for copy operations". So, I coded up a little thing to go after my insert statement to wait until it completes:

def insert_table_wait(table_name,prev_rows,rows_inserted):
    client = bigquery.Client()
    table_id = "pacs-user-analysis-dev.google_users."+table_name
    table = client.get_table(table_id)  # Make an API request.

    #wait until the insert fully completes
    curr_table=client.get_table(table_id)
    sys.stderr.write(str(datetime.datetime.now()) +" "+table_name +" properties: "+str(curr_table._properties)+"\n")
    curr_rows=int(curr_table._properties.get('numRows'))
    while curr_table._properties.get('streamingBuffer') is not None or curr_rows != prev_rows+rows_inserted:
        sys.stderr.write(str(datetime.datetime.now()) +" Waiting for insert into "+str(curr_table._properties.get('id'))+" to complete. StreamingBuffer details: "+str(curr_table._properties.get('streamingBuffer'))+" prev_rows: "+str(prev_rows)+" curr_rows: "+str(curr_rows)+ " should be: " + str(prev_rows+rows_inserted)+"\n")
        time.sleep(10)
        curr_table=client.get_table(table_id)
        curr_rows=int(curr_table._properties.get('numRows') )

I'm hoping that that might fix things. What I don't understand is that the new rows appear in the BigQuery Console UI almost immediately, but table._properties.get('numRows') doesn't seem to update nearly as promptly.

I tried to reproduce your problem but it worked fine for me. Can you let me know if your data is getting inserted in your destination table after a while? — rmesteves
Thanks @rmesteves. No, it doesn't show up after a while (over the weekend), even with a test insert of only 7 rows. — Ian Crew
You were copying the data just after the insertion? So maybe this is the problem. Please let me know if you had any progress on that — rmesteves
@rmesteves: Yes, it was due to copying right after insertion. The "insert_table_wait" function I added in my edited question does seem to do the trick by waiting until the insert is fully complete. (Though it does take a long time--usually 75-90 minutes--for that to complete. Painful!) I'd welcome any suggestions for alternatives. What's weird is that it's possible to query the inserted rows in the table right after the insert finishes, just not to copy that table. Shrug. — Ian Crew
I have a suggestion for you. I will post it directly as an answer since its difficult to post code in the comments. We can discuss in the answer if it achieves your expected behavior — rmesteves

rmesteves rmesteves · Accepted Answer · 2020-07-01T05:45:52

Since there is a limitation i copy jobs for data in streaming buffer, I suggest that you use a query job and set a destination table just like the code below:

from google.cloud import bigquery
client = bigquery.Client(project = "your-project")

job_config = bigquery.QueryJobConfig(destination="destination-table-id")
# SELECT * to copy the whole table
sql = "SELECT * FROM <source_table>"
query_job = client.query(sql, job_config=job_config)
query_job.result()

Please let me know if that helps you

BigQuery Python API copy_table copies schema but not data

1 Answers