CSV文件的格式规范及其在常见软件中的导入导出方法

计算机技术

作者

zenggyu

发布于

2019-12-04

摘要

介绍CSV文件格式的规范，以及如何在常用软件中导入和导出该类文件。

引言

逗号分隔值（Comma-Separated-Values, CSV）格式是指以半角逗号作为分隔符来分隔文本中不同字段的文件格式。由于存储形式和规则简单，该格式能够被几乎所有数据库、数据分析软件识别，因此常常被用作数据传输的中间格式。

尽管CSV格式被广泛使用，但它们的存储规则仍缺乏一个唯一的标准，不同的软件可能会采用基于不同规范的实现。如果不事先了解各种程序所依据规范并据此做出应对，那么在使用该格式进行数据传输时可能就会对数据完整性造成破坏。

本文将研究几种常用软件对CSV文件的读入和写出行为，以期得到实现数据完整传输的方案。为达到该目的，本文将引入最接近标准的CSV规范——RFC 4180 (Shafranovich 2005) 作为参考依据，研究如何使用各种软件导入导出符合该规范的CSV文件；另外，对于无法兼容该规范的软件，本文还将介绍相应的注意事项。

关于RFC 4180及W3C组织的有关建议

RFC 4180是互联网工程任务组（Internet Engineering Task Force, IETF）在2005年发布的CSV文件规范 (Shafranovich 2005) 。尽管前面提到CSV文件格式仍然缺乏世界公认的标准，但从W3C组织对RFC 4180的介绍 (CSV on the Web Working Group 2015) 可以看出，该规范就是事实上的标准；因此，在处理CSV文件时应尽可能地遵循该规范。

RFC 4180对CSV文件格式的核心定义如下：

文件中的各条记录必须位于不同行，其间以换行符CRLF分隔。例如：

aaa,bbb,ccc CRLF
zzz,yyy,xxx CRLF

最后一条记录的末尾可以不包括换行符。例如：

aaa,bbb,ccc CRLF
zzz,yyy,xxx

文件中的首条记录可以是字段名（但这不是必要的），且其所含的名称数量及存储规则须与其他记录保持一致。

field_name,field_name,field_name CRLF
aaa,bbb,ccc CRLF
zzz,yyy,xxx CRLF

每条记录中可以包含一个或多个字段，每个字段以半角逗号分隔。文件中的所有记录必须拥有相同数量的字段。字段中的空格属于字段取值，不可忽略。每条记录的最后一个字段之后不应再添加半角逗号。例如

aaa,bbb,ccc

每个字段可以用半角双引号括起来（但这不一定是必要的）。如果字段没有被双引号括起来，那么字段中不应该出现双引号。例如：

"aaa","bbb","ccc" CRLF
zzz,yyy,xxx

含有换行符、半角双引号或半角逗号的字段应该用半角双引号括起来。例如：

"aaa","b CRLF
bb","ccc" CRLF
zzz,yyy,xxx

如果字段被半角双引号括起来了，那么在表示字段取值中本身含有的半角双引号时，需要在其前方增加一个半角双引号。例如：

"aaa","b""bb","ccc"

W3C组织在RFC 4180的基础上提出了更多CSV文件的使用建议 (CSV on the Web Working Group 2015) ，其中三项特别值得注意的是：

在（类）Unix系统上，可使用LF（而不是CRLF）作为CSV文件的换行符；
无论是在（类）Unix系统还是在Windows系统上，均建议使用UTF-8作为文本的字符编码；
日期、时间戳类数据必须使用规范的文本格式表示（例如ISO8601）。

测试数据

本文将以表 1 所示数据作为测试数据，来研究如何在各种软件中导入导出符合前述规范的CSV文件。该表格取自维基百科 (Wikipedia 2019) ，笔者对其进行了少量修改，使修改后的数据中同时含有以下特殊字符（串）：

