Ubuntu安装Hive及mysql

蔡瀛

2012-08-03

关注关注

1、下载了hive-0.9.0.tar.gz

http://hadoop.apache.org/hive/releases.html

1、tarxzfhive-0.9.0.tar.gz

在/etc/profile中添加：

exportHIVE_INSTALL=/home/hadoop/hive-0.9.0

exportPATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HIVE_INSTALL/bin

在NameNode上用hadoop用户启动hadoop

/opt/hadoop/bin/start-all.sh

然后在hive服务器上

/home/hadoop/hive-0.9.0/bin/hive

启动hive

root@Slave2:/home/hadoop#/home/hadoop/hive-0.9.0/bin/hive

Logginginitializedusingconfigurationinjar:file:/home/hadoop/hive-0.9.0/lib/hive-common-0.9.0.jar!/hive-log4j.properties

Hivehistoryfile=/tmp/root/hive_job_log_root_201208021726_1864342643.txt

hive>showtables;

Timetaken:4.664seconds

hive>

配置：

root@Slave2:/home/hadoop/hive-0.9.0/conf#vimhive-env.sh.template

HADOOP_HOME=/opt/hadoop

//默认设置

root@Slave2:/home/hadoop/hive-0.9.0/conf#cp-rfhive-default.xml.templatehive-default.xml

//个性化设置

root@Slave2:/home/hadoop/hive-0.9.0/conf#cp-rfhive-default.xml.templatehive-site.xml

在namenode上用root用户

root@Master:/opt/hadoop#bin/hadoopdfsadmin-safemodeleave

SafemodeisOFF

root@Master:/opt/hadoop#/opt/hadoop/bin/hadoopfs-mkdir/tmp

root@Master:/opt/hadoop#/opt/hadoop/bin/hadoopfs-mkdir/user/hive/warehouse

root@Master:/opt/hadoop#/opt/hadoop/bin/hadoopfs-chmodg+w/tmp

root@Master:/opt/hadoop#/opt/hadoop/bin/hadoopfs-chmodg+w/user/hive/warehouse

root@Slave2:/tmp#/home/hadoop/hive-0.9.0/bin/hive-e"createtabledummy(valuesSTRING);"

root@Slave2:/tmp#/home/hadoop/hive-0.9.0/bin/hive-e"loaddatalocalinpath'/tmp/dummy.txt'overwriteintotabledummy"

root@Slave2:/tmp#/home/hadoop/hive-0.9.0/bin/hive

hive>showtables;

dummy

Timetaken:3.398seconds

或者：-S表示强制不显示信息

root@Slave2:/tmp#/home/hadoop/hive-0.9.0/bin/hive-S-e'select*fromdummy'

创建表

hive>createtablerecords(yearSTRING,temperatureINT,qulityINT)rowformatdelimitedfieldsterminatedby'\t';

-----------------------------------------------------

错误

FAILED:Errorinmetadata:MetaException(message:Gotexception:org.apache.hadoop.security.AccessControlExceptionorg.apache.hadoop.security.AccessControlException:Permissiondenied:user=root,access=WRITE,inode="":hadoop:supergroup:rwxr-xr-x)

请重新执行

root@Master:/opt/hadoop#/opt/hadoop/bin/hadoopfs-mkdir/tmp

root@Master:/opt/hadoop#/opt/hadoop/bin/hadoopfs-mkdir/user/hive/warehouse

root@Master:/opt/hadoop#/opt/hadoop/bin/hadoopfs-chmodg+w/tmp

root@Master:/opt/hadoop#/opt/hadoop/bin/hadoopfs-chmodg+w/user/hive/warehouse

-------------------------------------------------------

导入数据

hive>loaddatalocalinpath'/home/qiaowang/install_Hadoop/sample.txt'overwriteintotablerecords;

hive>select*fromrecords;

19921110

19931712

19941614

19951511

19961413

19971214

19981310

19991013

Timetaken:0.232seconds

hive>set-v列出所有属性值

如果出现权限问题，重新使用hadoop用户创建并format所有文件夹。

Hive的使用

hadoop@Slave2:/tmp$/home/hadoop/hive-0.9.0/bin/hive-servicehelp

Unrecognizedoption:-servicehelp

usage:hive

-d,--define<key=value>Variablesubsitutiontoapplytohive

commands.e.g.-dA=Bor--defineA=B

-e<quoted-query-string>SQLfromcommandline

