数据分析之 ETL 之 ADO, ODBC, OLE DB

Sheng | May 8, 2022, 11:55 a.m. | Views: 388

ETL (Extraction, Transformation, Loading) 是数据分析的基础，Power Query 就是最典型的 ETL 工具了。而 ETL 底层用到的技术里，ADO, ODBC, OLE DB 是很重要的一块。了解这些概念后可以不用借助于 UI 而靠代码来操作了。

如下图所示，可以利用 ADO 连接也可以不用。而利用 ADO 连接数据（数据库或文件）可以有两种方式，要么通过 ODBC driver，要么通过 OLE DB providers 更直接地连接。

在用 ODBC driver 时候，有两种途径：使用 DSN，或者使用连接字符串（两者都是存储数据连接信息）。创建 DSN 需要打开 ODBC Data Sources：

如果在 New Data Source 里面没有找到（比如 PostgreSQL），就需要先下载并安装相关的 driver（比如 psqlodbc_x64.msi）。再打开就能看到想要的 Data Source 了。

创建好了后，在 Excel 里如果选择 Microsoft Query 得到这样的对话框：

Data Source 里的 Databases 实际上是 DSN，整个连接过程没有用到 OLE DB providers（在录制宏里没有看到），甚至没有用到 ADO。

如果选择的是 From ODBC 则是下面的对话框：

这种方式用到了 OLE DB provider，也没用到 ADO。可以看到可以选择 DSN。DSN 可以选择 (None)，来使用连接字符串（Connection string），我还没怎么研究过，不过发现这个网站应该挺有用的： https://www.connectionstrings.com/

至于通过 OLE DB providers 更直接地连接，Power Query（Power BI 同理）就是一个例子了，用了 Microsoft.Mashup.OleDb.1 这个 provider。

//

补充说明关于录制宏的小发现：

在 VBA 或者 Python 编程来连接数据的时候，一般都会创建一个 ADODB.Connection 的对象。而如果使用 Excel 录制宏的功能，会发现使用 Microsoft Query 系统录制的宏里并没有明确的创建 ADODB.Connection，而是在 ListObjects.Add 方法里的 Source 参数里添加了描述目标数据的字符串，里面有 ODBC DSN 的信息等，但没有用到 ADODB.Connection 对象，是直接用的 ODBC driver。

而反观如果使用 From ODBC 的时候录制宏，则发现用的是 OLE DB provider (Microsoft.Mashup.OleDb.1) 了，真是神奇啊。如果查看 Query Properties 发现 Connection type 是 OLE DB Query：

前面 Microsoft Query 则为：Database Query。