Bharat Purohit

#azure #SQL #pyspark #databricks #bigdata #dataengineer #dataanalsyt Hi Connections, Let's solve another SQL interview question. Task: Find the words which are repeating more than 2 times.

17

🚀 Happy to share another achievement from Databricks: "#Advanced_Data_Engineering_with_Databricks Certification" 🚀 Key highlights of this certification: #Incremental_Data_Processing: Emphasizes designs favoring incremental data processing, enabling systems to continuously ingest and analyze growing datasets. #Platform_Optimizations: Leverages built-in optimizations to reduce the burden of code maintenance and on-call emergencies. #Adaptability: Quickly adapt production code to new demands with minimal refactoring or downtime. #NeverStopLearning #Databricks #DataEngineering #ApacheSpark #StructuredStreaming #DeltaLake #DataLakehouse #ContinuousLearning #DATA #AZURE

17

8 Comments

Day 12 of the 15 days #HTTDataChallenge Day 12 challlenge is to write an SQL query to calculate total sales and average order value by region. This involves the use of aggregate functions. Aggregate functions in SQL performs calculations on multiple values and return a single value. There are five aggregate functions in SQL which are SUM, AVG, COUNT, MIN, MAX and COUNT. Below is the snapshot of my SQL query and result gotten HerTechTrail Wofai Eyong Imaobong N. #HTTDataChallenge

4

#azure #pyspark #python #sql #bigdata #dataengineer #dataanalyst Hi Connections, Let's solve another Pyspark interview question. Task: Pivot the sales data for different countries. data = [("Banana",1000,"USA"), ("Carrots",1500,"USA"), ("Beans",1600,"USA"), \       ("Orange",2000,"USA"),("Orange",2000,"USA"),("Banana",400,"China"), \       ("Carrots",1200,"China"),("Beans",1500,"China"),("Orange",4000,"China"), \       ("Banana",2000,"Canada"),("Carrots",2000,"Canada"),("Beans",2000,"Mexico")] columns= ["Product","Amount","Country"] df = spark.createDataFrame(data = data, schema = columns) display(df)

19

Here's a quick quiz on #spark . Which of the followings are TRUE? a. A single task processes one partition. b. A partition is split among several executors. c. A task is executed by one executor. Share your answers in comments. #dataengineers #Apachespark Suraz G., Ashutosh Kumar , Daniel Ciocîrlan

17

6 Comments

🔖SQL Interview Question for Data Analyst Role at American Express 🖇️Problem: You are given the following two tables:    Transactions Table:        transaction_id (INT): Unique ID for each transaction.        customer_id (INT): The ID of the customer who made the transaction.        transaction_date (DATE): The date of the transaction.        amount (DECIMAL): The transaction amount.    Customers Table:        customer_id (INT): Unique ID for each customer.        customer_name (VARCHAR): Name of the customer. Task: Write an SQL query to find the top 3 customers who have the highest total transaction amounts in the last 6 months from today's date. Solution: WITH Recent_Transactions AS (    SELECT        t.customer_id,        SUM(t.amount) AS total_amount    FROM Transactions t    WHERE t.transaction_date >= DATEADD(MONTH, -6, CURRENT_DATE)    GROUP BY t.customer_id ), Ranked_Customers AS (    SELECT        rt.customer_id,        rt.total_amount,        ROW_NUMBER() OVER (ORDER BY rt.total_amount DESC) AS rank    FROM Recent_Transactions rt ) SELECT    c.customer_name,    rc.total_amount FROM Ranked_Customers rc JOIN Customers c    ON rc.customer_id = c.customer_id WHERE rc.rank <= 3 ORDER BY rc.total_amount DESC; Explanation:    ⏩ We use the Recent_Transactions CTE to filter transactions within the last 6 months and calculate the total transaction amount for each customer.    ⏩ In Ranked_Customers, we rank customers by their total transaction amounts using ROW_NUMBER().    ⏩ Finally, we retrieve the top 3 customers by joining with the Customers table and filtering for rank <= 3. This type of question tests both the ability to perform complex aggregations and an understanding of window functions. Do you have an alternate solution for this? Please mention your solution in the comment section 🙂 #Dataanalyst #SQL #Datascience #Bigdata #DataCleaning #tableau #Storytelling #DataQuality #DataAnalysis #Dashboard #DataProcessing #DataIntegrity #DatabaseManagement #SQLQueries #DataTransformation #DataMining #DataEngineer

