You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

Description

It is a API connection to get manufacturing data.  It is required authenticate from windows user, which Talend does not have the function to do. Therefore, it is required Python to run.

The python environment is setup in folder \data\[Env]\DATA_OCEAN_DOMAIN_INDUSTRIAL\Python

There are 3 main API that we use in the eBatch project

1. Event Frame with calculation

1.1 URL :  https://pivision.eua.solvay.com/piwebapi/assetdatabases/F1RD0WI4NHGJw0uG4fwriJz93welqtozWz30OpJS7GtHMFHgQUNFVzFQU1RFS1BBRjAzXEdCVSBTT0RBIC0gQUxMIFBMQU5UUw/eventframes?templateName=Batch_Record_Pharma

Option:

&starttime=*-24h    (last 24 hours)

&startime=%272024-05-19%2010:40:00%27  (specific start time)

It will list all event frames in the selected time. 

1.2 Each event frame is required to get the list of Attributes in the link of attribute. We can specific only the fields value that we need by using parameter "selectedFields"

https://pivision.eua.solvay.com/piwebapi/eventframes/F1Fm0WI4NHGJw0uG4fwriJz93w7OER3RAm7xGECgKK4iH3qQQUNFVzFQU1RFS1BBRjAzXEdCVSBTT0RBIC0gQUxMIFBMQU5UU1xFVkVOVEZSQU1FU1tFQkFUQ0ggRVZFTlQgRlJBTUUgR0VORVJBVElPTjIgMjAyNC0wNi0wOSAwMzozMTozOC40MDBd/attributes?selectedFields=Items.Name;Items.Links.Attributes;Items.Links.Value

We will get the value directly from Batch_ID, Weight and Silo by getting from Value links (point 1.4)

Sometime we will get error batch id but we keep it as the same as source

1.3 The rest of the attribute, which is not starting with Granulom and not equals to Batch_ID, Weight and Silo are required to access to Attribute link in order to get sub attribute (Avg, Low_limit, Upper_limit, Max, Mean, Min, Std)

1.4 Get the value from the Value link

2. Event Frame with value in time series - Granulom

The same steps as Event Frame with calculation on point 1 and 2 but the Attribute will be get only the name starting with "Granulom". Then instead of get the sub attribute, it is required to get the values during the event frame in "RecordedData" link

3. Tags data

3.1 Find the "Web ID" by using this URL: https://pivision.eua.solvay.com/piwebapi/points/search?dataServerWebId=F1DSUZOarN2bd0yKBpfAHpVUXAQUNFVzFQU1RFS1BEQTAx&query=TOR.BIR.SILO.SILO.SILO.CB_M_BIR_Calcio_BIR_2&selectedFields=Items.WebId

The red text is the tag name in this file List of Tag (column N). For example, in this project for Torrelavega, we will get only 5 tags, which control by variable l_VAR_eBatch_tag_TOR_tag

  • TOR.BIR.SILO.SILO.SILO.SY_L2_PPM_CA_BIR           = F1DPUZOarN2bd0yKBpfAHpVUXAraMBAAQUNFVzFQU1RFS1BEQTAxXFRPUi5CSVIuU0lMTy5TSUxPLlNJTE8uU1lfTDJfUFBNX0NBX0JJUg
  • TOR.BIR.SILO.SILO.SILO.SY_L2_COL_L                     = F1DPUZOarN2bd0yKBpfAHpVUXArqMBAAQUNFVzFQU1RFS1BEQTAxXFRPUi5CSVIuU0lMTy5TSUxPLlNJTE8uU1lfTDJfQ09MX0w
  • TOR.BIR.SILO.SILO.SILO.CB_M_BIR_Colorimetría_a = F1DPUZOarN2bd0yKBpfAHpVUXAyKMBAAQUNFVzFQU1RFS1BEQTAxXFRPUi5CSVIuU0lMTy5TSUxPLlNJTE8uQ0JfTV9CSVJfQ09MT1JJTUVUUsONQV9B
  • TOR.BIR.SILO.SILO.SILO.CB_M_BIR_Colorimetría_b = F1DPUZOarN2bd0yKBpfAHpVUXAyaMBAAQUNFVzFQU1RFS1BEQTAxXFRPUi5CSVIuU0lMTy5TSUxPLlNJTE8uQ0JfTV9CSVJfQ09MT1JJTUVUUsONQV9C
  • TOR.BIR.SILO.SILO.SILO.CB_M_BIR_Colorimetría_L = F1DPUZOarN2bd0yKBpfAHpVUXAyqMBAAQUNFVzFQU1RFS1BEQTAxXFRPUi5CSVIuU0lMTy5TSUxPLlNJTE8uQ0JfTV9CSVJfQ09MT1JJTUVUUsONQV9M

Example output of the link will be 


3.2 Get the value by using this URL: https://pivision.eua.solvay.com/piwebapi/streams/[WebId]/recorded?starttime=2024-06-07%2000:26:52

Python