-f<filename>SQLfromfiles

-H,--helpPrinthelpinformation

-h<hostname>connectingtoHiveServeronremotehost

--hiveconf<property=value>Usevalueforgivenproperty

--hivevar<key=value>Variablesubsitutiontoapplytohive

commands.e.g.--hivevarA=B

-i<filename>InitializationSQLfile

-p<port>connectingtoHiveServeronportnumber

-S,--silentSilentmodeininteractiveshell

-v,--verboseVerbosemode(echoexecutedSQLtothe

console)

----------------------------------------------------------------

安装mysql

----------------------------------------------------------------

1、下载mysql-connector-java-5.1.15-bin.jar

http://www.java2s.com/Code/Jar/m/Downloadmysqlconnectorjava5115binjar.htm

root@Slave2:/home/hadoop/hive-0.9.0/lib#

rz-e

mysql-connector-java-5.1.15-bin.jar加载进来

2、整合mysql做为hive的metastore

root@Slave2:/usr/local/mysql/bin#./mysql-uroot-p

创建hive数据库：

mysql>createdatabasehive;

③创建用户hive，它只能从localhost连接到数据库并可以连接到hive数据库：

mysql>grantallonhive.*[email protected]'123456';

mysql>flushprivileges;

root@Slave2:/home/hadoop/hive-0.9.0/conf#vimhive-site.xml

<name>javax.jdo.option.ConnectionURL</name>

<value>jdbc:mysql://10.2.128.120:3306/hive?createDatabaseIfNOtExist=true</value>

<description>JDBCconnectstringforaJDBCmetastore</description>

</property>

<name>javax.jdo.option.ConnectionDriverName</name>

<value>com.mysql.jdbc.Driver</value>

<description>DriverclassnameforaJDBCmetastore</description>

</property>

<name>javax.jdo.option.ConnectionUserName</name>

<description>usernametouseagainstmetastoredatabase</description>

</property>

<name>javax.jdo.option.ConnectionPassword</name>

<description>passwordtouseagainstmetastoredatabase</description>

</property>

hadoop@Slave2:~/hive-0.9.0/bin$./hive

hive>CREATETABLEmy(idINT,namestring)ROWFORMATDELIMITEDFIELDSTERMINATEDBY'\t';

root@Slave2:/usr/local/mysql/bin#./mysql-uhive-p123456-h10.2.128.120

WelcometotheMySQLmonitor.Commandsendwith;or\g.

YourMySQLconnectionidis8

Serverversion:5.0.82-logSourcedistribution

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>showtables;

ERROR1046(3D000):Nodatabaseselected

mysql>showdatabases;

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

|Database|

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

|information_schema|

|hive|

|test|

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

3rowsinset(0.00sec)

mysql>usehive

Databasechanged

mysql>showtables;

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

|Tables_in_hive|

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

|BUCKETING_COLS|

|CDS|

|COLUMNS_V2|

|DATABASE_PARAMS|

|DBS|

|PARTITION_KEYS|

|SDS|

|SD_PARAMS|

|SEQUENCE_TABLE|

|SERDES|

|SERDE_PARAMS|

|SORT_COLS|

|TABLE_PARAMS|

|TBLS|

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

14rowsinset(0.00sec)

mysql>select*fromTBLS;

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

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

|1|1343987480|1|0|hadoop|0|1|my|MANAGED_TABLE|NULL|NULL|

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

1rowinset(0.00sec)

创建完毕。

启动hive的web界面

http://10.2.128.120:9999/hwi/

方式一

exportANT_LIB=/opt/ant/lib

./hive--servicehwi

------------------------------------------------------------------------------------

以下为安装在Master上安装Hbase

1、下载地址

http://www.apache.org/dyn/closer.cgi/hbase

http://www.fayea.com/apache-mirror/hbase/stable/

hbase-0.92.1.tar.gz

root@Master:/opt#tarxfzhbase-0.92.1.tar.gz

root@Master:/opt#cdhbase-0.92.1/conf

root@Master:/opt/hbase-0.92.1/conf#vimhbase-site.xml

<name>hbase.rootdir</name>

<value>hdfs://Master.Hadoop:54310/hbase</value>

</property>

<name>hbase.cluster.distributed</name>

</property>

<name>hbase.master</name>

<value>hdfs://Master.Hadoop:60000</value>

</property>

