Setting up Apache Hive on your Machine

This is the third post of this series where we are setting up our machines to get started with learning big data. In the first post, we have installed and configured Hadoop on our Macbook and now we will install Apache Hive and use the same Hadoop cluster to save data in HDFS. Let's get started then.

Prerequisites


Installation

We will be installing Hive 1.2.2, so we need to update the brew formula (as we did for Hadoop in the previous article). Use brew edit hive to open the formula and edit it to have these values:

url "https://www.apache.org/dyn/closer.cgi?path=hive/hive-1.2.2/apache-hive-1.2.2-bin.tar.gz"
sha256 "763b246a1a1ceeb815493d1e5e1d71836b0c5b9be1c4cd9c8d685565113771d1"



Once you have updated the formula, install hive using brew install hive

Modify Hive Configurations

First things first, we need to edit the .bash_profile file. The hive installation directory is /usr/local/Cellar/hive/1.2.2, so update the .bash_profile file accordingly.


Setup for the Hive metastore - MySQL

We will configure a MySQL instance for the hive metastore. To do that, we need to setup mysql on our machine. Then we will setup JDBC connector to connect to the mysql instance. A prerequisite is to have wget installed on your machine. If it is not already installed, use brew install wget to install wget. Then follow these steps:


$ brew install mysql
$ mkdir ~/mysql/jars
$ cd ~/mysql/jars
$ wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.46.tar.gz
$ tar -zxvf mysql-connector-java-5.1.46.tar.gz
$ cd mysql-connector-java-5.1.46
$ sudo cp mysql-connector-java-5.1.46 /Library/Java/Extensions/
$ sudo chown kautukp:admin /Library/Java/Extensions/mysql-connector-java-5.1.46.jar

Start MySQL service

Start the MySQL service by running mysqld

As a test, try to connect to the instance by firing mysql -u root -p



Configure the metastore

We need to create a database which will be used for the metastore. We also need a user which will be used by Hive to connect to this DB. Follow these steps to do this:


> create database hive;
> use hive;
> create user 'kautukp'@'localhost';
> set password for 'kautukp'@'localhost' = 'your_password_here';
> grant all on hive.* to 'kautukp'@'localhost' ;
> flush privileges;

Update hive-site.xml

We need to configure hive to use the above mysql instance for the metastore. Go to the hive configuration directory. Go to the conf directory in HIVE_HOME. Create a new file with the name hive-site.xml. Add the following (after changing your folders, username, password etc.):


<configuration>
  <!-- WARNING!!! This file is auto generated for documentation purposes ONLY! -->
  <!-- WARNING!!! Any changes you make to this file will be ignored by Hive.   -->
  <!-- WARNING!!! You must make your changes in hive-site.xml instead.         -->
  <!-- Hive Execution Parameters -->
<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://localhost/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>kautukp</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>enter_your_password</value>
</property>
<property>
  <name>datanucleus.fixedDatastore</name>
  <value>false</value>
</property>
<property>
  <name>hive.exec.scratchdir</name>
  <value>/tmp/hive</value>
</property>
<property>
  <name>hive.exec.local.scratchdir</name>
  <value>/Users/kautukp/Documents/learning/hive/tmpData</value>
</property>
<property>
  <name>hive.downloaded.resources.dir</name>
  <value>/Users/kautukp/Documents/learning/hive/tmpData</value>
</property>
<property>
  <name>hive.querylog.location</name>
  <value>/Users/kautukp/Documents/learning/hive/tmpData</value>
</property>
<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>/apps/hive/warehouse</value>
</property>
<property>
  <name>hive.metastore.uris</name>
  <value>thrift://localhost:9083</value>
</property>
<property>
  <name>hive.cli.print.current.db</name>
  <value>true</value>
</property>
<property>
  <name>hive.cli.print.header</name>
  <value>true</value>
</property>

Starting Hive Services

To start and run hive, you need to start two services - metastore service and hiveserver (in that order, mind it).

$ nohup $HIVE_HOME/bin/hive --service metastore
$ nohup $HIVE_HOME/bin/hiveserver2

Using Hive CLI

Start the hive shell by running hive

Run some hql commands to test everything out.


Hive Query Timeout issue

In case you are facing timeout issues on your newly installed Hive setup, it is because you might not have set YARN properties yet. YARN is the resource manager for this Hadoop setup and it controls how many mappers and reducers get assigned to any Hive query fired. To fix this, go to your $HADOOP_CONF_DIR and edit yarn-site.xml. Add the following values in the file and restart your services (start-yarn.sh)


<property>
  <name>yarn.nodemanager.disk-health-checker.max-disk-utilization-per-disk-percentage</name>
  <value>90.0</value>
</property>
<property>
  <name>yarn.nodemanager.resource.memory-mb</name>
  <value>12288</value>
</property>
<property>
  <name>yarn.scheduler.minimum-allocation-mb</name>
  <value>256</value>
</property>

<property>
  <name>yarn.scheduler.maximum-allocation-mb</name>
  <value>6144</value>
