Snowflake

Snowflake Load Local CSV File using COPY and Example

Many organizations use flat files such as CSV or TSV files to offload large tables. Managing flat files such as CSV is easy and it can be transported by any electronic medium. In this article, we will check how to load or import local CSV file into Snowflake using COPY command with some examples. Load Local CSV File using Snowflake COPY Command There are a couple of methods that you can use to load a csv file present in your local system. Following are the methods. Use SnowSQL command Line…

Continue ReadingSnowflake Load Local CSV File using COPY and Example
Comments Off on Snowflake Load Local CSV File using COPY and Example
February 4, 2020
Snowflake

Snowflake Type of Subqueries and Examples

In general, subquery in a database is a nested query block in a query statement. It is simply a SELECT expression enclosed in a parenthesis. The Subquery may return zero to one or more values to its upper or parent SELECT statements. In this article, we will check Snowflake type of subqueries with an examples. Snowflake Subqueries A subquery in Snowflake is a nested select statement, that return zero or more records to is upper select statement. The outer SELECT statement that contains subquery is sometimes referred to as a…

Continue ReadingSnowflake Type of Subqueries and Examples
Comments Off on Snowflake Type of Subqueries and Examples
February 4, 2020
Snowflake

Snowflake Fixed-Width File Loading Options and Examples

Fixed width text files are special cases of text files where the format is specified by column widths, pad character and left or right alignment. Many telecom companies use fixed-width file to store call detail records (CDR) data. In this format, column width are in terms of units of characters. In this article, we will learn about Snowflake Fixed-Width file loading options and examples. Snowflake Fixed-Width File Loading The fixed-width data files have uniform lengths for each column of data. Each field in a fixed-width data file has exactly the…

Continue ReadingSnowflake Fixed-Width File Loading Options and Examples
Comments Off on Snowflake Fixed-Width File Loading Options and Examples
February 4, 2020
Apache Spark

Spark SQL CASE WHEN on DataFrame – Examples

In general, the CASE expression or command is a conditional expression, similar to if-then-else statements found in other languages. Spark SQL supports almost all features that are available in Apace Hive. One of such a features is CASE statement. In this article, how to use CASE WHEN and OTHERWISE statement on a Spark SQL DataFrame. Spark SQL CASE WHEN on DataFrame The CASE WHEN and OTHERWISE function or statement tests whether any of a sequence of expressions is true, and returns a corresponding result for the first true expression. Spark…

Continue ReadingSpark SQL CASE WHEN on DataFrame – Examples
Comments Off on Spark SQL CASE WHEN on DataFrame – Examples
February 4, 2020
Apache Spark

Import CSV file to Pyspark DataFrame – Example

Many organization uses a flat file format such as CSV or TSV to offload their tables. Managing flat file is easy and can be transported by any electronic medium. In this article we will check how to import CSV file to Pyspark DataFrame with some examples. Import CSV file to Pyspark DataFrame There are many methods that you can use to import CSV file into pyspark or Spark DataFrame. But, the following methods are easy to use. Read Local CSV using com.databricks.spark.csv FormatRun Spark SQL Query to Create Spark DataFrame…

Continue ReadingImport CSV file to Pyspark DataFrame – Example
Comments Off on Import CSV file to Pyspark DataFrame – Example
February 3, 2020
Apache Spark

Spark SQL Date and Timestamp Functions and Examples

Spark SQL provides many built-in functions. The functions such as date and time functions are useful when you are working with DataFrame which stores date and time type values. The built-in functions also support type conversion functions that you can use to format the date or time type. In this article, we will check what are Spark SQL date and timestamp functions with some examples. Spark SQL Date and Timestamp Functions Spark SQL supports almost all date and time functions that are supported in Apache Hive. You can use these…

Continue ReadingSpark SQL Date and Timestamp Functions and Examples
Comments Off on Spark SQL Date and Timestamp Functions and Examples
January 31, 2020
Apache Spark

Rename PySpark DataFrame Column – Methods and Examples

A DataFrame in Spark is a dataset organized into named columns. Spark data frame is conceptually equivalent to a table in a relational database or a data frame in R/Python, but with richer optimizations. When you work with Datarames, you may get a requirement to rename the column. In this article, we will check how to rename a PySpark DataFrame column, Methods to rename DF column and some examples. Rename PySpark DataFrame Column As mentioned earlier, we often need to rename one column or multiple columns on PySpark (or Spark) DataFrame. Note…

Continue ReadingRename PySpark DataFrame Column – Methods and Examples
Comments Off on Rename PySpark DataFrame Column – Methods and Examples
January 31, 2020
Snowflake

Snowflake Pattern Matching – LIKE, LIKE ANY, CONTAINS, LIKE ALL Conditions

The pattern matching conditions in Snowflake are used to search a string for a given pattern. You can search for the string by matching particular patterns. Snowflake Pattern Matching A pattern-matching operator searches a string for a pattern specified in the conditional expression and returns either Boolean (true/ false) or matching value if it finds a match. These conditions are particularly important when you need to search string patterns in your database column values. Pattern matching conditions are mainly used in WHERE conditions. Following are the commonly used pattern matching…

Continue ReadingSnowflake Pattern Matching – LIKE, LIKE ANY, CONTAINS, LIKE ALL Conditions
Comments Off on Snowflake Pattern Matching – LIKE, LIKE ANY, CONTAINS, LIKE ALL Conditions
January 30, 2020
Redshift

Redshift NOT NULL Constraint, Syntax and Examples

Similar to most of the MPP databases such as Snowflake, the Amazon Redshift database allows you to define constraints. The Redshift database does not enforce constraints like primary key, foreign key and unique key. But, it does enforce the NOT NULL constraint. In this article, we will check Redshift NOT NULL constraint, its syntax and usage. Redshift NOT NULL Constraint Constraints other than NOT NULL are created as disabled. Amazon Redshift enforces only NOT NULL. You can create NOT NULL constraint while creating tables. Redshift NOT NULL Constraint Syntax There…

Continue ReadingRedshift NOT NULL Constraint, Syntax and Examples
Comments Off on Redshift NOT NULL Constraint, Syntax and Examples
January 27, 2020
Apache Spark

SQL Merge Operation Using Pyspark – UPSERT Example

In the relational databases such as Snowflake, Netezza, Oracle, etc, Merge statement is used to manipulate the data stored in the table. In this article, we will check how to SQL Merge operation simulation using Pyspark. The method is same in Scala with little modification. SQL Merge Statement The MERGE command in relational databases, allows you to update old records and insert new records simultaneously. This command is sometimes called UPSERT (UPdate and inSERT command). Following is the sample merge statement available in RDBMS. merge into merge_test using merge_test2 on…

Continue ReadingSQL Merge Operation Using Pyspark – UPSERT Example
1 Comment
January 27, 2020