bloomberg bulkfile 在oracle的存储

文章导航

一表名和字段名称的命名规则

1.1. 表名以文件名称直接命名，将文件名中的"."用“_"代替。

如《fundamentals_namr_af_history.out》对应表名为：FUNDAMENTALS_NAMR_AF_HISTORY_OUT

1.2. 字段中特殊字段的处理规则

去接去除字段中的"&","%","/" 例外情况：对于最后一个"%" 用"PRE"替换.因为它代表百分比的含义,bb文件中会同时有xxx和xxx_%这样的field它们是不同的字段。

将"-" 替换为"_"

1.3. 以数字开头字段的字段处理规则

在字段前面加上”H_"

1.4.将连续的"_"只保留一个,且去除开头和结尾的"_"

如 "__ab__cd_" 替换为:"ab_cd"

1.5.长度截取,前24位+后6位

当表名或字段名称处理后长度仍超出30（oracle的最大限制).按规则截取：前面取24位，后面取6位。

如表名：FUNDAMENT ALS_NAMR_AF_HISTORY_OUT 截取后的表名为：FUNDAMENTALS_NAMR_AF_HISRY_OUT

二创建表结构

2.1. 导入bulkfile文件解析字段

根据bloomberg 下载的文件中 START-OF-FIELDS END-OF-FIELDS 之间的部分即为表的列名，将文件名按命名规则存储为表名。解析出来的表名和字段及字段顺序最终会存在BB_TABLES中的BB_table_name,bb_field_name,field_order中

CREATE TABLE BB_TABLES(
TABLE_NAME varchar2(200), --表名(oracle)
FIELD_NAME varchar2(200),  --字段名(oracle)
BB_field_Name varchar2(200), --bloomberg的字段名称
BB_table_name varchar2(200), --bloomberg的表名(对应名称的名称. .xxx替换为_xxx)
FIELD_ORDER number(4) --字段的排序（要与文件中的顺序相同)
)

将解析出来的数据先临时导入到BB_TABLES 的bb_table_name及bb_field_name中

2.2 导入《Harvest Bulk File Field Layout.xlsx》中的字段信息

2.2.1 导入sheet:5115-part I

sheet 内容如下：

将所有的.cax文件对应的表名，与"Bloomberg Field"列的值做笛卡尔积，导入BB_TABLES 的bb_table_name、bb_field_name .字段的顺序导入到 field_order。

导入时将"Bloomberg Field"列空格替换为"_".去掉"<" 、 ">"

2.2.2 导入sheet:5115-part II

sheet 部分内容截图如下：

将所有的_CAX 结尾的表与”Action Mnemonic“和” FieldMnemonic“ 做笛卡尔积、拼接成新的表及field。

如表xxx_cax 与 ACQUIS 拼接成：

Action Mnemonic 做为bb_table_name,FieldMnemonic 做为bb_field_name .字段的顺序做为field_order 导入到BB_TABLES中。

2.2.根据bb_table_name、bb_field_name生成table_name、field_name

update bb_tables set table_name = BB_table_name;
update bb_tables set table_name = replace(table_name,'%','') where instr(table_name,'%')>0;
update bb_tables set table_name = replace(table_name,'/','') where instr(table_name,'/')>0;
update bb_tables set table_name = replace(table_name,'&','') where instr(table_name,'&')>0;
update bb_tables set table_name = replace(table_name,'-','__')where instr(table_name,'-')>0;
update bb_tables set table_name = replace(table_name,'.','_') where instr(table_name,'.')>0;
update bb_tables set table_name = replace(table_name,'__','_') where instr(table_name,'__')>0; --执行2次，多点没关系
update bb_tables set table_name = substr(table_name,2) where substr(table_name,1,1)='_'; --去掉开头的下划线
update bb_tables set table_name = substr(table_name,1,length(table_name)-1) where substr(table_name,-1)='_'; --去掉结尾的下划线
update bb_tables set table_name ='H_'||table_name where regexp_instr(table_name,'[0-9]')=1; --以数字开头的前面加上"H_"
update bb_tables set table_name = substr(table_name,1,24)||substr(table_name,-6) where length(table_name)>30;
 
