def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists psLeps_small cascade")
cur.execute("drop table if exists tightLeps_small cascade")
cur.execute("create table psLeps_small as ( (select Run, Lumi, Event, SampleID, Pt, Eta, Phi, charge from preselected_muons_small) UNION ALL (select Run, Lumi, Event, SampleID, Pt, Eta, Phi, charge from preselected_electrons_small))")
cur.execute("create table tightLeps_small as ( (select Run, Lumi, Event, SampleID, Pt, Eta, Phi, charge from tightMVABased_muons_small) UNION ALL (select Run, Lumi, Event, SampleID, Pt, Eta, Phi, charge from tightMVABased_electrons_small))")
cur.execute("commit")
tableTime = time.time() - startTime
queryStart = time.time()
#query = " select Pt from jets;"
#cur.execute(query)
queryTime = time.time() - queryStart
totalTime = time.time() - startTime
print('Fill table: {}\nQuery time: {}\nTotal: {}'.format(tableTime, queryTime, totalTime) )
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists jets cascade")
cur.execute("drop table if exists leptons cascade")
cur.execute("drop table if exists event_info cascade")
cur.execute("create table event_info (Run int, Lumi int, Event int, PRIMARY KEY (Run, Lumi, Event))")
cur.execute("create table jets (Run int, Lumi int, Event int, Pt float, Eta float, Phi float, Charge float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event) REFERENCES event_info(Run, Lumi, Event))" )
cur.execute("create table leptons (Run int, Lumi int, Event int, Pt float, Eta float, Phi float, Charge float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event) REFERENCES event_info(Run, Lumi, Event))" )
cur.execute("copy event_info from '/home/newdbadmin/schema_test/event.txt' parser fdelimitedparser()")
cur.execute("copy leptons from '/home/newdbadmin/schema_test/lep.txt' parser fdelimitedparser()")
cur.execute("copy jets from '/home/newdbadmin/schema_test/jet.txt' parser fdelimitedparser()")
cur.execute("commit")
tableTime = time.time() - startTime
queryStart = time.time()
query = " select Pt from jets;"
cur.execute(query)
queryTime = time.time() - queryStart
totalTime = time.time() - startTime
print('Fill table: {}\nQuery time: {}\nTotal: {}'.format(tableTime, queryTime, totalTime) )
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists tempTable")
cur.execute("drop table if exists tempTable2")
cur.execute("DROP TABLE IF EXISTS tempTable3")
my_file = ROOT.TFile("test_2.root", "RECREATE")
histJetPt = ROOT.TH1F("jetpt", "Pt", 100,0,200)
lepPtHistSig = ROOT.TH1F("lepPt","Lepton Pt",100,0,200)
lepEtaHistSig = ROOT.TH1F("lepEta","Lepton #eta",100,-2,2)
lepPhiHistSig = ROOT.TH1F("lepPhiHistSig","Lepton #phi",100,-4,4)
jetPt_bucket = ROOT.TH1F("jetPt_bucket","jet Pt",100,0,200)
fill_hist_from_query(histJetPt,"select Pt from jets",cur)
fill_hist_from_query(lepPtHistSig,"select Pt from leptons",cur)
fill_hist_from_query(lepEtaHistSig, "select Eta from leptons where Pt >35",cur)
fill_hist_from_widthBucket(jetPt_bucket,100, "SELECT WIDTH_BUCKET(Pt,0,200,100), COUNT(WIDTH_BUCKET(PT,0,200,100)) FROM jets GROUP BY WIDTH_BUCKET(PT,0,200,100)", cur)
my_file.Write()
my_file.Close()
cur.execute("DROP TABLE IF EXISTS tempTable")
#cur.execute("DROP TABLE tempTable2")
cur.execute("DROP TABLE IF EXISTS tempTable3")
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists tempTable")
cur.execute("drop table if exists tempTable2")
cur.execute("DROP TABLE IF EXISTS tempTable3")
my_file = ROOT.TFile("test_2.root", "RECREATE")
histJetPt = ROOT.TH1F("jetpt", "Pt", 100, 0, 200)
lepPtHistSig = ROOT.TH1F("lepPt", "Lepton Pt", 100, 0, 200)
lepEtaHistSig = ROOT.TH1F("lepEta", "Lepton #eta", 100, -2, 2)
lepPhiHistSig = ROOT.TH1F("lepPhiHistSig", "Lepton #phi", 100, -4, 4)
jetPt_bucket = ROOT.TH1F("jetPt_bucket", "jet Pt", 100, 0, 200)
fill_hist_from_query(histJetPt, "select Pt from jets", cur)
fill_hist_from_query(lepPtHistSig, "select Pt from leptons", cur)
fill_hist_from_query(lepEtaHistSig, "select Eta from leptons where Pt >35",
cur)
fill_hist_from_widthBucket(
jetPt_bucket, 100,
"SELECT WIDTH_BUCKET(Pt,0,200,100), COUNT(WIDTH_BUCKET(PT,0,200,100)) FROM jets GROUP BY WIDTH_BUCKET(PT,0,200,100)",
cur)
my_file.Write()
my_file.Close()
cur.execute("DROP TABLE IF EXISTS tempTable")
#cur.execute("DROP TABLE tempTable2")
cur.execute("DROP TABLE IF EXISTS tempTable3")
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists demonstrator cascade")
makeTable = "create table demonstrator (row int, instance int, preselect float)"
cur.execute(makeTable)
cur.execute(
"copy demonstrator from '/home/newdbadmin/demonstrator/verticaInput4.txt' parser fdelimitedparser()"
)
cur.execute("commit")
tableTime = time.time() - startTime
queryStart = time.time()
query = " select preselect from demonstrator;"
cur.execute(query)
queryTime = time.time() - queryStart
totalTime = time.time() - startTime
print('Fill table: {}\nQuery time: {}\nTotal: {}'.format(
tableTime, queryTime, totalTime))
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS jets_v2")
cur.execute("DROP TABLE IF EXISTS sum_jet_vars")
cur.execute("DROP TABLE IF EXISTS sum_lep_vars")
cur.execute("DROP TABLE IF EXISTS jets_v3")
cur.execute("DROP TABLE IF EXISTS jets_v4")
cur.execute(
"CREATE TABLE jets_v2 AS SELECT Run, Lumi, Event, Pt, Eta, Phi, Pt*Pt as Ptsq FROM jets"
)
cur.execute("ALTER TABLE jets DROP Ptsq")
cur.execute("ALTER TABLE jets ADD COLUMN PtSq float DEFAULT Pt*Pt")
cur.execute(
"CREATE TABLE sum_jet_vars AS SELECT Run, Lumi, Event, SUM(Pt) as sum_pt, SUM(Pt*Pt) AS sum_pt_sq FROM jets GROUP BY Run, Lumi, Event"
)
cur.execute(
"CREATE TABLE sum_lep_vars AS SELECT leptons.Run AS Run, leptons.Lumi AS Lumi, leptons.Event AS Event, SUM(Leptons.Pt) as sum_pt, SUM(jets.Pt + leptons.Pt) AS sum_jet_lep_pt FROM jets, leptons WHERE jets.Run = leptons.Run AND jets.Lumi = leptons.Lumi AND jets.Event = leptons.Event GROUP BY leptons.Run, leptons.Lumi, leptons.Event"
)
cur.execute("CREATE TABLE jets_v3 AS SELECT *,Pt*Pt*Pt as Pt3 FROM jets")
cur.execute(
"CREATE TABLE jets_v4 AS SELECT *, CASE WHEN Pt > 30 THEN 1 ELSE 0 END AS check FROM jets"
)
cur.execute("commit")
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS jets_v2")
cur.execute("DROP TABLE IF EXISTS sum_jet_vars")
cur.execute("DROP TABLE IF EXISTS sum_lep_vars")
cur.execute("DROP TABLE IF EXISTS jets_v3")
cur.execute("DROP TABLE IF EXISTS jets_v4")
cur.execute("CREATE TABLE jets_v2 AS SELECT Run, Lumi, Event, Pt, Eta, Phi, Pt*Pt as Ptsq FROM jets")
cur.execute("ALTER TABLE jets DROP Ptsq")
cur.execute("ALTER TABLE jets ADD COLUMN PtSq float DEFAULT Pt*Pt")
cur.execute("CREATE TABLE sum_jet_vars AS SELECT Run, Lumi, Event, SUM(Pt) as sum_pt, SUM(Pt*Pt) AS sum_pt_sq FROM jets GROUP BY Run, Lumi, Event")
cur.execute("CREATE TABLE sum_lep_vars AS SELECT leptons.Run AS Run, leptons.Lumi AS Lumi, leptons.Event AS Event, SUM(Leptons.Pt) as sum_pt, SUM(jets.Pt + leptons.Pt) AS sum_jet_lep_pt FROM jets, leptons WHERE jets.Run = leptons.Run AND jets.Lumi = leptons.Lumi AND jets.Event = leptons.Event GROUP BY leptons.Run, leptons.Lumi, leptons.Event")
cur.execute("CREATE TABLE jets_v3 AS SELECT *,Pt*Pt*Pt as Pt3 FROM jets")
cur.execute("CREATE TABLE jets_v4 AS SELECT *, CASE WHEN Pt > 30 THEN 1 ELSE 0 END AS check FROM jets")
cur.execute("commit")
def main():
conn = hp_vertica_client.connect("")
cursor = conn.cursor()
test_query = "select Gondor from fakeData"
cursor.execute(test_query)
print "Test Query results: {0}".format(cursor.fetchall())
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists tempTable")
cur.execute("drop table if exists tempTable2")
cur.execute("DROP TABLE IF EXISTS tempTable3")
cur.execute("DROP TABLE IF EXISTS jetCosPhi")
my_file = ROOT.TFile("test_1.root", "RECREATE")
histJetPt = ROOT.TH1F("jetpt", "Pt", 100,0,200)
lepPtHistSig = ROOT.TH1F("lepPt","Lepton Pt",100,0,200)
lepEtaHistSig = ROOT.TH1F("lepEta","Lepton #eta",100,-2,2)
lepPhiHistSig = ROOT.TH1F("lepPhiHistSig","Lepton #phi",100,-4,4)
jetPt_2lep = ROOT.TH1F("jetPt_2lep","Jet Pt",100,0,200)
jetPt_2lep2 = ROOT.TH1F("jetPt_2lep2","Jet Pt",100,0,200)
jetPt_2lepCharge = ROOT.TH1F("jetPt_2lepCharge","Jet Pt",100,0,200)
jetPt_2lepChargeJet = ROOT.TH1F("jetPt_2lepChargeJet","Jet Pt",100,0,200)
jet2Pt40 = ROOT.TH1F("jet2Pt40","nJets >= 2 Pt>40",100,0,200)
jetCosPhi = ROOT.TH1F("jetCosPhi","Cos(phi)",100,-2,2)
fill_hist_from_query(histJetPt,"select Pt from jets",cur)
fill_hist_from_query(lepPtHistSig,"select Pt from leptons",cur)
fill_hist_from_query(lepEtaHistSig, "select Eta from leptons where Pt >35",cur)
cur.execute("CREATE TEMPORARY TABLE tempTable(Event, sumCharge) ON COMMIT PRESERVE ROWS AS select event_info.Event, SUM(leptons.Charge) from event_info left outer join leptons on event_info.Event = leptons.Event group by event_info.Event having count(leptons.Event) = 2" )
fill_hist_from_query(jetPt_2lep, "select Pt from jets where Event in (select event_info.Event from event_info left outer join leptons on event_info.Event = leptons.Event group by event_info.Event having count(leptons.Event) = 2)",cur)
fill_hist_from_query(jetPt_2lep2, "select Pt from jets where Event IN (SELECT Event FROM tempTable)",cur)
#cur.execute("CREATE TEMPORARY TABLE tempTable2(Event) ON COMMIT PRESERVE ROWS AS select Event from leptons where Event in (SELECT * FROM tempTable) AND SUM(Charge) != 0 GROUP BY Event ")
fill_hist_from_query(jetPt_2lepCharge, "select Pt from jets where Event in (select event_info.Event from event_info left outer join leptons on event_info.Event = leptons.Event group by event_info.Event having count(leptons.Event) = 2) AND Event in (Select Event from tempTable where sumCharge != 0) ",cur)
#fill_hist_from_query(lepPhiHistSig,"select leptons.Phi from leptons",cur)
fill_hist_from_query(jetPt_2lepChargeJet, "select Pt from jets where Event in (select Event from tempTable) AND Event in (Select Event from tempTable where sumCharge != 0) AND Event in (SELECT event_info.Event from event_info left outer join jets on event_info.Event = jets.Event GROUP BY event_info.Event HAVING COUNT(jets.Event) > 2) ",cur)
cur.execute("CREATE TEMPORARY TABLE tempTable3(Event) ON COMMIT PRESERVE ROWS AS SELECT Event FROM jets where Pt >40 GROUP BY Event HAVING COUNT(Event) >= 2")
fill_hist_from_query(jet2Pt40, "SELECT Pt from jets WHERE Event in (SELECT Event FROM tempTable3)",cur)
cur.execute("CREATE TABLE jetCosPhi(Event, CosPhi) AS SELECT Event, CASE WHEN min = 0 THEN 0 WHEN neg%2 =1 THEN -1*EXP(prod) ELSE EXP(prod) END FROM ( SELECT Event, SUM(LN(ABS(COS(Phi)))) AS prod, SUM(CASE WHEN COS(Phi) < 0 THEN 1 ELSE 0 END) AS neg, MIN(ABS(COS(Phi))) as min FROM jets GROUP BY Event)subQ ")
fill_hist_from_query(jetCosPhi, "SELECT CosPhi FROM jetCosPhi", cur)
my_file.Write()
my_file.Close()
cur.execute("DROP TABLE tempTable")
#cur.execute("DROP TABLE tempTable2")
cur.execute("DROP TABLE tempTable3")
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists tempTable")
cur.execute("drop table if exists tempTable2")
my_file = ROOT.TFile("test_1.root", "RECREATE")
histJetPt = ROOT.TH1F("jetpt", "Pt", 100, 0, 200)
lepPtHistSig = ROOT.TH1F("lepPt", "Lepton Pt", 100, 0, 200)
lepEtaHistSig = ROOT.TH1F("lepEta", "Lepton #eta", 100, -2, 2)
lepPhiHistSig = ROOT.TH1F("lepPhiHistSig", "Lepton #phi", 100, -4, 4)
jetPt_2lep = ROOT.TH1F("jetPt_2lep", "Jet Pt", 100, 0, 200)
jetPt_2lep2 = ROOT.TH1F("jetPt_2lep2", "Jet Pt", 100, 0, 200)
jetPt_2lepCharge = ROOT.TH1F("jetPt_2lepCharge", "Jet Pt", 100, 0, 200)
jetPt_2lepChargeJet = ROOT.TH1F("jetPt_2lepChargeJet", "Jet Pt", 100, 0,
200)
fill_hist_from_query(histJetPt, "select Pt from jets", cur)
fill_hist_from_query(lepPtHistSig, "select Pt from leptons", cur)
fill_hist_from_query(lepEtaHistSig, "select Eta from leptons where Pt >35",
cur)
cur.execute(
"CREATE TEMPORARY TABLE tempTable(Event, sumCharge) ON COMMIT PRESERVE ROWS AS select event_info.Event, SUM(leptons.Charge) from event_info left outer join leptons on event_info.Event = leptons.Event group by event_info.Event having count(leptons.Event) = 2"
)
fill_hist_from_query(
jetPt_2lep,
"select Pt from jets where Event in (select event_info.Event from event_info left outer join leptons on event_info.Event = leptons.Event group by event_info.Event having count(leptons.Event) = 2)",
cur)
fill_hist_from_query(
jetPt_2lep2,
"select Pt from jets where Event IN (SELECT Event FROM tempTable)",
cur)
#cur.execute("CREATE TEMPORARY TABLE tempTable2(Event) ON COMMIT PRESERVE ROWS AS select Event from leptons where Event in (SELECT * FROM tempTable) AND SUM(Charge) != 0 GROUP BY Event ")
fill_hist_from_query(
jetPt_2lepCharge,
"select Pt from jets where Event in (select event_info.Event from event_info left outer join leptons on event_info.Event = leptons.Event group by event_info.Event having count(leptons.Event) = 2) AND Event in (Select Event from tempTable where sumCharge != 0) ",
cur)
#fill_hist_from_query(lepPhiHistSig,"select leptons.Phi from leptons",cur)
fill_hist_from_query(
jetPt_2lepChargeJet,
"select Pt from jets where Event in (select Event from tempTable) AND Event in (Select Event from tempTable where sumCharge != 0) AND Event in (SELECT event_info.Event from event_info left outer join jets on event_info.Event = jets.Event GROUP BY event_info.Event HAVING COUNT(jets.Event) > 2) ",
cur)
my_file.Write()
my_file.Close()
cur.execute("DROP TABLE tempTable")
def main():
"""main entry point."""
# parse comman line arguments
if not sys.argv[1:]:
parse_args(args=['--help',])
args = parse_args()
# estabish connection
connection, cursor = None, None
try:
connection = driver.connect(**args.connection_options)
cursor = connection.cursor()
except driver.OperationalError as exception:
sys.exit(exception)
# validate query
if args.input:
query = args.input
try:
cursor.execute('SELECT * FROM ({}) AS T LIMIT 0'.format(query))
except driver.ProgrammingError as error:
print error
sys.exit(-1) # raise invalid query
cursor.execute(query)
if cursor.rowcount < 1:
print 'No data to export'
sys.exit(0)
# format ouput
if args.format.lower() == 'json':
output_stream = to_json(cursor, indent=args.indent)
elif args.format.lower() == 'csv':
output_stream = to_csv(cursor)
elif args.format.lower() == 'xml':
output_stream = to_xml(cursor)
else: # on case of custom implemintation of arguments parsing
raise Exception('invalid format {}'.format(args.format))
# write result
writer = sys.stdout
if args.filename:
writer = open(args.filename, 'wb')
for line in output_stream:
writer.write(line)
writer.close()
# clean up
cursor.close()
connection.close()
# exit
sys.exit(0)
def main():
conn = hp_vertica_client.connect("")
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS Moria")
cursor.execute("CREATE TABLE Moria(Event int, dwarves float, goblins float)")
cursor.execute("COPY Moria FROM '/home/newdbadmin/fakeDataTests/Moria.txt'")
test_query = "select EXP(SUM(LN(dwarves))) FROM Moria"
test_query2 = "SELECT Event, CASE WHEN min = 0 THEN 0 WHEN neg % 2 = 1 THEN -1* EXP(prod) ELSE EXP(prod) END FROM ( SELECT Event, SUM(LN(ABS(dwarves))) as prod, SUM(CASE WHEN dwarves < 0 THEN 1 ELSE 0 END) as neg, MIN(ABS(dwarves)) as min FROM Moria GROUP BY Event) subQ "
cursor.execute(test_query2)
print "Test Query results: {0}".format(cursor.fetchall())
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists preselected_electrons_scale cascade")
cur.execute("drop table if exists event_info_scale cascade")
cur.execute("DROP TABLE IF EXISTS preselected_muons_scale CASCADE")
cur.execute("drop table if exists preselected_jets_scale cascade")
#event table
cur.execute("create table event_info_scale (Run int, Lumi int, Event int, SampleID int, mcwgt float, wgt float, higgs_decay float, reco_score float, norm_score_sum float, num_real_jets_bdt float, num_jet_matches_truth float, metPt float, metPhi float, PRIMARY KEY (Run, Lumi, Event, SampleID))")
#copy event table
cur.execute("COPY event_info_scale FROM '/home/newdbadmin/skimmingPhase/scaleInput/event/*' GZIP")
#preselected electrons table
cur.execute("create table preselected_electrons_scale (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Px float, Py float, Pz float, En float, Mass float, pdgID float, dxy float, dz float, charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPrompTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, minAbsIsoNeutral float, rho float, effArea float, miniIsoR float, miniAbsIsoNeutralcorr float, SCeta float, isGsfCtfScPixChargeConsistent float, numMissingInnerHits float, passConversioVeto float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy preselected electrons table
cur.execute("COPY preselected_electrons_scale FROM '/home/newdbadmin/skimmingPhase/scaleInput/preElec/*' GZIP ")
#preselected muons table
cur.execute("CREATE TABLE preselected_muons_scale (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Px float, Py float, Pz float, En float, Mass float, pdgID float, dxy float, dz float, charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPrompTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, minAbsIsoNeutral float, rho float, effArea float, miniIsoR float, miniAbsIsoNeutralcorr float, chargeFlip float, isPFMuon float, isTrackerMuon float, isGlobalMuon float, normalizedChi2 float, numberOfValidMuonHits float, numberOfMatchedStations float, numberOfValidPixelHits float, trackerLayersWithMeasurement float, localChi2 float, trKink float, validFrac float, segCompatibility float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy preselected muons table
cur.execute("COPY preselected_muons_scale FROM '/home/newdbadmin/skimmingPhase/scaleInput/preMuon/*' GZIP")
#preselected jets
cur.execute("create table preselected_jets_scale (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Px float, Py float, Pz float, En float, Mass float, Charge float, genPdgID float, genMotherPdgID float, genGrandMotherPdgID float, csv float, qgid float, pdgID float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info_scale(Run, Lumi, Event, SampleID))" )
#copy preselected jes
cur.execute("COPY preselected_jets_scale FROM '/home/newdbadmin/skimmingPhase/scaleInput/preJet/*' GZIP ")
cur.execute("commit")
tableTime = time.time() - startTime
queryStart = time.time()
#query = " select Pt from jets;"
#cur.execute(query)
queryTime = time.time() - queryStart
totalTime = time.time() - startTime
print('Fill table: {}\nQuery time: {}\nTotal: {}'.format(tableTime, queryTime, totalTime) )
def main():
conn = hp_vertica_client.connect("")
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS Moria")
cursor.execute(
"CREATE TABLE Moria(Event int, dwarves float, goblins float)")
cursor.execute(
"COPY Moria FROM '/home/newdbadmin/fakeDataTests/Moria.txt'")
test_query = "select EXP(SUM(LN(dwarves))) FROM Moria"
test_query2 = "SELECT Event, CASE WHEN min = 0 THEN 0 WHEN neg % 2 = 1 THEN -1* EXP(prod) ELSE EXP(prod) END FROM ( SELECT Event, SUM(LN(ABS(dwarves))) as prod, SUM(CASE WHEN dwarves < 0 THEN 1 ELSE 0 END) as neg, MIN(ABS(dwarves)) as min FROM Moria GROUP BY Event) subQ "
cursor.execute(test_query2)
print "Test Query results: {0}".format(cursor.fetchall())
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists jets cascade")
cur.execute("drop table if exists leptons cascade")
cur.execute("drop table if exists event_info cascade")
cur.execute(
"create table event_info (Run int, Lumi int, Event int, PRIMARY KEY (Run, Lumi, Event))"
)
cur.execute(
"create table jets (Run int, Lumi int, Event int, Pt float, Eta float, Phi float, Charge float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event) REFERENCES event_info(Run, Lumi, Event))"
)
cur.execute(
"create table leptons (Run int, Lumi int, Event int, Pt float, Eta float, Phi float, Charge float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event) REFERENCES event_info(Run, Lumi, Event))"
)
cur.execute(
"copy event_info from '/home/newdbadmin/schema_test/event.txt' parser fdelimitedparser()"
)
cur.execute(
"copy leptons from '/home/newdbadmin/schema_test/lep.txt' parser fdelimitedparser()"
)
cur.execute(
"copy jets from '/home/newdbadmin/schema_test/jet.txt' parser fdelimitedparser()"
)
cur.execute("commit")
tableTime = time.time() - startTime
queryStart = time.time()
query = " select Pt from jets;"
cur.execute(query)
queryTime = time.time() - queryStart
totalTime = time.time() - startTime
print('Fill table: {}\nQuery time: {}\nTotal: {}'.format(
tableTime, queryTime, totalTime))
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists preselected_electrons_scale cascade")
#cur.execute("drop table if exists event_info_scale cascade")
cur.execute("DROP TABLE IF EXISTS preselected_muons_scale CASCADE")
#event table
#cur.execute("create table event_info (Run int, Lumi int, Event int, SampleID int, mcwgt float, wgt float, higgs_decay float, reco_score float, norm_score_sum float, num_real_jets_bdt float, num_jet_matches_truth float, metPt float, metPhi float, PRIMARY KEY (Run, Lumi, Event, SampleID))")
#copy event table
#cur.execute("COPY event_info FROM '/home/newdbadmin/skimmingPhase/input/event/*' GZIP")
#preselected electrons table
cur.execute("create table preselected_electrons_scale (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pdgID float, dxy float, dz float, charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPrompTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, minAbsIsoNeutral float, rho float, effArea float, miniIsoR float, miniAbsIsoNeutralcorr float, SCeta float, isGsfCtfScPixChargeConsistent float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy preselected electrons table
cur.execute("COPY preselected_electrons_scale FROM '/home/newdbadmin/skimmingPhase/scaleInput/preElec/*' GZIP ")
#preselected muons table
cur.execute("CREATE TABLE preselected_muons_scale (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pdgID float, dxy float, dz float, charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPrompTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, minAbsIsoNeutral float, rho float, effArea float, miniIsoR float, miniAbsIsoNeutralcorr float, chargeFlip float, isPFMuon float, isTrackerMuon float, isGlobalMuon float, normalizedChi2 float, numberOfValidMuonHits float, numberOfMatchedStations float, numberOfValidPixelHits float, trackerLayersWithMeasurement float, localChi2 float, trKink float, validFrac float, segCompatibility float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy preselected muons table
cur.execute("COPY preselected_muons_scale FROM '/home/newdbadmin/skimmingPhase/scaleInput/preMuon/*' GZIP")
cur.execute("commit")
tableTime = time.time() - startTime
queryStart = time.time()
#query = " select Pt from jets;"
#cur.execute(query)
queryTime = time.time() - queryStart
totalTime = time.time() - startTime
print('Fill table: {}\nQuery time: {}\nTotal: {}'.format(tableTime, queryTime, totalTime) )
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists demonstrator cascade")
makeTable = "create table demonstrator (row int, instance int, preselect float)"
cur.execute(makeTable)
cur.execute("copy demonstrator from '/home/newdbadmin/demonstrator/verticaInput4.txt' parser fdelimitedparser()")
cur.execute("commit")
tableTime = time.time() - startTime
queryStart = time.time()
query = " select preselect from demonstrator;"
cur.execute(query)
queryTime = time.time() - queryStart
totalTime = time.time() - startTime
print('Fill table: {}\nQuery time: {}\nTotal: {}'.format(tableTime, queryTime, totalTime) )
def main():
startTime = time.time()
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("drop table if exists preselected_jets cascade")
cur.execute("drop table if exists preselected_leptons cascade")
cur.execute("drop table if exists preselected_electrons cascade")
cur.execute("drop table if exists event_info cascade")
cur.execute("DROP TABLE IF EXISTS preselected_muons CASCADE")
cur.execute("DROP TABLE IF EXISTS preselected_taus CASCADE")
cur.execute("DROP TABLE IF EXISTS pruned_gen_particles CASCADE")
cur.execute("DROP TABLE IF EXISTS packed_gen_particles CASCADE")
cur.execute("DROP TABLE IF EXISTS matched_jets CASCADE")
cur.execute("DROP TABLE IF EXISTS matched_jets_truth CASCADE")
cur.execute("DROP TABLE IF EXISTS lep_fromHiggs_bdt CASCADE")
cur.execute("DROP TABLE IF EXISTS lep_fromTop_bdt CASCADE")
cur.execute("DROP TABLE IF EXISTS lep_fromTop_truth CASCADE")
cur.execute("DROP TABLE IF EXISTS lep_fromHiggs_truth CASCADE")
cur.execute("DROP TABLE IF EXISTS lep_fromTop_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS lep_fromHiggs_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS bjet_fromHadTop_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS bjet_fromLepTop_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS wjet1_fromHadTop_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS wjet2_fromHadTop_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS wjet1_fromHiggs_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS wjet2_fromHiggs_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS w_fromHadTop_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS w_fromHiggs_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS higgs_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS hadTop_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS lepTop_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS lepTop_higgs_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS hadTop_higgs_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS lepTop_hadTop_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS tth_bdt_tlv CASCADE")
cur.execute("DROP TABLE IF EXISTS passTrigger")
cur.execute("DROP TABLE IF EXISTS matching_results")
#event table
cur.execute("create table event_info (Run int, Lumi int, Event int, SampleID int, mcwgt float, wgt float, higgs_decay float, reco_score float, norm_score_sum float, num_real_jets_bdt float, num_jet_matches_truth float, metPt float, metPhi float, PRIMARY KEY (Run, Lumi, Event, SampleID))")
#copy event table
cur.execute("COPY event_info FROM '/home/newdbadmin/skimmingPhase/input/output/event/*' GZIP")
#passTrigger table
cur.execute("create table passTrigger (Run int, Lumi int, Event int, SampleID int, passTrigger varchar, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))" )
#copy table
cur.execute("COPY passTrigger FROM '/home/newdbadmin/skimmingPhase/input/output/passTrigger/*' GZIP")
#matching Results table
cur.execute("create table matching_results (Run int, Lumi int, Event int, SampleID int, passTrigger varchar, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))" )
#copy table
cur.execute("COPY matching_results FROM '/home/newdbadmin/skimmingPhase/input/output/matchingResults/*' GZIP")
#preselected lepton table
cur.execute("create table preselected_leptons (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pidgID float, dxy float, dz float, Charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPromptTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, miniAbsIsoNeutral float, rho float, effArea float, minIsoR float, miniAbsIsoNeutralcorr float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))" )
#copy preselected lepton table
cur.execute("COPY preselected_leptons FROM '/home/newdbadmin/skimmingPhase/input/output/preLep/*' GZIP")
#preselected electrons table
cur.execute("create table preselected_electrons (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pdgID float, dxy float, dz float, charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPrompTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, minAbsIsoNeutral float, rho float, effArea float, miniIsoR float, miniAbsIsoNeutralcorr float, SCeta float, isGsfCtfScPixChargeConsistent float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy preselected electrons table
cur.execute("COPY preselected_electrons FROM '/home/newdbadmin/skimmingPhase/input/output/preElec/*' GZIP ")
#preselected muons table
cur.execute("CREATE TABLE preselected_muons (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pdgID float, dxy float, dz float, charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPrompTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, minAbsIsoNeutral float, rho float, effArea float, miniIsoR float, miniAbsIsoNeutralcorr float, chargeFlip float, isPFMuon float, isTrackerMuon float, isGlobalMuon float, normalizedChi2 float, numberOfValidMuonHits float, numberOfMatchedStations float, numberOfValidPixelHits float, trackerLayersWithMeasurement float, localChi2 float, trKink float, validFrac float, segCompatibility float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy preselected muons table
cur.execute("COPY preselected_muons FROM '/home/newdbadmin/skimmingPhase/input/output/preMuon/*' GZIP")
#preslected taus
cur.execute("CREATE TABLE preselected_taus (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, charge float, genPdgID float, genMotherPdgID float, genGrandMotherPdgID float, csv float, qgid float, pdgID float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy preselected taus table
cur.execute("COPy preselected_taus FROM '/home/newdbadmin/skimmingPhase/input/output/preTau/*' GZIP")
#preselected jets
cur.execute("create table preselected_jets (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Mass float, Charge float, genPdgID float, genMotherPdgID float, genGrandMotherPdgID float, csv float, qgid float, pdgID float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))" )
#copy preselected jes
cur.execute("COPY preselected_jets FROM '/home/newdbadmin/skimmingPhase/input/output/preJet/*' GZIP")
#Pruned gen particles
cur.execute("CREATE TABLE pruned_genParticles (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Mass float, pdgID float, status float, isPromptFinalState float, isPromptDecayed float, isDirectPromptTauDecayProductFinalState float, child0 float, child1 float, mother float, grandmother float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
cur.execute("COPY pruned_genParticles FROM '/home/newdbadmin/skimmingPhase/input/output/prunedGenPart/*' GZIP")
#packed gen particles
cur.execute("CREATE TABLE packed_genParticles (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Mass float, pdgID float, status float, isPromptFinalState float, isPromptDecayed float, isDirectPromptTauDecayProductFinalState float, child0 float, child1 float, mother float, grandmother float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy packed gen particles
cur.execute("COPY packed_genParticles FROM /home/newdbadmin/skimmingPhase/input/output/prunedGenPart/*' GZIP")
#Matched jets
cur.execute("CREATE TABLE matched_jets (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Mass float, charge float, genPdgID float, genMotherPdgID float, genGrandMotherPdgID float, csv float, qgid float, pdgID float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy matched jets
cur.execute("COPY matched_jets FROM /home/newdbadmin/skimmingPhase/input/output/matchJet/*' GZIP")
#Matched jets truth
cur.execute("CREATE TABLE matched_jets_truth (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Mass float, charge float, genPdgID float, genMotherPdgID float, genGrandMotherPdgID float, csv float, qgid float, pdgID float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy matched jets truth
cur.execute("COPY matched_jets_truth FROM /home/newdbadmin/skimmingPhase/input/output/matchJetTruth/*' GZIP")
#lep from Higgs BDT
cur.execute("create table lep_fromHiggs_bdt (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pidgID float, dxy float, dz float, Charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPromptTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, miniAbsIsoNeutral float, rho float, effArea float, minIsoR float, miniAbsIsoNeutralcorr float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))" )
#copy lep from Higgs BDT table
cur.execute("COPY lep_fromHiggs_bdt FROM '/home/newdbadmin/skimmingPhase/input/output/lepHiggsBDT/*' GZIP")
#lep from Top BDT
cur.execute("create table lep_fromTop_bdt (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pidgID float, dxy float, dz float, Charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPromptTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, miniAbsIsoNeutral float, rho float, effArea float, minIsoR float, miniAbsIsoNeutralcorr float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))" )
#copy lep from Top BDT table
cur.execute("COPY lep_fromTop_bdt FROM '/home/newdbadmin/skimmingPhase/input/output/lepTopBDT/*' GZIP")
#lep from Top Truth
cur.execute("create table lep_fromTop_truth (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pidgID float, dxy float, dz float, Charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPromptTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, miniAbsIsoNeutral float, rho float, effArea float, minIsoR float, miniAbsIsoNeutralcorr float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))" )
#copy lep from Top truth table
cur.execute("COPY lep_fromTop_truth FROM '/home/newdbadmin/skimmingPhase/input/output/lepTopTruth/*' GZIP")
#lep from Higgs Truth
cur.execute("create table lep_fromHiggs_truth (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, pidgID float, dxy float, dz float, Charge float, relIso float, miniIso float, genPdgID float, isPromptFinalState float, isDirectPromptTauDecayProductFinalState float, genMotherPdgID float, genGrandMotherPdgID float, lepMVA float, miniIsoCharged float, miniIsoNeutral float, jetPtRatio float, jetPtRel float, csv float, sip3D float, jet_nCharged_tracks float, miniAbsIsoCharged float, miniAbsIsoNeutral float, rho float, effArea float, minIsoR float, miniAbsIsoNeutralcorr float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))" )
#copy lep from Higgs truth table
cur.execute("COPY lep_fromHiggs_truth FROM '/home/newdbadmin/skimmingPhase/input/output/lepHiggsTruth/*' GZIP")
#lep from top BDT tlv
cur.execute("create table lep_fromTop_bdt_tlv (Run int, Lumi int, Event int, SampleID int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy lep from top bdt tlv
cur.execute("COPY lep_fromTop_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/lepFromTopBDT_tlv/*' GZIP")
#lep from Higgs BDT tlv
cur.execute("create table lep_fromHiggs_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy lep from Higgs bdt tlv
cur.execute("COPY lep_fromHiggs_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/lepHiggsBDT_tlv/*' GZIP")
#bJet from Had Top BDT tlv
cur.execute("create table bJet_fromHadTop_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy bJet from Had top bdt tlv
cur.execute("COPY bJet_fromHadTop_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/bJetHadTopBDT_tlv/*' GZIP")
#bJet from Lep Top BDT tlv
cur.execute("create table bJet_fromLepTop_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy bJet from Lep top bdt tlv
cur.execute("COPY bJet_fromLepTop_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/bJetLepTopBDT_tlv/*' GZIP")
#wJet1 from Had Top BDT tlv
cur.execute("create table wJet1_fromHadTop_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy wJet1 from Had top bdt tlv
cur.execute("COPY wJet1_fromHadTop_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/wJet1HadTopBDT_tlv/*' GZIP")
#wJet from Had Top BDT tlv
cur.execute("create table wJet2_fromHadTop_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy wJet2 from Had top bdt tlv
cur.execute("COPY wJet2_fromHadTop_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/wJet2HadTopBDT_tlv/*' GZIP")
#wJet1 from Higgs BDT tlv
cur.execute("create table wJet1_fromHiggs_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy wJet1 from Higgs bdt tlv
cur.execute("COPY wJet1_fromHiggs_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/wJet1HiggsBDT_tlv/*' GZIP")
#wJet2 from Higgs BDT tlv
cur.execute("create table wJet2_fromHiggs_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy wJet2 from Higgs bdt tlv
cur.execute("COPY wJet2_fromHiggs_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/wJet2HiggsBDT_tlv/*' GZIP")
#w from Had Top BDT tlv
cur.execute("create table w_fromHadTop_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy w from Had top bdt tlv
cur.execute("COPY w_fromHadTop_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/wHadTopBDT_tlv/*' GZIP")
#w from Higgs BDT tlv
cur.execute("create table w_fromHiggs_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy w from Higgs bdt tlv
cur.execute("COPY w_fromHiggs_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/wHiggsBDT_tlv/*' GZIP")
#Higgs BDT tlv
cur.execute("create table Higgs_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy Higgs bdt tlv
cur.execute("COPY Higgs_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/higgsBDT_tlv/*' GZIP")
#Had top BDT tlv
cur.execute("create table HadTop_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy Had bdt tlv
cur.execute("COPY HadTop_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/hadTopBDT_tlv/*' GZIP")
#Lep top BDT tlv
cur.execute("create table LepTop_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy Lep top bdt tlv
cur.execute("COPY LepTop_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/lepTopBDT_tlv/*' GZIP")
#Lep top Higgs BDT tlv
cur.execute("create table LepTop_Higgs_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy Lep top Higgs bdt tlv
cur.execute("COPY LepTop_Higgs_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/lepTopHiggsBDT_tlv/*' GZIP")
#Had top Higgs BDT tlv
cur.execute("create table HadTop_Higgs_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy Had top Higgs bdt tlv
cur.execute("COPY HadTop_Higgs_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/hadTopHiggsBDT_tlv/*' GZIP")
#Lep top Had top BDT tlv
cur.execute("create table LepTopHadTop_Higgs_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy Lep top Had top bdt tlv
cur.execute("COPY LepTopHadTop_Higgs_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/lepTopHadTopBDT_tlv/*' GZIP")
#tth BDT tlv
cur.execute("create table tth_bdt_tlv (Run int, Lumi int, Event int, SampleuD int, Pt float, Eta float, Phi float, Mass float, CONSTRAINT fk_event FOREIGN KEY (Run, Lumi, Event, SampleID) REFERENCES event_info(Run, Lumi, Event, SampleID))")
#copy tth bdt tlv
cur.execute("COPY tth_bdt_tlv FROM '/home/newdbadmin/skimmingPhase/input/output/tthBDT_tlv/*' GZIP")
cur.execute("commit")
tableTime = time.time() - startTime
queryStart = time.time()
#query = " select Pt from jets;"
#cur.execute(query)
queryTime = time.time() - queryStart
totalTime = time.time() - startTime
print('Fill table: {}\nQuery time: {}\nTotal: {}'.format(tableTime, queryTime, totalTime) )
#!/opt/vertica/oss/python/bin/python
import hp_vertica_client
import os
import time
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS tightMVABased_Muons_small")
cur.execute("DROP TABLE IF EXISTS tightMVABased_Electrons_small")
startTime = time.time()
#cur.execute("CREATE TABLE tightMVABased_Muons AS SELECT *, CASE WHEN (validFrac >= 0.8 and lepMVA > 0.75 and segCompatibility > CASE WHEN isGlobalMuon and normalizedChiSq < 3 and localChiSq < 12 and trKink < 20 THEN 0.303 ELSE 0.451 END) THEN 1 ELSE 0 END FROM preselected_muons")
#cur.execute("EXPLAIN SELECT *, CASE WHEN (lepMVA > 0.75 ) THEN 1 ELSE 0 END FROM preselected_electrons")
#cur.execute("EXPLAIN SELECT * FROM preselected_muons WHERE validFrac >= 0.8 and lepMVA > 0.75 and segCompatibility > CASE WHEN isGlobalMuon = 1 and normalizedChi2 < 3 and localChi2 < 12 and trKink < 20 THEN 0.303 ELSE 0.451 END")
#explain = cur.fetchall()
#for i in explain:
# print(i)
#cur.execute("CREATE TABLE tightMVABased_Electrons_small AS SELECT *, CASE WHEN (lepMVA > 0.75 ) THEN 1 ELSE 0 END FROM preselected_electrons_small")
cur.execute("CREATE TABLE tightMVABased_Muons_small AS SELECT * FROM preselected_muons_small WHERE validFrac >= 0.8 and lepMVA > 0.75 and segCompatibility > CASE WHEN isGlobalMuon = 1 and normalizedChi2 < 3 and localChi2 < 12 and trKink < 20 THEN 0.303 ELSE 0.451 END")
#!/opt/vertica/oss/python/bin/python
import hp_vertica_client
import os
import time
conn = hp_vertica_client.connect("")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS tightMVABased_Muons_small")
cur.execute("DROP TABLE IF EXISTS tightMVABased_Electrons_small")
startTime = time.time()
#cur.execute("CREATE TABLE tightMVABased_Muons AS SELECT *, CASE WHEN (validFrac >= 0.8 and lepMVA > 0.75 and segCompatibility > CASE WHEN isGlobalMuon and normalizedChiSq < 3 and localChiSq < 12 and trKink < 20 THEN 0.303 ELSE 0.451 END) THEN 1 ELSE 0 END FROM preselected_muons")
#cur.execute("EXPLAIN SELECT *, CASE WHEN (lepMVA > 0.75 ) THEN 1 ELSE 0 END FROM preselected_electrons")
#cur.execute("EXPLAIN SELECT * FROM preselected_muons WHERE validFrac >= 0.8 and lepMVA > 0.75 and segCompatibility > CASE WHEN isGlobalMuon = 1 and normalizedChi2 < 3 and localChi2 < 12 and trKink < 20 THEN 0.303 ELSE 0.451 END")
#explain = cur.fetchall()
#for i in explain:
# print(i)
#cur.execute("CREATE TABLE tightMVABased_Electrons_small AS SELECT *, CASE WHEN (lepMVA > 0.75 ) THEN 1 ELSE 0 END FROM preselected_electrons_small")
cur.execute(
"CREATE TABLE tightMVABased_Muons_small AS SELECT * FROM preselected_muons_small WHERE validFrac >= 0.8 and lepMVA > 0.75 and segCompatibility > CASE WHEN isGlobalMuon = 1 and normalizedChi2 < 3 and localChi2 < 12 and trKink < 20 THEN 0.303 ELSE 0.451 END"
)
