hdfs需上传csv文件时hive表的DDL设计

原创已于 2024-05-07 12:13:17 修改 · 809 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库

于 2022-07-21 15:55:43 首次发布

hive 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何使用Apache Hive的OpenCSVSerde处理CSV数据，特别关注了处理包含换行符的文件，并详细说明了创建表格时指定的CSV属性，如分隔符、引号和转义字符的配置。

This SerDe works for most CSV data, but does not handle embedded newlines. To use the SerDe, specify the fully qualified class name org.apache.hadoop.hive.serde2.OpenCSVSerde.

Documentation is based on original documentation at https://github.com/ogrodnek/csv-serde.

Create table, specify CSV properties

CREATE TABLE my_table(a string, b string, ...)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

WITH SERDEPROPERTIES (

"separatorChar" = "\t",

"quoteChar" = "'",

"escapeChar" = "\\"

)

STORED AS TEXTFILE;

Default separator, quote, and escape characters if unspecified

DEFAULT_ESCAPE_CHARACTER \

DEFAULT_QUOTE_CHARACTER "

DEFAULT_SEPARATOR ,

For general information about SerDes, see Hive SerDe in the Developer Guide. Also see SerDe for details about input and output processing.

Versions

The CSVSerde has been built and tested against Hive 0.14 and later, and uses Open-CSV 2.3 which is bundled with the Hive distribution.

Hive Versions	Open-CSV Version
Hive 0.14 and later	2.3

当我们上传csv文件到hdfs上时，如果hive表以此SerDe建表，可以识别正常的csv文件，比如csv文件内出现有逗号的字符串，是会用引号包括起来，类似"abc,12331"，这样的数据hive表可以识别成一个整体，但是如果内容中有出现换行符需要先处理才能正常导入。

此SerDe默认的分隔符为 , 引号为 " 转义字符 \

CREATE TABLE my_table(a string, b string, ...)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

WITH SERDEPROPERTIES (

   "separatorChar" = "\t",

   "quoteChar"     = "'",

   "escapeChar"    = "\\"

)  

STORED AS TEXTFILE;

separatorChar:分隔符

quoteChar:引号符

escapeChar:转义字符