大数据基本操作锦集之Hive的基本操作(二)

哈喽，大家好，我是汉斯老师。近几年来，互联网行业由于较高的薪资收入，受到许多人的追捧。很多年轻的学子，或是其他行业的有志青年，都想要投身到这个行业中来。然而一方面受到“互联网寒冬”的影响，最近频频传出各家知名互联网公司裁员缩编的消息；另一方面，大量的人才涌入，又使得互联网产业在职场上呈现出供过于求的特征，并最终导致了职场上的激烈竞争。那么互联网行业未来的潜力在哪里？我们又应该在哪个方向上发力，才能保证自己可以获得一份高薪而稳定的工作？基于多年的互联网行业从业经验，汉斯老师在这里向大家推荐大数据方向，希望通过本人的《三分钟入门大数据》和《十分钟精通大数据》等系列文章，帮助所有有志了解学习大数据的朋友掌握大数据的相关知识，也欢迎所有对互联网行业以及大数据领域感兴趣的朋友前来交流。

前文请参看这里：https://www.jianshu.com/p/f886c1c17ea1

hive 加载数据

1.加载数据

- 本地数据源：

/home/hadoop/sample.txt hadoop@data2:~$ vim sample.txt

1201 Gopal 45000 Technical manager

1202 Manisha 45000 Proof reader

1203 Masthanvali 40000 Technical writer

1204 Kiran 40000 Hr Admin

1205 Kranthi 30000 Op Admin

- 从本地加载数据

hive> LOAD DATA LOCAL INPATH '/home/hadoop/sample.txt' OVERWRITE INTO TABLE employee;

Loading data to table test.employee Table test.employee stats: [numFiles=1, numRows=0, totalSize=201, rawDataSize=0] OK Time taken: 0.513 seconds

- 从hdfs上面加载数据：

hive> LOAD DATA INPATH '/home/hadoop/sample.txt' OVERWRITE INTO TABLE employee;

- 使用hadoop 命令：'hadoop fs -put /home/hadoop/sample.txt /user/hive/warehouse/test.db/employee/'

- 查看数据： hive> select * from employee;

OK

1201 Gopal 45000 Technical

1202 Manisha 45000 Proof

1203 Masthanvali 40000 Technical writer

1204 Kiran 40000 Hr

1205 Kranthi 30000 Op Time taken: 0.07 seconds, Fetched: 5 row(s)

- 查看在hdfs上路径：

hadoop@data2:~$ hadoop fs -ls /user/hive/warehouse/test.db SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/software/hadoop-2.6.0-cdh5.9.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/software/hbase-1.2.0-cdh5.9.0/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] Found 2 items drwxr-xr-x - hadoop supergroup 0 2017-05-15 12:43 /user/hive/warehouse/test.db/emp drwxr-xr-x - hadoop supergroup 0 2017-05-15 13:10 /user/hive/warehouse/test.db/employee

- 导出到hdfs上来：去掉local

hive> insert overwrite directory '/home/hadoop/emp' > select * from emp;

- 查看目录： hadoop@data2:~/emp$ hadoop fs -ls /home/hadoop/emp SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/software/hadoop-2.6.0-cdh5.9.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/software/hbase-1.2.0-cdh5.9.0/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] Found 1 items -rwxr-xr-x 3 hadoop supergroup 330 2017-05-15 15:36 /home/hadoop/emp/000000_0

