Live Big Data Mock Interview | Techno Managerial #interview | PySpark, Hive, SQL, Python #question

[Music]

In this section, Chandali introduces herself and initiates the interview with Akash, discussing his background in data engineering and the technologies he has worked with.

Introduction and Background

• Chandali introduces herself as the interviewer and prompts Akash to provide a brief overview of his background.

• Akash shares that he graduated in 2018 and has been working in data engineering since then, mentioning his experience with technologies like Apache Hadoop, Spark, HDFS, ELK stack, and Azure Stack.

• He highlights his work in retail and finance industries over the past five years.

Data Integration and Pipeline

• Akash explains the two parts of his work: on-premise system using Apache tools for data movement to HDFS and cloud integration using Azure Data Factory for orchestration.

• The on-premise system involves connecting sources with Apache tools to move data to HDFS for processing through Spark jobs before storing it back in HDFS.

• In Azure Data Factory, orchestration is done by calling databricks notebooks for processing existing data within Azure.

Data Warehousing and Analytics

• Akash mentions creating Hive tables within Spark for analytics teams to connect and prepare solutions in a retail domain context focusing on sales aggregations and purchase order details.

• The data dealt with includes approximately 70-80 GB weekly for reporting purposes involving both daily and weekly jobs.

Purchase Order Processing

This section delves into how Akash configures Spark clusters based on job requirements, optimizations made to enhance job performance, and the time taken for job completion.

Cluster Configuration

• Akash describes using a 4-node cluster with specific memory allocations per node along with executor configurations tailored based on varying data sizes across multiple jobs rather than dedicating the entire cluster to one job.

Job Optimization

Understanding Join Techniques in Spark

The discussion revolves around different join techniques in Spark, focusing on broadcast join and sort merge join, their advantages, and how they impact data processing efficiency.

Exploring Broadcast Join and Sort Merge Join

• Broadcast Join:

• Utilized for joining small tables with larger ones.

• Involves hashing a smaller table and distributing it to all nodes for efficient joining without shuffling data.

• Sort Merge Join:

• Implemented when data is distributed across multiple machines.

• Requires shuffling of data between machines for joining, impacting computation power and job completion time.

• Techniques Behind Avoiding Shuffling:

• Broadcasting involves copying a smaller table to all nodes, eliminating the need for shuffling during joins.

• In sort merge join, bucketing ensures that data with the same key is present in the same partition/machine, reducing shuffling requirements.

Importance of Minimizing Data Shuffling

• Significance of Avoiding Shuffling:

• Shuffling refers to moving data between machines during computations or transformations.

• Minimizing shuffling reduces processing time and computational resources.

Optimizing Spark Jobs Through Data Filtering

This segment delves into optimizing Spark jobs by filtering out non-essential data before initiating joins to enhance performance efficiency.

Preparing Data Before Joins

• Data Preparation Steps:

• Remove non-essential data beforehand to streamline processing.

• Conduct necessary operations or transformations on individual tables prior to joining for optimized performance.

• Importance of Data Filtering:

• Filtering out unnecessary data at the initial stage minimizes computational load during subsequent operations like joins.

Understanding Partitions in Spark Jobs

The conversation shifts towards comprehending partitions in Spark jobs, their role as task units computed by executors, and their relationship with CPU cores for efficient job execution.

Partitioning Logic and Task Distribution

• Partition Definition:

• Partitions act as logical units processed by individual executors within a Spark job.

• Determining Partition Count:

• The number of partitions can be defined based on CPU cores available for task distribution.

Assigning Partitions and CPU Cores Relationship

The discussion revolves around the relationship between the number of partitions and CPU cores in a data processing scenario.

Understanding Partitioning and CPU Core Allocation

• Each partition can be processed by one core, so if there are T cores available on a machine and 20 partitions, the first 10 partitions will be computed by the first set of cores, followed by the remaining partitions.

• The number of CPU cores assigned is directly related to how fast a job needs to be executed. Critical jobs may require assigning one core per partition for optimal performance, while non-critical tasks can have fewer cores allocated.