半角逗号：该字符在CSV文件中具有特殊含义，是字段分隔符；
半角双引号：该字符在CSV文件中具有特殊含义，用于封闭含有特殊字符的字段取值；
换行符：该字符在CSV文件中具有特殊含义，是记录分隔符；
反斜杠：有的程序在读取CSV文本时会将该字符视为转义符，使得以该字符开头的字符串表达出特殊含义（例如\t等同于制表符），而RFC 4180并未定义这种用法；
中文字符：中文字符必须使用ASCII以外的字符编码进行存储，这要求相关程序能够按指定的方式对CSV文件内容进行解码、编码；
空字符串（empty string）和空值（null）：表中model字段含有一个空字符串，而description字段含有一个空值（为区别前者，表 1 中的空值以NULL表示，但不同软件有不同的表示方法，详见后文），这两者将被用于测试有关程序是否能对其进行正确区分（注：RFC 4180并没有明确定义空字符串和空值的区分方法，此测试只是为了能够更深入了解有关程序的行为）；
时间戳：时间戳字符串具有特定的格式，还可能涉及时区信息，需要相关程序进行额外的解析。

只要含有上述字符（串）的内容能够被准确地导入、导出，那么理论上同样的方法就能够准确地处理任何内容。

表 1: tb_test数据集（NULL表示空值）

date_time	manufacturer	model	description	price
1997-01-01 00:00:01	福特	E350	ac, abs, moon	3000
1999-01-01 00:00:01	雪佛兰	Venture “Extended Edition”	\t	4900
1999-01-01 00:00:01	雪佛兰		`NULL`	5000
1996-01-01 00:00:01	吉普	Grand Cherokee	MUST SELL! air, moon roof, loaded	4799

以下是表 1 所示数据在一个符合RFC 4180规范的CSV文件tb_test.csv中的存储形式；特别值得注意的是，表中的空字符串被半角双引号所围绕，而空值则没有（见第三行）：

1997-01-01 00:00:01,福特,E350,"ac, abs, moon",3000
1999-01-01 00:00:01,雪佛兰,"Venture ""Extended Edition""",\t,4900
1999-01-01 00:00:01,雪佛兰,"",,5000
1996-01-01 00:00:01,吉普,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799

因为有的数据库软件不支持在CSV文件中存储字段名，所以本文使用不包含字段名的文件进行测试；此外，本文还采纳W3C的建议，以LF作为换行符、UTF8作为字符编码，并选取符合ISO8601标准的时间戳格式存储时间数据。

正文

以下开始介绍如何在各种软件中导入tb_test.csv文件内的数据，以及如何在各软件中生成表 1 所示测试数据并以CSV格式导出。为了充分展示各软件在处理CSV文件时可调节的相关参数、使操作方法能得到推广，本文会尽可能减少对默认参数的依赖，并显式定义各种参数。另外，针对数据库类软件，本文将优先介绍通过类SQL指令实现的方法，因为一般来说各种数据库均会提供这种方法。

R

R语言的readr包¹提供了read_delim()和write_delim()等函数分别用于导入、导出CSV文件。以下是在R语言中导入tb_test.csv的方法：

¹ 本次测试所用的readr包版本为1.3.1。

library(readr)

tb_test_r <- read_delim(
  "tb_test.csv",
  col_names = c("date_time", "manufacturer", "model", "description", "price"),
  col_types = cols(date_time = col_datetime(),
                   manufacturer = col_character(),
                   model = col_character(),
                   description = col_character(),
                   price = col_integer()),
  locale = locale(encoding = "UTF8"),
  delim = ",",
  quote = "\"",
  na = ""
)

导入后的结果如表 3 所示：

表 2: 在R语言中导入tb_test后得到的结果

date_time	manufacturer	model	description	price
1997-01-01 00:00:01	福特	E350	ac, abs, moon	3000
1999-01-01 00:00:01	雪佛兰	Venture “Extended Edition”	\t	4900
1999-01-01 00:00:01	雪佛兰	`NA`	`NA`	5000
1996-01-01 00:00:01	吉普	Grand Cherokee	MUST SELL! air, moon roof, loaded	4799

