数据治理 | “把大象装进冰箱的第二步”：海量微观数据如何“塞进”数据库？

我们将在数据治理板块中推出一系列原创推文，帮助读者搭建一个完整的社科研究数据治理软硬件体系。该板块将涉及以下几个模块：

1. 计算机基础知识

1. 编程基础
2. 数据采集
3. 数据存储

(1)安装篇 数据治理 | 遇到海量数据stata卡死怎么办？这一数据处理利器要掌握

(2)管理篇 数据治理 | 多人协同处理数据担心不安全？学会这一招，轻松管理你的数据团队

(3)本期内容：数据治理 | “把大象装进冰箱的第二步”：海量微观数据如何“塞进”数据库？

1. 数据清洗
2. 数据实验室搭建

Part1引言

阅读本文前，请先阅读之前的文章。

先前，我们搭建了一个mysql数据库，但里面空空如也。相信大家手头上有很多数据需要导入到mysql数据库中，本节介绍如何使用 KETTLE 工具，将我们手头上的 csv、excel 等文件导入mysql数据库。

Part2Kettle 的介绍和安装

ETL 与 Kettle

我们将csv、excel等文件中的内容转移到mysql数据库的这一过程，有一个专业的术语：ETL。

ETL（Extract-Transform-Load）是用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。csv、excel等文件就是数据的来源端，mysql数据库是我们的目的端。

在实际工作中，我们往往要和多种数据源打交道，例如：txt文件、esv文件、excel文件等等。我们要把这些数据导入到指定的数据库中，就需要一个高效的ETL工具。

Kettle是一个开源的ETL工具，主要作者是 Matt Casters，2006年，Kettle 加入了开源的BI组织Pentaho，正式命名为 PDI（Pentaho Data Integration），Kettle 是 PDI 以前的名称。2017年，PDI（Kettle）被日立集团下的公司收购。

Kettle（PDI）可以完成数据抽取、数据清洗、数据转换、数据过滤等多种数据处理工作。可以说凡是有数据整合、转换、迁移的场景都可以使用Kettle，它代替了完成数据转换任务所需的代码编写，降低了开发难度。

下载 Kettle

Kettle 的下载地址为：https://sourceforge.net/projects/pentaho/files/，点击【Download Latest Version】下载。

下载完毕后，解压文件到指定位置，不妨将解压文件放到D盘，解压后的文件叫【data-integration】。

下载并安装 Java

运行 kettle 前，需要安装java环境。java下载地址：https://www.oracle.com/java/technologies/downloads/#java8-windows。

下载完毕后，双击运行【jdk-8u311-windows-x64.exe】。点击【下一步】。

记住【jdk】的安装位置，点击【下一步】。

继续点击【下一步】，安装【jre】。

稍等片刻后安装成功，点击【关闭】退出。

将jdk下的bin目录添加到系统变量中。

右击此电脑，点击【属性】。

点击【高级系统设置】。

点击【环境变量】。

选择【系统变量】窗口中的【Paht】，然后点击下方的【编辑】。

在弹窗中，点击【新建】，将jdk的bin目录路径添加进去，最后依次点击【确定】，退出所有窗口。

打开【命令提示符】，输入 java -version，出现java的版本信息，说明java安装成功。

下载 mysql jdbc 驱动

运行kettle前，还需要配置mysql驱动，否则无法连接到mysql服务器。

mysql jdbc 下载连接：https://downloads.mysql.com/archives/c-j/。

解压后，得到【mysql-connector-java-8.0.26.jar】。

将【mysql-connector-java-8.0.26.jar】复制到【D:\data-integration\lib】目录下。

Part3Kettle 连接 MySQL

在具体介绍Kettle功能前，我们先连接上MySQL数据库，并熟悉一下Kettle的界面。

进入【D:\data-integration】，找到【Spoon.bat】,右击【打开】，启动Kettle。

稍等片刻后，进入欢迎页面。

点击【文件】，【新建】，【转换】，打开转换的编辑页面。

页面左侧是可选的功能组件。

【主对象树】是转换可以连接的数据库对象，【核心对象】是转换可以调用的功能，我们点击【主对象树】下的DB连接。

在弹出的【数据库连接】中，安装下图所示，填写信息。

在【主对象树】中点击【DB连接】，选择创建好的mysql连接【mysql】，右击点击【共享】。这样做的好处是，当我们再创建一个新的转换，也可以使用当前转换的mysql连接，不需要重复操作。

点击【文件】、【保存】，或者使用 快捷键【ctrl + s】，保存当前转换，命名为【mysql 数据库连接】。

Part4Kettle 组件介绍

大家安装上文的指示配置好mysql连接后，可能是一头雾水，本节就带大家熟悉一下Kettle这个强大的ETL工具。

Kettle的主要组件

我们进入Kettle软件所在目录【D:\data-integration】，在该目录下有几个重要的组件

