SYSCS_UTIL.VALIDATE_TABLE_BACKUP
The SYSCS_UTIL.VALIDATE_TABLE_BACKUP
system procedure validates a table backup by checking for inconsistencies; it reports on missing files and bad checksum values.
Syntax
SYSCS_UTIL.VALIDATE_TABLE_BACKUP( VARCHAR schemaName,
VARCHAR tableName,
VARCHAR directory,
BIGINT backupId,
schemaName
The name of the table’s schema.
tableName
The name of the table whose backup you want to validate.
directory
Specifies the path to the directory containing the backup you want to validate. This can be a local directory if you’re using the standalone version of Splice Machine, or a directory in your cluster’s file system (HDFS or MapR-FS).
Relative paths are resolved based on the current user directory. To avoid confusion, we strongly recommend that you use an absolute path when specifying the backup location.
backupId
The ID of the table backup job from which you want to restore your table.
Results
This procedure does not return a result.
Execute Privileges
If authentication and SQL authorization are both enabled, only the database owner has execute privileges on this function by default. The database owner can grant access to other users.
SQL Example: Backup, Validate, and Restore a Table
This example shows you how to back up a table, then validate and restore it, in these steps:
Backing Up the Table
This command line performs a full backup of the TPCH100 LINEITEM
table to the /backup
directory on HDFS:
splice> CALL SYSCS_UTIL.SYSCS_BACKUP_TABLE('TPCH100', 'LINEITEM', '/backup', 'full');
Success
----------------------
FULL backup to /backup
1 row selected
See the reference page for the SYSCS_UTIL.SYSCSBACKUP_TABLE
system procedure for more information about backing up a table.
Examining the Backup
After the backup completes, you can examine the sys.sysbackup
table to find the ID of our new backup:
splice> SELECT * FROM sys.sysbackup;
BACKUP_ID |BEGIN_TIMESTAMP |END_TIMESTAMP |STATUS |SCOPE |INCR&|INCREMENTAL_PARENT_&|BACKUP_ITEM
-----------------------------------------------------------------------------------------------------------------------------------
587516417 |2018-09-25 00:12:33.896 |2018-09-25 00:42:53.546 |SUCCESS |TABLE |false|-1 |3
You can use the ID of your backup job to examine the sys.sysbackupitems
and verify that the base table and two indexes have been backed up:
splice> SELECT * FROM sys.sysbackupitems WHERE backup_Id=587516417 ;
BACKUP_ID |ITEM |BEGIN_TIMESTAMP |END_TIMESTAMP
-----------------------------------------------------------------------------------------
587516417 |splice:292000 |2018-09-25 00:12:40.512 |2018-09-25 00:32:14.856
587516417 |splice:292033 |2018-09-25 00:12:40.513 |2018-09-25 00:42:48.573
587516417 |splice:292017 |2018-09-25 00:12:40.512 |2018-09-25 00:41:25.683
3 rows selected
Validating the Backup
Before restoring the table, you can validate the backup:
splice> CALL SYSCS_UTIL.VALIDATE_TABLE_BACKUP( 'TPCH100', 'LINEITEM', '/backup', 587516417 );
Results
---------------------------------------------------------------------------------------------
No corruptions found for backup.
1 row selected
Restoring the Backup
You can restore the table to another table on the same cluster, or on a different cluster.
This command restores the backed-up table to table named LINEITEM
in the SPLICE
schema:
splice> CALL SYSCS_UTIL.SYSCS_RESTORE_TABLE('SPLICE', 'LINEITEM', 'TPCH100', 'LINEITEM', '/backup', 587516417, false);
Statement executed.
See the reference page for the SYSCS_UTIL.SYSCS_RESTORE_TABLE
system procedure for more information about restoring a backed-up table.