- 使用hadoop命令：hadoop fs -get /user/hive/warehouse/test.db/emp/* /home/hadoop/hive

2.hive导出数据

1.导出到本地：

1.1. 命令

hive> insert overwrite local directory '/home/hadoop/emp' > select * from emp;

Query ID = hadoop_20170515153232_366cdc86-2146-423b-ab07-18779323edb6 Total jobs = 1 Launching Job 1 out of 1 Number of reduce tasks is set to 0 since there's no reduce operator Starting Job = job_1492396415914_1296, Tracking URL = http://data1.XXXXXX.cn:8088/proxy/application_1492396415914_1296/ Kill Command = /software/hadoop-2.6.0-cdh5.9.0/bin/hadoop job -kill job_1492396415914_1296 Hadoop job information for Stage-1: number of mappers: 1;

number of reducers: 0 2017-05-15 15:32:18,465 Stage-1 map = 0%, reduce = 0% 2017-05-15 15:32:23,584 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.23 sec MapReduce Total cumulative CPU time: 1 seconds 230 msec Ended Job = job_1492396415914_1296 Copying data to local directory /home/hadoop/emp MapReduce Jobs Launched: Stage-Stage-1: Map: 1 Cumulative CPU: 1.23 sec HDFS Read: 3583 HDFS Write: 330 SUCCESS Total MapReduce CPU Time Spent: 1 seconds 230 msec OK Time taken: 9.642 seconds

1.2. 查看目录：

hadoop@data2:~$ cd /home/hadoop/emp/ hadoop@data2:~/emp$ ll total 16

drwxrwxr-x 2 hadoop hadoop 4096 May 15 15:32 ./

drwxr-xr-x 15 hadoop hadoop 4096 May 15 15:32 ../

-rw-r--r-- 1 hadoop hadoop 330 May 15 15:32 000000_0

-rw-r--r-- 1 hadoop hadoop 12 May 15 15:32 .000000_0.crc hadoop@data2:~/emp$ vim 000000_0

1201^A Gopal^A45000^A Technical^Amanager 1202^AManisha^A45000^AProof^Areader 1203^AMasthanvali^A40000^ATechnicali^Awriter 1204^AKiran^A40000^AHr^AAdmin 1205^AKranthi^A30000^AOp^AAdmin

1206^AGopal^A45000^A Technical^Amanager

1207^AManisha 45000^AProof^Areader^A\N 1208^AMasthanvali^A40000^ATechnicali^Awriter 1209^AKiran^A40000^AHr^AAdmin 1210^AKranthi^A30000^AOp^AAdmin

1.3. 默认保存分割符号是^A（\\x01）,我们想要更直观的数据可以通过自己制定列分割符号：

hive> insert overwrite local directory '/home/hadoop/emp' > row format delimited > fields terminated by '\t' > select * from emp; 1.4. 再次查看数据： hadoop@data2:~/emp$ ll total 16 drwxrwxr-x 2 hadoop hadoop 4096 May 15 15:42 ./

drwxr-xr-x 15 hadoop hadoop 4096 May 15 15:42 ../

-rw-r--r-- 1 hadoop hadoop 330 May 15 15:42 000000_0

-rw-r--r-- 1 hadoop hadoop 12 May 15 15:42 .000000_0.crc hadoop@data2:~/emp$ cat 000000_0

1201 Gopal 45000 Technical manager

1202 Manisha 45000 Proof reader

1203 Masthanvali 40000 Technicali writer

1204 Kiran 40000 Hr Admin

1205 Kranthi 30000 Op Admin

1206 Gopal 45000 Technical manager

1207 Manisha 45000 Proof reader \N

1208 Masthanvali 40000 Technicali writer

1209 Kiran 40000 Hr Admin

1210 Kranthi 30000 Op Admin

2.导出到hdfs上来:

2.1.去掉local:

hive> insert overwrite directory '/home/hadoop/emp' > select * from emp;

2.2.查看目录：

hadoop@data2:~/emp$ hadoop fs -ls /home/hadoop/emp SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/software/hadoop-2.6.0-cdh5.9.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/software/hbase-1.2.0-cdh5.9.0/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] Found 1 items -rwxr-xr-x 3 hadoop supergroup 330 2017-05-15 15:36 /home/hadoop/emp/000000_0

2.3. 使用hadoop命令：hadoop fs -get /user/hive/warehouse/test.db/emp/* /home/hadoop/hive

hive的DML操作

select ...where ...

hive> select * from emp where salary > 40000 ;

OK

1201 Gopal 45000 Technical manager

1202 Manisha 45000 Proof reader

Time taken: 0.116 seconds, Fetched: 2 row(s)

2.从一张表查询插入另一张表：insert into table ..select ..from ..

hive> insert into table emp_bak select eid,ename,salary,destination,dept from emp where eid < 1206 ;

hive> select * from emp_bak > ;

OK

1201 Gopal 45000 Technical manager

1202 Manisha 45000 Proof reader

1203 Masthanvali 40000 Technicali writer

1204 Kiran 40000 Hr Admin

1205 Kranthi 30000 Op Admin

Time taken: 0.035 seconds, Fetched: 5 row(s)

3.覆盖表：insert overwrite table ... select ..from ...

hive> insert overwrite table emp_bak select eid,ename,salary,destination,dept from emp where eid >= 1206 ;

hive> select * from emp_bak;

OK

1206 Gopal 45000 Technical manager

1207 Manisha 45000 Proof reader NULL

1208 Masthanvali 40000 Technicali writer

1209 Kiran 40000 Hr Admin

1210 Kranthi 30000 Op Admin

Time taken: 0.034 seconds, Fetched: 5 row(s)

6.连接

6.1.join

hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT

> FROM CUSTOMERS c JOIN ORDERS o

> ON (c.ID = o.CUSTOMER_ID);

...(执行过程日志省略)

OK

2 Kali 31 2050

3 Cham 20 3000

4 Muffi 25 1500

Time taken: 14.722 seconds, Fetched: 3 row(s)

6.2.LEFT OUTER JOIN:LEFT JOIN返回左表中的所有的值，加上右表，或JOIN子句没有匹配的情况下返回NULL。

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE > FROM CUSTOMERS c > LEFT OUTER JOIN ORDERS o > ON (c.ID = o.CUSTOMER_ID);

Total MapReduce CPU Time Spent: 1 seconds 590 msec

OK

1 Ramsh NULL NULL

2 Kali 2050 2009-05-08 00:00:00

3 Cham 3000 2009-10-08 00:00:00

4 Muffi 1500 2009-11-20 00:00:00

5 Kaush NULL NULL

Time taken: 14.277 seconds, Fetched: 5 row(s)

6.3.RIGHT OUTER JOIN:RIGHT JOIN返回右表中的所有值，加上左表，或者没有匹配的情况下返回NULL。

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE > FROM CUSTOMERS c > RIGHT OUTER JOIN ORDERS o > ON (c.ID = o.CUSTOMER_ID);

Total MapReduce CPU Time Spent: 2 seconds 200 msec

OK

3 Cham 3000 2009-10-08 00:00:00

2 Kali 2050 2009-05-08 00:00:00

4 Muffi 1500 2009-11-20 00:00:00

6.4.FULL OUTER JOIN :连接表包含两个表的所有记录，或两侧缺少匹配结果那么使用NULL值填补

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE > FROM CUSTOMERS c > FULL OUTER JOIN ORDERS o > ON (c.ID = o.CUSTOMER_ID);

Total MapReduce CPU Time Spent: 4 seconds 740 msec

OK

1 Ramsh NULL NULL

2 Kali 2050 2009-05-08 00:00:00

3 Cham 3000 2009-10-08 00:00:00

4 Muffi 1500 2009-11-20 00:00:00

5 Kaush NULL NULL

Time taken: 15.693 seconds, Fetched: 5 row(s)

UDF函数：用户自定义函数。

1.首先要先继承 UDF

2.重写evale方法import org.apache.hadoop.hive.ql.exec.UDF;

import org.apache.hadoop.hive.ql.exec.UDF;

/*** 先去掉空值然后匹配正则去掉一些特殊字符，空格如果满足条件就返回数据，不满足置为null*/

