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