Hubble Backup API

Contents:

• Hubble Backup API Installation Path
• Hubble Backup API Basic Commands
• Hubble Backup API Vector Maintenance Guide

Hubble Backup API Installation Path

Hubble Backup API will be installed by default on:

/sr/libexec/hubble-applianceapi

Hubble Backup API Basic Commands

• Start up:

  systemctl start hubble-applianceapi

• Shut down:

  systemctl stop hubble-applianceapi

Hubble Backup API Vector Maintenance Guide

Contents:

• Backing up the Accelerator
• Restoring an Accelerator Backup

Backing up the Accelerator

The accelerator backup feature provides the ability to back up the data on Hubble Accelerator which has not been replicated using Qlik Replicate. This mainly comprises data submitted as part of planning and budgeting.

The data replicated from your ERP is not backed up as the replication process will be used to re-import that data. The backup includes the Qlik repository and all database objects and data stored under the "hubble" Vector database schema.

Storage of Backup Archives

Backup archives are written to the location /import/dbbackup on the Accelerator.

Do not rely on storing the backup files on the same physical server as the accelerator. It is recommended that the backup folder be mounted on an external device, e.g. using NFS. This NFS mount must be configured before a backup is run.

If the folder /import/dbbackup does not exist then any backup operations will fail.

Manually Triggering a Backup

The backup process is triggered from the Administration tool by right-clicking on a profile configured to use an Accelerator for its data connection.

On clicking Yes the following message is presented:

If there was an error starting the backup (e.g. the Accelerator could not be reached) then this will be shown to the user as an error message:

Using the REST API to Trigger a Backup

The backup process has a public REST API which can be called using a scheduling tool which allows a URL to be called using the POST Method. The API URL is called using the accelerator server hostname. e.g. http:// hostname:5000/api/.

The API service can be tested by entering the API URL into a browser, the following should be returned:

REST API Details

The three backup operations that can be performed from the API are, starting a job, checking on its status, and canceling it.

Execute:

• Verb: POST

• URL: http://hostname:5000/api/Backup/Execute

• Parameters: None

• Result: Status JSON (see below)

Starts a new backup job and assigns it an ID that can be used for the following calls. The backup process will continue running after the HTTP call has completed.

Status:

• Verb: GET

• URL: http://hostname:5000/api/Backup/Status

• Parameters: 'id' - The ID from a previous status JSON result.

• Result: Status JSON (see below)

Returns the status of the backup job, based on the ID from a previous Execute operation.

Cancel:

• Verb: POST

• URL: http://hostname:5000/api/Backup/Cancel

• Parameters: 'id' - The ID from a previous status JSON result.

• Result: Status JSON (see below)

Attempts to cancel the backup job identified by the ID from a previous Execute operation.

Status JSON:

{

"Id": "b61a96c2134f4a59b91188c94e24aa57",

"Status": "Queued",

"Message": "Job was queued for execution."

}

The result of all three calls is a JSON object in the same format and with the same three elements:

• Id: The ID of the job. For Execute this will be the ID that must be saved for future information. For

Status or Cancel calls this will echo back the ID supplied to the request.

• Status: There are six possible values for this field:

  • Success - The backup job was completed successfully

  • Queued - The backup job has been queued and is waiting to be processed

  • Executing - The backup job is being executed.

  • Canceled - The backup job has been canceled. This is unlikely to be received in practice as canceled jobs are removed from the queue once they have completed.

  • LimitReached - An internal limit (e.g. number of concurrent jobs) has been reached that meant that the job could not be started.

  • Error - An internal error has occurred. As well as the message, further information will be available in the accelerator API's internal log file.

• Message: A human-readable explanation of the status. This may provide more information, especially in the case of an error.

Scheduling calls to the Accelerator Backup REST API

You may also already possess scheduling software which is capable of calling REST APIs directly. If not, below are two example approaches to scheduling the backup using the API. There is widespread guidance online regarding the use of these tools - Hubble cannot provide specific support for them.

Windows Task Scheduler and PowerShell

