大数据备忘录———将数据从orcle导入impala中

上周遇到了将数据从orcle导入到impala的问题，这个项目耽误了我近一周的时间，虽然是种种原因导致的，但是还是做个总结。

需求首先是跑数据，跑数据这个就不叙述，用的是公司的平台。

讲讲耽误我最久的事吧 数据的导入导出。

将数据从orcle导出

plsql直接导出

我这边连接公司的orcle数据库是plsql，本身plsql就是可以可以导出数据的，而且很简单。

plsql在select后就能导出表的数据，能到处成csv、sql、xml等等。

但是这方法最后还是被舍弃了，有几个原因：

1.这种导出方法很慢，我导出200m的csv数据需要40分钟

2.数据导出有限制，这种方法好像最多只能导出104w数据，但是需求是需要导出1亿两千万的数据，很明显是不可以的。

注：中间我考虑过按分区导出数据，因为这个表是按时时间分了分区，然后发现还是不行，因为数量还是太大了，30天的数据平均下来每个还是有400w，最后放弃了

事实证明这个方法不是很好用，导出几百、几天还行。多了就不行了。

使用oracle的内建包utl_file

第二种用orcle里面用utl_file读写文件包 ，每分钟大约处理百万行。适用于大量导出时。

一、首先需要新建一个存储过程

  1 create
  2 or replace procedure sql_to_csv (
  3     p_query in varchar2,-- plsql文  
  4     p_dir in varchar2,-- 导出的文件放置目录  
  5     p_filename in varchar2 -- csv名  
  6 ) is l_output utl_file.file_type;
  7  
  8 l_thecursor integer default dbms_sql.open_cursor;
  9  
 10 l_columnvalue varchar2 (4000);
 11  
 12 l_status integer;
 13  
 14 l_colcnt number := 0;
 15  
 16 l_separator varchar2 (1);
 17  
 18 l_desctbl dbms_sql.desc_tab;
 19  
 20 p_max_linesize number := 32000;
 21  
 22  
 23 begin
 24     --open file  
 25     l_output := utl_file.fopen (
 26         p_dir,
 27         p_filename,
 28         'w',
 29         p_max_linesize
 30     );
 31  
 32 --define date format  
 33 execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
 34  
 35 --open cursor  
 36 dbms_sql.parse (
 37     l_thecursor,
 38     p_query,
 39     dbms_sql.native
 40 );
 41  
 42 dbms_sql.describe_columns (
 43     l_thecursor,
 44     l_colcnt,
 45     l_desctbl
 46 );
 47  
 48 --dump table column name  
 49 for i in 1 ..l_colcnt
 50 loop
 51     utl_file.put (
 52         l_output,
 53         l_separator || '"' || l_desctbl (i ).col_name || '"'
 54     );
 55  
 56 --输出表字段  
 57 dbms_sql.define_column (
 58     l_thecursor,
 59     i,
 60     l_columnvalue,
 61     4000
 62 );
 63  
 64 l_separator := ',';
 65  
 66  
 67 end
 68 loop
 69 ;
 70  
 71 utl_file.new_line (l_output);--输出表字段  
 72 --execute the query statement  
 73 l_status := dbms_sql. execute (l_thecursor);
 74  
 75 --dump table column value  
 76 while (
 77     dbms_sql.fetch_rows (l_thecursor) > 0
 78 )
 79 loop
 80     l_separator := '';
 81  
 82 for i in 1 ..l_colcnt
 83 loop
 84     dbms_sql.column_value (
 85         l_thecursor,
 86         i,
 87         l_columnvalue
 88     );
 89  
 90 utl_file.put (
 91     l_output,
 92     l_separator || '"' || trim (
 93         both ' '
 94         from
 95             replace (l_columnvalue, '"', '""')
 96     ) || '"'
 97 );
 98  
 99 l_separator := ',';
100  
101  
102 end
103 loop
104 ;
105  
106 utl_file.new_line (l_output);
107  
108  
109 end
110 loop
111 ;
112  
113 --close cursor  
114 dbms_sql.close_cursor (l_thecursor);
115  
116 --close file  
117 utl_file.fclose (l_output);
118  
119 exception
120 when others then
121     raise;
122  
123  
124 end;
125  
126 /

 二、创建导出路径

create or replace directory out_path as 'd:\out_path';  

注意：这步只是在oracle sql developer中定义了导出路径，如果路径不存在，并不会自动生成，需要手动去新建！

三、调用数据

exec sql_to_csv('select * from <tablename>','out_path','<filename>'); 

这种是在网上查到的方法，这边因为最近公司规定不能下载到本地所以没有采用这种方法，但是这种方法测试是可行的，没有具体测试效率。

sqluldr服务器导出数据

第三种是我最后采用的方法

用sqluldr脚本导出到服务上，然后把文件转移到impala的服务器上去。

下载链接链接：https://pan.baidu.com/s/1brrr-bdql0yijjta16tttw
提取码：1tns

将sqluldr.rar中文件上传到orcle所在服务器中，然后执行 
./sqluldr2_linux64_10204.binuser= query="" field=',' text=txt file='' charset=utf8;
file='' 是存放路径 charset=编码 user=是orcle数据库地址 query=是导出语句

然后执行效率100w大概40秒左右，速度比较快。

 数据上传到impala中

1、将数据从orcle服务器转移到impala所在服务器

2、在hive中建好导出数据的表

create table tmp.tmp_call_orcle_1 (
test string
)
 row format delimited 
fields terminated by ','  --csv分隔符
stored as textfile;  --设置文件为test文件

3、用hdfs命令将数据导入到建好的表中：hdfs dfs -put test.csv /user/hive/warehouse/tmp.db/test

4、将表的读取路径改成hdfs路径

load data inpath '/user/hive/warehouse/tmp.db/tmp_call_orcle_1/tmp_call_orcle_1.csv' into table tmp.tmp_call_orcle_1;

至此就完成了将orcle数据导入impala的操作