amazon-web-services - 将 csv 数据文件中的数组字段加载到 Athena 表中

Question

这是输入数据文件中的示例行，有两个字段 - 部门和姓名

dept,names
Mathematics,[foo,bar,alice,bob]

这里，'name' 是一个字符串数组，我想将它加载为字符串 Athena 数组。

有什么建议吗？

Answer 1

要获得有效的 CSV 文件，请确保在数组周围加上引号:

Mathematics,"[foo,bar,alice,bob]"

如果您可以删除“[”和“]”，下面的解决方案将变得更加简单，并且您可以在没有正则表达式的情况下进行拆分。

Better: Mathematics,"foo,bar,alice,bob"

首先使用仅包含字符串的 CSV 创建一个简单的表格:

CREATE EXTERNAL TABLE IF NOT EXISTS test.mydataset (
  `dept` string,
  `names` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ',',
  'quoteChar' = '"',
  "separatorChar" = ',',
  'collection.delim' = ',',
  'mapkey.delim' = ':'
) LOCATION 's3://<your location>'
TBLPROPERTIES ('has_encrypted_data'='false')

然后创建一个 View ，该 View 使用正则表达式删除您的“[”和“]”字符，然后通过“,”将其余字符拆分为一个数组。

CREATE OR REPLACE VIEW mydataview AS
SELECT  dept, 
        split(regexp_extract(names, '^\[(.*)\]$', 1), ',') as names
FROM mydataset 

然后使用该 View 进行查询。我不是 100% 确定，因为我只花了大约 12 个小时使用 Athena。

--

请注意，为了使用引号，您需要使用 OpenCSVSerde，'lazyserde' 将不起作用，因为它支持引号。 lazyserde 确实支持内部数组，但在这种情况下不能使用“，”作为分隔符。如果您想尝试一下，您的数据将如下所示:

Better: Mathematics,foo|bar|alice|bob

在那种情况下，这个 MIGHT 可以直接工作:

CREATE EXTERNAL TABLE IF NOT EXISTS test.mydataset (
      `dept` string,
      `names` array<string>
    )
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
    WITH SERDEPROPERTIES (
      'serialization.format' = ',',
      'field.delim' = ',',
      'quoteChar' = '"',
      "separatorChar" = ',',
      'collection.delim' = '|',
      'mapkey.delim' = ':'
    ) LOCATION 's3://<your location>'
    TBLPROPERTIES ('has_encrypted_data'='false')

请注意 collection.delim = '|'，它应该将您的字段直接转换为数组。

抱歉，我没有时间对此进行测试，如果您能确认哪些有效，我将很乐意更新我的答案。希望这能让你开始。