General

Connect PostgreSQL using Python and Jdbc Driver- Example

PostgreSQL is one of the widely used open source relational database management system (RDBMS). Sometimes, it is simply called Postgres. Many modern day databases such as Redshift, Netezza, Vertica,etc are based on the PostgreSQL. Postgres supports both JDBC and OBDC drivers. You can use those drivers from any programming language to connect. In this article, we will check how to connect PostgreSQL using Python and Jdbc driver with a working example. PostgreSQL JDBC Driver PostgreSQL offers drivers for the programming languages and tools that are compatible with JDBC API. You…

Continue ReadingConnect PostgreSQL using Python and Jdbc Driver- Example
Comments Off on Connect PostgreSQL using Python and Jdbc Driver- Example
October 4, 2019
BigData

What is Hive Lateral View and How to use it?

The best part of Apache Hive is it supports array types. i.e. you can store the array values in Hive table columns. With the help of an array, you can minimize the table rows by grouping together in the form of an array. In this article, we will check what is the Hive lateral view and how to use it with array values. You can use lateral view either with EXPLODE or INLINE function. What is Hive Lateral View? Before going in detail, let us check what is lateral view? In Hive, lateral view…

Continue ReadingWhat is Hive Lateral View and How to use it?
Comments Off on What is Hive Lateral View and How to use it?
October 3, 2019
BigData

Hive Insert into Partition Table and Examples

The Hive INSERT command is used to insert data into Hive table already created using CREATE TABLE command. Inserting data into partition table is a bit different compared to normal insert or relation database insert command. There are many ways that you can use to insert data into a partitioned table in Hive. In this article, we will check Hive insert into Partition table and some examples. Hive Insert into Partition Table As mentioned earlier, inserting data into a partitioned Hive table is quite different compared to relational databases. You…

Continue ReadingHive Insert into Partition Table and Examples
2 Comments
September 30, 2019
BigData

Apache Hive Type Conversion Functions and Examples

Apache Hive has some very strict rules regarding data types for function parameters that you provide while executing it. Hive type conversion functions are used to explicitly convert to the required type and format. For example, Hive does not convert DOUBLE to FLOAT, INT to STRING etc. In my other post, we have discussed on Hive date functions and examples. In this article, we will check out Cloudera Hive type conversion functions with some examples. Related Article Commonly used Apache Hive Date Functions and Examples Apache Hive Type Conversion Functions…

Continue ReadingApache Hive Type Conversion Functions and Examples
2 Comments
September 30, 2019
Apache Spark

How to Save Spark DataFrame as Hive Table – Example

Apache Spark is one of the highly contributed frameworks. Many e-commerce, data analytics and travel companies are using Spark to analyze the huge amount of data as soon as possible. Because of in memory computations, Apache Spark can provide results 10 to 100X faster compared to Hive. In this article, we will check How to Save Spark DataFrame as Hive Table? and some examples. How to Save Spark DataFrame as Hive Table? Because of its in-memory computation, Spark is used to process the complex computation. In case if you have…

Continue ReadingHow to Save Spark DataFrame as Hive Table – Example
Comments Off on How to Save Spark DataFrame as Hive Table – Example
September 26, 2019
BigData

Hive Insert from Select Statement and Examples

Apache Hive is the data warehouse framework on top of the Hadoop distributed file system (HDFS). It provides a query language called Hive Query Language, HiveQL or HQL. HiveQL syntax is similar to SQL syntax with minor changes. Similar to SQL insert statements, HQL also supports inserting data into tables using various methods. In this article, we will check one of the data insert methods into Hive table using a Select statement or clause. Hive Insert Data into Table Methods Below are the some of commonly used methods to insert…

Continue ReadingHive Insert from Select Statement and Examples
Comments Off on Hive Insert from Select Statement and Examples
September 25, 2019
Redshift

Amazon Redshift Array Support and Alternatives – Example

It is common to store values in the form of array in the database. Many databases like Netezza, PostgreSQL supports array functions to manipulate the array types. However, Amazon Redshift does not support array types. In this article, we will check alternative method that you can use. Amazon Redshift Array Support As mentioned in the previous section, Amazon Redshift does not support array types or functions. Though, Redshift uses PostgreSQL, but they yet to provide support to arrays. You can check unsupported features in the official documentation. Best part is,…

Continue ReadingAmazon Redshift Array Support and Alternatives – Example
Comments Off on Amazon Redshift Array Support and Alternatives – Example
September 20, 2019
Redshift

Amazon Redshift Load CSV File using COPY and Example

Many organizations use flat files such as CSV or TSV files to offload tables, managing flat files is easy and can be transported by any electronic medium. In this article, we will check how to load or import CSV file into Amazon Redshift using COPY command with some examples. Page Content Introduction Preparing for Redshift Load CSV Load CSV File using Redshift COPY Command Redshift COPY command with Column Names Redshift COPY command to ignore First Line from CSV Redshift COPY Command with Custom Delimiter Best Practices for Loading CSV…

Continue ReadingAmazon Redshift Load CSV File using COPY and Example
Comments Off on Amazon Redshift Load CSV File using COPY and Example
September 19, 2019
Redshift

How to Create Redshift Table from DataFrame using Python

As you know, Python is one of the widely used Programming languages for the data analysis, data science and machine learning. When analyzing data using Python, you will use Numpy and Pandas extensively. In this article, we will check how to create Redshift table from DataFrame in Python. Here DataFrame is actually referred to pandas not Spark. I will write another article on how to create tables out of Spark DataFrame, but for now let us stick to pandas df. Python Pandas DataFrame The pandas DataFrame's are really very useful…

Continue ReadingHow to Create Redshift Table from DataFrame using Python
Comments Off on How to Create Redshift Table from DataFrame using Python
September 18, 2019
BigData

How to Exclude Hive Partition Column From SELECT Query

Apache Hive is a data warehouse framework on top of Hadoop HDFS. Hive is a high level language to store and analyse large volumes of data. Apache Hive support most of the relational database features such as partitioning large tables and store values according to partition column. But, Hive stores partition column as a virtual column and is visible when you perform 'select * from table'. In this article, we will check method to exclude Hive partition column from a SELECT query. Hive Table Partition Partition in Hive table is…

Continue ReadingHow to Exclude Hive Partition Column From SELECT Query
Comments Off on How to Exclude Hive Partition Column From SELECT Query
September 17, 2019