['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
id evt_timestamp deviceid devicetype logicalinterface_id eventtype format updated_utc category_code company_code pressure temp _timestamp entitydatagenerator owner shift_day shift_id shift_start_date shift_end_date
73000 2019/08/21 18:44 73000 shift_calendar_test vn C ACME 318.4897967 23.61335373 2019/08/21 18:44 TRUE Jane S 2019/08/21 0:00 2 2019/08/21 14:00 2019/08/21 21:00
73000 2019/08/21 18:54 73000 shift_calendar_test ey B JDI 319.978806 20.64978853 2019/08/21 18:54 TRUE Jane S 2019/08/21 0:00 2 2019/08/21 14:00 2019/08/21 21:00
for r in reader:
# Pull Name and Type from headers
if (('Point_Name' in ''.join(r)) and ('Point_Data_Type' in ''.join(r))):
name_idx = r.index('Point_Name')
type_idx = r.index('Point_Data_Type')
continue
if (name_idx and type_idx):
name = r[name_idx].lower().replace(' ', '_')
type = r[type_idx]
if (len(name) < 1 ) or (len(type) < 1):
continue
if " " in type:
type = type.split(" ")[0]
print(name)
print(type)
print("-----")
if 'string' in type.lower():
metrics.append(Column(name, getattr(sqlalchemy, type)(50)))
else:
metrics.append(Column(name, getattr(sqlalchemy, type)()))
columns = tuple(metrics)
entity = EntityType(entity_name, db,
*columns,
**{
'_timestamp' : 'evt_timestamp',
'_db_schema' : db_schema}
)
entity.register()
print("Entity registered")
}
}
# when using the generation capabilities, the table name will be created
# automatically using the following naming convention
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.SCDLookup(table_name=scd_name, output_item='owner'), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
'''
Now that we have defined simulation parameters and attached them to an EntityDataGenerater
object on an EntityType we can run the AS calculation pipeline to execute the generator.
When running the calculation pipeline it is important to set a start_date in the past. Since
these dimensions change slowly, we will only see changes when looking a large enough
time window.
The default frequency for automatic generation of SCD data is 2 days. By starting the pipeline
30 days in the past we will see history.
'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(
entity_name, db, Column('x1', Float()), Column('x2', Float()),
Column('x3', Float()), Column('y0', Float()), fn_gen, fn_dep1, fn_dep2,
fn_noise,
estimator.SimpleRegressor(features=['x1', 'x2', 'x3'],
targets=['y1'],
predictions=['y1_predicted']), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
entity.register(raise_error=True)
start_date = dt.datetime.utcnow() - dt.timedelta(days=30)
entity.exec_local_pipeline(start_ts=start_date, **job_settings)
'''
When we execute the pipeline, it runs the preload generator, builds the target variables
and then executes the SimpleRegressor function on 30 days worth of data.
The first time the SimpleRegressor function executes, there is no trained model to
predict y1, so the SimpleRegressor trains one and writes it in serialized form to COS.
It uses the trained model to predict y1. As expected, with y1 as a direct linear
}
scd_name = '%s_scd_crew' % entity_name
activity_name = '%s_activity' % 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.ActivityDuration(table_name=activity_name,
activity_codes=['maintenance'],
activity_duration=['maintenance_duration']), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
start_date = dt.datetime.utcnow() - dt.timedelta(days=2)
'''
We will need some maintenance data. Activity tables have a predefined set of
columns. There are always only 4 columns a deviceid, start_date, end_date and
an activity_code.
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 = 'test_http_preload'
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('company_code', String(50)),
Column('temp', Float()), Column('pressure', Float()),
sample.HTTPPreload(request='GET',
url='internal_test',
output_item='http_preload_done'),
bif.PythonExpression(expression='df["temp"]*df["pressure"]',
output_name='volume'), **{
'_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)
db.register_functions([sample.HTTPPreload])
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])
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.
db = Database(credentials = credentials)
sourceconn = sourcedb.connect()
targetconn = targetdb.connect()
dbschema = credentials["connection"]
deviceid = 'KBHWVS1'
table_name = 'newmesdataset9'
#db.drop_table(table_name)
entity = EntityType(table_name,db,
Column('assetcode', String(50)),
Column('actualproddate', DateTime),
Column('productionshift', Integer),
Column('shiftduration', Integer),
Column('runningprodqty', Float),
Column('runninglossduration', Float),
**{
'_timestamp' : 'createddatetime',
'_db_schema' : dbschema
})
entity.register()
########################################Load Data into Entity #####################################################################################
#selectmesdata = text("SELECT prodexecid, assetcode, actualproddate, pe.productionshift, HOUR(S1.ENDTIME - S1.STARTTIME), actualprodqty, defectcount, lossreasoncode, lossduration FROM PRODUCTIONEXECUTION PE, Loss1 L1, SHIFT S1 WHERE PE.actualproddate = L1.proddate AND PE.PRODUCTIONSHIFT = L1.productionshift AND pe.PRODUCTIONSHIFT = S1.SHIFTID")
selectmesdata = text("SELECT datetime, s1.shiftID, HOUR(S1.ENDTIME - S1.STARTTIME), value, sum(lossduration) FROM PORCHEDATA PE, Loss1 L1, SHIFT S1 WHERE pe.TAGNAME = 'hist_KBHWVS1_Werte.sps_Stueckzahl_01' AND time(PE.datetime) > S1.STARTTIME AND time(PE.datetime) < S1.ENDTIME AND S1.SHIFTID = L1.productionshift AND date(datetime) = L1.PRODDATE GROUP BY datetime, s1.shiftID, HOUR(S1.ENDTIME - S1.STARTTIME), value")
mesdataresultset = sourceconn.execute(selectmesdata).fetchall()
print("Please place ENTITY_NAME in .env file")
exit()
# if (len(sys.argv) > 0):
# entity_name = sys.argv[1]
# else:
# entity_name = 'kb_anomaly'
entity = EntityType(
entity_name,
db,
Column('deviceid', String(50)),
Column("torque", Integer()),
Column("acc", Integer()),
Column("load", Integer()),
Column("tool_type", Integer()),
Column("speed", Float()),
Column("travel_time", Float()),
**{
'_timestamp': 'evt_timestamp',
# '_production_mode': False,
'_db_schema': db_schema
})
# db.unregister_functions(["InvokeExternalModel"])
# exit()
db.register_functions([InvokeWMLModel])
# exit()
print("Function registered")
entity.register(raise_error=False)
BI_PASSWORD = settings.BI_PASSWORD
BI_TENANT_ID = settings.BI_TENANT_ID
print("BI_Credentials")
print(BI_USERNAME)
print(BI_PASSWORD)
print(BI_TENANT_ID)
entity = EntityType(
entity_name, db, Column('building', String(50)),
Column('energy_value', Float()), Column('energy_compare_percent', Float()),
Column('energy_unit', String(50)), Column('energy_trend', String(50)),
Column('energy_trend_status', String(50)),
BIAssetHTTPPreload(username=BI_USERNAME,
password=BI_PASSWORD,
request='GET',
url=BI_TENANT_ID,
output_item='http_preload_done'),
bif.PythonExpression(
expression='df["energy_value"]*df["energy_compare_percent"]',
output_name='volume'), **{
'_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 Add Data to Entity Function UI.
To also register the functions and constants associated with the entity type, specify
'publish_kpis' = True.
'''
'zone_id': ['N', 'S', 'E', 'W']
},
"auto_entity_count": device_count
}
# create entity types and execute the calc pipeline to get some test data
data_sources = []
start_device_id = 10000
for (data_source_name, metric_name) in [child1, child2]:
sim_parameters['start_entity_id'] = start_device_id
entity = EntityType(
data_source_name, db, Column(metric_name, Float()),
bif.EntityDataGenerator(parameters=sim_parameters,
data_item='is_generated'), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
dim = '%s_dim' % data_source_name
entity.drop_tables([dim])
entity.make_dimension(
dim, # build name automatically
Column('work_area', String(50)))
entity.exec_local_pipeline()
data_sources.append(entity)
start_device_id += 10000
'''
'temp': 22,
'pressure': 320
},
"data_item_sd": {
'temp': 2,
'pressure': 5
}
}
entity = EntityType(
entity_name, db, Column('temp', Float()), Column('pressure', Float()),
Column('company_code', String(50)),
bif.EntityDataGenerator(parameters=sim_parameters,
data_item='is_generated'),
TestMetadataProvider(108, 'custom_metadata_added'),
bif.PythonExpression(
expression='df["temp"] + self._entity_type.custom_metadata',
output_name='adjusted_temp1'),
bif.PythonExpression(expression='df["temp"] *2 + c["custom_metadata"]*2',
output_name='adjusted_temp2'), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
entity.exec_local_pipeline(start_ts=dt.datetime.utcnow() -
dt.timedelta(days=30))
'''
Execution results
id evt_timestamp temp deviceid _timestamp entitydatagenerator custom_metadata_added adjusted_temp1 adjusted_temp2
73004 8/21/2019 20:50 24.46272725 73004 8/21/2019 20:50 TRUE TRUE 132.4627273 264.9254545
73003 8/21/2019 20:55 22.28387595 73003 8/21/2019 20:55 TRUE TRUE 130.283876 260.5677519
ui.UIFunctionOutSingle(
name='output_item',
datatype=bool,
description='Returns a status flag of True when executed'))
return (inputs, outputs)
entity_name = 'test_broken_functions'
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('company_code', String(50)),
Column('temp', Float()), Column('pressure', Float()),
bif.EntityDataGenerator(ids=['A01', 'A02', 'B01'],
data_item='is_generated'),
BrokenPreload('dummy', 'broken_preload'), **{
'_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.
WATSON_ML_UID = settings.WATSON_ML_UID
WATSON_ML_PASSWORD = settings.WATSON_ML_PASSWORD
WATSON_ML_INSTANCE_ID = settings.WATSON_ML_INSTANCE_ID
WATSON_ML_APIKEY = settings.WATSON_ML_APIKEY
entity = EntityType(
entity_name,
db,
# following columns can be dynamically generated based on meters associated with each asset
# Column('deviceid',String(50)),
# Column('evt_timestamp',String(50)),
InvokeExternalModel(
wml_endpoint=WATSON_ML_ENDPOINT,
uid=WATSON_ML_UID,
password=WATSON_ML_PASSWORD,
instance_id=WATSON_ML_INSTANCE_ID,
apikey=WATSON_ML_APIKEY,
# username="",
# password="",
# request="",
output_item='http_preload_done'),
# bif.PythonExpression(expression='df["energy_value"]*df["energy_value"]',
# output_name = 'volume'),
**{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
# anomaly_entity = EntityType("anomalies", db)
# anomaly_entity.register(raise_error=False)
'''
# testing creation of seperate table for anomalies
realistic outputs.
The EntityDataGenerator is designed to mimic an IoT source. IoT sources feed
a time series table that contains input data for function processing. The
EntityDataGenerator operates in the same way.
We will start by looking at vanilla results without parameters. The first thing
that we will need is an EntityType with some numeric and string items.
'''
entity_name = 'sim_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\
entity = EntityType(entity_name, db, Column('temp', Float()), Column('pressure', Float()),
Column('company_code', String(50)), Column('category_code', String(5)),
**{'_timestamp': 'evt_timestamp', '_db_schema': db_schema})
entity.register(raise_error=True)
'''
To build historical data, you can use the entity's "generate_data" method.
'''
entity.generate_data(days=0.5, drop_existing=True)
'''
To see the data you just loaded, ask the db object to read the database
table and produce a pandas dataframe.
'''
df = db.read_table(table_name=entity_name, schema=db_schema)
print(df.head())
print("BI_Credentials")
print(BI_USERNAME)
print(BI_PASSWORD)
print(BI_TENANT_ID)
db.drop_table(entity_name, schema=db_schema)
entity = EntityType(
entity_name, db, Column('TURBINE_ID', String(50)),
Column('TEMPERATURE', Float()), Column('PRESSURE', Float()),
Column('STEP', Float()), Column('PRESS_X', Float()),
Column('PRESS_Y', Float()), Column('TEMP_X', Float()),
Column('TEMP_Y', Float()),
DemoHTTPPreload(
username=BI_USERNAME,
password=BI_PASSWORD,
request='GET',
url="https://turbine-simulator.mybluemix.net/v1/api/reading",
output_item='http_preload_done'),
bif.PythonExpression(expression='df["TEMPERATURE"]*df["PRESSURE"]',
output_name='VOLUME'), **{
'_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 Add Data to Entity Function UI.
To also register the functions and constants associated with the entity type, specify
'publish_kpis' = True.
if INPUT_ITEMS and (len(INPUT_ITEMS) > 0):
INPUT_ITEMS = INPUT_ITEMS.replace(' ', '').split(',')
else:
INPUT_ITEMS = []
if settings.ENTITY_NAME:
entity_name = settings.ENTITY_NAME
elif (len(sys.argv) > 0):
entity_name = sys.argv[1]
else:
print("Please place ENTITY_NAME in .env file")
exit()
entity = EntityType(entity_name, db,
InvokeWMLModel(
wml_endpoint=WATSON_ML_ENDPOINT,
instance_id=WATSON_ML_INSTANCE_ID,
deployment_id=WATSON_ML_DEPLOYMENT_ID,
apikey=WATSON_ML_APIKEY,
input_items=INPUT_ITEMS,
output_items = 'anomaly_score_done'),
**{
'_timestamp' : 'evt_timestamp',
'_db_schema' : db_schema}
)
entity.exec_local_pipeline()
# job_settings = {'_production_mode': False}
# entity.exec_local_pipeline(**job_settings)
'''
A database object is our connection to the mother ship
'''
db = Database(credentials=credentials)
db_schema = None # set if you are not using the default
'''
To do anything with IoT Platform Analytics, you will need one or more entity type.
You can create entity types through the IoT Platform or using the python API.
Here is a basic entity type that has three data items: company_code, temperature and pressure
'''
entity_name = 'keras_model'
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('speed', Float()),
Column('temp', Float()), Column('pressure', Float()), **{
'_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.
'''
entity.register()
'''
Entities can get pretty lonely without data. You can feed your entity data by
writing directly to the entity table or you can cheat and generate data.
'''
entity.generate_data(days=0.5, drop_existing=True)
To do anything with IoT Platform Analytics, you will need one or more entity type.
You can create entity types through the IoT Platform or using the python API as shown below.
The database schema is only needed if you are not using the default schema. You can also rename the timestamp.
'''
entity_name = 'Turbines'
# dash100462 Used in dev2
db_schema = 'dash100462'
# 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('Turbine_ID', String(50)),
Column('Temperature', Float()), Column('drvn_p1', Float()),
Column('Volume', Float()),
TurbineHTTPPreload(request='GET',
url='internal_test',
output_item='http_preload_done'),
bif.PythonExpression(expression='df["Temperature"]*df["drvn_p1"]',
output_name='Volume'), **{
'_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)
db.register_functions([TurbineHTTPPreload])
'''
USERNAME = settings.USERNAME
PASSWORD = settings.PASSWORD
# TENANT_ID = settings.TENANT_ID
URL = settings.URL
entity = EntityType(entity_name, db,
# following columns can be dynamically generated based on meters associated with each asset
Column('building',String(50)),
# Column('energy_value',Float()),
# Column('energy_unit',String(50)),
Column('temperature',Float()),
Column('temperature_unit',String(50)),
MaximoAssetHTTP( username = USERNAME,
password = PASSWORD,
request='GET',
url=URL,
output_item = 'http_preload_done'),
# bif.PythonExpression(expression='df["energy_value"]*df["energy_value"]',
bif.PythonExpression(expression='df["temperature"]',
output_name = 'F'),
**{
'_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
'''
entity_name = 'issue455_0'
entity_dimension_lower = entity_name.lower() + '_dimension'
entity_dimension_upper = entity_name.upper() + '_dimension'
db.drop_table(entity_name, schema=db_schema)
db.drop_table(entity_dimension_lower, schema=db_schema)
db.drop_table(entity_dimension_upper, schema=db_schema)
entity = EntityType(
entity_name, db, Column('TURBINE_ID', String(50)),
Column('TEMPERATURE', Float()), Column('PRESSURE', Float()),
Column('STEP', Float()), Column('PRESS_X', Float()),
Column('PRESS_Y', Float()), Column('TEMP_X', Float()),
Column('TEMP_Y', Float()),
Issue455HTTPPreload(
request='GET',
url="https://turbine-simulator.mybluemix.net/v1/api/reading",
output_item='http_preload_done'), **{
'_timestamp': 'evt_timestamp',
'_db_schema': db_schema
})
entity.make_dimension(entity_dimension_upper, Column('CLIENT', String(50)),
Column('ORGANIZATION', String(50)),
Column('FUNCTION', String(50)), **{'schema': db_schema})
entity_dimension = entity.get_attributes_dict()['_dimension_table_name']
'''
When creating an EntityType object you will need to specify the name of the entity, the database
object that will contain entity data
