🐝A.5) Setting Up and Running Apache Hive on AWS EMR and Databricks

 

Setting Up and Running Apache Hive on AWS EMR and Databricks

1. SQL Databases and Standards

  • All SQL databases follow a common standard called SQL standards.
  • Limited only to processing structured data. SQL is used for structured (tabular) data.
  • Row-oriented scanning: Traditional databases process data row by row (OLTP systems).
  • OLTP (Online Transaction Processing): Used for real-time transaction-based processing (e.g., banking, e-commerce).
  • OLAP (Online Analytical Processing): Used for complex analytical queries (e.g., reporting, business intelligence).

2. Next-Generation Database & Hive Overview

  • Hive Query Language (HQL): Hive uses HQL, similar to SQL, but designed for big data processing.
  • Hive supports only OLAP queries (like SELECT, GROUP BY, JOIN, functions, etc.).
  • 90% syntax is similar to SQL, but not 100% the same.

3. Hive Data Processing & File Storage

  • Data must be stored in a folder, not as a single file.
  • ✅ Correct: s3://bucket/input/asldata/ (folder containing multiple CSVs with the same schema)
  • ❌ Incorrect: s3://bucket/input/asldata/asl.csv (single file)

4. Hive Configuration & Settings

  • Hive configuration file: hive-site.xml (stores all Hive settings).
  • To enable column headers in Hive CLI: 
    set hive.cli.print.header=true;
  • To change the Hive execution engine to Tez (for better performance): 
    set hive.execution.engine=tez;
  • To make settings permanent, modify hive-site.xml: 
    whereis hive 
/usr/lib/hive/conf/hive-site.xml

5. Hive in Development vs. Production

Development & Testing:

  • Use .hql script files.
  • Example: hqlscript.hql

Production:

  • Use automation tools like:
    • Shell scripts
    • Oozie
    • Apache Airflow
    • Control-M
    • Autosys

6. Setting Up Hive on AWS EMR (Recommended for Big Data Processing)

Step 1: Create an AWS EMR Cluster

  1. Log in to AWS Console → Navigate to Amazon EMR.
  2. Click Create Cluster.
  3. Select Advanced Options.
  4. Choose the following applications:
    • Hadoop
    • Hive
    • Spark
  5. Select an instance type (e.g., m5.xlarge for better performance).
  6. Set the number of nodes to 1 (for testing) or 3+ (for production).
  7. Enable SSH access (Port 22) in Security Groups.
  8. Click Create Cluster and wait for it to start.

Step 2: Connect to EMR via SSH

Once the cluster is running:

  1. Open a terminal on Linux/macOS or Git Bash on Windows.
  2. Connect to the master node using SSH: 
    ssh -i your-key.pem hadoop@<master-node-public-dns>
    • Replace your-key.pem with your AWS Key Pair.
    • Replace <master-node-public-dns> (find it in AWS EMR under Master Node details).

Step 3: Run Hive Commands in EMR

After logging in:

  1. Start Hive: 
    hive
  2. Run Hive queries: 
    CREATE DATABASE mydb;
