Hive基础语句

1.数据库操作 (1)创建数据库 为避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

create database if not exists myhivebook;

(2)使用数据库

use myhivebook;

(3)修改数据库 用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

alter database myhivebook set dbproperties('createtime'='20191230');

(4)查询数据库 ①显示数据库

show databases; --显示数据库 show databases like 'myhivebook*'; --过滤显示查询的数据库

②查看数据库详情

desc database myhivebook; --显示数据库信息 desc database extended myhivebook; --显示数据库详细信息

(5)删除数据库 ①删除空数据库

drop database myhivebook;

②如果删除的数据库不存在，最好采用 if exists判断数据库是否存在

drop database if exists myhivebook;

③如果数据库不为空，可以采用cascade命令，强制删除

drop database myhivebook cascade;

2.数据表操作 (1)字段释义 ①字段基本定义

CREATE TABLE --创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常;用户可以用 IF NOT EXIST 选项来忽略这个异常 EXTERNAL --关键字可以让用户创建一个外部表 COMMENT --可以为表与字段增加描述 PARTITIONED BY --分区 CLUSTERED BY --数据汇总 SORTED BY --按某列排序 BUCKETS --分桶 设置词句分桶才有效: set hive.enforce.bucketing=true; STORED AS --存储为不同文件格式 [textfile] --text [sequencefile] --序列化文件 [rcfile] LOCATION --在建表的同时指定一个指向实际数据的路径

②数据编码格式 按字段分割

row format delimited fields terminated by '\001' --行格式分隔的字段终止于 collection items terminated by '\002' --集合项终止于 map keys terminated by '\003' --# 映射键终止于 lines terminated by '\n' --# 行终止于

(2)内部表 ①创建内部表

create table if not exists student( id int, name string ) row format delimited fields terminated by '\t' stored as textfile location '/opt/soft/hive110/warehouse/student'; --hive.metastore.warehouse.dir 指定的目录 create table if not exists student(id int, name string); --内部表简写,属性为默认

②查询表的类型

desc formatted student;

(3)外部表 ①创建外部表(可以用来连接hdfs)

create external table if not exists emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int) row format delimited fields terminated by '\t';

②创建hive表的时候导入hdfs上的文件(该方法hdfs上的文件不消失)

create external table if not exists emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int) row format delimited fields terminated by '\t' location '/hive/emp';

③创建表结构用load data从hdfs上导入数据到hive(该方法hdfs上的文件会消失)

load data inpath '/hive/emp/emp.csv' overwrite into table emp;

④本地向外部表中导入数据

load data local inpath '/opt/datas/emp.txt' into table emp;

⑤外部表数据导入本地

insert overwrite local directory '/opt/txt/emp' select * from emp; --hive里操作 hive -e "select * from myhivebook.emp" >> /opt/emp/emp.txt; --liunx上操作(指定格式的导入)

⑥外部表数据导入到hdfs

insert overwrite directory '/hive/emp/emp.txt' select * from emp;

(4)其他建表方式 ①CTAS CTAS不能创建partition, external, bucket table

CREATE TABLE ctas_employee as SELECT * FROM employee;

②CTE

CREATE TABLE cte_employee AS WITH r1 AS (SELECT name FROM r2 WHERE name = 'Michael'), r2 AS (SELECT name FROM employee WHERE sex_age.sex= 'Male'), r3 AS (SELECT name FROM employee WHERE sex_age.sex= 'Female') SELECT * FROM r1 UNION ALL SELECT * FROM r3;

③like

CREATE TABLE employee_like LIKE employee;

(4)创建临时表 临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法 ①表只对当前session有效，session退出后自动删除 ②表空间位于/tmp/hive-<user_name>(安全考虑) ③如果创建的临时表表名已存在，实际用的是临时表

CREATE TEMPORARY TABLE tmp_table_name1 (c1 string); CREATE TEMPORARY TABLE tmp_table_name2 AS.. CREATE TEMPORARY TABLE tmp_table_name3 LIKE..

(5)修改表(Alter针对元数据) ①重命名

ALTER TABLE employee RENAME TO new_employee;

②修改列名

ALTER TABLE employee_internal CHANGE old_name new_name STRING;

③增加列

ALTER TABLE c_employee ADD COLUMNS (work string);

④替换列

ALTER TABLE c_employee REPLACE COLUMNS (name string);

(6)删除表 ①直接删除

DROP TABLE IF EXISTS employee;

②清空表数据

TRUNCATE TABLE employee;

3.分区表 (1)静态分区 ①创建静态分区

create table dept_partition( deptno int, dname string, loc string ) partitioned by (month string) row format delimited fields terminated by '\t';

②添加静态分区

alter table dept_partition add partition(month='201906') ; alter table dept_partition add partition(month='201905') partition(month='201904');

③删除静态分区

alter table dept_partition drop partition (month='201904'); alter table dept_partition drop partition (month='201905'), partition (month='201906');

④查看分区表有多少分区

show partitions dept_partition;

⑤加载数据到分区表中

load data local inpath '/opt/datas/dept.txt' into table dept_partition partition(month='201909');

⑥创建多级分区

create table dept_partition2( deptno int, dname string, loc string) partitioned by (month string, day string) row format delimited fields terminated by '\t'; oad data local inpath '/opt/datas/dept.txt' into table dept_partition2 partition(month='201909', day='13'); --加载数据到二级分区表中

(2)动态分区 ①开启分区

set hive.exec.dynamic.partition=true; --开区分区 set hive.exec.dynamic.partition.mode=nonstrict; --设置分区为非严格模式

②调优参数

set hive.exec.max.dynamic.partitions.pernode=100 --表示每个maper或reducer可以允许创建的最大动态分区个数，默认是100，超出则会报错。(默认是100) set hive.exec.max.dynamic.partitions =1000 --表示一个动态分区语句可以创建的最大动态分区个数，超出报错 set hive.exec.max.created.files =10000 --全局可以创建的最大文件个数，超出报错

③创建动态分区

create table dynamic_people( id int, age string, start_date string ) partitioned by (year string,month string) row format delimited fields terminated by '\t';

④动态分区插入数据

insert into dynamic_people partition(year,month) select id,name,age,start_date,year(start_date),month(start_date) from people; select * from dynamic_people where year = 2018; --查询数据

4.分桶表 (1)设置分桶开关 此设置为临时设置，一旦退出会话终端，再打开就会恢复默认设置 false

set hive.enforce.bucketing=true;

(2)创建分桶表

create table test_bucket( id int, name string, age int ) clustered by (age) into 4 buckets row format delimited fields terminated by '|';

(3)插入数据到分桶表中

insert into table test_bucket select id, name, age from people;