Redshift

Redshift WHERE Clause with Multiple Columns

Many relational databases such as Teradata and Oracle supports the multiple columns in WHERE clause. You use two or more columns in predicates. When you want to migrate to cloud databases such as Amazon Redshift, you always look for compatible SQL patterns on the target side. In this article, we will check one of such pattern that is Amazon Redshift WHERE clause predicate with multiple columns. What is a WHERE Clause? Before going into details, first let us check what is a where clause in general. The WHERE clause contains…

Continue ReadingRedshift WHERE Clause with Multiple Columns
Comments Off on Redshift WHERE Clause with Multiple Columns
July 6, 2022
Redshift

How to Create a Materialized View in Redshift?

Many organizations are using Redshift as their data warehouse house and they are using Redshift for reporting or for dashboard queries. Imagine if applications query often must perform complex queries on large tables. For example, consider a reporting SELECT statement that performs multi-table joins and aggregations on tables that contain billions of records. You can address this issue using materialized views in Amazon Redshift. In this article, we will check how to create a materialized view in Amazon Redshift. Materialized View in Redshift What are Materialized Views in Redshift? A materialized…

Continue ReadingHow to Create a Materialized View in Redshift?
Comments Off on How to Create a Materialized View in Redshift?
June 16, 2022
Apache Spark

How to Use Spark SQL REPLACE on DataFrame?

Similar to the DataFrame COALESCE function, REPLACE function is one of the important functions that you will use to manipulate string data. Replace function is one of the widely used function in SQL. You can use the replace function to replace values. In this article, we will check how to use Spark SQL replace function on an Apache Spark DataFrame with an example. Spark SQL REPLACE Spark SQL REPLACE on DataFrame In a SQL, replace function removes all occurrences of a specified substring, and optionally replaces them with another string.…

Continue ReadingHow to Use Spark SQL REPLACE on DataFrame?
Comments Off on How to Use Spark SQL REPLACE on DataFrame?
June 16, 2022
General

QUALIFY Clause in Oracle – Alternative

If you are migrating from Teradata to Oracle or Redshift, you will notice that QUALIFY clause is not available. The feature such as QUALIFY clause is widely used in Teradata. But, the same feature is not available in Oracle. In this article, we will check what is QUALIFY Clause alternative in Oracle. QUALIFY Clause in Oracle The relational databases such as Teradata uses QUALIFY clause to filter the result of ordered analytical function. The Oracle database does not support this feature yet. But, when you are migrating Teradata scripts to…

Continue ReadingQUALIFY Clause in Oracle – Alternative
Comments Off on QUALIFY Clause in Oracle – 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? Access Azure Blob Storage Files from Databricks Similar…

Continue ReadingHow to Access Azure Blob Storage Files from Databricks?
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…

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

How to Choose Correct Compression Encode in Redshift?

Data is a key aspect of any company's decision making process. It helps organizations to make smart decision using trends such as purchase, growth area and other high-level functionalities. Organizations grow by making smart decisions so does data. As a typical company’s size of data has grown exponentially, it’s become even more critical to optimize data storage. The size of data not only affects storage and cost, it also affects the performance of your query. The typical query performance is always directly proportional to the size of data because smaller…

Continue ReadingHow to Choose Correct Compression Encode in Redshift?
Comments Off on How to Choose Correct Compression Encode in Redshift?
March 17, 2022
Redshift

How to Query JSON Data in Redshift? – 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. Query JSON Data in Redshift Amazon Redshift do not contain any special data type to store JSON string. You can store JSON value in SUPER data type.…

Continue ReadingHow to Query JSON Data in Redshift? – Examples
Comments Off on How to Query JSON Data in Redshift? – Examples
March 4, 2022
Redshift

How to Create External Tables in Amazon Redshift?

Amazon Redshift External tables allow you to access files stored in S3 storage as a regular table. You can join the Redshift external table with a database tables such as permanent or temporary table to get required information. You can also perform a complex transformation involving various tables. The External tables are commonly used to build the data lake where you access the raw data which is stored in the form of file and perform join with existing tables. There are many situations in which you need to access the…

Continue ReadingHow to Create External Tables in Amazon Redshift?
Comments Off on How to Create External Tables in Amazon Redshift?
March 2, 2022
Redshift

Teradata Number Date Alternative in Redshift

The relational database such as Teradata supports numeric dates. For example, 851231 is a valid representation for date '1985-12-31'. These types of Teradata integer date formats are not yet supported in Amazon Redshift. In this article, we will check what is Teradata number date alternative in AWS Redshift. Teradata Number Date Values There are many Teradata features that are not yet available in any other Relational or databases. One of such features is Teradata number date values. Teradata Database stores each DATE value as a four-byte integer using the following…

Continue ReadingTeradata Number Date Alternative in Redshift
Comments Off on Teradata Number Date Alternative in Redshift
January 19, 2022