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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值