Apache Hive

Connection

hive
Start the Hive CLI shell

beeline -u jdbc:hive2://hostname:10000/databasename -n username -p password
Connect to HiveServer2 using Beeline client

beeline -u jdbc:hive2://
Connect to local HiveServer2 instance

beeline
Start Beeline CLI without connecting

Database Operations

SHOW DATABASES;
List all databases

CREATE DATABASE dbname;
Create a new database

USE dbname;
Switch to a database

DROP DATABASE dbname;
Delete a database

DESCRIBE DATABASE dbname;
Show database details

Table Operations

SHOW TABLES;
List all tables in current database

DESCRIBE table_name;
Show table structure

DESCRIBE EXTENDED table_name;
Show detailed table metadata

CREATE TABLE table_name (col_name data_type);
Create a new table

CREATE EXTERNAL TABLE table_name (col_name data_type) LOCATION '/path/to/data';
Create external table pointing to data

DROP TABLE table_name;
Delete a table

TRUNCATE TABLE table_name;
Remove all rows from a table

ALTER TABLE table_name ADD COLUMNS (col_name data_type);
Add new columns to a table

ALTER TABLE table_name RENAME TO new_table_name;
Rename a table

Data Querying

SELECT * FROM table_name;
Query all columns from a table

SELECT col1, col2 FROM table_name WHERE condition;
Select specific columns with filter

SELECT * FROM table_name LIMIT 10;
Query a limited number of rows

SELECT COUNT(*) FROM table_name;
Count rows in a table

SELECT DISTINCT col_name FROM table_name;
Get unique values from a column

Data Manipulation

LOAD DATA LOCAL INPATH 'file_path' INTO TABLE table_name;
Load data from local file system

LOAD DATA INPATH 'hdfs_path' INTO TABLE table_name;
Load data from HDFS

INSERT INTO table_name SELECT * FROM another_table;
Insert data from another table

INSERT OVERWRITE TABLE table_name SELECT * FROM source_table;
Replace all data in a table

CREATE TABLE new_table AS SELECT * FROM source_table;
Create table from query result

Partitioning

CREATE TABLE table_name (col_name data_type) PARTITIONED BY (partition_col data_type);
Create a partitioned table

ALTER TABLE table_name ADD PARTITION (partition_col='value');
Add a partition to a table

SHOW PARTITIONS table_name;
List all partitions of a table

DROP PARTITION (partition_col='value');
Delete a partition

MSCK REPAIR TABLE table_name;
Repair table metadata for partitions

Performance

SET hive.execution.engine=tez;
Set execution engine to Tez

SET hive.execution.engine=mr;
Set execution engine to MapReduce

SET hive.execution.engine=spark;
Set execution engine to Spark

SET mapred.reduce.tasks=10;
Set number of reducer tasks

EXPLAIN SELECT * FROM table_name;
Show query execution plan

Configuration

SET;
Show all configuration parameters

SET hive.cli.print.header=true;
Enable column headers in output

SET -v;
Show all Hive and Hadoop configuration parameters

RESET;
Reset all configuration to default values

User-Defined Functions

SHOW FUNCTIONS;
List all available functions

DESCRIBE FUNCTION function_name;
Show details of a function

ADD JAR /path/to/jar;
Add JAR containing UDF to classpath

CREATE TEMPORARY FUNCTION func_name AS 'com.example.MyUDF';
Create a UDF