datapundittechblogs

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

     Abinitio Interview Questions 25 Day to Day Abinitio Commands        Watch my YouTube video for explanation :                     a. How to know depth of MFS    m_expand -n <mfs> b. air sandbox diff   ==> Display the differences between 2 graphs     comparing 2 files in different sandboxes air sandbox diff  <sandbox1-path>/a.dml <sandbox2-path>/a.dml comparing sandbox and TR file air sandbox diff my-sandbox/dml/b.dml comparing graph of sandbox with current eme version graph air sandbox diff -version current my-sand/mp/p.mp comparing TR version and sandbox graph air sandbox diff <version of eme>  my-sand/mp/p.mp c. count the no of fields in a file head -1 file-name | sed 's/|/\n/g'| wc -l d. plan-admin set parameter-name new-value e. m-password -prompt/-password   -restrict restriction | -unre...

Abinitio Interview Questions 24 Abinitio Parallelism   Advanced Watch my YouTube video for explanation :              Abinitio Parallelism component parallelism The AI component who increases component parallelism                         replicate input file reformat FBE PBE dedup sorted split The AI component who decreases component parallelism gather                 join fuse combine concatenate pipeline parallelism                         SORT ROLLUP JOIN Dedup Sorted Phases / Check Points component folding Continuous Graphs:  Checkpoint-->  Compute points   data parallelism types of layouts:     data layout     processing layout file:serial mfile:mfs...

                           Abinitio Interview Questions 23 Multifile System Part 2 Watch my YouTube video for explanation :                Please look into the class notes here for your references: b. Use m_partition command m_partition <source-url-path> <destination-url-path> <DML> [KEY]    partitions  a serial file into a multifile    repartition a multifile into another multifile   c. m_mv, m_cp ,m_gzip,m_gunzip d. If same depth - copy serial location one by one (manual work around) target server: m_touch mfs-file-name src cp <partition#0 of source> <partition#0 of destination>  cp <partition#1 of source> <partition#1 of destination>  cp <partition#n-1 of source> <partition#n-1 of destination> 

Watch my YouTube video for explanation :                                                                       a. Create a graph and use                                                                        <all-to-all> Input->Partitioning Component     <      =      >    gather/merge -> Output Fore more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

Unix Miscellaneous Commands  | 10 Useful UNIX commands For Class Notes: Unix  Commands and Use Cases- 1. tar command  what is it , why we use it tar <options> <archivename> <files to be archived> tar -xzvf myfiles.tar.gz tar -xzvf myfiles.tar.gz -C /dir/test tar -tvf myfiles.tar.gz tar -czvf myfiles.tar.gz /dir/test2 2. source command source abc.ksh 3. mount command   4. split split -l 200 myfile datapunit datapunita  datapunitb  datapunitc datapunitd datapunite split -b 200k myfile datapundit datapunita  datapunitb  datapunitc datapunitd datapunite 5. df /du df  mounted on , size in blocks , free , used , df --total du -d 1 /home/mandeep/test du -c -h /home/mandeep/test du -a -h /home/mandeep/test 6. sed '/exit/d' filename.txt 7. sed '2,$ s/unix/linux/' geekfile.txt 8. awk '/UUID/ {print $0}' /etc/fstab 9. finger David cat emailist.txt  a@gmai.com b@datapunit.org.in 10. echo -e "Hi There, \n Please Find the attached ...

Different ways to create Abinitio MFS | How to  Different ways to create Abinitio MFS | How Different ways to create Abinitio MFS | How to create MFS in Abinitio | multifile system in Abinitio For class notes as below: 1.  build_mfs -env <path-to-stdenv>/AB_ENV_ROOT data_areas disk1/p1 disk2/p2 -mfs-depth 4 Or single Directory MFS - hdfs , s3a,gs,azure(blob) - wasb wasbs azure datalake-abfss mvs s3a:////bucket/pdir/ gs://hotname/pdir/ wasb://containeraccount/dir/path build-mfs -basedir /abinitio/apps/ste-enable/dataPundit/stdenv -mfs-depth 8 -mfs-mount /abinitio/data/papaEarth/dataPundit/mfs -data-areas /abinitio/data/papaEarth/dataPundit/mfs/parts build-mfs -dynamic -singledir -basedir /abinitio/apps/sandboxes/papaEarth/Projects/sand/stdenv -mfs-depth 64 -mfs-mount /abinitio/data/papaEarth/dataPundit/mfs 2. m_mkfs  3. install_environement mfs-depth, data_areas 4. m_touch - //datapundit.in/user/nd/mfs/mfs-4way/dust/a.dat //datapundit.in/user/nd/mfs/mfs-4way/dus...

