如何在Amazon Athena中读取包含NULL值的引用CSV文件

Question

如何在Amazon Athena中读取包含NULL值的引用CSV文件

14

我正在尝试使用存储在S3上的带引号的CSV文件在Athena中创建外部表。问题是，我的CSV文件中包含应该读取为INT的列中缺失的值。以下是一个简单的例子：

CSV文件：

id,height,age,name
1,,26,"Adam"
2,178,28,"Robert"

创建表定义：

CREATE EXTERNAL TABLE schema.test_null_unquoted (
  id INT,
  height INT,
  age INT,
  name STRING
)
ROW FORMAT 
SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ",",
'quoteChar' = '"',
'skip.header.line.count' = '1'
)
STORED AS TEXTFILE
LOCATION 's3://mybucket/test_null/unquoted/'

CREATE TABLE 语句运行良好，但是一旦我尝试查询表格，就会收到 HIVE_BAD_DATA: Error parsing field value '' 的错误信息。

我尝试将 CSV 格式更改为以下形式（引用空字符串）：

"id","height","age","name"
1,"",26,"Adam"
2,178,28,"Robert"

但是它不起作用。

尝试在SERDEPROPERTIES中指定'serialization.null.format' = '' - 无效。

尝试通过TBLPROPERTIES ('serialization.null.format'='')进行相同的指定 - 仍然没有结果。

当您将所有列指定为STRING时，它可以工作，但这不是我所需要的。

因此，问题是，是否有任何方法可以使用正确的列规范将带引号的CSV（引号很重要，因为我的实际数据更加复杂）读入Athena？

- Mikolaj

3个回答

2

很遗憾，在 Athena 中无法同时支持带引号的字段和空值。你必须选择其中一个。

你可以使用 OpenCSVSerDe 并将所有列类型定义为字符串，这样可以支持带引号的字段和空字段。在查询时使用 TRY_CAST 或 CASE/WHEN 进行值转换。

或者你可以使用 LazySimpleSerDe 并在查询时去除引号。

我会选择 OpenCSVSerDe，因为你可以随时创建带有所有类型转换的视图，并将其用于常规查询。

你可以在这里阅读有关在 Athena 中使用 CSV 的详细信息：The Athena Guide: Working with CSV。

- Theo

0

这对我有用。使用OpenCSVSerDe并将所有列转换为字符串。阅读更多：https://aws.amazon.com/premiumsupport/knowledge-center/athena-hive-bad-data-error-csv/

- AlizaminJ

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Zerodf · Accepted Answer

处理这些数据的快速而简单的方法：

CSV：

id,height,age,name
1,,26,"Adam"
2,178,28,"Robert"
3,123,34,"Bill, Comma"
4,183,38,"Alex"

DDL：

CREATE EXTERNAL TABLE stackoverflow.test_null_unquoted (
  id INT,
  height INT,
  age INT,
  name STRING
)
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
 LINES TERMINATED BY '\n' -- Or use Windows Line Endings
LOCATION 's3://XXXXXXXXXXXXX/'
TBLPROPERTIES ('skip.header.line.count'='1')
;

问题在于它不能处理最后一个字段中的引号字符。根据AWS提供的文档，这是有道理的，因为LazySimpleSerDe是基于Hive提供的以下内容。我怀疑解决方案是使用以下SerDe org.apache.hadoop.hive.serde2.RegexSerDe。我稍后会处理正则表达式。 编辑： 如承诺的那样，下面是正则表达式：

CREATE EXTERNAL TABLE stackoverflow.test_null_unquoted (
  id INT,
  height INT,
  age INT,
  name STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "(.*),(.*),(.*),\"(.*)\""
)
LOCATION 's3://XXXXXXXXXXXXXXX/'
TBLPROPERTIES ('skip.header.line.count'='1') -- Does not appear to work
;

注意：似乎RegexSerDe在使用TBLPROPERTIES('skip.header.line.count'='1')时无法正常工作。这可能是由于Athena使用的Hive版本或SerDe引起的。在您的情况下，您可以只排除ID为空的行。

进一步阅读： Stackoverflow-在将数据加载到Hive中时删除字段周围的引号 Athena-用于处理CSV的OpenCSVSerDe