1.注意最后要加上这一句话,没有这句话,就会报错。
alter database hive set dbproperties('edited-by'='hadoop');#为hive设置键值对属性
hive-site.xml的正确配置方式应加上&useSSL=false,否则会出现warning提示。其中&是&在HTML中对应的转义符。
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value>
<description>password to use against metastore database</description>
</property>
</configuration>
2.创建内部表修正。
create table if not exists hive.usr(
name string comment 'username',
pwd string comment 'password',
address struct<street:string,city:string,state:string,zip:int> comment 'home address',
identify map<int,tinyint> comment 'number,sex')
comment 'description of the table'
tblproperties('creator'='me','time'='2019.1.4');
3.设置分区部分错误及修正。
alter table usr3 add if not exists
partition(city="beijing",state="China")
location '/usr/local/hive/warehouse/usr3/China/beijing'; #为表3增加一个分区,表2没有分区属性
alter table usr3 partition(city="beijing",state="China")
set location 'hdfs://localhost:9000/usr/local/hive/warehouse/usr3/China/beijing';#注意location前面要加上绝对路径
alter table usr3 partition(city="beijing",state="China") set fileformat sequencefile; #修改存储属性
alter table usr3 drop if exists partition(city="beijing",state="China");#注意英文标点
4.注释
alter table usr change column pwd password string after address;#指定位置,修改之后后添加在address列的后面。
alter table usr replace columns(uname string); #把所有的列删除,并替换成这个
5.case ...when ..then
select id,name,case when id=1 then 'first' when id=2 then 'second' else 'third' end from stu; #注意最后的end from stu不能少。
6.标准 SQL 的子查询支持嵌套的 select 子句,HiveQL 对子查询的支持很有限,只能在from 引导的子句中出现子查询。
7.hive实现词频统计多测试及实例注释
create table word_count1 as
select word, count(1) as count_word from
(select explode(split(line,' '))as word from docs) wuchangfa
group by word
order by word;
create table word_count2 as
select count(1) as count_word from
(select explode(split(line,' '))as word from docs) wuchangfa;
#hive explode函数可以将一个array或者map展开,其中explode(array)使得结果中将array列表里的每个元素生成一行;explode(map)使得结果中将map里的每一对元素作为一行,key为一列,value为一列。
create table word_count3 as
select word, count(1) as count_word from
(select explode(split(line,' |,|\\?|\\.'))as word from docs) wuchangfa
group by word
order by word;#分割多个字符的写法