mysql多维数据仓库指南--第一篇第1章（续）

出处：http://blog.chinaunix.net/uid-43642-id-2124572.html

第3节 任务

 

       有四个任务需要在本章完成：

1．  建立一个数据库用户。

2．建立两个关系数据库，一个作为数据仓库，另外一个作为源数据库。

3．为数据仓库建立数据库表。

4．产生代理键。

每个任务将在独立的小节中说明。

建立一个数据库用户id

       第一步产生一个数据库用户，你可以用该帐户进行操作数据仓库和数据源。

/*****************************************************************/                                                             *//* create_user_id.sql                                            *//*                                                               *//*****************************************************************/GRANT ALL ON *.* TO dwid@localhost IDENTIFIED BY 'pw';/* end of script   

在你开始前，确信你已经将本书随附的脚本文件放置在mysql的安装目录下面。例如，我的安装目录是C:\mysql,所以我将我的脚本文件放在C:\mysql\scripts目录中。

       我们开始将用root用户登陆mysql，请输入下面的命令：

C:\>mysql -uroot -p

你将被提示输入密码：

 Enter password: ********

输入密码后，将看到控制台的欢迎信息：

Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6 to server version: 5.0.21-community-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

 

敲入密码后的这些信息是当一个root用户登陆mysql时典型的回应信息。列表1-1 所示的脚本create_user_id.sql产生一个用户id dwid 其密码是 pw 。

列表1-1: 创建dwid 用户 id

/*****************************************************************/                                                             *//* create_user_id.sql                                            *//*                                                               *//*****************************************************************/GRANT ALL ON *.* TO dwid@localhost IDENTIFIED BY 'pw';/* end of script   

用下面的命令方式运行该脚本：

mysql> \. c:\mysql\scripts\create_user_id.sql

回车后，你将看到回应的信息：

Query OK, 0 rows affected (0.03 sec)

用show grants命令，你可以确认用户id：dwid 是否已经被成功创建，你应该在命令中包含你的mysql服务器名称：

mysql> show grants for dwid@localhost;

假如存在用户 dwid，你可以看到如下信息：

Grants for dwid@localhost

GRANT ALL PRIVILEGES ON *.* TO 'dwid'@'localhost' IDENTIFIED BY

      PASSWORD '*D821809F681A40A6E379B50D0463EFAE20BDD122'

1 row in set (0.00 sec)

注意 密码部分在你的显示屏上显示的方式将不同于上面的描述。

现在你需要退出mysq然后用dwid用户登陆，退出mysql用exit命令：

mysql> exit

为了以dwid身份登陆mysql，可以用下面的命令：

c:\>mysql -udwid -p

 

然后输入dwid的密码，记住，密码是pw。

建立数据库

       有两个数据库需要建立，源数据（source）和数据仓库（dw）。Source数据库存储你的数据，也就是这些数据将成为你的数据仓库的源数据。Dw数据库是为了作为数据仓库建立的。

       可以用列表1-2中的create_databases.sql 脚本进行创建数据库。

列表 1.2: 建立 dw 和 source 数据库

/*****************************************************************//*                                                               *//* create_databases.sql                                          *//*                                                               *//*****************************************************************/CREATE DATABASE dw;CREATE DATABASE source

运行create_databases.sql脚本的命令如下：

mysql> \. c:\mysql\scripts\create_databases.sql

在你的控制台界面上，你将看到：

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

 

你可以用show databases命令来确认上面的命令是否成功运行。 为了确认dw数据库被创建用下面命令：

mysql> show databases like 'dw';

 

回应信息将如下面所示：

+---------------+

| Database (dw) |

+---------------+

| dw            |

+---------------+

1 row in set (0.00 sec)

 

为了确认source数据库被成功创建用下面命令：

mysql> show databases like 'source';

 

回应信息将如下面所示：

+-------------------+

| Database (source) |

+-------------------+

| source            |

+-------------------+

1 row in set (0.00 sec)

创建数据仓库表

     第二步是在dw数据库中创建数据仓库表。你可以用列表1-3所示的create_dw_tables.sql脚本来创建图1-1中的sales_order_fact表和四个维表。

列表1-3:创建数据仓库表

