1 在CentOS 6.4上安装PostgreSql 9.3
1.1 用yum安装PostgreSql Server:
CentOS 6.4用yum默认安装的PostgreSql版本是8.x,比目前最新的9.3差了一代,需要更新yum repository之后,才能用yum安装。用yum安装的好处是方便、模块化,而且以后比较好卸载。
首先到http://yum.postgresql.org/repopackages.php找到对应操作系统的合适的yum repository版本,我使用的服务器环境是CentOS 6.4 x86_64,找到该版本对用的rpm url后,用rpm安装yum repository:
sudo rpm –i http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
然后就可以通过yum安装PostgreSql 9.3了:
sudo yum install postgresql93-server postgresql93 postgresql93-contrib postgresql93-devel
这几个安装包分别对应的是:
- postgresql93-server:PostgreSql的服务器端;
- postgresql93:PostgreSql的客户端程序和库;
- postgresql93-contrib:一些外部扩展;
- postgresql93-devel:库和头文件,对于一般使用,这个是可选的;但是如果后面需要以编译源文件的形式安装一些PostgreSql的扩展(例如Multicorn),它们在编译过程中需要这些文件,那么这个一定要有。
安装程序会创建postgres用户和postgres组,为了安全起见,建议将这个用户禁止从远程登录(具体方法请自行查找)。然后设置postgres用户的密码:
sudo passwd postgres
至此,postgresql9.3安装过程完成。
提醒:这个postgres用户是操作系统的用户,和后面将会讲到的数据库登录用户postgres不是一个东西,千万不要混淆。
1.2 设置PostgreSql Server:
1.2.1 首先初始化数据库:
sudo service postgresql-9.3 initdb
对于使用yum安装的PostgreSql Server,默认配置是服务器会将数据库的数据文件放在/var/lib/pgsql/9.3/data目录下,初始化数据库后,这个目录下保存有最基本的数据和配置文件;通常这个目录都是在磁盘的系统分区中,如果服务器有可靠性更高、速度更快的存储空间(例如磁盘阵列),最好把数据文件目录移到那里,移动的方法是:假设有一个磁盘阵列已经挂载在了/dwdata下,我们想将数据文件放在/dwdata/data/postgresql/目录下,则我们需要做如下移动:
sudo mkdir –p /dwdata/data
sudo cp –r /var/lib/pgsql/9.3/data/ /dwdata/data
sudo mv /dwdata/data/data /dwdata/data/postgresql
设置新数据目录及其所有内容的owner和group为postgres:
sudo chowm –r postgres /dwdata/data/postgresql
sudo chgrp –r postgres /dwdata/data/postgresql
1.2.2 然后修改PostgreSql Server的启动配置文件/etc/init.d/postgresql-9.3:
Sudo vi /etc/init.d/postgresql-9.3
找到如下字样的行:
# Set defaults for configuration variables
PGENGINE=/usr/pgsql-9.3/bin
PGPORT=5432
PGDATA=/var/lib/pgsql/9.3/data
PGLOG=/var/lib/pgsql/9.3/pgstartup.log
# Log file for pg_upgrade
PGUPLOG=/var/lib/pgsql/$PGMAJORVERSION/pgupgrade.log
这里面值得改的几个参数及其含义是:
- PGPORT:PostgreSql Server蹲守的TCP端口,一般用默认值即可;
- PGDATA:数据文件目录,对于DW生产来说,要指定到磁盘阵列上;
- PGLOG:PostgreSql Server启动日志的位置,是一个全路径文件名;
- PGUPLOG:PostgreSql升级日志的位置,也是一个全路径文件名;
这里我需要将其中几个参数修改为新的数据文件目录:
PGDATA=/dwdata/data/postgresql
PGLOG=/dwdata/data/postgresql/pgstartup.log
PGUPLOG=/dwdata/data/postgresql/pgupgrade.log
PostgreSql就会将数据文件和关键的log文件放到创建的数据目录中。
1.2.3 启动和停止PostgreSql Server:
上述设置完成后,就可以用下述命令启动PostgreSql Server了:
sudo service postgresql-9.3 start
下述命令停止PostgreSql Server:
sudo service postgresql-9.3 stop
下述命令检查PostgreSql Server的运行状态:
sudo service postgresql-9.3 status
下述命令重新启动PostgreSql Server:
sudo service postgresql-9.3 restart
1.2.4 配置PostgreSql Server的网络参数:
PostgreSql Server的配置文件在数据文件目录中(在这里即为/dwdata/data/postgresql目录)。最主要的配置文件是postgresql.conf,里面有关于网络、性能、缓存大小等各类设置,对于具备多块网卡的服务器来说,最主要的设置是服务器监听的地址,在配置文件中可以找到如下条目:
#listen_addresses = ‘localhost’ # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to ‘localhost’; use ‘*’ for all
# (change requires restart)
一般情况况下,要求PostgreSql Server在所有网卡上均监听,可以将此条目前的“#”注释符去掉,并改为:
listen_addresses=’*’即可监听来自所有网卡的请求。如果需要监听特定的网卡,则可以用网卡的IP地址替换“*”字符。
如需修改监听的端口,则可以找到#port=5432这一条目,去掉“#”注释符并按需要修改之。对于不直接暴露在公网上的服务器,不需要修改这个端口。
上述配置修改后,需要重新启动PostgreSql Server才能生效。
1.2.5 控制PostgreSql Server的访问权限:
为了保证安全,PostgreSql Server在安装后,仅允许来自服务器本机的连接,如果需要对PostgreSql Server进行网络访问(比如用另一台服务器访问,或者用图形化的管理工具从远程管理服务器),就需要对访问权限进行设置。
控制网络访问权限的配置文件是pg_hba.conf,在文件末尾会有如下条目:
# TYPE DATABASE USER ADDRESS METHOD
# “local” is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
……
为了打开PostgreSql Server的外部访问,需要向文件末尾添加内容。例如:假如我们安装PostgreSql Server的服务器IP地址是192.168.66.23,我们希望该子网内(即IP为192.168.66.*)所有计算机都能连接这台PostgreSql Server,并且都需要用户名/密码登录,则可以添加如下行:
host all all 192.168.66.1/24 md5
这个例子中,“192.168.66.1/24”表示允许所有IP为“192.168.66.*”的计算机对服务器进行访问,“24”即IP的掩码有24个二进制的“1”,就是子网掩码的概念;例如,如果我们希望所有IP为“192.168.*.*”的计算机对服务器进行访问,则可以写为“192.168.1.1/16”。
第一个all表示允许访问所有数据库,第二个all表示允许所有用户名的用户登录;这两个域的设置可以参阅该文件前面长长的说明内容,本文不再赘述。
最后的md5表示用加密后的密码进行用户名/密码匹配的认证,这里的“用户名”是指数据库的用户,而不是操作系统的用户。常用的几种其他认证方式有ident(与操作系统的用户名做映射,采用操作系统的认证,数据库用户与操作系统用户之间的映射在pg_ident.conf文件中配置)、password(在网络传送明文密码认证,不安全)、trust(完全信任)、peer(取客户端操作系统的用户名,一般仅用来做本地连接)。
通常,采用数据库用户的用户名和密码进行认证,即md5。
1.2.6 使用psql命令行界面设置postgres用户的密码:
PostgreSql Server安装后,数据库中会有一个默认的用户叫postgres(注意这里是数据库登陆用户postgres,不是上面提到的操作系统用户postgres),具备管理员级别的权限。如果我们想通过图形化的管理界面登录到PostgreSql Server上对服务器进行管理,就需要先设置这个用户的密码,首先要切换到操作系统的postgres用户,该用户具有使用psql进行数据库管理的权限:
su postgres
然后执行psql,进入PostgreSql Server的命令行界面,提示符是“postgres=#”,这时我们就可以键入PostgreSql的命令了,例如用来修改用户密码的ALTER USER命令,我们将数据库用户postgres的密码修改为abcdefg:
psql
postgres=#ALTER USER postgres with password ‘abcdefg‘;
ALTER ROLE
postgres=#\q
exit
上面一段命令中,粗体蓝色表示我们要键入的操作系统命令,灰色表示psql的提示与输出,蓝色表示我们要键入的PostgreSql命令。psql界面用\q命令退出。
于是PostgreSql Server具备了一个超级用户postgres,其密码是abcdefg。可以从远程用各种客户端工具连接这台服务器了。
1.2.7 在客户端安装图形化管理界面并连接PostgreSql Server:
目前PostgreSql比较成熟的图形化管理界面是PgAdmin,具有Windows、Mac、Linux等多种版本,网站是http://www.pgadmin.org/,支持PostgreSql Server 9.3需要PgAdmin 1.18.0或更高的版本。我们可以用自己的笔记本作为客户端,安装管理界面,前提是笔记本与服务器之间的网络是直接连通的,5432端口能够正常通信。
下载安装都是图形化的。安装完成并运行,由于尚未连接过任何服务器,所以Server Groups中是空的。
点击左上角的“Add a connection to a server”按钮:
在弹出的对话框中,填入PostgreSql Server的地址和用户信息:
Name一栏可以自行填写易于识别的名字;Host是PostgreSql Server的机器名或IP地址;Port是Server的端口(默认是5432);Service一栏留空;Maintenance DB留默认的postgres即可;Username填入postgres,这是上面被设定过密码的数据库用户;Password填入上面设定的密码,并勾选Store password。由于我是在Windows下安装PgAdmin来连接虚拟机中CentOS中的postgresql,因此需要利用VirtualBox的端口转发规则:Host是我在Windows下的IP地址(cmd->ipconfig);然后在VirtualBox中设置->网络->端口转发->插入新规则,如下图所示添加端口转发规则,自定义主机端口,子系统IP是CentOS(ifconfig)中的IP地址,子系统端口是postgresql server端口,默认是5432。
![](http://static.oschina.net/uploads/space/2013/1201/222743_BIBy_1033344.png)
完成后点击OK按钮,即可看到Server Groups列表中多了一台服务器,点击它,即可一级一级的展开各种数据库对象:
在使用pgAdmin连接postgresql数据库的过程中,总是出现“端口监听未开启”等的问题连接不上,经过各种排错,我把CentIOS中的防火墙关闭,并且关闭了SELinux功能,同时修改了postgresql的配置文件pg-hba.conf,添加host all all 10.0.2.2/24 md5,这样才最后成功连接。
至此,在CentOSPostgreSql 6上安装PostgreSql 9.3的过程就成功完成了,而且也具有了能够管理该PostgreSql Server的图形化管理界面,可以进行任何服务器管理、监控、用户增删、数据查询、存储过程编写和运行等各类工作了。
2 将文件放到HDFS上并使用Hive查询:
现有一份一千万条数据记录的文件FakeData,需要部署到HDFS上,先创建一个文件夹命名为FakeDataDir:hadoop dfs –mkdir FakeDataDir
然后使用命令将文件放到HDFS上:hadoop dfs –put FakeData FakeDataDir,
使用命令查看HDFS下的文件:hadoop dfs –ls
![](http://static.oschina.net/uploads/space/2013/1201/222814_byee_1033344.png)
进入hive的所在路径,我的是/usr/local/hive/hive-0.12.0/bin,输入以下命令:
./hive
![](http://static.oschina.net/uploads/space/2013/1201/222828_hYxo_1033344.png)
创建外部表:
create external table if not exists fakedata(JULIAN_DATE string, PLATFORM string,…, LOG_ID double) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ location ‘/user/root/FakeDataDir’
注意由于LOG_ID字段是一个20位整随机数,所以使用double类型。
由于数据文件是由逗号分隔,所以TERMINATED BY ‘,’
最后定位数据文件所在位置,被放在HDFS路径/user/root/FakeDataDir下,注意location只要写文件所在目录即可。
这样就可以通过hive对数据文件进行查询了,如下图所示:
![](http://static.oschina.net/uploads/space/2013/1201/222841_jWE4_1033344.png)
3 安装Sqoop1.3.0并将数据从HDFS导入到Postgresql数据库:
首先需要在postgresql中创建表fakedata;然后在CentOS 6.4上安装Sqoop:
yum install sqoop
并且下载postgresql-9.3-1100.jdbc41.jar和hadoop-0.20.2-CDH3B4.tar.gz,放入sqoop所在路径的lib文件夹下/usr/lib/sqoop/lib。同时需要修改配置文件configure-sqoop,将hbase和zookeeper注释掉检查:(除非需要使用HBase等Hadoop组件)
![](http://static.oschina.net/uploads/space/2013/1201/222855_rp1w_1033344.png)
![](http://static.oschina.net/uploads/space/2013/1201/222905_5ofC_1033344.png)
并且确保sqoop所需的HADOOP_HOME正确指向hadoop安装目录,执行echo $HADOOP_HOME查看HADOOP_HOME是否正确配置。
然后使用命令将之前部署在HDFS上的数据文件导入postgresql:
./sqoop export --connect jdbc:postgresql://localhost:5432/postgres --username postgres --password 123456 --table fakedata--fields-terminated-by ',' --export-dir /user/root/FakeDataDir
这样就可以使用pgAdmin在postgresql中进行查询了。
需要注意的是,sqoop与hadoop可能会出现连接失败,提示:ident authenation failed for user postgres,这时需要修改postgresql的配置文件pg_hba,conf,修改postgresql的认证方式为trust:
local all all trust
host all all 127.0.0.1/32 trust
这样终于能够将postgresql连接到hadoop。
然后我对fakedata文件中的数据进行计算,并需要把计算结果导出到postgresql数据库中,按照如下步骤进行:
1、通过pgAdmin在postgresql中创建表,建表的字段和数据类型要符合计算结果的字段和数据类型。
2、然后在hive中创建相应的表作为存储计算结果的临时表。在浏览器输入localhost:50070,browse the filesystem,hive内部表默认位置在hive安装路径conf下的hive-site.xml中规定,设置为/user/hive/warehouse。输入下面命令建表:
create table success(pv bigint, uv bigint) row format delimited fields terminated by ‘,’;(不设置行分隔符默认为\001,是8进制的ASCII码序始字符SOH,start of header)
show tables;
desc success;
insert into success select sum(pv), count(distinct uv) from testdata group by julian_date;
3、然后通过sqoop将hive的表中的数据导出到postgresql数据库中:
./sqoop export –connect jdbc:postgresql://localhost:5432/postgres –username postgres –password 123456 –table testdata –fields-terminated-by ‘,’ –export-dir /user/hive/warehouse/success
注意:由于是初学者,我在实践过程中在分隔符上栽了大跟头,还是要注意这些命令的细节!这篇文档也不完全是自己完成的,当然要感谢提供我材料和解决方案的技术人员!!!