Joins in Interview

 CREATE TABLE table1 (
    id INT
);


CREATE TABLE table2 (
    id INT
);

INSERT ALL

INTO table1 (id) VALUES (1)

INTO table1 (id) VALUES (1)

INTO table1 (id) VALUES (2)

INTO table1 (id) VALUES (2)

INTO table1 (id) VALUES (3)

INTO table1 (id) VALUES (0)

INTO table1 (id) VALUES (NULL)

SELECT 1 FROM DUAL;

SELECT * FROM hr.table1

INSERT ALL

INTO table2 (id) VALUES (0)

INTO table2 (id) VALUES (0)

INTO table2 (id) VALUES (4)

INTO table2 (id) VALUES (5)

INTO table2 (id) VALUES (NULL)

INTO table2 (id) VALUES (NULL)

SELECT 1 FROM DUAL;

SELECT * FROM hr.table2;

Below is a clean, Blogger/Blogspot-friendly version. I’ve removed the broken formatting, normalized the SQL, and converted the tables so they render cleanly in a blog post.

SQL JOIN Examples – INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN

One of the most important concepts to understand when working with SQL JOINs is that duplicate matching values can multiply the number of records returned.

In this example, we will create two tables and understand the number of records returned by:

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

• FULL OUTER JOIN

---

1. Create the Tables

CREATE TABLE table1 (
    id INT
);

CREATE TABLE table2 (
    id INT
);

---

2. Insert Data into Table 1

For Oracle, we can use INSERT ALL:

INSERT ALL
    INTO table1 (id) VALUES (1)
    INTO table1 (id) VALUES (1)
    INTO table1 (id) VALUES (2)
    INTO table1 (id) VALUES (2)
    INTO table1 (id) VALUES (3)
    INTO table1 (id) VALUES (0)
    INTO table1 (id) VALUES (NULL)
SELECT 1 FROM DUAL;

Check the data:

SELECT *
FROM table1;

Table 1

ID
1
1
2
2
3
0
NULL

Total records = 7

---

3. Insert Data into Table 2

INSERT ALL
    INTO table2 (id) VALUES (0)
    INTO table2 (id) VALUES (0)
    INTO table2 (id) VALUES (4)
    INTO table2 (id) VALUES (5)
    INTO table2 (id) VALUES (NULL)
    INTO table2 (id) VALUES (NULL)
SELECT 1 FROM DUAL;

Check the data:

SELECT *
FROM table2;

Table 2

ID
0
0
4
5
NULL
NULL

Total records = 6

---

4. INNER JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
INNER JOIN table2 t2
    ON t1.id = t2.id;

An INNER JOIN returns only the records where there is a matching value in both tables.

The only matching value between the two tables is:

0

Table 1 contains one 0.

Table 2 contains two 0s.

Therefore:

1 × 2 = 2 records

Result

TABLE1_ID	TABLE2_ID
0	0
0	0

INNER JOIN = 2 records

---

5. LEFT JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id;

A LEFT JOIN returns:

1. All records from Table 1

2. Matching records from Table 2

3. NULL for Table 2 columns when there is no match

Let's calculate:

Table 1 Value	Count in Table 1	Matching Rows in Table 2	Output Rows
1	2	0	2
2	2	0	2
3	1	0	1
0	1	2	2
NULL	1	0	1
Total	7		8

Therefore:

2 + 2 + 1 + 2 + 1 = 8

LEFT JOIN = 8 records

The result will conceptually look like:

TABLE1_ID	TABLE2_ID
1	NULL
1	NULL
2	NULL
2	NULL
3	NULL
0	0
0	0
NULL	NULL

Notice that the NULL from Table 1 is still returned because a LEFT JOIN preserves every row from the left table.

---

6. RIGHT JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
RIGHT JOIN table2 t2
    ON t1.id = t2.id;

A RIGHT JOIN returns:

1. All records from Table 2

2. Matching records from Table 1

3. NULL for Table 1 columns when there is no match

Let's calculate:

Table 2 Value	Count in Table 2	Matching Rows in Table 1	Output Rows
0	2	1	2
4	1	0	1
5	1	0	1
NULL	2	0	2
Total	6		6

Therefore:

2 + 1 + 1 + 2 = 6

RIGHT JOIN = 6 records

The result will conceptually look like:

TABLE1_ID	TABLE2_ID
0	0
0	0
NULL	4
NULL	5
NULL	NULL
NULL	NULL

---

7. FULL OUTER JOIN

SELECT t1.id AS table1_id,
       t2.id AS table2_id
