Staging for MariaDB - DSS 6 | Data Source Solutions Documentation

Documentation: Staging for MariaDB - DSS 6 | Data Source Solutions Documentation

Staging for MariaDB

While running Burst Integrate and Bulk Refresh, Data Source Solutions DSS 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 DSS put data into a temporary directory (‘staging file') before loading data into a target database.

For best performance, DSS performs Burst Integrate and Bulk Refresh into MariaDB location using staging files. DSS implements the file staging in MariaDB by using either of the following methods:

Server Based Staging - The MariaDB Server directly reads the staging files and ingest them into the target tables.
1. DSS first writes data into the local staging directory on the machine where DSS is installed.
2. DSS then uses the MariaDB command <b>load data</b> to ingest the data into the MariaDB target tables. Here, the MariaDB Server will directly read the staging files from the staging folder and load them into the target tables.

In this method, if DSS and MariaDB Server are installed/running on the same machine, the MariaDB Server can directly read and load the staging files into the target tables. If DSS and MariaDB Server are installed/running on separate machines, then the staging files created by DSS must be made accessible to the MariaDB Server, so that the MariaDB Server can directly read and load the staging files into the target tables.

Client Based Staging - The MariaDB Client directly reads the staging files and pass them to the MariaDB Server, which then ingest them into the target tables.
- Use case 1: DSS and MariaDB Server are installed on separate machines.

This use case requires MariaDB Client to be installed on the same machine where DSS is installed.

    1.  DSS first writes data into the local staging directory on the machine where DSS is installed.
    2.  DSS then uses the MariaDB command `<b>load data local</b>` to ingest the data into MariaDB target tables. Here, the MariaDB Client will directly read the staging files from the staging folder and copy them into the machine where MariaDB Server is installed. The MariaDB Server will read the staging files received from the MariaDB Client and load them into the target tables.

-   **Use case 2**: If you do not want to give the **FILE** privilege to the DSS database **User**.  

    <div class="callout callout-note">

This use case requires DSS, MariaDB Server, and MariaDB Client to be installed on the same machine.

    1.  DSS first writes data into the staging directory on the machine where DSS is installed.
    2.  DSS then uses the MariaDB command `<b>load data local</b>` to ingest the data into MariaDB target tables. Here, the MariaDB Client will directly read the staging files from the staging folder and pass them to the MariaDB Server, which will then read the staging files received from the MariaDB Client and load them into the target tables.

<div class="callout callout-important">

For MariaDB on cloud, only this staging method is possible since DSS and MariaDB Server cannot be installed on the same machine.

Configuring Staging

DSS must be configured to stage data on the local directory before loading it into MariaDB. For staging the data on local directory and perform Burst Integrate and Bulk Refresh into MariaDB, the following are required:

Depending on the Staging method used, the following must be configured in MariaDB:
- Server Based Staging,
  1. The database User must have FILE privilege.
  2. The system variable (of MariaDB server) secure_file_priv should be set to "" (blank).
- Client Based Staging
  1. The system variable (of MariaDB client and server) local_infile should be enabled.
Define the following location properties (while creating a location or by editing the existing location's source and target properties) for the MariaDB location:

The Location Property equivalent to the UI field is shown inside (bracket) below.

1.  **Staging Directory Is Local** ([**Staging_Directory_Is_Local**](/docs/dss6-property-reference-location-properties#stagingdirectoryislocal)): Indicates that staging files are created in the local directory. This field is automatically selected by default and it cannot be unselected.

2.  **STAGING DIRECTORY** ([**Staging_Directory**](/docs/dss6-property-reference-location-properties#stagingdirectory)): Local directory path where DSS will create the temporary staging files (e.g. **/my_staging_dir**).

3.  **STAGING DIRECTORY AS VISIBLE FROM THE DATABASE** ([**Staging_Directory_Database**](/docs/dss6-property-reference-location-properties#stagingdirectorydatabase)): Directory path from where MariaDB will access the temporary staging files (e.g. **/my_staging_dir**). This field must be left blank if the staging method is ****MariaDB** Client Based Staging**.

<div class="callout callout-important">

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.