Hive教程,beeline, hive cli
beeline -n hive -p hive -u jdbc:hive2://localhost:10000
-- SHOW语句,列出databases, tables, columns, partitions等信息
SHOW DATABASES [like 'de*'];
SHOW TABLES [like 'te*'];
SHOW COLUMNS FROM tab1;
SHOW FUNCTIONS [like 'tr*'];
SHOW PARTITIONS tabl1;
-- DESCRIBE语句,显示databases, tables, partitions元信息
DESCRIBE DATABASE default;
-- 显示表tab1的简单元信息
DESCRIBE tab1;
-- 显示表tab1的详细元信息
DESCRIBE EXTENDED tab1;
-- 显示表tab1格式化排版的详细元信息
DESCRIBE FORMATTED tab1;
-- 显示函数简单描述
DESCRIBE FUNCTION avg;
-- 显示函数详细描述
DESCRIBE FUNCTION EXTENDED avg;
DESCRIBE tab1 PARTITION (date='20180402')
CREATE DATABASE test_db COMMENT 'this is a test databases';
DROP DATABASE test_db;
ALTER DATABASE test_db SET DBPROPERTIES (property_name=property_value, ...);
USE test_db;
USE default;
-- 创建表
CREATE TABLE tab1(id INT, name VARCHAR(20), birthday DATE, address STRING) COMMENT 'comments';
-- Create Table As Select,通过select语句创建表
CREATE TABLE tab2 AS SELECT(id, name, birthday, address) FROM tab1;
-- Create Table Like,创建与tab1相同的表结构,但不复制数据
CREATE TABLE tab2 LIKE tab1;
CREATE TABLE IF NOT EXISTS tab1 (f1 int, f2 string, f3 double, f4 map<int, array<string>>)
PARTITIONED BY (date string)
CLUSTERED BY (f1) SORTED BY (f2) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
-- 修改表名
ALTER TABLE tab1 RENAME TO tab2;
-- 修改字段
ALTER TABLE tab1 CHANGE id person_id INT COMMENT 'this is person id';
-- 添加字段
ALTER TABLE tab1 ADD COLUMNS (email VARCHAR(50), ip STRING);
-- 删除表
DROP TABLE tab1;
-- 删除分区
ALTER TABLE DROP tab1 PARTITION (date='20180402');
-- 删除索引
DROP INDEX index1 ON tab1;
-- 显示函数简单描述
DESCRIBE FUNCTION avg;
-- 显示函数详细描述
DESCRIBE FUNCTION EXTENDED avg;
SQL Standard Based Hive Authorized
CREATE ROLE myrole;
DROP ROLE myrole;
SHOW CURRENT ROLES;
SHOW ROLES;