FROM table1 t1
FULL OUTER JOIN table2 t2
    ON t1.id = t2.id;

A FULL OUTER JOIN returns:

• All matching records

• All unmatched records from Table 1

• All unmatched records from Table 2

Let's calculate it step by step.

Matching Records

The only matching value is 0.

Table 1:

0 → 1 occurrence

Table 2:

0 → 2 occurrences

Therefore:

1 × 2 = 2 matching records

Unmatched Records from Table 1

The following records don't have a match in Table 2:

1
1
2
2
3
NULL

Total:

6 unmatched records

Unmatched Records from Table 2

The following records don't have a match in Table 1:

4
5
NULL
NULL

Total:

4 unmatched records

Therefore:

2 matching records
+ 6 unmatched Table 1 records
+ 4 unmatched Table 2 records
--------------------------------
= 12 records

FULL OUTER JOIN = 12 records

---

8. Final Result

JOIN Type	Number of Records
INNER JOIN	2
LEFT JOIN	8
RIGHT JOIN	6
FULL OUTER JOIN	12

---

9. Important Interview Concept – Duplicate Values

A JOIN does not simply return one row for each matching value.

When duplicate values exist, the matching rows are multiplied.

For example:

Table 1       Table 2

0             0
              0

Table 1 has one 0.

Table 2 has two 0s.

Therefore:

1 × 2 = 2 rows

If the data were:

Table 1       Table 2

0             0
0             0
              0

Then:

2 × 3 = 6 rows

This is known as the many-to-many matching effect.

---

10. Important Point About NULL

Another important concept is that:

NULL = NULL

does not evaluate to TRUE in SQL.

Therefore, the NULL value in Table 1 does not match the NULL values in Table 2 when using:

ON t1.id = t2.id

This is why the NULL values are treated as unmatched rows in the LEFT, RIGHT, and FULL OUTER JOIN examples.

---

Summary

The key numbers to remember from this example are:

INNER JOIN       → 2 records
LEFT JOIN        → 8 records
RIGHT JOIN       → 6 records
FULL OUTER JOIN  → 12 records

The two most important interview concepts are:

1. Duplicate matching values multiply the number of JOIN records.

2. NULL does not match NULL using the = operator.

For Blogspot, I’d recommend keeping the SQL inside <pre><code> blocks if you are using the HTML view, because that preserves indentation and makes the code much easier to read.

Absolutely. The important point here is that duplicate matching values multiply the number of rows in a join.

Let's use exactly your data.

1. Create the tables

CREATE TABLE table1 (
    id INT
);

CREATE TABLE table2 (
    id INT
);

2. Insert the data

Table 1

INSERT INTO table1 (id) VALUES
(1),
(1),
(2),
(2),
(3),
(0),
(NULL);

Table 2

INSERT INTO table2 (id) VALUES
(0),
(0),
(4),
(5),
(NULL),
(NULL);

So the data is:

Table 1	Table 2
1	0
1	0
2	4
2	5
3	NULL
0	NULL
NULL	NULL
7 rows	6 rows

---

3. INNER JOIN

SELECT t1.id, t2.id
FROM table1 t1
INNER JOIN table2 t2
    ON t1.id = t2.id;

Only matching values are returned.

The only matching value is 0.

Table 1 has:

0 → 1 occurrence

Table 2 has:

0 → 2 occurrences

Therefore:

1 × 2 = 2 rows

Result:

T1.id	T2.id
0	0
0	0

INNER JOIN = 2 records

NULL does not match NULL with =.

---

4. LEFT JOIN

SELECT t1.id, t2.id
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id;

A LEFT JOIN returns every row from Table 1, plus matching rows from Table 2.

Let's calculate:

Table 1 value	Count in T1	Matching T2 rows	Output
1	2	0	2
2	2	0	2
3	1	0	1
0	1	2	2
NULL	1	0	1

Total:

2 + 2 + 1 + 2 + 1 = 8

LEFT JOIN = 8 records

The result conceptually looks like:

T1.id	T2.id
1	NULL
1	NULL
2	NULL
2	NULL
3	NULL
0	0
0	0
NULL	NULL

Notice that Table 1's NULL still appears because LEFT JOIN preserves all T1 rows.

---

5. RIGHT JOIN

SELECT t1.id, t2.id
FROM table1 t1
RIGHT JOIN table2 t2
    ON t1.id = t2.id;

A RIGHT JOIN returns every row from Table 2, plus matching rows from Table 1.

Calculate:

Table 2 value	Count in T2	Matching T1 rows	Output
0	2	1	2
4	1	0	1
5	1	0	1
NULL	2	0	2

