ITPub博客

首页 > Linux操作系统 > Linux操作系统 > CCU迁移

CCU迁移

原创 Linux操作系统 作者:skuary 时间:2011-05-11 12:48:18 0 删除 编辑
  • 对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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: DG建立及配置
下一篇: CCU ETL脚本列表
请登录后发表评论 登录
全部评论

注册时间:2011-03-31

  • 博文量
    88
  • 访问量
    316880