datapundittechblogs

            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(i...

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

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

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

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

                    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(D...

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

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

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

                        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)  -o...

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

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

Abinitio Interview Questions 28 Abinitiorc Files Watch my YouTube video for explanation :                               What are the different abinitiorc files    30.1  Global Abitiorc file /etc/abinitio/abinbitiorc             At the level of host and it is accessible for all installation under the same host             a. its an optional abinitiorc file  b. it is global in nature as its declaration impacts all installation done under the host c. the variable of this can not be overridden d. can use include statement in this config file Use cases: AB_CHARSET AB_OUTPUT_FILE_UMASK UNIX Windows Super User(Admin)                     30.2. Server installation specific abinitiorc file $AB_HOME/config/abinitiorc   ...

Abinitio Interview Questions 27 Date Algorithms Watch my YouTube video for explanation :                 Take the 2 dates and create end date  for first and last quarter. (First date of the quarter , Last Date of the quarter) There will be 2 output fields in the output file. DATE1  = 25102022/ 24022022 DATE2  = 24082023 / 11072023 fqdt=25102022  01102022 lqdt=24082023  30092023 Yr_part1=2022 = string_Substring(DATE1,5,4); Yr_part2=2023 = string_Substring(DATE2,5,4); let string(",")[int] quarter_dates1=["0101"+Yr_part1,'0104'+Yr_part1,'0107'+Yr_part1,'0110'+Yr_part1]; let string(",")[int] quarter_dates2=["3112"+Yr_part2,'3009'+Yr_part2,'3006'+Yr_part2,'3103'+Yr_part2]; for (i,i<4) begin qstartdt = if((date("DDMMYYYY))quarter_dates1[i]<(date("DDMMYYYY))fqdt) (date("DDMMYYYY))quarter_dates1[i]; qenddt = if((date("DDMMYYYY))quarter_dates2[i]>(date("DDMMYYYY))lqdt) (date...

Watch my YouTube video for explanation :                Get the unique values from the input data Input code, country 1, India 2, Mumbai 1, USA 2, Newyork 1,          UK 2, Edinburgh 2, London output code, countries 2, India,Mumbai 2, USA,Newyork 2, UK,Edinburgh,London type temporary_type= record string(",") l_countries; decimal("") cnt; end; temp::initialize(in)= begin temp.l_countries::in.country; temp.cnt::0; end; out::key_change(in1,in2)= begin out::if(in1.code==1); end; out::rollup(temp,in)= begin out.l_countries::if(temp.cnt!0) string_concat(temp.countries,',',in.country); out.cnt::temp.cnt+1; end; out::finalize(temp,in)= begin out.code::in.code; out.countries::temp.l_countries; end; output code, countries 2, India,Mumbai 2, USA,Newyork 2, UK,Edinburgh For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel...