Redshift

How to Export Spark DataFrame to Redshift Table

In my other article How to Create Redshift Table from DataFrame using Python, we have seen how to create Redshift table from Python Pandas DataFrame. In this article, we will check how to export Spark DataFrame to Redshift table. Export Spark DataFrame to Redshift Table Apache Spark is fast because of its in-memory computation. It is common practice to use Spark as an execution engine to process huge amount data. Sometimes, you may get a requirement to export processed data back to Redshift for reporting. We are going to use…

Continue ReadingHow to Export Spark DataFrame to Redshift Table
Comments Off on How to Export Spark DataFrame to Redshift Table
July 10, 2020
General

How to Load Spark DataFrame to Oracle Table – Example

In my previous post Steps to Connect Oracle Database from Spark, I have explained how to connect to Oracle and query tables from the database. But in some cases, you may get requirement to load Spark dataFrame to Oracle table. We can also use JDBC to write data from a Spark dataframe to database tables. In the subsequent sections, we will explore method to write Spark dataframe to Oracle Table. Load Spark DataFrame to Oracle Table As mentioned in the previous section, we can use JDBC driver to write dataframe…

Continue ReadingHow to Load Spark DataFrame to Oracle Table – Example
Comments Off on How to Load Spark DataFrame to Oracle Table – Example
July 9, 2020
General

Google Cloud Spanner Regular Expression Functions

The regular expressions are commonly used to identify the precise pattern of the characters in the string. Many popular programming languages such as Python, Java, R, etc. extensively use regular expression functions. You can also use regular expression to validate strings, for example, extracting numbers from the string values. In this article, we will check regular expression functions supported in cloud spanner. Cloud Spanner Regular Expression Functions The regular expression functions in cloud spanner are string functions that match a given regular expression. These functions are commonly called as a…

Continue ReadingGoogle Cloud Spanner Regular Expression Functions
Comments Off on Google Cloud Spanner Regular Expression Functions
July 7, 2020
Redshift

Rows Affected by Last Redshift SQL Query – Examples

In many situations, you need to know the number of rows affected by an insert, delete or update query in the current session. The popular relational databases such as SQL Server, Snowflake provides system variables that hold the information of records affected by the last SQL statement. Amazon Redshift just like an Azure synapse date warehouse does not provide any system variables. We have to identify the workaround to get rows affected by the last SQL query in Redshift. Redshift Rows Affected by Last SQL Query When you are migrating existing applications such as a Teradata BTEQ scripts to Redshift, you will find it hard…

Continue ReadingRows Affected by Last Redshift SQL Query – Examples
Comments Off on Rows Affected by Last Redshift SQL Query – Examples
July 7, 2020
Azure Synapse

Azure Synapse @@ROWCOUNT Alternative

The @@ROWCOUNT is one of the important system variable that holds the count of the last executed statement in SQL server and Azure SQL Database. This variable is often used in conditional statements such as IF statement to execute and skip certain T-SQL statements. Azure Synapse does not support @@ROWCOUNT variable. In this article, we will check Azure Synapse @@ROWCOUNT Alternative. The @@ROWCOUNT in SQL server returns the number of rows affected by the last statement. Azure Synapse @@ROWCOUNT Alternative When you are migrating existing applications such as a Teradata…

Continue ReadingAzure Synapse @@ROWCOUNT Alternative
Comments Off on Azure Synapse @@ROWCOUNT Alternative
July 6, 2020
Data Warehouse

Data Warehouse and Data Lake – Definition and differences

You will hear a lot about data warehouse and data lake when you work on Big Data. Both are widely used for storing Big Data but, they are not interchangeable. In this article, we will check data warehouse and data lake, its definition and differences. Data Warehouse and Data Lake As mentioned earlier, both are used for storing big data. But, they server different purpose when it comes to data usage. Data Warehouse A Data warehouse is an electronic storage of business data for analysis. It is a technique for…

Continue ReadingData Warehouse and Data Lake – Definition and differences
Comments Off on Data Warehouse and Data Lake – Definition and differences
June 23, 2020
Apache Spark

Spark SQL Bucketing on DataFrame – Examples

We have already discussed the Hive bucketing concept in my other post. The concept is also same in Spark SQL. Bucketing concept is dividing partition into a number of equal clusters (also called clustering) or buckets. The concept is very much similar to clustering in relational databases such as Netezza, Snowflake, etc. In this article, we will check Spark SQL bucketing on DataFrame instead of tables. We will use Pyspark to demonstrate the bucketing examples. The concept is same in Scala as well. Spark SQL Bucketing on DataFrame Bucketing is an optimization technique in both…

Continue ReadingSpark SQL Bucketing on DataFrame – Examples
Comments Off on Spark SQL Bucketing on DataFrame – Examples
May 29, 2020
Apache Spark

Replace Pyspark DataFrame Column Value – Methods

A DataFrame in Spark is a dataset organized into named columns. Spark DataFrame consists of columns and rows similar to that of relational database tables. There are many situations you may get unwanted values such as invalid values in the data frame. In this article, we will check how to replace such a value in pyspark DataFrame column. We will also check methods to replace values in Spark DataFrames. Replace Pyspark DataFrame Column Value As mentioned, we often get a requirement to cleanse the data by replacing unwanted values from the DataFrame…

Continue ReadingReplace Pyspark DataFrame Column Value – Methods
Comments Off on Replace Pyspark DataFrame Column Value – Methods
May 20, 2020
Apache Spark

Create Row for each array Element using PySpark Explode

Best about Spark is that you can easily work with semi-structured data such as JSON. The json can contains arrays or map elements. You may get requirement to create a row for each array or map elements. In this article, we will check how to use Pyspark explode function to create a row for each array element. Create a Row for each array Element using PySpark Explode Before jumping into the examples, first, let us understand what is explode function in PySpark. Pyspark Explode Function The Pyspark explode function returns…

Continue ReadingCreate Row for each array Element using PySpark Explode
Comments Off on Create Row for each array Element using PySpark Explode
May 20, 2020
Apache Spark

Apache Spark SQL Bucketing Support – Explanation

Spark SQL supports clustering column values using bucketing concept. Bucketing and partition is similar to that of Hive concept, but with syntax change. In this article, we will check Apache Spark SQL Bucketing support in different versions of Spark. In this article, we will concentrate only on the Spark SQL DDL changes. On applying bucketing on DataFrame, go through the article. Apache Spark SQL Bucketing Support Bucketing is an optimization technique in Spark SQL that uses buckets and bucketing columns to determine data partitioning. The bucketing concept is one of the optimization technique that use bucketing to…

Continue ReadingApache Spark SQL Bucketing Support – Explanation
Comments Off on Apache Spark SQL Bucketing Support – Explanation
May 18, 2020