林子雨hive实践教程错误笔记

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&amp;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;#分割多个字符的写法