hive.md

title	category	layout	tags	updated
Hive Commands	Hadoop	2017/sheet	Featured	2019-02-07

Hive DDL

Show databases

# show all databases
> SHOW databases;
# show current database being used
> SHOW current_database();

Alter database

# change a field in DBPROPERTIES
> ALTER DATABASE myhivebook SET DBPROPERTIES ('edited-by'='Dayong');
# change database owner
> ALTER DATABASE myhivebook SET OWNER user dayongd;
# change database location on HDFS
> ALTER DATABASE myhivebook SET LOCATION '/tmp/data/myhivebook';

Show & Describe tables

# Show all tables in database
> SHOW TABLES;
# Show tables name contains "sam"
> SHOW TABLES '*sam*';
# Show tables name contains "sam” or "lily"
> SHOW TABLES '*sam|lily*';
# List detailed table infofor all tables matching regex
> SHOW TABLE EXTENDED LIKE 'employee_*';
# show table properties
> SHOW TBLPROPERTIES employee_internal;
# show partitions of a table
> SHOW PARTITIONS employee;

# describe table in formatted way
> DESC FORMATTED table;
> DESC EXTENDED table;

# show columns of a table
> SHOW COLUMNS IN employees;

# Show create-table DDL statements for a table
> SHOW CREATE TABLE employees;

Table Alternation

# Rename a table
> ALTER TABLE cte_employee RENAME TO cte_employee_backup;
# Change the table properties with TBLPROPERTIES:
> ALTER TABLE c_employee SET TBLPROPERTIES ('comment'='New comments');
# Change the table's row format and SerDe
> ALTER TABLE employee_internal SET SERDEPROPERTIES ('field.delim' = '$');
# Change the table's file format
> ALTER TABLE c_employee SET FILEFORMAT RCFILE;
> ALTER TABLE c_employee SET FILEFORMAT ORC;
# Change the table's location, a full URI of HDFS, with LOCATION:
> ALTER TABLE c_employee SET LOCATION 'hdfs://localhost:9000/tmp/employee';

# Enable/Disable the table's protection; NO_DROP or OFFLINE.
# NO_DROP prevents a table from being dropped
> ALTER TABLE c_employee ENABLE NO_DROP;
> ALTER TABLE c_employee DISABLE NO_DROP;
# OFFLINE prevents data from being queried
> ALTER TABLE c_employee ENABLE OFFLINE;
> ALTER TABLE c_employee DISABLE OFFLINE;

# Enable concatenation on a ORC table
> ALTER TABLE c_employee CONCATENATE;

Column Alternation

# Change the column's data type, position (with AFTER or FIRST), and comment
> ALTER TABLE employee_internal
  CHANGE name employee_name string AFTER gender_age;
> ALTER TABLE employee_internal
  CHANGE employee_name name string COMMENT 'updated' FIRST;

# Add new columns to a table:
> ALTER TABLE c_employee ADD COLUMNS (work string);
# Replace all columns in a table using the new columns specified:
> ALTER TABLE c_employee REPLACE COLUMNS (name string);

Table Cleaning

# drop an internal table and move data to .trash in the current user directory
> DROP TABLE IF EXISTS employees_temp;

# truncate to only remove the data
> TRUNCATE TABLE employees_temp;

Create Table

# create an external table if not exist
> CREATE DATABASE IF NOT EXISTS mytable
  COMMENT 'hive database demo'
  LOCATION '/hdfs/directory/data'
  WITH DBPROPERTIES ('creator'='foo','date'='2018-05-01');

# create a partitioned table
> CREATE TABLE logs (ts BIGINT, line STRING)
  PARTITIONED BY (dt STRING);

# create table using another table schema
> CREATE TABLE new_table LIKE existing_table;

# create table as select (CTAS)
> CREATE TABLE ctas_employee as SELECT * FROM employee_external;

Temporary Tables

> CREATE TEMPORARY TABLE IF NOT EXISTS tmp_emp1 (
 name string,
 surname string,
 work_place ARRAY<string>,
);
# use CTAS to create temp table
> CREATE TEMPORARY TABLE tmp_emp2 AS SELECT * FROM employees;
# create temp table using another table schema
> CREATE TEMPORARY TABLE tmp_emp3 LIKE tmp_emp1;

Views

# Create a view
> CREATE VIEW users_subset
  as
  SELECT * FROM users WHERE....;
# verify details of a view
> DESCIBE EXTENDED users_subset;