ITPub博客

首页 > 大数据 > 数据挖掘 > Greenplum数据库管理 - 创建SSB演示系统

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

原创 数据挖掘 作者:dodd 时间:2014-04-03 13:35:18 0 删除 编辑

本示例在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-22

  • 博文量
    46
  • 访问量
    162841