本文介绍如何在KingbaseES数据库导入导出数据。
KingbaseES数据库支持使用命令copy和工具sys_bulkload对数据进行导入导出。
一、使用copy
1、创建测试表
create table test (id int ,name text); create table test1 (id int ,name text); insert into test select generate_series(1,100),'kes'; test=# select count(*) from test; count ------- 100 (1 行记录)
2、导出test表数据到文本文件
test=# copy test to '/home/kingbase/test.csv' with csv; COPY 100
3、查看导出的数据文件
[kingbase@node01 ~]$ more test.csv 1,kes 2,kes 3,kes 4,kes 5,kes 6,kes 7,kes 8,kes 9,kes 10,kes 11,kes 12,kes 13,kes 14,kes 15,kes 16,kes 17,kes
...
4、导入test.csv数据到test1表
test=# copy test1 from '/home/kingbase/test.csv' with csv; COPY 100
5、查看test1表数据
test=# select count(*) from test1; count ------- 100 (1 行记录)
二、使用sys_bulkload
1、创建导入数据表
create table tt(id int primary key, info text, crt_time timestamp);
2、创建数据文件：将下列数据以 tt.csv 为文件名保存到 KingbaseES 服务器所在目录
vi tt.csv 1,29b35ff06c949e7e442c929e1df86396,2017-10-08 10:52:47.746062 2,06fde814525395de5ab85f6d92b04e87,2017-10-08 10:52:47.746573 3,c93f02e8677c9cd7c906c6ad5dbd450e,2017-10-08 10:52:47.746627 4,6541700070ae3d051f965fcef43baf45,2017-10-08 10:52:47.746835 5,3d7e7246016acaa842526b6614d0edf5,2017-10-08 10:52:47.746869 6,1d1ae5a03ef0bad3bc14cd5449ba0985,2017-10-08 10:52:47.746894 7,7745c57c54b97656bec80a502ec13ec7,2017-10-08 10:52:47.746918 8,3c377131f6ef82c3284dc77a3b4ffdf7,2017-10-08 10:52:47.746942 9,5ef98d40aeeadf65eb1f0d7fd86ed585,2017-10-08 10:52:47.746968 10,312c0a0188da9e34fe45aa19d0d07427,2017-10-08 10:52:47.746993
3、修改配置文件（以 tt.ctl 为名保存到服务器所在目录，也可自行指定其他目录。)
vi tt.ctl TABLE = tt INPUT = /home/kingbase/tt.csv TYPE = TEXT SKIP = 0 LIMIT = 11 WRITER = BUFFERED PROCESSOR_COUNT = 1
4、创建扩展：
ksql -Usystem test create extension sys_bulkload;
5、使用sys_bulkload导入数据
[kingbase@node01 ~]$ sys_bulkload -h 192.168.128.128 -d test -U system -p54321 -W 123456 /home/kingbase/tt.ctl NOTICE: BULK LOAD START NOTICE: BULK LOAD END 0 Rows skipped. 10 Rows successfully loaded. 0 Rows not loaded due to parse errors. 0 Rows not loaded due to duplicate errors. 0 Rows replaced with new rows. log path: tt.log parse error path: /home/kingbase/tt.bad duplicate error path: /home/kingbase/tt.dupbad ctrl file path: /home/kingbase/tt.ctl data file path: /home/kingbase/tt.csv Run began on 2022-12-15 11:12:44.704532+08 Run ended on 2022-12-15 11:12:44.706225+08
6、查看数据
[kingbase@kes ~]$ ksql -Usystem test ksql (V8.0) 输入 "help" 来获取帮助信息. id | info | crt_time ----+----------------------------------+---------------------------- 1 | 29b35ff06c949e7e442c929e1df86396 | 2017-10-08 10:52:47.746062 2 | 06fde814525395de5ab85f6d92b04e87 | 2017-10-08 10:52:47.746573 3 | c93f02e8677c9cd7c906c6ad5dbd450e | 2017-10-08 10:52:47.746627 4 | 6541700070ae3d051f965fcef43baf45 | 2017-10-08 10:52:47.746835 5 | 3d7e7246016acaa842526b6614d0edf5 | 2017-10-08 10:52:47.746869 6 | 1d1ae5a03ef0bad3bc14cd5449ba0985 | 2017-10-08 10:52:47.746894 7 | 7745c57c54b97656bec80a502ec13ec7 | 2017-10-08 10:52:47.746918 8 | 3c377131f6ef82c3284dc77a3b4ffdf7 | 2017-10-08 10:52:47.746942 9 | 5ef98d40aeeadf65eb1f0d7fd86ed585 | 2017-10-08 10:52:47.746968 10 | 312c0a0188da9e34fe45aa19d0d07427 | 2017-10-08 10:52:47.746993 (10 行记录)
7、导出数据
[kingbase@node01 ~]$ sys_bulkload -h 192.168.128.128 -d test -U system -p54321 -W 123456 -i tt -O tt_out.csv -o "TYPE=DB" -o "WRITER=CSV_FILE" -o "DELIMITER=," NOTICE: BULK LOAD START NOTICE: BULK LOAD END 0 Rows skipped. 10 Rows successfully loaded. 0 Rows not loaded due to parse errors. 0 Rows not loaded due to duplicate errors. 0 Rows replaced with new rows. log path: /home/kingbase/tt_out.log parse error path: tt.bad duplicate error path: tt.dupbad ctrl file path: data file path: tt Run began on 2022-12-15 11:17:26.211119+08 Run ended on 2022-12-15 11:17:26.211592+08
8、查看导出的数据
[kingbase@node01 ~]$ cat tt_out.csv 1,29b35ff06c949e7e442c929e1df86396,2017-10-08 10:52:47.746062 2,06fde814525395de5ab85f6d92b04e87,2017-10-08 10:52:47.746573 3,c93f02e8677c9cd7c906c6ad5dbd450e,2017-10-08 10:52:47.746627 4,6541700070ae3d051f965fcef43baf45,2017-10-08 10:52:47.746835 5,3d7e7246016acaa842526b6614d0edf5,2017-10-08 10:52:47.746869 6,1d1ae5a03ef0bad3bc14cd5449ba0985,2017-10-08 10:52:47.746894 7,7745c57c54b97656bec80a502ec13ec7,2017-10-08 10:52:47.746918 8,3c377131f6ef82c3284dc77a3b4ffdf7,2017-10-08 10:52:47.746942 9,5ef98d40aeeadf65eb1f0d7fd86ed585,2017-10-08 10:52:47.746968 10,312c0a0188da9e34fe45aa19d0d07427,2017-10-08 10:52:47.746993
标签：10,tt,kes,数据库,52,导入,2017,KingbaseES,08 From： https://www.cnblogs.com/happy-0824/p/16984499.html