<name>hbase.zookeeper.property.clientPort</name>

<description>PropertyfromZooKeeper'sconfigzoo.cfg.

Theportatwhichtheclientswillconnect.

</description>

</property>

<name>hbase.zookeeper.quorum</name>

<value>Master.Hadoop,Slave1.Hadoop,Slave2.Hadoop</value>

<description>CommaseparatedlistofserversintheZooKeeperQuorum.

Forexample,"host1.mydomain.com,host2.mydomain.com,host3.mydomain.com".

Bydefaultthisissettolocalhostforlocalandpseudo-distributedmodes

ofoperation.Forafully-distributedsetup,thisshouldbesettoafull

listofZooKeeperquorumservers.IfHBASE_MANAGES_ZKissetinhbase-env.sh

thisisthelistofserverswhichwewillstart/stopZooKeeperon.

</description>

</property>

<name>hbase.zookeeper.property.dataDir</name>

<value>/usr/local/zookeeper</value>

<description>PropertyfromZooKeeper'sconfigzoo.cfg.

Thedirectorywherethesnapshotisstored.

</description>

</property>

</configuration>

其中：

<value>Master,Slave1,Slave2</value>//配置的主机数为单数

hbase.rootdir：“hdfs://frank-1:9000”这部分必须和hadoop-config/core-site.xml中的fs.default.name保持一致；

hbase.zookeeper.property.clientPort，是由于我在zoo.cfg中修改了默认的clientPort，所以在这里要设置，监听2222端口(默认是2181)

Hbase管理着的ZooKeeper集群在节点rs{1,2,3,4,5}.example.com,监听2222端口(默认是2181)，并确保conf/hbase-env.sh文件中HBASE_MANAGE_ZK的值是true，再编辑conf/hbase-site.xml设置hbase.zookeeper.property.clientPort和hbase.zookeeper.quorum。你还可以设置hbase.zookeeper.property.dataDir属性来把ZooKeeper保存数据的目录地址改掉。默认值是/tmp，这里在重启的时候会被操作系统删掉，可以把它修改到/user/local/zookeeper.

root@Master:/opt/hbase-0.92.1/conf#mkdir/usr/local/zookeeper

root@Master:/opt/hbase-0.92.1/conf#vim/etc/profile//每台机器都要配

exportHBASE_HOME=/opt/hbase-0.92.1

exportPATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HIVE_INSTALL/bin:$HBASE_HOME/bin

root@Master:/opt/hadoop/conf#vim/opt/hbase-0.92.1/conf/hbase-env.sh

exportJAVA_HOME=/usr/lib/jdk1.6.0_33

exportHBASE_MANAGES_ZK=true

exportHBASE_CLASSPATH=/opt/hadoop/conf

5把hadoophdfs-site.xml考到hbaseconf下

root@Master:/opt/hadoop/conf#cphdfs-site.xml/opt/hbase-0.92.1/conf/

6配置conf下的regionservers

root@Master:/opt/hbase-0.92.1/conf#vimregionservers

Slave1

Slave2

拷贝到Slave2上

root@Slave2:/opt/hbase-0.92.1#[email protected]:/opt/hbase-0.92.1//opt/

root@Slave1:/opt#[email protected]:/opt/hbase-0.92.1//opt/

7、启动HBase

权限问题，都改为hadoop组

chown-Rhadoop:hadoophbase-0.92.1/

chown-Rhadoop:hadoop/usr/local/zookeeper/

hadoop@Master:/opt/hbase-0.92.1/bin$./start-hbase.sh

Slave2.Hadoop:startingzookeeper,loggingto/opt/hbase-0.92.1/bin/../logs/hbase-hadoop-zookeeper-Slave2.Hadoop.out

Slave1.Hadoop:startingzookeeper,loggingto/opt/hbase-0.92.1/bin/../logs/hbase-hadoop-zookeeper-Slave1.Hadoop.out

Master.Hadoop:startingzookeeper,loggingto/opt/hbase-0.92.1/bin/../logs/hbase-hadoop-zookeeper-Master.Hadoop.out

startingmaster,loggingto/opt/hbase-0.92.1/logs/hbase-hadoop-master-Master.Hadoop.out

Slave2.Hadoop:startingregionserver,loggingto/opt/hbase-0.92.1/bin/../logs/hbase-hadoop-regionserver-Slave2.Hadoop.out

