datapundittechblogs

                                Nested Vectors in Abinitio                                                                                                      Nested Vectors Fixed Length Nested Vector string(2)[6][3][2] =      [vector         [vector  vector ['a','b','c','d','e','f'], vector ['g','h','i','j','k','l'], vector ['m','n','o','p','q','r'] ], [vector  vector ['1a','1b','1c','1d','1e','1f'], vector ['1g','1h','1i','1j','1k','1l'], vector ['1m','1n','1o','1p','1q','1r'] ] ]; ===============================================             Constructing Nested Vector: Normalization level 1 Normalization Level 2 Normalization Level 3                  

            Abinitio Interview Scenario Based Question 38            Watch my YouTube Video for this as below:  Solve Using Abinitio  Input DeptID  teacher    IsAssigned D1      Teacher1   1 D1      Teacher2   1 D1      Teacher3   0 D2      Teacher1   0 D2      Teacher2   1 D2   Teacher3   0          DeptID   Teacher1    Teacher2   Teacher3 D1 1 1 0 D2 0 1 0 Input--->Rollup(DeptID)-> Output teporary type= begin decimal("") Teacher1; decimal("") Teacher2; decimal("") Teacher3; end; out::initialize(temp,in)= begin out.Teacher1::0; out.Teacher2::0; out.Teacher3::0; end; out::rollup(temp,in)= begin out.Teacher1::if(in.Teacher=='Teacher1' and in.IsAssigned ==1) 1; out.Teacher2::if(in.Teacher=='Teacher2' and in.IsAssigned ==1) 1; out.Teacher3::if(in.Teacher=='Teacher2' and in.IsAssigned ==1) 1; end; out::finalize(temp,in)= begin out.DeptID::in.DeptID; out.Teacher1::temp.Teacher1; out.Teacher2::temp.Teacher2;

                                    Abinitio Dense Rank Evaluation by Scan without transform expansion         Watch my yourube video here: 2.  Same as Dense_Rank() function is SQL         INPUT-> SORT (dept, sal desc) -> Scan-> OUTPUT Rank Evaluation by Scan without transform expansion Use SCAN with key deptId. let int _d_ranking_ =0;   // use global variable to track/update the rankings  out::scan(int)= begin //previous() fuction returns NULL for the first record in each group,  //so the assignment is such situations is 1. _d_ranking_=first_defined(if(previous(in.sal)!=in.sal) _d_ranking_+1 else _d_ranking_,1); out.*::in.*; out.dense_rank::_d_ranking_; end; deptId , empId, Sal,            Drank D1,       E1,      890 2 D1,      E2, 750 3 D1,      E9, 750 3 D1,    E3, 895 1 D1,    E11, 740 4 D2,      E4,      800 2 D2,      E5, 900 1 D3,      E8,      500 1 For More Abinitio AWS Database content please visit my yo

       Rank Evaluation by Scan without transform expansion      1. Same as Rank Function is SQL Use SCAN with key deptId. Input -> sort (dept,sal dec) -> SCAN  ->  Output let int _ranking_ =0;   // use global variable to track/update the count  out::scan(int)= begin //previous() fuction returns NULL for the first record in each group,  //so the assignment is such situations is 1. _ranking_=  first_defined(if(previous(in.sal)!=in.sal) count() else _ranking_,1); out.*::in.*; out.rank::_ranking_; end; For example Input: deptId , empId,      sal  D1,      E1,              890 D1,      E2,      750 D1,      E9,      750 D1,    E3,      895         D1,      E11,            740 D2,      E4,              800 D2,      E5,      900 D3,      E8,              500 Output: deptId , empId, Sal, Rank    Drank D1,      E1,       890 2 2 D1,     E2, 750 3 3 D1,      E9, 750 3 3 D1,    E3, 895 1 1 D1,    E11, 740 5 4 D2,      E4,     

                                                    Recursion In Abinitio watch my youtube video as below                                                                          Input as well Lookup labeled as "EmpMgrDtails" having fields as EMPID, MGRID EMPID, MGRID Level topmostmgrid 1007, 1006 4 1006, 1003 3 1003, 1002 2 1002, 1001 1 1001, 1000 0 3007, 3006 5 3006, 3003 4 3003, 3002 3 3002, 3001 2 3001, 1001 1 4008, 4002 2 4002, 4000 0 4003, 4002 2 5009, 5004 4 5004, 4003 3 9900, 9001 2 9001, 9000 1 9000, 8000 0 ========== The Input dataset will be used as input and lookup as well. Input data -> Refromat (lookup) -> Output out:reformat(in)= begin out.empid :: in.EMPID; out.topMgrId :: top_mgrid(in.EMPID,in.MGRID); //Call the top_mgrid recursive function end; //topmgid is a recusive function which returns the ul

  Abinitio Interview Questions 33 - Sample Normalize code            The Sample code for this as below: Input serialNO 1 2 3 Output 1 2 2 3 3 3 out::length(in) begin out::in.value; end; out::normalize(in, index)= begin out.value::in.value + index; end; For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

  AWS Managed Streaming for Kafka (Govern how your clients interact with Apache Kafka using API Gateway)         Watch my You tube video on this: Follow the steps below to create the data pipeline: STEP1 Networking Part VPC , Public and Private Subnets, Internet Gateway, NAT Gateway , S3 end point , Route Tables , Security Groups STEP2  Launch MSK Cluster in the same VPC as created in STEP1 , unauthorized access allowed , plaintext encryption Make sure the security group remain as it is. STEP3 Launch Linux EC2 Choose the Same VPC , create in Public Subnet In the list Auto-assign Public IP, choose Enable. STEP4 Once the client for Amazon MSK has been created, the security group rules must be configured to allow the connection between the cluster and the  client ec2 machine that we have just created. For that , Add the security group id of ec2 to MSK cluster security group all traffic Repeat these steps to add an inbound rule in the security group that corresponds to your client computer

                    Primary Keys ,  Foreign Keys, Identity column Watch YouTube Video:                               Primary Keys ,  Foreign Keys, Identity column CREATE TABLE auto.Departments ( Id INT NOT NULL identity , Name VARCHAR (25 ) NOT NULL , PRIMARY KEY (Id )  ) ; INSERT INTO auto.Departments ( Name ) VALUES (  'HR' ) , ('Sales' ), ('Tech ' ); select * from auto.Departments CREATE TABLE auto.Employees ( Id INT NOT NULL identity , FName VARCHAR (35 ) NOT NULL , LName VARCHAR (35 ) NOT NULL , PhoneNumber VARCHAR (11 ), Managerid INT , Departmentid INT NOT NULL , Salary INT NOT NULL , HireDate DATETIME NOT NULL , PRIMARY KEY (Id ) ) ; (INSERT INTO auto.Employees  (  FName , LName , PhoneNumber, Managerid , Departmentid , Salary , HireDate ) VALUES ('James','Smith','1234567890',NULL,1,1000,CONVERT(DATETIME,'2004-01-01')), ('John','Johnson','2468101214',1,1,400,CONVERT(DATETIME,'2007-03-23')),

  Normal Forms in Relational Database Management System: TO watch YouTube Video ,      raw-data-: name  , phone  city,  state Ram,    919876543210 , 918987654327, Banaglore,                             KA Dave,    919876543212 , 918987654328, Banaglore,                             KA Ali,    919876543213 Jaipur,                                  RJ Milkha,    919876543219,  918987654329, Bathinda,                                PA 1st Normal Form - 1. each table cell contain a single value (atomic) 2. each record needs to be unique eliminate the repetition Solution: name    ,  Phone ,    City,    State Ram ,  919876543210,       Banaglore, KA Ram ,  918987654327,       Banaglore, KA Dave                   ,  919876543212,         Banaglore, KA Dave                   ,  918987654328,         Banaglore, KA Ali                         ,  919876543213,      Jaipur, RJ Milkha,                   919876543219          Bathin

Please watch my video on catalog management in abinitio: For class notes: Catalog Management in Abinitio Problem Statement - How to share the same lookup/or Set of lookups in different graphs 1. Sharing Lookup file with catalogs a. Graph settings -> Catalog - create lookup catalog  ,  specify the catalog path $AI_SERIAL_LOOKUP/shared_catalog.cat b. Graph settings -> Catalog - uses lookups from catalog  , specify the catalog path $AI_SERIAL_LOOKUP/catalog.cat c. Graph settings -> Catalog - does not use catalog (by default)                                                     2. Use Dynamic Subgraph ( Recommended) Catalog Management Commands: Make sure you do either of these below: AB_CATALOG=$AI_SERIAL_LOOKUP/mycatalog.cat specify -catalog <catalogurl>  in commands 1. ls -l $AI_SERIAL_LOOKUP .cat 2. m_lscatalog -catalog mycatalog.cat Table - employee Key -  DATAURL -  Metadata URL - 3. Add a lookup to catalog a. m_catalog_add -catalog $AI_SERIAL_LOOKUP/mycatalog.cat -tab

Watch my you tube video below:                For Class notes: MIME types-  datapundit Why some objects developed in GDE can be checked in as is?? List Of Extention List at Project Level. *.dml  text/x-abinitio-dml *.job   ignore *.mfctl ignore *.mp  application/x-abinitio-mp air project show <eme-projet-path> air project files <eme-prj-path>  -versions -all -basedir <sandbox-path> How to modify extention list:- air project modify <Project-path-eme> -extention "extention-pattern" <mime-type> air project modify <Project-path-eme> -remove -extention <extention-pattern> How to know the mime type of an object? Apply the MIME type to a specific project. air project set-type <eme-path-of-object> <extenstion-type> GDE - While Check In For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

                                       Watch my youtube episode:                     For Class notes please visit: SQL Operations by dataPundit Episode 1.  Introduction - Why SQL operations and Major Constituents SQL (Structured Query Language) HiveQL  Spark SQL NewSQL - RDBMS(ACID) + no sql(Scalability) KQL (Kusto Query Language) -azure PartiQL -dynamodb  - aws Federated Query a.  Who Uses SQL ? SQL Developer,  DBA ,  Database Developer,  Data Analyst,  Business Analyst,  DataEngineers,  Data scientist,  Business Operation Analyst, Operation Engineer,  Sales Manager, Product Managers, Pricing Managers b. Huge Demand -  spark sql ,   no sql db,  hive sql   c. It helps understand the logic and other languages d. open source , easy to learn For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

                        For Video Explanation please watch my you tube video:                    For Class notes please see below: ACID : Atomicity: Either all or None T1  transfer 5000 from account a to account b read(a)  -op1 a=a-5000 - op2 write(a) -op3 read(b) b=b+5000 write(b)- system crashed, power goes,techinical or non technical commit; Consistency T1  transfer 5000 from account a to account b total money before the starting of the transaction must be equal to total money after the trasaction take place. before transaction A  - 2000 B  - 3000 T1 need to trasfer 1000 rupees from A to B After transaction: A - 1000 B - 4000 Isolation: T1    T2 parallel transactions T1  transfer 1000 rupees from A to B  T2  transfer 2000 rupees from B to C T1  T2 Durability: T1 transfer 1000 rupees from A to B  read(a)  -op1 a=a-5000 -op2 write(a) -op3 read(b)  -op4 b=b+5000 -op5 write(b) -op6 commit;  -op7 A = 1000 B = 4000 For more Abinitio, AWS and data engineering videos please subscribe , view

                                   SCAN Parameters in abinitio See my youtube video explanation: Class notes as below: Scan - functionality - coummulative summary .  key - 1) key specifier 2) key method - key_change  sorted input- false/true max_core - 64 mb  reject threshold -  maintain order - true  grouped input - true /false  major key - region id , minor key - dept id. check sort - sorted input , key method -key specifier For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

                                             Abinitio Interview Questions 34 For Class Notes please visit:                     i/p a,a,b,c,d,a,b,c,d  record string("\n") input_str; end out::length(in)= begin out::length_of(string_split(input_str,','); end; field a a b c d a b c d RollUp  o/p a,a,a b,b c,c d,d INPUT-> NORMALIZE-> Rollup(field) For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

                   Abinitio Interview Questions 32                Watch my YouTube video for explanation :         Solve Using Abinitio Input DeptID  teacher    IsAssigned D1      Teacher1   1 D1      Teacher2   1 D1      Teacher3   0 D2      Teacher1   0 D2      Teacher2   1 D2 Teacher3   0          DeptID   Teacher1    Teacher2   Teacher3 D1 1 1 0 D2 0 1 0 Input--->Rollup(DeptID) teporary type= begin decimal("") Teacher1; decimal("") Teacher2; decimal("") Teacher3; end; out::initialize(temp,in)= begin out.Teacher1::0; out.Teacher2::0; out.Teacher3::0; end; out::rollup(temp,in)= begin out.Teacher1::if(in.Teacher=='Teacher1' and in.IsAssigned ==1) 1; out.Teacher2::if(in.Teacher=='Teacher2' and in.IsAssigned ==1) 1; out.Teacher3::if(in.Teacher=='Teacher2' and in.IsAssigned ==1) 1; end; out::finalize(temp,in)= begin out.DeptID::in.DeptID; out.Teacher1::temp.Teacher1; out.Teacher2::temp.Teacher2; out.Teacher

Abinitio Interview Questions 31 m_eval commands Watch my YouTube video for explanation :                            Quick Commands in Abinitio 35.1 concatenation of sring m_eval 'string_concat("abc","cde")' abccde 35.2 m_eval -print-type -no-print-value 3.14159 double 35.3 quick testing of functions m_eval -include $AI_XFR/myfunctions.xfr 'getRate(1890,'JAN')' 35.4 in the context of pset cat .project.pset DML|En|||sandbox/dml m_eval -context .project.pset "'\$DML is ' +  \$DML" "$DML is sandbox/dml" 35.5 m_eval 'lookup("ProductList","908").description' SSD Drive 987 35.6 m_eval '(date("YYYYMMDD")) (today() -10)'  for example if today=20230520 20230510 For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

          Abinitio Interview Questions 30 M_dump commands Watch my YouTube video for explanation :                m_dump video done m_dump file.dml mfs -start 1 -end 10 m_dump loader.dml mfile:mfs8/tempfile.dat -partition 3  m_dump loader.dml mfile:mfs16/tempfile.dat -select 'empid==90' m_dump loader.dml mfile:mfs32/tempfile.dat -print-no-data print-n-records m_dump loader.dml mfile:mfs32/tempfile.dat print-n-records m_dump loader.dml mfile:mfs32/tempfile.dat -record 12 For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

                                        Abinitio Interview Questions 29 Dynamic Layout Watch my YouTube video for explanation :                     What is Dynamic MFS layout - video done          What is it: fixed depth variable depth dynamic mfs - build-mfs -dynamic -fixed depth dynamic layout How it is being done: fixed depth mfile:dynamic:n  OR mfile:dynamic:$DEPTH  variable depth mfile:dynamic:-1:data-path[:MB-per-partition[:max-depth]] -1 meaning its COP decided the depth of paralleism at runtime not the user Creating Dynamic Single Directory MFS: build-mfs -dynamic -singledir mfs-depth 64 -mfs-mount s3://my-bucket/mfs-64way laypout:  s3://my-bucket/mfs-64way  OR mfile:dynamic:64 What are advantages of Dynamic Layout: a. Migration   b. Promotion c. Collaboration and Resue For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataP