DataSphere

 Please keep below points in mind before creating a table in HIVE. HIVE is not a database but it is a data warehouse which is designed for data analytics(OLAP) HIVE doesn't have any internal storage to store data, it will use HDFS to store table data and RDBMS(like MySQL) to store schema related information. Hence we need to have a RDBMS like MySQL. When we create a database, HIVE will create a folder in HDFS. Later when we create a HIVE table, it will create another folder inside database folder. After creating database, table, once we load/insert data into it then that data will be stored as a file in HDFS and schema information will be stored in MySQL. Delimiter is very important while loading the data, we will see some examples below. As this is analytics, we know what type of data is there(as data is historical) and we will create schema based on the data. Based on the data, we need to create a table. HOW TO CREATE DATABASE ----------------------------- CREATE DATABASE IF NOT ...

HIVE is a Data warehouse, it is not a database.  Like how important it is to understand when to use a particular tool, it is equally important to understand when NOT to use it. HIVE is designed for only analytical operations in large scale, it is not a good fit for transactional operations. HIVE data is totally de-normalized. HIVE supports JOINS but need to avoid them as much as we can to improve performance.  HIVE query language HQL is similar to SQL. Lets understand the relation between Hadoop and HIVE : HDFS is having folders and files HIVE have databases and tables When we create a database in HIVE, it will create a folder in HDFS When we create a table in HIVE, it will create a folder in HDFS When we insert records in HIVE table, those records will be saved in HDFS in the form of files Delimiter is very important while creating a table in HIVE Delimiter can be a Comma , Tab etc., HIVE can store structured , semi-structured & un-structured data but it is important t...

A person sailing in a boat who know swimming will always be in a safe side comparatively with other person who doesn't know how to swim. Guys, we all know that we are using Cloud based platforms like AWS Athena for working on HIVE these days, but it is very important to understand the basics of HIVE, like what is HIVE, how to install it, what are the installation modes, HIVE tables, concepts involved in it to format the data, serDe etc.,  Most of the time, already implemented HIVE serDe's are good enough for practical use cases but what if we land into a situation where we have to write our own serDe in Java? Hence, it is good to have this knowledge for a Data engineer though we are using UI in cloud to perform same activities. Hence let's learn HIVE in depth and have this knowledge with us to sail further.  What is HIVE ? Apache Hive is a data warehouse software project. It is built on top of Apache Hadoop for providing data query and analysis. Hive gives an SQL-like inter...

What is Athena ? Amazon Athena is a serverless, interactive query service that allows users to analyze data stored in Amazon Simple Storage Service(S3). To make it more clear, it is a kind of workbench for working on HIVE queries in more visual way.  AWS took open source Apache HIVE and modified it and released their own product called Athena. Home page of Athena looks as below(login to AWS and type Athena under search) : Once you are in Athena home page, it will show a prompt to launch it on right hand side, click launch to open Athena editor(as shown in above screenshot). Also, before start working on Athena, we need to set up a query result location in Amazon S3. Let's understand why we do this, if you remember, HIVE doesn't have a storage, we need a relational database to store schema information and HDFS storage to store table data(records). HIVE is just a processing layer on the top of Hadoop.  Similarly, even in Athena, we need to set up a query result location in Amazo...