1
2
3
4
5
6
7
8
9
# Launch Hive CLI (legacy - use Beeline instead)
hive

# Run Hive script
hive -f script.hql

# Execute inline query
hive -e "SELECT * FROM table LIMIT 10;"

1
2
3
4
5
6
7
8
9
# Connect to Hive server
beeline -u jdbc:hive2://localhost:10000 -n username

# Run script via Beeline
beeline -u jdbc:hive2://localhost:10000 -f script.hql

# Connect with Kerberos
beeline -u "jdbc:hive2://localhost:10000/default;principal=hive/_HOST@REALM"

1
2
3
4
5
6
7
8
9
10
11
12
-- Create database (DATABASE and SCHEMA are synonyms)
CREATE DATABASE IF NOT EXISTS my_database;

-- Switch database
USE my_database;

-- List databases
SHOW DATABASES;

-- Drop database
DROP DATABASE IF EXISTS my_database CASCADE;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Create managed (ACID) table - default in Hive 3+ is transactional ORC
CREATE TABLE employees (
    id INT,
    name STRING,
    salary DOUBLE,
    hire_date DATE
) STORED AS ORC
TBLPROPERTIES ('transactional'='true');

-- Create external table
CREATE EXTERNAL TABLE log_data (
    ts STRING,
    level STRING,
    message STRING
)
STORED AS TEXTFILE
LOCATION '/path/to/log/files';

1
2
3
4
5
6
7
8
9
10
11
-- ORC (Optimized Row Columnar) - preferred for managed/ACID tables
CREATE TABLE orc_table (...) STORED AS ORC
TBLPROPERTIES ("orc.compress"="ZSTD");

-- Parquet (Columnar storage)
CREATE TABLE parquet_table (...) STORED AS PARQUET;

-- Avro (Schema-based)
CREATE TABLE avro_table (...)
STORED AS AVRO;

1
2
3
4
5
6
7
8
-- Load local file
LOAD DATA LOCAL INPATH '/local/path/data.csv'
INTO TABLE my_table;

-- Load from HDFS
LOAD DATA INPATH '/hdfs/path/data.csv'
INTO TABLE my_table;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Insert single row
INSERT INTO TABLE employees
VALUES (1, 'John Doe', 50000.00, '2023-01-01');

-- Insert from another table
INSERT INTO TABLE target_table
SELECT * FROM source_table
WHERE condition;

-- Multi-table insert
FROM source_table s
INSERT INTO TABLE high_value SELECT * WHERE s.amount > 1000
INSERT INTO TABLE low_value  SELECT * WHERE s.amount <= 1000;

1
2
3
4
5
6
7
8
-- Requires transactional table
UPDATE employees SET salary = salary * 1.05 WHERE department = 'ENG';
DELETE FROM employees WHERE hire_date < '2010-01-01';
MERGE INTO target t
USING updates u ON t.id = u.id
WHEN MATCHED THEN UPDATE SET t.amount = u.amount
WHEN NOT MATCHED THEN INSERT VALUES (u.id, u.amount);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Basic select
SELECT id, name, salary
FROM employees
WHERE salary > 50000;

-- Aggregations
SELECT
    department,
    AVG(salary) AS avg_salary,
    COUNT(*)    AS employee_count
FROM employees
GROUP BY department;

-- Ordering
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10;

-- Common Table Expression (CTE)
WITH top_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) FROM top_earners GROUP BY department;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Inner Join
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- Left Outer Join
SELECT e.name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_id = d.id;

-- Subquery (correlated and non-correlated supported)
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- Map-side / broadcast join hint
SELECT /*+ MAPJOIN(d) */ e.name, d.department_name
FROM employees e JOIN departments d ON e.dept_id = d.id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Create partitioned table
CREATE TABLE sales (
    id INT,
    amount DOUBLE
)
PARTITIONED BY (year INT, month STRING);

-- Add partition
ALTER TABLE sales
ADD PARTITION (year=2023, month='January');

-- Dynamic partitioning
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE sales PARTITION (year, month)
SELECT id, amount, year, month FROM staging_sales;

1
2
3
4
5
6
7
-- Create bucketed table
CREATE TABLE users (
    id INT,
    name STRING
)
CLUSTERED BY (id) INTO 10 BUCKETS;

1
2
3
4
5
6
7
8
9
10
11
12
-- Permanent UDF
CREATE FUNCTION my_uppercase
AS 'com.example.MyUppercaseUDF'
USING JAR 'hdfs:///path/to/udf.jar';

-- Temporary UDF (session-scoped)
CREATE TEMPORARY FUNCTION my_uppercase
AS 'com.example.MyUppercaseUDF';

-- Use UDF
SELECT my_uppercase(name) FROM employees;

1
2
3
4
5
6
7
8
9
10
-- Rank employees by salary in each department
SELECT
    name,
    salary,
    department,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    LAG(salary)  OVER (PARTITION BY department ORDER BY hire_date)   AS prev_salary
FROM employees;

1
2
3
4
-- Vectorization is enabled by default in Hive 3+
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled=true;

1
2
3
4
5
6
7
8
-- CBO is enabled by default in Hive 3+; keep stats fresh
SET hive.cbo.enable=true;
SET hive.compute.query.using.stats=true;

-- Refresh statistics
ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE TABLE employees COMPUTE STATISTICS FOR COLUMNS;

1
2
3
4
-- Sub-second interactive queries via persistent daemons
SET hive.llap.execution.mode=all;
SET hive.execution.engine=tez;

1
2
3
4
5
6
7
8
CREATE MATERIALIZED VIEW mv_dept_totals AS
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

-- CBO can automatically rewrite queries to use MVs
SET hive.materializedview.rewriting=true;