access 导入 txt sql语句_用 SQL 而不是 Python 处理文本数据

本文的英语版本在 https://medium.com/@yi.wang.2005/nlp-in-sql-word-vectors-82dffc908423

很多朋友们以为 SQL 是用来处理结构化数据的；而文本是典型的非结构化数据（unstructured data）。其实 SQL 可以处理文本，而且比 Python 更方便 。

这篇文章简要介绍如何用 MySQL 来分词，构造词表，词向量，和计算文本的相似度。本文中的例子，用 TiDB 应该也可以跑通。微调之后，Apache Hive 和阿里巴巴 MaxCompute 应该也是可以的。

导入数据

为了简单，我们用一个只有三行（三个文档）的文本文件（a.txt）作为原始数据。MySQL 只支持从特定的目录导入文件中的数据。可以用如下 SQL 语句查询这个目录：

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
           

把 a.txt 拷贝到这个目录（/var/lib/mysql-files/）之后，可以用如下语句导入创建一张表，并且导入数据。因为表里的 id 是自动生成的，所以导入过程会给每一行（每一个文档）分配一个文档 id。

CREATE 
           

现在我们可以检查一下结果

mysql> SELECT * FROM docs;
+----+------------------------+
| id | doc                    |
+----+------------------------+
|  1 | fresh carnation flower |
|  2 | mother day             |
|  3 | mother teresa          |
+----+------------------------+
           

分词

有一些数据库系统，比如阿里云上的 MaxCompute 提供分词用的 UDF，是一个特色。本文假设没有这样的功能。仅仅按照空格来分词，SQL 也是可以通过 inner join 做到的。

因为分词是把一个字符串变成多条记录。具体的说，要取出字符串中第一个、第二个、第三个。。。子串。所以我们需要一个自然数序列。我们可以通过上面例子里自动产生文档 ID 的机制，生成这个序列。下面的语句创建一个表 incr，其中只有一列，是自动产生的自然数序列。

DROP 
           

上面语句创建了 SQL 子程序（procedure），其中的循环往 incr 表里增加了 5 条记录，从而产生了一个 1 到 5 的自然数序列。我们可以修改其中的 5 为其他任何数值，来创建更长或者更短的序列。

mysql> select * from incr;
+----+
| n |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
           

利用这个序列，我们可以把每个字符串分割成最多 5 个（或者更多）的子串。

CREATE 
           

上面语句里的 join 操作把每条记录（字符串，或者叫文档）复制了 5 份；而 SELECT 操作选取每个复制中的第 i 个子串（word）；CREATE TABLE 把结果写入一张新的表 doc_words，其内容如下。

mysql> select * from doc_words;
+----+-----------+
| id | word      |
+----+-----------+
|  1 | fresh     |
|  1 | carnation |
|  1 | flower    |
|  2 | mother    |
|  2 | day       |
|  3 | mother    |
|  3 | teresa    |
+----+-----------+
           

停用词

很多时候，我们回想剔除分词结果中的停用词（stopwords）。假设我们有一个停用词表 —— 下文中用 (SELECT 'fresh'）替代 —— 假设这个词表里只有一个单词了，下面语句剔除掉 doc_words 表中的停用词。

mysql> SELECT * FROM doc_words WHERE word NOT IN (SELECT 'fresh');
+----+-----------+
| id | word      |
+----+-----------+
|  1 | carnation |
|  1 | flower    |
|  2 | mother    |
|  2 | day       |
|  3 | mother    |
|  3 | teresa    |
+----+-----------+
           

词向量

仅仅分词还不足以计算文档距离，还需要统计每个文档里，每个词出现的次数 —— 也就是词向量。下面的 SQL 语句可以很方便地做这件事。

CREATE 
           

我们看看结果。

mysql> SELECT * FROM doc_word_count;
+----+-----------+-------+
| id | word      | count |
+----+-----------+-------+
|  1 | carnation |     1 |
|  1 | flower    |     1 |
|  1 | fresh     |     1 |
|  2 | day       |     1 |
|  2 | mother    |     1 |
|  3 | mother    |     1 |
|  3 | teresa    |     1 |
+----+-----------+-------+
           

归一化词向量

通过归一化词向量，我们可以得到一个文档的词分布（word distribution）；这是计算文档相似度的输入。为了归一，需要能统计文档的长度，这可以通过 GROUP BY id 来实现。

mysql> SELECT id, sum(count) as len FROM doc_word_count GROUP BY id;
+----+------+
| id | len  |
+----+------+
|  1 |    3 |
|  2 |    2 |
|  3 |    2 |
+----+------+
           

基于上述方法，下面的 SQL 语句从 doc_words 表推导出 doc_word_dist 表，表示词分布。

CREATE 
           

我们检查一下结果。

mysql> SELECT * FROM doc_word_dist;
+----+-----------+--------+
| id | word      | prob   |
+----+-----------+--------+
|  1 | carnation | 0.3333 |
|  1 | flower    | 0.3333 |
|  1 | fresh     | 0.3333 |
|  2 | day       | 0.5000 |
|  2 | mother    | 0.5000 |
|  3 | mother    | 0.5000 |
|  3 | teresa    | 0.5000 |
+----+-----------+--------+
           

文档相似度

有了归一化的词向量，下面语句计算文档之间的两两相似度（pairwise similarity）。我们用的是 dot product similarity。

SELECT 
           

在这个非常简单的例子里，第二个和第三个文档里共同出现了一个单词“mother”。而其他任何文档对（pairs）都没有共用的词，所以结果只有一行。

+----+----+--------------------+
| id | id | sum(x.prob*y.prob) |
+----+----+--------------------+
|  3 |  2 |         0.25000000 |
+----+----+--------------------+
           

AI + SQL

从这个例子我们可以看到。虽然文档 2 和 3 在词向量空间有一定相似度，但是其实一个是关于特蕾莎修女，一个是关于母亲节 —— 英语里 mother 有修女和母亲两个意思 —— 这结果不合理。反而是 文档 1 “康乃馨” 是母亲节必备的礼物，应该存在一定的相似度。

不管我们用 SQL 还是 Python 来做文本分析，我们都希望借助 AI 的力量深刻理解文本，而不是仅仅在字面上做聚类等分析。接下来的文章，我们会更新如何利用 SQLFlow 扩展 SQL，引入 latent topic modeling 技术来做语义理解。

相关数据

上文中为了演示方便，用了很小的数据集（只有三个文档）。对于程序员来说，一个更有意义的数据集是 GitHub 上一些著名开源项目里的 code review comments —— 我们可以看看各个项目里的 code review 都在说些什么内容；大牛是怎么表达意见的。

为此，我们写了一个调用 GitHub API 的 crawler：

https://github.com/wangkuiyi/code-review-what/blob/master/crawl/crawl.go​github.com

这个 crawler 的注释里有用法。我们试着爬下来了 TensorFlow 和 PyTorch（当然还有我们自己的项目 SQLFlow）的 code review comments。其中 PyTorch 的最多，有 20MB 左右。欢迎大家用上述方法，试试处理这样的数据。我们也很期待听到大家关于用 SQL 处理文本数据的反馈。