对比表 1 ，可以发现表 3 中model字段下的空字符串被判断成了空值NA，而其他单元格的数据则与前者一致。

以下代码在R语言中重构了表 1 所示的数据，并使用write_delim()函数将结果导出至tb_test_r.csv文件中：

tb_test <- tibble::tibble(
  date_time = lubridate::ymd_hms(c("1997-01-01 00:00:01", "1999-01-01 00:00:01", "1999-01-01 00:00:01", "1996-01-01 00:00:01")),
  manufacturer = c("福特", "雪佛兰", "雪佛兰", "吉普"),
  model = c("E350", 'Venture "Extended Edition"', "", "Grand Cherokee"),
  description = c("ac, abs, moon", "\\t", NA, "MUST SELL!\nair, moon roof, loaded"),
  price = c(3000, 4900, 5000, 4799)
)

# `write_delim()`函数强制使用UTF-8编码写出结果
write_delim(tb_test,
            "tb_test_r.csv",
            delim = ",",
            na = "",
            col_names = F)

以下是tb_test_r.csv中的内容：

1997-01-01T00:00:01Z,福特,E350,"ac, abs, moon",3000
1999-01-01T00:00:01Z,雪佛兰,"Venture ""Extended Edition""",\t,4900
1999-01-01T00:00:01Z,雪佛兰,"",,5000
1996-01-01T00:00:01Z,吉普,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799

对比tb_test.csv，可以发现tb_test_r.csv中的时间戳字段增加了时区信息（write_demin()强制使用UTC标准时），而其他内容与前者一致。如果想去除时间戳的时区信息，可以在导出数据前先使用as.character()函数将date_time字段转换为字符串类型。

Python

Python的pandas包²提供了read_csv()函数和to_csv()方法分别用于导入、导出CSV文件。以下操作演示了如何在Python中导入tb_test.csv：

² 本次测试所用的pandas包版本为0.25.3。

import pandas as pd

tb_test_py = pd.read_csv(
  "tb_test.csv",
  names = ["date_time", "manufacturer", "model", "description", "price"],
  dtype = {"date_time": str, "manufacturer": str, "model": str, "description": str, "price": int},
  parse_dates = ["date_time"],
  encoding = "UTF8",
  sep = ",",
  quotechar = "\"",
  na_values = ""
)

导入后的结果如表 3 所示：

表 3: 在Python语言中导入tb_test后得到的结果

date_time	manufacturer	model	description	price
1997-01-01 00:00:01	福特	E350	ac, abs, moon	3000
1999-01-01 00:00:01	雪佛兰	Venture “Extended Edition”	\t	4900
1999-01-01 00:00:01	雪佛兰	`NaN`	`NaN`	5000
1996-01-01 00:00:01	吉普	Grand Cherokee	MUST SELL! air, moon roof, loaded	4799

对比表 1 ，可以发现表 3 中model字段下的空字符串被判断成了空值NaN，而其他单元格的数据则与前者一致。

以下代码在Python中重构了表 1 所示的数据，并使用to_csv()方法将结果导出至tb_test_py.csv文件中：

tb_test = pd.DataFrame(
  {"date_time": pd.to_datetime(["1997-01-01 00:00:01", "1999-01-01 00:00:01", "1999-01-01 00:00:01", "1996-01-01 00:00:01"], format = "%Y-%m-%d %H:%M:%S"),
  "manufacturer": ["福特", "雪佛兰", "雪佛兰", "吉普"],
  "model": ["E350", 'Venture "Extended Edition"', "", "Grand Cherokee"],
  "description": ["ac, abs, moon", "\\t", None, "MUST SELL!\nair, moon roof, loaded"],
  "price": [3000, 4900, 5000, 4799]}
)

tb_test.to_csv("tb_test_py.csv",
               sep = ",",
               na_rep = "",
               header = False,
               encoding = "UTF8",
               index = False,
               quotechar = "\"")

