将CSV数据加载到Snowflake表
将CSV数据加载到Snowflake表
提问于 2020-11-25 23:20:57
当尝试将以下数据从其CSV文件加载到SnowFlake表中时，列将拆分为多列。
列数据：
{"Department":"Mens
Wear","Departmentid":"10.1;20.1","customername":"john4","class":"tops wear","subclass":"sweat shirts","product":"North & Face 2 Bangle","style":"Sweat shirt hoodie - Large - Black"}是否有其他方法可以将数据加载到单列中。
回答 1
Stack Overflow用户
发布于 2020-11-26 01:03:16
最佳解决方案是在CSV文件中使用不同的分隔符，而不是逗号。如果不可能，那么您可以使用不存在的分隔符来摄取数据，以获得整行作为一列，然后对其进行解析。当然，它不会像本地加载那样有效：
cat test.csv
1,2020-10-12,Gokhan,{"Department":"Mens Wear","Departmentid":"10.1;20.1","customername":"john4","class":"tops wear","subclass":"sweat shirts","product":"North & Face 2 Bangle","style":"Sweat shirt hoodie - Large - Black"}
create file format csvfile type=csv FIELD_DELIMITER='NONEXISTENT';
select $1 from @my_stage (file_format => csvfile );
create table testtable( id number, d1 date, name varchar, v variant );
copy into testtable from (
select
split( split($1,',{')[0], ',' )[0],
split( split($1,',{')[0], ',' )[1],
split( split($1,',{')[0], ',' )[2],
parse_json( '{' || split($1,',{')[1] )
from @my_stage (file_format => csvfile )
);
select * from testtable;
+----+------------+--------+-----------------------------------------------------------------+
| ID | D1 | NAME | V |
+----+------------+--------+-----------------------------------------------------------------+
| 1 | 2020-10-12 | Gokhan | { "Department": "Mens Wear", "Departmentid": "10.1;20.1", ... } |
+----+------------+--------+-----------------------------------------------------------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65007620
复制相关文章
相似问题
腾讯云开发者