• Spoon.bat：快速设计、维护和复制ETL工作流的图形化工具，能调用Kitchen和Pan
• Kitchen.bat：运行作业的命令行工具，运行一次作业后就退出
• Pan.bat：运行转换的命令行工具, 用于执行由Spoon编辑的转换和作业
• Carte.bat：一个轻量级Web容器，用于建立远程的 ETL Server 在这四个组件中，我们最常用到的组件是Spoon.bat。

作业与转换

转换由一些基本步骤组成，本质是数据的流动，从源端转移到目的端，转移过程中可以对数据进行处理，简单地说，一个转换就是一个ETL过程。

作业是一系列转换和其他作业的集合，用于实现复杂的ETL工作，最常用的功能是实现循环。可以点击【文件】、【新建】创建一个【转换】或者【作业】，也可以使用快捷键创建。

主对象树和核心对象

当我们新建一个转换或作业后，Kettle最左侧的边栏会有两个选项：【主对象树】和【核心对象】。

主对象树中常用的功能如下，图中左侧是作业的主对象树，右侧是转换的主对象树：

• Run configurations：用于查看当前的运行设置。
• DB连接：查看当前数据库连接，也可以对当前作业和转换新建一个数据库连接。
• Step（步骤）：可以查看当前转换使用了哪些【核心对象】中的组件。
• Hops（节点连接）：双击使用，可以将两个组件关联，但我们更多的是用 shift + 鼠标左键 这种快捷方式关联两个组件。

核心对象中的组件非常多，我们只讲解本书会用到的组件。

首先我们了解一下转换中常用的核心对象：

• 【输入】分类下的【CSV 文件输入】、【Excel 输入】、【表输入】分别表示把CSV、Excel、数据库表的数据导入到Kettle中。
• 【转换】分类下的【字段选择】可以对输入到Kettle中的数据进行筛选，也可以做数据类型的转换。
• 【输出】分类下的【表输出】则是将Kettle中的数据，输入到数据库表中。

作业中的常用的核心对象是【通用】分类下的【start】、【转换】、【作业】和【成功】。

【start】组件用于设置作业的开始位置，【转换】组件用于在作业中添加一个转换，【作业】组件用于在作业中添加一个作业，【成功】组件用于判断作业或转换进行的状态。

Part5Kettle 实战

了解了Kettle的基本功能后，我们通过实际案例，加深对Kettle的认识。我们现有一份【2020年浙江省各区县国民经济主要指标.xls】统计数据，我们要把它导入数据库中。操作如下：

首先，打开Kettle的【Spoon.bat】组件，进入可视化ETL工作界面。输入【ctrl + n】创建一个转换，在左侧边栏的【主对象树】中，我们可以看到已经存在mysql连接，这是我们之前的转换共享出来的。

然后，我们点击【核心对象】，将【输入】分类下的【Excel输入】组件，和【输出】分类下的【表输出】组件拖动到右侧的编辑界面。

我们按住shift，然后用鼠标左键，点击【Excel输入】，再点击【表输出】，将两个组件关联起来。

双击，【Excel 输入】组件，在【文件】一栏中，点击【浏览】，选择我们要导入数据库的excel表格，然后点击【增加】，在下方【选中的文件】中会出现该excel的路径。

在【工作表】一栏中，点击【获取工作表名称】，选择需要导入的Sheet。

在【内容】一栏中，选择【头部】表示保留列名，【非空记录】表示去除整行都是空值的数据，excel的编码一般为【GBK】。

在【字段】一栏中，点击【获取来自头部数据的字段】，kettle会获取excel中的列名，我们可以根据数据的实际情况给数据设置类型、去除空格等操作。

然后，我们点击【预览记录】，查看数据是否处理正确。

预览没有问题，则点击【确定】，完成【Excel 输入】组件的配置。

然后，我们配置【表输出】组件，双击它，进入编辑界面。数据库连接选择mysql，目标表输入“2020年浙江省各区县国民经济主要指标”。

我们点击【SQL】按钮，窗口中是mysql建表语句，这个语句是Kettle自动生成的，我们可以修改建表语句，让表结构更合理，点击【确定】后，完成该组件的配置。

我们进入DBeaver连接到MySQL数据库，可以看到表格已经生成。

然后，我们点击左上角的【运行】按键，在【执行转换】的弹窗中，设定日志级别，一般选择【基本日志】，然后点击【启动】。

Kettle的执行速度很快，出现下图所示日志，则说明运行成功。

我们进入DBeaver，查看导入数据后的表，出现数据表明导入成功。

Part6总结

本文首先介绍了ETL相关概念，然后介绍了KETTLE这款开源工具及其主要功能，最后演示了将Excel文件数据导入到mysql数据库的方法。

本文适合未掌握编程语言的读者，通过KETTL的界面操作，来完成数据导入。而对于会 java 、python 等编程语言的读者，可以通过编程来实现ETL。

星标⭐我们不迷路！

想要文章及时到，文末“在看”少不了！

点击搜索你感兴趣的内容吧

往期推荐

数据Seminar

这里是大数据、分析技术与学术研究的三叉路口

文 | 陈煌杰

    欢迎扫描👇二维码添加关注    

点击下方“阅读全文”了解更多