暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / VerticaTips-使用CURRENT_LOAD_SOURCE()函数将copy入库的文件名信息带入表中

VerticaTips-使用CURRENT_LOAD_SOURCE()函数将copy入库的文件名信息带入表中

原创 simonchiang 2021-12-15
619
  1. 该函数在vertica8.1.1版本不支持使用source HDFS方式导入数据
  2. 该函数在vertica8.1.1版本不支持使用copy from local方式导入数据

下面的测试基于Vertica Analytic Database v9.1.0-3、Apache Hadoop 2.8.0
建表，除表的数据列外，需要另外加一列用来存放该条数据的来源

[dbadmin@szxtsp101 ~]$ VSQL
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
	   \g or terminate with semicolon to execute query
	   \q to quit

dbadmin=>
drop table public.LOAD_SOURCE_test cascade;
CREATE TABLE public.LOAD_SOURCE_test
(
a varchar,
b varchar,
LOAD_SOURCE varchar(8192)
);

准备测试数据

[dbadmin@szxtsp101 ~]$ cat 1.txt
a|s
f|d
v|d

[dbadmin@szxtsp101 ~]$ cp 1.txt 2.txt
[dbadmin@szxtsp101 ~]$ cp 1.txt 3.txt
[dbadmin@szxtsp101 ~]$ cp 1.txt 4.txt

将数据copy入库，并带上文件信息

[dbadmin@szxtsp101 ~]$ VSQL
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
	   \g or terminate with semicolon to execute query
	   \q to quit

dbadmin=>
dbadmin=>
dbadmin=> copy public.LOAD_SOURCE_test(a,b,LOAD_SOURCE as CURRENT_LOAD_SOURCE()) from '/home/dbadmin/*';
copy public.LOAD_SOURCE_test(a,b,LOAD_SOURCE as CURRENT_LOAD_SOURCE()) from '/home/dbadmin/*';
 Rows Loaded
-------------
		  12
(1 row)

dbadmin=> select * from public.LOAD_SOURCE_test;
select * from public.LOAD_SOURCE_test;
 a | b |     LOAD_SOURCE
---+---+---------------------
 v | d | /home/dbadmin/1.txt
 v | d | /home/dbadmin/2.txt
 a | s | /home/dbadmin/3.txt
 a | s | /home/dbadmin/4.txt
 v | d | /home/dbadmin/4.txt
 a | s | /home/dbadmin/1.txt
 a | s | /home/dbadmin/2.txt
 f | d | /home/dbadmin/2.txt
 v | d | /home/dbadmin/3.txt
 f | d | /home/dbadmin/1.txt
 f | d | /home/dbadmin/3.txt
 f | d | /home/dbadmin/4.txt
(12 rows)

再来看看从hdfs上copy数据怎么样
首先上传数据

