Staging for SingleStorelink
While running Burst Integrate and Bulk Refresh, Fivetran HVR can stream data into a target database straight over the network into a bulk loading interface specific for each DBMS (e.g. direct-path-load in Oracle), or else HVR put data into a temporary directory (‘staging file') before loading data into a target database.
For best performance, HVR performs Burst Integrate and Bulk Refresh into SingleStore location using staging files. HVR implements the file staging in SingleStore by using either of the following methods:
Server File Staging - The SingleStore Server directly reads the staging files and ingest them into the target tables.
- HVR first writes data into the local staging directory on the machine where HVR is installed.
- HVR then uses the SQL command
load data
to ingest the data into the SingleStore target tables. Here, the SingleStore Server will directly read the staging files from the staging folder and load them into the target tables.
The following conditions should be satisfied to use this method:
- The HVR database User should have FILE permission.
- The system variable (of SingleStore server) secure_file_priv should be set to "" (blank).
Client File Staging - The SingleStore Client directly reads the staging files and pass them to the SingleStore Server, which then ingest them into the target tables.
Use case 1: HVR and SingleStore Server are installed on separate machines.
In this use case, the SingleStore Client must be installed on the same machine where HVR is installed.
- HVR first writes data into the local staging directory on the machine where HVR is installed.
- HVR then uses the SQL command
load data local
to ingest the data into SingleStore target tables. Here, the SingleStore Client will directly read the staging files from the staging folder and copy them into the machine where SingleStore Server is installed. The SingleStore Server will read the staging files received from the MySQL Client and load them into the target tables.
Use case 2: If you do not want to give the FILE privilege to the HVR database User.
In this use case, the SingleStore Server, and SingleStore Client must be installed on the same machine.
- HVR first writes data into the staging directory on the machine where HVR is installed.
- HVR then uses the SQL command
load data local
to ingest the data into SingleStore target tables. Here, the SingleStore Client will directly read the staging files from the staging folder and pass them to the SingleStore Server, which will then read the staging files received from the SingleStore Client and load them into the target tables.
The following condition should be satisfied to use this method:
- The system variable (of SingleStore client and server) local_infile should be enabled.
Configuring Staginglink
HVR must be configured to stage data on the local directory before loading it into SingleStore. For staging the data on local directory and perform Burst Integrate and Bulk Refresh into SingleStore, the following are required:
Depending on the Staging method used, the following must be configured in SingleStore:
- Server Based Staging,
- The database User must have FILE privilege.
- The system variable (of SingleStore server) secure_file_priv should be set to "" (blank).
- Client Based Staging
- The system variable (of SingleStore client and server) local_infile should be enabled.
- Server Based Staging,
Define the following location properties (while creating a location or by editing the existing location's source and target properties) for the SingleStore location:
The Location Property equivalent to the UI field is shown inside (bracket) below.
Staging Directory Is Local (Staging_Directory_Is_Local): Indicates that staging files are created in the local directory. This field is automatically selected by default and it cannot be unselected.
STAGING DIRECTORY (Staging_Directory): Local directory path where HVR will create the temporary staging files (e.g. /my_staging_dir).
STAGING DIRECTORY AS VISIBLE FROM THE DATABASE (Staging_Directory_Database): Directory path from where SingleStore will access the temporary staging files (e.g. /my_staging_dir). This field must be left blank if the staging method is Client Based Staging.
- If the staging method is Client Based Staging, then only the STAGING DIRECTORY must be specified.
- If the staging method is Server Based Staging, then both STAGING DIRECTORY and STAGING DIRECTORY AS VISIBLE FROM THE DATABASE must be specified.