MySQL的Hive安装
安装MySQL(可以在不同机器上, 但网络要可以互访)
在里面创建一个允许远程访问的MySQL账号
|
1 2 3 4 |
CREATE USER 'hive'@'%' IDENTIFIED BY 'hive1234'; GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'%' WITH GRANT OPTION; CREATE USER 'hive'@'localhost' IDENTIFIED BY 'hive1234'; GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'localhost' WITH GRANT OPTION; |
- 在虚拟机测试能否远程连接MySQL服务
- 有MySQL客户端的情况下
- mysql -uhive -h <mysql所在机器的ip> -p
- 输出密码回车, 如果能进入mysql>命令行界面说明连接成功
- 没有MySQL客户端也可以直接ping一下mysql所在机器的ip
- 有MySQL客户端的情况下
下载hive, 这里选择1.2.2版本
解压到 ~/app下, 可以视你的情况更改, 但要保证有读写权限,
编辑~/.bash_profile 添加环境变量, 红色高亮处要根据你的实际情况修改
export HIVE_HOME=/home/hadoop/app/hive-1.2.2(替换为hive实际解压位置)
export PATH=$HIVE_HOME/bin:$PATH
输入source ~/.bash_profile 使其生效
终端输入hiv按tab键, 如果能自动补全为hive说明环境变量ok
进入hive解压目录的conf文件夹下面, hive-env.sh 和 hive-site.xml 两个文件
-
- 创建 hive-env.sh, 并添加下面配置
HADOOP_HOME=/home/hadoop/app/hadoop-2.6.0-cdh5.15.1(换成你hadoop所在目录)
-
- 创建hive-site.xml, 参照示例修改
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<?xml version="1.0"?> <?xml-stylesheet type="text/xsl" href="configuration.xsl"?> <configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false(如果MySQL和hive在一个机器上可以使用localhost, 否则需要填写MySQL所在机器的实际IP)</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>hive(换成你实际使用的MySQL账户)</value> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>hive1234(换成你使用MySQL账户的实际密码)</value> </property> </configuration> |
下载mysql-connector–java-5.1.xx.bin.jar 放在 ${HIVE_HOME}/lib 下面
启动dfs 和 yarn
|
1 2 3 4 5 6 7 |
[hadoop@hadoop000 conf]$ jps 11602 NameNode 12212 ResourceManager 11863 SecondaryNameNode 11706 DataNode 12315 NodeManager 48207 Jps |
等待几分钟后, 避开安全模式, 启动 hive客户端
[hadoop@hadoop000 ~]$ hive
PS:
如果MysQL服务器部署在其他机器上, 则URL中要改成IP或主机名的访问方式
如: jdbc:mysql://192.168.1.142:3306/hive?createDatabaseIfNotExist=true&useSSL=false
这种情况下所使用的账户必须要开启远程访问权限 hive@%
Hive快速入门
hive sql 语法 https://cwiki.apache.org/confluence/display/Hive/LanguageManual
hive的hql语句和sql极其类似
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[hadoop@hadoop000 ~]$ hive which: no hbase in (/home/hadoop/app/hive-1.1.0-cdh5.15.1/bin:/home/hadoop/app/hadoop-2.6.0-cdh5.15.1/bin:/home/hadoop/app/hadoop-2.6.0-cdh5.15.1/sbin:/home/hadoop/app/jdk1.8.0_91/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/hadoop/.local/bin:/home/hadoop/bin) Logging initialized using configuration in jar:file:/home/hadoop/app/hive-1.1.0-cdh5.15.1/lib/hive-common-1.1.0-cdh5.15.1.jar!/hive-log4j.properties WARNING: Hive CLI is deprecated and migration to Beeline is recommended. hive> hive> show databases; OK default Time taken: 22.65 seconds, Fetched: 1 row(s) hive> create database hive; OK Time taken: 0.372 seconds hive> use hive; OK Time taken: 0.069 seconds hive> show tables; OK Time taken: 0.074 seconds hive> |
这时登录hive用户登录mysql中查看, 会发现自动创建了hive数据库, 并且里面出现了26张表(这些就是用来保存元数据的)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
mysql> use hive; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------------+ | Tables_in_hive | +---------------------------+ | BUCKETING_COLS | | CDS | | COLUMNS_V2 | | DATABASE_PARAMS | | DBS | | FUNCS | | FUNC_RU | | GLOBAL_PRIVS | | PARTITIONS | | PARTITION_KEYS | | PARTITION_KEY_VALS | | PARTITION_PARAMS | | PART_COL_STATS | | ROLES | | SDS | | SD_PARAMS | | SEQUENCE_TABLE | | SERDES | | SERDE_PARAMS | | SKEWED_COL_NAMES | | SKEWED_COL_VALUE_LOC_MAP | | SKEWED_STRING_LIST | | SKEWED_STRING_LIST_VALUES | | SKEWED_VALUES | | SORT_COLS | | TABLE_PARAMS | | TAB_COL_STATS | | TBLS | | VERSION | +---------------------------+ 29 rows in set (0.00 sec) |
其中DBS那张表中保存的就是数据库的相关信息
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> select * from DBS \G *************************** 1. row *************************** DB_ID: 1 DESC: Default Hive database DB_LOCATION_URI: hdfs://hadoop000:8020/user/hive/warehouse NAME: default OWNER_NAME: public OWNER_TYPE: ROLE *************************** 2. row *************************** DB_ID: 2 DESC: NULL DB_LOCATION_URI: hdfs://hadoop000:8020/user/hive/warehouse/hive.db NAME: hive OWNER_NAME: hadoop OWNER_TYPE: USER 2 rows in set (0.00 sec) |
Views: 16