//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(); } }