General

QUALIFY Clause in Oracle: Native Example and Alternative

If you are moving your database from Teradata to Oracle, you might be searching for a QUALIFY Clause in Oracle. While older versions needed a workaround to filter ordered analytical functions, the highly anticipated QUALIFY clause has officially been introduced in Oracle 26. Below, we will check how to use this new feature and the best fallback method if you are still on an older Oracle version. Page Content Introduction QUALIFY Clause in Oracle 26 QUALIFY Clause Alternative in Oracle Older Versions Conclusion Introduction When writing SQL queries, filtering the…

Comments Off on QUALIFY Clause in Oracle: Native Example and Alternative
May 19, 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
Redshift

Psql Invalid Value for Parameter client_encoding – Redshift

In my other article, we have seen steps to connect to Redshift cluster using PostgreSQL PSQL. When you connect to Amazon Redshift from Windows command prompt, you may experience the client encoding issue. In this article, we will check how to resolve "psql: FATAL: invalid value for parameter "client_encoding": "WIN1252"" issue. This is issue is mostly seen when connecting using Windows command prompt. Postgres PSQL Introduction PSQL is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Since Redshift…

Comments Off on Psql Invalid Value for Parameter client_encoding – Redshift
April 5, 2022
Redshift

How to Choose the Best Compression Encoding in Amazon Redshift?

Data is the backbone of any modern company's decision-making process, helping teams spot trends and fuel growth. However, as your data grows exponentially, optimizing how it is stored becomes critical to keep costs down and speeds up. In this guide, we will walk you through how to choose the right Amazon Redshift compression encoding to improve your query performance and save storage space. Analyze Compression in Redshift Page Content Introduction What is Compression Encoding in Redshift? Types of Redshift Compression Encodings How to Use Redshift ENCODE AUTO? How to Choose…

Comments Off on How to Choose the Best Compression Encoding in Amazon Redshift?
March 17, 2022
Redshift

How to Query JSON Data in Amazon Redshift (With Examples)?

Similar to many cloud data warehouses such as Snowflake, Amazon Redshift supports many json functions to perform operations on json such as validating json data, parsing json values, etc. You can use these json functions to query the JSON data stored in a varchar column. In this article, we will check how to query JSON data in an Amazon Redshift with an example. Handling semi-structured data doesn't have to be a headache. If you are working with Amazon Redshift, you have powerful, native functions to store, parse, and analyze JSON…

Comments Off on How to Query JSON Data in Amazon Redshift (With Examples)?
March 4, 2022
Redshift

How to Create External Tables in Amazon Redshift (Step-by-Step Guide)

Want to query your Amazon S3 data directly without moving it into your data warehouse? Amazon Redshift external tables make it easy to bridge your data lake and your database. Let's walk through exactly how to set them up and start querying your flat files in minutes. Page Content Introduction Step 1: Create an External Schema and Database Step 2: Create an External Table in an External Schema Step 3: Access the External Table Step 4: List External Tables Available on Amazon Redshift Step 5: Export Redshift Tables Using External…

Comments Off on How to Create External Tables in Amazon Redshift (Step-by-Step Guide)
March 2, 2022
Redshift

How to Convert Teradata Integer Dates in Amazon Redshift?

Migrating from Teradata to Amazon Redshift often brings up unique data formatting challenges, especially around legacy data types. If you are struggling to handle Teradata's custom integer date formats in your new Redshift environment, you aren't alone. Let's explore the most efficient ways to translate these numeric dates into standard, usable Redshift date formats. Page Content Introduction Understanding Teradata Number Date Values Translating Numbers to Dates in Teradata Converting Teradata Integer Dates in Amazon Redshift Using a Redshift UDF for Date Conversion Conclusion Introduction Relational databases like Teradata natively support…

Comments Off on How to Convert Teradata Integer Dates in Amazon Redshift?
January 19, 2022
GCP BigQuery

NVL and NVL2 Functions in BigQuery and Alternatives

Like many relational databases, GCP BigQuery supports many useful built-in functions. Not all the functions those are available in other relational databases are supported in BigQuery. But, BigQuery provides alternative built-in functions that you can use. In this article, we will check NVL and NVL2 functions in BigQuery database. Page Content Introduction Does BigQuery Support NVL and NVL2 Functions? Best BigQuery Alternatives to the NVL Function Using IFNULL as an NVL Alternative Using COALESCE as an NVL Alternative Best BigQuery Alternatives to the NVL2 Function Using CASE as an NVL2…

Comments Off on NVL and NVL2 Functions in BigQuery and Alternatives
January 13, 2022
Redshift

Redshift SELECT INTO Variable – Complete Guide and Examples

The relational database that supports procedural language allows you to assign a value to local variable using SELECT statement within stored procedures. The databases such as Teradata, Oracle supports SELECT INTO clause to assign a value to a local variable. In this article, we will check how to assign a subquery value to a local variable using Redshift SELECT INTO clause within Stored Procedures. Are you trying to assign a query result to a local variable in Amazon Redshift? Mastering the SELECT INTO statement is essential for writing efficient PL/pgSQL…

Comments Off on Redshift SELECT INTO Variable – Complete Guide and Examples
December 13, 2021
Redshift

What is ENDS_WITH Function Alternative in Redshift?

The relational databases such as Oracle and cloud databases such as BigQuery, Snowflake supports ends with function. The function name may be different, but the functionality is same. For example, Oracle and BigQuery provides ENDS_WITH function and Snowflake contains ENDSWITH function. The Amazon Redshift does not provide ENDS_WITH function. In my other article, What is STARTS_WITH Function Alternative in Redshift, we have seen starts_with alternative in Redshift. In this article, we will check what is ENDS_WITH function alternative in AWS Redshift? ENDS_WITH Function In Redshift Not all the string functions…

Comments Off on What is ENDS_WITH Function Alternative in Redshift?
November 30, 2021