select distinct table_name,bb_table_name from bb_tables where table_name<>bb_table_name; --检查一下是否修改正确
 
update bb_tables set field_name = BB_field_name;
update bb_tables set field_name = substr(field_name,1,length(field_name)-1)||'PRE' where substr(field_name,-1,1)='%'; --最后一个%用pre代替。
update bb_tables set field_name = 'PRE'||substr(field_name,2) where substr(field_name,1,1)='%'; --第一个%用pre代替。
update bb_tables set field_name = replace(field_name,'%','') where instr(field_name,'%')>0;
update bb_tables set field_name = replace(field_name,'#','') where instr(field_name,'#')>0;
update bb_tables set field_name = replace(field_name,'/','') where instr(field_name,'/')>0; 
update bb_tables set field_name = replace(field_name,'&','') where instr(field_name,'&')>0; 
update bb_tables set field_name = replace(field_name,'-','__')where instr(field_name,'-')>0; 
update bb_tables set field_name = replace(field_name,'__','_') where instr(field_name,'__')>0; --执行2次，多点没关系 
update bb_tables set field_name = substr(field_name,2) where substr(field_name,1,1)='_'; --去掉开头的下划线 
update bb_tables set field_name = substr(field_name,1,length(field_name)-1) where substr(field_name,-1)='_'; --去掉结尾的下划线 
update bb_tables set field_name ='H_'||field_name where regexp_instr(field_name,'[0-9]')=1; --以数字开头的前面加上"H_" 
update bb_tables set field_name = substr(field_name,1,24)||substr(field_name,-6) where length(field_name)>30; 
select distinct field_name,BB_field_name from bb_tables where field_name<>BB_field_name; --检查一下是否修改正确

2.3 手工修改一下会重复的表名

--检查重复的表名
select distinct bb_table_name,table_name from bb_tables
where table_name in (
select table_name from bb_tables
group by table_name
having count(distinct bb_table_name)>1
)
order by 2;

update bb_tables set table_name='FUNDAMENTALS_ASIA2_BSPIT_OUT' where bb_table_name='FUNDAMENTALS_ASIA2_BS_PIT_OUT';
update bb_tables set table_name='FUNDAMENTALS_ASIA2_SARD_OUT2' where bb_table_name='FUNDAMENTALS_ASIA2_SARD_CF_PIT_OUT';
update bb_tables set table_name='FUNDAMENTALS_ASIA2_SBP_OUT' where bb_table_name='FUNDAMENTALS_ASIA2_SARD_BS_PIT_OUT';
update bb_tables set table_name='FUNDAMENTALS_NAMR_SBHP_OUT' where bb_table_name='FUNDAMENTALS_NAMR_SARD_BS_HISTORY_PIT_OUT';
update bb_tables set table_name='FUNDAMENTALS_NAMR_SCHP_OUT' where bb_table_name='FUNDAMENTALS_NAMR_SARD_CF_HISTORY_PIT_OUT';
update bb_tables set table_name='FUNDAMENTALS_NAMR_SIHP_OUT' where bb_table_name='FUNDAMENTALS_NAMR_SARD_IS_HISTORY_PIT_OUT';
update bb_tables set table_name='EQUITYASIA1_CAX_EQYOFFER' where bb_table_name='EQUITYASIA1CORPORATEACTIONSV2_CAX.EQY_OFFER';
update bb_tables set table_name='EQUITYASIA2_CAX_EQYOFFER' where bb_table_name='EQUITYASIA2CORPORATEACTIONSV2_CAX.EQY_OFFER';
update bb_tables set table_name='EQUITYEURO2_CAX_EQYOFFER' where bb_table_name='EQUITYEUROCORPORATEACTIONSV2_CAX.EQY_OFFER';
update bb_tables set table_name='EQUITYLAMR2_CAX_EQYOFFER' where bb_table_name='EQUITYLAMRCORPORATEACTIONSV2_CAX.EQY_OFFER';
update bb_tables set table_name='EQUITYNAMR2_CAX_EQYOFFER' where bb_table_name='EQUITYNAMRCORPORATEACTIONSV2_CAX.EQY_OFFER';
update bb_tables set table_name='FUNDAMENTALS_ASIA1_SCP_OUT' where bb_table_name='FUNDAMENTALS_ASIA1_SARD_CF_PIT_OUT'; 
update bb_tables set table_name='FUNDAMENTALS_ASIA2_IHP_OUT' where bb_table_name='FUNDAMENTALS_ASIA2_IND1_HISTORY_PIT_OUT'; 
update bb_tables set table_name='FUNDAMENTALS_ASIA2_SBH_OUT' where bb_table_name='FUNDAMENTALS_ASIA2_SARD_BS_HISTORY_OUT'; 
update bb_tables set table_name='FUNDAMENTALS_ASIA2_SCH_OUT' where bb_table_name='FUNDAMENTALS_ASIA2_SARD_CF_HISTORY_OUT';

