解析在 AWS Athena/AWS Glue 目录中的一列中具有换行符的 csv 文件 [英] parsing csv file that has newline characters in one of columns in AWS Athena/ AWS Glue catalog

问题描述

我有如下示例数据:

id,log,code,sequence
100,sample <(>&<)> O sample ? PILE UP - 3 sample,20,7^M$
101,sample- 4/52$
sample$
CM,21,7^M$
102,sample AT 3PM,22,4^M$

在第二行 (id=101) 中，日志列有换行符，一行中有 3 行.我在 vim 编辑器中启用了:set list"选项来显示换行符 ($) 和换行符 (^M) 字符.

In second row (id=101), log column has newline characters making 3 lines out of one line. I've enabled ":set list" option in vim editor to show newline ($) and endofline (^M) characters.

要处理换行符AWS 建议在此处使用 OpenCSVSerde.我尝试使用带有 escapeChar=\\, quoteChar=\", seperatorChar= 的 OPENCSVSerde 序列化，尽管如此，它将数据显示为 5 行，而我需要三行.当我在 Athena 中查询时，id=101 只显示第一行，其余部分丢失:

To handle newline characters AWS Suggested OpenCSVSerde here. I tried using OPENCSVSerde serialisation with escapeChar=\\, quoteChar=\", seperatorChar=, Nonetheless, it is showing data as 5 rows where as I need three rows. When I query in Athena, id=101 is showing only first line and rest is missing:

id,log,code,sequence
101,sample- 4/52

有关如何处理 csv 文件列中的多行字符的任何提示或示例?

Any tips or example on how to handle multiline characters in a csv file column?

我正在探索自定义分类器，但还没有运气.

I'm exploring custom classifiers but no luck yet.

推荐答案

根据此文档 https://docs.aws.amazon.com/athena/latest/ug/csv.html opencsvserde 不支持换行.

According to this doc https://docs.aws.amazon.com/athena/latest/ug/csv.html opencsvserde does not support line breaks.

我看到你试图在那里放置某种日志.您的选择是:

I see that you are trying to put some kind of log there. Your options are:

• 清理日志以不包含换行符.或者，

• Cleanup the log not to include the line breaks. Or,

使用regexserde，如果您的日志格式不断变化，这将没有用.或者，

use regexserde, which is not useful if your log format keeps changing. Or,

如果两者都不是一个选项，您可以将您的格式从 csv 更改为 parquet 或其他格式，其中没有换行问题

If both are not an option you can change ur format from csv to parquet or something else, where there are no line break issues

这篇关于解析在 AWS Athena/AWS Glue 目录中的一列中具有换行符的 csv 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！