blocks|key|191991|text|我也遇到了同样的问题，因为我的字段用双引号括起来，并用分号(；)分隔。我的表名是employee1。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|191992|因此，我搜索了链接，我找到了完美的解决方案。|191993|为此，我们必须使用serde。请使用以下链接下载serde+jar：https://github.com/downloads/IllyaYalovyy/csv-serde/csv-serde-0.9.1.jar|offset|length|191994|然后使用配置单元提示执行以下步骤：|191995|add+jar+path/to/csv-serde.jar;

create+table+employee1(id+string,+name+string,+addr+string)
row+format+serde+'com.bizo.hive.serde.csv.CSVSerde'
with+serdeproperties(
"separatorChar"+=+"\;",
"quoteChar"+=+"\"")
stored+as+textfile
;|code-block|syntax|javascript|191996|然后使用下面的查询从给定的路径加载数据：|191997|load+data+local+inpath+'path/xyz.csv'+into+table+employee1;|191998|然后运行：|191999|select+*+from+employee1;|192000|现在你将看到魔力。谢谢。|192001|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/downloads/IllyaYalovyy/csv-serde/csv-serde-0.9.1.jar^0|0|0|Y|1Z|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|18|8|@]|9|@$F|19|G|1A|1|1B]]|A|$]]|$1|H|3|I|5|6|7|1C|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1D|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1F|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|1G|8|@]|9|@]|A|$]]|$1|U|3|V|5|L|7|1H|8|@]|9|@]|A|$M|N]]|$1|W|3|X|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]]]

I was also stuck with the same issue as my fields are enclosed with double quotes and separated by semicolon(;). My table name is employee1.

So I have searched with links and I have found perfect solution for this.

We have to use serde for this. Please download serde jar using this link : <a href="https://github.com/downloads/IllyaYalovyy/csv-serde/csv-serde-0.9.1.jar">https://github.com/downloads/IllyaYalovyy/csv-serde/csv-serde-0.9.1.jar</a>

then follow below steps using hive prompt :

<pre><code>add jar path/to/csv-serde.jar;

create table employee1(id string, name string, addr string)
row format serde 'com.bizo.hive.serde.csv.CSVSerde'
with serdeproperties(
"separatorChar" = "\;",
"quoteChar" = "\"")
stored as textfile
;
</code></pre>

and then load data from your given path using below query:

<pre><code>load data local inpath 'path/xyz.csv' into table employee1;
</code></pre>

and then run :

<pre><code>select * from employee1;
</code></pre>

Now you will see the magic. Thanks.

blocks|key|192017|text|请参见http://illyayalovyy.github.io/csv-serde/，在配置单元查询中使用csv-serde-0.9.1.jar文件|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|192018|add+jar+/path/to/jar_file

Create+external+table+emrS3_import_1(col1+string,+col2+string,+col3+string,+col4+string)+row+format+serde+'com.bizo.hive.serde.csv.CSVSerde'
with+serdeproperties
(
++"separatorChar"+=+"\;",
++"quoteChar"+=+"\"
)+stored+as+textfile
tblproperties("skip.header.line.count"="1")+---to+skip+if+have+any+header+file
LOCATION+'s3://emrTest/folder';|code-block|syntax|javascript|192019|entityMap|0|LINK|mutability|MUTABLE|url|http://illyayalovyy.github.io/csv-serde/^0|1I|J|3|14|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

Use the <code>csv-serde-0.9.1.jar</code> file in your hive query, see
<a href="http://illyayalovyy.github.io/csv-serde/" rel="nofollow">http://illyayalovyy.github.io/csv-serde/</a>

<pre><code>add jar /path/to/jar_file

Create external table emrS3_import_1(col1 string, col2 string, col3 string, col4 string) row format serde 'com.bizo.hive.serde.csv.CSVSerde'
with serdeproperties
(
 "separatorChar" = "\;",
 "quoteChar" = "\"
) stored as textfile
tblproperties("skip.header.line.count"="1") ---to skip if have any header file
LOCATION 's3://emrTest/folder';
</code></pre>

blocks|key|410191|text|Hive现在包括一个OpenCSVSerde，它可以正确地解析那些引用的字段，而不会添加额外的jars或容易出错和缓慢的正则表达式。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|410192|ROW+FORMAT+SERDE+'org.apache.hadoop.hive.serde2.OpenCSVSerde'|410193|entityMap^0|A|C|0|0|1P|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

Hive now includes an <code>OpenCSVSerde</code> which will properly parse those quoted fields without adding additional jars or error prone and slow regex.

<code>ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
</code>

blocks|key|192067|text|下面的代码解决了相同类型的问题|type|unstyled|depth|inlineStyleRanges|entityRanges|data|192068|CREATE+TABLE+TableRowCSV2(++++
++++CODE+STRING,++++++++
++++PRODUCTCODE+STRING,+
++++PRICE+STRING+++++
)
++++COMMENT+'row+data+csv'++++
ROW+FORMAT+SERDE+'org.apache.hadoop.hive.serde2.OpenCSVSerde'

WITH+SERDEPROPERTIES+(
+++"separatorChar"+=+"\,",
+++"quoteChar"+++++=+"\""
)
STORED+AS+TEXTFILE
tblproperties("skip.header.line.count"="1");|code-block|syntax|javascript|192069|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Following code solved same type of problem 

<pre><code>CREATE TABLE TableRowCSV2( 
 CODE STRING, 
 PRODUCTCODE STRING, 
 PRICE STRING 
)
 COMMENT 'row data csv' 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

WITH SERDEPROPERTIES (
 "separatorChar" = "\,",
 "quoteChar" = "\""
)
STORED AS TEXTFILE
tblproperties("skip.header.line.count"="1");
</code></pre>

I am trying to use EMR/Hive to import data from S3 into DynamoDB. My CSV file has fields which are enclosed within double quotes and separated by comma.
While creating external table in hive, I am able to specify delimiter as comma but how do I specify that fields are enclosed within quotes?

If I don’t specify, I see that values in DynamoDB are populated within two double quotes ““value”” which seems to be wrong.

I am using following command to create external table. Is there a way to specify that fields are enclosed within double quotes?

<pre><code>CREATE EXTERNAL TABLE emrS3_import_1(col1 string, col2 string, col3 string, col4 string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '","' LOCATION 's3://emrTest/folder';
</code></pre>

Any suggestions would be appreciated.
Thanks
Jitendra

How to handle fields enclosed within quotes(CSV) in importing data from S3 into DynamoDB using EMR/Hive

Hive

我正在尝试使用电子病历/配置单元将数据从S3导入DynamoDB。我的CSV文件包含用双引号括起来并用逗号分隔的字段。在配置单元中创建外部表时，我可以将分隔符指定为逗号，但如何指定将字段括在引号中？如果我没有指定，我会看到DynamoDB中的值被填充在两个双引号“”value“”中，这似乎是错误的。我正在使用下面的命令...

问在使用电子病历/配置单元将数据从S3导入DynamoDB时，如何处理用引号括起的字段
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在使用电子病历/配置单元将数据从S3导入DynamoDB时，如何处理用引号括起的字段EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在使用电子病历/配置单元将数据从S3导入DynamoDB时，如何处理用引号括起的字段
EN