You can schedule a PowerShell script to invoke the backup API using Windows Task Scheduler. The script should look like the following:

Invoke-RestMethod -Uri "http://<hostname>:5000/api/Backup/Execute" -Method Post

where you replace <hostname> with the host name or IP address of your accelerator. To learn how to schedule this see for example https://social.technet.microsoft.com/wiki/contents/articles/38580.configure-to-run-a-powershell-script-into-task-scheduler.aspx.

To run unsigned PowerShell scripts, you may need to relax the 'execution policy' on the server, or override it on the PowerShell command line. See for example https://superuser.com/questions/106360/how-to-enable-execution-of-powershell-scripts.

Linux Cron and Curl

You can schedule the Linux command 'curl' using the scheduler 'cron'. The command you need to schedule should look like the following:

curl -X POST -d "" http://<hostname>:5000/api/Backup/Execute

where you replace <hostname> with the host name or IP address of your accelerator. Depending on your Linux distribution, you might have to use 'wget' instead of 'curl'.

Cron is not available within an accelerator deployed as a VMWare or AWS image, but you can schedule the task on any other Linux server. If you have deployed the accelerator as a Docker container, then you can use cron in the Docker host server which you supplied.

Troubleshooting

1. Check the log file “/tmp/ybapi.log” on the accelerator for error messages:

   1. Job with a given ID was not found. – Either the Cancel or Status API commands were executed with a Job ID that is either invalid or has completed.
   2. Unable to move file: {Error} – Several steps involve moving files, one of these failed. Examples are moving the replication backup file to a temporary folder, or moving the backup file to '/import/ dbbackup'.
   3. An error occurred while executing system command: {Command}. Error: {Error} – While we ask Qlik or Vector to perform a backup their data an error could occur.
   4. Not enough space is left in '{Location}': minimum required {xx}MB, space left {xx}MB. – Both the accelerator's temporary directory and '/import/dbbackup' must have a minimum amount of space available. By default this is 2GB.
   5. Unable to determine the drive of path '{Location}' – While checking for available space there was an error determining the drive to check.
   6. Directory '{Location}' does not exist. – The directory does not exist.
   7. File {Location} does not exist. – The file does not exist. This may occur because of an internal error that prevents a file we're trying to move from being created.
2. Restart the service.

Restoring an Accelerator Backup

Introduction

The Accelerator backup will come in the form of a Gzipped Tar archive which contains the contents of the budgeting and activity data stored on the database's Hubble schema. It also contains a backup of the Qlik replication tasks.

The process of restoring a backup consists of copying the archive onto the Accelerator then restoring each portion in turn. The order in which these tasks are run is important as attempting to restore the Hubble schema before allowing Qlik to re-replicate is likely to fail.

It assumes an empty Accelerator as the destination and that you have access to the 'root' account of it.

Preliminary Steps

To restore these backups onto a fresh server it is necessary to connect to a terminal (SSH) session on the Accelerator as root, and to copy the backup archive onto it.

We will assume that a Windows machine with Putty installed is being used. A Linux desktop could also be used with minimal modification to the steps.

Copying the Backup

1. From the host machine this can be achieved with Putty PSCP command. Replace the location of backup file and the Accelerator's IP address or host name.

   pscp -scp path\to\hubble_schema_backup.tar.gz root@accelerator_ip:/tmp/

2. Once there the archive should be expanded using the following commands from within the root SSH session.

   cd /tmp

   gunzip hubble_schema_backup_2017_07_15_21_56_10.tar.gz tar xvf hubble_schema_backup_2017_07_15_21_56_10.tar

3. This will create two folders, AttunityBackup and DatabaseBackup.

The first of these contains the definition of the ERP database replication tasks, whilst the second contains the data from the ‘Hubble’ schema of the database. We need to give the relevant accounts full access to 'their' data,

chown -R attunity:attunity /tmp/AttunityBackup chown -R actian:actian /tmp/DatabaseBackup

Qlik Backup

Importing the Replication Definition

