CODEWITHSUNDEEP
databasegoogle-bigqueryschemadatabase-migration
J_lion
J_lion

Reputation: 1

Big Query Restoring table

I have two data sets due to the recent Big Query migration. The old dataset has a table 20180629 the new dataset also had a table labeled 20180629. I deleted the new table to migrate data believing that all the information for that day was stored in the old table. However, when I migrated the old to the new, I found out that only partial amounts of data were stored on the old table. Thus the new 20180629 had a partial amount of the total data from that day.

I have tried:

bq cp table1.events_20180629@1530460302987 table1.temp_table

This either gets me the error that the timestamp is incorrect (BTW what is the timestamp length suppose to be). Or when I get it to work, it copies an empty table.

I want to restore in the new dataset the original 20180629. I however, I deleted one 20180629 and then created a partial 20180629 and then I deleted this one. I fear that this data is completely lost. Can some one tell me the command bq cp command with proper timestamp to go back a day in my big query dataset? My research on this issue tells me I only have two days to do this so any help would be great.

Upvotes: 0

Views: 1140

Answers (1)

Bharat Mishra
Bharat Mishra

Reputation: 91

I am afraid but I think you lost your data. Restoration will work within 2 days of table deletion and if there is no new table with same id created. In your case you have created table with same id.

however if you want to get timestamp information then you can run below query in legacy sql. This query will return you timestamp of 12 hours ago. You can change hours based on your need.

SELECT INTEGER(DATE_ADD(USEC_TO_TIMESTAMP(NOW()), -12, 'HOUR')/1000)

And also it is better to use entire details in bq cp command like location, project if and others. Restoration command works absolutely fine but with 2 primary limitation, 1)table created with same id and 2) dataset is deleted

bq --location=[LOCATION] cp myprojectid.mydataset.mytable@1418864998000 myprojectid.mydataset.newtable

Hope this help.

Upvotes: 1

Related Questions