</property>
<property>
  <name>yarn.scheduler.minimum-allocation-vcores</name>
  <value>1</value>
</property>
<property>
  <name>yarn.scheduler.maximum-allocation-vcores</name>
  <value>4</value>
</property>
<property>
  <name>yarn.nodemanager.resource.cpu-vcores</name>
  <value>6</value>
</property>

And you are done! Quite straightforward, right?


In the next post, we will build on this progress and use this Hadoop and Hive instance to run Presto.

Previous post: Install Hadoop                                     Next post: Install Presto

Comments

  1. cadyeyachMarch 3, 2022 at 1:46 PM

    Casino Games Near Me | Mapyro
    Find the best casinos near you, located in Denver, Colorado. Mapyro offers a 부산광역 출장안마 complete 전라북도 출장안마 inventory of gaming, entertainment, 강원도 출장마사지 and dining options 제주도 출장마사지 in the 대전광역 출장안마 state.

    ReplyDelete

Post a Comment

Popular posts from this blog

Uber Data Model

Image
In this post I will try to come up with a data model which can serve the requirements of ride sharing companies like Uber, Lyft, Ola etc. We will approach the problem as an interview and see how we can come up with a feasible data model by answering important questions. Important Entities The first step towards building a data model is to identify important actors/ entities involved in the process. In our case, if we think about our interaction with taxi apps, we can identify important entities involved. The user (i.e. Rider) is one such entity, so is the Driver/ Partner . Once we open the app, we try to book a trip by finding a suitable taxi/ cab from a particular location to another . After the trip gets finished, the app collects the payment and we are done . Ideally, the flow continues to reviews/ ratings, helpcenter in case of issues etc. but for this post we will only consider scenarios till the ride gets finished. So, to summarize, we have the following key entities;
Read more

Data Engineer Interview Questions: SQL

Image
In this post, I will try to share some actual questions asked by top companies for Data Engineer positions. A lot of these companies will cover data modelling as one of the rounds and will use the data model for the next round based on SQL queries. Q1: Find the number of drivers available for rides in any area at any given point of time. Q2: Do you consider Driver and Rider as separate entities? Why or why not? Q3: Give me all passenger names who used the app for only airport rides. Q4: How will you decide where to apply surge pricing? Q5: How will you calculate wait times for rides? Q6: A driver can ride multiple cars, how will you find out who is driving which car at any moment? Q7: Find out Rank without using any function. Q8: How will you delete duplicates from a table? Q9: How will you find percentile? Q10:  You have 3 tables, user_dim (user_id, account_id), account_dim (account_id, paying_customer), and dload_facts (date, user_id, and downloads), find the ave
Read more

Cracking Data Engineering Interviews

Of late, a lot of people have asked me for tips on how to crack Data Engineering interviews at FAANG (Facebook, Amazon, Apple, Netflix, Google) or similar companies. I have not worked at all of these companies so I can't share tips which will necessarily apply for all of them but I will share tips which can be generalized for most of the big companies. First of all, the field of Data Engineering has expanded a lot in the last few years and has become one of the core functions of any big technology company. The obvious reason for this expansion is the amount of data being generated by devices and data-centric economy of the internet age. Each company is focussed on making the best use of data owned by them by making data driven decisions. If you compare this to the Data Engineering roles which used to exist a decade back, you will see a huge change. In the past, Data Engineering was invariably focussed on Databases and SQL. Even now, these two form some part of most Data Engin
Read more

Hive Challenges: Bucketing, Bloom Filters and More

In the second post of this series, we will learn about few more aspects of table design in Hive. Now that you know about partitioning challenges , you will be able to appreciate these features which will help you to further tune your Hive tables. Bucketing In addition to Partitioning the tables, you can enable another layer of bucketing of data based on some attribute value by using the Clustering method. Clustering can be used with partitioned or non-partitioned hive tables. One particular use case where Clustering becomes useful when your partitions might have unequal number of records (e.g. users logging in per country, US partition might be a lot bigger than New Zealand). In such cases, you can define the number of buckets and the clustered by field (like user Id), so that all the buckets have equal records. In partitioning each partition gets a directory while in Clustering, each bucket gets a file. Records with the same bucketed column will always be stored in the same bucke
Read more

UBER Data Architecture

Image
In this post, I will try to cover the data architecture Uber has built to support their big data applications. This should be applicable for other ride hailing apps as well. There are multiple modules at play here so I will try to give a brief overview along with a detailed discussion on the data architecture.  An aeroplane view of the problem tells us that effectively we are trying to solve a demand vs. supply problem. All the Drivers active at any point of time constitute the supply while all the cab requesters (Riders) form the demand. What Uber tries to do is to have the best matching between demand and supply "at any point at any moment". I have highlighted the location and temporal conditions as they are critical for the success of what Uber tries to do. Let's get started to see how Uber comes around these challenges by creating a scalable data architecture. Every Driver active on Uber keeps sending his location data to the server (e.g. every 5 seconds). T
Read more