def GetFiles(StatsStructureId, maxFilesToprocess):
dbhost = 'callhomelab-vertica01'
dbuser = '******'
passwd = 'c@llhome'
dbsource = 'callhomedb'
sql = 'SELECT STATS_OUTPUTFILE_NAME,STATS_OUTPUTFILE_PATH,SUBSTR(STATS_OUTPUTFILE_NAME,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,1) + 1,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,2) - INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,1) -1) INSERVSERIAL,SUBSTR(STATS_OUTPUTFILE_NAME,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,2) + 1,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,3) - INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,2) -1) ||\' \'||SUBSTR(STATS_OUTPUTFILE_NAME,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,3) + 1,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,4) - INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,3) -1) DATDATE,STATS_OUTPUTFILE_ID,TO_CHAR(TO_TIMESTAMP(SUBSTR(STATS_OUTPUTFILE_NAME,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,2) + 1,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,3) - INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,2) -1) ||\' \'||SUBSTR(STATS_OUTPUTFILE_NAME,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,3) + 1,INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,4) - INSTR(STATS_OUTPUTFILE_NAME,\'.\',1,3) -1),\'YYmmdd HH24MISS\'),\'MM/DD/YYYY HH24:MI:SS\') datadatetype FROM datastore.STATSOUTPUT WHERE STATS_STRUCTURE_TYPE_ID=' + str(
StatsStructureId) + ' AND NVL(PROCESSED_STATUS,0)=0 LIMIT ' + str(
maxFilesToprocess)
conn = vconn.vertica_connect(dbhost, 5433, dbuser, passwd, dbsource)
vdat = vconn.vertica_sql_execute(conn, sql)
dbresulSet = vdat.fetchall()
fileList = []
for rec in dbresulSet:
flrec = []
flrec.append(str(rec[0]))
flrec.append(str(rec[1]))
flrec.append(str(rec[2]))
flrec.append(str(rec[3]))
flrec.append(str(rec[4]))
flrec.append(str(rec[5]))
fileList.append(flrec)
vdat.close()
conn.close()
return fileList
def updateLoadStatus(FileId, status, logfilename):
try:
sql = 'UPDATE DATASTORE.STATSOUTPUT SET STATS_FILE_LOAD_STATUS=' + str(
status
) + ', STATS_FILE_CREATE_DATE=now() WHERE STATS_OUTPUTFILE_ID=' + str(
FileId)
conn = vconn.vertica_connect('callhomelab-vertica01', 5433, 'dbadmin',
'c@llhome', 'callhomedb')
vdat = vconn.vertica_sql_execute(conn, sql)
conn.commit()
sql = 'UPDATE DATASTORE.VERTICA_COPY SET STATUS=' + str(
status) + ', EXECUTE_DATE=now() WHERE STATS_OUTPUTFILE_ID=' + str(
FileId)
vdat = vconn.vertica_sql_execute(conn, sql)
conn.commit()
vdat.close()
conn.close()
except:
logger("Error reported: " + str(sys.exc_info()[1]), logfilename)
def GetVerticaRowCount():
try:
conn = vconn.vertica_connect('callhomelab-vertica01', 5433, 'dbadmin',
'c@llhome', 'callhomedb')
sql = 'SELECT COUNT(1) FROM DATASTORE.STATSOUTPUT'
vdat = vconn.vertica_sql_execute(conn, sql)
datres = vdat.fetchall()
recount = 0
for datrec in datres:
recount = datrec[0]
return recount
except:
print("Error reported: " + str(sys.exc_info()[1]))
def UpdateProcessDoneInVertica(fileId, STATUS, logfilename):
try:
conn = vconn.vertica_connect('callhomelab-vertica01', 5433, 'dbadmin',
'c@llhome', 'callhomedb')
sql = 'UPDATE DATASTORE.STATSOUTPUT SET PROCESSED_STATUS=' + str(
STATUS) + ',PROCESSED_DATE=now() WHERE STATS_OUTPUTFILE_ID=' + str(
fileId)
vdat = vconn.vertica_sql_execute(conn, sql)
conn.commit()
vdat.close()
conn.close()
except:
logger("Error reported: " + str(sys.exc_info()[1]), logfilename)
def GetMaxFileIDfromVertica():
try:
conn = vconn.vertica_connect('callhomelab-vertica01', 5433, 'dbadmin',
'c@llhome', 'callhomedb')
sql = 'SELECT COUNT(1) FROM DATASTORE.STATSOUTPUT'
vdat = vconn.vertica_sql_execute(conn, sql)
datres = vdat.fetchall()
for datrec in datres:
recount = datrec[0]
if recount > 0:
sql = 'select max(STATS_OUTPUTFILE_ID) from datastore.STATSOUTPUT'
vdat = vconn.vertica_sql_execute(conn, sql)
valres = vdat.fetchall()
for valrec in valres:
maxVal = valrec[0]
else:
maxVal = 0
vdat.close()
conn.close()
return maxVal
except:
print("Error reported: " + str(sys.exc_info()[1]))
def CheckIdinVertica(statsoutputid):
try:
conn = vconn.vertica_connect('callhomelab-vertica01', 5433, 'dbadmin',
'c@llhome', 'callhomedb')
sql = 'SELECT COUNT(1) FROM DATASTORE.STATSOUTPUT WHERE STATS_OUTPUTFILE_ID=' + str(
statsoutputid)
vdat = vconn.vertica_sql_execute(conn, sql)
datres = vdat.fetchall()
recount = 0
for datrec in datres:
recount = datrec[0]
return recount
except:
print("Error reported: " + str(sys.exc_info()[1]))
def GetMaxCreateDate():
try:
conn = vconn.vertica_connect('callhomelab-vertica01', 5433, 'dbadmin',
'c@llhome', 'callhomedb')
sql = 'SELECT COUNT(1) FROM DATASTORE.STATSOUTPUT'
vdat = vconn.vertica_sql_execute(conn, sql)
datres = vdat.fetchall()
for datrec in datres:
recount = datrec[0]
if recount > 0:
sql = 'SELECT TO_CHAR(MAX(STATS_FILE_CREATE_DATE),\'YYYYMMDDHH24MISS\') from datastore.STATSOUTPUT'
vdat = vconn.vertica_sql_execute(conn, sql)
valres = vdat.fetchall()
for valrec in valres:
maxdate = valrec[0]
else:
maxdate = 0
vdat.close()
conn.close()
return maxdate
except:
print("Error reported: " + str(sys.exc_info()[1]))
def checkinvertica(fileid):
try:
conn=vconn.vertica_connect('callhomelab-vertica01',5433,'dbadmin','c@llhome','callhomedb');
sqlstmt='select count(1) from datastore.eventlog where fileid='+str(fileid);
vdat=vconn.vertica_sql_execute(conn,sqlstmt);
datres=vdat.fetchall();
for datrec in datres:
reccount=datrec[0];
vdat.close();
conn.close();
return reccount;
except:
print("Error reported: "+str(sys.exc_info()[1]));
def GetLoads():
maxLoads = 10000
sql = 'SELECT STATS_OUTPUTFILE_ID,COPYSTMT FROM DATASTORE.VERTICA_COPY WHERE NVL(STATUS,0)=0 LIMIT ' + str(
maxLoads)
conn = vconn.vertica_connect('callhomelab-vertica01', 5433, 'dbadmin',
'c@llhome', 'callhomedb')
vdat = vconn.vertica_sql_execute(conn, sql)
datresult = vdat.fetchall()
loaddat = []
for drec in datresult:
loadrec = []
loadrec.append(drec[0])
loadrec.append(drec[1])
loaddat.append(loadrec)
return loaddat