10+ Knowledge Planet Interview Questions and Answers

To remove header and trailer from a sequential data file in Datastage.

• Use Sequential File stage in Datastage.

• Set the 'Skip Rows' property to the number of header rows to be skipped.

• Set the 'Trailer Rows' property to the number of trailer rows to be skipped.

• Use a Transformer stage to remove any remaining header or trailer rows.

• Use the 'Remove' function in the Transformer stage to remove the rows.

Deleting duplicate rows in SQL

• Use the DISTINCT keyword in SELECT statement to retrieve unique rows

• Use GROUP BY clause to group rows with same values and then use aggregate functions to select one row

• Use the ROW_NUMBER() function to assign a unique number to each row and then delete the rows with duplicate numbers

To find process id in Linux, use the command 'ps -aux | grep '

• Open the terminal

• Type 'ps -aux' to list all running processes

• Use 'grep ' to filter the process you are looking for

• The process id (PID) will be listed in the second column

Reading data from a .log file and extracting columns with a specific regex.

• Use Python's built-in 're' module to define the regex pattern.

• Open the .log file using Python's 'open' function.

• Iterate through each line of the file and extract the desired columns using the regex pattern.

• Store the extracted data in a data structure such as a list or dictionary.

To kill a job in Datastage

• Stop the job manually from the Director client

• Terminate the job from the command line using the dsjob command

• Kill the job process from the operating system level

• Delete the job from the Datastage repository

SORT BY, ORDER BY, CLUSTER BY, and DISTRIBUTE BY are SQL clauses used for data sorting and partitioning.

• SORT BY is used to sort the result set in ascending or descending order based on one or more columns.

• ORDER BY is used to sort the result set in ascending or descending order based on one or more columns. It is similar to SORT BY but can be used with other clauses like LIMIT and OFFSET.

• CLUSTER BY is used to group data based on a specific column. It is used to improve query p...read more

SQL queries with window functions

• Window functions perform calculations across a set of rows that are related to the current row

• Common window functions include ROW_NUMBER, RANK, DENSE_RANK, and NTILE

• Window functions are used with the OVER() clause to define the window or subset of rows to perform the calculation on

Optimizations for data engineering

• Use indexing to speed up queries

• Partition data to improve query performance

• Use caching to reduce data retrieval time

• Optimize data storage format for faster processing

• Use parallel processing to speed up data processing

• Optimize network bandwidth usage

• Use compression to reduce storage and network usage

Small file problem refers to the issue of having a large number of small files in a storage system.

• Small files can cause inefficiencies in storage and processing.

• Solutions include consolidating small files into larger ones or using a different storage system.

• Examples include Hadoop's SequenceFile format and Amazon S3's object size optimization.