def main():
"Main function"
optmgr = OptionParser()
opts = optmgr.parser.parse_args()
print("Input arguments: %s" % opts)
start_time = time.time()
verbose = opts.verbose
yarn = opts.yarn
inst = opts.inst
date = opts.date
fout = opts.fout
if inst.lower() in ['global', 'phys01', 'phys02', 'phys03']:
inst = inst.upper()
else:
raise Exception('Unsupported DBS instance "%s"' % inst)
# Create spark context
ctx = spark_context('cms', yarn, verbose)
# Create SQL context to be used for SQL queries
sql_context = HiveContext(ctx)
# Initialize DBS tables (will be used with AAA, CMSSW)
dbs_tables(sql_context, inst=inst, verbose=verbose)
aaa_start_time = time.time()
run_aaa(date, fout, ctx, sql_context, verbose)
aaa_elapsed_time = elapsed_time(aaa_start_time)
cmssw_start_time = time.time()
run_cmssw(date, fout, ctx, sql_context, verbose)
cmssw_elapsed_time = elapsed_time(cmssw_start_time)
eos_start_time = time.time()
run_eos(date, fout, ctx, sql_context, verbose)
eos_elapsed_time = elapsed_time(eos_start_time)
jm_start_time = time.time()
run_jm(date, fout, ctx, sql_context, verbose)
jm_elapsed_time = elapsed_time(jm_start_time)
ctx.stop()
print('Start time : %s' % time.strftime('%Y-%m-%d %H:%M:%S GMT', time.gmtime(start_time)))
print('End time : %s' % time.strftime('%Y-%m-%d %H:%M:%S GMT', time.gmtime(time.time())))
print('Total elapsed time : %s' % elapsed_time(start_time))
print('AAA elapsed time : %s' % aaa_elapsed_time)
print('CMSSW elapsed time : %s' % cmssw_elapsed_time)
print('EOS elapsed time : %s' % eos_elapsed_time)
print('JM elapsed time : %s' % jm_elapsed_time)
def remove_invalid_datasets(df, sqlContext, verbose):
tables = {}
instances = ['GLOBAL', 'PHYS01', 'PHYS02', 'PHYS03']
for instance in instances:
dbs_dict = dbs_tables(sqlContext, inst=instance, verbose=verbose)
for key, val in dbs_dict.items():
new_key = '%s_%s' % (key, instance)
tables[new_key] = val
daf = reduce(lambda a,b: a.unionAll(b), [tables['daf_%s' % x] for x in instances])
ddf = reduce(lambda a,b: a.unionAll(b), [tables['ddf_%s' % x] for x in instances])
dbs_ddf_cols = ['d_dataset', 'd_dataset_access_type_id']
dbs_daf_cols = ['dataset_access_type_id', 'dataset_access_type']
ddf_df = ddf.select(dbs_ddf_cols)
daf_df = daf.select(dbs_daf_cols)
# d_dataset, dataset_access_type
dbs_df = ddf_df.join(daf_df, ddf_df.d_dataset_access_type_id == daf_df.dataset_access_type_id)\
.drop(ddf_df.d_dataset_access_type_id)\
.drop(daf_df.dataset_access_type_id)
# ..., dataset_access_type
df = df.join(dbs_df, df.dataset == dbs_df.d_dataset)
df = df.where(df.dataset_access_type == 'VALID')\
.drop(df.dataset_access_type)
return df
def run(fout,
yarn=None,
verbose=None,
patterns=None,
antipatterns=None,
inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
sqlContext = HiveContext(ctx)
# read DBS and Phedex tables
tables = {}
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose))
bdf = tables['bdf']
fdf = tables['fdf']
flf = tables['flf']
# join tables
cols = ['*'] # to select all fields from table
cols = [
'b_block_id', 'b_block_name', 'f_block_id', 'f_file_id', 'fl_file_id',
'fl_lumi_section_num'
]
# join tables
stmt = 'SELECT %s FROM bdf JOIN fdf on bdf.b_block_id = fdf.f_block_id JOIN flf on fdf.f_file_id=flf.fl_file_id' % ','.join(
cols)
print(stmt)
joins = sqlContext.sql(stmt)
# keep table around
joins.persist(StorageLevel.MEMORY_AND_DISK)
# construct conditions
# adler = ['ad8f6ad2','9c441343','f68d5dca','81c90e2a','471d2524','a3c1f077','6f0018a0','8bb03b60','d504882c','5ede357f','b05303c3','716d1776','7e9cf258','1945804b','ec7bc1d7','12c87747','94f2aa32']
# cond = 'f_adler32 in %s' % adler
# cond = cond.replace('[', '(').replace(']', ')')
# fjoin = joins.where(cond).distinct().select(cols)
# print_rows(fjoin, stmt, verbose)
fjoin = joins\
.groupBy(['b_block_name'])\
.agg({'fl_lumi_section_num':'count'})\
.withColumnRenamed('count(fl_lumi_section_num)', 'nlumis')\
# keep table around
fjoin.persist(StorageLevel.MEMORY_AND_DISK)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
fjoin.write.format("com.databricks.spark.csv")\
.option("header", "true").save(fout)
ctx.stop()
def run(fout,
yarn=None,
verbose=None,
patterns=None,
antipatterns=None,
inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
sqlContext = HiveContext(ctx)
# read DBS and Phedex tables
tables = {}
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose))
ddf = tables['ddf']
bdf = tables['bdf']
fdf = tables['fdf']
# join tables
cols = [
'd_dataset', 'd_dataset_id', 'b_block_id', 'b_file_count',
'f_block_id', 'f_file_id', 'f_dataset_id', 'f_event_count',
'f_file_size'
]
# join tables
stmt = 'SELECT %s FROM ddf JOIN bdf on ddf.d_dataset_id = bdf.b_dataset_id JOIN fdf on bdf.b_block_id=fdf.f_block_id' % ','.join(
cols)
print(stmt)
joins = sqlContext.sql(stmt)
# keep table around
joins.persist(StorageLevel.MEMORY_AND_DISK)
# construct aggregation
fjoin = joins\
.groupBy(['d_dataset'])\
.agg({'b_file_count':'sum', 'f_event_count':'sum', 'f_file_size':'sum'})\
.withColumnRenamed('d_dataset', 'dataset')\
.withColumnRenamed('sum(b_file_count)', 'nfiles')\
.withColumnRenamed('sum(f_event_count)', 'nevents')\
.withColumnRenamed('sum(f_file_size)', 'size')
# keep table around
fjoin.persist(StorageLevel.MEMORY_AND_DISK)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
fjoin.write.format("com.databricks.spark.csv")\
.option("header", "true").save(fout)
ctx.stop()
def generate_dataset_totals_pandasdf(
period=("20200101", "20200131"),
isUserCMS=False,
parquetLocation=DEFAULT_PARQUET_LOCATION,
spark=None,
verbose=False,
):
"""
Query the parquet dataset for a given period grouping by
dataset, and aplication.
This will omit files that doesn't match to dbs files
(e.g. files without dataset)
For a full report you should use generate_dataset_file_days
"""
if spark is None:
spark = get_spark_session(True, False)
eos_df = (spark.read.option(
"basePath", parquetLocation).parquet(parquetLocation).filter(
"day between {} AND {}".format(*period)))
eos_df = eos_df.groupby("session", "file_lfn", "application", "user",
"user_dn").agg({
"rt": "sum",
"rb": "sum",
"wb": "sum",
"wt": "sum",
"rb_max": "max",
"timestamp": "max",
})
eos_df = eos_df.selectExpr(*[
x if "(" not in x else "`{}` as {}".
format(x,
x.replace("max(", "").replace("sum(", "").replace(")", ""))
for x in eos_df.columns
])
eos_df.registerTempTable("eos_df")
tables = dbs_tables(spark, tables=["ddf", "fdf"])
if verbose:
print(tables)
grouped = spark.sql("""
select d_dataset,
application,
count(distinct(session)) as nevents,
sum(rb)/(1024*1024) as total_rb,
sum(wb)/(1024*1024) as total_wb,
sum(rt)/1000 as total_rt,
sum(wt)/1000 as total_wt
from eos_df join fdf on file_lfn = concat('/eos/cms',f_logical_file_name)
join ddf on d_dataset_id = f_dataset_id
where user {} like 'cms%' -- THIS IS EQUIVALENT TO isUserCMS IN THE OLD QUERY
group by d_dataset, application
""".format("" if isUserCMS else "NOT"))
grouped = grouped.na.fill("/Unknown", "d_dataset")
grouped = grouped.withColumn("data_tier",
regexp_extract("d_dataset", ".*/([^/]*)$", 1))
_datasets_totals = grouped.toPandas()
return _datasets_totals
def run(fout,
yarn=None,
verbose=None,
patterns=None,
antipatterns=None,
inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
sqlContext = SQLContext(ctx)
# read DBS and Phedex tables
tables = {}
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose))
tables.update(phedex_tables(sqlContext, verbose=verbose))
phedex_df = tables['phedex_df']
ddf = tables['ddf']
fdf = tables['fdf']
print("### ddf from main", ddf)
# join tables
cols = ['*'] # to select all fields from table
cols = ['d_dataset_id', 'd_dataset', 'f_logical_file_name', 'f_adler32']
# join tables
stmt = 'SELECT %s FROM ddf JOIN fdf on ddf.d_dataset_id = fdf.f_dataset_id' % ','.join(
cols)
print(stmt)
joins = sqlContext.sql(stmt)
# construct conditions
adler = [
'ad8f6ad2', '9c441343', 'f68d5dca', '81c90e2a', '471d2524', 'a3c1f077',
'6f0018a0', '8bb03b60', 'd504882c', '5ede357f', 'b05303c3', '716d1776',
'7e9cf258', '1945804b', 'ec7bc1d7', '12c87747', '94f2aa32'
]
cond = 'f_adler32 in %s' % adler
cond = cond.replace('[', '(').replace(']', ')')
# scols = ['f_logical_file_name']
fjoin = joins.where(cond).distinct().select(cols)
print_rows(fjoin, stmt, verbose)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
fjoin.write.format("com.databricks.spark.csv")\
.option("header", "true").save(fout)
ctx.stop()
def run(date, fout, yarn=None, verbose=None, inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
sqlContext = HiveContext(ctx)
# read DBS and Phedex tables
tables = {}
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose))
ddf = tables['ddf'] # dataset table
fdf = tables['fdf'] # file table
if verbose:
for row in ddf.head(1):
print("### ddf row", row)
# read CMSSW avro rdd
date = eos_date(date)
tables.update(eos_tables(sqlContext, date=date, verbose=verbose))
eos_df = tables['eos_df'] # EOS table
if verbose:
for row in eos_df.head(1):
print("### eos_df row", row)
# merge DBS and CMSSW data
cols = ['d_dataset', 'd_dataset_id', 'f_logical_file_name', 'file_lfn']
stmt = 'SELECT %s FROM ddf JOIN fdf ON ddf.d_dataset_id = fdf.f_dataset_id JOIN eos_df ON fdf.f_logical_file_name = eos_df.file_lfn' % ','.join(
cols)
joins = sqlContext.sql(stmt)
print_rows(joins, stmt, verbose)
# perform aggregation
fjoin = joins.groupBy(['d_dataset'])\
.agg({'file_lfn':'count'})\
.withColumnRenamed('count(file_lfn)', 'count')\
.withColumnRenamed('d_dataset', 'dataset')\
.withColumn('date', lit(eos_date_unix(date)))\
.withColumn('count_type', lit('eos'))\
# keep table around
fjoin.persist(StorageLevel.MEMORY_AND_DISK)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
ndf = split_dataset(fjoin, 'dataset')
ndf.write.format("com.databricks.spark.csv")\
.option("header", "true").save(fout)
ctx.stop()
def run(date, fout, yarn=None, verbose=None, inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
sqlContext = HiveContext(ctx)
# read DBS and JobMonitoring tables
tables = {}
# read JobMonitoring avro rdd
date = jm_date(date)
jm_df = jm_tables(ctx, sqlContext, date=date, verbose=verbose)
# DBS tables
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose))
ddf = tables['ddf'] # dataset table
fdf = tables['fdf'] # file table
# merge DBS and JobMonitoring data
cols = [
'd_dataset', 'd_dataset_id', 'f_logical_file_name', 'FileName',
'FileType', 'Type', 'SiteName', 'WrapWC', 'WrapCPU', 'JobExecExitCode'
]
stmt = 'SELECT %s FROM ddf JOIN fdf ON ddf.d_dataset_id = fdf.f_dataset_id JOIN jm_df ON fdf.f_logical_file_name = jm_df.FileName' % ','.join(
cols)
joins = sqlContext.sql(stmt)
print_rows(joins, stmt, verbose)
# perform aggregation
fjoin = joins.groupBy(['SiteName','JobExecExitCode','FileType','Type','d_dataset'])\
.agg({'WrapWC':'sum','WrapCPU':'sum','JobExecExitCode':'count','FileType':'count','Type':'count'})\
.withColumnRenamed('sum(WrapWC)', 'tot_wc')\
.withColumnRenamed('sum(WrapCPU)', 'tot_cpu')\
.withColumnRenamed('count(JobExecExitCode)', 'ecode_count')\
.withColumnRenamed('count(FileType)', 'file_type_count')\
.withColumnRenamed('count(Type)', 'type_count')\
.withColumnRenamed('d_dataset', 'dataset')\
.withColumn('date', lit(jm_date_unix(date)))\
.withColumn('count_type', lit('jm'))\
# keep table around
fjoin.persist(StorageLevel.MEMORY_AND_DISK)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
ndf = split_dataset(fjoin, 'dataset')
ndf.write.format("com.databricks.spark.csv")\
.option("header", "true").save(fout)
ctx.stop()
def run(fout,
yarn=None,
verbose=None,
patterns=None,
antipatterns=None,
inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
sqlContext = SQLContext(ctx)
# read DBS and Phedex tables
tables = {}
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose))
bdf = tables['bdf']
fdf = tables['fdf']
flf = tables['flf']
# join tables
cols = ['*'] # to select all fields from table
cols = ['b_block_id', 'b_block_name', 'f_block_id', 'f_logical_file_name']
# join tables
stmt = 'SELECT %s FROM bdf JOIN fdf on bdf.b_block_id = fdf.f_block_id' % ','.join(
cols)
print(stmt)
joins = sqlContext.sql(stmt)
# keep table around
joins.persist(StorageLevel.MEMORY_AND_DISK)
# construct conditions
cols = ['b_block_name', 'f_logical_file_name']
pat = '%00047DB7-9F77-E011-ADC8-00215E21D9A8.root'
# pat = '%02ACAA1A-9F32-E111-BB31-0002C90B743A.root'
fjoin = joins.select(cols).where(col('f_logical_file_name').like(pat))
print_rows(fjoin, stmt, verbose)
# keep table around
fjoin.persist(StorageLevel.MEMORY_AND_DISK)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
fjoin.write.format("com.databricks.spark.csv")\
.option("header", "true").save(fout)
ctx.stop()
def generate_dataset_file_days(
period=("20190101", "20190131"),
app_filter=None,
parquetLocation=DEFAULT_PARQUET_LOCATION,
spark=None,
verbose=False,
):
"""
Generate a pandas dataset with dataset, filename, day, application,
avg_size, total_rb (read bytes), total_wb (written bytes,
total_rt (read time), and total_wt (write time)
"""
if spark is None:
spark = get_spark_session(True, False)
df = spark.read.parquet(parquetLocation).filter(
"day between {} AND {}".format(*period))
if app_filter is not None:
df = df.filter(df.application.like(app_filter))
df.registerTempTable("eos_df")
tables = dbs_tables(spark, tables=["ddf", "fdf"])
if verbose:
print(tables)
grouped = spark.sql("""
select d_dataset,
file_lfn,
day,
application,
count(distinct(session)) as nevents,
mean(csize) as avg_size,
sum(rb) as total_rb,
sum(wb) as total_wb,
sum(rt) as total_rt,
sum(wt) as total_wt
from eos_df left join fdf on file_lfn = concat('/eos/cms',f_logical_file_name)
left join ddf on d_dataset_id = f_dataset_id
group by d_dataset, file_lfn, day, application
""")
grouped = grouped.withColumn("data_tier",
regexp_extract("d_dataset", ".*/([^/]*)$", 1))
return grouped.toPandas()
def run(date, fout, yarn=None, verbose=None, inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
sqlContext = SQLContext(ctx)
# read DBS and Phedex tables
tables = {}
dtables = ['daf', 'ddf', 'bdf', 'fdf', 'aef', 'pef', 'mcf', 'ocf', 'rvf']
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose, tables=dtables))
# tables.update(phedex_tables(sqlContext, verbose=verbose))
# phedex_df = tables['phedex_df']
daf = tables['daf'] # dataset access table
ddf = tables['ddf'] # dataset table
bdf = tables['bdf'] # block table
fdf = tables['fdf'] # file table
aef = tables['aef'] # acquisition era
pef = tables['pef'] # processing era table
mcf = tables['mcf'] # output mod config table
ocf = tables['ocf'] # output module table
rvf = tables['rvf'] # release version table
# read Condor rdd
# tables.update(condor_tables(sqlContext, hdir='hdfs:///cms/users/vk/condor', date=condor_date(date), verbose=verbose))
tables.update(condor_tables(sqlContext, date=condor_date(date), verbose=verbose))
condor_df = tables['condor_df'] # aaa table
# aggregate dbs info into dataframe
cols = ['d_dataset_id', 'd_dataset','d_creation_date','d_is_dataset_valid','f_event_count','f_file_size','dataset_access_type','acquisition_era_name','processing_version']
stmt = 'SELECT %s FROM ddf JOIN fdf on ddf.d_dataset_id = fdf.f_dataset_id JOIN daf ON ddf.d_dataset_access_type_id = daf.dataset_access_type_id JOIN aef ON ddf.d_acquisition_era_id = aef.acquisition_era_id JOIN pef ON ddf.d_processing_era_id = pef.processing_era_id' % ','.join(cols)
print(stmt)
joins = sqlContext.sql(stmt)
# construct conditions
cond = 'dataset_access_type = "VALID" AND d_is_dataset_valid = 1'
fjoin = joins.where(cond).distinct().select(cols)
# at this step we have fjoin table with Row(d_dataset_id=9413359, d_dataset=u'/SingleMu/CMSSW_7_1_0_pre9-GR_R_71_V4_RelVal_mu2012D_TEST-v6000/DQM', d_creation_date=1406060166.0, d_is_dataset_valid=1, f_event_count=5318, f_file_size=21132638.0, dataset_access_type=u'DELETED', acquisition_era_name=u'CMSSW_7_1_0_pre9', processing_version=u'6000'))
newdf = fjoin\
.groupBy(['d_dataset','d_dataset_id','dataset_access_type','acquisition_era_name','processing_version'])\
.agg({'f_event_count':'sum', 'f_file_size':'sum', 'd_creation_date':'max'})\
.withColumnRenamed('sum(f_event_count)', 'evts')\
.withColumnRenamed('sum(f_file_size)', 'size')\
.withColumnRenamed('max(d_creation_date)', 'date')
# at this point we have ndf dataframe with our collected stats for every dataset
# let's join it with release info
newdf.registerTempTable('newdf')
cols = ['d_dataset_id','d_dataset','evts','size','date','dataset_access_type','acquisition_era_name','processing_version','r_release_version']
stmt = 'SELECT %s FROM newdf JOIN mcf ON newdf.d_dataset_id = mcf.mc_dataset_id JOIN ocf ON mcf.mc_output_mod_config_id = ocf.oc_output_mod_config_id JOIN rvf ON ocf.oc_release_version_id = rvf.r_release_version_id' % ','.join(cols)
agg_dbs_df = sqlContext.sql(stmt)
agg_dbs_df.registerTempTable('agg_dbs_df')
# merge dbs+phedex and Condor data
cols = ['d_dataset','evts','size','date','dataset_access_type','acquisition_era_name','r_release_version']
cols = cols + ['data.KEvents', 'data.CMSSWKLumis', 'data.CMSSWWallHrs', 'data.Campaign', 'data.Workflow', 'data.CpuEff', 'data.CoreHr', 'data.QueueHrs', 'data.CRAB_UserHN', 'data.Type', 'data.ExitCode', 'data.TaskType', 'data.RecordTime']
stmt = 'SELECT %s FROM condor_df JOIN agg_dbs_df ON agg_dbs_df.d_dataset = condor_df.data.DESIRED_CMSDataset WHERE condor_df.data.KEvents > 0' % ','.join(cols)
# stmt = 'SELECT %s FROM condor_df JOIN dbs_phedex_df ON dbs_phedex_df.d_dataset = condor_df.data.DESIRED_CMSDataset WHERE condor_df.data.KEvents > 0' % ','.join(cols)
final_df = sqlContext.sql(stmt)
print_rows(final_df, stmt, verbose)
# keep table around
final_df.persist(StorageLevel.MEMORY_AND_DISK)
# user defined function
def rate(evts, cores):
"Calculate the rate of events vs cores, if they're not defineed return -1"
if evts and cores:
return float(evts)/float(cores)
return -1.
func_rate = udf(rate, DoubleType())
# our output
store = {}
# conditions
# load pyspark functions to be used here to redefine any previous usage of those names
from pyspark.sql.functions import lit, sum, count, col, split
# here we split dataframe based on exitcode conditions to reduce dimentionality
# of the input, otherwise job crashes with Integer.MAX_VALUE exception which
# basically tells that input dataframe exceed number of available partitions
for ecode in [0,1]:
if ecode == 0:
refdf = final_df.where(col('ExitCode') == 0)
condf = condor_df.where(col('data.ExitCode') == 0)
else:
refdf = final_df.where(col('ExitCode') != 0)
condf = condor_df.where(col('data.ExitCode') != 0)
refdf.persist(StorageLevel.MEMORY_AND_DISK)
condf.persist(StorageLevel.MEMORY_AND_DISK)
# aggregate CMS datasets
cols = ['data.DESIRED_CMSDataset', 'data.CRAB_UserHN', 'data.ExitCode', 'data.Type', 'data.TaskType', 'data.RecordTime']
xdf = condf.groupBy(cols)\
.agg(sum('data.KEvents').alias('sum_evts'),sum('data.CoreHr').alias('sum_chr'))\
.withColumn('date', lit(date))\
.withColumn('rate', func_rate(col('sum_evts'),col('sum_chr')))\
.withColumn("tier", split(col('DESIRED_CMSDataset'), "/").alias('tier').getItem(3))\
.withColumnRenamed('CRAB_UserHN', 'user')\
.withColumnRenamed('RecordTime', 'rec_time')\
.withColumnRenamed('DESIRED_CMSDataset', 'dataset')
store.setdefault('dataset', []).append(xdf)
# aggregate across campaign
cols = ['data.Campaign', 'data.CRAB_UserHN', 'data.ExitCode', 'data.Type', 'data.TaskType', 'data.RecordTime']
xdf = condf.groupBy(cols)\
.agg(sum('data.KEvents').alias('sum_evts'),sum('data.CoreHr').alias('sum_chr'))\
.withColumn('date', lit(date))\
.withColumn('rate', func_rate(col('sum_evts'),col('sum_chr')))\
.withColumnRenamed('CRAB_UserHN', 'user')\
.withColumnRenamed('RecordTime', 'rec_time')\
.withColumnRenamed('Campaign', 'campaign')
store.setdefault('campaign', []).append(xdf)
# aggregate across DBS releases
cols = ['r_release_version', 'CRAB_UserHN', 'ExitCode', 'Type', 'TaskType', 'RecordTime']
xdf = refdf.groupBy(cols)\
.agg(sum('KEvents').alias('sum_evts'),sum('CoreHr').alias('sum_chr'))\
.withColumn('date', lit(date))\
.withColumn('rate', func_rate(col('sum_evts'),col('sum_chr')))\
.withColumnRenamed('CRAB_UserHN', 'user')\
.withColumnRenamed('RecordTime', 'rec_time')\
.withColumnRenamed('r_release_version', 'release')
store.setdefault('release', []).append(xdf)
# aggregate across DBS eras
cols = ['acquisition_era_name', 'CRAB_UserHN', 'ExitCode', 'Type', 'TaskType', 'RecordTime']
xdf = refdf.groupBy(cols)\
.agg(sum('KEvents').alias('sum_evts'),sum('CoreHr').alias('sum_chr'))\
.withColumn('date', lit(date))\
.withColumn('rate', func_rate(col('sum_evts'),col('sum_chr')))\
.withColumnRenamed('CRAB_UserHN', 'user')\
.withColumnRenamed('RecordTime', 'rec_time')\
.withColumnRenamed('acquisition_era_name', 'era')
store.setdefault('era', []).append(xdf)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
year, month, day = split_date(date)
for col in store.keys():
out = '%s/%s/%s/%s/%s' % (fout, col, year, month, day)
print("output: %s" % out)
odf = unionAll(store[col])
print("%s rows: %s" % (col, odf.count()))
print_rows(odf, col, verbose=1)
odf.write.format("com.databricks.spark.csv")\
.option("header", "true").save(out)
ctx.stop()
def run(fout, date, yarn=None, verbose=None, inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
quiet_logs(ctx)
sqlContext = HiveContext(ctx)
fromdate = '%s-%s-%s' % (date[:4], date[4:6], date[6:])
todate = fromdate
# read Phedex and DBS tables
tables = {}
tables.update(
phedex_tables(sqlContext,
verbose=verbose,
fromdate=fromdate,
todate=todate))
phedex = tables['phedex_df']
instances = [inst]
if inst == 'all':
instances = ['GLOBAL', 'PHYS01', 'PHYS02', 'PHYS03']
for instance in instances:
dbs_dict = dbs_tables(sqlContext, inst=instance, verbose=verbose)
for key, val in dbs_dict.items():
new_key = '%s_%s' % (key, instance)
tables[new_key] = val
daf = reduce(lambda a, b: a.unionAll(b),
[tables['daf_%s' % x] for x in instances])
ddf = reduce(lambda a, b: a.unionAll(b),
[tables['ddf_%s' % x] for x in instances])
fdf = reduce(lambda a, b: a.unionAll(b),
[tables['fdf_%s' % x] for x in instances])
dbs_fdf_cols = ['f_dataset_id', 'f_file_size']
dbs_ddf_cols = ['d_dataset_id', 'd_dataset', 'd_dataset_access_type_id']
dbs_daf_cols = ['dataset_access_type_id', 'dataset_access_type']
fdf_df = fdf.select(dbs_fdf_cols)
ddf_df = ddf.select(dbs_ddf_cols)
daf_df = daf.select(dbs_daf_cols)
# Aggregate by campaign and find total PhEDEx and DBS size of each campaign
# d_dataset_id, d_dataset, dataset_access_type
dbs_df = ddf_df.join(daf_df, ddf_df.d_dataset_access_type_id == daf_df.dataset_access_type_id)\
.drop(ddf_df.d_dataset_access_type_id)\
.drop(daf_df.dataset_access_type_id)
# dataset, dbs_size
dbs_df = dbs_df.where(dbs_df.dataset_access_type == 'VALID')\
.join(fdf_df, dbs_df.d_dataset_id == fdf_df.f_dataset_id)\
.withColumnRenamed('d_dataset', 'dataset')\
.withColumnRenamed('f_file_size', 'dbs_size')\
.drop(dbs_df.d_dataset_id)\
.drop(fdf_df.f_dataset_id)\
.drop(dbs_df.dataset_access_type)
# dataset, dbs_size
dbs_df = dbs_df.groupBy(['dataset'])\
.agg({'dbs_size':'sum'})\
.withColumnRenamed('sum(dbs_size)', 'dbs_size')
# dataset, site, phedex_size
phedex_cols = ['dataset_name', 'block_bytes', 'node_name']
phedex_df = phedex.select(phedex_cols)
phedex_df = phedex_df.withColumnRenamed('block_bytes', 'phedex_size')\
.withColumnRenamed('dataset_name', 'dataset')\
.withColumnRenamed('node_name', 'site')
# dataset, sites, phedex_size
phedex_df = phedex_df.groupBy(['dataset'])\
.agg({'phedex_size':'sum', 'site': 'collect_set'})\
.withColumnRenamed('sum(phedex_size)', 'phedex_size')\
.withColumnRenamed('collect_set(site)', 'sites')
# Subtract to get leftovers
extract_campaign_udf = udf(lambda dataset: dataset.split('/')[2])
# dataset
leftover_datasets_df = phedex_df.select('dataset').subtract(
dbs_df.select('dataset'))
# dataset, campaign, sites, phedex_size
leftovers_df = leftover_datasets_df.select('dataset').join(
phedex_df, 'dataset') #.join(dbs_df, 'dataset')
leftovers_df = leftovers_df.withColumn('campaign', extract_campaign_udf(leftovers_df.dataset))\
.select(['dataset', 'campaign', 'sites', 'phedex_size'])
# Subtract to get leftovers that don't even exist in DBS (orphans)
ddf_datasets_df = ddf_df.withColumnRenamed('d_dataset',
'dataset').select('dataset')
leftover_datasets_df = phedex_df.select('dataset').subtract(
ddf_datasets_df)
# dataset, campaign, sites, phedex_size
leftovers_orphans_df = leftover_datasets_df.select('dataset').join(
phedex_df, 'dataset') #.join(dbs_df, 'dataset')
leftovers_orphans_df = leftovers_orphans_df.withColumn('campaign', extract_campaign_udf(leftovers_orphans_df.dataset))\
.select(['dataset', 'campaign', 'sites', 'phedex_size'])
# Sum total size of leftovers
all_leftovers_size = leftovers_df.select(
'phedex_size').groupBy().sum().rdd.map(lambda x: x[0]).collect()[0]
orphan_leftovers_size = leftovers_orphans_df.select(
'phedex_size').groupBy().sum().rdd.map(lambda x: x[0]).collect()[0]
print 'All leftovers PhEDEx size: %s' % bytes_to_readable(
all_leftovers_size)
print 'Orphan leftovers PhEDEx size: %s' % bytes_to_readable(
orphan_leftovers_size)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
leftovers_df.write.format("com.databricks.spark.csv")\
.option("header", "true").save('%s/all' % fout)
leftovers_orphans_df.write.format("com.databricks.spark.csv")\
.option("header", "true").save('%s/orphans' % fout)
ctx.stop()
def get_events_by_tier_month(
spark,
start_date,
end_date,
tiers_raw=None,
remove_raw=None,
skims_raw=None,
only_valid_files=False,
verbose=False,
):
"""
Generate a pandas dataframe containing data_tier_name, month, nevents
for the given time period.
It will add virtual tiers based on the skims.
args:
- spark: Spark session
- start_date: String with the date y format yyyy/MM/dd
- end_date: String with the date y format yyyy/MM/dd
- tiers_raw: List of tiers
- remove_raw: List of remove patterns
- skims_raw: List of skim patterns
- only_valid_files: True if you want to take into account only the valid files.
- verbose: True if you want additional output messages, default False.
"""
if tiers_raw is None:
tiers_raw = [".*"]
if skims_raw is None:
skims_raw = []
if remove_raw is None:
remove_raw = []
tiers = "^({})$".format("|".join(
["{}".format(tier.strip()) for tier in tiers_raw]))
skims_rlike = (".*-({})-.*".format("|".join([l.strip() for l in skims_raw
])) if skims_raw else "^$")
remove_rlike = (".*({}).*".format("|".join(
[l.strip().lower() for l in remove_raw])) if remove_raw else "^$")
tables = spark_utils.dbs_tables(spark, tables=["ddf", "bdf", "fdf", "dtf"])
if verbose:
logger.info("remove %s", remove_rlike)
logger.info("skims %s", skims_rlike)
for k in tables:
# tables[k].cache()
logger.info(k)
tables[k].printSchema()
tables[k].show(5, truncate=False)
datablocks_file_events_df = spark.sql(
"""SELECT sum(fdf.f_event_count) as f_event_count,
max(ddf.d_data_tier_id) as d_data_tier_id,
d_dataset,
b_block_name,
max(b_creation_date) as b_creation_date,
max(b_block_size) as size
FROM ddf JOIN bdf on ddf.d_dataset_id = bdf.b_dataset_id
JOIN fdf on bdf.b_block_id = fdf.f_block_id
WHERE d_is_dataset_valid = 1
{}
group by d_dataset, b_block_name
""".format(
"AND f_is_file_valid = 1" if only_valid_files else ""))
fiter_field = "b_creation_date"
datablocks_file_events_df = (datablocks_file_events_df.withColumn(
fiter_field, from_unixtime(fiter_field)).filter(
fiter_field +
" between '{}' AND '{}' ".format(start_date.replace(
"/", "-"), end_date.replace("/", "-"))).withColumn(
"month",
concat(year(fiter_field), lpad(month(fiter_field), 2,
"0"))))
datablocks_file_events_df.registerTempTable("dbfe_df")
# Union of two queries:
# - The first query will get all the selected data tiers,
# excluding the datasets who match the skims
# - The second query will get all the selected data tiers,
# but only the dataset who match the skims.
grouped = spark.sql("""
select month, data_tier_name, sum(f_event_count) as nevents
from dbfe_df join dtf on data_tier_id = d_data_tier_id
where
data_tier_name rlike '{tiers}'
and lower(d_dataset) not rlike '{remove}'
and d_dataset not rlike '{skims}'
group by month, data_tier_name
UNION
select month,
concat(data_tier_name, '/',regexp_extract(d_dataset,'{skims}',1)) AS data_tier_name,
sum(f_event_count) as nevents
from dbfe_df join dtf on dtf.data_tier_id = d_data_tier_id
where
data_tier_name rlike '{tiers}'
and lower(d_dataset) not rlike '{remove}'
and d_dataset rlike '{skims}'
group by month, concat(data_tier_name, '/',regexp_extract(d_dataset,'{skims}',1))
""".format(tiers=tiers, remove=remove_rlike, skims=skims_rlike))
return grouped.toPandas()
def run(fout, date, yarn=None, verbose=None, inst='GLOBAL', limit=100):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
quiet_logs(ctx)
sqlContext = HiveContext(ctx)
fromdate = '%s-%s-%s' % (date[:4], date[4:6], date[6:])
todate = fromdate
# read Phedex and DBS tables
tables = {}
tables.update(phedex_tables(sqlContext, verbose=verbose, fromdate=fromdate, todate=todate))
phedex = tables['phedex_df']
instances = ['GLOBAL'] # , 'PHYS01', 'PHYS02', 'PHYS03'
for instance in instances:
dbs_dict = dbs_tables(sqlContext, inst=instance, verbose=verbose)
for key, val in dbs_dict.items():
new_key = '%s_%s' % (key, instance)
tables[new_key] = val
daf = reduce(lambda a,b: a.unionAll(b), [tables['daf_%s' % x] for x in instances])
ddf = reduce(lambda a,b: a.unionAll(b), [tables['ddf_%s' % x] for x in instances])
fdf = reduce(lambda a,b: a.unionAll(b), [tables['fdf_%s' % x] for x in instances])
dbs_fdf_cols = ['f_dataset_id', 'f_file_size']
dbs_ddf_cols = ['d_dataset_id', 'd_dataset', 'd_dataset_access_type_id']
dbs_daf_cols = ['dataset_access_type_id', 'dataset_access_type']
fdf_df = fdf.select(dbs_fdf_cols)
ddf_df = ddf.select(dbs_ddf_cols)
daf_df = daf.select(dbs_daf_cols)
# d_dataset_id, d_dataset, dataset_access_type
dbs_df = ddf_df.join(daf_df, ddf_df.d_dataset_access_type_id == daf_df.dataset_access_type_id)\
.drop(ddf_df.d_dataset_access_type_id)\
.drop(daf_df.dataset_access_type_id)
# dataset, dbs_size
dbs_df = dbs_df.where(dbs_df.dataset_access_type == 'VALID')\
.join(fdf_df, dbs_df.d_dataset_id == fdf_df.f_dataset_id)\
.withColumnRenamed('d_dataset', 'dataset')\
.withColumnRenamed('f_file_size', 'dbs_size')\
.drop(dbs_df.d_dataset_id)\
.drop(fdf_df.f_dataset_id)\
.drop(dbs_df.dataset_access_type)
# dataset, dbs_size
dbs_df = dbs_df.groupBy(['dataset'])\
.agg({'dbs_size':'sum'})\
.withColumnRenamed('sum(dbs_size)', 'dbs_size')
# dataset_name, block_bytes, node_name
phedex_cols = ['dataset_name', 'block_bytes', 'node_name']
phedex_all_df = phedex.select(phedex_cols)
aggregate(sqlContext, fout, phedex_all_df, dbs_df, limit)
aggregate(sqlContext, fout, phedex_all_df, dbs_df, limit, disk_only=True)
ctx.stop()
def run(fout,
yarn=None,
verbose=None,
patterns=None,
antipatterns=None,
inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
sqlContext = HiveContext(ctx)
# read DBS and Phedex tables
tables = {}
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose))
tables.update(phedex_tables(sqlContext, verbose=verbose))
phedex_df = tables['phedex_df']
daf = tables['daf']
ddf = tables['ddf']
bdf = tables['bdf']
fdf = tables['fdf']
aef = tables['aef']
pef = tables['pef']
mcf = tables['mcf']
ocf = tables['ocf']
rvf = tables['rvf']
print("### ddf from main", ddf)
# aggregate phedex info into dataframe
phedex_cols = [
'node_name', 'dataset_name', 'dataset_is_open', 'block_bytes',
'replica_time_create'
]
newpdf = phedex_df.select(phedex_cols).groupBy(['node_name', 'dataset_name', 'dataset_is_open'])\
.agg({'block_bytes':'sum', 'replica_time_create':'max'})\
.withColumnRenamed('sum(block_bytes)', 'pbr_size')\
.withColumnRenamed('max(replica_time_create)', 'max_replica_time')
newpdf.registerTempTable('newpdf')
# print_rows(newpdf, 'newpdf', verbose)
# newpdf.persist(StorageLevel.MEMORY_AND_DISK)
# join tables
cols = ['*'] # to select all fields from table
cols = [
'd_dataset_id', 'd_dataset', 'd_creation_date', 'd_is_dataset_valid',
'f_event_count', 'f_file_size', 'dataset_access_type',
'acquisition_era_name', 'processing_version'
]
# join tables
stmt = 'SELECT %s FROM ddf JOIN fdf on ddf.d_dataset_id = fdf.f_dataset_id JOIN daf ON ddf.d_dataset_access_type_id = daf.dataset_access_type_id JOIN aef ON ddf.d_acquisition_era_id = aef.acquisition_era_id JOIN pef ON ddf.d_processing_era_id = pef.processing_era_id' % ','.join(
cols)
print(stmt)
joins = sqlContext.sql(stmt)
# print_rows(joins, 'joins', verbose)
# keep joins table around
# joins.persist(StorageLevel.MEMORY_AND_DISK)
# construct conditions
cond = 'dataset_access_type = "VALID" AND d_is_dataset_valid = 1'
fjoin = joins.where(cond).distinct().select(cols)
# at this step we have fjoin table with Row(d_dataset_id=9413359, d_dataset=u'/SingleMu/CMSSW_7_1_0_pre9-GR_R_71_V4_RelVal_mu2012D_TEST-v6000/DQM', d_creation_date=1406060166.0, d_is_dataset_valid=1, f_event_count=5318, f_file_size=21132638.0, dataset_access_type=u'DELETED', acquisition_era_name=u'CMSSW_7_1_0_pre9', processing_version=u'6000'))
newdf = fjoin\
.groupBy(['d_dataset','d_dataset_id','dataset_access_type','acquisition_era_name','processing_version'])\
.agg({'f_event_count':'sum', 'f_file_size':'sum', 'd_creation_date':'max'})\
.withColumnRenamed('sum(f_event_count)', 'evts')\
.withColumnRenamed('sum(f_file_size)', 'size')\
.withColumnRenamed('max(d_creation_date)', 'date')
# at this point we have ndf dataframe with our collected stats for every dataset
# let's join it with release info
newdf.registerTempTable('newdf')
cols = [
'd_dataset_id', 'd_dataset', 'evts', 'size', 'date',
'dataset_access_type', 'acquisition_era_name', 'processing_version',
'r_release_version'
]
stmt = 'SELECT %s FROM newdf JOIN mcf ON newdf.d_dataset_id = mcf.mc_dataset_id JOIN ocf ON mcf.mc_output_mod_config_id = ocf.oc_output_mod_config_id JOIN rvf ON ocf.oc_release_version_id = rvf.r_release_version_id' % ','.join(
cols)
agg_dbs_df = sqlContext.sql(stmt)
agg_dbs_df.registerTempTable('agg_dbs_df')
# print_rows(agg_dbs_df, 'agg_dbs_df', verbose)
# keep agg_dbs_df table around
# agg_dbs_df.persist(StorageLevel.MEMORY_AND_DISK)
# join dbs and phedex tables
# cols = ['d_dataset_id','d_dataset','evts','size','date','dataset_access_type','acquisition_era_name','processing_version','r_release_version','dataset_name','node_name','pbr_size','dataset_is_open','max_replica_time']
cols = [
'd_dataset', 'evts', 'size', 'date', 'dataset_access_type',
'acquisition_era_name', 'r_release_version', 'node_name', 'pbr_size',
'dataset_is_open', 'max_replica_time'
]
stmt = 'SELECT %s FROM agg_dbs_df JOIN newpdf ON agg_dbs_df.d_dataset = newpdf.dataset_name' % ','.join(
cols)
finaldf = sqlContext.sql(stmt)
# keep agg_dbs_df table around
# finaldf.persist(StorageLevel.MEMORY_AND_DISK)
# print_rows(finaldf, stmt, verbose)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
ndf = split_dataset(finaldf, 'd_dataset')
ndf.write.format("com.databricks.spark.csv")\
.option("header", "true").save(fout)
ctx.stop()
def run(fout,
date,
yarn=None,
verbose=None,
patterns=None,
antipatterns=None,
inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
quiet_logs(ctx)
sqlContext = HiveContext(ctx)
fromdate = '%s-%s-%s' % (date[:4], date[4:6], date[6:])
todate = fromdate
# read DBS and Phedex tables
tables = {}
tables.update(dbs_tables(sqlContext, inst=inst, verbose=verbose))
tables.update(
phedex_tables(sqlContext,
verbose=verbose,
fromdate=fromdate,
todate=todate))
phedex = tables['phedex_df']
daf = tables['daf']
ddf = tables['ddf']
fdf = tables['fdf']
# DBS
dbs_fdf_cols = ['f_dataset_id', 'f_file_size']
dbs_ddf_cols = ['d_dataset_id', 'd_dataset', 'd_dataset_access_type_id']
dbs_daf_cols = ['dataset_access_type_id', 'dataset_access_type']
fdf_df = fdf.select(dbs_fdf_cols)
ddf_df = ddf.select(dbs_ddf_cols)
daf_df = daf.select(dbs_daf_cols)
# dataset, dbs_size, dataset_access_type_id
dbs_df = fdf_df.join(ddf_df, fdf_df.f_dataset_id == ddf_df.d_dataset_id)\
.drop('f_dataset_id')\
.drop('d_dataset_id')\
.withColumnRenamed('d_dataset', 'dataset')\
.withColumnRenamed('f_file_size', 'size')\
.withColumnRenamed('d_dataset_access_type_id', 'dataset_access_type_id')
# dataset, size, dataset_access_type
dbs_df = dbs_df.join(daf_df, dbs_df.dataset_access_type_id == daf_df.dataset_access_type_id)\
.drop(dbs_df.dataset_access_type_id)\
.drop(daf_df.dataset_access_type_id)
# dataset, dbs_size
dbs_df = dbs_df.where(dbs_df.dataset_access_type == 'VALID')\
.groupBy('dataset')\
.agg({'size':'sum'})\
.withColumnRenamed('sum(size)', 'dbs_size')
# PhEDEx
size_on_disk_udf = udf(lambda site, size: 0 if site.endswith(
('_MSS', '_Buffer', '_Export')) else size)
# dataset, size, site
phedex_cols = ['dataset_name', 'block_bytes', 'node_name']
phedex_df = phedex.select(phedex_cols)\
.withColumnRenamed('dataset_name', 'dataset')\
.withColumnRenamed('block_bytes', 'size')\
.withColumnRenamed('node_name', 'site')
# dataset, phedex_size, size_on_disk
phedex_df = phedex_df.withColumn('size_on_disk', size_on_disk_udf(phedex_df.site, phedex_df.size))\
.groupBy('dataset')\
.agg({'size':'sum', 'size_on_disk': 'sum'})\
.withColumnRenamed('sum(size)', 'phedex_size')\
.withColumnRenamed('sum(size_on_disk)', 'size_on_disk')
# dataset, dbs_size, phedex_size, size_on_disk
result = phedex_df.join(dbs_df, phedex_df.dataset == dbs_df.dataset)\
.drop(dbs_df.dataset)
extract_campaign_udf = udf(lambda dataset: dataset.split('/')[2])
extract_tier_udf = udf(lambda dataset: dataset.split('/')[3])
# campaign, tier, dbs_size, phedex_size, size_on_disk
result = result.withColumn('campaign', extract_campaign_udf(result.dataset))\
.withColumn('tier', extract_tier_udf(result.dataset))\
.drop('dataset')\
.groupBy(['campaign', 'tier'])\
.agg({'dbs_size':'sum', 'phedex_size': 'sum', 'size_on_disk': 'sum'})\
.withColumnRenamed('sum(dbs_size)', 'dbs_size')\
.withColumnRenamed('sum(phedex_size)', 'phedex_size')\
.withColumnRenamed('sum(size_on_disk)', 'size_on_disk')
# campaign, tier, dbs_size, phedex_size, size_on_disk
result = result.withColumn('sum_size',
result.dbs_size + result.phedex_size)
result = result.orderBy(result.sum_size, ascending=False)\
.drop('sum_size')\
.limit(LIMIT)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
result.write.format("com.databricks.spark.csv")\
.option("header", "true").save(fout)
ctx.stop()
def run(fout,
date,
yarn=None,
verbose=None,
patterns=None,
antipatterns=None,
inst='GLOBAL'):
"""
Main function to run pyspark job. It requires a schema file, an HDFS directory
with data and optional script with mapper/reducer functions.
"""
# define spark context, it's main object which allow to communicate with spark
ctx = spark_context('cms', yarn, verbose)
quiet_logs(ctx)
sqlContext = HiveContext(ctx)
fromdate = '%s-%s-%s' % (date[:4], date[4:6], date[6:])
todate = fromdate
# read Phedex and DBS tables
tables = {}
tables.update(
phedex_tables(sqlContext,
verbose=verbose,
fromdate=fromdate,
todate=todate))
phedex = tables['phedex_df']
instances = ['GLOBAL', 'PHYS01', 'PHYS02', 'PHYS03']
for instance in instances:
dbs_dict = dbs_tables(sqlContext, inst=instance, verbose=verbose)
for key, val in dbs_dict.items():
new_key = '%s_%s' % (key, instance)
tables[new_key] = val
daf = reduce(lambda a, b: a.unionAll(b),
[tables['daf_%s' % x] for x in instances])
ddf = reduce(lambda a, b: a.unionAll(b),
[tables['ddf_%s' % x] for x in instances])
fdf = reduce(lambda a, b: a.unionAll(b),
[tables['fdf_%s' % x] for x in instances])
dbs_fdf_cols = ['f_dataset_id', 'f_file_size']
dbs_ddf_cols = ['d_dataset_id', 'd_dataset', 'd_dataset_access_type_id']
dbs_daf_cols = ['dataset_access_type_id', 'dataset_access_type']
fdf_df = fdf.select(dbs_fdf_cols)
ddf_df = ddf.select(dbs_ddf_cols)
daf_df = daf.select(dbs_daf_cols)
# Aggregate by campaign and find total PhEDEx and DBS size of each campaign
extract_campaign_udf = udf(lambda dataset: dataset.split('/')[2])
# dataset, size, dataset_access_type_id
dbs_df = fdf_df.join(ddf_df, fdf_df.f_dataset_id == ddf_df.d_dataset_id)\
.drop('f_dataset_id')\
.drop('d_dataset_id')\
.withColumnRenamed('d_dataset', 'dataset')\
.withColumnRenamed('f_file_size', 'size')\
.withColumnRenamed('d_dataset_access_type_id', 'dataset_access_type_id')
# dataset, size, dataset_access_type
dbs_df = dbs_df.join(daf_df, dbs_df.dataset_access_type_id == daf_df.dataset_access_type_id)\
.drop(dbs_df.dataset_access_type_id)\
.drop(daf_df.dataset_access_type_id)
# campaign, dbs_size
dbs_df = dbs_df.where(dbs_df.dataset_access_type == 'VALID')\
.withColumn('campaign', extract_campaign_udf(dbs_df.dataset))\
.groupBy(['campaign'])\
.agg({'size':'sum'})\
.withColumnRenamed('sum(size)', 'dbs_size')\
.drop('dataset')
# campaign, phedex_size
phedex_cols = ['dataset_name', 'block_bytes']
phedex_df = phedex.select(phedex_cols)
phedex_df = phedex_df.withColumn('campaign', extract_campaign_udf(phedex_df.dataset_name))\
.groupBy(['campaign'])\
.agg({'block_bytes':'sum'})\
.withColumnRenamed('sum(block_bytes)', 'phedex_size')
# campaign, dbs_size, phedex_size
dbs_phedex_df = dbs_df.join(phedex_df, dbs_df.campaign == phedex_df.campaign)\
.drop(dbs_df.campaign)
print 'DISTINCT DBS AND PHEDEX CAMPAIGN COUNT:'
print dbs_phedex_df.select('campaign').distinct().count()
# Select campaign - site pairs and their sizes (from PhEDEx)
# campaign, site, size
phedex_cols = ['dataset_name', 'node_name', 'block_bytes']
campaign_site_df = phedex.select(phedex_cols)
campaign_site_df = campaign_site_df.withColumn('campaign', extract_campaign_udf(campaign_site_df.dataset_name))\
.groupBy(['campaign', 'node_name'])\
.agg({'block_bytes':'sum'})\
.withColumnRenamed('sum(block_bytes)', 'size')\
.withColumnRenamed('node_name', 'site')
# Aggregate data for site - campaign count table
# site, count
site_campaign_count_df = campaign_site_df.groupBy(['site'])\
.agg(countDistinct('campaign'))\
.withColumnRenamed('count(campaign)', 'campaign_count')\
.orderBy('campaign_count', ascending=False)\
.limit(LIMIT)
# Find two most significant sites for each campaign
columns_before_pivot = campaign_site_df.columns
result = campaign_site_df.groupBy(['campaign'])\
.pivot('site')\
.sum('size')\
.na.fill(0)
columns_after_pivot = result.columns
sites_columns = [
x for x in columns_after_pivot if x not in columns_before_pivot
]
number_of_sites_udf = udf(lambda row: len([x for x in row if x != 0]),
IntegerType())
mss_udf = udf(get_mss, LongType())
second_mss_udf = udf(get_second_mss, LongType())
mss_name_udf = udf(lambda row: get_mss_name(row, sites_columns),
StringType())
second_mss_name_udf = udf(
lambda row: get_second_mss_name(row, sites_columns), StringType())
result = result.withColumn('sites', number_of_sites_udf(struct([result[x] for x in sites_columns])))\
.withColumn('mss', mss_udf(struct([result[x] for x in sites_columns])))\
.withColumn('mss_name', mss_name_udf(struct([result[x] for x in sites_columns])))\
.withColumn('second_mss', second_mss_udf(struct([result[x] for x in sites_columns])))\
.withColumn('second_mss_name', second_mss_name_udf(struct([result[x] for x in sites_columns])))
# campaign, phedex_size, dbs_size, mss, mss_name, second_mss, second_mss_name, sites
result = result.join(dbs_phedex_df, result.campaign == dbs_phedex_df.campaign)\
.drop(result.campaign)
sorted_by_phedex = result.orderBy(result.phedex_size,
ascending=False).limit(LIMIT)
sorted_by_dbs = result.orderBy(result.dbs_size,
ascending=False).limit(LIMIT)
# write out results back to HDFS, the fout parameter defines area on HDFS
# it is either absolute path or area under /user/USERNAME
if fout:
sorted_by_phedex.write.format("com.databricks.spark.csv")\
.option("header", "true").save('%s/phedex' % fout)
sorted_by_dbs.write.format("com.databricks.spark.csv")\
.option("header", "true").save('%s/dbs' % fout)
site_campaign_count_df.write.format("com.databricks.spark.csv")\
.option("header", "true").save('%s/site_campaign_count' % fout)
ctx.stop()
def main():
"Main function"
optmgr = OptionParser()
opts = optmgr.parser.parse_args()
print("Input arguments: %s" % opts)
start_time = time.time()
verbose = opts.verbose
yarn = opts.yarn
inst = opts.inst
date = opts.date
fout = opts.fout
aaa_hdir = opts.aaa_hdir
if inst.lower() in ['global', 'phys01', 'phys02', 'phys03']:
inst = inst.upper()
else:
raise Exception('Unsupported DBS instance "%s"' % inst)
# Create spark context
ctx = spark_context('cms', yarn, verbose)
quiet_logs(ctx)
# Create SQL context to be used for SQL queries
sql_context = SQLContext(ctx)
# Initialize DBS tables
dbs_tables(sql_context,
inst=inst,
verbose=verbose,
tables=['fdf', 'bdf', 'ddf'])
# Initialize PhEDEx table to be used in file_block_site table
phedex_tables(sql_context, verbose=verbose)
# Register clean_site_name to be used with SQL queries
sql_context.udf.register("clean_site_name", clean_site_name)
# Register tier_from_site_name to be used with SQL queries
sql_context.udf.register("tier_from_site_name", tier_from_site_name)
# Register dn2uuid to be used with SQL queries
sql_context.udf.register("dn2uuid", dn2uuid)
# Register parse_app to be used with SQL queries
sql_context.udf.register("parse_app", parse_app)
# Register stream4app to be used with SQL queries
sql_context.udf.register("stream4app", stream4app)
# Register parse_dn to be used with SQL queries
sql_context.udf.register("parse_dn", parse_dn)
f_b_s_start_time = time.time()
# Create temp table with file name, block name, site name and site from PhEDEx
create_file_block_site_table(ctx, sql_context, verbose)
f_b_s_elapsed_time = elapsed_time(f_b_s_start_time)
cmssw_start_time = time.time()
aggregated_cmssw_df = run_agg_cmssw(date, ctx, sql_context, verbose)
cmssw_elapsed_time = elapsed_time(cmssw_start_time)
aaa_start_time = time.time()
if len(aaa_hdir) > 0:
aggregated_aaa_df = run_agg_aaa(date, ctx, sql_context, aaa_hdir,
verbose)
else:
aggregated_aaa_df = run_agg_aaa(date,
ctx,
sql_context,
verbose=verbose)
aaa_elapsed_time = elapsed_time(aaa_start_time)
eos_start_time = time.time()
aggregated_eos_df = run_agg_eos(date, ctx, sql_context, verbose)
eos_elapsed_time = elapsed_time(eos_start_time)
jm_start_time = time.time()
aggregated_jm_df = run_agg_jm(date, ctx, sql_context, verbose)
jm_elapsed_time = elapsed_time(jm_start_time)
if verbose:
print('Will union outputs from all streams to a single dataframe')
# Schema for output is:
# site name, dataset name, number of accesses, distinct users, stream
all_df = aggregated_cmssw_df.unionAll(aggregated_aaa_df)
all_df = all_df.unionAll(aggregated_eos_df)
all_df = all_df.unionAll(aggregated_jm_df)
all_df = all_df.sort(desc("nacc"))
if verbose:
print('Done joining all outputs to a single dataframe')
fout = fout + "/" + short_date_string(date)
# output_dataframe(fout + "/Aggregated/CMSSW/" + short_date_string(date), aggregated_cmssw_df, verbose)
# output_dataframe(fout + "/Aggregated/AAA/" + short_date_string(date), aggregated_aaa_df, verbose)
# output_dataframe(fout + "/Aggregated/EOS/" + short_date_string(date), aggregated_eos_df, verbose)
# output_dataframe(fout + "/Aggregated/JobMonitoring/" + short_date_string(date), aggregated_jm_df, verbose)
output_dataframe(fout, all_df, verbose)
if verbose:
cmssw_df_size = aggregated_cmssw_df.count()
aaa_df_size = aggregated_aaa_df.count()
eos_df_size = aggregated_eos_df.count()
jm_df_size = aggregated_jm_df.count()
all_df_size = all_df.count()
print('CMSSW:')
aggregated_cmssw_df.show(10)
aggregated_cmssw_df.printSchema()
print('AAA:')
aggregated_aaa_df.show(10)
aggregated_aaa_df.printSchema()
print('EOS:')
aggregated_eos_df.show(10)
aggregated_eos_df.printSchema()
print('JobMonitoring:')
aggregated_jm_df.show(10)
aggregated_jm_df.printSchema()
print('Aggregated all:')
all_df.show(10)
all_df.printSchema()
print('Output record count:')
print('Output record count CMSSW : %s' % cmssw_df_size)
print('Output record count AAA : %s' % aaa_df_size)
print('Output record count EOS : %s' % eos_df_size)
print('Output record count JobMonitoring : %s' % jm_df_size)
print('Output record count Total: : %s' % all_df_size)
ctx.stop()
print('Start time : %s' %
time.strftime('%Y-%m-%d %H:%M:%S GMT', time.gmtime(start_time)))
print('End time : %s' %
time.strftime('%Y-%m-%d %H:%M:%S GMT', time.gmtime(time.time())))
print('Total elapsed time : %s' % elapsed_time(start_time))
print('FileBlockSite elapsed time : %s' % f_b_s_elapsed_time)
print('AAA elapsed time : %s' % aaa_elapsed_time)
print('CMSSW elapsed time : %s' % cmssw_elapsed_time)
print('EOS elapsed time : %s' % eos_elapsed_time)
print('JobMonitoring elapsed time : %s' % jm_elapsed_time)
