db_schema = 'bluadmin' # set if you are not using the default
with open('../bouygues-beta-credentials.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
#db_schema = 'dash100462' # replace if you are not using the default schema
#with open('credentials_dev2.json', encoding='utf-8') as F:
# credentials = json.loads(F.read())
print("here db")
db = Database(credentials=credentials)
entity_name = 'ACME_Compressors'
entityType = entity_name
db_schema = None # replace if you are not using the default schema
db.drop_table(entity_name, schema=db_schema)
entity = EntityType(
entity_name, db,
bif.EntityDataGenerator(ids=['73000', '73001', '73002', '73003', '73004'],
data_item='is_generated'), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
# dimension columns
dimension_columns = []
dimension_columns.append(Column('business', String(50)))
dimension_columns.append(Column('site', String(50)))
dimension_columns.append(Column('equipment_type', String(50)))
dimension_columns.append(Column('train', String(50)))
dimension_columns.append(Column('service', String(50)))
dimension_columns.append(Column('asset_id', String(50)))
entity.register(raise_error=False)
db.register_functions([TurbineHTTPPreload])
def __init__(
self,
name,
db,
db_schema=None,
description=None,
generate_days=0,
drop_existing=True,
):
# constants
constants = []
physical_name = name.lower()
# granularities
granularities = []
# columns
columns = []
columns.append(Column('asset_id', String(50)))
columns.append(Column('drvr_rpm', Float()))
columns.append(Column('drvn_flow', Float()))
columns.append(Column('drvn_t1', Float()))
columns.append(Column('drvn_p1', Float()))
columns.append(Column('predict_drvn_t1', Float()))
columns.append(Column('predict_drvn_p1', Float()))
columns.append(Column('drvn_t2', Float()))
columns.append(Column('drvn_p2', Float()))
columns.append(Column('predict_drvn_t2', Float()))
columns.append(Column('predict_drvn_p2', Float()))
columns.append(Column('run_status', Integer()))
columns.append(Column('scheduled_maintenance', Integer()))
columns.append(Column('unscheduled_maintenance', Integer()))
columns.append(Column('compressor_in_x', Float()))
columns.append(Column('compressor_in_y', Float()))
columns.append(Column('compressor_out_x', Float()))
columns.append(Column('compressor_out_y', Float()))
columns.append(Column('run_status_x', Integer()))
columns.append(Column('run_status_y', Integer()))
columns.append(Column('maintenance_status_x', Integer()))
columns.append(Column('mainteancne_status_y', Integer()))
# dimension columns
dimension_columns = []
dimension_columns.append(Column('business', String(50)))
dimension_columns.append(Column('site', String(50)))
dimension_columns.append(Column('equipment_type', String(50)))
dimension_columns.append(Column('train', String(50)))
dimension_columns.append(Column('service', String(50)))
dimension_columns.append(Column('asset_id', String(50)))
# functions
functions = []
# simulation settings
# uncomment this if you want to create entities automatically
# then comment it out
# then delete any unwanted dimensions using SQL
# DELETE FROM BLUADMIN.EQUIPMENT WHERE DEVICEID=73000;
sim = {
'freq': '5min',
'auto_entity_count': 1,
'data_item_mean': {
'drvn_t1': 22,
'STEP': 1,
'drvn_p1': 50,
'asset_id': 1
},
'data_item_domain': {
#'dim_business' : ['Australia','Netherlands','USA' ],
'dim_business': ['Netherlands'],
#'dim_site' : ['FLNG Prelude','Pernis Refinery','Convent Refinery', 'FCCU', 'HTU3', 'HTU2','H-Oil','HCU' ],
'dim_site': ['HCU'],
'dim_equipment_type': ['Train'],
'dim_train_type': ['FGC-B', 'FGC-A', 'FGC-C ', 'P-45001A'],
#'dim_service': ['Charge Pump','H2 Compressor','Hydrogen Makeup Compressor','Wet Gas Compressor', 'Fresh Feed Pump'],
'dim_service': ['H2 Compressor'],
#'dim_asset_id': ['2K-330','2K-331','2K-332','2K-333'],
'dim_asset_id': [
'016-IV-1011', '016-IV-3011', '016-IV-4011', '016-IV-5011',
'016-IV-6011'
]
},
'drop_existing': True
}
generator = bif.EntityDataGenerator(ids=None, parameters=sim)
functions.append(generator)
# data type for operator cannot be inferred automatically
# state it explicitly
output_items_extended_metadata = {}
super().__init__(
name=name,
db=db,
constants=constants,
granularities=granularities,
columns=columns,
functions=functions,
dimension_columns=dimension_columns,
output_items_extended_metadata=output_items_extended_metadata,
generate_days=generate_days,
drop_existing=drop_existing,
description=description,
db_schema=db_schema)
},
"scds": {
'owner':
['Fred K', 'Mary J', 'Jane S', 'John H', 'Harry L', 'Steve S']
}
}
scd_name = '%s_scd_owner' % entity_name
# entity has an EntityDataGenerator function to generate data
# also has a SCDLookup function to retrieve data
entity = EntityType(
entity_name, db, Column('temp', Float()), Column('pressure', Float()),
Column('company_code', String(50)), Column('category_code', String(5)),
bif.EntityDataGenerator(parameters=sim_parameters,
data_item='is_generated'),
bif.ShiftCalendar(shift_definition=shift_dict,
period_start_date='shift_start_date',
period_end_date='shift_end_date',
shift_day='shift_day',
shift_id='shift_id'),
bif.SCDLookup(table_name=scd_name, output_item='owner'), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
entity.exec_local_pipeline(start_ts=dt.datetime.utcnow() -
dt.timedelta(days=30))
'''
Execution results
def make_sample_entity(self,
db,
schema=None,
name='as_sample_entity',
register=False,
data_days=1,
freq='1min',
entity_count=5,
float_cols=5,
string_cols=2,
bool_cols=2,
date_cols=2,
drop_existing=False,
include_generator=True):
"""
Build a sample entity to use for testing.
Parameters
----------
db : Database object
database where entity resides.
schema: str (optional)
name of database schema. Will be placed in the default schema if none specified.
name: str (optional)
by default the entity type will be called as_sample_entity
register: bool
register so that it is available in the UI
data_days : number
Number of days of sample data to generate
float_cols: list
Name of float columns to add
string_cols : list
Name of string columns to add
"""
if entity_count is None:
entities = None
else:
entities = ['E%s' % x for x in list(range(entity_count))]
if isinstance(float_cols, int):
float_cols = ['float_%s' % x for x in list(range(float_cols))]
if isinstance(string_cols, int):
string_cols = ['string_%s' % x for x in list(range(string_cols))]
if isinstance(date_cols, int):
date_cols = ['date_%s' % x for x in list(range(date_cols))]
if isinstance(bool_cols, int):
bool_cols = ['bool_%s' % x for x in list(range(bool_cols))]
if drop_existing:
db.drop_table(table_name=name, schema=schema)
float_cols = [Column(x.lower(), Float()) for x in float_cols]
string_cols = [Column(x.lower(), String(255)) for x in string_cols]
bool_cols = [Column(x.lower(), SmallInteger) for x in bool_cols]
date_cols = [Column(x.lower(), DateTime) for x in date_cols]
functions = []
if include_generator:
sim = {'freq': freq}
generator = bif.EntityDataGenerator(ids=entities, parameters=sim)
functions.append(generator)
cols = []
cols.extend(float_cols)
cols.extend(string_cols)
cols.extend(bool_cols)
cols.extend(date_cols)
entity = metadata.BaseCustomEntityType(name=name,
db=db,
columns=cols,
functions=functions,
generate_days=data_days,
drop_existing=drop_existing,
db_schema=schema)
if register:
entity.register(publish_kpis=True, raise_error=True)
return entity
def __init__(self,
name,
db,
db_schema=None,
description=None,
generate_days=10,
drop_existing=False):
# constants
constants = []
physical_name = name.lower()
# granularities
granularities = []
# columns
columns = []
columns.append(Column('TURBINE_ID', String(50)))
columns.append(Column('drvn_t1', Float()))
columns.append(Column('drvn_p1', Float()))
columns.append(Column('STEP', Float()))
# dimension columns
dimension_columns = []
dimension_columns.append(Column('dim_business', String(50)))
dimension_columns.append(Column('dim_site', String(50)))
dimension_columns.append(Column('dim_equipment_type', String(50)))
# functions
functions = []
# simulation settings
sim = {
'freq': '5min',
'auto_entity_count': 10,
'data_item_mean': {
'drvn_t1': 22,
'STEP': 1,
'drvn_p1': 50,
'TURBINE_ID': 1
},
'data_item_domain': {
'SITE': ['Riverside MFG', 'Collonade MFG', 'Mariners Way MFG'],
'dim_site': [
'Engineering', 'Supply Chain', 'Production', 'Quality',
'Other'
],
'dim_equipment_type': [
'New Products', 'Packaging', 'Planning', 'Warehouse',
'Logistics', 'Customer Service', 'Line 1', 'Line 2',
'Quality Control', 'Calibration', 'Reliability'
]
},
'drop_existing': False
}
generator = bif.EntityDataGenerator(ids=None, parameters=sim)
functions.append(generator)
# data type for operator cannot be inferred automatically
# state it explicitly
output_items_extended_metadata = {}
super().__init__(
name=name,
db=db,
constants=constants,
granularities=granularities,
columns=columns,
functions=functions,
dimension_columns=dimension_columns,
output_items_extended_metadata=output_items_extended_metadata,
generate_days=generate_days,
drop_existing=drop_existing,
description=description,
db_schema=db_schema)
The "widgets" entity type below has 3 input data items. Dataitems are denoted by the
SqlAlchemy column objects company_code, temp and pressure.
It also has a function EntityDataGenerator
The keyword args dict specifies extra properties. The database schema is only
needed if you are not using the default schema. You can also rename the timestamp.
'''
entity_name = 'pump1'
db_schema = None # replace if you are not using the default schema
db.drop_table(entity_name, schema=db_schema)
entity = EntityType(
entity_name, db, Column('site', String(50)), Column('temp', Float()),
Column('pressure', Float()),
bif.EntityDataGenerator(ids=['A01', 'A02', 'B01'],
data_item='is_generated'), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
'''
When creating an EntityType object you will need to specify the name of the entity, the database
object that will contain entity data
After creating an EntityType you will need to register it so that it visible in the UI.
To also register the functions and constants associated with the entity type, specify
'publish_kpis' = True.
'''
entity.register(raise_error=False)
'''
Entities can get pretty lonely without data.
def __init__(
self,
name,
db,
db_schema=None,
# functions=functions,
# columns=columns,
description=None,
generate_days=0,
drop_existing=True,
):
# constants
constants = []
physical_name = name.lower()
# granularities
granularities = []
# columns
columns = []
columns.append(Column('runningstatus', String(50)))
columns.append(Column('drvr_rpm', Float()))
columns.append(Column('drvn_p1', Float()))
# dimension columns
dimension_columns = []
# functions
functions = []
# simulation settings
# uncomment this if you want to create entities automatically
# then comment it out
# then delete any unwanted dimensions using SQL
# DELETE FROM BLUADMIN.EQUIPMENT WHERE DEVICEID=73000;
sim = {
'freq': '5min',
'auto_entity_count': 1,
'data_item_mean': {
'drvr_rpm': 1,
'drvn_p1': 50,
'runningstatus': 1
},
'data_item_domain': {},
'drop_existing': True
}
generator = bif.EntityDataGenerator(ids=None, parameters=sim)
functions.append(generator)
# data type for operator cannot be inferred automatically
# state it explicitly
output_items_extended_metadata = {}
super().__init__(
name=name,
db=db,
constants=constants,
granularities=granularities,
columns=columns,
functions=functions,
dimension_columns=dimension_columns,
output_items_extended_metadata=output_items_extended_metadata,
generate_days=generate_days,
drop_existing=drop_existing,
description=description,
db_schema=db_schema)
y0 is also an independent random variable - it will not be possible to predict this variable
y1 is a direct linear function of x1 and x2 - it will be a breeze to predict
y2 is y1 with some added noise thrown in to make it more difficult to fit a model
y3 has a non linear relation to x1 and x2, but will be easy to predict with the right estimator
y4 is y3 with some noise
We will start by trying to predict the easy on: y1 using the SimpleRegressor function.
'''
entity_name = 'predict_test' # you can give your entity type a better nane
db = Database(credentials=credentials)
db_schema = None # set if you are not using the default
db.drop_table(entity_name)
fn_gen = bif.EntityDataGenerator(output_item='generator_ok')
fn_dep1 = bif.PythonExpression( # linear relatoionship
'5*df["x1"]-df["x2"]', 'y1')
fn_dep2 = bif.PythonExpression(
'df["x1"]*df["x1"]-df["x2"]', # non-linear relationship
'y3')
fn_noise = bif.RandomNoise( # add noise to y1 and y3 to produce y2 and y4
input_items=['y1', 'y3'],
standard_deviation=.5,
output_items=['y2', 'y4'])
job_settings = {
'delete_existing_models': True,
}
entity = EntityType(
