Hive sql 基础操作

shangjg3

已于 2023-10-26 17:06:20 修改

阅读量56

点赞数

分类专栏： Hive 文章标签： hive sql hadoop

于 2023-08-03 11:38:41 首次发布

本文链接：https://blog.csdn.net/shangjg03/article/details/132080142

版权

Hive 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1 数据库操作

# 创建数据库
CREATE DATABASE IF NOT EXISTS hive_test  COMMENT 'hive database for test'  WITH DBPROPERTIES ('create'='admin');

# 查看数据库信息
DESC DATABASE  EXTENDED hive_test;

# 删除数据库
DROP DATABASE IF EXISTS hive_test CASCADE;

2 创建表

# 内部表
CREATE TABLE emp(    empno INT,    ename STRING,    job STRING,    mgr INT,    hiredate TIMESTAMP,    sal DECIMAL(7,2),    comm DECIMAL(7,2),    deptno INT)    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

# 外部表
CREATE EXTERNAL TABLE emp_external(    empno INT,    ename STRING,    job STRING,    mgr INT,    hiredate TIMESTAMP,    sal DECIMAL(7,2),    comm DECIMAL(7,2),    deptno INT)    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"    LOCATION '/hive/emp_external';

# 分区表 -- 按照部门编号进行分区
CREATE EXTERNAL TABLE emp_partition(    empno INT,    ename STRING,    job STRING,    mgr INT,    hiredate TIMESTAMP,    sal DECIMAL(7,2),    comm DECIMAL(7,2)    )    PARTITIONED BY (deptno INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"    LOCATION '/hive/emp_partition';

# 分桶表 --按照员工编号散列到四个 bucket 中
CREATE EXTERNAL TABLE emp_bucket(    empno INT,    ename STRING,    job STRING,    mgr INT,    hiredate TIMESTAMP,    sal DECIMAL(7,2),    comm DECIMAL(7,2),    deptno INT)    CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS      ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"    LOCATION '/hive/emp_bucket';

# 倾斜表 通过指定一个或者多个列经常出现的值（严重偏斜），Hive会自动将涉及到这些值的数据拆分为单独的文件。在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。

CREATE EXTERNAL TABLE emp_skewed(    empno INT,    ename STRING,    job STRING,    mgr INT,    hiredate TIMESTAMP,    sal DECIMAL(7,2),    comm DECIMAL(7,2)    )    SKEWED BY (empno) ON (66,88,100)  --指定 empno 的倾斜值 66,88,100    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"    LOCATION '/hive/emp_skewed';

# 临时表
临时表仅对当前session可见，临时表的数据将存储在用户的暂存目录中，并在会话结束后删除。如果临时表与永久表表名相同，则对该表名的任何引用都将解析为临时表，而不是永久表。临时表还具有以下两个限制：不支持分区列,不支持创建索引。
CREATE TEMPORARY TABLE emp_temp(    empno INT,    ename STRING,    job STRING,    mgr INT,    hiredate TIMESTAMP,    sal DECIMAL(7,2),    comm DECIMAL(7,2)    )    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

# 从查询语句的结果创建表
CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';

# 复制表结构
CREATE TEMPORARY EXTERNAL TABLE  IF NOT EXISTS  emp_co  LIKE emp;

# 加载数据到表
7369    SMITH    CLERK    7902    1980-12-17 00:00:00    800.00        
207499    ALLEN    SALESMAN    7698    1981-02-20 00:00:00    1600.00    300.00    
307521    WARD    SALESMAN    7698    1981-02-22 00:00:00    1250.00    500.00    
307566    JONES    MANAGER    7839    1981-04-02 00:00:00    2975.00        
207654    MARTIN    SALESMAN    7698    1981-09-28 00:00:00    1250.00    1400.00    
307698    BLAKE    MANAGER    7839    1981-05-01 00:00:00    2850.00        
307782    CLARK    MANAGER    7839    1981-06-09 00:00:00    2450.00        
107788    SCOTT    ANALYST    7566    1987-04-19 00:00:00    1500.00        
207839    KING    PRESIDENT        1981-11-17 00:00:00    5000.00        
107844    TURNER    SALESMAN    7698    1981-09-08 00:00:00    1500.00    0.00    
307876    ADAMS    CLERK    7788    1987-05-23 00:00:00    1100.00        
207900    JAMES    CLERK    7698    1981-12-03 00:00:00    950.00        
307902    FORD    ANALYST    7566    1981-12-03 00:00:00    3000.00        
207934    MILLER    CLERK    7782    1982-01-23 00:00:00    1300.00        10

load data local inpath "/home/emp.txt" into table emp;

3. 修改表

# 重命名表
ALTER TABLE table_name RENAME TO new_table_name;
# 修改列
-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT; 
-- 修改字段 sal 的名称 并将其放置到 empno 字段后
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2)  AFTER ename;
-- 为字段增加注释
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';

# 新增列
ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');

4 清空表

-- 清空整个表或表指定分区中的数据
TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value,  ...)];

目前只有内部表才能执行 TRUNCATE 操作，外部表执行时会抛出异常 Cannot truncate non-managed table XXXX。
TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);

5 删除表

删除表语法：
DROP TABLE [IF EXISTS] table_name [PURGE];
内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
外部表：只会删除表的元数据，不会删除 HDFS 上的数据；
删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）。