/*****************************************************************//*                                                               *//* create_dw_tables.sql                                          *//*                                                               *//*****************************************************************//* default to dw database                                        */ USE dw; /* creating customer_dim table                                   */ CREATE TABLE customer_dim( customer_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, customer_number INT, customer_name CHAR (50), customer_street_address CHAR (50), customer_zip_code INT (5), customer_city CHAR (30), customer_state CHAR (2), effective_date DATE, expiry_date DATE ); /* creating product_dim table                                    */CREATE TABLE product_dim( product_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, product_code INT, product_name CHAR (30), product_category CHAR (30), effective_date DATE, expiry_date DATE ); /* creating order_dim table                                      */CREATE TABLE order_dim( order_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, order_number INT, effective_date DATE, expiry_date DATE ); /* creating date_dim table                                       */CREATE TABLE date_dim( date_sk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date DATE, month_name CHAR (9), month INT (1), quarter INT (1), year INT (4), effective_date DATE, expiry_date DATE ); /* creating sales_order_fact_table                               */CREATE TABLE sales_order_fact( order_sk INT, customer_sk INT, product_sk INT, order_date_sk INT, order_amount DECIMAL (10, 2) );

现在运行create_dw_tables.sql脚本：

mysql> \. c:\mysql\scripts\create_dw_tables.sql

你的控制台将显示如下类似的信息：

Database changed

Query OK, 0 rows affected (0.13 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.12 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.11 sec)

你可以用show create table命令确认某个表是否被创建成功，例如，为了验证customer_dim表是否创建成功，用这个命令：

mysql> show create table customer_dim \G

在你的控制台界面上，将可以看到：

*************************** 1. row ***************************       Table: customer_dimCreate Table: CREATE TABLE 'customer_dim' (  'customer_sk' int(11) NOT NULL auto_increment,  'customer_number' int(11) default NULL,  'customer_name' char (50) default NULL,  'customer_street_address' char (50) default NULL,  'customer_zip_code' int (5) default NULL,  'customer_city' char (30) default NULL,  'customer_state' char (2) default NULL,  'effective_date' date default NULL,  'expiry_date' date default NULL,  PRIMARY KEY ('customer_sk')) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

用相同的方式，可以确认其他表是否成功创建。

产生代理键

     本节的最后一个任务是用列表1-4所示的customer_sk.sql脚本创建代理键，这个脚本将向customer_dim表插入3条记录。 

列表 1-4: 产生 客户代理键值

/******************************************************************//*                                                                *//* customer_sk.sql                                                *//*                                                                *//******************************************************************//* default to dw                                                  */ USE dw; INSERT INTO customer_dim( customer_sk, customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, effective_date, expiry_date )VALUES  (NULL, 1, 'Big Customers', '7500 Louise Dr.', '17050',       'Mechanicsburg', 'PA', CURRENT_DATE, '9999-12-31'), (NULL, 2, 'Small Stores', '2500 Woodland St.', '17055',       'Pittsburgh', 'PA', CURRENT_DATE, '9999-12-31'), (NULL, 3, 'Medium Retailers', '1111 Ritter Rd.', '17055'       'Pittsburgh', 'PA', CURRENT_DATE, '9999-12-31'); /* end of script 

运行customer_sk.sql脚本之前，你必须将你的mysql时间设置为：2007-02-01,这是因为customer_sk.sql脚本中的CURRENT_DATE函数用操作系统的时间作为载入effective_date字段的值,而我就是在这个时间运行customer_sk.sql脚本的。你可以通过将mysql运行的机器的系统的时间修改为特定的值来修改mysql时间。

      注意要牢记，这里你改变mysql的时间只是为了能比较平滑结合这个学习教程，实际的生产环境中，你不能在运行脚本的前后改变你的数据库或者系统的时间。事实上，你应该在数据库仓库环境中安排部署你的各个脚本定期的运行。

如何定期安排将在第8节“定期载入”中讨论。

     注意你的mysql监控端将在系统时间被修改后断开和mysql服务端的连接，为了确保你运行脚本不会有什么问题，在调整时间后，试着运行一下诸如“use dw”的简单的命令，你将得到一个错误的信息提示，但是你的监控端将重新连接上mysql。你可以再次运行“use dw”命令来确定已经连接上myslq服务器，这次，你不会再得到任何错误提示了。

        现在，你已经将mysql的时间调整到2007-02-01，可以用如下的命令运行列表1-4中所示的脚本，

        mysql> \. c:\mysql\scripts\customer_sk.sql

你将在你的控制台上看到如下信息：

Database changed

Query OK, 3 rows affected (0.06 sec)

Records: 3  Duplicates: 0  Warnings: 0

查询该表，你可以看到这个脚本已经准确的插入代理键值了。

mysql> select * from customer_dim \G

*************************** 1. row ***************************

            customer_sk: 1

        customer_number: 1

          customer_name: Big Customers

customer_street_address: 7500 Louise Dr.

      customer_zip_code: 17050

          customer_city: Mechanicsburg

         customer_state: PA

         effective_date: 2007-02-01

            expiry_date: 9999-12-31

*************************** 2. row ***************************

            customer_sk: 2

        customer_number: 2

          customer_name: Small Stores

customer_street_address: 2500 Woodland St.

      customer_zip_code: 17055

          customer_city: Pittsburgh

         customer_state: PA

         effective_date: 2007-02-01

            expiry_date: 9999-12-31

*************************** 3. row ***************************

            customer_sk: 3

        customer_number: 3

          customer_name: Medium Retailers

customer_street_address: 1111 Ritter Rd.

      customer_zip_code: 17055

          customer_city: Pittsburgh

         customer_state: PA

         effective_date: 2007-02-01

            expiry_date: 9999-12-31

3 rows in set (0.00 sec)

 

mysql>

 

现在运行列表1-5中的脚本more_customer_sk.sq来增加更多的行。

 

列表 1-5: 插入更多客户资料

/*****************************************************************//*                                                               *//* more_customer_sk.sql                                          *//*                                                               *//*****************************************************************/USE dw; INSERT INTO customer_dim (  customer_sk, customer_number, customer_name, customer_street_address, customer_zip_code, customer_city, customer_state, effective_date, expiry_date)VALUES  (NULL, 4, 'Good Companies', '9500 Scott St.', '17050',       'Mechanicsburg', 'PA', CURRENT_DATE, '9999-12-31'), (NULL, 5, 'Wonderful Shops', '3333 Rossmoyne Rd.', '17050',      'Mechanicsburg', 'PA', CURRENT_DATE, '9999-12-31'), (NULL, 6, 'Loyal Clients', '7070 Ritter Rd.', '17055',       'Pittsburgh', 'PA', CURRENT_DATE, '9999-12-31');

