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文件格式的核心定义如下:

  1. 文件中的各条记录必须位于不同行,其间以换行符CRLF分隔。例如:
aaa,bbb,ccc CRLF
zzz,yyy,xxx CRLF
  1. 最后一条记录的末尾可以不包括换行符。例如:
aaa,bbb,ccc CRLF
zzz,yyy,xxx
  1. 文件中的首条记录可以是字段名(但这不是必要的),且其所含的名称数量及存储规则须与其他记录保持一致。
field_name,field_name,field_name CRLF
aaa,bbb,ccc CRLF
zzz,yyy,xxx CRLF
  1. 每条记录中可以包含一个或多个字段,每个字段以半角逗号分隔。文件中的所有记录必须拥有相同数量的字段。字段中的空格属于字段取值,不可忽略。每条记录的最后一个字段之后不应再添加半角逗号。例如
aaa,bbb,ccc
  1. 每个字段可以用半角双引号括起来(但这不一定是必要的)。如果字段没有被双引号括起来,那么字段中不应该出现双引号。例如:
"aaa","bbb","ccc" CRLF
zzz,yyy,xxx
  1. 含有换行符、半角双引号或半角逗号的字段应该用半角双引号括起来。例如:
"aaa","b CRLF
bb","ccc" CRLF
zzz,yyy,xxx
  1. 如果字段被半角双引号括起来了,那么在表示字段取值中本身含有的半角双引号时,需要在其前方增加一个半角双引号。例如:
"aaa","b""bb","ccc"

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

  1. 在(类)Unix系统上,可使用LF(而不是CRLF)作为CSV文件的换行符;
  2. 无论是在(类)Unix系统还是在Windows系统上,均建议使用UTF-8作为文本的字符编码;
  3. 日期、时间戳类数据必须使用规范的文本格式表示(例如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语言的readr1提供了read_delim()write_delim()等函数分别用于导入、导出CSV文件。以下是在R语言中导入tb_test.csv的方法:

1 本次测试所用的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的pandas2提供了read_csv()函数和to_csv()方法分别用于导入、导出CSV文件。以下操作演示了如何在Python中导入tb_test.csv

2 本次测试所用的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

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

3 本次测试所用的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

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

4 本次测试所用的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 INFILESELECT 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 DATAINSERT指令对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.