This guide covers installation and setup of Hadoop, Hive, and PostgreSQL as a Hive Metastore on Ubuntu.

1. Install and Configure Hadoop

Download Hadoop:

https://hadoop.apache.org/releases.html
wget https://dlcdn.apache.org/hadoop/common/hadoop-3.4.0/hadoop-3.4.0.tar.gz
tar -xvzf hadoop-3.4.0.tar.gz
mv hadoop-3.4.0 ~/hadoop

Update Hadoop Configuration

• Edit hadoop-env.sh → set JAVA_HOME:

export JAVA_HOME=/usr/lib/jvm/java-17-openjdk-amd64/

• Edit core-site.xml:

<configuration>
  <property>
    <name>fs.default.name</name>
    <value>hdfs://localhost:9745</value>
  </property>
  <property>
    <name>hadoop.tmp.dir</name>
    <value>/home/sthithapragna/hadoop/tmp</value>
  </property>
</configuration>

• Edit hdfs-site.xml (NameNode + DataNode paths):

<configuration>
  <property>
    <name>dfs.name.dir</name>
    <value>file:/home/sthithapragna/hdfs/namenode</value>
  </property>
  <property>
    <name>dfs.data.dir</name>
    <value>file:/home/sthithapragna/hdfs/datanode</value>
  </property>
  <property>
    <name>dfs.replication</name>
    <value>1</value>
  </property>
</configuration>

• Edit mapred-site.xml:

<configuration>
  <property>
    <name>mapreduce.framework.name</name>
    <value>yarn</value>
  </property>
</configuration>

• Edit yarn-site.xml:

<property>
  <name>yarn.web-proxy.address</name>
  <value>localhost:9099</value>
</property>

Update .bashrc

export HADOOP_HOME=/home/sthithapragna/hadoop
export PATH=$PATH:$HADOOP_HOME/sbin:$HADOOP_HOME/bin
export JAVA_HOME=/usr/lib/jvm/java-17-openjdk-amd64
export HADOOP_CONF_DIR=$HADOOP_HOME/etc/hadoop
export HADOOP_YARN_HOME=$HADOOP_HOME
export HADOOP_COMMON_HOME=$HADOOP_HOME
export HADOOP_HDFS_HOME=$HADOOP_HOME
export HADOOP_MAPRED_HOME=$HADOOP_HOME

Enable SSH for Hadoop

sudo apt update
sudo apt install openssh-server -y
sudo systemctl enable ssh
sudo systemctl start ssh
ssh-keygen -t rsa
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
chmod 755 ~/.ssh/authorized_keys
ssh localhost

Format & Start Hadoop

hdfs namenode -format
$HADOOP_HOME/sbin/start-dfs.sh
$HADOOP_HOME/sbin/start-yarn.sh

2. Install and Configure Hive

Download and extract Hive:

wget https://dlcdn.apache.org/hive/hive-4.1.0/apache-hive-4.1.0-bin.tar.gz
tar -zxvf apache-hive-4.1.0-bin.tar.gz
mv apache-hive-4.1.0-bin ~/hive

Update Hive configuration:

cd ~/hive/conf
mv hive-default.xml.template hive-site.xml
mv hive-env.sh.template hive-env.sh

Edit hive-site.xml (temporary Derby DB config):

<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:derby:;databaseName=/home/sthithapragna/hive/metastore/metastore_db;create=true</value>

Update .bashrc

export HIVE_HOME=/home/sthithapragna/hive
export PATH=$PATH:$HIVE_HOME/bin
export HIVE_CONF_DIR=$HIVE_HOME/conf

Prepare HDFS directories

hdfs dfs -mkdir -p /user/hive/warehouse /tmp/hive
hdfs dfs -chmod -R 777 /user/hive /tmp/hive

3. Configure PostgreSQL as Hive Metastore

Install PostgreSQL:

sudo apt install postgresql -y
sudo systemctl status postgresql

Create Hive Metastore user + DB:

sudo -i -u postgres
psql
CREATE USER psqluser WITH PASSWORD 'sthithapragna';
CREATE DATABASE pymetastore OWNER psqluser;
\q

Edit pg_hba.conf to allow local connections (change auth to trust):

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust

Update Hive to use PostgreSQL

Edit hive-site.xml:

<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:postgresql://localhost:5432/pymetastore</value>

<name>javax.jdo.option.ConnectionDriverName</name>
<value>org.postgresql.Driver</value>

<name>javax.jdo.option.ConnectionUserName</name>
<value>psqluser</value>

<name>javax.jdo.option.ConnectionPassword</name>
<value>sthithapragna</value>

Initialize Hive Metastore

• Download PostgreSQL JDBC driver: PostgreSQL JDBC
• Copy driver to Hive lib:

cp postgresql-42.7.2.jar ~/hive/lib/
schematool -dbType postgres -initSchema --verbose

Start Hive Services

$HIVE_HOME/bin/hive --service metastore 1>$HIVE_HOME/logs/metastore.out 2>$HIVE_HOME/logs/metastore.log &
$HIVE_HOME/bin/hiveserver2 1>$HIVE_HOME/logs/hive.out 2>$HIVE_HOME/logs/hive.log &

Connect with Beeline

beeline --verbose=true -u "jdbc:hive2://127.0.0.1:10000/default"

4. Handy Queries in PostgreSQL

• List Hive databases:

SELECT "NAME", "DB_LOCATION_URI" FROM "DBS";

• Show all tables in a database (example: smoke_test_db):

SELECT "TBL_NAME", "TBL_TYPE"
FROM "TBLS" t
JOIN "DBS" d ON t."DB_ID"=d."DB_ID"
WHERE d."NAME"='smoke_test_db';

• Show columns of a table:

SELECT c."COLUMN_NAME", c."TYPE_NAME"
FROM "COLUMNS_V2" c
JOIN "SDS" s ON c."CD_ID"=s."CD_ID"
JOIN "TBLS" t ON s."SD_ID"=t."SD_ID"
JOIN "DBS" d ON t."DB_ID"=d."DB_ID"
WHERE d."NAME"='smoke_test_db' AND t."TBL_NAME"='your_table';

✅ You now have Hadoop, Hive, and PostgreSQL Metastore configured on Ubuntu 🚀