Total:

2 + 1 + 1 + 2 = 6

RIGHT JOIN = 6 records

Result:

T1.id	T2.id
0	0
0	0
NULL	4
NULL	5
NULL	NULL
NULL	NULL

---

6. FULL OUTER JOIN

SELECT t1.id, t2.id
FROM table1 t1
FULL OUTER JOIN table2 t2
    ON t1.id = t2.id;

FULL OUTER JOIN returns:

• All matching rows

• All unmatched rows from Table 1

• All unmatched rows from Table 2

Let's break it down.

Matching records

Only 0 matches:

T1 has 1 zero
T2 has 2 zeros

1 × 2 = 2

Unmatched Table 1 records

Table 1 has:

1
1
2
2
3
NULL

That's 6 unmatched rows.

Unmatched Table 2 records

Table 2 has:

4
5
NULL
NULL

That's 4 unmatched rows.

Therefore:

2 matching
+ 6 unmatched T1
+ 4 unmatched T2
----------------
= 12 rows

FULL OUTER JOIN = 12 records

---

Final Answer

Join Type	Number of Records
INNER JOIN	2
LEFT JOIN	8
RIGHT JOIN	6
FULL OUTER JOIN	12

The key interview concept

The biggest thing to remember is:

JOIN doesn't simply return the number of matching values. Duplicate values can multiply the rows.

For example:

Table 1          Table 2

0                0
                 0

The single 0 in Table 1 matches both zeros in Table 2:

1 × 2 = 2 rows

If you had:

Table 1: 0, 0
Table 2: 0, 0, 0

then the join would produce:

2 × 3 = 6 rows

One more critical point about NULL

This condition:

ON t1.id = t2.id

does not consider:

NULL = NULL

as TRUE.

Therefore, the two NULLs in Table 2 do not match the NULL in Table 1.

This is a very common SQL interview question.

Absolutely. The important point here is that duplicate matching values multiply the number of rows in a join.

Let's use exactly your data.

1. Create the tables

CREATE TABLE table1 (
    id INT
);

CREATE TABLE table2 (
    id INT
);

2. Insert the data

Table 1

INSERT INTO table1 (id) VALUES
(1),
(1),
(2),
(2),
(3),
(0),
(NULL);

Table 2

INSERT INTO table2 (id) VALUES
(0),
(0),
(4),
(5),
(NULL),
(NULL);

So the data is:

Table 1	Table 2
1	0
1	0
2	4
2	5
3	NULL
0	NULL
NULL	NULL
7 rows	6 rows

---

3. INNER JOIN

SELECT t1.id, t2.id
FROM table1 t1
INNER JOIN table2 t2
    ON t1.id = t2.id;

Only matching values are returned.

The only matching value is 0.

Table 1 has:

0 → 1 occurrence

Table 2 has:

0 → 2 occurrences

Therefore:

1 × 2 = 2 rows

 

 

 

 

Result:

T1.id	T2.id
0	0
0	0

INNER JOIN = 2 records

NULL does not match NULL with =.

---

4. LEFT JOIN

SELECT t1.id, t2.id
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.id = t2.id;

A LEFT JOIN returns every row from Table 1, plus matching rows from Table 2.

Let's calculate:

Table 1 value	Count in T1	Matching T2 rows	Output
1	2	0	2
2	2	0	2
3	1	0	1
0	1	2	2
NULL	1	0	1

 

 

 

Total:

2 + 2 + 1 + 2 + 1 = 8

LEFT JOIN = 8 records

The result conceptually looks like:

T1.id	T2.id
1	NULL
1	NULL
2	NULL
2	NULL
3	NULL
0	0
0	0
NULL	NULL

Notice that Table 1's NULL still appears because LEFT JOIN preserves all T1 rows.

---

5. RIGHT JOIN

SELECT t1.id, t2.id
FROM table1 t1
RIGHT JOIN table2 t2
    ON t1.id = t2.id;

A RIGHT JOIN returns every row from Table 2, plus matching rows from Table 1.

Calculate:

Table 2 value	Count in T2	Matching T1 rows	Output
0	2	1	2
4	1	0	1
5	1	0	1
NULL	2	0	2

Total:

2 + 1 + 1 + 2 = 6

 

 

 

 

RIGHT JOIN = 6 records

Result:

T1.id	T2.id
0	0
0	0
NULL	4
NULL	5
NULL	NULL
NULL	NULL

---

6. FULL OUTER JOIN

