构建一个IQ学习数据库(3)_全文完

5.装载数据
   在成功创建表之后，需要装载数据。装载数据load_tables.sql脚本如下：

/* ============================================================ */
/*   load_tables.sql                                            */
/*   Megaphone 2.1.2 Demo Database    */
/* ============================================================ */

begin
  declare startDate datetime;
  declare endDate datetime;
  set startDate=getDate();
 
  Load Table customer
  (   customer_key              '|',
    customer_first_name         '|',
    customer_last_name          '|',
    customer_gender             '|',
    street_address              '|',
    city                        '|',
    state                       '|',
    postal_code                 '|',
    phone_number                '|'  )
  FROM '/sybiqdb/mp2/load_data/customer.dat'
  escapes off
  quotes off
  Notify 10000
  ROW DELIMITED BY '\x0a'
  WITH CHECKPOINT ON;
  COMMIT;

  set endDate=getDate();
  message '[load customer command execute time is : ',datediff(ms,startDate,endDate),'ms]' type info to client;
end;

Load Table month
(   month_key        '|',
    month_text       '|',
    month_number     '|',
    fiscal_period    '|',
    year             '|',
    period_and_year  '|',
    month_and_year   '\x0a')
FROM '/sybiqdb/mp2/load_data/month.dat'
escapes off
quotes off
Notify 100
WITH CHECKPOINT ON;
COMMIT;

Load Table service
(   service_key   '|',
    call_waiting_flag  '|',
    caller_id_flag  '|',
    voice_mail_flag  '|',
    cellular_flag  '|',
    internet_flag  '|',
    isdn_flag   '\x0a'  )
FROM '/sybiqdb/mp2/load_data/service.dat'
escapes off
quotes off
Notify 10000
WITH CHECKPOINT ON;
COMMIT;

Load Table status
(   status_key   '|',
    new_customer  '|',
    new_address   '|',
    call_waiting_status  '|',
    caller_id_status  '|',
    voice_mail_status           '|',
    cellular_status  '|',
    internet_status  '|',
    isdn_status   '|',
    closed_this_period '\x0a'  )
FROM '/sybiqdb/mp2/load_data/status.dat'
escapes off
quotes off
Notify 100
WITH CHECKPOINT ON;
COMMIT;

begin
  declare startDate datetime;
  declare endDate datetime;
  set startDate=getDate();
 
  Load Table telco_facts
  (   month_key   '|',
    customer_key  '|',
    service_key   '|',
    status_key   '|',
    combined_revenue  '|',
    number_of_lines  '|',
    local_call_count  '|',
    local_call_minutes  '|',
    long_distance_call_count '|',
    long_distance_call_minutes '\x0a' )
  FROM
   '/sybiqdb/12.7/mp2/load_data/fact_jan98.dat',
   '/sybiqdb/12.7/mp2/load_data/fact_feb98.dat',
   '/sybiqdb/12.7/mp2/load_data/fact_mar98.dat'
  escapes off
  quotes off
  Notify 500000
  WITH CHECKPOINT ON;
  COMMIT;

  set endDate=getDate();
  message '[load telco_facts command execute time is : ',datediff(ms,startDate,endDate),'ms]' type info to client;
end;

 

   说明：

   （1）数据文件可以从如下地址下载：

       

 

   （2）load语句中的数据文件路径需要根据自己机器的情况进行修改。

 

   （3）大家可以体会一下IQ的装载速度，load速度快是IQ的一个优势。