public class NameUDF extends UDF {

// 剔除特殊字符，空格

public static final String nameRegx = "\\pP|\\pS|\\s";

public String evaluate(String name) {

// 判断是否为空和null值

if (name != null && !"".equals(name)) {

// 将特殊字符使用空字符串来补充

name = name.replaceAll(nameRegx, "");

if ("".equals(name)) {

return null;

} else {

return name;

}

}

return null; }}

3.jar包传入hdfs

hadoop@data2:~$ hadoop fs -put dw-udf-0.0.1-SNAPSHOT.jar /user/udf/

4.添加jar包

hive> add jar hdfs://XXXXX:9000/user/udf/dw-udf-0.0.1-SNAPSHOT.jar;converting to local hdfs://XXXXX:9000/user/udf/dw-udf-0.0.1-SNAPSHOT.jarAdded [/tmp/fbbf05c3-c70f-4a16-9033-5d57119a18d0_resources/dw-udf-0.0.1-SNAPSHOT.jar] to class pathAdded resources: [hdfs://XXXX:9000/user/udf/dw-udf-0.0.1-SNAPSHOT.jar]

5.创建临时函数

hive> create temporary function FN_CLS_Name as 'cn.XXXXXX.scrm.udf.NameUDF';

OK Time taken: 0.013 seconds

6.使用udf函数

- 查看表：

hive> select * from emp_bak;

OK

1201 @@# 45000 Technical manager

1202 Manisha 45000 Proof reader

1203 Masthanvali 40000 Technicali writer

1204 Kiran 40000 Hr Admin

1205 Kranthi 30000 Op Admin

Time taken: 0.033 seconds, Fetched: 5 row(s)

- 使用udf函数然后查看

hive> insert overwrite table emp_bak select eid,FN_CLS_Name(ename),salary,destination,dept from emp_bak ;

hive> select * from emp_bak;

OK

1201 NULL 45000 Technical manager

1202 Manisha 45000 Proof reader

1203 Masthanvali 40000 Technicali writer

1204 Kiran 40000 Hr Admin

1205 Kranthi 30000 Op Admin

Time taken: 0.043 seconds, Fetched: 5 row(s)