SELECT t1.id, t2.id
FROM table1 t1
FULL OUTER JOIN table2 t2
    ON t1.id = t2.id;

FULL OUTER JOIN returns:

• All matching rows

• All unmatched rows from Table 1

• All unmatched rows from Table 2

Let's break it down.

Matching records

Only 0 matches:

T1 has 1 zero
T2 has 2 zeros

1 × 2 = 2

Unmatched Table 1 records

Table 1 has:

1
1
2
2
3
NULL

That's 6 unmatched rows.

Unmatched Table 2 records

Table 2 has:

4
5
NULL
NULL

That's 4 unmatched rows.

Therefore:

2 matching
+ 6 unmatched T1
+ 4 unmatched T2
----------------
= 12 rows

 

 

 

FULL OUTER JOIN = 12 records

---

Final Answer

Join Type	Number of Records
INNER JOIN	2
LEFT JOIN	8
RIGHT JOIN	6
FULL OUTER JOIN	12

The key interview concept

The biggest thing to remember is:

JOIN doesn't simply return the number of matching values. Duplicate values can multiply the rows.

For example:

Table 1          Table 2

0                0
                 0

The single 0 in Table 1 matches both zeros in Table 2:

1 × 2 = 2 rows

If you had:

Table 1: 0, 0
Table 2: 0, 0, 0

then the join would produce:

2 × 3 = 6 rows

One more critical point about NULL

This condition:

ON t1.id = t2.id

does not consider:

NULL = NULL

as TRUE.

Therefore, the two NULLs in Table 2 do not match the NULL in Table 1.

This is a very common SQL interview question.

LEAD() and LAG() are window functions

 

LEAD() and LAG() are window functions used to access values from the next row or previous row without using a self-join.

Sample Employees Table

EmployeeID	EmployeeName	Salary
1	Alice	3000
2	Bob	4000
3	Charlie	2500
4	David	5000
5	Emma	3500

---

1. LAG() Example

Get the previous employee's salary:

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LAG(Salary) OVER (
        ORDER BY EmployeeID
    ) AS PreviousSalary
FROM Employees;

Output

EmployeeID	EmployeeName	Salary	PreviousSalary
1	Alice	3000	NULL
2	Bob	4000	3000
3	Charlie	2500	4000
4	David	5000	2500
5	Emma	3500	5000

LAG() looks backward.

---

2. LEAD() Example

Get the next employee's salary:

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LEAD(Salary) OVER (
        ORDER BY EmployeeID
    ) AS NextSalary
FROM Employees;

Output

EmployeeID	EmployeeName	Salary	NextSalary
1	Alice	3000	4000
2	Bob	4000	2500
3	Charlie	2500	5000
4	David	5000	3500
5	Emma	3500	NULL

LEAD() looks forward.

---

3. Compare Salary with Previous Employee

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LAG(Salary) OVER (ORDER BY EmployeeID) AS PrevSalary,
    Salary - LAG(Salary) OVER (ORDER BY EmployeeID) AS Difference
FROM Employees;

Output

Employee	Salary	PrevSalary	Difference
Alice	3000	NULL	NULL
Bob	4000	3000	1000
Charlie	2500	4000	-1500
David	5000	2500	2500
Emma	3500	5000	-1500

Useful for salary growth/decline analysis.

---

4. Using Offset

Previous 2nd Employee Salary

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LAG(Salary, 2) OVER (
        ORDER BY EmployeeID
    ) AS Salary2RowsBack
FROM Employees;

Output:

Employee	Salary	Salary2RowsBack
Alice	3000	NULL
Bob	4000	NULL
Charlie	2500	3000
David	5000	4000
Emma	3500	2500

---

5. Default Value Example

Instead of NULL:

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    LAG(Salary, 1, 0) OVER (
        ORDER BY EmployeeID
    ) AS PreviousSalary
FROM Employees;

Output:

Employee	Salary	PreviousSalary
Alice	3000	0
Bob	4000	3000
Charlie	2500	4000
David	5000	2500
Emma	3500	5000

---

Syntax

LAG

LAG(column_name, offset, default_value)
OVER (ORDER BY column_name)

LEAD

LEAD(column_name, offset, default_value)
OVER (ORDER BY column_name)

Interview Use Cases

• Compare current row with previous row.

• Find salary increase/decrease.

• Calculate month-over-month sales growth.

• Detect gaps in dates or sequences.

• Compare employee performance with previous/next employee.

• Find consecutive records (attendance, login history, transactions).

SUM() OVER() is a window function often used to calculate a running total without grouping rows.

 

SUM() OVER() is a window function often used to calculate a running total without grouping rows.

