def add_functions(json_payload, credentials=None):
"""
add kpi functions to a given entity type
Uses the following APIs:
POST /api/kpi/v1/{orgId}/entityType/{entityTypeName}/kpiFunction
:param credentials: dict analytics-service dev credentials
:param json_payload:
```
{
"entity_type_name": "sample_entity_type_name"
"functions": [
{
"name": "RandomUniform", #a valid catalog function name
# PARAMETERS REQUIRED FOR THE FUNCTION
# For example bif.RandomUniform needs these additional parameters
"parameters" :
{
"min_value" : 0.1,
"max_value" : 0.2,
"output_item" : "discharge_perc"
}
}
]
}
```
:return:
"""
# 1. INPUT CHECKING
logger.debug('Performing Input Checking')
payload = validateJSON(json_payload) # input is valid json
validate(instance=payload,
schema=create_kpifunction_schema) # input has valid schema
# 2. INPUT PARSING
if 'entity_type_name' not in payload:
raise Exception('No Entity Type was specified')
functions = None
if 'functions' in payload:
functions = payload['functions']
functions = parse_input_functions(functions, credentials=credentials)
# 3. DATABASE CONNECTION
# :description: to access Watson IOT Platform Analytics DB.
logger.debug('Connecting to Database')
db = Database(credentials=credentials)
# 4. CREATE CUSTOM ENTITY FROM JSON
# 4.a Instantiate a custom entity type
entity_type = BaseCustomEntityType(name=payload['entity_type_name'],
db=db,
functions=functions)
# 4.b Publish kpi to register kpis and constants to appear in the UI
entity_type.publish_kpis()
# 5. CLOSE DB CONNECTION
db.release_resource()
return
def __init__(self, entity_type_name=None, entity_name=None):
# replace with valid table and column names
self.entity_type_name = entity_type_name
self.entity_name = entity_name
self.db_schema = "public" # only required if you are not using the default
self.table_name = entity_type_name.upper(
) # change to a valid entity time series table name
self.dim_table_name = "DM_" + self.table_name # change to a entity dimenstion table name
self.timestamp = 'evt_timestamp'
self.credentials = settings.CREDENTIALS
# logging.info('username %s' %self.credentials['db2']['username'])
# logging.info('password %s' %self.credentials['db2']['password'])
# logging.info('host %s' %self.credentials['db2']['host'])
# logging.info('port %s' %self.credentials['db2']['port'])
# logging.info('databaseName%s' %self.credentials['db2']['databaseName'])
self.db = Database(credentials=self.credentials)
def main(argv):
sys.path.append(
os.path.realpath(os.path.join(os.path.dirname(__file__), '..')))
credPath = os.path.join(os.path.dirname(__file__),
f"credentials_as_{os.environ['USERNAME']}.json")
print(f"Loading credentials from {credPath}")
with io.open(credPath, encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
db = Database(credentials=credentials)
from goodvibrations.predictStatus import PredictCondition
print(f"Registering function")
db.unregister_functions(["PredictCondition"])
try:
db.register_functions([PredictCondition])
except Exception as exc:
print(exc)
fn = PredictCondition(condition='predStatus')
df = fn.execute_local_test(db=db,
db_schema=db_schema,
generate_days=1,
to_csv=True)
print(df)
def query_db(query, dbtype='db2'):
if dbtype is 'db2':
with open('./dev_resources/credentials_as_dev.json',
encoding='utf-8') as F:
credentials = json.loads(F.read())
elif dbtype is 'postgres':
with open('./dev_resources/credentials_as_postgre.json',
encoding='utf-8') as F:
credentials = json.loads(F.read())
db = Database(credentials=credentials)
df = pd.read_sql_query(sql=text(query), con=db.connection)
csvname = dbtype + 'results' + time.strftime("%Y%m%d-%H%M%S")
df.to_csv('data/' + csvname)
db.connection.dispose()
def __init__(self, entity_type_name=None):
self.entity_type_name = entity_type_name
logging.info(self.entity_type_name)
self.db_schema = "public" # only required if you are not using the default
# self.table_name = entity_type_name.upper() # change to a valid entity time series table name
# self.dim_table_name = "DM_"+self.table_name # change to a entity dimenstion table name
# self.timestamp = 'evt_timestamp'
self.credentials = settings.CREDENTIALS
# logging.info('username %s' %self.credentials['db2']['username'])
# logging.info('password %s' %self.credentials['db2']['password'])
# logging.info('host %s' %self.credentials['db2']['host'])
# logging.info('port %s' %self.credentials['db2']['port'])
# logging.info('databaseName%s' %self.credentials['db2']['databaseName'])
self.db = Database(credentials=self.credentials)
self.entity_names = self.get_entity_names
if entity_type_name != None:
self.table_name = entity_type_name.upper(
) # change to a valid entity time series table name
self.dim_table_name = "DM_" + self.table_name # change to a entity dimenstion table name
self.timestamp = 'evt_timestamp'
self.http = urllib3.PoolManager()
EngineLogging.configure_console_logging(logging.DEBUG)
logging = logging.getLogger(__name__)
#db_schema = 'bluadmin' # set if you are not using the default
#with open('credentials_MAS-Demo.json', encoding='utf-8') as F:
# credentials = json.loads(F.read())
print("here")
#with open('credentials.json', encoding='utf-8') as F:
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 = []
import logging
import pandas as pd
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, func
from iotfunctions import bif
from iotfunctions.metadata import EntityType, BaseCustomEntityType
from iotfunctions.db import Database
from iotfunctions.enginelog import EngineLogging
EngineLogging.configure_console_logging(logging.DEBUG)
# replace with a credentials dictionary or provide a credentials file
with open('/Users/ryan/watson-iot/functions/scripts/credentials_as_dev.json',
encoding='utf-8') as F:
credentials = json.loads(F.read())
db = Database(credentials=credentials)
db_schema = None # set if you are not using the default
table = db.get_table('MIKE_ROBOT_JUNE_25')
dim = db.get_table('MIKE_ROBOT_JUNE_25_DIMENSION')
group_by = {
'plant_abv': func.left(table.c['plant_code'], 3),
'manufacturer': dim.c['manufacturer']
}
aggs = {'avg_speed': (table.c['speed'], func.avg)}
def prepare_aggregate_query(group_by, aggs):
# build a sub query.
sargs = []
import datetime as dt
import json
import pandas as pd
import numpy as np
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, func
from iotfunctions.base import BaseTransformer
from iotfunctions.metadata import EntityType
from iotfunctions.db import Database
from iotfunctions import ui
with open('credentials_as.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
db = Database(credentials=credentials)
from custom.multiply_by_factor import MultiplyByFactor
db.register_functions([MultiplyByFunction])
with open('credentials.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
'''
Developing Test Pipelines
-------------------------
When creating a set of functions you can test how they these functions will
work together by creating a test pipeline. You can also connect the test
pipeline to real entity data so that you can what the actual results that the
function will deliver.
'''
'''
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
})
'''
with open('credentials_as_dev.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
'''
Developing Test Pipelines
-------------------------
When creating a set of functions you can test how they these functions will
work together by creating a test pipeline. You can also connect the test
pipeline to real entity data so that you can what the actual results that the
function will deliver.
'''
'''
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.
When defining an entity type, you can describe the raw input data items for the
entity type, as well as the functions , constants and granularities that apply to it.
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.
import datetime as dt
import json
import os
import pandas as pd
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, func
from iotfunctions.preprocessor import BaseTransformer
from iotfunctions.bif import IoTExpression
from iotfunctions.metadata import EntityType, make_sample_entity
from iotfunctions.db import Database
#replace with a credentials dictionary or provide a credentials file
with open('credentials.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
#create a sample entity to work with
db_schema = None #set if you are not using the default
db = Database(credentials=credentials)
entity = make_sample_entity(db=db, schema = db_schema)
#examine the sample entity
df = db.read_table(entity.name,schema=db_schema)
df.head(1).transpose()
#configure an expression function
expression = 'df["throttle"]/df["grade"]'
fn = IoTExpression(expression=expression, output_name='expression_out')
df = entity.exec_pipeline(fn)
df.head(1).transpose()
better to keep track of changes to dimensional attributes so that when recalculating data, history is
not rewritten. AS provides a built in function called SCD Lookup for this.
This tutorial demonstrates how to:
-- create database tables to use as SCD lookups.
-- use SCD lookups in calculations
When using a simulation to provide data to an entity type you can get the simulation to produce
the SCD table/s and sample data. To do this, add an scds element to the parameters dict. The
scd element is a dict too. It is keyed on the name of the scd property and contains a list of
possible values.
'''
entity_name = 'scd_test' # you can give your entity type a better name
db = Database(credentials=credentials)
db_schema = None # set if you are not using the default
sim_parameters = {
"data_item_mean": {
'temp': 22,
'pressure': 320
},
"data_item_sd": {
'temp': 2,
'pressure': 5
},
"data_item_domain": {
'category_code': ['A', 'B', 'C']
},
"scds": {
import datetime as dt
import sys
import logging
import json
from iotfunctions.db import Database
from iotfunctions import bif
from iotfunctions.enginelog import EngineLogging
PACKAGE_URL = "https://github.com/sedgewickmm18/mmfunctions"
with open('credentials_as_dev.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
db = Database(credentials=credentials)
auth_token = {
"identity": {
"appId": "A:fillin:yourcred"
},
"auth": {
"key": "a-fillin-yourcred",
"token": "FILLINYOURTKENHERE"
}
}
class AuthToken:
def __init__(self, name, json):
self.name = name
},
"config": {
"objectStorageEndpoint": "https://undefined",
"bos_logs_bucket": "analytics-logs-beta-3-337dfd48872c",
"bos_runtime_bucket": "analytics-runtime-beta-3-fa4e4e54e2e8",
"mh_topic_analytics_alerts": "analytics-alerts-beta-3"
}
}
EngineLogging.configure_console_logging(logging.DEBUG)
db_schema = None
if (len(sys.argv) <= 1) or (sys.argv[1] != 'test'):
db = Database(credentials=credentials)
print(db.cos_load)
# if in test mode call execute()
ais = anomaly.SpectralAnomalyScore('Val', windowsize=12, output_item='zscore')
kis = anomaly.KMeansAnomalyScore('Val', windowsize=4, output_item='kscore')
print("Instantiated")
# if there is a 2nd argument do not register but exit
if (len(sys.argv) > 1):
sys.exit()
EngineLogging.configure_console_logging(logging.DEBUG)
import datetime as dt
import json
import pandas as pd
import numpy as np
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, func
from iotfunctions.base import BaseTransformer
from iotfunctions.metadata import EntityType
from iotfunctions.db import Database
from iotfunctions import ui
with open('credentials.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
db = Database(credentials=credentials)
from custom.functions import MultiplyByFator
db.register_functions([MultiplyByFator])
from custom.functions import MyCustomFunction
db.register_functions([MyCustomFunction])
Aug 26 1 30 min
Aug 26 2 8 hours
Aug 26 3 8 hours
Aug 27 1 7.5 hours
In the tutorial that follows you will use the ActivityDuration built in function
to calculate activity durations and see how it apportions durations over changing
dimensions like shift and maintenance crew. You will also learn how ActivityDuration
can handle cases where incoming activity data contains overlapping
time periods.
We will start by creating and entity type and some data.
'''
entity_name = 'merge_test' # you can give your entity type a better name
db = Database(credentials=credentials)
db_schema = None # set if you are not using the default
shift_dict = {"1": (5.5, 14), "2": (14, 21), "3": (21, 29.5)}
sim_parameters = {
"data_item_mean": {
'temp': 22,
'pressure': 320
},
"data_item_sd": {
'temp': 2,
'pressure': 5
},
"scds": {
'crew': ['A', 'B', 'C']
db_schema = 'bluadmin' # set if you are not using the default
with open('credentials_Monitor-Demo.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())
'''
Developing Test Pipelines
-------------------------
When creating a set of functions you can test how they these functions will
work together by creating a test pipeline.
'''
'''
Create a database object to access Watson IOT Platform Analytics DB.
'''
db = Database(credentials=credentials)
'''
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 = 'Clients02'
BI_USERNAME = settings.BI_USERNAME
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)
import json
import pandas as pd
import numpy as np
import logging
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, func
import iotfunctions.bif as bif
from iotfunctions.metadata import EntityType, LocalEntityType
from iotfunctions.db import Database
from iotfunctions.enginelog import EngineLogging
EngineLogging.configure_console_logging(logging.DEBUG)
with open('credentials_as_dev.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = 'BLUADMIN'
db = Database(credentials=credentials)
'''
You can serialize simple functions to Cloud Object Storage to avoid having to
paste replicas of them in the UI or avoid the need to manage them in a
git repository
See offline simple functions sample to see how to create simple functions.
Here is a simple function:
'''
def f(df, parameters=None):
# generate an 2-D array of random numbers
output = np.random.normal(1, 0.1, len(df.index))
return output
}
}
EngineLogging.configure_console_logging(logging.DEBUG)
'''
The db_schema is the db2_schema name for your client database. If
you are using the default schema of your user, there is no need to
provide a schema.
'''
db_schema = None
'''
Use the credentials to build an AS Database connection.
'''
if (len(sys.argv) <= 1) or (sys.argv[1] != 'test'):
db = Database(credentials=credentials)
print(db.cos_load)
# if in test mode call execute()
if (len(sys.argv) > 1) and (sys.argv[1] == 'test'):
np.random.seed([3, 14323])
df = pd.DataFrame(
dict(
col1=np.random.randint(400, 500, 40),
col2=np.random.randint(400, 500, 40),
#col2 = np.random.laplace(400,50,40)
))
print(df)
df2 = pd.read_csv('./anomalyoutput.csv',
parse_dates=['timestamp_date', 'metricTS_date'],
import json
import logging
import datetime as dt
from iotfunctions.db import Database
from iotfunctions.enginelog import EngineLogging
from poc.functions import State_Timer
import pandas as pd
from scripts.test_entities import Equipment
from iotfunctions.pipeline import JobController
logger = logging.getLogger(__name__)
with open('credentials_Monitor-Demo2.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = 'bluadmin'
db = Database(credentials=credentials)
entity_type_name = 'Container6'
entityType = entity_type_name
# Use create_entities_usingcsv.py to create Entity Type.
#db.drop_table(entity_type_name, schema = db_schema)
entity = Equipment(
name=entity_type_name,
db=db,
db_schema=db_schema,
description="Smart Connect Operations Control Center",
)
#entity.register(raise_error=False)
meta = db.get_entity_type(entityType)
import datetime as dt
import json
import pandas as pd
import numpy as np
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, func
from iotfunctions.base import BaseTransformer
from iotfunctions.metadata import EntityType
from iotfunctions.db import Database
from iotfunctions import ui
with open('credentials_as.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
db = Database(credentials=credentials)
from customSG.multiplybyfactorSG import MultiplyByFactorSG
fn = MultiplyByFactorSG(
input_items=['speed', 'travel_time'],
factor='2',
output_items=['adjusted_speed', 'adjusted_travel_time'])
df = fn.execute_local_test(db=db,
db_schema=db_schema,
generate_days=1,
to_csv=True)
print(df)
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, func
from iotfunctions.base import BaseTransformer
from iotfunctions.metadata import EntityType
from iotfunctions.db import Database
from iotfunctions import ui
'''
Basic storyline is that you are monitoring a crew of manufacturing robots
Sometimes errors in programming make robots follow roundabout paths to get their work done
'''
with open('credentials_as_dev.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
db = Database(credentials=credentials)
'''
Use PythonExpression to calculate distance traveled
'''
from iotfunctions.bif import PythonExpression
dist = PythonExpression(expression='df["speed"] * df["travel_time"] ',
output_name='distance')
dist.execute_local_test()
'''
Use PythonFunction to identify outliers
'''
EngineLogging.configure_console_logging(logging.DEBUG)
'''
This script shows how to use an entity's random dimension generator
'''
'''
1. Create a database object to access Watson IOT Platform Analytics DB.
# Getting Db credentials
# Explore > Usage > Watson IOT Platform Analytics > Copy to clipboard
# Paste contents in credentials_as.json file
# Save in scripts
'''
schema = 'bluadmin' # set if you are not using the default
with open('./scripts/credentials_as.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db = Database(credentials=credentials)
'''
2. To do anything with IoT Platform Analytics, you will need one or more entity type.
This example assumes that the entity to which we are adding dimensions already exists
'''
entity_name = 'issue_455_blank_script'
entity_type = db.get_entity_type(name=entity_name)
# get dimension table name - to add dimension values to
try:
dim_table_name = (
entity_type.get_attributes_dict()['_dimension_table_name']).lower()
except:
dim_table_name = entity_name + '_dimension'
# db.drop_table(dim_table_name, schema=schema)
def create_custom_entitytype(json_payload, credentials=None, **kwargs):
"""
creates an entity type using the given json payload
Uses the following APIs:
POST /meta/v1/{orgId}/entityType
POST /api/kpi/v1/{orgId}/entityType/{entity_type_name}/kpiFunctions/import
POST /api/constants/v1/{orgId}
:param json_payload: JSON describes metadata required for creating desired entity type
expected json schema is as follows:
```
example_schema = {
"type": "object",
"properties": {
"entity_type_name": {"type": "string"},
"metrics": {"type": "array", "items": {"type": "object"}},
"constants": {"type": "array", "items": {"type": "object"}},
"dimensions": {"type": "array", "items": {"type": "object"}},
"functions": {"type": "array", "items": {"type": "object"}},
"metric_timestamp_column_name":{"type": "string"}
},
"required": ["entity_type_name"]
}
```
example example_schema.metrics/dimensions property
```
[{
'name': 'metric_a',
'datatype': 'str'
# allowed column types number, boolean, literal/string, timestamp
# accepted datatypes: 'str'/'string, 'int'/'integer', 'number'/'float','datetime', 'bool'/'boolean'
}]
```
example example_schema.constants property
```
[{
'name': 'sample_constant_name',
'datatype' : 'number',
'value': 0.3,
'default': 0.3,
'description': 'optional'
# accepted datatypes: 'str'/'string, 'int'/'integer', 'number'/'float','datetime', 'bool'/'boolean'
}]
```
example example_schema.functions property
```
[{
'name': 'RandomUniform', #a valid catalog function name
# PARAMETERS REQUIRED FOR THE FUNCTION
# For example bif.RandomUniform needs these addition parameters
'parameters' :
{
'min_value' : 0.1,
'max_value' : 0.2,
'output_item' : 'discharge_perc'
}
}]
```
:param credentials: dict analytics-service dev credentials
:param **kwargs {
drop_existing bool delete existing table and rebuild the entity type table in Db
db_schema str if no schema is provided will use the default schema
}
:return:
"""
# 1. INPUT CHECKING
logger.debug('Performing Input Checking')
payload = validateJSON(json_payload) # input is valid json
validate(instance=payload,
schema=create_custom_schema) # input has valid schema
# 2. INPUT PARSING
metrics = None
constants = None
dimensions = None
functions = None
if 'metrics' in payload:
metrics = payload['metrics']
metrics = parse_input_columns(metrics)
if 'constants' in payload:
constants = payload['constants']
constants = parse_input_constants(constants)
if 'dimensions' in payload:
dimensions = payload['dimensions']
dimensions = parse_input_columns(dimensions)
if 'functions' in payload:
functions = payload['functions']
functions = parse_input_functions(functions, credentials=credentials)
# 3. DATABASE CONNECTION
# :description: to access Watson IOT Platform Analytics DB.
logger.debug('Connecting to Database')
db = Database(credentials=credentials)
# 4. CREATE CUSTOM ENTITY FROM JSON
# 4.a Instantiate a custom entity type
# overrides the _timestamp='evt_timestamp'
if 'metric_timestamp_column_name' in payload.keys():
BaseCustomEntityType._timestamp = payload[
'metric_timestamp_column_name']
# TODO: BaseCustomEntityType.timestamp= add user defined timestamp column
entity_type = BaseCustomEntityType(name=payload['entity_type_name'],
db=db,
columns=metrics,
constants=constants,
dimension_columns=dimensions,
functions=functions,
**kwargs)
# 4.b Register entity_type so that it creates a table for input data and appears in the UI
# Publish kpi to register kpis and constants to appear in the UI
entity_type.register(publish_kpis=True)
# 5. CLOSE DB CONNECTION
db.release_resource()
import datetime as dt
import json
import pandas as pd
import numpy as np
import logging
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, func
from iotfunctions.base import BaseTransformer
from iotfunctions.metadata import EntityType
from iotfunctions.db import Database
from iotfunctions import ui
from iotfunctions.enginelog import EngineLogging
EngineLogging.configure_console_logging(logging.DEBUG)
'''
This is a script used to genenerate the SQL used to import
built in functions into AS
'''
with open('credentials_as_dev.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
db = Database(credentials=credentials)
from iotfunctions import bif
db.register_module(module=bif, raise_error=True, force_preinstall=True)
def load_metrics_data_from_csv(entity_type_name,
file_path,
credentials=None,
**kwargs):
"""
reads metrics data from csv and stores in entity type metrics table
Note: make sure 'deviceid' and 'evt_timestamp' columns are present in csv
'evt_timestamp' column will be inferred to be current time if None present
:param entity_type_name: str name of entity we want to load data for
:param file_path: str path to csv file
:param credentials: dict analytics-service dev credentials
:param **kwargs {
db_schema str if no schema is provided will use the default schema
if_exists str default:append
}
:return:
"""
# load csv in dataframe
df = pd.read_csv(file_path)
# Map the lowering function to all column names
# required columns are lower case
df.columns = map(str.lower, df.columns)
# DATABASE CONNECTION
# :description: to access Watson IOT Platform Analytics DB.
logger.debug('Connecting to Database')
db = Database(credentials=credentials, entity_type=entity_type_name)
# check if entity type table exists
db_schema = None
if 'db_schema' in kwargs:
db_schema = kwargs['db_schema']
#get the entity type to add data to
entity_type_metadata = db.entity_type_metadata.get(entity_type_name)
logger.debug(entity_type_metadata)
if entity_type_metadata is None:
raise RuntimeError(
f'No entity type {entity_type_name} found.'
f'Make sure you create entity type before loading data using csv.'
f'Refer to create_custom_entitytype() to create the entity type first'
)
# find required columns
timestamp_col_name = entity_type_metadata['metricTimestampColumn']
logical_name = entity_type_metadata['name']
table_name = db_table_name(entity_type_metadata['metricTableName'],
db.db_type)
deviceid_col = 'deviceid'
required_cols = db.get_column_names(table=table_name, schema=db_schema)
missing_cols = list(set(required_cols) - set(df.columns))
logger.debug(f'missing_cols : {missing_cols}')
# Add data for missing columns that are required
#required columns that can't be NULL {'evt_timestamp','device_id','updated_utc','devicetype','rcv_timestamp_utc'}
for m in missing_cols:
if m == timestamp_col_name or m == 'rcv_timestamp_utc':
#get possible timestamp columns and select the first one from all candidate
df_timestamp = df.filter(like='_timestamp')
if not df_timestamp.empty:
df_timestamp_columns = df_timestamp.columns
timestamp_col = df_timestamp_columns[0]
df[m] = pd.to_datetime(df_timestamp[timestamp_col])
logger.debug(
f'Inferred column {timestamp_col} as missing column {m}')
else:
df[m] = dt.datetime.utcnow() - dt.timedelta(seconds=15)
logger.debug(
f'Adding data: current time to missing column {m}')
elif m == 'devicetype':
df[m] = logical_name
logger.debug(f'Adding data: {logical_name} to missing column {m}')
elif m == 'updated_utc':
logger.debug(f'Adding data: current time to missing column {m}')
df[m] = dt.datetime.utcnow() - dt.timedelta(seconds=15)
elif m == deviceid_col:
raise RuntimeError(f'Missing required column {m}')
else:
df[m] = None
# DATA CHECKS
# 1. Check pd.DataFrame data types against entitytype/database data types
# coerce data frame object data type to corresponding database-data_type
# Add None for missing columns (Not added to the db)
logger.debug(f'Dataframe columns before data check 1. {df.columns}')
entity_type_columns = entity_type_metadata['dataItemDto']
df = change_df_dtype_to_db_dtype(df, entity_type_columns)
logger.debug(f'Dataframe columns after data check 1. {df.columns}')
# 2. allowed device_id name: alpha-numeric + hypen + underscore + period + between [1,36] length
# Drop rows with un-allowed device_id names
logger.debug(
f'Dataframe has {len(df.index)} rows of data before data check 2')
df = df[df[deviceid_col].str.contains(r'^[A-Za-z0-9._-]+$')]
df = df[df[deviceid_col].str.len() <= 36]
logger.warning(
f'This function will ignore rows where deviceid has values that are not allowed'
)
logger.warning(
f'(NOTE) Allowed characters in deviceid string are: alpha-numeric/hypen/underscore/period with '
f'length of 1 to 36 characters')
logger.debug(
f'Dataframe has {len(df.index)} rows of data after data check 2')
# remove columns that are not required/ in entity type definition
logger.debug(f'Updating columns: {required_cols}')
df = df[required_cols]
logger.debug(f'Top 5 elements of the df written to the db: \n{df.head(5)}')
# write the dataframe to the database table
db.write_frame(df=df, table_name=table_name)
logger.debug(
f'Generated {len(df.index)} rows of data and inserted into {table_name}'
)
# CLOSE DB CONNECTION
db.release_resource()
#!/user/bin/env python3
import json
import logging
from iotfunctions.db import Database
from iotfunctions.enginelog import EngineLogging
EngineLogging.configure_console_logging(logging.DEBUG)
with open('../dev_resources/credentials_as_dev.json', encoding='utf-8') as F:
#with open('../dev_resources/cognio.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
db = Database(credentials=credentials)
#from custom.forecast import Cognio_NeuralNetwork_Forecaster
from custom.functions import SS_HelloWorld
db.register_functions([SS_HelloWorld])
# Past contents in a json file.
'''
#with open('credentials_staging.json', encoding='utf-8') as F:
with open('credentials.json', encoding='utf-8') as F:
#with open('credentials_dev.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
'''
Developing Test Pipelines
-------------------------
When creating a set of functions you can test how they these functions will
work together by creating a test pipeline.
'''
'''
Create a database object to access Watson IOT Platform Analytics DB.
'''
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 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 = 'kalbuildings'
entity_name = "KB_Robot_Type"
# meta = db.get_entity_type(entity_name)
# print(meta)
# db_schema = "BLUADMIN" # None # replace if you are not using the default schema
# db.drop_table(entity_name, schema = db_schema)
with open('credentials.json', encoding='utf-8') as F:
#with open('credentials_dev.json', encoding='utf-8') as F:
credentials = json.loads(F.read())
'''
Developing Test Pipelines
-------------------------
When creating a set of functions you can test how they these functions will
work together by creating a test pipeline.
'''
'''
Create a database object to access Watson IOT Platform Analytics DB.
'''
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 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 = settings.ENTITY_NAME or 'buildings'
db_schema = settings.DB_SCHEMA or "BLUADMIN" # None # replace if you are not using the default schema
db.drop_table(entity_name, schema = db_schema)
# Credentials to access Building Insights API.
USERNAME = settings.USERNAME
PASSWORD = settings.PASSWORD
# TENANT_ID = settings.TENANT_ID
You can test functions locally before registering them on the server to
understand how they work.
Supply credentials by pasting them from the usage section into the UI.
Place your credentials in a separate file that you don't check into the repo.
'''
credPath = os.path.join(os.path.dirname(os.path.abspath(sys.argv[0])),
'credentials_as.json')
print(f"Loading Analytics Service credentials from {credPath}")
with open(credPath, encoding='utf-8') as F:
credentials = json.loads(F.read())
db_schema = None
if 'postgresql' in credentials:
credentials['postgresql']['databaseName'] = credentials['postgresql']['db']
db = Database(credentials=credentials)
'''
Import and instantiate the functions to be tested
The local test will generate data instead of using server data.
By default it will assume that the input data items are numeric.
Required data items will be inferred from the function inputs.
The function below executes an expression involving a column called x1
The local test function will generate data dataframe containing the column x1
By default test results are written to a file named df_test_entity_for_<function_name>
This file will be written to the working directory.
'''
