Expired table

[zinger]

Hello,

Two days ago the table i was using has expired and got deleted. I'm trying to recover it by using 

FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(current_timestamp(),interval 3 DAY) statement, hovewer it fails with the following error message: Table  was not found in location US.

I also tried to bq cp command, but it fails too (with flexible column name `salary-frequency` does not support table copy)

Any ideas how to solve it? Thank you in advance

[jangemmar]

Hi @zinger,

Welcome to Google Cloud Community!

I understand the challenges you’re facing with restoring deleted tables in BigQuery.

Regarding the errors that you’re getting, this could be because of the following reasons:

• Incorrect table location: You might be specifying the wrong location for your table. 
• Incorrect table name: There’s a good chance that you entered an incorrect table name (case-sensitive)
• Table is deleted: The table was actually deleted, even if you think it might have expired.
• Flexible column name does not support table copy: You might be using some special characters that are not supported, or you might be using the same name for your table and one of its columns.

Here’s what you can do to verify the possible reasons above:

• Verify the location of the table: Double-check that your project is in the "US" location and that the table was created there. You can issue the following command in the cloud shell to verify where your table is configured.

bq show --project_id=<project_id> --location=<location> <dataset_id>.<table_id>

• Verify the table name:Ensure you're using the exact table name. Please note that table names are case-sensitive.
• Please ensure your table name is completely unique from one of its columns. You can refer to supported characters for flexible column name.

Here is an example of a query that returns a historical version of the table from one hour ago:

SELECT *
FROM `mydataset.mytable`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

I hope the above information is helpful.