Bipost API

Bipost API runs with Lambda. It has 4 main stages which are described next.

Create-Alter Schemas

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

    • Name: Over email we ask for this name for each Service No. provided.

    • Encoding: cp1252 West European (latin1)

    • Collation: latin1_spanish_ci

  • Tables are created with the full set of fields found on source db.

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

  • Fields will appear on a different position as the source db.

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


Initial Statement

After the schemas are created (or checked if they exist) and before new data is loaded, you can specify to run a query on 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.

Please let us know over email to activate this stored procedure for a given Service No.

Example:

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

  TRUNCATE TABLE `mytesttable`;

END$$

Load Data

Data loading is performed by Aurora using LOAD DATA FROM S3 statement with REPLACE parameter. This technique ensures that all data is loaded with great performance.

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

select * from mysql.aurora_s3_load_history where file_name regexp 'mytablename';

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

After data is loaded, it always checks to create and populate these objects:

Name Type
T Table
time Table
ym Table
dowhile Stored Procedure
spCreateTime Stored Procedure
spCreateYM Stored Procedure
sp_createIndex Stored Procedure

Final Statement

After new data is loaded to your database, you can specify to run a query.

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

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

Please let us know over email to activate this stored procedure for a given Service No.

Example:

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

  INSERT INTO mytable (message) VALUES ('my_message');

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

END$$