以下是tb_test_py.csv中的内容：

1997-01-01 00:00:01,福特,E350,"ac, abs, moon",3000
1999-01-01 00:00:01,雪佛兰,"Venture ""Extended Edition""",\t,4900
1999-01-01 00:00:01,雪佛兰,,,5000
1996-01-01 00:00:01,吉普,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799

对比tb_test.csv，可以发现tb_test_py.csv的内容与前者基本一致，但没有对空值和空字符串进行区分。

PostgreSQL

PostgreSQL³内置的COPY指令可以用来导入、导出CSV文件。以下操作演示了如何在PostgreSQL中导入tb_test.csv：

³ 本次测试所用的PostgreSQL数据库版本为12.1。

CREATE TABLE tb_test_pg (
  date_time    TIMESTAMP,
  manufacturer VARCHAR(200),
  model        VARCHAR(200),
  description  VARCHAR(200),
  price        INTEGER
);

COPY tb_test_pg
  FROM 'tb_test.csv'
  WITH (FORMAT CSV, ENCODING 'UTF8', DELIMITER ',', QUOTE '"', NULL '', HEADER FALSE
  );

导入后的结果如表 4 所示：

表 4: 在PostgreSQL中导入tb_test后得到的结果

date_time	manufacturer	model	description	price
1997-01-01 00:00:01	福特	E350	ac, abs, moon	3000
1999-01-01 00:00:01	雪佛兰	Venture “Extended Edition”	\t	4900
1999-01-01 00:00:01	雪佛兰		`NULL`	5000
1996-01-01 00:00:01	吉普	Grand Cherokee	MUST SELL! air, moon roof, loaded	4799

对比表 1 ，可以发现表 4 所示内容完全与前者一致，符合预期。

以下代码显示了如何将tb_test_pg中的数据导出至tb_test_pg.csv文件中：

COPY tb_test_pg
  TO 'tb_test_pg.csv'
  WITH (FORMAT CSV, ENCODING 'UTF8', DELIMITER ',', QUOTE '"', NULL '', HEADER FALSE
  );

以下是tb_test_pg.csv中的内容：

1997-01-01 00:00:01,福特,E350,"ac, abs, moon",3000
1999-01-01 00:00:01,雪佛兰,"Venture ""Extended Edition""",\t,4900
1999-01-01 00:00:01,雪佛兰,"",,5000
1996-01-01 00:00:01,吉普,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799

对比tb_test.csv，可以发现tb_test_py.csv的内容与前者完全一致。

MySQL

MySQL⁴内置的LOAD DATA指令可以用来导入CSV文件。以下操作演示了如何在MySQL中导入tb_test.csv：

⁴ 本次测试所用的MySQL数据库版本为5.8。

CREATE TABLE tb_test_mysql (
  date_time    DATETIME,
  manufacturer VARCHAR(100),
  model        VARCHAR(100),
  description  VARCHAR(100),
  price        INT
);

LOAD DATA
  INFILE 'tb_test.csv'
    INTO TABLE tb_test_mysql
    CHARACTER SET 'UTF8'
    FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '\\'
    IGNORE 0 LINES;

导入后的结果如表 5 所示：

表 5: 在MySQL中导入tb_test后得到的结果

date_time	manufacturer	model	description	price
1997-01-01 00:00:01	福特	E350	ac, abs, moon	3000
1999-01-01 00:00:01	雪佛兰	Venture “Extended Edition”		4900
1999-01-01 00:00:01	雪佛兰			5000
1996-01-01 00:00:01	吉普	Grand Cherokee	MUST SELL! air, moon roof, loaded	4799

对比表 1 ，可以发现表 5 中description字段下的空值被识别成了空字符串，且该字段下的字符串\t被错误地识别成了制表符。

以下代码在MySQL中重构了表 1 所示的数据，并使用SELECT INTO指令将结果导出至tb_test_mysql.csv文件中：

