CCU遷移

skuary發表於2011-05-11
  • 對sql server裡的資料做BCP匯出工作:在命令列下進行操作：
    • 關於ACCU的匯出 
bcp "select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.ACCU_Daily_2007 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.ACCU_Daily_2008 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.ACCU_Daily_2009 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg  from GEKPI.dbo.ACCU_Daily_2010 union all select 'FIFA' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,null,val,val_avg from FIFA_KPI.dbo.ACCU_Daily_2009 union all select 'FIFA' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,null,val,val_avg from FIFA_KPI.dbo.ACCU_Daily_2010 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.ACCU_daily_2007 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.ACCU_daily_2008 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.ACCU_daily_2009 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.ACCU_daily_2010 union all select '9Z' AS site_cd,convert(varchar(10),dat,120),cast(site as varchar(6)) as site_id,null,accu,case when accuAvg is NULL then 0 else accuAvg end from [9Z_KPI].dbo.Nw_CharCount2009 union all select '9Z' AS site_cd,convert(varchar(10),dat,120),cast(site as varchar(6)) as site_id,null,accu,case when accuAvg is NULL then 0 else accuAvg end from [9Z_KPI].dbo.Nw_CharCount2010" queryout c:\test1.bat -c -T
匯出了64181條資料。
**關於PCCU的匯出
bcp "select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.PCCU_Daily_2007 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.PCCU_Daily_2008 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from GEKPI.dbo.PCCU_Daily_2009 union all select 'GE' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg  from GEKPI.dbo.PCCU_Daily_2010 union all select 'FIFA' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,null,val,val_avg from FIFA_KPI.dbo.PCCU_Daily_2009 union all select 'FIFA' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,null,val,val_avg from FIFA_KPI.dbo.PCCU_Daily_2010 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.PCCU_daily_2007 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.PCCU_daily_2008 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.PCCU_daily_2009 union all select 'SUN' AS site_cd,convert(varchar(10),dt,120),cast(site as varchar(6)) as site_id,cast(world as varchar(4)) as group_id,val,val_avg from sun_kpi.dbo.PCCU_daily_2010 union all select '9Z' AS site_cd,convert(varchar(10),dat,120),cast(site as varchar(6)) as site_id,null,pccu,0 from [9Z_KPI].dbo.Nw_CharCount2009 union all select '9Z' AS site_cd,convert(varchar(10),dat,120),cast(site as varchar(6)) as site_id,null,pccu,0 from [9Z_KPI].dbo.Nw_CharCount2010" queryout c:\test2.bat -c -T

匯出了64181條資料。

  • 建立分割槽表
    • ACCU指令碼： 
CREATE TABLE ACCU_Daily_His
(
site_cd CHAR(5),
create_ts DATE,
site_id CHAR(4),
GROUP_ID VARCHAR(4),
accu_val NUMBER(10),
accu_val_avg NUMBER(10)
)
PARTITION BY RANGE(create_ts)
( 
	PARTITION ACCU_Daily_His_before_2004 VALUES less than (TO_DATE('20040101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2004 VALUES less than (TO_DATE('20050101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2005 VALUES less than (TO_DATE('20060101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2006 VALUES less than (TO_DATE('20070101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2007 VALUES less than (TO_DATE('20080101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2008 VALUES less than (TO_DATE('20090101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2009 VALUES less than (TO_DATE('20100101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2010 VALUES less than (TO_DATE('20110101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2011 VALUES less than (TO_DATE('20120101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2012 VALUES less than (TO_DATE('20130101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2013 VALUES less than (TO_DATE('20140101','yyyymmdd')),
	PARTITION ACCU_Daily_His_2014 VALUES less than (TO_DATE('20150101','yyyymmdd'))
);
    • PCCU指令碼： 
CREATE TABLE PCCU_Daily_His
(
site_cd CHAR(5),
create_ts DATE,
site_id CHAR(4),
GROUP_ID VARCHAR(4),
pccu_val NUMBER(10),
pccu_val_avg NUMBER(10)
)
PARTITION BY RANGE(create_ts)
(
 
        PARTITION p_PCCU_His_Before_2004 VALUES less than (TO_DATE('20040101','yyyymmdd')),
	PARTITION p_PCCU_His_2004 VALUES less than (TO_DATE('20050101','yyyymmdd')),
	PARTITION p_PCCU_His_2005 VALUES less than (TO_DATE('20060101','yyyymmdd')),
	PARTITION p_PCCU_His_2006 VALUES less than (TO_DATE('20070101','yyyymmdd')),
	PARTITION p_PCCU_His_2007 VALUES less than (TO_DATE('20080101','yyyymmdd')),
	PARTITION p_PCCU_His_2008 VALUES less than (TO_DATE('20090101','yyyymmdd')),
	PARTITION p_PCCU_His_2009 VALUES less than (TO_DATE('20100101','yyyymmdd')),
	PARTITION p_PCCU_His_2010 VALUES less than (TO_DATE('20110101','yyyymmdd')),
	PARTITION p_PCCU_His_2011 VALUES less than (TO_DATE('20120101','yyyymmdd')),
	PARTITION p_PCCU_His_2012 VALUES less than (TO_DATE('20130101','yyyymmdd')),
	PARTITION p_PCCU_His_2013 VALUES less than (TO_DATE('20140101','yyyymmdd')),
	PARTITION p_PCCU_His_2014 VALUES less than (TO_DATE('20150101','yyyymmdd'))
);
  • 取出test1.bat,test2.bat檔案放在10.127.16.15上，並在同一路徑建立控制檔案input1.ctl,input2.ctl。
    • input1.ctl指令碼： 
load data
infile 'test.bat'
append INTO TABLE KPI.ACCU_Daily_His
fields terminated BY X'09'
(site_cd,create_ts "to_date(:create_ts,'yyyy-mm-dd')",site_id,GROUP_ID,accu_val,accu_val_avg)
    • input2.ctl指令碼： 
load data
infile 'test1.bat'
append INTO TABLE KPI.PCCU_Daily_His
fields terminated BY X'09'
(site_cd,create_ts "to_date(:create_ts,'yyyy-mm-dd')",site_id,GROUP_ID,pccu_val,pccu_val_avg)
  • 匯入資料：

在命令列下同一路徑下： 

sqlldr KPI/kpigamenow@orads control=input1.ctl
sqlldr KPI/kpigamenow@orads control=input2.ctl

這樣就OK了。 PS: 建表的時候 site_id為char(4)需要在匯入資料前修改型別為 varchar(6)。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25618347/viewspace-713876/，如需轉載，請註明出處，否則將追究法律責任。

相關文章