1、创建表：
hive>create table pokes(foo int,bar string);
hive>create table new_tb like pokes; 创建一个一样的表结构
2、创建分区表：
Hive>create table logs(ts bigint,line string) partitioned by (dt string,country string);
3、加载分区表数据：
hive> load data local inpath '/home/hadoop/input/hive/partitions/file1' into table logs partition (dt='2001-01-01',country='GB');
4、展示表中有多少分区：
Hive>show partitions logs;
5、展示所有表：
Hive>show tables;
Hive>show tables ‘.*s’;
6、显示表的结构信息：
Hive>describe invites;
7、更改表的名字：
Hive>alter table source rename to terget;
8、添加新一列：
Hive>alter table invites add columns(new_col2 int comment ‘a comment’);
9、删除表：
Hive>drop table records;
删除表中数据，但要保持表的结构定义：
Hive>dfs -rmr /usr/hive/warehouse/records;
10、从本地文件加载数据：
hive> load data local inpath '/home/hadoop/input/ncdc/micro-tab/sample.txt' overwrite into table records;
11、显示所有函数
Hive>show functions;
12、查看函数用法
Hive>describe function substr;
13、查看数组，map，结构
Hive> select col1[0],col2[‘b’],col3.c from complex;
14、内连接
Hive>select sale.*,thing.* from sale join thing on (sale.id=thing.id);
15、查看hive为某个查询使用多少个mapreduce作业
Hive>explain select sale.*,thing.* from sale join thing on (sale.id=thing.id);
16、外连接
Hive> select sale.*,thing.* from sale left outer join thing on (sale.id=thing.id);
Hive> select sale.*,thing.* from sale right outer join thing on (sale.id=thing.id);
Hive> select sale.*,thing.* from sale full outer join thing on (sale.id=thing.id);
17、in查询：hive不支持，但是可以使用left semi join
Hive>select * from thing left semi join sale on(sale.id=thing.id);
18、map连接：hive可以把较小的表放在每个mapper的内存来执行连接操作
Hive>select /*+mapjoin(thing)*/ sale.*,thing.* from sale join thing on (sale.id=thing.id);
19、insert overwrite table … select :新表预先存在
Hive>from record2
>insert overwrite table stations_by_year select year,count(distinct station) group by year
> insert overwrite table records_by_year select year,count(1) group by year
> insert overwrite table good_records_by_year select year,count(1) where temperature!=999 and (quality=0 or quality=1 or quality=4) group by year;
20、create table … as select :新表预先不存在
Hive>create table target as select sol1,col2 from source;
21、创建视图
Hive>create view valid_records as select * from records2 where temperature!=999;
22、查看试图详细信息
Hive>describe extended valid_records;
Create Table语句的一些注意项：
（1）CREATE TABLE创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用IF NOT EXIST选项来忽略这个异常。
（2）EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（ LOCATION ），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）LIKE允许用户复制现有的表结构，但是不复制数据。
（4）用户在建表的时候可以自定义SerDe或者使用自带的 SerDe （ Serialize/Deserilize 的简称，目的是用于序列化和反序列化 ）。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
（5）如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用STORED AS SEQUENCE。
（6）有分区的表可以在创建的时候使用 PARTITIONED B Y语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行CLUSTERED BY操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY对数据进行排序。这样可以为特定应用提高性能。
（7）表名和列名不区分大小写，SerDe和属性名区分大小写。表和列的注释是字符串。
Alter Table语句：主要功能包括Add Partitions, Drop Partitions, Rename Table, Change Column, Add/Replace Columns。
Create View语句：创建视图。格式CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ]
Show语句：Show tables; Show partitions; describe查看表结构。
Load语句：HIVE装载数据时没有做任何转换，加载到表中的数据只是进入相应的配置单元表的位置。Load操作只是单纯的复制/移动操作，将数据文件移动到Hive表对应的位置。
Insert语句：插入数据。Hive不支持一条一条的用 insert 语句进行插入操作，这个应该是与hive的storage layer是有关系的，因为它的存储层是HDFS，插入一个数据要全表扫描，还不如用整个表的替换来的快些。Hive也不支持update的操作。数据是以load的方式，加载到建立好的表中。数据一旦导入，则不可修改。要么drop掉整个表，要么建立新的表，导入新的数据。
Drop语句：删除一个内部表的同时会同时删除表的元数据和数据。删除一个外部表，只删除元数据而保留数据。
Limit子句：可以限制查询的记录数。查询的结果是随机选择的。下面的查询语句从 t1 表中随机查询5条记录，SELECT * FROM t1 LIMIT 5。
Top K查询：下面的查询语句查询销售记录最大的 5 个销售代表。
SET mapred.reduce.tasks = 1
SELECT * FROM sales SORT BY amount
DESC LIMIT 5
正则表达式使用：SELECT语句可以使用正则表达式做列选择，下面的语句查询除了ds和h 之外的所有列：
SELECT `(ds|hr)?+.+` FROM sales
SELECT语句：查询数据。
Group by, Order by, Sort by子句：聚合可进一步分为多个表，甚至发送到 Hadoop 的 DFS 的文件（可以进行操作，然后使用HDFS的utilitites）。可以用hive.map.aggr控制怎么进行汇总。默认为为true，配置单元会做的第一级聚合直接在MAP上的任务。这通常提供更好的效率，但可能需要更多的内存来运行成功。
Join语句：连接操作。一些注意事项：
（1）Hive只支持等值连接（equality joins）、外连接（outer joins）和（left/right joins）。Hive不支持所有非等值的连接，因为非等值连接非常难转化到map/reduce任务。
（2）Hive 支持多于2个表的连接。
（3）join 时，每次 map/reduce 任务的逻辑： reducer 会缓存 join 序列中除了最后一个表的所有表的记录， 再通过最后一个表将结果序列化到文件系统。这一实现有助于在reduce端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。
（4）LEFT，RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况。
（5）LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。Hive 当前没有实现 IN/EXISTS 子查询，所以你可以用 LEFT SEMI JOIN 重写你的子查询语句。LEFT SEMI JOIN的限制是， JOIN子句中右边的表只能在ON子句中设置过滤条件，在WHERE子句、SELECT子句或其他地方过滤都不行。
使用HIVE注意点
（1）字符集
Hadoop和Hive都是用UTF-8编码的，所以, 所有中文必须是UTF-8编码, 才能正常使用。
备注：中文数据load到表里面,，如果字符集不同，很有可能全是乱码需要做转码的，但是hive本身没有函数来做这个。
（2）压缩
hive.exec.compress.output 这个参数，默认是false，但是很多时候貌似要单独显式设置一遍，否则会对结果做压缩的，如果你的这个文件后面还要在hadoop下直接操作，那么就不能压缩了。
（3）count(distinct)
当前的Hive不支持在一条查询语句中有多Distinct。如果要在Hive查询语句中实现多Distinct，需要使用至少n+1条查询语句（n为distinct的数目），前n条查询分别对n个列去重，最后一条查询语句对n个去重之后的列做Join操作，得到最终结果。
（4）JOIN
只支持等值连接
（5）DML操作
只支持INSERT/LOAD操作，无UPDATE和DELTE
（6）HAVING
不支持HAVING操作。如果需要这个功能要嵌套一个子查询用where限制
（7）子查询
Hive不支持where子句中的子查询
（8）Join中处理null值的语义区别
SQL标准中，任何对null的操作（数值比较，字符串操作等）结果都为null。Hive对null值处理的逻辑和标准基本一致，除了Join时的特殊逻辑。这里的特殊逻辑指的是，Hive的Join中，作为Join key的字段比较，null=null是有意义的，且返回值为true。
（9）分号字符
分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：
select concat(cookie_id,concat(';',’zoo’)) from c02_clickstat_fatdt1 limit 2;
FAILED: Parse Error: line 0:-1 cannot recognize input '<EOF>' in function
specification
可以推断，Hive解析语句的时候，只要遇到分号就认为语句结束，而无论是否用引号包含起来。
解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成：
select concat(cookie_id,concat(' 73','zoo')) from c02_clickstat_fatdt1 limit
2;
为什么是八进制ASCII码？我尝试用十六进制的ASCII码，但Hive会将其视为字符串处理并未转义，好像仅支持八进制，原因不详。这个规则也适用于其他非SELECT语句，如CREATE TABLE中需要定义分隔符，那么对不可见字符做分隔符就需要用八进制的ASCII码来转义。
（10）Insert
根据语法Insert必须加“OVERWRITE”关键字，也就是说每一次插入都是一次重写。
其他优化：
1).Left Semi Join实现in/exists子查询：
SELECT A.* FROM A LEFT SEMI JOIN B ON(A.KEY = B.KEY AND B.KEY > 100);
等同于SELECT A.* FROM A WHERE A.KEY IN(SELECT B.KEY FORM B
WHERE B.KEY > 100);
作用：map端用group by减少流入reduce端的数据量
2).Bucket Map Join:
set hive.optimize.bucketmapjoin = true;
和Map join一起工作；
所有join的表都做列分桶，同时大表桶的数量是小表桶的整数倍；
做bucket的列必须是join的列；
SELECT /*+MAPJOIN(a,c)*/ a.*, b.*,
c.*
a join b on a.key = b.key
join c on a.key=c.key;
在现实的生产环境中，会有成百上千个buckets；
3).Skew join:
join时数据倾斜，造成Reduce端OOM
set hive.optimize.skewjoin = true;
set hive.skewjoin.key = 阀值;
当JOIN得到的map超过阀值时，将内存中的a-k1/b-k1数据分别存入hdfs中,然后遍历完后再对hdfs上的两块数据做Map Join,和其它key一起组成最后结果。
Hive查询进
通过hive提供的order by子句可以让最终的输出结果整体有序。但是因为Hive是基于hadoop之上的，要生成这种整体有序的结果，就必须强迫Hadoop只利用一个Reduce来完成处理。这种方式的副作用就是回降低效率。如果你不需要最终结果整体有序，你就可以使用sort by子句来进行排序。这种排序操作只保证每个Reduce的输出是有序的。如果你希望某些特定行被同一个Reduce处理，则你可以使用distribute by子句来完成。【Hive中select top N的问题。】
Hive> select subject,score,student from score where dt='2012' orderby subject,socre desc ;
使用distribute by + sort by。distribute by可以让相同科目的成绩记录发送到同一个reducer,而sort by可以在reducer端对记录做排序。
Hive> select subject,score,student from score where dt='2012' distribute by subject sort by subject asc, socre desc；
连接join
内链接inner join
外连接left/right/full outer join
半连接 left semi join
Map连接（map join）：
当一个表非常小，足以直接装载到内存中去时，可以使用map连接以提高效率，比如：Select /*+mapjoin(teacher) */ a.stuNo,b.teacherNamefrom student a join teacher b on a.classNo = b.classNo;
以上红色标记部分采用了C的注释风格。当连接时用到不等值判断时，也比较适合Map连接。具体原因需要深入了解Hive和MapReduce的工作原理。
Top N
开窗函数：row_number() over(partition by col_a order by col_b) rank
select product,category,revenue from(
select product,category,revenue,
row_number() over(partition by catrgory order by revenue desc)rank
from mytable)t
where t.rank<=3;