Delta lake RESTORE issue (Databricks)

Question

I am working on Azure databricks and created a delta table on ADLS Gen2.

I have 4 versions of delta lake created already.

I am trying to restore to version number 2 with the following command.

%sql
RESTORE TABLE Sales TO VERSION AS OF 2

Could someone let me know why I am not able to restore to the older version? The error as below happening now.

Error in SQL statement: ParseException: 
extraneous input 'RESTORE' expecting {'(', 'CONVERT', 'COPY', 'OPTIMIZE', 'ADD', 'ALTER', 'ANALYZE', 'CACHE', 'CLEAR', 'COMMENT', 'COMMIT', 'CREATE', 'DELETE', 'DESC', 'DESCRIBE', 'DFS', 'DROP', 'EXPLAIN', 'EXPORT', 'FROM', 'GRANT', 'IMPORT', 'INSERT', 'LIST', 'LOAD', 'LOCK', 'MAP', 'MERGE', 'MSCK', 'REDUCE', 'REFRESH', 'REPLACE', 'RESET', 'REVOKE', 'ROLLBACK', 'SELECT', 'SET', 'SHOW', 'START', 'TABLE', 'TRUNCATE', 'UNCACHE', 'UNLOCK', 'UPDATE', 'USE', 'VALUES', 'WITH'}(line 1, pos 0)

== SQL ==
RESTORE TABLE Sales TO VERSION AS OF 2

Answer 1

Please check that you're using the correct version of the Databricks Runtime. Per documentation it's available since DBR 7.4 only, and not in the earlier versions. I just checked on DBR 7.5, and it works just fine

If you're on the earlier versions of Databricks Runtime, then you can use INSERT OVERWRITE statement with select of specific version of the table:

insert overwrite <table> SELECT * FROM <table> VERSION AS OF <version>

Or another possibility is to use TRUNCATE TABLE + INSERT INTO:

truncate table <table>;
INSERT INTO <table> SELECT * FROM <table> VERSION AS OF <version>