Azure Synapse

Azure Synapse Update Join Syntax – Update using other Table

In an ETL model, we use fact tables to store data. Initially, data is loaded into stage (intermediate) tables and then finally, cleansed data is loaded to target fact tables. New records are loaded and existing records are updated, You can use MERGE statement or update table using some other table. In this article, we will check Azure synapse update join syntax with an example. Azure Synapse Update Join Many ETL applications such as loading fact tables use an update join statement where you need to update a table using…

Continue ReadingAzure Synapse Update Join Syntax – Update using other Table
Comments Off on Azure Synapse Update Join Syntax – Update using other Table
July 21, 2020
Redshift

Redshift Stored Procedure Array Variable Alternative

The ARRAY data type is a composite data value that consists of zero or more elements of a specified specific data type. Most of the relational databases such as Netezza, Teradata, etc. supports array variables to be defined inside stored procedures. As of now, Amazon Redshift does not support array variables. In this article, we will check, Redshift Stored Procedure array variable alternative. Redshift Stored Procedure Array Variable Support As mentioned, Amazon Redshift does not support array variables. In my other article, Amazon Redshift Array Support and Alternatives, we have…

Continue ReadingRedshift Stored Procedure Array Variable Alternative
Comments Off on Redshift Stored Procedure Array Variable Alternative
July 21, 2020
General

Cloud Spanner FORMAT_DATE Function and Example

You can use the FORMAT_DATE function to format the string containing a date expression. It is one of the highly used functions to format date. This function is equivalent to to_char and to_date function present in other relational databases. In this article, we will check cloud Spanner FORMAT_DATE function, syntax and some example Cloud Spanner FORMAT_DATE Function The format_date function is used to date expression to required format. Cloud Spanner FORMAT_DATE function formats the date expression containing date according to the specified date format string. Cloud Spanner FORMAT_DATE Function Syntax Following is the FORMAT-DATE syntax. FORMAT_DATE(format_string,…

Continue ReadingCloud Spanner FORMAT_DATE Function and Example
Comments Off on Cloud Spanner FORMAT_DATE Function and Example
July 13, 2020
General

Google Cloud Spanner String Functions, Syntax, Examples

Google Cloud Spanner String Functions are used for various string manipulations in your SQL queries. Cloud Spanner supports most of the standard SQL string functions along with the many extensions to those functions. In my other article, Google Cloud Spanner Regular Expression Functions, we have seen regular expression string functions. In this article, we will check other string functions. Google Cloud Spanner String Functions Following are some of the commonly used Cloud Spanner string functions. String FunctionsDescriptionsBYTE_LENGTH(value)Returns the length of the value in bytes. Value can be string or bytes.CHAR_LENGTH(value)…

Continue ReadingGoogle Cloud Spanner String Functions, Syntax, Examples
Comments Off on Google Cloud Spanner String Functions, Syntax, Examples
July 13, 2020
General

How to Export Spark DataFrame to Teradata Table

In my other article, Steps to Connect Teradata Database from Spark, we have seen how to connect Teradata database from Spark using the JDBC driver. In this article, we will check how to export spark dataframe to a Teradata table using same JDBC drivers. We will also check how to create table our of Spark dataframe if it's not present in the target database. i.e. Teradata database. Export Spark DataFrame to Teradata Table Apache Spark is fast because of its in-memory computation. It is common practice to use Spark as…

Continue ReadingHow to Export Spark DataFrame to Teradata Table
Comments Off on How to Export Spark DataFrame to Teradata Table
July 13, 2020
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