Apache Spark

Spark SQL Count Distinct Window Function

Windows are commonly used analytical functions in a Spark SQL query. The COUNT is one of such a windows functions that will allow you to count over certain window. Many relational databases such as Oracle support COUNT window function with distinct keyword. However, Spark SQL does not support count distinct window function. Spark SQL Count Distinct Window Function Spark SQL supports count window functions. However, the COUNT window function with distinct keyword is not supported as of now. Following is the example of an Oracle COUNT window function with distinct…

Comments Off on Spark SQL Count Distinct Window Function
November 29, 2022
Apache Spark

How to Use Spark SQL REPLACE on DataFrame?

Similar to the DataFrame COALESCE function, REPLACE function is one of the important functions that you will use to manipulate string data. Replace function is one of the widely used function in SQL. You can use the replace function to replace values. In this article, we will check how to use Spark SQL replace function on an Apache Spark DataFrame with an example. Spark SQL REPLACE Spark SQL REPLACE on DataFrame In a SQL, replace function removes all occurrences of a specified substring, and optionally replaces them with another string.…

Comments Off on How to Use Spark SQL REPLACE on DataFrame?
June 16, 2022
Apache Spark

How to Access Azure Blob Storage Files from Databricks?

Azure blob storage is a Microsoft Azure cloud service to store large amount of structured and unstructured data such as text files, database export files, json files, etc. Azure blob storage allows you to store data publicly or you can store application data privately. You can access public Azure blob data without using any additional credentials. But, to access private data, you need to generate access key. In this article, we will check how to access Azure Blob storage files from Databricks? Connecting Azure blob storage with Databricks is a…

Comments Off on How to Access Azure Blob Storage Files from Databricks?
May 4, 2022
Apache Spark

How to Search String in Spark DataFrame? – Scala and PySpark

Being a data engineer, you may work with many different kinds of datasets. You will always get a requirement to filter out or search for a specific string within a data or DataFrame. For example, identify the junk string within a dataset. In this article, we will check how to search a string in Spark DataFrame using different methods. How to Search String in Spark DataFrame? Apache Spark supports many different built in API methods that you can use to search a specific strings in a DataFrame. Following are the…

Comments Off on How to Search String in Spark DataFrame? – Scala and PySpark
June 28, 2021
Apache Spark

How to Find Tables Size in Spark SQL? – Scala Example

Be it relational database, Hive, or Spark SQL, Finding the table size is one of the common requirements. Relational databases such as Snowflake, Teradata, etc support system tables. You can use those system tables to identify the size of tables. But, there are no system tables in Spark SQL. You can make use of the Spark catalog API to find the tables size in the Spark SQL database. Find Tables Size in Spark SQL Starting version 2.0, Spark supports catalog API. It has many useful methods such as listtables, listdatabases,…

Comments Off on How to Find Tables Size in Spark SQL? – Scala Example
June 28, 2021
Apache Spark

A Complete Guide to Spark SQL Array Functions (Syntax & Examples)

Working with complex data types like arrays can feel overwhelming, but Apache Spark makes it incredibly straightforward. If you need to manipulate, clean, or analyze list-based data within your dataframes, mastering Spark's built-in array functions is essential for any data engineer. Page Content Introduction List of Spark SQL Array Functions How to Create an Array in Spark SQL How to Check if an Element is Present How to Remove Duplicate Elements How to Find the Difference Between Two Arrays How to Find Common Elements (Intersection) How to Concatenate Elements with…

Comments Off on A Complete Guide to Spark SQL Array Functions (Syntax & Examples)
June 14, 2021
Apache Spark

How to Add Column with Default Value to Pyspark DataFrame?

Adding a default or constant value to a new column is a common data engineering task. Whether you are dealing with nulls or tracking pipeline metadata, PySpark provides several straightforward ways to get the job done. Let's dive into the best methods to add a default column to your DataFrame. Page Content Introduction Why Add a Column with a Default Value in PySpark? Test DataFrame Setup Method 1: Add a Constant Column Using the lit Function Method 2: Use a PySpark UDF to Add a Default Value Method 3: Use…

Comments Off on How to Add Column with Default Value to Pyspark DataFrame?
June 11, 2021
Apache Spark

How to Search and Check if a Table Exists in a Spark Database using PySpark?

Working with Apache Spark often means managing massive data catalogs with thousands of tables, which can make finding exactly what you need feel like looking for a needle in a haystack. Fortunately, you don't have to guess or manually hunt through your metadata. Let's explore how you can quickly search and verify whether a table exists in your Spark database using PySpark. Page Content Introduction Search Table in Database using PySpark List All Tables in a Database using PySpark Catalog API Check if Table Exists in Database using PySpark Catalog…

Comments Off on How to Search and Check if a Table Exists in a Spark Database using PySpark?
June 8, 2021
Apache Spark

Spark SQL Correlated Subquery and Usage Restrictions

The Correlated subquery in a Spark SQL is a query within a query that refer the columns from the parent or outer query table. These kind of subquery contains one or more correlations between its columns and the columns produced by the outer query. Spark SQL supports the regular and correlated subqueries. You can use the subqueries to improve the performance of the Spark SQL queries such as limiting the number of records returned by the subquery. Spark SQL Correlated Subquery Spark SQL supports many types of subqueries. However, it…

Comments Off on Spark SQL Correlated Subquery and Usage Restrictions
June 7, 2021
Apache Spark

Spark SQL to_date() Function – Pyspark and Scala

Spark SQL supports many date and time conversion functions. One of such a function is to_date() function. Spark SQL to_date() function is used to convert string containing date to a date format. The function is useful when you are trying to transform captured string data into particular data type such as date type. In this article, we will check how to use the Spark to_date function on DataFrame as well as in plain SQL queries. Spark SQL to_date() Function You can use Spark to_date() function to convert and format string…

Comments Off on Spark SQL to_date() Function – Pyspark and Scala
June 7, 2021