Snowflake

How to Update JSON field in Snowflake Variant Column?

The update is a common operation in any relational databases. A Snowflake, a leading cloud data warehouse supports some unique features such as built-in support for semi structured data. Snowflake support many built-in functions that allow you yo manipulate semi-structured data, such as JSON and XML data. Its universal data type VARIANT allows you to store semi-structured data including parquet. In this article, we will check how to update JSON field in Snowflake. Update or Replace JSON field in Snowflake Snowflake support functionalities that are present in almost all relational…

Continue ReadingHow to Update JSON field in Snowflake Variant Column?
Comments Off on How to Update JSON field in Snowflake Variant Column?
June 15, 2021
Apache Spark

Spark SQL Array Functions – Syntax and Examples

Similar to relational databases such as Snowflake, Teradata, Spark SQL support many useful array functions. You can use these array manipulation functions to manipulate the array types. In this article, we will check how to work with Spark SQL Array Functions its Syntax and Examples. Spark SQL Array Functions Following is the list of Spark SQL array functions with brief descriptions: Spark SQL Array FunctionDescriptionarray(expr, ...) Returns an array with the given elements.array_contains(array, value)Returns true if the array contains the value.array_distinct(array)This function removes duplicate values from the arrayarray_except(array1, array2)Returns an array…

Continue ReadingSpark SQL Array Functions – Syntax and Examples
Comments Off on Spark SQL Array Functions – Syntax and Examples
June 14, 2021
Apache Spark

How to Add Column with Default Value to Pyspark DataFrame?

Since the inception, Spark has made a lot of improvement and added many useful DataFrame API's. If you are from SQL background, you might have noticed that adding default value to a column when you add new column is a common practice. This is just to make sure the new column does not hold junk or NULL values. In this article, we will check how to add a column with a default or constant value to a Pyspark DataFrame. Add a Column with Default Value to Pyspark DataFrame Adding a…

Continue ReadingHow to Add Column with Default Value to Pyspark DataFrame?
Comments Off on How to Add Column with Default Value to Pyspark DataFrame?
June 11, 2021
Snowflake

How to combine two arrays in Snowflake?

Snowflake is the one of the databases that combines many useful functions from other relational databases such as Oracle, PostgreSQL, Teradata, etc. Like many other relational databases, Snowflake support many array functions. In this article, we will check how to combine/merge/concatenate two or more arrays in Snowflake. Combine Two Arrays in Snowflake Snowflake allows you to deal with many different kinds of data sets. For example, you can work with JSON, XML or array variables with an ease. As Snowflake integrates many heterogeneous data sets, you may get requirement such…

Continue ReadingHow to combine two arrays in Snowflake?
Comments Off on How to combine two arrays in Snowflake?
June 10, 2021
Snowflake

How to Get First Row of each Group in Snowflake?

In the data warehouse reporting, you will encounter many different scenarios. One of such scenario is to get first row of each group. For example, identify the department wise highest salary. In this article, we will check how to select or get first row of each group in Snowflake. Select First Record of each Group in Snowflake Selecting first row of each group in SQL is one of the common query in reporting. You need to use proper function that does not take much time to return results. Following are…

Continue ReadingHow to Get First Row of each Group in Snowflake?
Comments Off on How to Get First Row of each Group in Snowflake?
June 9, 2021
Apache Spark

PySpark – Search Table in Spark Database

In a real world scenario, you will be dealing with petabytes of data and thousands of tables in a hundred of databases within Spark or Hive catalog. It is practically, time consuming to identify the particular table in a database, hence it is always good idea to develop reusable code that you can use to search table(s) in a given database. The usability includes safely dropping table(s) and identify table structure. In this article, we will learn how to search table in a database using PySpark. Search Table in Database…

Continue ReadingPySpark – Search Table in Spark Database
Comments Off on PySpark – Search Table in Spark Database
June 8, 2021
Snowflake

Snowflake Concat Function and Operator – Examples

The Snowflake cloud architecture supports data ingestion from multiple sources, hence it is a common requirement to combine data from multiple columns to come up with required results. You may also get a requirement to concatenate multiple strings before loading them to target table. For example, you may get requirement to combine state and city columns before loading data to the customer address table. In this article, we will check Snowflake CONCAT function, its Syntax and examples. We will also check how to combine two or more columns using Snowflake…

Continue ReadingSnowflake Concat Function and Operator – Examples
Comments Off on Snowflake Concat Function and Operator – 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…

Continue ReadingSpark SQL Correlated Subquery and Usage Restrictions
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…

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

Apache Spark SQL Supported Subqueries and Examples

A subquery in Spark SQL is a select expression that is enclosed in parentheses as a nested query block in a query statement. The subquery in Apache Spark SQL is similar to subquery in other relational databases that may return zero to one or more values to its upper select statements. In this article, we will check Apache Spark SQL supported subqueries and some examples. Spark SQL Supported Subqueries Spark SQL subqueries are another select statement or expression enclosed in parenthesis as a nested query block. You can use these…

Continue ReadingApache Spark SQL Supported Subqueries and Examples
Comments Off on Apache Spark SQL Supported Subqueries and Examples
June 2, 2021