创建的表：
create table if not exists employee( name string comment 'employee name', salary float comment 'employee salary', subordinates array<string> comment 'names of subordinates', deductions map<string,float> comment 'keys are deductions values are percentages', address struct<street:string, city:string, state:string, zip:int> comment 'home address' ) comment 'description of the table' location '/user/hive/warehouse/mydb.db/employee' tblproperties ('creator'='yoona','date'='20160719');//注意：此处location放在tblproperties之前 //comment 为注释代码 //TBLPROPERTIES允许开发者定义一些自己的键值对信息。可以对TBLPROPERTIES进行查看和修改（部分可修改）。 在TBLPROPERTIES中有一些预定义信息，比如last_modified_user和last_modified_time，其他的一些预定义信息包括：
单分区 create table day_table(id int,content string) partitioned by(dt string) 查询分区： select day_table * from day where day_table.dt='2018-07-087' 分区就是为了缩小查询范围多分区 create table day_table(id int,content string) partitioned by(dt string,hour string); 分区的修改： 增加分区 alter table 表名 add partition(dt=‘2018-0808’) 删除分区： alter table 表名 drop partition(dt=‘2018-0808’) 分桶： 字段： cluster by 、sort by、distribute by 指定开启分桶 set hive.enforce.bucketing = true; set mapreduce.job.reduces=4; 创建分桶表： create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string) clustered by(Sno) sorted by(Sno DESC) into 4 buckets row format delimited fields terminated by ‘,’;
准备数据： 95001,李勇,男,20,CS 95002,刘晨,女,19,IS 95003,王敏,女,22,MA 95004,张立,男,19,IS 95005,刘刚,男,18,MA 95006,孙庆,男,23,CS 95007,易思玲,女,19,MA 95008,李娜,女,18,CS 95009,梦圆圆,女,18,MA 95010,孔小涛,男,19,CS 95011,包小柏,男,18,MA 95012,孙花,女,20,CS 95013,冯伟,男,21,CS 95014,王小丽,女,19,CS 95015,王君,男,18,MA 95016,钱国,男,21,MA 95017,王风娟,女,18,IS 95018,王一,女,19,IS 95019,邢小丽,女,19,IS 95020,赵钱,男,21,IS 95021,周二,男,17,MA 95022,郑明,男,20,MA 注： 1分桶表的个数：用户定义HSQL语句设置的reduceTask的个数决定，分桶表已经决定就不能修改，如果要改变分桶，需要重新插入分桶数据 2表的分区的个数：用户自定义的，可以由程序自定义生成，也可以动态增长 数据表现： 1分桶表中每个分桶数据可以有多个key值 2分区表中每个分区只有一个key 插入数据： select * from student cluster by(Sno) sort by(Sage); 报错,cluster 和 sort 不能共存 所以，用下面的： insert overwrite table t07 select * from t07_tmp cluster by(sno); select * from stu_buck tablesample (bucket 1 out of 2 on sno); 解析：查询第1桶、第（1+2）桶的数据，其中sno是之前按照sno进行分桶。 这里的“2”的位置，必须是分桶个数的整数倍或者因子 内外部表 创建内部表： 创建外部表： CREATE external TABLE test1(id INT, content STRING) ROW FORMAT delimited FIELDS TERMINATED BY ',' location '/stu'; 区别： 删除内部表时，会删除表和相关的元数据； 删除外部表时，会删除相关的元数据，但是那个目录不会删除修改表：
1.2． 修改表
增加分区： ALTER TABLE table_name ADD PARTITION (dt='20170101') location '/user/hadoop/warehouse/table_name/dt=20170101'; //一次添加一个分区 ALTER TABLE table_name ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809'; //一次添加多个分区 删除分区 ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2008-08-08'); ALTER TABLE table_name DROP IF EXISTS PARTITION (dt='2008-08-08', country='us'); 修改分区 ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808'); 添加列 ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name STRING); 注：ADD 是代表新增一个字段，新增字段位置在所有列后面(partition 列前) REPLACE 则是表示替换表中所有字段。 修改列 test_change (a int, b int, c int); ALTER TABLE test_change CHANGE a a1 INT; 修改 a 字段名 表重命名 ALTER TABLE table_name RENAME TO new_table_name显示命令
show tables; 显示当前数据库所有表 show databases |schemas; 显示所有数据库 show partitions table_name; 显示表分区信息，不是分区表执行报错 show functions; 显示当前版本 hive 支持的所有方法 desc extended table_name; 查看表信息 desc formatted table_name; 查看表信息（格式化美观） describe database database_name; 查看数据库相关信息like字段： create table t_t9 like t_t8; load字段 Load 在将数据加载到表中时， Hive 不会进行任何转换。 加载操作是将数据文件移动到与 Hive表对应的位置的纯复制/移动操作。 语法结构 LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]
说明： 1、 filepath 相对路径，例如：project/data1 绝对路径，例如：/user/hive/project/data1 完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1 filepath 可以引用一个文件（在这种情况下，Hive 将文件移动到表中） ，或 者它可以是一个目录 （在这种情况下， Hive 将把该目录中的所有文件移动到表中） 。 2、 LOCAL 如果指定了 LOCAL， load 命令将在本地文件系统中查找文件路径。 load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表 的位置属性决定。被复制的数据文件移动到表的数据对应的位置。 如果没有指定 LOCAL 关键字，如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。 否则：如果没有指定 schema 或者 authority，Hive 会使 用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。 3、 OVERWRITE 如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除， 然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。 如果目标表 （分区） 已经有一个文件， 并且文件名和 filepath 中的文件名冲突， 那么现有的文件会被新文件所替代动态分区插入
set hive.exec.dynamic.partition=true; #是否开启动态分区功能，默认false关闭。 set hive.exec.dynamic.partition.mode=nonstrict; #动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。需求： 将dynamic_partition_table中的数据按照时间(day)，插入到目标表d_p_t的相应分区中。
原始表：
create table dynamic_partition_table(day string,ip string)row format delimited fields terminated by ","; load data local inpath '/root/hivedata/dynamic_partition_table.txt' into table dynamic_partition_table; dynamic_partition_table.txt数据为： 2015-05-10,ip1 2015-05-10,ip2 2015-06-14,ip3 2015-06-14,ip4 2015-06-15,ip1 2015-06-15,ip2 目标表： create table d_p_t(ip string) partitioned by (month string,day string); 动态插入： insert overwrite table d_p_t partition (month,day) select ip,substr(day,1,7) as month,day from dynamic_partition_table;1: jdbc:hive2://node1:10000> select * from d_p_t; ±----------±-------------±------------±-+ | d_p_t.ip | d_p_t.month | d_p_t.day | ±----------±-------------±------------±-+ | ip1 | 2015-05 | 2015-05-10 | | ip2 | 2015-05 | 2015-05-10 | | ip3 | 2015-06 | 2015-06-14 | | ip4 | 2015-06 | 2015-06-14 | | ip1 | 2015-06 | 2015-06-15 | | ip2 | 2015-06 | 2015-06-15 | ±----------±-------------±------------±-+
查询结果导出到文件系统
3、将查询结果保存到指定的文件目录（可以是本地，也可以是hdfs） insert overwrite local directory ‘/home/hadoop/test’ select * from t_p;
insert overwrite directory ‘/aaa/test’ select * from t_p;
Select 基本的 Select 操作 语法结构 SELECT [ALL | DISTINCT] select_expr, select_expr, … FROM table_reference JOIN table_other ON expr [WHERE where_condition] [GROUP BY col_list [HAVING condition]] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY | ORDER BY col_list] ] [LIMIT number]
说明： 1、order by 会对输入做全局排序，因此只有一个 reducer，会导致当输入规模较大时， 需要较长的计算时间。
2、sort by 不是全局排序，其在数据进入 reducer 前完成排序。因此，如果用 sort by 进 行排序，并且设置 mapred.reduce.tasks>1，则 sort by 只保证每个 reducer 的输出有序，不保 证全局有序。
3、 distribute by(字段)根据指定字段将数据分到不同的 reducer， 分发算法是 hash 散列。
4、Cluster by(字段) 除了具有 Distribute by 的功能外，还会对该字段进行排序。 如果 distribute 和 sort 的字段是同一个时，此时，cluster by = distribute by + sort by
1分桶下载：insert overwrite local directory ‘/root/hivedata/aaa’ select * from t07_tmp cluster by(Sno);
select * from student order by sage asc 2分桶排序 insert overwrite local directory ‘/root/aaa888’ select * from student DISTRIBUTE by Sno sort by sage 3全局排序： select * from student order by sage asc
多表关联：
create table a(id int,name string) row format delimited fields terminated by ‘,’;
create table b(id int,name string) row format delimited fields terminated by ‘,’;
关于hive中的各种join
准备数据 1,a 2,b 3,c 4,d 7,y 8,u
2,bb 3,cc 7,yy 9,pp
导入数据 load data local inpath ‘/root/hivedata/a.txt’ into table a; load data local inpath ‘/root/hivedata/b.txt’ into table b;
select * from a cross join b;
cross join（##慎用） 返回两个表的笛卡尔积结果，不需要指定关联键。 ±------±--------±------±--------±-+ | a.id | a.name | b.id | b.name | ±------±--------±------±--------±-+ | 1 | a | 2 | bb | | 1 | a | 3 | cc | | 1 | a | 7 | yy | | 1 | a | 9 | pp | | 2 | b | 2 | bb | | 2 | b | 3 | cc | | 2 | b | 7 | yy | | 2 | b | 9 | pp | | 3 | c | 2 | bb | | 3 | c | 3 | cc | | 3 | c | 7 | yy | | 3 | c | 9 | pp | | 4 | d | 2 | bb | | 4 | d | 3 | cc | | 4 | d | 7 | yy | | 4 | d | 9 | pp | | 7 | y | 2 | bb | | 7 | y | 3 | cc | | 7 | y | 7 | yy | | 7 | y | 9 | pp | | 8 | u | 2 | bb | | 8 | u | 3 | cc | | 8 | u | 7 | yy | | 8 | u | 9 | pp | ±------±--------±------±--------±-+
内连接： select * from a inner join b on a.id=b.id; ±------±--------±------±--------±-+ | a.id | a.name | b.id | b.name | ±------±--------±------±--------±-+ | 2 | b | 2 | bb | | 3 | c | 3 | cc | | 7 | y | 7 | yy | ±------±--------±------±--------±-+
左连接： select * from a left join b on a.id=b.id; ±------±--------±------±--------±-+ | a.id | a.name | b.id | b.name | ±------±--------±------±--------±-+ | 1 | a | NULL | NULL | | 2 | b | 2 | bb | | 3 | c | 3 | cc | | 4 | d | NULL | NULL | | 7 | y | 7 | yy | | 8 | u | NULL | NULL | ±------±--------±------±--------±-+
右连接： select * from a right join b on a.id=b.id; ±------±--------±------±--------±-+ | a.id | a.name | b.id | b.name | ±------±--------±------±--------±-+ | 2 | b | 2 | bb | | 3 | c | 3 | cc | | 7 | y | 7 | yy | | NULL | NULL | 9 | pp | ±------±--------±------±--------±-+
全连接： select * from a full outer join b on a.id=b.id; ±------±--------±------±--------±-+ | a.id | a.name | b.id | b.name | ±------±--------±------±--------±-+ | 1 | a | NULL | NULL | | 2 | b | 2 | bb | | 3 | c | 3 | cc | | 4 | d | NULL | NULL | | 7 | y | 7 | yy | | 8 | u | NULL | NULL | | NULL | NULL | 9 | pp | ±------±--------±------±--------±-+
**hive中的特别join select * from a left semi join b on a.id = b.id; ±------±--------±-+ | a.id | a.name | ±------±--------±-+ | 2 | b | | 3 | c | | 7 | y | ±------±--------±-+ 相当于 select a.id,a.name from a where a.id in (select b.id from b); 在hive中效率极低
select a.id,a.name from a join b on (a.id = b.id);
自定义分割符 drop table t_bi_reg; create table t_bi_reg(id string,name string) row format serde ‘org.apache.hadoop.hive.serde2.RegexSerDe’ with serdeproperties( ‘input.regex’=’(.)\|\|(.)’, ‘output.format.string’=’%1 s s %2 ss’ ) stored as textfile; hive>load data local inpath ‘/root/hivedata/bi.dat’ into table t_bi_reg; hive>select * from t_bi_reg;
±-------------±---------------±-+ | t_bi_reg.id | t_bi_reg.name | ±-------------±---------------±-+ | zhangsan | 23 | | lisi | 45 | ±-------------±---------------±-+
联级求和： create table t_access_times(username string,month string,salary int) row format delimited fields terminated by ‘,’;
load data local inpath ‘/root/hivedata/t_access_times.dat’ into table t_access_times;
A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5
select * from t_access_times; ±-------------------------±----------------------±-----------------------±-+ | t_access_times.username | t_access_times.month | t_access_times.salary | ±-------------------------±----------------------±-----------------------±-+ | A | 2015-01 | 5 | | A | 2015-01 | 15 | | B | 2015-01 | 5 | | A | 2015-01 | 8 | | B | 2015-01 | 25 | | A | 2015-01 | 5 | | A | 2015-02 | 4 | | A | 2015-02 | 6 | | B | 2015-02 | 10 | | B | 2015-02 | 5 | ±-------------------------±----------------------±-----------------------±-+
1、第一步，先求个用户的月总金额 select username,month,sum(salary) as salary from t_access_times group by username,month tmp ±----------±---------±--------±-+ | username | month | salary | ±----------±---------±--------±-+ | A | 2015-01 | 33 | | A | 2015-02 | 10 | | B | 2015-01 | 30 | | B | 2015-02 | 15 | ±----------±---------±--------±-+
2、第二步，将月总金额表 自己连接 自己连接 select A.,B. FROM (select username,month,sum(salary) as salary from t_access_times group by username,month) A inner join (select username,month,sum(salary) as salary from t_access_times group by username,month) B on A.username=B.username where B.month <= A.month; ±------------±---------±----------±------------±---------±----------±-+ | a.username | a.month | a.salary | b.username | b.month | b.salary | ±------------±---------±----------±------------±---------±----------±-+ | A | 2015-01 | 33 | A | 2015-01 | 33 | | A | 2015-01 | 33 | A | 2015-02 | 10 | | A | 2015-02 | 10 | A | 2015-01 | 33 | | A | 2015-02 | 10 | A | 2015-02 | 10 | | B | 2015-01 | 30 | B | 2015-01 | 30 | | B | 2015-01 | 30 | B | 2015-02 | 15 | | B | 2015-02 | 15 | B | 2015-01 | 30 | | B | 2015-02 | 15 | B | 2015-02 | 15 | ±------------±---------±----------±------------±---------±----------±-+
注：此处有坑：注意空格 bug：Error: Error while compiling statement: FAILED: ParseException line 2:0 cannot recognize input near ‘select’ ‘username’ ‘,’ in join source (state=42000,code=40000)
3、第三步，从上一步的结果中 进行分组查询，分组的字段是a.username a.month 求月累计值： 将b.month <= a.month的所有b.salary求和即可 select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate from (select username,month,sum(salary) as salary from t_access_times group by username,month) A inner join (select username,month,sum(salary) as salary from t_access_times group by username,month) B on A.username=B.username where B.month <= A.month group by A.username,A.month order by A.username,A.month;
±------------±---------±--------±------------±-+ | a.username | a.month | salary | accumulate | ±------------±---------±--------±------------±-+ | A | 2015-01 | 33 | 33 | | A | 2015-02 | 10 | 43 | | B | 2015-01 | 30 | 30 | | B | 2015-02 | 15 | 45 | ±------------±---------±--------±------------±-+
