Target Guides
Wirekite Loaders for MySQL and MariaDB
MySQL and MariaDB as Data Destinations
Loader Prerequisites
To configure the Wirekite Loaders for MySQL and MariaDB, you must have the following prereqs met.- Make sure that you have a properly configured database user with the GRANTs needed to CREATE TABLEs, access FILEs, run INSERT/UPDATE/DELETE.
- Use “mysql” or the Wirekite cmdline command line tool to interact with the database instance.
- Make sure your Extractor host and database instance are set up to run the extraction. In most situations, you will run the Extract and Load operation at the same time using the Wirekite Orchestrator tool.
- Make sure that the Wirekite target metadata tables - wirekite_progress and wirekite_action - exist in your target database dataworld and are read-and-writable from the MySQL/MariaDB user and host that will do the extraction.
Wirekite Schema Loader for MySQL and MariaDB
The Wirekite Schema Loader is a utility that takes the schema file created by the Schema Extractor on the extraction host/database as input. The Schema Loader for MySQL and MariaDB will generate appropriate SQL to CREATE the target tables in the target. If you need to add additional storage directives or other schema elements, you can use the CREATE TABLE output as a basis for these. But don’t change the column names, column order, or change datatypes or constraints to ones that are incompatible with the ones created here. You can take these files and load them directly to MySQL or MariaDB to CREATE the target tables, using “mysql” or the Wirekite cmdline command-line tool. Here are the configuration variables required to run the Schema Loader.schemaFile is the file containing Wirekite schema data that was generated by a Wirekite Schema Extractor on the source database. The Schema Loader will use the contents of this file to generate SQL DDL to create the target schema. The path should be absolute and readable by the Schema Loader executable.
createTableFile is an output file that will contain CREATE TABLE statements appropriate to MySQL and MariaDB. Note that each source table will have two entries: one for the table itself, and one for its “MERGE” table used to load change events by the Wirekite Change Loader.
createForeignFile is an output file that will be used for FOREIGN KEY definitions. If you wish to add FOREIGN KEY specs - this should likely wait until after the initial load completes for performance reasons - you can run this file to create them.
createConstraintFile is an output file with non-columnar constraints, including indexes. Note that column-level constraints such as NOT NULL are handle by CREATE TABLE.
dropTableFile is a file that will contain DROP TABLE IF EXISTS statements. DROP TABLE statements will be created for both base tables and MERGE tables. This is optional.
logFile is where the Wirekite Schema Loader write its logging output. This needs to be writable by the user who runs the load.
createMergeTables is an optional argument. Its default is true. If set to false, CREATE TABLE statements will be generated only for base tables. MERGE tables will be skipped. This can be set to false if you only intend to do DATA loads, but not CHANGE loads.
Wirekite Data Loader for MySQL and MariaDB
The Wirekite Data Loader uses the output files written by the Extractor or moved by the File Data Mover and loads them to PostgreSQL.dsnFile is a file containing the connection information for the instance that will be accessed by the Data Loader.
workDirectory is the path to the directory where the Data Loader will find the data files to be loaded. It is where either the Data Extractor or File Mover wrote data files.
schemaFile is the file containing Wirekite schema data that was generated by a Wirekite Schema Extractor on the source database, and used by the Schema Loader mentioned above. The path should be absolute and readable by the Data Loader executable.
logFile is where the Data Loader will write its logging output.
hexEncoding is optional and its default is false. It should only be set if the Data Extractor extracted data using hex format.
maxThreads is the maximum number of parallel threads used to load data to the target database instance. The default is 5. We recommend setting this to the number of CPUs on the host.
removeFiles specifies whether the Data Loader will remove files from the workDirectory once the files have been loaded. The default is true. This may be set to false while troubleshooting, but should not normally be set as these files can take a lot of space in the bucket.
Wirekite Change Loader for MySQL and MariaDB
The Wirekite Change Loader for MySQL and MariaDB loads changes to the target tables, using output created by a Wirekite Change Extractor. The Wirekite Change Loader uses a merging approach to load changes, and needs to move transient data to an scratch directory, which will be used to load the Merge Tables. The Change Loader shouldn’t start until the Data Loader for the same table-world has successfully completed its full load.dsnFile is a file containing the connection information for the instance that will be accessed by the Change Loader.
dataDirectory is where the Wirekite Change Extractor wrote its files. These will be sourced for changes by the Wirekite Change Loader.
workDirectory is where the Change Loader writes intermediate files.
removeFiles specifies whether the Change Loader will remove files from the dataDirectory once it has fully processed their changes. The default is true. This may be set to false while troubleshooting, but should not normally be set as these files can take a lot of storage space.
logFile is where the Data Loader will write its logging output.