下面说明如何运行more_customer_sk.sql 脚本

mysql> \. c:\mysql\scripts\more_customer_sk.sql

你将看到：

Database changed

Query OK, 3 rows affected (0.06 sec)

Records: 3  Duplicates: 0  Warnings: 0

如果你查询customer_dim表，将发现有6条记录在该表中：

mysql> select * from customer_dim \G*************************** 1. row ***************************            customer_sk: 1        customer_number: 1          customer_name: Big Customerscustomer_street_address: 7500 Louise Dr.      customer_zip_code: 17050          customer_city: Mechanicsburg         customer_state: PA         effective_date: 2007-02-01            expiry_date: 9999-12-31*************************** 2. row ***************************            customer_sk: 2        customer_number: 2          customer_name: Small Storescustomer_street_address: 2500 Woodland St.      customer_zip_code: 17055          customer_city: Pittsburgh         customer_state: PA         effective_date: 2007-02-01            expiry_date: 9999-12-31*************************** 3 row ***************************            customer_sk: 3        customer_number: 3          customer_name: Medium Retailerscustomer_street_address: 1111 Ritter Rd.      customer_zip_code: 17055          customer_city: Pittsburgh         customer_state: PA         effective_date: 2007-02-01            expiry_date: 9999-12-31*************************** 4. row ***************************            customer_sk: 4        customer_number: 4          customer_name: Good Companiescustomer_street_address: 9500 Scott St.      customer_zip_code: 17050          customer_city: Mechanicsburg         customer_state: PA         effective_date: 2007-02-01            expiry_date: 9999-12-31*************************** 5. row ***************************            customer_sk: 5        customer_number: 5          customer_name: Wonderful Shopscustomer_street_address: 3333 Rossmoyne Rd.      customer_zip_code: 17050          customer_city: Mechanicsburg         customer_state: PA         effective_date: 2007-02-01            expiry_date: 9999-12-31*************************** 6. row ***************************            customer_sk: 6        customer_number: 6          customer_name: Loyal Clientscustomer_street_address: 7070 Ritter Rd.      customer_zip_code: 17055          customer_city: Pittsburgh         customer_state: PA         effective_date: 2007-02-01            expiry_date: 9999-12-316 rows in set (0.01 sec)

不要删除这些客户记录，在下个章节中，你将用到它们。

第4节 小结

       这一个节，你学习了星型模式和代理键。你还建立两个数据库以及操作这两个数据库的mysql用户。下面的章节，你将使用和扩展这些数据库。