//obtiene las variables del sitio operativo
public static void ExtractAndLoad(OperationalVariable variable)
{
DateTime initTime = DateTime.Now;
DateTime finishTime = DateTime.Now;
//conexion al central para saber el estado del storage y si es necesario crearlo
MySqlConnection connCentralEDR = new MySqlConnection(centralEDRconnStr);
try
{
Console.WriteLine("Extracting... " + variable.StorageName);
connCentralEDR.Open();
connCentralEDR.ChangeDatabase(DefaultCentralSchema);
string EXISTE_STORAGE = "SELECT COUNT(*) AS count " +
"FROM information_schema.tables " +
"WHERE table_schema = '{0}' " +
"AND table_name = '{1}'";
EXISTE_STORAGE = string.Format(EXISTE_STORAGE, DefaultCentralSchema, variable.StorageName);
string CREATE_STORAGE = "CREATE TABLE {0}( " +
"id_site int(11) DEFAULT NULL, " +
"id_variable int(11) DEFAULT NULL," +
"value smallint(4) DEFAULT NULL," +
"ts timestamp(6) ," +
"mts smallint(6) DEFAULT NULL" +
")" +
"ENGINE = INNODB," +
"CHARACTER SET utf8," +
"COLLATE utf8_spanish_ci," +
"COMMENT = 'Tabla para la variable {0}'" +
"PARTITION BY KEY(id_site)" +
"(" +
"PARTITION partitionby_site ENGINE = INNODB" +
");" +
"ALTER TABLE {0} " +
"ADD INDEX UK_{0}(id_site);";
CREATE_STORAGE = string.Format(CREATE_STORAGE, variable.StorageName);
//configuramos la sentencia de Extraccion de datos del Site Remoto
string EXTRACT_QUERY = "SELECT DISTINCT value, ts, mts FROM {0}.{1} WHERE ts > '{2}' ORDER BY ts ASC LIMIT 20000";
EXTRACT_QUERY = string.Format(EXTRACT_QUERY
, SiteConf.SiteSchema
, variable.StorageName
, variable.StringLastSync);
MySqlCommand cmd = new MySqlCommand(EXISTE_STORAGE, connCentralEDR);
MySqlDataReader rdr = cmd.ExecuteReader();
bool existeStorage = false;
if (rdr.Read())
{
existeStorage = rdr.GetInt16("count") == 1;
}
Console.WriteLine("Existe Storage? " + existeStorage.ToString());
rdr.Close();
if (!existeStorage)
{
cmd.CommandText = string.Format(CREATE_STORAGE, variable.StorageName);
Console.WriteLine("CREATE_STORAGE... " + CREATE_STORAGE);
cmd.ExecuteNonQuery();
Console.WriteLine("CREATE_STORAGE... Ok");
}
//ahora nos conectamos al sitio remoto para extraer los datos
MySqlConnection connOnSite = new MySqlConnection(onSiteEDRconnStr);
DataTable tblOrigen = new DataTable();
DateTime? LastTimeStamp = variable.LastSync;
string remark = string.Empty;
try
{
connOnSite.Open();
tblOrigen = GetDataTableLayout(variable.StorageName, connOnSite);
tblOrigen.Columns.Add("id_site");
//tblOrigen.NewRow();
Console.WriteLine("EXTRACT_QUERY...");
MySqlCommand cmdExtract = new MySqlCommand(EXTRACT_QUERY, connOnSite);
MySqlDataReader etlReader = cmdExtract.ExecuteReader();
Console.WriteLine("EXTRACT_QUERY...Ok");
while (etlReader.Read())
{
var r = tblOrigen.NewRow();
r["id_site"] = SiteConf.IdSite;
r["id_variable"] = 0;
r["value"] = etlReader.GetFloat("value");
r["ts"] = etlReader.GetDateTime("ts");
r["mts"] = etlReader.GetFloat("mts");
tblOrigen.Rows.Add(r);
//Console.WriteLine(r["value"].ToString() + " | " + r["ts"].ToString());
r = null;
rowExtractCount++;
LastTimeStamp = etlReader.GetDateTime("ts"); //guardamos el valor del time stamp para obtener el ultimo para la siguiente actualización incremental
}
if (rowExtractCount == 0)
{
remark = "-No hay datos desde la última sincronización";
}
}
catch (Exception innerEx)
{
Console.WriteLine(innerEx.ToString());
}
finally
{
if (connOnSite.State == ConnectionState.Open)
{
connOnSite.Close();
}
}
Console.WriteLine("Loading... " + variable.StorageName);
currentVariable = variable.StorageName;
rowInsertedCount = BulkInsertMySQL(tblOrigen, variable.StorageName, connCentralEDR);
remark = "Ok" + remark;
//update delta
string UPDATE_DELTA = "UPDATE delta " +
"SET row_count_source = {0} " +
", row_count_destiny = '{1}' " +
", row_last_timestamp = '{2}' " +
", finish_date = NOW() " +
", remark = '{3}' " +
"WHERE id_delta = {4}";
UPDATE_DELTA = string.Format(UPDATE_DELTA
, rowExtractCount //número de filas recuperadas
, rowInsertedCount //nùmero de filas insertadas
, ETLHelper.ConvertDateToYYYMMDD(LastTimeStamp)
, remark
, variable.DeltaID
);
MySqlCommand cmdUpdateDelta = new MySqlCommand(UPDATE_DELTA, connCentralEDR);
cmdUpdateDelta.ExecuteNonQuery();
connCentralEDR.Close();
finishTime = DateTime.Now;
long elapsedTime = finishTime.Ticks - initTime.Ticks;
TimeSpan elapsedSpan = new TimeSpan(elapsedTime);
Console.WriteLine("ETL " + variable.StorageName + " - Start: " + initTime.ToString() + " Finish:" + finishTime.ToString() + " Elapsed time: " + elapsedSpan.TotalSeconds.ToString());
//Console.WriteLine("Row count: " + rowCount.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
if (connCentralEDR.State == ConnectionState.Open)
{
connCentralEDR.Close();
}
}
}
//obtiene la configuración de conexión al sitio operativo y su última
public static DateTime?GetLastSync(OperationalVariable variable)
{
DateTime initTime = DateTime.Now;
DateTime finishTime = DateTime.Now;
DateTime?result;
MySqlConnection conn = new MySqlConnection(centralEDRconnStr);
try
{
//Console.WriteLine("GetLastSync - " + variable.StorageName);
conn.Open();
conn.ChangeDatabase(DefaultCentralSchema);
string SQL_LAST_SYNC = "SELECT MAX(row_last_timestamp) FROM delta WHERE storage_name = '{0}'";
SQL_LAST_SYNC = string.Format(SQL_LAST_SYNC, variable.StorageName);
MySqlCommand cmd = new MySqlCommand(SQL_LAST_SYNC, conn);
Object rdr = cmd.ExecuteScalar();
//no hay datos en la tabla delta, es carga completa (primera vez)
if (rdr.GetType().Name.Equals("DBNull"))
{
variable.LastSync = DateTime.MinValue;
result = DateTime.Now.AddYears(-1); //simulamos una fecha anterior a un año
}
//ya hay registro de cargas anteriores, es una carga incremental
else
{
//SELECCIONAR REGISTROS DESDE LA ULTIMA SINCRONIZACION A LA FECHA
variable.LastSync = (DateTime)rdr;
result = (DateTime)rdr;
}
rdr = null;
//insertamos en el delta el control de este proceso de integración
string INSERT_DELTA = "INSERT INTO delta(id_site, storage_name, start_date) " +
"VALUES( " +
" {0} " + //se obtiene de la configuración del Site
", '{1}' " + //Se obtiene de la variable actual
", NOW() " + //Default del servidor
")";
cmd.CommandText = string.Format(INSERT_DELTA, SiteConf.IdSite, variable.StorageName);
cmd.ExecuteNonQuery();
string DELTA_ID = "SELECT LAST_INSERT_ID()";
cmd.CommandText = DELTA_ID;
Object lastID = cmd.ExecuteScalar();
if (!lastID.GetType().Name.Equals("DBNull"))
{
variable.DeltaID = (UInt64)lastID;
}
finishTime = DateTime.Now;
long elapsedTime = finishTime.Ticks - initTime.Ticks;
TimeSpan elapsedSpan = new TimeSpan(elapsedTime);
Console.WriteLine("GetLastSync - " + variable.StorageName + " - Start: " + initTime.ToString() + " - Finish:" + finishTime.ToString() + " - Elapsed time: " + elapsedSpan.TotalSeconds.ToString());
return(result);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
return(null);
}
finally
{
conn.Close();
}
}