datapundittechblogs

API gateway integration to get method and post method to  retrieve/insert data in DynamoDB using Lambda Watch the following video for hands on using AWS management console. API Gateway (Get/Post)----> Lambda ----> DynamoDB Integration 1 Get method integration in API Gateway to retrieve information from the DynamoDB table   a.       Create an IAM role Create an assumable IAM role. Following trust policy attached to it {   "Version": "2012-10-17",   "Statement": [     {       "Sid": "",       "Effect": "Allow",       "Principal": {         "Service": "apigateway.amazonaws.com"       },       "Action": "sts:AssumeRole"     }   ] } The role must have invokefunction permission   {     "Version": "2012-10-17",     "Statement": [         {             "Effect": "Allow&q

Click Here

Abinitio Phases and Checkpoints | Why Phases | Abinitio Checkpoints | Recovery and Resource Control Phases  and Checkpoints mechanism in Abinitio works on below tradeoffs: Speed/control  - More control makes graph slower  Speed /safety  - more safety (recovery point of view) makes graph slover primary objective of creating phases in graph is to efficient use of available resources such as CPU, main memory and disk space. phase break :  the boundary between 2 phases in called phase break. first phases does complete and then only the 2nd phase can be started. When the first phases completes, the Abinitio component immediately before the phase break  writes down all the data passing through it to the temporary files  under the layout of the component immediately after the phase break, the component after the phase break reads all these files to begin the next phase. check point: a point where Co operating system saves all the information it would need to restore the  job to its state. At

SCD-type 2 (Abinitio) - a follow up session a.dml record string("#") id; string("#") id; date("YYYY-MM-DD")("\n") id; end port0 = $AI_DML/a.dml =in0_metadata port1 = $AI_DML/a.dml =in1_metadata pdl1=$[add_field("record\nend","new",read_type("$AI_DML/a.dml"))] in0_metadata record record string("#") id; string("#") id; date("YYYY-MM-DD")("\n") id; end new; end pdl2=$[add_field("record\nend","old",read_type("$AI_DML/a.dml"))] in1_metadata record record string("#") id; string("#") id; date("YYYY-MM-DD")("\n") id; end old; end $[join_types(in0_metadata,in1_metadata)] record record string("#") id; string("#") id; date("YYYY-MM-DD")("\n") id; end new; record string("#") id; string("#") id; date("YYYY-MM

SCD-type 2 in Abinitio | SCD type 2 in Abinitio | How to implement SCD type 2 in Abinitio Lets take an example dataset to understand the SCD type 2 in Abinitio. For example . we have an input dataset which is already loaded in as dimension table already. vehicle dimension dataset sk, veh_cd,     curr_driver,hist_driver,start_dt,         end_dt,             curr_flag 1, veh01, dr0,  dr0,            19000101000000,39991231235959,Y 2, veh02, dr1,  dr1,            19000101000000,39991231235959,Y 3, veh03, dr2,  dr2,           19000101000000,39991231235959,Y 4, veh04, dr3,  dr3,           19000101000000,39991231235959,Y 5, veh05, dr5,          dr4,           19000101000000,20210220235959,N 6, veh05, dr5,          dr5,           20210221000000,39991231235959,Y Sk is Logical Surrogate Key created by Abinitio Process veh_cd - vehicle code of the driven vehicle curr_driver - current driver of the vehicle hist_driver - driver who drove the

SQL Server DB Database Back , S3 and store to RDS and vice versa Click Me We will try to understand the 2 things here : PART 1. Restore the DB from local DB to  RDS data base instance from S3 bucket  PART 2. Move (migrate dB )Database From Local DB to AWS RDS DB instance First of all part 1: Restore the DB from local DB to  RDS data base instance from S3 bucket  1.Create DB Instance 2. Create S3 Bucket 3. Local SSMS and local DB  4. Take backup of DB and Store it in S3 5. Create Option Group and modify to add restore capability and add to DB instance   6. Execute the restoration stored procedure Use Cases 1. Take On-premises SQL server DB back up to AWS Cloud (RDS DB instance) 2. Back Up of any SQL Server DB , may be taken from other Cloud can be moved to AWS RDS DB 3. From Local SQL Server DB to AWS RDS DB 4. From any other AWS account ( Cross Region) Secondly part 2    Move (migrate dB )Database From Local DB to AWS RDS DB instance 1. Create DB Instance 2. Create S3 Bucket 3. Local S

Following use cases were covered as part of the session: A i/p a,a,b,c,d,a,b,c,d o/p a,a,a b,b c,c d,d NORMALIZE ROLLUP normalize and rollup  out::length(in)= begin out::string_split(in.input_string,','); end; out::nomalize(index,in)= begin out.val::string_split(in.input_string,',')[index]; end; B I/P 11 14 15 13 16 12 18 20 O/P 11,12 13,14 15,16 18,20 SORT ROLLUP {} null key 11,12,13,14,15,16,18,20 NORMALIZE out::length(in)= begin length=length(string_split(in.input,','))/2; end; out::normalize(in , index)= begin let string(",")[int] values_vec =string_split(in.input,','); out.field1::values_vec[index]; out.field2::values_vec[index+1]; end; C Input: DID, revenue 1,90 3,30 7,48 Output: DID,Revenue 1,90 2,90 3,30 4,30 5,30 6,30 7,48 SCAN DID,revenue,gap,prv_day_revenue,prv_did 1,90,1,0,NA 3,30,2,90,1 7,48,4,30,3 NORMALIZE DID,revenue,prv_day_revenue,prv_did 1,90,0,NA 2,90,90,1 3,30,90,1 4,30,30,3 5,30,30,3 6,30,30,3 7,48,30,3 D. Input 1 2 3 4 5

https://youtu.be/JdDDpjZcv-4 next_in_sequence() working in Abinitio: Definition -      within a single process(component) , this gives successive number.   next_in_sequence()>1 i.e. all records whose occurance is 2 and more in the file.      for example dataset below:    employeeName ram shyam ravi hanuman shekhar seeta geeta shayama vidushi vishakha If we process by condition next_in_sequence()>1 then the output will be [first record will be removed] shyam ravi hanuman shekhar seeta geeta shayama vidushi vishakha 2. next_in_sequence() with reformat  - rely -  FieldA 1a 2a 3a 4a 5a 6a 7a Example 1 (serial processing)--> Reformat   count=2      select:   next_in_sequence()<900      Transofom1:   out:reformat(in)=   begin   out.*::in.*;   out.Seq-No::next_in_sequence();   end;      Transform2:   out:reformat(in)=   begin   out.*::in.*;   out.Seq-No::next_in_sequence();   end;      Output_indexes()=   begin   out::if(next_in_sequence()%2==0) [vector 0,1] else [vector 0,1];   e

  Abinitio Reformat - Run Time Behavior Watch my you tube channel for the explanation: Steps: Be mindful that each out port has corresponding error and reject port, the record will be redirected to these ports based on the action generated in the respective transform of the associated output port. 1. Reformat reads the record 2. if select is defined then , if select return True then record goes further else dropped out to be no where be caught , If select expression OR select transform results into NULL then graph does error out and stops execution 3.  if select is not defined then input records further processed 4.  now it turns to pass from transform function   a. if output index or output indexes are not defined then record will be send to all output ports b. if any of these functions defined then the record will be route from that respective port and send across to the respective out put port. In case of both parameters defined( i.e. we have both output index and output indexes con

  Abinitio Reformat Parameters Description, Configuring Reformat Component, Reformat Parameters. Watch my you tube video for detailed explanation : REFORMAT Common purpose of REFORMAT component are below: 1. transforming the data 2. drop the fields  3. datatype conversion  PARAMETERS : 1. count - 1,2 , 3  2. Select ( JUST like Filter By Expression but we can not capture the filtered records in this)   (you can use Select Expression OR Select transform function depending on your need) 2. transform 0,1,2... 3. output_index out:output_index(in)= begin out.x::if(in.region=='Europe') 0 out.x::if(in.region=='APAC') 1 out.x::if(in.region=='North America') 2 end 4. output_indexes out:output_indexes(in)= begin out.x::if(in.region=='Europe') [vector 0] out.x::if(in.region=='APAC') [vector 1,2] out.x::if(in.region=='North America') [vector 2] end 5.  logging true/false  , LOGX, 6.  reject threshold - abort on first reject,

Conduct It > - Graph calling plan - How graph can call/communicate plan                            Publisher - Subscriber - Pub Sub mechanism in Abinitio Please view my video as below: There are 2 ways where we can fit in Publish to plan mechanism 1. Batch Graph -> Subscribe Looping Plan 2. Publisher   -> Subscribe Looping Plan 1. Batch Graph -> Subscribe Looping Plan are below: a. Create a Plan  named as "$AI_PALN/mysubscriber.plan" b. Add Subplan in "$AI_PALN/mysubscriber.plan" and Make it Looping plan - Subscribe Loop plan c.. Create New Graph named "$AI_MP/plan_to_sbscribe.mp" and build as it as below-:   Create Data -> Reformat -> Publish to Plan  count=10 out::create(index)= begin out.plan_name:1:if(index==0) "a.pset"; out.plan_name:2:if(index==1) "b.pset"; out.plan_name:3:if(index==2) "c.pset"; out.plan_name:4:if(index==3) "d.pset"; out.plan_name:5:if(index==4) "

The Input DataSet is as follws: Acc_No, Acc_holder, Credit_month,Credit_Amount      month_no 001, xxxx, JAN, 1000 1 001, xxxx, FEB, 500 2 002, yyyy, JAN, 800             ===> 1 002, yyyy, AUG, 2300 8 002, yyyy, DEC, 500 12 003,    zzzz, May, 200 5 Problem Statement: For each acc holder the credit month and the  corresponding credit amount will be stored.  It's not necessary to have the amount for all the 12  months and in case of missing , value '0' needs to be populated.  We need one record per acc_holder in the output with  12 months data along with the total.  Steps for Approach: 1. Generic the missing #s -> Normalize (12)  2. Aggregate the records -> Rollup Expected O/p:  Acc_No |Acc_holder      | monthly_amt_summary           |    Total  001 | xxxx | 1000 ,500,0,0,0,0,0,0,0,0,0,0|    1500 002 | yyyy  | 800 ,0,0,0,0,0,0,2300,0,0,0,500|    3600 003 |