上周遇到了将数据从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的操作
经验首页