三. 字段类型导入

3.1 将bloomberg提供的《fields.csv》中的内容导入到oracke中。

表名为bb_fields,额外增加三个列:

oracle_type varchar2(50),  --对应到oracle中的类型
oracle_field_name varchar2(50), --对应到oracle的字段名称
bb_type_src varchar2(100) --这些字段的导入源

其中 bb_type_src='fields.csv'

3.2 将bloomberg提供的《Harvest Bulk File Field Layout.xlsx》中的内容导入到bb_fields中

仅需要导入《5115-part I》《5115-part 2》两个sheet中的内容.导入后需要过滤一下同名的field

3.3 构建bb_types对应到oracle中的字段名称和字段类型

根据 bb_fields中的 filed_type、standard_width、standard_decimal_places 来构造出oracle_type.

update bb_fields set oracle_type='Real' where bb_type_src='fields.csv' and field_type='Real';
update bb_fields set oracle_type='number(30,6)' where bb_type_src='fields.csv' and field_type='Price';
update bb_fields set oracle_type='varchar2(20)' where bb_type_src='fields.csv' and field_type='Time';
update bb_fields set oracle_type='varchar2(30)' where bb_type_src='fields.csv' and field_type='Month/Year';
update bb_fields set oracle_type='CLOB' where bb_type_src='fields.csv' and field_type='Bulk Format';
update bb_fields set oracle_type='CLOB' where bb_type_src='fields.csv' and field_type='Long Character';
update bb_fields set oracle_type='number(4)' where bb_type_src='fields.csv' and field_type='Boolean';
update bb_fields set oracle_type='number(10)' where bb_type_src='fields.csv' and field_type='Integer';
update bb_fields set oracle_type='DATE' where bb_type_src='fields.csv' and field_type='Date';
update bb_fields set oracle_type=(case when CURRENT_MAXIMUM_WIDTH>2000 then 'CLOB' else 'VARCHAR2('||CURRENT_MAXIMUM_WIDTH||')' end) where bb_type_src='fields.csv' and field_type='Character';
update bb_fields set oracle_type='varchar2(30)' where bb_type_src='fields.csv' and field_type='Date or Time';
update bb_fields set oracle_type='rela' where bb_type_src='fields.csv' and field_type='Integer/Real';

然后再根据命名规则，生成oracle_field_name