TRUNCATE tb_test_mysql;
INSERT INTO
  tb_test_mysql
VALUES
  ('1997-01-01 00:00:01', '福特', 'E350', 'ac, abs, moon', 3000),
  ('1999-01-01 00:00:01', '雪佛兰', 'Venture "Extended Edition"', '\\t', 4900),
  ('1999-01-01 00:00:01', '雪佛兰', '', NULL, 5000),
  ('1996-01-01 00:00:01', '吉普', 'Grand Cherokee', 'MUST SELL!\nair, moon roof, loaded', 4799);

SELECT * FROM tb_test_mysql
INTO OUTFILE 'tb_test_mysql.csv'
  CHARACTER SET 'UTF8'
  FIELDS
  TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  ESCAPED BY '\\';

以下是tb_test_mysql.csv中的内容：

"1997-01-01 00:00:01","福特","E350","ac, abs, moon",3000
"1999-01-01 00:00:01","雪佛兰","Venture \"Extended Edition\"","\\t",4900
"1999-01-01 00:00:01","雪佛兰","",\N,5000
"1996-01-01 00:00:01","吉普","Grand Cherokee","MUST SELL!\
air, moon roof, loaded",4799

对比tb_test.csv，可以发现tb_test_mysql.csv中除了数值以外的字段均被用半角双引号括了起来；数据中所含的半角双引号、反斜杠、换行符等特殊符号均前置了反斜杠进行转义；另外，空值必须使用字符串\N表示。

Hive

Hive内置的LOAD DATA指令可以用来导入CSV文件。以下操作演示了如何在Hive中导入tb_test.csv：

CREATE TABLE tb_test_hive (
  date_time    TIMESTAMP,
  manufacturer STRING,
  model        STRING,
  description  STRING,
  price        INT
)
ROW FORMAT delimited
FIELDS TERMINATED BY ',' ESCAPED BY '\\' NULL defined AS ''
STORED AS textfile;

LOAD DATA LOCAL INPATH 'tb_test.csv' INTO TABLE tb_test_hive;

导入后的结果如表 6 所示：

表 6: 在Hive中导入tb_test后得到的结果

date_time	manufacturer	model	description	price
1997-01-01 00:00:01	福特	E350	“ac	`NULL`
1999-01-01 00:00:01	雪佛兰	“Venture”“Extended Edition”“”	t	4900
1999-01-01 00:00:01	雪佛兰	“”	`NULL`	5000
1996-01-01 00:00:01	吉普	Grand Cherokee	“MUST SELL!	`NULL`
`NULL`	moon roof	loaded”	4799	`NULL`
`NULL`	`NULL`	`NULL`	`NULL`	`NULL`

对比表 1 ，可以发现表 6 的数据完整性遭受了严重的破坏；并且，还可以观察到以下情况：1. 半角双引号不能被识别为封闭字符，而是作为字段取值被读入；2. 反斜杠既未被当做普通字符读入，也未将字母t转义成制表符；3. 换行符被强行视作记录分隔符，且空行会被视为一条字段全为空的记录。

以下代码试图在Hive中重构表 1 所示的数据，并使用INSERT指令将结果导出至tb_test_hive/目录中：

TRUNCATE TABLE tb_test_hive;
INSERT INTO TABLE
  tb_test_hive
VALUES
  ('1997-01-01 00:00:01', '福特', 'E350', 'ac, abs, moon', 3000),
  ('1999-01-01 00:00:01', '雪佛兰', 'Venture "Extended Edition"', '\\t', 4900),
  ('1999-01-01 00:00:01', '雪佛兰', '', NULL, 5000),
  ('1996-01-01 00:00:01', '吉普', 'Grand Cherokee', 'MUST SELL!\nair, moon roof, loaded', 4799);

INSERT OVERWRITE LOCAL DIRECTORY 'tb_test_hive/'
  ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ',' ESCAPED BY '\\'
  NULL defined AS ''
  SELECT * FROM tb_test_hive;

