Let’s look at a standard teams interview question to understand your SQL knowledge in using a non-equi join. Problem Statement 1 Given the below 5 teams
|
1 2 3 4 5 6 7 8 9 |
team_id team_name ------------------------ 1 Team A 2 Team B 3 Team C 4 Team D 5 Team E |
1) Write a query so that each team plays with each other only once. 2) Write a query so that each team plays...
This is based on the series of tutorials & interview Q&As at PySpark interview Q&As with tutorials
Problem: Convert the below table
|
1 2 3 4 5 6 7 8 9 10 |
+------+--------+---------+-------------+ |emp_id|emp_name| emp_city|emp_expertise| +------+--------+---------+-------------+ | 1| John| Sydney| Java| | 2| Peter|Melbourne| Scala| | 3| Sam| Brisbane| Python| | 4| David|Melbourne| Python| | 5| Elliot| Sydney| Java| +------+--------+---------+-------------+ |
to
|
1 2 3 4 5 6 7 8 9 |
+-------------+----------------------------------------------------------+ |Column |Content | +-------------+----------------------------------------------------------+ |emp_id |[[1, 1], [3, 1], [5, 1], [4, 1], [2, 1]] | |emp_name |[[Elliot, 1], [John, 1], [Sam, 1], [Peter, 1], [David, 1]]| |emp_city |[[Sydney, 2], [Melbourne, 2], [Brisbane, 1]] | |emp_expertise|[[Python, 2], [Java, 2], [Scala, 1]] | +-------------+----------------------------------------------------------+ |
Where each column is counted for its occurrence. For example,
…
Q01. How will you create a Spark context? A01.
|
1 2 3 4 5 6 7 8 9 |
from pyspark.sql import SparkSession spark = SparkSession.builder.appName("my spark job") spark.master('local[*]') spark.config('spark.jars.packages', 'com.amazonaws:aws-java-sdk:1.11.297,org.apache.hadoop:hadoop-aws:2.8.3,mysql:mysql-connector-java:5.1.46') .config('spark.hadoop.mapreduce.fileoutputcommitter.algorithm.version', 2) .config('spark.speculation', 'false') |
Q02. How will you create a Dataframe by reading a file from AWS S3 bucket? A02.
|
1 2 3 4 5 6 |
csvFileAsDataframe = spark.read.format("com.databricks.spark.csv") \ .option("header", "false") \ .option("inferSchema", "true") \ .load(s3://my-bucket/some-path/input-file.csv) \ |
Q03. How will you create a Dataframe by reading a table in a database? … Read more ›...
Q01. What is Amazon S3 storage? A01. S3 stands for Simple Storage Service, which provides storage through web service interfaces. Unlike the other storage systems like Unix file system, HDFS (i.e. Hadoop Distributed File System), etc which are based on having folders and files, the S3 is based on a...
Here is another SQL scenario based interview question to determine a candidate’s ability to translate a business requirement into an SQL query. Problem Statement Given the below data of running account balance, find the start_date and end_date where the balance changes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
id balance date ------------------------------------- 1 24000.00 2023-01-15 2 24000.00 2023-01-16 3 19000.00 2023-01-17 4 19000.00 2023-01-18 5 22000.00 2023-01-19 6 24000.00 2023-01-20 7 24000.00 2023-01-21 8 29000.00 2023-01-22 9 29000.00 2023-01-23 10 29000.00 2023-01-24 |
Expected output:
|
1 2 3 4 5 6 7 8 9 |
balance start_date end_date ---------------------------------------------- 24000.00 2023-01-15 2023-01-16 19000.00 2023-01-17 2023-01-18 22000.00 2023-01-19 2023-01-19 24000.00 2023-01-20 2023-01-21 29000.00 2023-01-22 2023-01-24 |
Create sample data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE IF NOT EXISTS account_balance (id INT, balance DECIMAL(10,2), date DATE ); INSERT INTO account_balance(id, balance, date) VALUES (1, 24000.00, '2023-01-15'); INSERT INTO account_balance(id, balance, date) VALUES (2, 24000.00, '2023-01-16'); INSERT INTO account_balance(id, balance, date) VALUES (3, 19000.00, '2023-01-17'); INSERT INTO account_balance(id, balance, date) VALUES (4, 19000.00, '2023-01-18'); INSERT INTO account_balance(id, balance, date) VALUES (5, 22000.00, '2023-01-19'); INSERT INTO account_balance(id, balance, date) VALUES (6, 24000.00, '2023-01-20'); INSERT INTO account_balance(id, balance, date) VALUES (7, 24000.00, '2023-01-21'); INSERT INTO account_balance(id, balance, date) VALUES (8, 29000.00, '2023-01-22'); INSERT INTO account_balance(id, balance, date) VALUES (9, 29000.00, '2023-01-23'); INSERT INTO account_balance(id, balance, date) VALUES (10, 29000.00, '2023-01-24'); |
Solution...
Problem: Convert the below table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+--------+------------+---+ |emp_name| product|qty| +--------+------------+---+ | John| Notebook| 7| | Peter|Mobile phone| 12| | Sam| Camera| 3| | David|Mobile phone| 9| | Elliot| Notebook| 3| | Elliot| Camera| 1| | John|Mobile phone| 13| | Peter|Mobile phone| 2| +--------+------------+---+ |
to
|
1 2 3 4 5 6 7 8 9 10 |
+--------+------+------------+--------+ |emp_name|Camera|Mobile phone|Notebook| +--------+------+------------+--------+ | Elliot| 1| null| 3| | John| null| 13| 7| | Sam| 3| null| null| | Peter| null| 14| null| | David| null| 9| null| +--------+------+------------+--------+ |
Step 1: Let’s create the sample data using PySpark. problem.py:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
from pyspark.sql import SparkSession from pyspark.sql.types import * from pyspark.sql import functions as F from pyspark.sql import DataFrame from functools import reduce def solve(spark): sales = ( ('John', 'Notebook', 7), ('Peter', 'Mobile phone', 12), ('Sam', 'Camera', 3), ('David', 'Mobile phone', 9), ('Elliot', 'Notebook', 3), ('Elliot', 'Camera', 1), ('John', 'Mobile phone', 13), ('Peter', 'Mobile phone', 2) ) data_schema = [StructField('emp_name', StringType(), False), StructField('product',StringType(),False), StructField('qty', IntegerType(), False)] final_struc = StructType(fields=data_schema) rdd = spark.sparkContext.parallelize(sales); df_sales = spark.createDataFrame(rdd, final_struc) df_sales.show() if __name__ == '__main__': spark = SparkSession.builder.appName("Spark problem solving")\ .master("local[*]")\ .getOrCreate() solve(spark) |
Run as:
|
1 2 3 |
$ spark-submit problem.py |
Outputs:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
+--------+------------+---+ |emp_name| product|qty| +--------+------------+---+ | John| Notebook| 7| | Peter|Mobile phone| 12| | Sam| Camera| 3| | David|Mobile phone| 9| | Elliot| Notebook| 3| | Elliot| Camera| 1| | John|Mobile phone| 13| | Peter|Mobile phone| 2| +--------+------------+---+ |
pivot( ) to the rescue Step 2: The code to create the results. … Read more ›...
More and more organisations are adopting the policy of “Cloud first architecture” where Cloud based storages like AWS S3 plays a major role. A data lake is a storage repository that holds a vast amount of structured, semi-structured, and unstructured raw data in its native format (aka pristine condition). …...
Here is another SQL scenario based interview question to determine a candidate’s ability to translate a business requirement into an SQL query. These are scenarios where you want to find a value at a certain point in time. Lean more about SCD Type 2 at 13 Data Warehouse interview Q&As...
Given the below data, where the record with the emp_name “Elliot” is repeated.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
+--------+------------+---+ |emp_name| product|qty| +--------+------------+---+ | John| Notebook| 3| | Peter|Mobile phone| 12| | Sam| Camera| 3| | David|Mobile phone| 9| | Elliot| Notebook| 3| | Elliot| Notebook| 3| | John|Mobile phone| 13| | Peter|Mobile phone| 2| +--------+------------+---+ |
distinct(..) Q: How will you eliminate duplicate rows? A: Use the distinct(..) function. problem.py:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
from pyspark.sql import SparkSession from pyspark.sql.types import * from pyspark.sql import functions as F from pyspark.sql import DataFrame from functools import reduce def solve(spark): sales = ( ('John', 'Notebook', 3), ('Peter', 'Mobile phone', 12), ('Sam', 'Camera', 3), ('David', 'Mobile phone', 9), ('Elliot', 'Notebook', 3), ('Elliot', 'Notebook', 3), ('John', 'Mobile phone', 13), ('Peter', 'Mobile phone', 2) ) data_schema = [StructField('emp_name', StringType(), False), StructField('product',StringType(),False), StructField('qty', IntegerType(), False)] final_struc = StructType(fields=data_schema) rdd = spark.sparkContext.parallelize(sales); df_sales = spark.createDataFrame(rdd, final_struc) df_sales.distinct()\ .show() if __name__ == '__main__': spark = SparkSession.builder.appName("Spark problem solving")\ .master("local[*]")\ .getOrCreate() solve(spark) |
Run as:
|
1 2 3 |
$ spark-submit problem.py |
Outputs:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+--------+------------+---+ |emp_name| product|qty| +--------+------------+---+ | John| Notebook| 3| | Peter|Mobile phone| 12| | Sam| Camera| 3| | David|Mobile phone| 9| | Elliot| Notebook| 3| | John|Mobile phone| 13| | Peter|Mobile phone| 2| +--------+------------+---+ |
dropDuplicates(..) Q: What if you want to remove records based on duplicate values in selected columns?...
Q125. What is Apache Zookeeper & where can you use it? A125. Distributed applications like HBase, Solr, Kafka, etc run services on multiple nodes (i.e. computers) for scalability (i.e more nodes can be added) & redundancy (i.e. several nodes provide the same service & if one node is down another...
Let’s look at a very common problem statement of finding out the repeat customers, repeat visits, etc. The key to this problem is first determining the first visit or first purchase and then comparing it with the remaining records. Problem Statement 1 Given the below table, how will you determine...
This extends NumPy Vs Pandas interview questions & answers – part 1 Q: What is the difference between PySpark Dataframe & a Pandas Dataframe? A: PySpark is a library where the operations are quicker than Pandas Dataframe library because of its parallel execution over multiple CPU cores & … Read...
Covers Solr interview Q&As in general, and how Solr can be used with Big Data in HDFS to index & query the data. You can get sub-seconds query performance when query the data on HDFS that are indexed using Solr. Q131. How does Solr accomplish fast search performance? … Read...