7

Pyspark Code Practice Questions Here is the document for Easy to Hard levels of questions for your practice with solutions. Document Credits - Respective Owners # Karthik K. # Seekho Bigdata Institute

116

2 Comments

Day5: Most asked Pyspark questions for the Data Engineering interviews Concepts and Features 1. What is Spark and why is it preferred over MapReduce? 2. Explain the difference between RDDs, DataFrames and Datasets. 3. What is broadcasting and how is it useful in PySpark? 4. Explain the concept of broadcast variables in Spark. 5. What is the significance of caching in Spark? 6. What is the role of Spark SQL in data processing? 7. How does Spark handle fault tolerance? 8. Discuss the significance of partitioning in Spark. 9. What is DAG & how does it help? 10. What is shuffling? Why should we minimize it? Practical Usage and Implementation 1. How do you create a DataFrame in PySpark? 2. Explain the concept of window functions in PySpark. 3. How do you handle schema evolution in PySpark? 4. How do you test your Spark code? 5. Which version control do you use? 6. How does Spark handle memory management? 7. How does Spark handle fault tolerance? 8. What is the role of Spark SQL in data processing? 9. What is the significance of caching in Spark? 10. Discuss the significance of partitioning? #jobinterviews #dataEngineer #pyspark #future #Spark #ApacheSpark #DataScience #MachineLearning #DataAnalysis #InterviewPreparation #CodingInterviews #DataPipeline #ETL #CloudComputing #DataAnalytics #TechInterviews #DataDriven #DataManagement #DataTransformation #BusinessIntelligence #SQL #Analytics #DataVisualization #TechCareers #DataEngineeringLife #DataOps #DataStrategy #Hadoop #SparkSQL #DataScienceCommunity #Programming

26

3 Comments

Common spark interview questions that might help for cracking interviews as well as for better learning!! Another rarely asked question is UDF in pyspark: PySpark UDF is a User Defined Function that is used to create a reusable function in Spark. CREATION OF UDF IN PYSPARK: i. Creating UDF ,first we write python function : def upperCase(str): return str.upper() ii. Then Converting function to UDF using below code: upperCaseUDF = udf(lambda z: upperCase(z)) iii. Then directly using it with dataframes as below: df.withColumn("Cureated Name", upperCaseUDF(col("Name"))) \ .show(truncate=False) CREATION OF UDF USING SPARK SQL: i. In order to use above defined upperCase() function on PySpark SQL, you need to register the function with PySpark by using spark.udf.register(). spark.udf.register("upperCaseUDF", upperCase,StringType()) df.createOrReplaceTempView("NAME_TABLE") spark.sql("select Seqno, upperCaseUDF(Name) as Name from NAME_TABLE").show(truncate=False) CREATION OF UDF USING ANNOTATIONS: i. Create it with just a single step by using annotations. @udf(returnType=StringType()) def upperCase(str): return str.upper() df.withColumn("Cureated Name", upperCase(col("Name"))) \ .show(truncate=False) #pyspark #interviewpreparation

73

4 Comments

#DATAENGINEERING_WITH_SNOWFLAKE HI all, #connections Here is the doc on first part of SNOWPIPE #snowflake #snowpipe #dataloading‍‍ #‍‍‍‍‍‍‍dataengineering #datascience #snowflake_25 c‍‍‍‍‍overed the following: 1. about snowpipe 2. data loading using snowpipe Thanks, #all

31

#azure #pyspark #databricks #bigdata #dataengineer #dataanalyst Hi Connections, Let's solve another Pyspark interview question. Task: Get top 3 pick up locations. from pyspark.sql.types import StructType, StructField, StringType, IntegerType schema = StructType([     StructField("reqid", IntegerType(), True),     StructField("pickup_location", StringType(), True) ]) data = [(48, "Airport"), (49, "Office"),(50, "Hospital"),(51, "Airport"),(52, "Hospital"),(53, "Shoppingmall"),(54, "Office"),(55, "Hospital"),(56, "Hospital")] pickup_df = spark.createDataFrame(data, schema) pickup_df.display()