The 'AttunityBackup' directory should contain a single file called Replication_Definition.json which contains the configuration of the Qlik components.

The major parts of this file are the definitions of the tasks, and the database connections that feed them. This can be imported into the Accelerator’s Qlik database with the following commands in the SSH session.

su attunity

repctl importrepository json_file=/tmp/AttunityBackup/ Replication_Definition.json

Create Schemas on the Accelerator

Before we can execute the replication tasks it is necessary to create the schema(s) into which this data will be placed. This is performed from within a SSH session and must be performed for each destination schema required by an Qlik Replicate task.

1. From the SSH session execute the command below as a root user:

   sql iidbdb

2. From within the SQL terminal create a new user for the target schema and then quit the application:

   create user schemaName with password='password' \g grant all on database db to schemaName \g

commit \g

\q

3. Now we need to create and drop a table for the above user, to initialize the new schema. From the SSH session execute the following command:

   sql db -uschemaName

From within the SQL terminal:

create table t1 (c1 char(1)); \g commit \g

drop table t1; \g commit \g

\q

Run Qlik Replicate Tasks

1. Configure the Qlik Replicate console to connect to the new Accelerator. This is described elsewhere.

2. Run Qlik Replicate Console and note that the tasks will be present.

3. Click Manage Databases.

4. Click Open on the source/target databases and re-enter the passwords and test the connections.

5. Open the task we are interested in and check that there are tables populated in the Explicitly Selected Tables list.

If there are none, then it is likely that permissions on Linux were not given to the Attunity user earlier on (before repctl importrepository was run).

6. Run the task. Database Hubble Schema File Contents

   The DatabaseBackup folder contains a file for each of the tables in the Hubble database schema. This

   includes:

   • Budgeting data. Budgeting tables will be backed up including the custom strategic planning tables.

     Much of these tables are named with a two-letter prefix then ‘budget’. Also included in this group are ‘is_generic’ and ‘isbpayroll’ ('is' is the insightsoftware prefix)

   • Activity tables. These tables’ names all begin with ‘activity’ and contain user activity logged by the application.

   • Dual table (for EBS only). This is a dummy table which may be present, but contains no data.

     In addition to these tables there will be two other files, called ‘copy.in’ and ‘copy.out’. These are scripts to automate the insertion and extraction of data.

     Copy.in Script

     The ‘copy.in’ script is used to restore the exported data. As well as containing the definition of the tables exported it also contains links to the files containing the tables’ contents.

     There are known scenarios where the backed up data contains synonyms that point to tables that are not included in the replication tasks. If this is the case then it is preferable to allow the restoration process to run through without stopping on error. The list of errors can then be examined after the import to verify the missing synonyms, or other errors that occurred.

     By default the import process will stop when it encounters an error. To allow this to continue it is necessary to edit the 'copy.in' file using a text editor such as nano. Near the top of the file will be the line

   • \nocontinue

   • To allow the import to continue on error this should be changed thus, and the file saved.

   • \continue

   • Performing Import

   • It is required that the Hubble schema on a fresh Accelerator is empty. A freshly deployed Accelerator will have the five Activity tables created automatically, which must be deleted before restoring the backup. If the Activity tables are not deleted the restore script will fail when it attempts to recreate them.

   • Start the restore process from the backup directory with:

   • cd /tmp/DatabaseBackup

   • sql db -uhubble < copy.in > output.txt

   • This line assumes that the database is called ‘db’ and the user/schema is ‘hubble’.

   • Changes to the user used will require manual changes to the copy.in script as the original schema name is mentioned there.

   • Checking for Errors

   • The output of the restore process has been logged to 'output.txt'. It is therefore necessary to identify if there have been any errors logged to this file, using the following command:

   • grep ^E_ output.txt

   • If the import encountered tables are no longer replicated or had been created manually, but which had synonyms, then errors like this will be listed:

   • E_US0845 Table 'ar_receipt_classes' does not exist or is not owned by you.

   • The surrounding text for this error will detail the operation that was being attempted when the error occurred. This can be found by examining the log file in a text editor or viewer.