CloudStorage Bucket

cs-ew1-prj-data-dm-sust-ppd-staging/CO2_Emission/Processed

cs-ew1-prj-data-dm-sust-ppd-staging/CO2_Emission/Processed

Automated extraction of shipment details in order to calculate their CO2 emissions

Step 1: Compute the Year & Previous Month and store in a variable (in the format YYYYMM). 

Step 2: Compute meta run id using DataOcean framework job and store in a global variable

Step3: Build the BW query and pass the query to the Step 4.

Step4: Extract the data from xtract and load the csv to the cloud storage bucket.

Job: J1000_BW_Query_csv_to_GCS

Step 5: Using the Data Ocean reference jobs, process the above file in step 4 into STG & ODS tables.

ref to data ocean bucket to STG jobs: 2 - Move data from bucket to staging

STG to ODS: 3 - Move data from staging to ods

Step6: Extract the data from the ODS table for that batch execution

Job: J1100_BW_Query_ods_to_Bucket

Step 7: Using the Data Ocean reference jobs, Write the success logs into the log table.

Use XtractUniversal tool to retrieve BW queries and upload the result to GCS

1.Extract the data from xtract and load the csv to the cloud storage bucket. Once the csv file bucket load is completed then delete the file in local Talend server.

Extract the data from the ODS table for that batch execution based on Meta_Execution_ID.

Query:

"SELECT * FROM "+ context.l_VAR_GCP_PROJECT_ID+"."+ context.l_LOCAL_VAR_STAGING_TO_ODS_DATASET_ODS + "."+context.l_LOCAL_VAR_STAGING_TO_ODS_Target_TABLE + " where meta_execution_id='" + context.Meta_Execution_ID + "'". 

The values for above context's can be found in the RDS database.

cs-ew1-prj-data-dm-sust-test-staging/CO2_Emission/Final/YYYYMM_TALEND_DEV_CO2_BW_QRY_MVSDTR01_0010.xlsx