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