Greenplum数据库管理 - 创建SSB演示系统

本示例在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/