[hadoop@szxtsp101 ~]$ hadoop fs -put /tmp/*.txt /vertica_data/
[hadoop@szxtsp101 ~]$ hadoop fs -ls /vertica_data/
Found 17 items
-rw-r--r--   2 hadoop  supergroup         12 2018-08-10 16:02 /vertica_data/1.txt
-rw-r--r--   2 hadoop  supergroup         12 2018-08-10 16:02 /vertica_data/2.txt
-rw-r--r--   2 hadoop  supergroup         12 2018-08-10 16:02 /vertica_data/3.txt
-rw-r--r--   2 hadoop  supergroup         12 2018-08-10 16:02 /vertica_data/4.txt
-rwxrwxrwx   3 dbadmin supergroup  379423758 2017-04-12 17:01 /vertica_data/Online_Sales_Fact.tbl
-rwxrwxrwx   3 dbadmin supergroup  379423758 2017-04-12 17:03 /vertica_data/Online_Sales_Fact.tbl2
-rwxrwxrwx   3 dbadmin supergroup  379423758 2017-04-12 17:29 /vertica_data/Online_Sales_Fact.tbl3
-rwxrwxrwx   2 dbadmin supergroup  379423758 2017-04-12 18:00 /vertica_data/Online_Sales_Fact.tbl4
-rwxrwxrwx   2 dbadmin supergroup  379423758 2017-04-12 18:05 /vertica_data/Online_Sales_Fact.tbl5
drwxr-xr-x   - hadoop  supergroup          0 2017-08-17 15:14 /vertica_data/csvData
drwxr-xr-x   - dbadmin supergroup          0 2018-07-18 10:57 /vertica_data/parquetData
drwxrwxrwx   - dbadmin supergroup          0 2017-07-27 11:08 /vertica_data/parquet_data_date_dimension
drwxrwxrwx   - dbadmin supergroup          0 2017-07-27 11:19 /vertica_data/parquet_data_date_dimension2
drwxrwxrwx   - dbadmin supergroup          0 2017-07-27 11:22 /vertica_data/parquet_data_date_dimension3
drwxrwxrwx   - dbadmin supergroup          0 2017-07-27 11:36 /vertica_data/parquet_data_date_dimension4
drwxrwxrwx   - dbadmin supergroup          0 2017-07-27 11:43 /vertica_data/parquet_data_date_dimension5
drwxr-xr-x   - dbadmin supergroup          0 2017-08-17 14:51 /vertica_data/parquet_data_public.test3
[hadoop@szxtsp101 ~]$

然后使用hdfs原生协议copy入库

[hadoop@szxtsp101 ~]# su - dbadmin
[dbadmin@szxtsp101 ~]$ VSQL
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
	   \g or terminate with semicolon to execute query
	   \q to quit

dbadmin=>
dbadmin=>
dbadmin=> copy public.LOAD_SOURCE_test(a,b,LOAD_SOURCE as CURRENT_LOAD_SOURCE()) from 'hdfs:///vertica_data/*.txt';
copy public.LOAD_SOURCE_test(a,b,LOAD_SOURCE as CURRENT_LOAD_SOURCE()) from 'hdfs:///vertica_data/*.txt';
 Rows Loaded
-------------
		  12
(1 row)

dbadmin=>
dbadmin=>
dbadmin=> select * from public.LOAD_SOURCE_test;
select * from public.LOAD_SOURCE_test;
 a | b |        LOAD_SOURCE
---+---+----------------------------
 a | s | /home/dbadmin/3.txt
 a | s | /home/dbadmin/4.txt
 v | d | /home/dbadmin/1.txt
 v | d | /home/dbadmin/2.txt
 v | d | /home/dbadmin/4.txt
 a | s | hdfs:///vertica_data/4.txt
 f | d | hdfs:///vertica_data/4.txt
 f | d | hdfs:///vertica_data/1.txt
 f | d | hdfs:///vertica_data/3.txt
 a | s | /home/dbadmin/1.txt
 a | s | /home/dbadmin/2.txt
 f | d | /home/dbadmin/2.txt
 v | d | /home/dbadmin/3.txt
 f | d | hdfs:///vertica_data/2.txt
 v | d | hdfs:///vertica_data/4.txt
 a | s | hdfs:///vertica_data/3.txt
 f | d | /home/dbadmin/1.txt
 f | d | /home/dbadmin/3.txt
 f | d | /home/dbadmin/4.txt
 a | s | hdfs:///vertica_data/2.txt
 v | d | hdfs:///vertica_data/2.txt
 a | s | hdfs:///vertica_data/1.txt
 v | d | hdfs:///vertica_data/1.txt
 v | d | hdfs:///vertica_data/3.txt
(24 rows)

试试SOURCE HDFS？

dbadmin=>
dbadmin=> COPY public.LOAD_SOURCE_test SOURCE Hdfs(url='http://szxtsp101:50070/webhdfs/v1/vertica_data/*.txt',username='hadoop') direct no escape null '\N';
ERROR 3457:  Function Hdfs() does not exist, or permission is denied for Hdfs()
HINT:  No function matches the given name and argument types. You may need to add explicit type casts
dbadmin=>
dbadmin=>
dbadmin=> select version();
select version();
			  version
------------------------------------
 Vertica Analytic Database v9.1.0-3
(1 row)

报错了，因为在9的版本，Vertica已经不在支持source HDFS这个语法，所以咱们试试webhdfs

dbadmin=> copy public.LOAD_SOURCE_test(a,b,LOAD_SOURCE as CURRENT_LOAD_SOURCE()) from 'webhdfs://szxtsp101:50070/vertica_data/*.txt' direct;
dbadmin=> select * from public.LOAD_SOURCE_test;
select * from public.LOAD_SOURCE_test;
 a | b |                 LOAD_SOURCE
---+---+----------------------------------------------
 f | d | /home/dbadmin/1.txt
 f | d | /home/dbadmin/3.txt
 f | d | /home/dbadmin/4.txt
 a | s | hdfs:///vertica_data/1.txt
 a | s | hdfs:///vertica_data/2.txt
 v | d | hdfs:///vertica_data/1.txt
 v | d | hdfs:///vertica_data/2.txt
 v | d | hdfs:///vertica_data/3.txt
 v | d | webhdfs://szxtsp101:50070/vertica_data/1.txt
 v | d | webhdfs://szxtsp101:50070/vertica_data/2.txt
 a | s | /home/dbadmin/3.txt
 a | s | /home/dbadmin/4.txt
 v | d | /home/dbadmin/1.txt
 v | d | /home/dbadmin/2.txt
 v | d | /home/dbadmin/4.txt
 a | s | hdfs:///vertica_data/4.txt
 f | d | hdfs:///vertica_data/1.txt
 f | d | hdfs:///vertica_data/3.txt
 f | d | hdfs:///vertica_data/4.txt
 a | s | webhdfs://szxtsp101:50070/vertica_data/1.txt
 a | s | webhdfs://szxtsp101:50070/vertica_data/4.txt
 f | d | webhdfs://szxtsp101:50070/vertica_data/1.txt
 v | d | webhdfs://szxtsp101:50070/vertica_data/3.txt
 a | s | /home/dbadmin/1.txt
 a | s | /home/dbadmin/2.txt
 f | d | /home/dbadmin/2.txt
 v | d | /home/dbadmin/3.txt
 a | s | hdfs:///vertica_data/3.txt
 f | d | hdfs:///vertica_data/2.txt
 v | d | hdfs:///vertica_data/4.txt
 a | s | webhdfs://szxtsp101:50070/vertica_data/2.txt
 a | s | webhdfs://szxtsp101:50070/vertica_data/3.txt
 f | d | webhdfs://szxtsp101:50070/vertica_data/2.txt
 f | d | webhdfs://szxtsp101:50070/vertica_data/3.txt
 f | d | webhdfs://szxtsp101:50070/vertica_data/4.txt
 v | d | webhdfs://szxtsp101:50070/vertica_data/4.txt
(36 rows)

二维码.png

verticaverticatips
最后修改时间：2024-08-29 11:26:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

未来“数”于你 | Vertica体验赛参赛作品（共48篇）
分享与Vertica 的故事：使用心得、上手体验、架构理解、技术指南、产品对比等。
VerticaTips（共13篇）
一些Vertica使用上的小技巧

评论