hadoop - 使用默认 SerDE 加载 Hive 表数据

Question

数据格式如下

a,"b,c",d,e

p,q,"e,r", t

a,s,"t,g", t

我想创建一个 Hive 表

第 1 列、第 2 列、第 3 列、第 4 列

a,b,c,d,e

p, q, e,r, t

a , s , t,g , t

如上所见，如果数据是用双引号括起来的，那么在创建表数据时就不需要考虑中间的逗号了。如果我使用默认的 SerDe，双引号将被忽略，b、c 被视为两个单独的列。

如果封装在双引号中，如何确保双引号忽略两个元素之间的逗号

Answer 1

如果可能且可行，我首先建议您探索输入数据是否可以通过使用除逗号以外的其他方式作为字段分隔符来清理输入数据。使用数据中可能自然出现的定界模式总是很冒险。

但如果那是不可能的，那么有一种基于正则表达式的方法来检测引用的逗号:

1. 首先将您的数据作为单列行(整行放入每一行)提取到暂存表中。
2. 检测引号之间出现的逗号并将其替换为人工占位符。
3. 使用逗号作为分隔符拆分生成的字符串。
4. 将人造占位符替换为它们最初代表的逗号。

作为一个人为的具体示例，我使用您的数据加载了以下单列暂存表(第 1 步):

hive> DESCRIBE staging;
OK
rawline                     string
Time taken: 0.238 seconds, Fetched: 1 row(s)
hive> SELECT * FROM staging;
OK
a,"b,c",d, e
p,q,"e,r", t
a,s,"t,g", t
Time taken: 0.277 seconds, Fetched: 3 row(s)

接下来的查询会生成最终的目标表。

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    Col1 STRING,
    Col2 STRING,
    Col3 STRING,
    Col4 STRING
  );
INSERT INTO TABLE test SELECT
  regexp_replace(fields[0], "\\[QUOTEDCOMMA\\]", ","),  -- Step #4
  regexp_replace(fields[1], "\\[QUOTEDCOMMA\\]", ","),  -- Step #4
  regexp_replace(fields[2], "\\[QUOTEDCOMMA\\]", ","),  -- Step #4
  regexp_replace(fields[3], "\\[QUOTEDCOMMA\\]", ",")   -- Step #4
FROM (
  SELECT split(  -- Step #2 and #3
    regexp_replace(rawline, "\"([^,]*),([^,]*)\"", "$1[QUOTEDCOMMA]$2"),
    ',') AS fields
  FROM staging
) t;

这将生成以下最终表 test:

hive> SELECT * FROM test;
OK
a   b,c     d        e
p   q       e,r      t
a   s       t,g      t
Time taken: 0.196 seconds, Fetched: 3 row(s)

在此示例实现中，字符串 [QUOTEDCOMMA] 用作引号之间逗号的人工占位符。这个选择完全是任意的，在实践中，如果你走这条路，你会想要确保你的占位符不会自然地出现在你的数据中。