Working with Aurora-MySQL

Every time Bipost Sync sends data to AWS it works along with an API that runs 4 steps.

1. Create-Alter Schemas

  • If it doesn't exist, database is created with:

    • Name: The one you specified on Factor BI Console.

    • Encoding: cp1252 West European (latin1)

    • Collation: latin1_spanish_ci

  • Tables are created with all the columns found on source db.

  • Alter tables to match source schemas. Columns are never deleted.

  • Only fields specified in customData.json will be populated.


2. Prepare Database Before Loading

After schemas are created/altered and before new data is loaded, you can specify to run a query against your database.

This is very useful if you need to delete, truncate or make any changes before data is loaded.

Currently you need to include all desired statements in a stored procedure with the name spPostInitial and must not have parameters.

Example:

DELIMITER $$
DROP PROCEDURE IF EXISTS `spPostInitial`$$
CREATE PROCEDURE `spPostInitial`()
postinitial:BEGIN

  INSERT INTO logPostInitial (message) VALUES ('auto');

  TRUNCATE TABLE `mytesttable`;
  TRUNCATE TABLE `bipostlog`;
  TRUNCATE TABLE `movtipo`;

END$$

3. Load Data

Data loading is performed by Aurora. Rows with the same primary key are updated and the rest are inserted.

You can verify which tables where loaded by querying aurora_s3_load_history table like this:

SELECT * FROM mysql.aurora_s3_load_history WHERE file_name REGEXP 'mytablename' ORDER BY load_timestamp desc;

Optionally convert load_timestamp to your local time, e.g.: CONVERT_TZ(load_timestamp,'UTC','America/Mexico_City')


4. Transform Data After Loading

After new data is loaded to your Aurora-MySQL database, and before it begins the downloading process to your on-prem, you can specify to run a query.

This is very handy if you need to execute several routines and, for example, populate new tables.

It is also very useful to prepare tables with data sets for the downloading process back to on-premises.

Currently you need to include all desired statements in a stored procedure with the name spPostFinal and must not have parameters.

Example:

DELIMITER $$
DROP PROCEDURE IF EXISTS `spPostFinal`$$
CREATE PROCEDURE `spPostFinal`()
postfinal:BEGIN

  REPLACE INTO dateInfo (tag, cDate)
  SELECT 'yesterday', fnDateInfo('yesterday',fnServiceDate());

  REPLACE INTO ymInfo (tag, y, m)
  SELECT 'current', YEAR(fnDateInfo('yesterday',fnServiceDate())), MONTH(fnDateInfo('yesterday',fnServiceDate()));

  call spPopulateMyOtherTables;

  INSERT INTO logPostFinal (message) VALUES ('auto');

END$$