本示例在Greenplum数据库里创建SSB演示系统
一、首先创建角色role:
create role ssb with password 'ssb' login createdb;
二、修改ssb用户登录权限:
进入master的数据目录
修改登录权限:
vi /data/gpmaster/gpseg-1/pg_hba.conf
添加一行(请根据自己客户端的网段设置)
host all ssb 10.10.1.11/32 trust
gpstop -u ,重新load该配置文件。
再以ssb用户身份登录数据库
psql -d template1 -U ssb -h mdw
三、创建数据库:
Create database ssb;
登陆ssb数据库:
psql -d ssb -U ssb -h mdw
四、创建ssb表
drop table lineorder;
create table lineorder (
lo_orderkey decimal(24),
lo_linenumber int,
lo_custkey int,
lo_partkey int,
lo_suppkey int,
lo_orderdate int,
lo_orderpriority varchar(15),
lo_shippriority varchar(1),
lo_quantity int,
lo_extendedprice int,
lo_ordtotalprice int,
lo_discount int,
lo_revenue int,
lo_supplycost int,
lo_tax int,
lo_commitdate int,
lo_shipmode varchar(10)
) ;
drop table if exists part;
create table part (
p_partkey decimal(24),
p_name varchar(22),
p_mfgr varchar(6) ,
p_category varchar(7) ,
p_brand1 varchar(9) ,
p_color varchar(11) ,
p_type varchar(25) ,
p_size int,
p_container varchar(15)
) ;
drop table if exists supplier;
create table supplier (
s_suppkey int,
s_name varchar(25),
s_address varchar(25),
s_city varchar(10) ,
s_nation varchar(15) ,
s_region varchar(12) ,
s_phone varchar(15)
) ;
drop table if exists customer;
create table customer (
c_custkey decimal(24),
c_name varchar(25),
c_address varchar(25),
c_city varchar(10),
c_nation varchar(15),
c_region varchar(12),
c_phone varchar(15),
c_mktsegment varchar(10)
) ;
drop table if exists dwdate;
create table dwdate (
d_datekey int,
d_date varchar(18),
d_dayofweek varchar(9),
d_month varchar(9),
d_year int,
d_yearmonthnum int,
d_yearmonth varchar(7),
d_daynuminweek int,
d_daynuminmonth int,
d_daynuminyear int,
d_monthnuminyear int,
d_weeknuminyear int,
d_sellingseason varchar(12),
d_lastdayinweekfl int,
d_lastdayinmonthfl int,
d_holidayfl int,
d_weekdayfl int
) ;
四、启动gpfdist服务
nohup /usr/local/greenplum-db/bin/gpfdist -d /home/gpadmin/ssb_data -p 8081 -l gpfdist.log &
五、创建外部表
赋予ssb用户创建外部表权限:
要允许用户创建外部表,否则建外部表时会得到错误
ERROR: permission denied: no privilege to create a readable gpfdist external table
修改配置文件/opt/gp/data/master/gpseg-1/ postgresql.conf,添加参数
gp_external_enable_exec = on # enable external tables with EXECUTE.
gp_external_grant_privileges = on #enable create http/gpfdist for non su's
允许非超级管理员创建外部表,必须重启数据库服务(使用gpstop –u命令该参数文件修改不生效),才能生效。
创建外部表:
create external table lineorder_ex (
lo_orderkey decimal(24),
lo_linenumber int,
lo_custkey int,
lo_partkey int,
lo_suppkey int,
lo_orderdate int,
lo_orderpriority varchar(15),
lo_shippriority varchar(1),
lo_quantity int,
lo_extendedprice int,
lo_ordtotalprice int,
lo_discount int,
lo_revenue int,
lo_supplycost int,
lo_tax int,
lo_commitdate int,
lo_shipmode varchar(10)
)
LOCATION ('gpfdist://10.10.1.11:8081/lineorder/*')
FORMAT 'TEXT' (DELIMITER '|');
create external table part_ex (
p_partkey decimal(24),
p_name varchar(22),
p_mfgr varchar(6) ,
p_category varchar(7) ,
p_brand1 varchar(9) ,
p_color varchar(11) ,
p_type varchar(25) ,
p_size int,
p_container varchar(15)
)
LOCATION ('gpfdist://10.10.1.11:8081/part/*')
FORMAT 'TEXT' (DELIMITER '|');
create external table supplier_ex (
s_suppkey int,
s_name varchar(25),
s_address varchar(25),
s_city varchar(10) ,
s_nation varchar(15) ,
s_region varchar(12) ,
s_phone varchar(15)
)
LOCATION ('gpfdist://10.10.1.11:8081/supplier/*')
FORMAT 'TEXT' (DELIMITER '|');
create external table customer_ex (
c_custkey decimal(24),
c_name varchar(25),
c_address varchar(25),
c_city varchar(10),
c_nation varchar(15),
c_region varchar(12),
c_phone varchar(15),
c_mktsegment varchar(10)
)
LOCATION ('gpfdist://10.10.1.11:8081/customer/*')
FORMAT 'TEXT' (DELIMITER '|');
create external table dwdate_ex (
d_datekey int,
d_date varchar(18),
d_dayofweek varchar(9),
d_month varchar(9),
d_year int,
d_yearmonthnum int,
d_yearmonth varchar(7),
d_daynuminweek int,
d_daynuminmonth int,
d_daynuminyear int,
d_monthnuminyear int,
d_weeknuminyear int,
d_sellingseason varchar(12),
d_lastdayinweekfl int,
d_lastdayinmonthfl int,
d_holidayfl int,
d_weekdayfl int
)
LOCATION ('gpfdist://10.10.1.11:8081/dwdate/*')
FORMAT 'TEXT' (DELIMITER '|');
六、使用子查询加载数据
insert into lineorder select * from lineorder_ex;
insert into part select * from part_ex;
insert into customer select * from customer_ex;
insert into dwdate select * from dwdate _ex;
insert into supplier select * from supplier_ex;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7220098/viewspace-1135375/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7220098/viewspace-1135375/