Slave1.Hadoop:startingregionserver,loggingto/opt/hbase-0.92.1/bin/../logs/hbase-hadoop-regionserver-Slave1.Hadoop.out

hadoop@Master:/opt/hbase-0.92.1/bin$

启动成功！

./hbaseshell

create't1','f1','f2','f3'

ERROR:org.apache.hadoop.hbase.MasterNotRunningException:Retried7times

很明显，日志中说RPC协议不一致所造成的，恍然见明白因为我在hbase的配置文件中将rootdir设置为hdfs，如果这两者的RPC协议不一致就会导致这个问题。

解决方法：

将hbase/lib目录下的hadoop-core的jar文件删除，将hadoop目录下的hadoop-0.20.2-core.jar拷贝到hbase/lib下面，然后重新启动hbase即可。

rm-rfhadoop-core-1.0.0.jar

cphadoop-0.20.2-core.jar/opt/hbase-0.92.1/lib/

root@Master:/opt/hbase-0.92.1/lib#chown-Rhadoop:hadoop*

错误

2012-08-0618:42:03,177FATALorg.apache.hadoop.hbase.master.HMaster:Unhandledexception.Startingshutdown.

java.net.ConnectException:CalltoMaster.Hadoop/127.0.1.1:54310failedonconnectionexception:java.net.ConnectException:Connectionrefused

/etc/hosts与hostname不一致所致

修改后的/etc/hosts

127.0.0.1localhost

10.2.128.46Master.Hadoop

10.2.128.20Slave1.Hadoop

10.2.128.120Slave2.Hadoop

可查看日志tail-fhbase-hadoop-master-Master.Hadoop.log

hbase(main):005:0>create'test','id','name'

0row(s)in1.8840seconds

hbase(main):001:0>put'test','row1','id','1'

0row(s)in0.5120seconds

hbase(main):002:0>put'test','row1','name','feng'

0row(s)in0.0160seconds

hbase(main):003:0>list

TABLE

test

1row(s)in0.1210seconds

hbase(main):004:0>scan'test'

ROWCOLUMN+CELL

row1column=id:,timestamp=1344251239462,value=1

row1column=name:,timestamp=1344251259562,value=feng

1row(s)in0.0510seconds

hbase(main):005:0>get'test','row1'

COLUMNCELL

id:timestamp=1344251239462,value=1

name:timestamp=1344251259562,value=feng

2row(s)in0.0330seconds

--------------------------------------------------------------------

1、Hive与HBase的整合

/home/hadoop/hive-0.9.0/conf/hive-site.xml添加

<value>file:///home/hadoop/hive-0.9.0/lib/hive-hbase-handler-0.9.0.jar,file:///home/hadoop/hive-0.9.0/lib/hbase-0.92.0.jar,file:///home/hadoop/hive-0.9.0/lib/zookeeper-3.4.3.jar</value>

</property>

<name>hive.zookeeper.quorum</name>

<value>Master.Hadoop,Slave1.Hadoop,Slave2.Hadoop</value>

<description>Thelistofzookeeperserverstotalkto.Thisisonlyneededforread/writelocks.</description>

</property>

2、拷贝hbase-0.92.0.jar到所有hadoop节点的hadoop/lib下

root@Slave2:/home/hadoop/hive-0.9.0/lib#cphbase-0.92.0.jar/opt/hadoop/lib

chown-Rhadoop:hadoophbase-0.92.0.jar

root@Slave2:/home/hadoop/hive-0.9.0/lib#[email protected]:/opt/hadoop/lib/

chown-Rhadoop:hadoophbase-0.92.0.jar

3、拷贝hbase/conf下的hbase-site.xml文件到所有hadoop节点(包括master)的hadoop/conf下

root@Master:/opt/hadoop/conf#cp/opt/hbase-0.92.1/conf/hbase-site.xml/opt/hadoop/conf/

chown-Rhadoop:hadoophbase-site.xml

root@Master:/opt/hbase-0.92.1/conf#[email protected]:/opt/hadoop/conf/

chown-Rhadoop:hadoophbase-site.xml

启动mysql

/etc/init.d/mysqldstart

启动hbase

hadoop@Master:/opt/hbase-0.92.1/bin$./start-hbase.sh

可以查看hbase状态

http://10.2.128.46:60010/master-status

启动Hive

root@Slave2:/opt/hadoop/conf#hive

或

./hive

hive

1.创建hbase识别的数据库