update bb_fields set oracle_field_name = field_mnemonic 
update bb_fields set oracle_field_name = substr(oracle_field_name,1,length(oracle_field_name)-1)||'PRE' where substr(oracle_field_name,-1,1)='%'; --最后一个%用pre代替。
update bb_fields set oracle_field_name = replace(oracle_field_name,'%','') where instr(oracle_field_name,'%')>0;
update bb_fields set oracle_field_name = replace(oracle_field_name,'/','') where instr(oracle_field_name,'/')>0;
update bb_fields set oracle_field_name = replace(oracle_field_name,'&','') where instr(oracle_field_name,'&')>0;
update bb_fields set oracle_field_name = replace(oracle_field_name,'-','_') where instr(oracle_field_name,'-')>0;
update bb_fields set oracle_field_name = replace(oracle_field_name,'__','_') where instr(oracle_field_name,'__')>0;
update bb_fields set oracle_field_name = substr(oracle_field_name,2) where substr(oracle_field_name,1,1)='_';
update bb_fields set oracle_field_name = substr(oracle_field_name,1,length(oracle_field_name)-1) where substr(oracle_field_name,-1)='_';
update bb_fields set oracle_field_name ='H_'||oracle_field_name where regexp_instr(oracle_field_name,'[0-9]')=1; --以数字开头的前面加上"H_"
update bb_fields set oracle_field_name = substr(oracle_field_name,1,24)||substr(oracle_field_name,-6) where length(oracle_field_name)>30;

select oracle_field_name,field_mnemonic from bb_fields where oracle_field_name<>field_mnemonic;

3.4 检查一下是否有字段无法找到对应的类型

select distinct bb_field_name,field_name from bb_tables t
where not exists(select 1 from bb_fields s where t.bb_field_name=s.bb_field_name);

匹配不上反馈给bloomberg处理。我这里临时把匹配不上的统一改为类型varchar2(1999)

3.5 建表

-- Created on 2019/5/22 by YUBL

declare
  -- Local variables here
  v_sql_cmd clob:='';
  v_current_tbname varchar2(30):='';
  v_pre_tbname varchar2(30):='';
  v_loopup_num number(10):=0;
  v_field_name varchar2(100); 

  cursor sp_cursor is
  select a.table_name,a.field_name,b.oracle_type
  from bb_tables a
       left join bb_fields b on a.field_name=b.field_mnemonic
  where not exists(select 1 from user_tables where table_name=a.table_name)
  order by 1,2;

begin

  for v_row in sp_cursor loop
      v_loopup_num :=v_loopup_num+1;
      v_current_tbname:=v_row.table_name;   
      if v_loopup_num=1 then
         v_sql_cmd:='create table '||v_current_tbname||'(';
      end if;
     
      v_field_name:=v_row.field_name;    
      if regexp_instr(v_row.field_name,'[0-9]')=1 then
        v_field_name:='C'||v_row.field_name;
      end if;     

      if length(v_field_name)>30 then
        v_field_name:=substr(v_field_name,1,24)||substr(v_field_name,-6);
      end if;     

      if v_current_tbname<>v_pre_tbname then        
         v_sql_cmd:=v_sql_cmd||'HCREATETIME DATE DEFAULT SYSDATE,HUPDATETIME DATE)';  
         BEGIN    
           execute immediate v_sql_cmd;
           exception
             when OTHERS then
               dbms_output.put_line(v_sql_cmd);
         END;

         v_sql_cmd:='create table '||v_current_tbname||'(';   
      end if;
   

      v_sql_cmd:= v_sql_cmd||' '||v_field_name||' '||nvl(v_row.oracle_type,'varchar2(1999)')||','; 
      v_pre_tbname:=v_current_tbname;
    
  end loop;
end;

如果output中有打印出建表语句，把这些复制出来检查为什么无法创建。

四主键信息导入

4.1 新建表：bb_table_key_columns 用于存储每个表的主键信息

CREATE TABLE BB_TABLE_KEY_COLUMNS
(
BB_TABLE_NAME VARCHAR2(100),
BB_KEY_COLUMN VARCHAR2(100),
TABLE_NAME VARCHAR2(100),
KEY_COLUMN VARCHAR2(100)
)

4.2 将《Harvest Bulk File List.xlsx》sheet：File List的内容导入,部分内容截图如下：

将file_name、primary key 分别导入到bb_table_name、bb_key_column中。parimary key 要按“|”分隔后导入。

注意：

文件中有些主键的信息是错误的。比如:idbbcompany 要改成:ID_BB_COMPANY