您好,登錄后才能下訂單哦!
這篇文章主要介紹“大文本數(shù)據(jù)怎么導入導出到數(shù)據(jù)庫”,在日常操作中,相信很多人在大文本數(shù)據(jù)怎么導入導出到數(shù)據(jù)庫問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”大文本數(shù)據(jù)怎么導入導出到數(shù)據(jù)庫”的疑惑有所幫助!接下來,請跟著小編一起來學習吧!
一,使用sql load
創(chuàng)建表
create table ext_gv_tmp_amazon_sku
(
skucode varchar2(255 char),
eancode varchar2(255 char)
)
配置控制文件
more input.ctl
load data
infile input.txt
badfile t.bad
discardfile t.dsc
append into table ext_gv_tmp_amazon_sku ----導入的表
fields terminated by "|" ---分割符
trailing nullcols ---允許列有空值
(skucode,eancode) ---導入的字段
導入命令
先設置導入客戶端字符集,不然中文會亂碼
export NLS_LANG=AMERICAN_AMERICA.UTF8
同時看看系統(tǒng)的字符集是否設置正確
[oracle@rac-test1 pandump]$ cat /etc/sysconfig/i18n
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"
最后導入數(shù)據(jù)
[oracle@rac-test1 pandump]$ sqlldr panhf/oracle control=input.ctl
SQL> select count(*) from ext_gv_tmp_amazon_sku;
COUNT(*)
----------
2409530
二,使用外部表
利用外部表的方式來導入數(shù)據(jù)
1,創(chuàng)建oracle目錄,并授權給相關賬號
CREATE OR REPLACE DIRECTORY pandump
AS '/oradata/pandump';
grant read,write on DIRECTORY pandump to panhf;
2,創(chuàng)建外部表,注意這里的字符集設置,如果不設置中文會無法讀取導致數(shù)據(jù)丟失
create table ext_gv_tmp_amazon_sku
(
skucode varchar2(255 char),
eancode varchar2(255 char)
)
organization external (
type oracle_loader
default directory pandump
access parameters
(
records delimited by newline
logfile pandump:'ext_gv_tmp_amazon_sku.log'
badfile pandump:'ext_gv_tmp_amazon_sku.bad'
discardfile pandump:'ext_gv_tmp_amazon_sku.disc'
characterset 'AL32UTF8'
fields terminated by "|" lrtrim
missing field values are null
(
skucode,
eancode
)
)
location ('ext_ids_1.txt')
)
reject limit unlimited
/
3,將數(shù)據(jù)cts的方式拷貝到另外的表
select count(*) from ext_gv_tmp_amazon_sku;
COUNT(*)
----------
2409530
create table panhf.ext_gv_tmp_amazon_sku as select * from ext_gv_tmp_amazon_sku;
Table created.
三,數(shù)據(jù)導出文本
cat exp.sql
set echo off
set feedback off
set newpage none
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading off
set timing off
set numwidth 40
spool /home/oracle/empInfor.txt
select skucode||'|'||eancode from panhf.ext_gv_tmp_amazon_sku;
spool off
執(zhí)行腳本
@exp.sql
到此,關于“大文本數(shù)據(jù)怎么導入導出到數(shù)據(jù)庫”的學習就結束了,希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習,快去試試吧!若想繼續(xù)學習更多相關知識,請繼續(xù)關注億速云網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權內(nèi)容。