以下是tb_test_hive/目录中文件的内容：

1997-01-01 00:00:01,福特,E350,ac\, abs\, moon,3000
1999-01-01 00:00:01,雪佛兰,Venture "Extended Edition",\\t,4900
1999-01-01 00:00:01,雪佛兰,,,5000
1996-01-01 00:00:01,吉普,Grand Cherokee,MUST SELL!,
,4799,,,

从结果可以看出，字段中内嵌的换行符被识别成了记录分隔符，导致数据完整性遭到了破坏。实际上，若检视Hive中tb_test_hive表中的内容，会发现同样的问题；这表明在使用INSERT INTO TABLE ... VALUES语句生成表记录时，换行符已经被识别成记录分隔符。上述情况提示Hive中的字段取值不可包含换行符。

小结

R语言readr包对CSV文件导入导出操作均能够符合RFC 4180规范和W3C组织的有关建议，但有两点需要注意。首先，在导入CSV文件时，该软件包不能准确地区分空字符串和空值；为解决此问题，可以预先定义一个非空字符串代表空值，以之区别空字符串。其次，该软件包导出的CSV文件中的时间戳数据带有时区信息，有时这并不是使用者想要的；为解决此问题，可以在导出数据前先使用as.character()函数将时间戳字段转换为字符型字段。

Python的pandas包对CSV文件导入导出操作也均能够符合RFC 4180规范和W3C组织的有关建议。但需要注意的是，无论是导入还是导出CSV文件，该软件包均不区分空字符串和空值；为解决此问题，可以预先定义一个非空字符串代表空值，以之区别空字符串。

PostgreSQL的COPY指令对CSV文件的导入导出操作完全符合RFC 4180规范和W3C组织的有关建议，并且能够完整地按照预期处理复杂的测试数据，因此大可放心地使用。

MySQL的LOAD DATA INFILE和SELECT INTO OUTFILE指令的行为与RFC 4180和W3C组织的有关建议存在较大的差异，使用者利用MySQL处理CSV文件时需要多加注意。以下是根据笔者经验总结的几条使用建议：

在使用LOAD DATA INFILE导入CSV文件或使用SELECT INTO OUTFILE导出CSV文件时，均使用\作为转义字符、"作为封闭字符；
在使用LOAD DATA INFILE导入CSV文件时：
- 若数据本身含有特殊字符（包括转义字符、封闭字符、记录分隔符、字段分隔符等），则先将数据中的转义字符替换为两个连续的转义字符，然后再在其他特殊字符前面前置一个转义字符；
- 数据中的空值以\N表示。

例如，针对tb_test中的数据，在R语言中可以以如下方式（假设转义字符为\、封闭字符为"、记录分隔符为\n、字段分隔符为,）导出CSV文件，使之能够被顺利地读入MySQL中：

tb_test %>%
  mutate_if(is.character, ~gsub("\\", "\\\\", ., fixed = T)) %>%
  mutate_if(is.character, ~gsub("\n", "\\\n", ., fixed = T)) %>%
  mutate_if(is.character, ~gsub("\"", "\\\"", ., fixed = T)) %>%
  mutate_if(is.character, ~gsub(",", "\\,", ., fixed = T)) %>%
  mutate_if(lubridate::is.POSIXt, ~as.character(.)) %>% # 去除时区信息
  write_csv("tb_test.csv", na = "\\N", quote_escape = "none", col_names = F)

与上述各种软件相比，Hive的LOAD DATA和INSERT指令对CSV文件的处理方式与RFC 4180及W3C组织的有关建议的吻合度最低，因此在使用时需多加注意。其中，尤其需要注意的是换行符在Hive中被强制视为记录分隔符，因此为保证数据完整性，字段中绝不可含有该字符。此外，反斜杠只能对字段分隔符进行转义，使后者能够嵌入字段取值中，但对其他字符则无转义作用（制表符等特殊字符只能以该字符本身表示）。

