实训课 KNIME数据库操作

1、实验摘要

实验目的：通过KNIME连接到数据库并进行简单的读写操作

实验内容：连接数据库；读取数据库数据；处理数据；将处理结果写入数据库；实验步骤；

2、实验步骤

（1）KNIME支持从数据库导入数据进行数据分析等操作，它本身也提供很多对数据库直接进行操作的组件，例如对表数据的增删改查，建表删表。KNIME的扩展组件提供连接到主流数据库的驱动器（Driver），这些数据库包括mysql、sql server、postgresql。

开始试验前，我们假设已经有两个数据库，postgresql和mysql，postgresql在本机上，mysql在云服务器上。实验中，我们将一个csv文件先写入postgresql的test数据库中，然后从该数据库中读入数据，进行简单的groupby操作，最后将结果写入mysql的knime_test数据库中。整个工作流和csv文件内容显示如下：

（2）首先，我们要安装连接到数据库的驱动器扩展组件，如下图所示：

（3）通过“Database Connector”节点连接到数据库。在设置界面中，选择对应数据库的driver，修改url地址，填写用户名和密码进行验证，勾选“validate connection on close”测试连接：

（4）通过“CSV Reader”节点读入csv文件，然后再通过“Database Write”节点将文件写入数据库。“Database Write”节点的设置如下，我们将表命名为“customer”，如果表存在则添加数据到该表，如果存在信息缺失则插入null值：

（5）执行完毕后，postgresql的test数据库中便新生成了一张“customer”表，数据来自已有的csv文件。然后，我们通过“Database Reader”读取表数据，在设置界面，我们发现可以识别到新表“customer”：

（6）执行节点“Database Reader”读入表数据，结果显示如下

之后，我们用“Row Filter”和“Groupby”对这张表进行操作，统计男女存活人数。当然，你也可以进行其他感兴趣的操作。因为本次实验的重点是关于数据库的操作，所以具体的设置这里不再详细描述，有问题的同学可以查看节点说明。结果显示如下：

（7）通过节点“MySQL Connection”连接到mysql数据库。在这里，考虑到可能需要连接到多个数据库，就会有多个验证信息，每次都要输入用户名和密码比较麻烦，所以我们将验证信息直接存入工程中，具体操作如下：

这之后，我们在“MySQL Connection”节点设置中，验证部分直接选择已有的Credential就行了：

（8）连接成功后，我们通过“Database Write”节点，将结果写入到mysql的knime_test数据库中，具体设置如下：

（9）执行完毕后，我们可以直接去数据库中检验结果，也可以通过knime检验结果。这里我们使用“Database Table Selector”节点来查看写入数据库是否成功。在节点设置界面，点击“Fetch Metadata”，我们发现数据库已经生成了新表“result”了：

执行节点，打开结果界面，点击“Cache no. of row”得到表结果，说明我们写入数据库成功了：

注意：

1. mysql目前最新版为8.0.x，knime当前最新版（3.7.x）默认扩展组件提供的driver无法连接到最新版mysql。请使用旧版本的mysql！根据官网信息，之后的knime版本应该会更新框架，完善这个问题。

2. 另附上本实验中容器下启动postgresql和mysql数据库的指令：

$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=mysecretpassword -p 3306:3306 -d mysql:5

$ docker run --name postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres:10