Hive常用语句
1Hive简介
Hive对我来说就是一个基于HDFS的数据仓库，它提供了一个种类SQL语言（和SQL标准基本一样又有一些特殊的地方不一样），能让不精通Java语言而熟悉SQL语言的工程师，快速的对HDFS或其他存储文件系统如Amazon，S3，上的数据进行数据分析，是Hadoop生态系统中非常重要的一个工具。对于大数据分析师而言，HiveQL则是必须要掌握的一个工具。
2.Hive常用语句
2.1菜鸟建表法
1.直接建表，指定分隔符，默认存储为text，也可以指定存储格式！
create table if not exists employees( nam STRING, salary FLOAT comment "income", subordinates ARRAY<STRING>, deductions MAP<STRING, FLOAT>, address STRUCT<street:STRING, city:STRING, state:STRING,zip:INT> ) row format delimited fields terminated by "," collection items terminated by "-" map keys terminated by "_" lines terminated by "\n" stored as textfile; --comment注释，collection items以"-"分割，即此建表语句中的ARRAY，map keys以"_"分割，行以"\n"分割，存储为text格式，也可以选则sequence 等，text是默认值。 --查看表结构： hive> desc employees; OK nam string salary float income subordinates array<string> deductions map<string,float> address struct<street:string,city:string,state:string,zip:int> Time taken: 0.153 seconds, Fetched: 5 row(s) --也可以用desc extended employees;或者desc formatted employees;来显示更加详尽冗余的表信息。
2从查询语句建表
--原表 hive> select * from tb151; OK 0 马文楷 29 1 王俊朋 28 2 李玉林 28 3 陈鹏辉 28 4 李春廷 28 5 白卫东 27
--通过查询语句建表
create table tb151_sample as select * from tb151 tablesample(5 rows);
hive> select * from tb151_sample;
OK
0 马文楷 29
1 王俊朋 28
2 李玉林 28
3 陈鹏辉 28
4 李春廷 28
--注：tablesample是表抽样函数，5 rows代表抽取前5行，也可以是百分比(50 percent)或按照内存抽样(10M)：
3根据已知表结构创建表
create table test_tb like tb151； hive> desc tb151; OK id string name string age int Time taken: 0.173 seconds, Fetched: 3 row(s) hive> desc test_like; OK id string name string age int Time taken: 0.164 seconds, Fetched: 3 row(s)
2.2菜鸟导入导出Hive数据的几种方法
1.装载数据
--1直接从本地导入表 load data local inpath "/path/to/data" overwrite into table tbname; --注：local指定本地目录，不加的话默认是HDFS目录，overwrite关键字直接覆盖原表数据 --2将查询语句结果导入表 insert overwrite table tbname partition(partname="ptname") select * from tbname2 where....; --注：overwrite关键字会覆盖元数据，使用into则追加数据
2.导出数据
--1如果数据文件恰好是需要的格式则直接从HDFS目录下载即可 hadoop fs -get /path --2通过查询语句 insert overwrite local directory "/path/to/data" select ... from tbname where...; --3通过shell命令行 /path/to/hive -e "select ...from tbname where...." >> /path/to/data.txt
2.3关于Join
一般情况下按一下规则进行join：
1.从左到右表由小到大
2或者显示的标记大表
select /*+STREAMTABLE(t)*/t.a,t1.b from t join t1 where……; --注：t表示大表
3小表join大表可以使用mapjoin将小表放到内存中
select /*+MAPJOIN（d）*/s.ymd,s.tmd from s join d on s.ymd=d.ymd; --注：d指的是小表
3.Hive集合之交并差
先看原始表：
hive> select * from tb151; OK 0 马文楷 29 1 王俊朋 28 2 李玉林 28 3 陈鹏辉 28 4 李春廷 28 5 白卫东 27
1.Hive并集可以用union实现
hive> select age from tb151 union > select age from tb151; Total MapReduce CPU Time Spent: 3 seconds 520 msec OK 27 28 29 Time taken: 36.328 seconds, Fetched: 3 row(s)
2.Hive的union all则是合并两个集合不去重
> select age from tb151 union all > select age from tb151; Total MapReduce CPU Time Spent: 1 seconds 290 msec OK 29 29 28 28 28 28 28 28 28 28 27 27 Time taken: 23.941 seconds, Fetched: 12 row(s)
3.Hive交集可以通过先A左关联B，然后得到的结果将右列不为NULL的元素去重即可
4差集左表A-右表B，先用Aleft joinB，然后得到的结果右列为NULL的左列元素去重即可
注：3，4可以先把做关联的结果存储为一个表，然后根据要求进行选择去重即可，咱们看一下关联结果就OK
hive> select * from tb151; OK 0 马文楷 29 1 王俊朋 28 2 李玉林 28 3 陈鹏辉 28 4 李春廷 28 5 白卫东 27 Time taken: 0.141 seconds, Fetched: 6 row(s) hive> select * from tb151_sample; OK 0 马文楷 29 1 王俊朋 28 2 李玉林 28 3 陈鹏辉 28 4 李春廷 28 hive> select t.age,t1.age from tb151 t left join tb151_sample t1 > on t.age=t1.age; Total MapReduce CPU Time Spent: 1 seconds 550 msec OK 29 29 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 28 27 NULL --注：两个表的结果交集很明显右列不为空的元素去重，差集左表-右表则是右列为空的元素去重即可
4.Hive的几种执行方式
--1执行单条语句 hive -e "select * from tbname" --2执行sql文件 hive -f /path/to/sqlfil --3可以在hive命令行通过dfs执行Hadoop命令 hive> dfs -ls /; Found 3 items drwxr-xr-x - root supergroup 0 2019-02-15 19:18 /test drwx-wx-wx - root supergroup 0 2019-02-12 18:23 /tmp drwxr-xr-x - root supergroup 0 2019-01-27 09:19 /user --4也可以在hive命令行通过！+命令执行shell的一些简单命令 hive> ! ls /wjp; data javacode pycode readDoc.py sparkcode udf
