安装1.5mysql_1.5 Hive初步使用和安装MySQL

一、HQL初步试用

1、创建一个student表

#创建一个student表

hive> create table student(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

OK

Time taken: 0.028 seconds

hive> show tables;

OK

bf_log

student

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

#因为数据是存在HDFS的文件中，ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 就是指定数据之间的分隔符；

2、准备测试数据

[root@hadoop-senior datas]# touch student.txt

[root@hadoop-senior datas]# vim student.txt #制表符分割

1001 zhangsan

1002 lisi

1003 wangwu

3、加载数据到hive表中

hive> load data local inpath '/opt/datas/student.txt' into table student;

Copying data from file:/opt/datas/student.txt

Copying file: file:/opt/datas/student.txt

Loading data to table default.student

Table default.student stats: [numFiles=1, numRows=0, totalSize=45, rawDataSize=0]

OK

Time taken: 0.222 seconds

4、查询

#查询全部，不会走MapReduce

hive> select * from student;

OK

1001 zhangsan

1002 lisi

1003 wangwu

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

#查询部分，就会走MapReduce

hive> select id from student;

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_1554717689707_0003, Tracking URL = http://hadoop-senior.ibeifeng.com:8088/proxy/application_1554717689707_0003/

Kill Command = /opt/modules/hadoop-2.5.0/bin/hadoop job -kill job_1554717689707_0003

Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0

2019-04-18 18:04:48,120 Stage-1 map = 0%, reduce = 0%

2019-04-18 18:04:53,240 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.85 sec

MapReduce Total cumulative CPU time: 850 msec

Ended Job = job_1554717689707_0003

MapReduce Jobs Launched:

Job 0: Map: 1 Cumulative CPU: 0.85 sec HDFS Read: 268 HDFS Write: 15 SUCCESS

Total MapReduce CPU Time Spent: 850 msec

OK

1001

1002

1003

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

二、安装mysql存储元数据

1、准备安装包

#卸载自带的mysql

[root@hadoop-senior softwares]# rpm -qa |grep mysql

mysql-libs-5.1.71-1.el6.x86_64

[root@hadoop-senior softwares]# rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps

#

[root@hadoop-senior ~]# cd /opt/softwares/

[root@hadoop-senior softwares]# unzip mysql-libs.zip

#

[root@hadoop-senior softwares]# cd mysql-libs

[root@hadoop-senior mysql-libs]# ls

MySQL-client-5.6.24-1.el6.x86_64.rpm mysql-connector-java-5.1.27.tar.gz MySQL-server-5.6.24-1.el6.x86_64.rpm

2、安装启动

#安装server端

[root@hadoop-senior mysql-libs]# rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm #会生成随机密码，在/root/.mysql_secret里

[root@hadoop-senior ~]# cat /root/.mysql_secret

# The random password set for the root user at Fri Apr 19 09:29:24 2019 (local time): O9zwV6WQljcMkzRa

#安装client

[root@hadoop-senior mysql-libs]# rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm

#启动

[root@hadoop-senior mysql-libs]# service mysql start

Starting MySQL. [确定]

[root@hadoop-senior mysql-libs]# service mysql status

MySQL running (24085) [确定]

[root@hadoop-senior mysql-libs]# netstat -ntlp |grep 3306

tcp 0 0 :::3306 :::* LISTEN 24085/mysqld

3、连接mysql，改密码

#改密码

[root@hadoop-senior mysql-libs]# mysql -uroot -pO9zwV6WQljcMkzRa #密码是刚才自动生成的

......

mysql> set password=password('123456');

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

#新密码连接

[root@hadoop-senior mysql-libs]# mysql -uroot -p123456

......

mysql>

4、授权mysql的root用户

#

[root@hadoop-senior ~]# hostname

hadoop-senior.ibeifeng.com

#连入mysql操作

mysql> grant all privileges on *.* to root@'%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'hadoop-senior.ibeifeng.com' identified by '123456' with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql> select User,Host,Password from user;

+------+----------------------------+-------------------------------------------+

| User | Host | Password |

+------+----------------------------+-------------------------------------------+

| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| root | hadoop-senior.ibeifeng.com | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| root | 127.0.0.1 | *D25D1C957F2E56F330D565256AE9D88C49E7194D |

| root | ::1 | *D25D1C957F2E56F330D565256AE9D88C49E7194D |

| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+------+----------------------------+-------------------------------------------+

5 rows in set (0.00 sec)

mysql> delete from user where User='root' and Host='hadoop-senior.ibeifeng.com';

Query OK, 1 row affected (0.00 sec)

mysql> delete from user where User='root' and Host='127.0.0.1';

Query OK, 1 row affected (0.00 sec)

mysql> delete from user where User='root' and Host='::1';

Query OK, 1 row affected (0.00 sec)

mysql> delete from user where User='root' and Host='localhost';

Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select User,Host,Password from user;

+------+------+-------------------------------------------+

| User | Host | Password |

+------+------+-------------------------------------------+

| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+------+------+-------------------------------------------+

1 row in set (0.00 sec)

mysql> exit;