def testjiraapidata():
jira_data_path = "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRAData_07102020.csv"
jira_data = DataReader.Read(jira_data_path)
pkey = TSQLInterface.PrimaryKeys(jira_data, 4, findFirst=True)
comp = DataComparer()
# Test locally:
#interface = TSQLInterface('.', 'MetricsDyetl')
#interface.Insert(jira_data, 'JIRAData')
#compare_data = interface.Select("SELECT * FROM JIRAData")
#comp.GenerateComparisonReport('C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRADataComp_LOCAL.xlsx', compare_data, jira_data, pKey = pkey)
# Test QA:
#interface = TSQLInterface('nj1qasql13', 'MetricsDyetl')
#interface.Insert(jira_data, 'JIRAData')
#compare_data = interface.Select("SELECT * FROM JIRAData")
#comp.GenerateComparisonReport('C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRADataComp_QA.xlsx', compare_data, jira_data, pKey = pkey)
# Test UAT:
#interface = TSQLInterface('nj1uatsql13', 'MetricsDyetl')
#interface.Insert(jira_data, 'JIRAData')
#compare_data = interface.Select("SELECT * FROM JIRAData")
#comp.GenerateComparisonReport('C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRADataComp_UAT.xlsx', compare_data, jira_data, pKey = pkey)
# Test STG:
interface = TSQLInterface('nj1stgsql13', 'MetricsDyetl')
interface.Insert(jira_data, 'JIRAData')
compare_data = interface.Select("SELECT * FROM JIRAData")
comp.GenerateComparisonReport(
'C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\JIRAData\\JIRADataComp_STG.xlsx',
compare_data,
jira_data,
pKey=pkey)
def storedprocfix():
interface = TSQLInterface('nj1sql13', 'MetricsDyetl')
query = "select Report_Date,Type from tbl_Finance_GS_SalesTrading_Equity_PnL where Report_Date>='2020-1-1';"
data = interface.Select(query)
data.to_csv(
"C:\\Users\\berutan\\Desktop\\Projects\\Fix ETLs\\FinanceGSSalesTradingequityPnL.v1\\tbl_Finance_GS_SalesTrading_equity_PnL_Duplicates.csv"
)
def insert():
interface = TSQLInterface('nj1qasql13', 'MetricsDyetl')
data = DataReader.Read(
'C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GS.SecurityExceptions.v1\\SecurityExceptionsComp.csv'
)
interface.Insert(data, 'tbl_CyberSecurity_Exceptions_GS')
out = interface.Select(
"SELECT * FROM tbl_CyberSecurity_Exceptions_GS where fileDate = '2020-04-13'"
)
return out
def ConnectToServer(args, argTup):
"""
* Connect to target T-SQL server and database.
If testing LOCAL:
* Drop existing rows with test FileDate.
* Open DYETL.WebApi, post file using postargs.json.
* Run DYETL.Service to insert data into target table.
* Check local log file, throw exception if issue occurred with
DYETL.Service.
If not testing STG:
* Drop existing rows with test FileDate.
"""
try:
interface = TSQLInterface(argTup[1], argTup[2])
except Exception as ex:
print("Could not connect to %s::%s" % (argTup[1], argTup[2]))
print("Reason: %s" % str(ex))
input('Press enter to exit.')
os._exit(0)
if args['testetlargs']['testmode'] == 'LOCAL':
# Open DynamicETL.WebApi and post test ETL job:
print("Loading ETL %s test job to WebAPI at" %
args['testetlargs']['postargs']['subject'])
print(args['fixedargs']['webapipath'])
try:
loader = ETLJobLoader(args['fixedargs']['webapipath'],
args['fixedargs']['dynamicetlservicepath'],
args['fixedargs']['logpath'],
args['fixedargs']['webapiurl'])
loader.RunETL(args['testetlargs']['postargs'])
# Check log file for issues, throw exception if occurred:
except Exception as ex:
print('ETL could not be run. Reason: %s' % str(ex))
input('Press enter to exit.')
os._exit(0)
elif args['testetlargs']['testmode'] != 'STG' and args['testetlargs'][
'removeprevfiledate']:
# Remove data with filedate from server:
print('Removing data with [%s] %s from %s::%s::%s' %
(argTup[4], argTup[0], argTup[1], argTup[2], argTup[3]))
try:
query = "DELETE FROM [%s] WHERE [%s] = '%s';" % (
argTup[3], argTup[4], argTup[0])
interface.Execute(query)
except Exception as ex:
print(
'Could not delete data with [%s] %s from %s::%s::%s.' %
argTup[4], argTup[1], argTup[2], argTup[3])
print('Reason: %s' % str(ex))
input('Press enter to exit.')
os._exit(0)
return interface
def comparetablecols():
interface = TSQLInterface('.', 'MetricsDyetl')
kwargs = {}
kwargs[
'path'] = 'C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.Regulatory.TradeRequests\\New Dataset\\TradeRequestAllStatus_07102020.csv'
kwargs['delim'] = '|'
data = DataReader.Read(**kwargs)
cols = set(data.columns)
attrs = interface.GetColumnAttributes('RegulatoryTradeRequests')
attrs = set(attrs['ColumnName'])
diff = cols - attrs
with TSQLInterface('abc', 'nowhere') as f:
pass
def comparetraderequests():
interface = TSQLInterface('.', 'MetricsDyetl')
kwargs = {}
kwargs[
'path'] = 'C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.Regulatory.TradeRequests\\New Dataset\\TradeRequestAllStatus_07122020.csv'
kwargs['delim'] = '|'
data_true = DataReader.Read(**kwargs)
query = "SELECT * FROM [dbo].[RegulatoryTradeRequests] WHERE [FileDate] = '7-12-2020'"
data_compare = interface.Select(query)
ignoreCols = ['FileDate', 'RunDate']
pKey = TSQLInterface.PrimaryKeys(data_true, 4, ignoreCols, True)
comparer = DataComparer()
comparer.GenerateComparisonReport('TradeRequests_Local.xlsx', data_compare,
data_true, ignoreCols, pKey)
def comparelocal():
interface = TSQLInterface('.', 'MetricsDyetl')
query = "SELECT * FROM [dbo].[RegulatoryTradeRequests];"
data_test = interface.Select(query)
data_valid = DataReader.Read(
"C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.Regulatory.TradeRequests\\TradeRequests_2020_0608.xlsx"
)
ignoreCols = ['FileDate', 'RunDate']
pkey = ['User Name', 'First Name', 'Requested Date Time']
#pkey = TSQLInterface.PrimaryKeys(data_valid, 4, ignoreCols = ignoreCols, findFirst = True)
DataComparer.GenerateComparisonReport('TradeRequestDiff.xlsx',
data_test,
data_valid,
ignoreCols=ignoreCols,
pKey=pkey)
def genreportwitherrors():
path = "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.InternalAudit.GSIBPhysicalAccessKnownExceptions.v1\\GS IB Physical Access Known Exceptions_20200115.xlsx"
errpath = "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.InternalAudit.GSIBPhysicalAccessKnownExceptions.v1\\GS IB Physical Access Known Exceptions_20200115_Errs.xlsx"
insertdata = DataReader.Read(path)
testdata = DataReader.Read(errpath)
interface = TSQLInterface('.', 'MetricsDyetl')
interface.Insert(insertdata,
'InternalAudit_GSIBPhysicalAccessKnownExceptions')
tabledata = interface.Select(
"SELECT * FROM InternalAudit_GSIBPhysicalAccessKnownExceptions")
comp = DataComparer()
path = "C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.InternalAudit.GSIBPhysicalAccessKnownExceptions.v1\\GS IB Physical Access Known Exceptions_ERRS.xlsx"
pKey = TSQLInterface.PrimaryKeys(insertdata, findFirst=True)
ignoreCols = ['FileDate', 'RunDate']
comp.GenerateComparisonReport(path,
tabledata,
testdata,
ignoreCols,
pKey=pKey)
def GetDatasets(args, argTup, interface, waittime):
"""
Query server to get test data, pull
valid data from local file.
"""
try:
query = "SELECT * FROM [%s] WHERE [%s] = '%s'" % (argTup[3], argTup[4],
argTup[0])
print(
'Waiting %d seconds to allow data to be pulled and transformed...'
% waittime)
# Keep pulling from server until data has been uploaded:
Countdown(waittime)
data_test = interface.Select(query)
if len(data_test) == 0:
raise Exception(
'No data uploaded to server after dropping to etlfolder.')
except Exception as ex:
print('Could not query %s::%s::%s' % (argTup[1], argTup[2], argTup[3]))
print('Reason: %s' % str(ex))
input('Press enter to exit.')
os._exit(0)
# Pull data from test file:
compareFile = args['testetlargs']['comparefile'] if 'comparefile' in args[
'testetlargs'] else args['testetlargs']['samplefile']
data_valid = DataReader.Read(compareFile,
delim=args['testetlargs']['delim'])
# Compare test file data versus output etl data:
print('Generating comparison report...')
ignorecols = ['%s' % argTup[4], 'RunDate']
if 'ignorecols' in args['testetlargs']:
ignorecols.extend(args['testetlargs']['ignorecols'])
ignorecols = set([col.strip() for col in ignorecols if col.strip()])
if 'pkey' in args['testetlargs']:
pkeys = args['testetlargs']['pkey']
else:
print(
"Finding appropriate primary key(s) to compare datasets using input file..."
)
pkeys = TSQLInterface.PrimaryKeys(data_valid, 4, ignorecols, True)
print("Using: {%s} as primary key(s)..." % ', '.join(pkeys))
return pkeys, ignorecols, data_test, data_valid
def compare():
interface = TSQLInterface('nj1qasql13', 'MetricsDyetl')
query = ['select * ']
query.append("from tbl_CyberSecurity_Exceptions_GS ")
query.append(" where fileDate = '2020-4-13'")
query.append(" order by [Employee ID] asc, [Group] asc")
query = ''.join(query)
data_valid = interface.Select(query)
ignoreCols = ['ID', 'fileDate', 'RunDate']
pKey = TSQLInterface.PrimaryKeys(data_valid,
4,
ignoreCols=ignoreCols,
findFirst=True)
interface = TSQLInterface('.', 'MetricsDyetl')
data_test = interface.Select(query)
DataComparer.GenerateComparisonReport(
'CyberSecurityExceptionsDiff_Post.xlsx', data_test, data_valid,
ignoreCols, pKey)
def pulldata():
interface = TSQLInterface('nj1sql13', 'Metrics')
query = "EXEC dbo.AdHocReport_StuDurnin_DeskVolumes '2020-05-29'"
results = interface.Execute(query, True)
results.to_csv("StuDurninResults.csv")
def getdata():
interface = TSQLInterface('.', 'MetricsDyetl')
query = "SELECT * FROM tbl_CyberSecurity_Exceptions_GS WHERE fileDate = '2020-04-13'"
data = interface.Select(query)
data.to_csv('SecurityExceptionsComp.csv', index=False)
def getpkey():
data = DataReader.Read(
"C:\\Users\\berutan\\Desktop\\Projects\\New ETL\\GEMS.DyEtl.Regulatory.TradeRequests\\TradeRequests_2020_0608.xlsx"
)
pkey = TSQLInterface.PrimaryKeys(data, 5, findFirst=False)
return pkey