Overview
Magnus features MySql Task which provides the ability to transfer data to a MySql location
MySql Task accepts a reference to Google BigQuery source data and an IP for a destination MySql database. Data can be exported in different encoding and loading methods
Front Panel
Flip Panel
Add a MySQL Task
1. Under Add Task, select the desired position from the "Add to position" drop down list, then click +MySQL. 
Source BQ Table
2. Specify the Source BQ Table. 
a. Click the icon 
to open the BQ selector to select a BigQuery table as the source.
b. The source table can also be specified manually.
It is of the format projectID:datasetID.tableID with optional project ID.
If project ID is not provided, for example:myDataset.myResult. User's billing project will be used.
c. Parameters can be used.
d. User must have read permission to the source table.
e. The schema of the source table must match that of the destination table.
Destination DB
3. Specify the Destination DB. 
a. It has the format of host:port/database_name.
The port can be omitted if it is the default.
b. Parameters can be used.
Destination Table
4. Specify the Destination Table. 
a. The destination table must already exist in the MySQL database.
b. Parameters can be used.
c. The schema of the destination table must match that of the source table.
Write Preferences
5. Specify Write Preferences. 
a. Append to table: This appends data from the source table to the destination table.
b. Overwrite table: This first truncates the destination table, and then it inserts data from the source table to the destination table.
c. Replace data: This first deletes those data that meet the "Replace Data Where" condition from the destination table. Then it inserts data from the source table to the destination table. Parameters can be used when specifying the "Replace Data Where" condition.
Credentials
6. Specify the Credentials.
a. Click on the link Credentials.
b. Specify Username and Password.
i. Parameters can be used for Username and Password.
ii. Note: if the password contains reference to a parameter and that parameter exists in the workflow, parameter substitution will apply. For example, if the password is p<var_n>wd and var_n happens to be a parameter, <var_n> will be substituted with the parameter value.
c. Password length cannot be more than 245 in characters. The password is encrypted so there is no way to edit it. To change an existing password, a new password must be provided to replace it.
d. Validate the credential.
i. Click on the Validate button
ii. If validation is successful, this popup message will be displayed.
iii. If validation failed, the failed message will be displayed.
iv. This validation test only checks if the MySQL account specified can log into the MySQL database. Please see section “Prerequisites” to see the complete list of requirements.
e. The MySQL account specified must have DROP, DELETE, INSERT, SELECT, CREATE TEMPORARY TABLES, and FILE to the MySQL destination. See the MySQL administrator to create or grant account permissions that are needed.
Encoding
7. Specify encoding.
a. Click on the link Credentials.
b. Specify Export Encoding.
i. UTF-8 (International): This is the default encoding. This includes handling of international characters.
ii. Legacy (Lation-1): This does not handle international characters.
c. Specify MySQL Load Encoding.
i. UTF-8 (International): This is the default encoding. This includes handling of international characters.
ii. Legacy (Lation-1): This does not handle international characters.
Prerequisites
The MySQL server needs to accept connections from the Magnus backend machine. Please work with the MySQL server administrator.
For Write Preferences: “Append to table” and “Replace data”:
The MySQL account must have DELETE, INSERT, SELECT, CREATE TEMPORARY TABLES, and FILE to the MySQL destination. See the MySQL administrator to create or grant account permissions that are needed.
For Write Preference: “Overwrite table”:
The MySQL account must have DROP, DELETE, INSERT, SELECT, CREATE TEMPORARY TABLES, and FILE to the MySQL destination. See the MySQL administrator to create or grant account permissions that are needed.
Internals
1. Magnus makes a request to the Google BigQuery Tabledata: list API. This returns table data from the source BigQuery table.
2. The source table data is written to a file on the server.
3. A temporary MySQL table (z_ds_temp_<destinationTableName>) of identical schema as the destination table is created. The temporary table is dropped if it already exists.
4. The data file is loaded to the temporary MySQL table via the command "LOAD DATA LOCAL INFILE…" in batch size of 1,000,000 rows.
5. The following MySQL command is executed:
a. For "Append to table"
i. Insert into <destinationTable> select * from <tempTable>
b. For "Overwrite table"
i. truncate table <destinationTable>
ii. Insert into <destinationTable> select * from <tempTable>
c. For "Replace data"
i. delete from <destionTable> where <replaceCondition>
ii. Insert into <destinationTable> select * from <tempTable>
FAQ
1. MySQL destination table has an auto increment key column. How do I handle adding data to this table?
Answer: For the column that is auto incremented add a column to the Google BigQuery source data that include a 0 for the column. This will let MySQL know that it can auto increment as normal when the data is imported into the MySQL table.
2. I have a MySQL account and tested that I can connect locally to the MySQL server but when I try to use the MySQL Export Task I am unable to connect. Why is that?
Answer: In addition to a valid MySQL account the MySQL server needs to accept connections from the Magnus backend machine. If you continue to experience connection issues after validating your MySQL account send an email to your administrator. Provide the MySQL server name/IP address along with a request to check connectivity between MySQL server and the backend. Your administrator will work with MySQL server administrator to test and configure MySQL server to accept connections from the Magnus backend machine.