USE mydb;
CREATE TABLE test_table (id INT, name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
SHOW TABLES;
  3. Exit Hive when done: 
    exit;

7. Using Databricks to Run Hive Queries

Step 1: Create a Databricks Workspace

  1. Log in to Databricks (Azure or AWS).
  2. Click Create Workspace.
  3. Create a Cluster with a Databricks Runtime that supports Hive.

Step 2: Run Hive Queries in Databricks

  1. Open a Databricks Notebook.
  2. Use the %sql magic command before running SQL queries: 
    %sql
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE test_table (id INT, name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
SHOW TABLES;
  3. Run the notebook to see results.

8. Connecting Hive from Local CMD Using Beeline

If you want to run Hive commands from your local machine without installing Hive, you can connect to AWS EMR using beeline.

Step 1: Install Beeline Locally

  • On Windows/Linux, install Apache Hive Client.
  • Add Hive to the system PATH.

Step 2: Connect Beeline to AWS EMR

Run this command:

beeline -u jdbc:hive2://<master-node-public-dns>:10000
  • Replace <master-node-public-dns> with your EMR Master Node Public DNS.
  • Now you can execute Hive queries from your local terminal.

9. Comparison: AWS EMR vs. Databricks vs. Beeline

Option Best For
AWS EMR Running Hive on a Hadoop cluster for big data processing
Databricks Running Hive queries in an interactive cloud notebook
Beeline (Local CMD) Running Hive commands locally without full installation

10. Conclusion

Apache Hive is a great tool for processing large-scale datasets. If you don’t have Hive installed locally, you can set it up using AWS EMR or Databricks. Additionally, you can use Beeline to run Hive queries from your local machine without needing a full installation. Choose the setup that best fits your needs and start querying big data efficiently!

💡 Next Steps: Try these steps and let me know if you have any questions! Please comment 🚀

Comments

Post a Comment

Popular posts from this blog

🔥Apache Spark Architecture with RDD & DAG

  Apache Spark Architecture with RDD & DAG Apache Spark follows a master-slave architecture, designed for fast, distributed data processing. It consists of three main components: Driver Node (Master) Cluster Manager Executors (Workers) Additionally, two important internal components play a crucial role in execution: RDD (Resilient Distributed Dataset) DAG (Directed Acyclic Graph) 1. Driver Node (Master) The Driver Node is responsible for coordinating and executing a Spark application. Responsibilities of the Driver: Starts a SparkSession (entry point of Spark). Divides the program into tasks and schedules them. Sends tasks to executors for execution. Monitors task execution and collects results. Working of the Driver Node: User submits a Spark job using spark-submit . Driver requests resources from the Cluster Manager. The job is converted into a DAG (Directed Acyclic Graph). The DAG is split into Stages, and each Stage contains Tasks. The Cluster Man...
Read more

🌐Filtering and Copying Files Dynamically in Azure Data Factory (ADF)

Image
  Filtering and Copying Files Dynamically in Azure Data Factory (ADF) Introduction In Azure Data Factory (ADF) , automating file processing is a common use case. This guide demonstrates how to: Retrieve file metadata (including filenames inside a folder). Filter files based on today’s date. Apply advanced filter conditions such as contains() , startswith() , and endswith() . Copy only filtered files dynamically using ForEach and Copy Data activities. Step 1: Set Variable for Today's Date The first step is to create a Set Variable activity to store today’s date in ddMMyyyy format. Activity: Set Variable Variable Name: dt Value: @formatDateTime(utcNow(), 'ddMMyyyy') Explanation utcNow() fetches the current UTC time. formatDateTime(utcNow(), 'ddMMyyyy') converts it into a day-month-year format. Example Output: { "name": "dt", "value": "28022025" } Step 2: Get Metadata of Folder Contents ...
Read more

🌐End-to-End ETL Pipeline: MS SQL to MS SQL Using Azure Databricks

Understanding Databricks and Azure Databricks 1. Introduction What is Databricks? Databricks is a cloud-based platform optimized for big data processing and machine learning. It is built on Apache Spark , an open-source distributed computing system known for its speed and efficiency in handling large datasets. What is Azure Databricks? Azure Databricks is a Microsoft Azure cloud service that enhances Databricks by integrating it with Azure services. It provides enterprise-level scalability, security, and AI capabilities for data engineering, machine learning, and analytics. 2. Key Features Features of Databricks: Unified Platform : Combines data engineering, machine learning, and analytics. Optimized Performance : Enhances Apache Spark for better speed. Scalability : Handles large-scale data efficiently. Multiple Editions : Community Edition (Free) : Best for learning and small experiments. Paid Version : Offers enterprise support, additional features, and scalability. Feat...
Read more