CREATETABLEhbase_table_1(keyint,valuestring)

STOREDBY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITHSERDEPROPERTIES("hbase.columns.mapping"=":key,cf1:val")

TBLPROPERTIES("hbase.table.name"="xyz");

hbase.table.name定义在hbase的table名称

hbase.columns.mapping定义在hbase的列族

登录hbase发现

hadoop@Master:/opt/hbase-0.92.1/bin$./hbaseshell

HBaseShell;enter'help<RETURN>'forlistofsupportedcommands.

Type"exit<RETURN>"toleavetheHBaseShell

Version0.92.1,r1298924,FriMar916:58:34UTC2012

hbase(main):001:0>list

TABLE

test

xyz

2row(s)in0.5090seconds

hbase(main):002:0>

hbase(main):003:0>describe'xyz'

DESCRIPTIONENABLED

{NAME=>'xyz',FAMILIES=>[{NAME=>'cf1',BLOOMFILTER=>'NONE',REPLtrue

ICATION_SCOPE=>'0',VERSIONS=>'3',COMPRESSION=>'NONE',MIN_VERSIO

NS=>'0',TTL=>'2147483647',BLOCKSIZE=>'65536',IN_MEMORY=>'fals

e',BLOCKCACHE=>'true'}]}

1row(s)in0.0500seconds

hbase(main):004:0>scan'xyz'

ROWCOLUMN+CELL

0row(s)in0.1010seconds

hbase(main):005:0>put'xyz','100','cf1:val','www.51.com'

0row(s)in0.0700seconds

返回Hive

hive>select*fromhbase_table_1limit10;

100www.51.com

Timetaken:0.611seconds

OK数据已经存在！

查看已hase中已存在的表

hbase(main):010:0>scan'test'

ROWCOLUMN+CELL

row1column=id:,timestamp=1344251239462,value=1

row1column=name:,timestamp=1344251259562,value=feng

1row(s)in0.0720seconds

droptablehbase_table_3;

hbase(main):013:0>put'xyz','86','cf1:val','val_86'

0row(s)in0.0130seconds

hive>select*fromhbase_table_1;

100www.51.com

86val_86

Timetaken:0.191seconds

三、多列和多列族（MultipleColumnsandFamilies）

CREATETABLEpokes(fooINT,barSTRING);

b）批量插入数据

hive>LOADDATALOCALINPATH'/home/hadoop/hive-0.9.0/examples/files/kv1.txt'OVERWRITEINTOTABLEpokes;、

这个文件位于hive的安装目录下，examples/files/kv1.txt

INSERTOVERWRITETABLEhbase_table_1SELECT*FROMpokesWHEREfoo=200;

hive>select*fromhbase_table_1;

100www.51.com

200val_200

86val_86

Timetaken:0.11seconds

1．创建数据库

CREATETABLEhbase_table_2(keyint,value1string,value2int,value3int)STOREDBY'org.apache.hadoop.hive.hbase.HBaseStorageHandler'WITHSERDEPROPERTIES("hbase.columns.mapping"=":key,a:b,a:c,d:e");

INSERTOVERWRITETABLEhbase_table_2SELECTfoo,bar,foo+1,foo+2FROMpokesWHEREfoo=98ORfoo=100;

hive>INSERTOVERWRITETABLEhbase_table_2SELECTfoo,bar,foo+1,foo+2FROMpokesWHEREfoo=98ORfoo=200;

hive>select*fromhbase_table_2limit10;

100val_100101102

200val_200201202

98val_9899100

Timetaken:0.126seconds

hive hadoop bin tmp mysql

安科网

Ubuntu安装Hive及mysql

蔡瀛

蔡瀛

相关推荐

hdfs、hive、hbase的搭建总结

Hadoop

Hive安装，以及一些问题处理

hadoop hdfs csv导入hive表

hadoop 伪分布式单机部署练习hive

3（Hive）

Hive函数大全-完整版

hive函数之~hive当中的lateral view 与 explode

hive函数之~窗口函数与分析函数

hive函数之~reflect函数

hive函数之~条件函数

hive函数之~日期函数

hive函数之~字符串函数

hive函数之~关系运算

Hive使用

Hive的安装与启动

Hive llap服务安装说明及测试（二）

Hive学习之路（二）Hive安装

（一）hive远程模式搭建

Hive学习(二)【数据类型、类型转换】

蔡瀛