11

#azuredataengineer #azure #dataengineer #dataanalyst Hi Connections, Sharing one more interview question: How does Union() work in Pyspark? In SQL: The UNION operator removes duplicate rows from the result set. But Union Operator does not remove duplicate rows in PySpark. It works as UnionAll() works in SQL. Example: simpleData = [("James","Sales","NY",90000,34,10000), \     ("Michael","Sales","NY",86000,56,20000), \     ("Robert","Sales","CA",81000,30,23000), \     ("Maria","Finance","CA",90000,24,23000) \   ] columns= ["employee_name","department","state","salary","age","bonus"] df = spark.createDataFrame(data = simpleData, schema = columns) simpleData2 = [("James","Sales","NY",90000,34,10000), \     ("Maria","Finance","CA",90000,24,23000), \     ("Jen","Finance","NY",79000,53,15000), \     ("Jeff","Marketing","CA",80000,25,18000), \     ("Kumar","Marketing","NY",91000,50,21000) \   ] columns2= ["employee_name","department","state","salary","age","bonus"] df2 = spark.createDataFrame(data = simpleData2, schema = columns2)

9

.This video provided a detailed understanding of core concepts in Spark, including DataFrames, Datasets, and RDDs, along with insights into job execution and optimization techniques. It breaks down the differences and use cases for these components, making it easier to grasp their practical applications in big data processing. A must-watch for anyone looking to strengthen their Spark fundamentals!

1

Hey Guys, today we will explore about ,how Efficiently Truncate Columns in Snowflake. Truncating data in specific columns without affecting the entire table can be a crucial part of maintaining data hygiene. Here’s how you can efficiently truncate (i.e., clear) specific columns in a Snowflake table: 1.Update Columns to Null or Default Value: Use the UPDATE statement to set the column values to NULL or a default value. This approach maintains the table structure and integrity. UPDATE my_table SET column1 = NULL,   column2 = NULL WHERE TRUE; 2.Alternative Method Using Default Values: If you have default values for the columns, you can reset them to these defaults. UPDATE my_table SET column1 = DEFAULT,   column2 = DEFAULT WHERE TRUE; Why Use This Method? Maintains Data Integrity: Only the specified columns are cleared, preserving other data in the table. Flexibility: You can choose to set columns to NULL or any default value, based on your data management needs. #Snowflake #DataEngineering #DataManagement #SQL #DataHygiene

3

#azure #dataanalyst #azuredataengineer Hi Connections, Sharing how we can apply join using PySpark: The example shown is of 'Inner Join' emp = [(1,"Smith",-1,"2018","10","M",3000), \     (2,"Rose",1,"2010","20","M",4000), \     (3,"Williams",1,"2010","10","M",1000), \     (4,"Jones",2,"2005","10","F",2000), \     (5,"Brown",2,"2010","40","",-1), \       (6,"Brown",2,"2010","50","",-1) \   ] empColumns = ["emp_id","name","superior_emp_id","year_joined", \        "emp_dept_id","gender","salary"] empDF = spark.createDataFrame(data=emp, schema = empColumns) dept = [("Finance",10), \     ("Marketing",20), \     ("Sales",30), \     ("IT",40) \   ] deptColumns = ["dept_name","dept_id"] deptDF = spark.createDataFrame(data=dept, schema = deptColumns)

15

2 Comments

#azure #sql #bigdata #dataengineer #dataanalyst Hi Connections, Let's solve another SQL interview question. Task: Transform rows to columns.

22

👉 Pyspark Interview Questions Day15 🤞 How to fill down the values in Pyspark Tagging for more reach Ajay Kadiyala Ansh Lamba Arundhati Sen Shivakiran Kotur Lipsa Biswas Ankit Bansal #pysparkinterview #databricks #azuredataengineer #pyspark #azuredataengineer #data #dataengineer #azure #databricks #hadoop #sql #pyspark #python #happylearning #InterviewTips #CareerAdvice #DataEngineering #TechCommunity #azure #databricks #dataengineer #data #interview

12

Important Interview Question and Answers for a quick preparation. Follow Shwetank Singh for upskilling yourself.

1