def truncateTable():
databaseip = rdDet.getDevIP()
print(databaseip)
print("Pointing the script towards " + str(databaseip))
query = "TRUNCATE TABLE t_ticketDataAnalysis;"
try:
#mysqlConnection
con = mdb.connect(databaseip, 'admin', 'Welcome123$', 'vast')
cur = con.cursor()
cur.execute("SELECT VERSION();")
ver = cur.fetchone()
print(ver)
cur.execute(query)
con.commit()
except Exception as e:
print("Error %s" % str(e.args))
sys.exit(1)
def loadTickets(file):
query = "LOAD DATA LOCAL INFILE '" + str(
file
) + "' INTO TABLE `t_ticketDataAnalysis` FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';"
databaseip = rdDet.getDevIP()
print("Pointing the script towards " + str(databaseip))
try:
#mysqlConnection
con = mdb.connect(databaseip, 'admin', 'Welcome123$', 'vast')
cur = con.cursor()
cur.execute("SELECT VERSION();")
ver = cur.fetchone()
print(ver)
cur.execute(query)
con.commit()
except Exception as e:
print("Error %s" % str(e.args))
sys.exit(1)
def deleteOldTablesFromData():
databaseip = rdDet.getDevIP()
print("Pointing the script towards " + databaseip)
try:
#mysqlConnection
con = mdb.connect(databaseip, 'admin', 'Welcome123$', 'vast')
cur = con.cursor()
cur.execute("SELECT VERSION();")
ver = cur.fetchone()
print(ver)
dateFromQ = "SELECT ConfigValue FROM t_Config WHERE ConfigKey = 'VastEmediaHistoryToKeepDays';"
cur.execute(dateFromQ)
dateFrom = cur.fetchone()
start_date = (date.today() -
timedelta(int(dateFrom[0]))).strftime('%Y-%m-%d')
start_date_string = dt.strptime(start_date, '%Y-%m-%d')
print("We will Delete Tables before: " + str(start_date_string))
getTableDates = "SELECT TABLE_NAME, REPLACE(TABLE_NAME,'t_VastEmedia_','') FROM information_schema.tables where TABLE_SCHEMA = 'vast' and TABLE_NAME like 't_VastEmedia_%' and DATE(REPLACE(TABLE_NAME,'t_VastEmedia_','')) <= '" + str(
start_date_string) + "';"
cur.execute(getTableDates)
deleteTableNames = cur.fetchall()
for tableName in deleteTableNames:
print("Deleteing table: " + tableName[0])
deleteQuery = "DROP TABLE `" + tableName[0] + "`;"
print(deleteQuery)
cur.execute(deleteQuery)
con.commit()
deleteFromLog = "DELETE FROM t_FileLog where FileDate = '" + tableName[
1] + "'"
print(deleteFromLog)
cur.execute(deleteFromLog)
except Exception as e:
print("Error %s" % str(e.args))
sys.exit(1)
def updateLoadLog():
pwd = os.path.abspath('..')
key_file = pwd + '/key/pk.ppk' # NOT .pub
host = 'sftp.barb.2cnt.net'
port = 22
username = '******'
ip = socket.gethostbyname(socket.gethostname())
prodip = '172.16.193.111'
databaseip = devDet.getDevIP()
print(databaseip)
print("Pointing the script towards " + databaseip)
try:
#mysqlConnection
con = mdb.connect(databaseip, 'admin', 'Welcome123$', 'vast')
cur = con.cursor()
cur.execute("SELECT VERSION();")
ver = cur.fetchone()
print(ver)
print("Getting file list from SFTP....")
my_key = paramiko.RSAKey.from_private_key_file(key_file)
transport = paramiko.Transport((host, port))
transport.connect(username=username, pkey=my_key)
sftp = paramiko.SFTPClient.from_transport(transport)
dateFromQ = "SELECT ConfigValue FROM t_Config WHERE ConfigKey = 'VastSftpMetaDataToGet';"
cur.execute(dateFromQ)
dateFrom = cur.fetchone()
start_date = (date.today() -
timedelta(int(dateFrom[0]))).strftime('%Y-%m-%d')
start_date_string = dt.strptime(start_date, '%Y-%m-%d')
print("We will look for files after: " + str(start_date_string))
print(sftp.listdir())
folder_list = sftp.listdir(path='/usage')
print(len(folder_list))
for folder in folder_list:
print(folder)
if folder.startswith('usage-20'):
folderdate = folder[6:16]
datetime_object = dt.strptime(folderdate, '%Y-%m-%d')
if start_date_string < datetime_object:
rc = 0
files_in_folder = sftp.listdir_attr(path=('/usage/' +
folder))
for file in files_in_folder:
if file.filename.endswith('.gz'):
remotePath = '/usage/' + folder + '/' + file.filename
fileDate = folder[6:16]
fileHour = folder[17:19]
fileName = file.filename[0:6]
fileRevision = file.filename[7]
lastModified = datetime.datetime.fromtimestamp(
file.st_mtime)
fileSize = file.st_size
insertStatement = 'INSERT INTO t_FileLogSftp (RemotePath, FileDate, FileName, FileRevision, LastModified, FileSize, UpdateTime, FileHour) VALUES \n'
insertStatement = insertStatement + '(\'' + remotePath + '\', \'' + fileDate + '\', \'' + fileName + '\', \'' + str(
fileRevision)
insertStatement = insertStatement + '\', \'' + str(
lastModified) + '\', ' + str(
fileSize) + ', NOW(), ' + str(
fileHour) + ')\n'
insertStatement = insertStatement + 'ON DUPLICATE KEY UPDATE RemotePath = \'' + remotePath + '\', FileRevision = \'' + str(
fileRevision)
insertStatement = insertStatement + '\', LastModified = \'' + str(
lastModified) + '\', FileSize =' + str(
fileSize) + ', UpdateTime = NOW();'
cur.execute(insertStatement)
rc = rc + cur.rowcount
con.commit()
print("Commited %d rows for folder %s" %
(rc, folder))
except Exception as e:
print("Error %s" % str(e.args))
sys.exit(1)
def loadFiles():
databaseip = devDet.getDevIP()
pwd = os.path.abspath('..')
key_file = pwd + '/key/pk.ppk' # NOT .pub
host = 'sftp.barb.2cnt.net'
port = 22
username = '******'
ip = socket.gethostbyname(socket.gethostname())
try:
my_key = paramiko.RSAKey.from_private_key_file(key_file)
transport = paramiko.Transport((host, port))
transport.connect(username=username, pkey=my_key)
sftp = paramiko.SFTPClient.from_transport(transport)
con = mdb.connect(databaseip,
'admin',
'Welcome123$',
'vast',
local_infile=1)
cur = con.cursor()
cur.execute("SELECT VERSION();")
ver = cur.fetchone()
print(ver)
cur.execute("call sp_GetFileListToLoad()")
result = cur.fetchall()
if len(result) > 0:
selfInstanceID = urllib2.urlopen(
'http://169.254.169.254/latest/meta-data/instance-id').read()
response = ECTag.addLoadStatusTag('loading', selfInstanceID)
print("We have " + str(len(result)) + " files to load.")
for file in result:
truncateQuery = "TRUNCATE TABLE t_Stg_Emedia;"
print("Truncating Table " + truncateQuery)
cur.execute(truncateQuery)
con.commit()
print("Start Loading " + file[0])
filepath = file[0]
localpath = '../temp/' + file[2]
rowcount = 0
print("Downloading File....")
sftp.get(filepath, localpath)
print("File Downloaded")
with gzip.open(localpath, 'rb') as f_in:
print("GZIPR OPEN")
with open(localpath.replace('.gz', ''), 'wb') as f_out:
print("COPY FILE")
shutil.copyfileobj(f_in, f_out)
print("COPIED FILE")
f_out.close()
f_in.close()
loadquer = "LOAD DATA LOCAL INFILE '" + os.getcwd(
) + "/" + f_out.name + "' INTO TABLE t_Stg_Emedia FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n';"
print(os.getcwd())
print(loadquer)
cur.execute(loadquer)
con.commit()
loadToTable = "CALL sp_Loadfile(NULL,'" + file[
0] + "','" + str(file[3]) + "','" + str(file[4]) + "');"
print(loadToTable)
cur.execute(loadToTable)
con.commit()
ECTag.addLoadStatusTag('complete', selfInstanceID)
else:
selfInstanceID = urllib2.urlopen(
'http://169.254.169.254/latest/meta-data/instance-id').read()
ECTag.addLoadStatusTag('complete', selfInstanceID)
print("No more files to load")
except Exception as e:
print("Error %s" % str(e.args))
sys.exit(1)