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. '''