Snowflake

How to Connect Databricks to Snowflake?: A Step-by-Step Guide

Building a modern data stack often means combining the best tools for the job. By connecting Databricks to Snowflake, data teams can seamlessly run complex machine learning workflows and push the finalized datasets directly into their data warehouse for lightning-fast business intelligence reporting. Page Content Introduction Why Connect Databricks and Snowflake? Install the Snowflake Spark Connector on Databricks Create the Snowflake Options Dictionary Write the Databricks DataFrame to a Snowflake Table Connection Troubleshooting Conclusion Introduction Many organizations rely on a hybrid data architecture to handle different stages of their data…

Comments Off on How to Connect Databricks to Snowflake?: A Step-by-Step Guide
June 16, 2021
Snowflake

How to Update a JSON Field in a Snowflake VARIANT Column?

Working with semi-structured data like JSON is one of Snowflake's strongest capabilities. However, modifying a specific JSON field nested inside a VARIANT column can sometimes trip up even experienced data engineers. In this guide, we'll walk you through the simplest way to update or replace JSON values natively in Snowflake. Page Content Introduction Updating or Replacing JSON Fields in Snowflake Using the Snowflake OBJECT_INSERT Function Example: Updating a JSON Field in a VARIANT Column Conclusion Introduction Data updates are a standard operation across all relational databases. As a leading cloud…

Comments Off on How to Update a JSON Field in a Snowflake VARIANT Column?
June 15, 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
Snowflake

How to Combine Two or More Arrays in Snowflake (Using ARRAY_CAT)?

Working with semi-structured data in Snowflake often means dealing with arrays. Whether you are transforming raw JSON or consolidating lists, knowing how to properly combine arrays in Snowflake is an essential skill for any data engineer or analyst. Page Content Introduction Why Merge Arrays in Snowflake? The Snowflake ARRAY_CAT Function Test Data Setup Examples: Merging Array Columns and Variables How to Combine Three or More Arrays in Snowflake Conclusion Introduction Snowflake stands out as a powerful data warehouse because it natively supports semi-structured data formats like JSON, XML, and arrays…

Comments Off on How to Combine Two or More Arrays in Snowflake (Using ARRAY_CAT)?
June 10, 2021
Snowflake

How to Get the First Row of Each Group in Snowflake Using Window Functions?

When building data warehouse reports, you'll often need to find the top record within specific categories, like identifying the highest-paid employee in each department. If you're wondering how to get the first row of each group in Snowflake, you're in the right place. In this guide, we'll walk through the most efficient SQL window functions to handle this common scenario without slowing down your queries. Page Content Introduction Setting Up the Test Data Method 1: Using ROW_NUMBER to Select the First Row Method 2: Using FIRST_VALUE to Get the First…

Comments Off on How to Get the First Row of Each Group in Snowflake Using Window Functions?
June 9, 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
Snowflake

Snowflake CONCAT Function and Operator: A Complete Guide with Examples

Working with data often means pulling pieces of information together from different places. Whether you need to merge first and last names or combine city and state columns for a reporting dashboard, knowing how to properly concatenate strings in Snowflake is an essential skill for any data professional. Page Content Introduction Snowflake CONCAT Function and Operator Overview How to Use the Snowflake CONCAT Function Snowflake CONCAT Function Examples Nested CONCAT Functions in Snowflake Using the Snowflake CONCAT_WS Function The Snowflake CONCAT Operator (||) Handling NULL Values When Concatenating Strings Conclusion…

Comments Off on Snowflake CONCAT Function and Operator: A Complete Guide with Examples
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