Watch my YouTube video for explanation : Please look into the class notes here for your references: DynamoDB Schema Design      **Limit the access pattern **Throttling  meaning the reads are more than defined RCU **data latency , storage Use NOSQL WorkBench Create Directly in DynamoDB Use PartiQL   [Partition Key]           {Primary Key} [Partition Key,Sort Key]  {composite primary key} Partition Key  - Storage Index Sort Key  - Ordering  dept, opening_date 1, 12-03-2022 2, 22-09-2020 2, 21-12-2020 3, 17-06-2021 4, 16-05-2021 Partitions  --->partition 1    1, 12-03-2022 3, 17-06-2021 partition 2     2, 22-09-2020 2, 21-12-2020 Case I when we lookup of a known global unique key empid, joining _date 1, 16-05-2021 2, 21-12-2020 3, 17-06-2021 use the partition key Case II when the key is non unique OR when the...

Interview Question 18   AI Commands Watch my YouTube video for explanation : Please look into the class notes here for your references: 23.1  Size of MFS file (No of records in MFS file) m_dump <dml> <url-path> | grep 'Record'| wc -l 23.2  size in bytes $[file_information("file-path").size] 23.3  total no of records in a mfs file  m_dump <dml> <datafile> print-n-records m_dump -string "   " <data file> print-n-records better use  m_dump <dml> <datafile> no-print-data print-n-records 23.4  listing of the files directory_listing("path",pattern="a*") For more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

Watch my YouTube video for explanation : Please look into the class notes here for your references: My I/p file is field 1,field 2     f1,f2 10,A 10,C 20,B  ==> 20,B 30,C 30,A    --->10,20,30 ----------------------> INPUT ->Reformat ->                                     --> FUSE the data                     --->A,B,C->next_in_seq->sort->C,B,A-> My O/P will be 10,C 20,B 30,A Fore more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

Watch my YouTube video for explanation : Please look into the class notes here for your references: I/P airport,from,to  Mumbai,f1,f2             Mumbai,f2,f1  Delhi,f1,f2              Delhi,f2,f1 Pune,f1,f2 Bangalore,f1,f2 Chennai,f1,f2 Chennai,f2,f1 Hyderabad,f1,f2 Pune,f2,f1 Delhi,f2,f1 Delhi,f1,f2 Chennai,f1,f3 Chennai,f3,f1 o/p airport,from,to  (fields) Mumbai,f1,f2 Delhi,f1,f2 Pune,f1,f2 Bangalore,f1,f2 Chennai,f1,f2 Hyderabad,f1,f2 Chennai,f1,f3 -->  input as is        ->       INPUT -> Replicate(2) JOIN as Key(F1,F2,F3)  -> Collect Unused0,Output    -->  suffled col 2 and 3 -> Unused1 -----------------> Gather  --->Final Output Output-->Dedup on f1 ----> Fore more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube c...

Watch my YouTube video for explanation : Please look into the class notes here for your references: Get the employee getting Nth highest salary? provide multiple solutions. 1. Using dense_rank analytical function in sql 2. using sub query 3. using scan component in abinitio sol1 with T as   ( select *, dense_rank() over(order by sal desc) as RANK from employee    )  select name, salary from T where RANK=N Sol2 select name, salary from employee A       where N-1 = (select count(1) from employee B where B.salary>A.salary) sol3 scan , Assign Rank Flag and Filter in output_select function Fore more Abinitio, AWS and data engineering videos please subscribe , view , like and share my YouTube channel  Click  DataPundit

Watch my YouTube video for explanation : Please look into the class notes here for your references: Write Excel files DML : utf8 record= begin string(‘\t’) sheet; string(‘\0’) line; end  Create Data num_records=2 out.sheet::if(index==0) 'customer' else 'revenue'; out.line:: if(index==0) 'customerid\tzip\taddress' else 'revenueID\trevenueAmt\trevenueDate' Customer Reformat out.sheet::'customer'; out.line::"'"+ "\t" + in.customerid              + "\t" + in.zip + "\t" + in.address + "'"; Revenue Reformat out.sheet::'Revenue'; out.line::"'"+ "\t" + in.revenueID              + "\t" + in.revenueAmt + "\t" + in.revenueDate + "'";   Parameters: xls:$AI_SERIAL/excels/relationsheep.xls write-mode:newworkbook/apend-records utf8:True date-format:yyyy-mm-dd autosizelumn:True record-delimiter:\0 use-format:True format-at...

Watch my YouTube video for explanation : Please look into the class notes here for your references: Local Secondary Index: - FormPost   Table Partition Key= ForumName Sort Key (Range Key) = LastPostDateTime   {     TableName: " FormPost ",     KeyConditionExpression: " ForumName = :a and LastPostDateTime =:t",     ExpressionAttributeValues: {         ":a": "EC2",         ":t": "2015-02-12:11:07:56"     } } Only one sort key is applicable for a DynamoDB table so applying expression condition becomes difficult. Approach 1 – Scan it Read Capacity costs money – costly and poor latency Approach 2 -  FormName==’S3’   narrow down the data                    ...