Staging for MySQL

While running and , Fivetran Local Data Processing 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 Local Data Processing puts data into a temporary directory (‘staging file') before loading data into a target database.

For best performance, Local Data Processing performs and into MySQL location using staging files. Local Data Processing implements the file staging in MySQL by using either of the following methods:

• Server Based Staging - The MySQL Server directly reads the staging files and ingest them into the target tables.

  1. Local Data Processing first writes data into the local staging directory on the machine where Local Data Processing is installed.
  2. Local Data Processing then uses the MySQL command load data to ingest the data into the MySQL target tables. Here, the MySQL Server will directly read the staging files from the staging folder and load them into the target tables.

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

• Client Based Staging - The MySQL Client directly reads the staging files and passes them to the MySQL Server, which then ingests them into the target tables.

  • Use case 1: Local Data Processing and MySQL Server are installed on separate machines.

    This use case requires MySQL Client to be installed on the same machine where Local Data Processing is installed.

    1. Local Data Processing first writes data into the local staging directory on the machine where Local Data Processing is installed.
    2. Local Data Processing then uses the MySQL command load data local to ingest the data into MySQL target tables. Here, the MySQL Client will directly read the staging files from the staging folder and copy them into the machine where MySQL Server is installed. The MySQL 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 Local Data Processing database User.

    This use case requires Local Data Processing, MySQL Server, and MySQL Client to be installed on the same machine.

    1. Local Data Processing first writes data into the staging directory on the machine where Local Data Processing is installed.
    2. Local Data Processing then uses the MySQL command load data local to ingest the data into MySQL target tables. Here, the MySQL Client will directly read the staging files from the staging folder and pass them to the MySQL Server, which will then read the staging files received from the MySQL Client and load them into the target tables.

  For MySQL on cloud, this staging method is not possible since Local Data Processing and MySQL Server cannot be installed on the same machine.

Configuring Staging

Local Data Processing must be configured to stage data on the local directory before loading it into MySQL. For staging the data on local directory and perform and into MySQL, the following are required:

1. Depending on the Staging method used, the following must be configured in MySQL:

   • Server Based Staging,
     1. The database User must have FILE privilege.
     2. The system variable (of MySQL server) secure_file_priv should be set to "" (blank).
   • Client Based Staging
     1. The system variable (of MySQL client and server) local_infile should be enabled.

2. Define the following location properties (while or by ) for the MySQL location:

   The equivalent to the UI field is shown inside brackets below.

   1. 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.

   2. STAGING DIRECTORY (): Local directory path where Local Data Processing will create the temporary staging files (e.g. /my_staging_dir).

   3. STAGING DIRECTORY AS VISIBLE FROM THE DATABASE (): Directory path from where MySQL will access the temporary staging files (e.g. /my_staging_dir). This field must be left blank if the staging method is MySQL 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.