• The allocation of CPU cores depends on the criticality and speed requirements of the job. For instance, assigning more cores than necessary to a job with fewer partitions would result in underutilization.

Proportional Relationship between Cores and Partitions

• The allocation of CPU cores should be directly proportional to the criticality and speed requirements of the job. Overallocating or underallocating cores can impact job performance significantly.

Partitioning vs. Bucketing: Real-time Scenarios

Exploring scenarios where partitioning or bucketing is preferred based on real-world use cases.

Use Cases for Partitioning and Bucketing

• Partitioning is beneficial when dealing with limited unique values like vendors in sales data. It allows for efficient searching based on specific criteria without scanning unnecessary data folders.

• Bucketing using hash functions is preferred when handling many non-unique rows. By grouping similar records together based on hash values, it optimizes querying performance for datasets with repetitive data patterns.

Hash Function Explanation

New Section

In this section, the discussion revolves around the concept of hash tables and their role in organizing and querying data efficiently.

Understanding Hash Tables

• Hash tables help differentiate various data points into unique sets for efficient querying.

• A hash table is a structure that maps keys to values, aiding in quick data retrieval.

• Drawing parallels with Python dictionaries, a hash table stores key-value pairs with corresponding indexes for data storage.

Collision Handling in Hash Tables

• Collisions occur when different indexes have the same key or value, leading to conflicts that need resolution.

• Techniques like collision handling are employed to address collisions effectively within hash tables.

New Section

This segment delves into exploring various aspects related to hash functions, partitions, and optimizing partition numbers for enhanced performance.

Optimizing Partition Numbers

• When using joins in Spark, by default, approximately 200 partitions are created which can be excessive in some cases.

• Techniques such as setting Shuffle partitions property or enabling Advanced Query Execution (AQE) can help reduce unnecessary partitions and enhance efficiency.

Understanding Colis vs. Repartition

• Colis involves compressing partitions on the same machine before shuffling occurs while repartition shuffles all data together.

• It is advisable to use colis over repartition for reducing overhead as it optimizes local machine processing before initiating shuffling operations.

New Section

This part focuses on addressing issues related to partition skewness and implementing solutions like salting to distribute data evenly across partitions.

Resolving Partition Skewness

• Data skewness occurs when certain keys dominate a partition, causing imbalance and performance issues during transformations like group by operations.

• Implementing techniques such as salting helps distribute keys evenly across partitions, mitigating skewness problems effectively.

Understanding Data Skewness and Optimization Techniques

The discussion revolves around data skewness in partitions and the optimization techniques to address performance issues related to skewed data distribution.

Dealing with Data Skewness

• Data partitioning issue where one partition holds 90% of the data, causing performance delays.

• Scenario with one partition having 90% of the data while others remain empty or underutilized.

• Contemplating between using repartition or coalesce to address data skewness for optimal performance.

• Opting for repartition due to its full shuffling capability, essential in scenarios with significant data skewness.

Understanding Transformations and Optimization Strategies

Exploring transformations in Spark, focusing on narrow transformations, stages involved, lazy evaluation concept, and RDD usage considerations.

Transformations and Stages

• Discussion on narrow transformations like GroupBy for aggregations and filtering as examples.

• Understanding the staging process when using white transformations like ReduceByKey.

• Clarifying tasks, stages, and job components in Spark processing based on the number of partitions.

Lazy Evaluation Concept and RDD Usage

Delving into lazy evaluation in Spark, its significance in optimizing resource utilization, and preferences between DataFrame APIs and RDD based on use cases.

Lazy Evaluation and RDD Utilization

• Defining lazy evaluation as deferring execution until an action is triggered for optimized resource management.

• Preference towards DataFrame APIs for efficiency unless granular operations necessitate RDD usage.

Optimization Techniques in Spark Processing

Exploring optimization strategies such as Catalyst Optimizer in Spark processing to enhance performance through intelligent query planning.

Optimization Strategies

• Highlighting Catalyst Optimizer's role in optimizing queries by avoiding unnecessary computations through intelligent optimizations.

