Hive导出ORC表数据所遇到的乱码问题及其它导出问题

背景

今天遇到一个临时需求，需求 Hive 中的表数据导出成文件的形式。以为很简单，谁知道遇到一些问题，所以记录下来了。

具体执行步骤

导出表：
连接 linux，输入如下命令：

hdfs dfs -get /user/hive/warehouse/student/dt=20230619 /opt/module/hive/datas/export/student.txt;

导出文件后，查看文件内容，然后发现乱码，然后查了些资料发现，hdfs dfs -get 命令不适用于导出 ORC 格式的数据，因为 ORC 是一种二进制格式，而 hdfs dfs -get 命令默认将二进制文件视为文本文件进行解析，从而导致乱码。

所以我们需要将 ORC 格式的数据转换为 TEXTFILE 格式的数据，从而保证 hdfs dfs -get 名利导出的数据不会乱码。

要将 ORC 数据转换为 TEXTFILE 格式数据，可以使用 Hive 的 INSERT OVERWRITE 语句并指定输出文件格式。具体步骤如下：

1. 创建一个新的表用于存储转换后的数据，表结构和原始 ORC 表一致，但是文件格式为 TextFile。

CREATE TABLE student_text (
  id INT,
  name STRING,
  age INT
)
partition by（dt STRING COMMENT 'yyyyMMdd'）
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

2. 执行 INSERT INTO 语句将 ORC 表中的数据导入到新建的 TEXTFILE 表中。

INSERT OVERWRITE TABLE student_text partition(dt = '20230619')
SELECT id, name, age
FROM student where dt = '20230619';

但是当运行上述插入语句时，任务报错，报错主要信息如下：

java.lang.ClassCastException: org.apache.hadoop.hive.ql.io.orc.OrcSerde$OrcSerdeRow cannot be cast to org.apache.hadoop.io.BytesWritable

具体原因可以了解如下文章：
https://www.cnblogs.com/shudazhaofeng/p/15867919.html

这个问题是 Spark Sql 特有的，我们直接使用 Hive Sql 来进行上面的数据格式的转换操作就可以了。

这样就将 ORC 格式的数据转换为 TEXTFILE 格式的数据，然后我们直接将临时表的数据导出为文件形式，具体如下：

hdfs dfs -get /user/hive/warehouse/student_text/dt=20230619 /opt/module/hive/datas/export/student.txt;

此时我们再查看导出的文件中的数据就不会乱码了。

其它导出表数据的方法

1. hive -e 方法

• 导出带字段名的 csv 文件，并将数据字段值之间的制表符 (\t) 替换为逗号 (,)，这样导出的 csv 文件各个字段列才会分开(因为 csv 文件各个列之间的分割符就是逗号)。

hive -e "set hive.cli.print.header=true; select * from table_name where test_table" | sed 's/[\t]/,/g'  > test.csv

• 上面的方法有一个问题，就是如果某些字段数据值中包含英文下的逗号的话，那么就会将这个值直接切分开成两列，这样的话就会导致取出的数据有问题，所以上面的代码需要做些修改，如下：

hive -e "set hive.cli.print.header=true;select * from test_table where dt = '20230801';" | sed 's/[\t]/","/g; s/^/"/; s/$/"/' > /home/test.csv

这样导出的 csv 文件就不会影响到字段值中包含的逗号 (,) 了。

注意：下面方法未验证，均为 chatGPT 提供

• 其实上面的代码还是有点问题，就是如果字段中包含逗号和制表符 (\t)呢

hive -e "SELECT * FROM database.table" | awk 'BEGIN { FS="\t"; OFS="," } { for (i=1; i<=NF; i++) gsub(/,/,"\\,",$i) } 1' > /usr/lxb/hive/test.csv

在这个命令中，我们使用了 awk 工具来处理Hive查询结果。首先，我们将输入字段分隔符（FS）设置为制表符（\t），输出字段分隔符（OFS）设置为逗号（,）。然后，在每个字段上使用 gsub 函数，将其中的逗号替换为转义逗号（\\,）。最后，使用1参数将结果输出到 CSV 文件中。

这个方法会保留字段中的逗号和制表符，并将其它逗号作为字段分隔符。但需要注意，当读取CSV文件时，需要使用相同的处理逻辑来解析内容，以避免错误地将逗号或制表符识别为字段分隔符。

• 如果字段值中包含换行符和回车符呢

regexp_replace(col, '\n|\t|\r|\r\n', '')
regexp_replace(col, '\\n|\\t|\\r|\\r\\n', '')
regexp_replace(col, '\\\n|\\\t|\\\r|\\\r\\\n', '')
regexp_replace(col, '\\\\n|\\\\t|\\\\r|\\\\r\\\\n', '')

最简单的就是直接使用函数处理掉数据中包含的回车符（\r）和换行符（\n）以及制表符（\t）。
因为转义的问题，可以尝试上面几种方式，目前测试前三种都可以去掉回车符（\r）和换行符（\n）。