Example file of python file

  1. It is the main URL to call in order to get another url 
  2. Output from run 1 on web (required authorization to get the result)
  3. Selected link will be called to get the result
  4. Call the selected link URL  + the new parameter  on 
  5. Example of the full URL 
  6. It is the result of the second call.  The field can be select by parameter "selectedFields=Items.Timestamp;Items.Value"

Talend

Project IND_EBATCH

1. Job Detail

  1. tFileDelete: Delete the log file from the python name DATA\[env]\IND\IND_EBATCH\py_log.txt.  All the jobs use the same file name and it will delete before. 
  2. tJava: To generate .py file in DATA\[env]\IND\IND_EBATCH\pyCodeyyyyMMddHHmmss.py by reading input parameter from the Flow job and the RSD parameter.  Example of python code
  3. tSystem: To run the py file.
    1. The output of the screen will be kept in DATA\[env]\IND\IND_EBATCH\py_log.txt.
    2. The extraction will be generated in DATA\[env]\IND\IND_EBATCH\Output\[API interface]\file.csv
  4. tGSPut:  To upload csv file to GCS prj-data-sad-ebatch-[env]/cs-ew1-prj-data-sad-ebatch-[env]-staging/[API interface]
  5. tFileDelete: Delete csv files
  6. tFileDelete: Delete py file

2. Flow job 

2.1. Get the last load from table STG.incremental_loading

2.2. Run the job detail that generate py file and run the python to get the output

2.3. Standard job to load to STG and ODS

2.4. Update the last load from the event frame start date and tag timestamp to the STG.incremental_loading

Remark: PI_TOR_TAG has many tags. Therefore, it will get the minimum date of the max loading date.

3. Access rights

To access to PI, it is required david.genet@solvay.com to grant the authorization to access the pi (window account - SVC_TALEND_ADMIN_DEV ) first in order to run the python (authenticate by window account). If promote, it is required to ask authorization.

4. Source

https://pivision-dev.eua.solvay.com/piwebapi/

5. Format

Json web API

Destination

Location

Bucket = cs-ew1-prj-data-sad-ebatch-[env]-staging and it will keep in each folder for each table

DataOean GCP = N/A

Product GCP = prj-data-sad-ebatch-ppd

Plan job is PL_EBATCH_PI_TO_ODS

Main job is F100_PI_TOR_TO_BQ

Detail Job

STG

ODS

F001_PI_TOR_EFRAME_TO_BQSTG_EXT_TOR_0000_F001_I_H_pi_eframeODS_EXT_TOR_F001_I_H_pi_eframe
F002_PI_TOR_EFRAME_GRANULOM_TO_BQSTG_EXT_TOR_0000_F001_I_H_pi_eframe_granulomODS_EXT_TOR_F001_I_H_pi_eframe_granulom
F010_PI_TOR_TAG_TO_BQSTG_EXT_TOR_0000_F001_I_H_pi_tagODS_EXT_TOR_F001_I_H_pi_tag


Format

Event Frame with calculation
Event Frame with value in time series - Granulom
Tags data

Sizing

Less than 100 records / hour

Assessment

    How to validate that the generated output is valid: Compare with the output from the web API

starttime='2024-04-19 19:40:00' 

starttime='2024-06-07 00:26:52'


Loading

1. Incremental Load 

The main job is F100_PI_TOR_TO_BQ to trigger loading of 3 PI data sources by using table STG.incremental_load as the last successful loading to use this time to send to URL parameter to get the start time from this point

The step 2.4 of the Flow job, it will use job T101 for Tag and Event Frame while T103_UPDATE_LAST_EXTRACT_FROM_TAGS.  This job will be a special select sql because the extraction will get many tags at the same time and the start time is not the same for each tags. Therefore, we need to select minimum start time among the tags. 

2. Reloading data

It is flexible to do the reload by selected starttime on table STG.incremental_load

3. Scheduling

    PL_EBATCH_PI_TO_ODS run every hour on Mon - Fri

4. Timing

    An event frame will take around 2 - 4 min

    For tags, it is very quick

Criticality

High 

Logging

    1. Monitor number of records within a day

select job.job_name, job.meta_start_date, logs.meta_run_id, logs.meta_source_system, logs.meta_step, logs.meta_status, logs.meta_num_lines, logs.meta_error_lines from STG.log_tables logs join STG.run_jobs job on logs.meta_run_id = job.meta_run_id
where  logs.meta_run_id in (SELECT meta_run_id FROM STG.run_jobs order by meta_start_date desc limit 100)
and job_name like '%PI%'
and meta_step = 'Bucket to Staging'
and meta_start_date >  DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
order by job.meta_start_date desc

     We should NOT have 0 record.  If it is 0 normally, it mean that python is error. (if python is error, the log will be OK in Talend log)

    2. Monitor last update date. The result should be the same current date. 

SELECT * FROM STG.incremental_loading
where meta_file_name like '%PI%'
order by meta_file_name

  • No labels