Error Handling and Memory Management

In this section, the speaker discusses error handling related to driver memory issues and provides insights on troubleshooting strategies.

Driver Memory Allocation

• The error is attributed to driver memory concerns, suggesting a need to check transactions like broadcast joins that may exceed allocated driver memory.

• Solutions involve adjusting broadcast sizes or increasing driver memory based on specific scenarios to optimize performance.

Memory Optimization Strategies

This part delves into optimizing memory allocation for partitions and cores in Spark jobs.

Partition Memory Allocation

• Adjusting partition sizes based on data volume per partition can enhance computation efficiency.

• Prioritizing between driver and executor memory debugging is crucial for effective troubleshooting and optimization strategies.

Partitioning Strategy in Spark Jobs

The discussion focuses on determining the optimal number of partitions for efficient job execution in Spark.

Partitioning Considerations

• Illustration of file joining scenario with varying file sizes emphasizes the importance of partition configuration.

• Calculating appropriate partition numbers based on file sizes ensures job efficiency and resource utilization.

Optimizing Partition Size

Insights are shared regarding adjusting block size and partition numbers for improved performance in Spark jobs.

Block Size Adjustment

• Adapting block size to match job requirements can enhance processing speed and resource utilization.

• Tailoring partition numbers to suit specific job needs prevents resource wastage and optimizes performance.

Handling Small File Issues

Addressing challenges related to small files in data processing workflows through optimization techniques.

Small File Management

• Strategies involving reducing partition numbers or utilizing optimization commands help mitigate issues caused by numerous small files.

New Section

In this section, the speaker discusses the process of identifying the top two customers every month based on sales data.

Understanding Top Customers Identification

• The goal is to identify the top two customers each month with the highest sales.

• Sales data for each customer ID is summed up monthly to determine top customers.

• Grouping data by ID and month allows calculation of total sales using window functions.

• Syntax for finding top customers involves utilizing ranking functions.

New Section

This segment delves into understanding the difference between Rank and Dense Rank in SQL queries.

Differentiating Rank and Dense Rank

• Dense Rank is preferred when needing consecutive rankings without gaps.

• Rank may skip ranks if values are tied, while Dense Rank ensures sequential ranking.

New Section

A discussion on null comparisons and logical evaluations in SQL queries.

Null Comparisons and Logical Evaluation

• Null comparisons always result in false due to SQL restrictions.

• Exploring case statements for logical evaluations in SQL queries.

New Section

Analyzing inner joins and row counts in SQL operations.

Inner Joins Analysis

• Explanation of inner join results based on key matches between tables.

New Section

Differentiating semi join and anti join concepts in database operations.

Semi Join vs. Anti Join

• Semi join retains unmatched rows from one table, excluding them from results.

New Section

Finalizing discussions with a brief overview of normalization versus denormalization in Hive databases.

Normalization vs. Denormalization

Understanding Denormalization in Data Warehousing

The discussion revolves around the concept of denormalization in data warehousing, comparing it with normalization and highlighting its advantages.

Advantages of Denormalization over Normalization

• Denormalization involves breaking down tables into smaller ones to avoid duplicate data, which helps in reducing storage requirements.

• In data warehousing, denormalization is preferred as it eliminates the need for multiple joins when querying data, unlike normalization.

• Using factor dimensions in denormalization allows for having a table with all measurable values present, reducing the necessity for extensive joins to obtain final data.

Implementing Data Structures in Python and PySpark

The conversation shifts towards practical implementation by creating dictionaries and loops in Python and PySpark.

Creating Output from Dictionary

• Task involves creating an output like 'A2 B2 C2' from a given dictionary by iterating through its elements.

• Looping through the dictionary's items and printing them out sequentially achieves the desired output efficiently.

Data Manipulation in Python and PySpark

Exploring data manipulation further by converting lists into data frames using Python and PySpark.

Converting List to DataFrame

• Request to convert a list into a table-like structure (data frame) using Python or PySpark for efficient data handling.