注意事项：如果要用R（或Python）等软件读入从Hive导出的CSV文件时，最好将封闭字符设置为空。这是因为后者输出的CSV文件不含封闭字符，而前者在读取文件时会默认文件含有封闭字符；这可能导致某些换行符在读入时被误认为是数据内容，导致最终所得的行数变少。同样地，从R（或Python）等软件生成用于导入Hive的CSV文件时，也应该将封闭字符设为空，否则封闭字符将出现在字段取值之中。

附录

本附录提供了一些shell函数，可以用于在常见数据库中导入或导出csv文件。

hive_to_csv () {
  # 用法：hive_to_csv [选项]... 源表 目标文件

  local delim='\t'
  local escape='\\'
  local null='\\N' # 与MySQL的LOAD DATA指令相兼容
  local table=${@: -2:1}
  local file=${@: -1:1}

  local OPTIND
  while getopts 'd:e:n:' OPTION; do
    case "${OPTION}" in
      d) local delim="${OPTARG}";;
      e) local escape="${OPTARG}";;
      n) local null="${OPTARG}";;
    esac
  done

  if [[ -z "${table}" || -z "${file}" ]]; then
    echo "请指定源表和目标文件"
    exit 1
  fi

  if [[ -f "${file}" ]]; then
    rm "${file}"
  elif [[ -d "${file}" ]]; then
    echo "${file} 是一个目录"
    exit 1
  fi

  sql=$(cat <<eof
    INSERT OVERWRITE LOCAL DIRECTORY '${file}'
      ROW FORMAT DELIMITED
      FIELDS TERMINATED BY '${delim}' ESCAPED BY '${escape}'
      NULL DEFINED AS '${null}'
      SELECT * FROM ${table};
eof
  )

  hive -e "${sql}"

  if [[ ${?} -ne 0 ]]; then exit 1; fi

  find ${file} -regextype egrep -regex '.*/[0-9_]*' -exec cat {} \; > ${file}.tmp && rm -r ${file} && mv ${file}.tmp ${file} # 注意不要将*.crc文件和数据文件混到一起

  return
}

csv_to_mysql () {
  # 用法：csv_to_mysql [选项]... 源文件 目标表
  # 运行此函数前必须先通过alias指定数据库登录信息
  # alias mysql="mysql -h $host -u ${user} -D ${database} --password ${password}"）

  local delim='\t'
  local escape='\\'
  local quote='"'
  local skip=0
  local file=${@: -2:1}
  local table=${@: -1:1}
  
  local OPTIND
  while getopts 'd:e:q:s:' OPTION; do
    case "${OPTION}" in
      d) local delim="${OPTARG}";;
      e) local escape="${OPTARG}";;
      q) local quote="${OPTARG}";;
      s) local skip="${OPTARG}";;
    esac
  done

  if [[ -z "${table}" || -z "${file}" ]]; then
    echo "请指定源文件和目标表"
    exit 1
  fi

  sql=$(cat <<eof
    LOAD DATA
      INFILE '${file}'
        INTO TABLE ${table}
        CHARACTER SET 'UTF8'
        FIELDS
        TERMINATED BY '${delim}'
        OPTIONALLY ENCLOSED BY '${quote}'
        ESCAPED BY '${escape}'
        IGNORE ${skip} LINES;
eof
  )

  mysql -e "${sql}"

  if [[ ${?} -ne 0 ]]; then exit 1; fi

  return
}

参考文献

CSV on the Web Working Group. 2015. 《Model for Tabular Data and Metadata on the Web》. W3C Recommendation. 2015年. https://www.w3.org/TR/tabular-data-model/.

Shafranovich, Yakov. 2005. 《Common Format and MIME Type for Comma-Separated Values (CSV) Files》. Internet Engineering Task Force. https://www.ietf.org/rfc/rfc4180.txt.

Wikipedia. 2019. 《Comma-Separated Values》. 2019年. https://en.wikipedia.org/wiki/Comma-separated_values.