Sample Employees Table

EmployeeID	EmployeeName	Salary
1	Alice	3000
2	Bob	4000
3	Charlie	2500
4	David	5000
5	Emma	3500

Running Total of Salaries

SELECT
    EmployeeID,
    EmployeeName,
    Salary,
    SUM(Salary) OVER (
        ORDER BY EmployeeID
    ) AS RunningTotal
FROM Employees;

Output

EmployeeID	EmployeeName	Salary	RunningTotal
1	Alice	3000	3000
2	Bob	4000	7000
3	Charlie	2500	9500
4	David	5000	14500
5	Emma	3500	18000

How it works

For each row:

• Alice → 3000

• Alice + Bob → 7000

• Alice + Bob + Charlie → 9500

• Alice + Bob + Charlie + David → 14500

• Alice + Bob + Charlie + David + Emma → 18000

The window function is effectively doing:

SUM(Salary) OVER (
    ORDER BY EmployeeID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

which means:

• Start from the first row (UNBOUNDED PRECEDING)

• Keep adding values up to the current row (CURRENT ROW)

Running Total by Department

Suppose the table has a department column:

EmployeeID	EmployeeName	Department	Salary
1	Alice	HR	3000
2	Bob	HR	4000
3	Charlie	IT	2500
4	David	IT	5000
5	Emma	HR	3500

SELECT
    EmployeeID,
    EmployeeName,
    Department,
    Salary,
    SUM(Salary) OVER (
        PARTITION BY Department
        ORDER BY EmployeeID
    ) AS DeptRunningTotal
FROM Employees;

Output

EmployeeID	EmployeeName	Department	Salary	DeptRunningTotal
1	Alice	HR	3000	3000
2	Bob	HR	4000	7000
5	Emma	HR	3500	10500
3	Charlie	IT	2500	2500
4	David	IT	5000	7500

Here, PARTITION BY Department restarts the running total for each department.

Formula:

SUM(column_name) OVER (
    [PARTITION BY partition_column]
    ORDER BY order_column
)

This is one of the most common SQL interview questions involving window functions.

Decorator Function in python

A decorator is a function that takes another function and extends its behavior without explicitly modifying it. 
It's a powerful tool that allows you to add functionality to an existing code in a clean and readable way.


#Define a decorator function
def my_decorator(func):
    def wrapper():
        print("Something is happening before the function is called.")
        func()
        print("Something is happening after the function is called.")
    return wrapper

#Apply the decorator to a function
@my_decorator
def say_hello():
    print("Hello!")

#Call the decorated function
say_hello()


Explanation:

1. The my_decorator function takes another function (func) as an argument.
2. Inside my_decorator, a new function (wrapper) is defined. 
   This function calls the original function (func) and also does some additional work before and after calling func.
3. The wrapper function is returned by my_decorator.
4. The @my_decorator syntax before the say_hello function definition applies the my_decorator decorator to say_hello. 
   This means that when say_hello is called, it will actually call the wrapper function returned by my_decorator.
5. When say_hello() is called, it will print:


Explanation of Parameters

1. decorator_name(func):

decorator_name: This is the name of the decorator function.
func: This parameter represents the function being decorated. When you use a decorator, the decorated function is passed to this parameter.

2. wrapper(*args, **kwargs):

wrapper: This is a nested function inside the decorator. It wraps the original function, adding additional functionality.
*args: This collects any positional arguments passed to the decorated function into a tuple.
**kwargs: This collects any keyword arguments passed to the decorated function into a dictionary.
The wrapper function allows the decorator to handle functions with any number and types of arguments.

3. @decorator_name:

This syntax applies the decorator to the function_to_decorate function. It is equivalent to writing function_to_decorate = decorator_name(function_to_decorate).


Something is happening before the function is called.
Hello!
Something is happening after the function is called.



Without Decorator Function:
##########################
def say_hello():
    print("Hello!")

#Call the decorated function
say_hello()


Output: 
    
Hello!


Ex-2 : User Authentication:
    
    def requires_authentication(func):
    def wrapper(user, *args, **kwargs):
        if not user.get('is_authenticated', False):
            print("User is not authenticated. Access denied.")
            return None
        return func(user, *args, **kwargs)
    return wrapper


# Apply the decorator
@requires_authentication

def view_profile(user):
    print(f"Viewing profile of {user['username']}")

# Test the decorated function
user1 = {'username': 'Alice', 'is_authenticated': True}
user2 = {'username': 'Bob', 'is_authenticated': False}

view_profile(user1)
view_profile(user2)

#Output:
 Viewing profile of Alice
 User is not authenticated. Access denied.   
 
 
 
 #Without Decorator function 
 
 def view_profile(user):
    print(f"Viewing profile of {user['username']}")

# Test the decorated function
user1 = {'username': 'Alice', 'is_authenticated': True}
user2 = {'username': 'Bob', 'is_authenticated': False}

view_profile(user1)
view_profile(user2)

#output
Viewing profile of Alice
Viewing profile of Bob

Exploding nested JSON in Pyspark (Struct and Array)

Exploding nested JSON in Pyspark (Struct and Array) 

'''

{
  "company": {
    "name": "Tech Innovators Inc.",
    "founded": 2010,
    "isPublic": true,
    "departments": [
      {
        "name": "Engineering",
        "manager": {
          "name": "Alice Johnson",
          "email": "alice.johnson@techinnovators.com"
        },
        "employees": [
          {
            "name": "John Doe",
            "position": "Software Engineer",
            "skills": ["JavaScript", "Python", "AWS"]
          },
          {
            "name": "Emily Smith",
            "position": "DevOps Engineer",
            "skills": ["Docker", "Kubernetes", "Linux"]
          }
        ]
      },
      {
        "name": "Marketing",
        "manager": {
          "name": "Robert Brown",
          "email": "robert.brown@techinnovators.com"
        },
        "employees": [
          {
            "name": "Laura Wilson",
            "position": "Marketing Specialist",
            "skills": ["SEO", "Content Writing", "Analytics"]
          }
        ]
      }
    ],
    "contact": {
      "address": {
        "street": "123 Innovation Lane",
        "city": "Tech City",
        "state": "CA",
        "zip": "94000"
      },
      "phone": "+1-800-555-1234",
      "email": "info@techinnovators.com"
    }
  }
}



'''
Explanation:
Top-level object: The company key holds the main structure.

Nested objects:
departments is an array of objects, each representing a department.
Each department object has a manager (another object) and an employees array.

Arrays:
employees contains an array of objects, with each object describing an employee.

Each employee has an array of skills.

Contact information: contact contains another nested object for the address and other contact details.

This JSON structure can be easily parsed and used in programming languages like JavaScript,
 Python, or any other that supports JSON. 
 '''



'''
from pyspark.sql import SparkSession
from pyspark.sql.functions import *

# Initialize Spark session
spark = SparkSession.builder.appName("NestedJSON").getOrCreate()

# Load the JSON file
df = spark.read.format('json').option('multiline','true').load('E:\\source_files\\nested_json_explode_example.json')
#df.show(truncate=False)
df.printSchema()
'''
root
 |-- company: struct (nullable = true)
 |    |-- contact: struct (nullable = true)
 |    |    |-- address: struct (nullable = true)
 |    |    |    |-- city: string (nullable = true)
 |    |    |    |-- state: string (nullable = true)
 |    |    |    |-- street: string (nullable = true)
 |    |    |    |-- zip: string (nullable = true)
 |    |    |-- email: string (nullable = true)
 |    |    |-- phone: string (nullable = true)
 |    |-- departments: array (nullable = true)
 |    |    |-- element: struct (containsNull = true)
 |    |    |    |-- employees: array (nullable = true)
 |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |-- name: string (nullable = true)
 |    |    |    |    |    |-- position: string (nullable = true)
 |    |    |    |    |    |-- skills: array (nullable = true)
 |    |    |    |    |    |    |-- element: string (containsNull = true)
 |    |    |    |-- manager: struct (nullable = true)
 |    |    |    |    |-- email: string (nullable = true)
 |    |    |    |    |-- name: string (nullable = true)
 |    |    |    |-- name: string (nullable = true)
 |    |-- founded: long (nullable = true)
 |    |-- isPublic: boolean (nullable = true)
 |    |-- name: string (nullable = true)
'''
# Flatten the nested fields
flattened_df = df.select(col("company.name").alias("company_name"),
                         col("company.founded").alias("company_founded"),
                         col("company.isPublic").alias("company_isPublic"),
                         col("company.contact.address.street").alias("contact_street"),
                         col("company.contact.address.city").alias("contact_city"),
                         col("company.contact.address.state").alias("contact_state"),
                         col("company.contact.address.zip").alias("contact_zip"),
                         col("company.contact.phone").alias("contact_phone"),
                         col("company.contact.email").alias("contact_email"),

                         explode("company.departments").alias("department")) \
    .select("company_name", "company_founded", "company_isPublic", "contact_street", "contact_city",
            "contact_state", "contact_zip", "contact_phone", "contact_email",

            col("department.name").alias("department_name"), col("department.manager.name").alias("manager_name"),
            col("department.manager.email").alias("manager_email"),

            explode("department.employees").alias("employee")) \
    .select("company_name", "company_founded", "company_isPublic", "contact_street", "contact_city",
            "contact_state", "contact_zip", "contact_phone", "contact_email", "department_name",
            "manager_name", "manager_email",
            col("employee.name").alias("employee_name"),
            col("employee.position").alias("employee_position"),
            explode(col("employee.skills").alias("employee_skills"))
            )

# Show the flattened DataFrame
flattened_df.show(truncate=False)

###############################################

from pyspark.sql import SparkSession
from pyspark.sql.functions import *

# Initialize Spark session
spark = SparkSession.builder.appName("NestedJSON").getOrCreate()

# Load the JSON file
df = spark.read.format('json').option('Multiline','True').load('E:\\source_files\\nested_json_explode_example.json')
#df.show(truncate=False)
df.printSchema()

# Select and display top-level fields
df.select("company.name", "company.founded", "company.isPublic").show()

#Explode the nested departments array
departments_df = df.select(explode("company.departments").alias("department"))

departments_df.printSchema()

# Explode the nested employees array within departments
expanded_df = departments_df.select(
    col("department.name").alias("department_name"),
    col("department.manager.name").alias("manager_name"),
    col("department.manager.email").alias("manager_email"),
    explode(col("department.employees")).alias("employee")
)

# Further expand employee details
final_df = expanded_df.select(
    col("department_name"),
    col("manager_name"),
    col("manager_email"),
    col("employee.name").alias("employee_name"),
    col("employee.position").alias("employee_position"),
    explode(col("employee.skills")).alias("employee_skill")
)

# Show the final exploded DataFrame
final_df.show(truncate=False)

# Select and display contact information
contact_df = df.select("company.contact.*")
contact_df.select(
"address.street", "address.city", "address.state", "address.zip", "phone", "email" ).show()
# Stop Spark session
spark.stop()

lambda function and its limitations in AWS

 

In AWS, Lambda functions are serverless compute services that allow you to run code in response to events or HTTP requests without provisioning or managing servers. While AWS Lambda is a powerful tool, it has certain limitations you should be aware of when designing applications.

---

Key Features of AWS Lambda:

• Supports multiple runtimes (e.g., Python, Node.js, Java, etc.).
• Scales automatically based on traffic.
• Pay-as-you-go model, billed for execution time and requests.

---

Common Limitations of AWS Lambda:

1. Execution Timeout

• Limit: Maximum execution time for a Lambda function is 15 minutes.
• Impact: Long-running tasks such as batch processing, video encoding, or large database operations may fail.
• Solution: Use AWS Step Functions for workflows or break tasks into smaller chunks.

---

2. Memory and CPU

• Limit: Memory allocation ranges from 128 MB to 10,240 MB.
  • CPU is proportional to memory, with no option to configure CPU directly.
• Impact: Computationally intensive tasks may require higher memory settings.
• Solution: Optimize code, offload heavy processing to ECS/EKS, or use purpose-built services like AWS SageMaker for ML tasks.

---

3. Ephemeral Storage

• Limit: Each Lambda function gets 512 MB of temporary storage in the /tmp directory.
• Impact: Insufficient for storing large files during execution.
• Solution: Use S3 for temporary storage or increase storage with ephemeral storage (up to 10 GB as of 2023).

---

4. Deployment Package Size

• Limit:
  • 50 MB for direct upload as a ZIP file.
  • 250 MB unzipped (including layers).
• Impact: Large libraries or dependencies may exceed this limit.
• Solution: Use Lambda Layers to share dependencies or container images (up to 10 GB).

---

5. Concurrent Execution

• Limit: Default concurrency limit is 1,000 simultaneous executions per region, adjustable by AWS support.
• Impact: Exceeding this limit leads to throttling, which may affect user experience.
• Solution: Request a limit increase or use reserved concurrency to allocate resources to critical functions.

---

6. Cold Starts

• Limit: When a function is invoked after being idle, a cold start occurs, adding latency.
• Impact: Affects real-time or low-latency applications.
• Solution: Use provisioned concurrency or optimize function initialization.

---

7. VPC Networking

• Limit: Lambda functions inside a VPC may experience additional latency when establishing ENI (Elastic Network Interface) connections.
• Impact: Slower execution when accessing VPC resources like RDS or Elasticsearch.
• Solution: Use AWS PrivateLink, reduce VPC subnets, or optimize ENI setup.

---

8. Supported Runtimes

• Limit: Only supports specific runtimes (e.g., Python, Node.js, Java, Go).
• Impact: Custom runtimes need to be built using AWS Lambda Runtime API.
• Solution: Use custom runtimes or container images for unsupported languages.

---

9. Statefulness

• Limit: AWS Lambda is stateless, meaning the function does not retain state between invocations.
• Impact: Complex applications requiring persistent state need additional storage.
• Solution: Use DynamoDB, S3, or external databases for state management.

---

10. Execution Environment

• Limit: Functions run in a sandboxed environment with restrictions on OS access, thread counts, and system libraries.
• Impact: Limited control over the underlying environment.
• Solution: Use container-based Lambdas for more control over the runtime.

---

11. IAM Permissions

• Limit: Misconfigured IAM roles or excessive permissions can lead to security issues.
• Impact: Potential data leaks or unauthorized access.
• Solution: Follow the principle of least privilege for IAM roles.

---

12. Cost

• Limit: While Lambda is cost-effective for infrequent tasks, high-frequency or long-running tasks can become expensive.
• Impact: Unexpected costs for poorly optimized or high-throughput applications.
• Solution: Monitor costs using AWS Cost Explorer or switch to alternative compute services (e.g., ECS, Fargate).

---

Conclusion

AWS Lambda is a versatile and efficient solution for event-driven and serverless architectures, but its limitations require careful design and planning. Understanding and working around these constraints ensures optimal performance and cost-efficiency. For complex applications, consider hybrid approaches using other AWS services.

Map vs flatmap in Pyspark

 

The difference between flatMap and map in PySpark lies in the output structure they produce after applying a transformation function to each element of an RDD. Both are transformations, but they behave differently based on the results of the applied function.

---

Key Differences Between map and flatMap

Feature	map	flatMap
Output	Transforms each input element into exactly one output element.	Transforms each input element into zero, one, or multiple output elements.
Flattening	Does not flatten the output; results remain nested if the function returns a list or collection.	Flattens the output; all elements from lists or collections are returned as a single, flattened sequence.
Use Case	Use when the function produces one-to-one mapping or transformation.	Use when the function may produce multiple outputs or a collection for each input.

---

Examples

1. map Example

Each element of the RDD is transformed into exactly one element in the result.

from pyspark import SparkContext

sc = SparkContext("local", "Map vs FlatMap")

# Input RDD
rdd = sc.parallelize([1, 2, 3])

# Apply map to double each number
mapped_rdd = rdd.map(lambda x: [x, x * 2])

print(mapped_rdd.collect())  
# Output: [[1, 2], [2, 4], [3, 6]]

---

2. flatMap Example

Each element can be transformed into multiple outputs, and the result is flattened.

# Apply flatMap to produce multiple outputs for each element
flat_mapped_rdd = rdd.flatMap(lambda x: [x, x * 2])

print(flat_mapped_rdd.collect())  
# Output: [1, 2, 2, 4, 3, 6]

---

Key Points in Behavior

1. Nested Output with map:

   • The map transformation retains the structure of the function's output, even if it is a list or collection.
   • Example: A single list [1, 2] remains as [1, 2] inside the RDD.

2. Flattened Output with flatMap:

   • The flatMap transformation flattens the output of the function.
   • Example: A list [1, 2] is split into separate elements 1 and 2 in the final RDD.

---

When to Use Which?

• Use map:

  • When you want a one-to-one transformation (e.g., applying a function to each element).
  • When the transformation doesn't produce lists or collections as output.

• Use flatMap:

  • When you need a one-to-many transformation or need to flatten the output.
  • When the function produces lists, collections, or even empty outputs for some elements.

---

Advanced Example

Splitting Sentences into Words (flatMap vs. map)

# Input RDD of sentences
rdd = sc.parallelize(["Hello world", "PySpark map and flatMap"])

# Using map
mapped_rdd = rdd.map(lambda sentence: sentence.split(" "))
print(mapped_rdd.collect())
# Output: [['Hello', 'world'], ['PySpark', 'map', 'and', 'flatMap']]

# Using flatMap
flat_mapped_rdd = rdd.flatMap(lambda sentence: sentence.split(" "))
print(flat_mapped_rdd.collect())
# Output: ['Hello', 'world', 'PySpark', 'map', 'and', 'flatMap']

---

Summary

• Use map for transformations where the output is exactly one element per input.
• Use flatMap for transformations where the output may be multiple elements per input or where the result needs to be flattened into a single list.