informix怎么确定一个字段是不是主键_Python数据核对系列之1——一个偷懒的开始...

近期文章发布的少了，公众号都开始掉粉了。吓得我赶紧把最近做的事，感觉有意义的跟大家分享一下。也就是我们要开始的一个小系列——Python个数据核对。 问题描述：对比两个表主键字段数据的一致性，查看是否源数据的主键都存在于数据仓表里面，把没有的标记出来。(也就是excel里的VLOOKUP，SQL里的left/right join)

诞生背景

这事的诞生，源于自己工作过程中遇到的一个大坑！！在使用kettle进行ETL搭建数仓过程中，出现了一个不可思议的问题——kettle抽取数据竟然会有遗漏！！直接导致业务部门天天追我们屁股后面问，数据怎么不对，何时能对！我也在多个群里(数仓、大数据、python啥的各种群)问过这问题，唉，渺小的身影没有引起大家注意，没有谁给过一个答案或者类似的经历，在某度上也搜索过这个问题，但并没有发现有同样问题的贴子，去了某歌看了看也没有。在查找问题根源失败后，就只能想办法先治标了。(如果亲们谁有这个问题的解决方案，不胜感激！！)所以就自己想办法从源数据库里抽取表数据的主键字段，然后再抽取数据仓表的主键字段，然后进行对比查看哪些主键遗漏了，再给kettle工程师，让他把数据补到数仓里！

版本演化

excel版-Power Query

最开始想到的办法就是利用sql从源数据库里将主键字段数据查询出来，然后导出为excel或者csv文件，然后利用excel的query加载数据，再利用query的合并查询来核对两个表中核对不上的数据。

这个方案最熟练了，但是无奈表数据量太大(单表数据在20万行-100万行-1000万行-4000万行)基本各层级数据量都覆盖了，query每次处理数据加载时间太长了，而且严重消耗CPU的。一个100万行的合并查询就得半小时左右。耗不住这时间，最后选择放弃。

Python版1.0

excel的query搞不定的数据量，那么就交给专业处理数据的工具——Python吧。

想到要用Python完成数据核对，遇到的第一个问题就是：如何读取excel/csv文件。

问题一：python如何读取excel/csv文件

于是开始百度，查了各种文章，听说了几个excel/csv的操作库

openpyxl(https://www.pythonexcel.com/python-excel-writer.php)

XLWT((https://xlwt.readthedocs.io/en/latest/#)

XLRD(https://xlrd.readthedocs.io/en/latest/#)

XLUTILS(https://xlutils.readthedocs.io/en/latest/)

这几个库确实是处理excel文件的利器，但似乎距离我的需求有点偏，我只需要读取excel/csv文件，然后进行核对。

最后查来查去，还是回到了python数据分析扛把子——pangdas身边。看看pandas的官方文档，真是相见恨晚啊！看这截图里的文件格式，基本没啥pandas不能读取、输出的格式了！

于是用pandas.read_excel()或者pandas.read_csv方法解决！

pandas的官方文档

https://pandas.pydata.org/pandas-docs/stable/user_guide/index.html

接下来就是要解决第二个问题了，如何对比两个表的数据呢？

问题二： python 如何进行数据对比解决方案还得从pandas入手。在文档中看到了  Merge, j oin, and concatenate 仔细阅读文档之后，最终选择了Merge 这个方法。(具体用法后面的文章详细介绍)利用merge可以对比两个表的数据，而且还可以输出为带_Merge字段(内容为left_only，both，right_only)。可以清晰的看到哪些字段是哪个表独有或共有的。经过筛选过后就可以获取到数仓这边遗漏的数据的主键明细了。那么最后一步就是如何导出这些数据了 问题三： python 如何导出数据为excel/csv

这里用到的还是问题一里我们查到的内容，利用pandas的io功能来搞定！

pandas的文件输出有pandas.to_excel()和pandas.to_csv()

可以导出对应格式文件

这个方法是可以解决问题，但是全程手动操作的内容太多了，手动sql导出表，然后python加载核对导出表，再把导出的表导入补数的表里。每核对一个表我就要重新操作一遍。几十个表，累死！

Python版2.0

这个版本首先解决的一个问题就是如何直接读取数据库数据加载到pandas里面！

问题一： python 如何读取数据库数据要想读取数据库数据，首先得有数据库连接库。mysql的话对应pymysqlhttps://pymysql.readthedocs.io/en/latest/oracle对应cx_Oraclehttps://cx-oracle.readthedocs.io/en/latest/index.htmlsqlsever对应pymssqlhttps://pymssql.readthedocs.io/en/latest/index.html在这个问题上前后经历了两个版本方案。版本一：利用游标cursor进行数据读取版本二：利用pandas的read_sql方法读取 感觉这俩速度没啥差别，最后选择的是pandas.read_sql()方法。这个方案基本最大的问题就是解决数据库读取的问题。但具体实施过程里还有不少小问题要解决。比如1、如何获取要读取的表信息2、如何遍历获取每一个表名称3、如何用变量搞定每个表的sql查询4、如何解决数据库连接断掉(虽然这是比较靠后才解决的问题，但跟2.0关系密切就放这里吧)5、用cursor方案的话还需要解决如何让dataframe获取读取SQL的数据字段名称6、如何处理读取后的数据大小写转换7、如何处理读取后的数据格式转换这个问题解决了频繁手动sql导出表在python读取的处理耗时(主要是想偷懒！)但如何更进一步，我把遗漏的主键数据直接插入补数的数据库表里，而不是人工导入呢？ Python版3.0

这里主要解决如何把pandas的dataframe数据导入数据库。

问题一： python 如何导出数据到数据库表中在这个问题上，也主要是有两个选择。一是，pandas的to_sql方法；二是，cursor方法执行insert语句最后无奈选择了cursor方法，因为to_sql怎么尝试怎么失败，最后虽然插入成功了，但是无法选择插入的字段数据，表内其他字段数据无法获取默认值。如果我设置默认值，又遇到了数据格式无法匹配的问题，最后实在没精力再研究了，只能放弃改为cursor方法执行insert语句。cursor方法虽然成功了，但是速度并不快。每秒插入数据不过百十条而已。后期再继续优化这个方案基本成型了。可以一套代码跑下来，就把所有表都可以完成代码自动核对了。但是最大的问题在于耗时太长。一方面由于数仓数据读取速度太慢，大约每秒5000-2000条。而我们源数据库读取速度可以高达每秒25000条。速度快10倍有余。整个代码跑完得花6个小时，而且一下子读取两个4000万量级的表，我电脑内存扛不住。我电脑16GB内存，为处理数据特意扩的内存，配置不算低了，依然鸭梨山大。 Python版4.0

这个版本的优化真的是血泪史啊，踩坑无数，耗时4天，看代码看的每天脑袋迷迷糊糊。

这个版本主要尝试的是利用多线程和多进程进行数据核对。

首先要解决的多线程、多进程这俩概念的理解。看了不少文章的介绍和自己的理解消化，3天左右时间才搞明白(不禁感叹，人老了，学习能力下降了)

问题一： python 如何使用多线程、多进程这其实是俩问题，但它俩实在相似，就放一起吧。其实一开始我尝试的是多线程，但查看资料后了解到python的多线程是伪多线程，并不是多个线程同时执行，而是在多个线程之间快速切换运行，因此特别适合IO密集型的，利用CPU切换线程的空档去进行IO处理，能一定程度减少整体时间消耗。这个伪多线程可以理解为列车上的乘务员，她不可能同时出现在所有车厢上，要想看似同时出现在所有车厢里，那么她要在不同车厢间进行快速移动，达到看似同时出现的效果。而多进程是实实在在的同时执行的，可以充分利用多核CPU的性能，因此适合计算密集型的。比如计算个天体运行轨道、计算圆周率、计算核爆炸(打个比方而已)。这个多进程用乘务员的比喻来说的话，那就是所有车厢里都有乘务员，她们可以同时为所有车厢服务，而不需要一个个车厢快速移动更换。在这个问题里还需要解决如何把一个SQL读取一个表转化为多个sql读取一个表。怎么把这个表数据分配给不同线程或者进程。 问题二： python 如何读取多线程、多进程执行后的数据

不管是同时执行也好，快速切换也好，它都是开启了多个途径来执行一个任务，因为不同途径完成后的结果如何再组合到一起就是个大问题。

在这上面我也是挠头不已，掉了不少头发(手动狗头)

一番折腾之后，我最终搞定了多进程+多线程读取数据库。能解决整个问题，还是靠看大神的讲解视频(小破站里莫烦大神的视频)。古人诚不欺我：吾尝终日而思矣，不如须臾之所学也！吾尝跂而望矣，不如登高之所见也！

虽然最后测试结果显示，不管是多进程还是多线程对于数据库读取来说并没有特别明显提升。所以又引出5.0的改良。

Python版5.0

在这个版本里解决的问题就是如何缩短整体数据核对的时间消耗了。

既然我们的数仓数据抽取可以是增量的，那么我这个数据核对也可以是增量的吗！历史数据都已经核对过了，如果有遗漏的话也肯定是新抽取过来的数据产生的遗漏。所以转换下思路，只抽取增量更新的部分数据进行核对。减少数据量，一来可以降低数据读取耗时，二来可以降低内存压力。为了读取那两个4000万的表，我电脑只能全部腾出来做这事，就这内存使用都高达97%，真是内存爆掉。

在这个方案里，跟数仓抽取的增量逻辑一样，通过时间戳来划定增量抽取的范围，然后获取数据进行对比。

对于源数据库的数据可以只抽取增量，而数据仓库这边的数据是不能只抽取增量的，是无法达到核对效果的。所以想了个办法，抽取最大时间戳的1/2的时间戳这个范围内的数据。小于这个时间戳的数据想来也不会发生变化了。这样就可以极大缩小我们数据抽取的体量。提升数据核对速度。

这样改进之后，从6小时可以压缩到2个半小时。

Python版6.0

这个版本还未实际执行，目前是个思路。

之前的思路一直是一个python文件搞定所有表数据核对，因此数据核对有个先后顺序，时间会消耗在先后的等待上。

既然单表数据抽取都可以多线程、多进程了，那么这些表的执行为何不可？

后续的方案就是把所有表单拆出来，通过一个python同时执行所有表的核对。这样整体时间消耗就=最大表的核对时间了。也就基本是在40分钟内搞定了。

今天只是跟大家把我这这一周时间里，整个方案从无到有，各个版本的过渡经过梳理一遍，让大家对我整个事情有个整体印象，后面我将详细展开介绍下每一步、遇到的每一个坑的具体实现、解决方案，介绍下用到的各个python库，各个方法。

这一周下来，对于python的使用熟练了不少。还是那句话问题是最好的老师！希望我这个小系列的介绍，能对大家有所帮助！

人生苦短，我用Python！一切皆是信息，万物源自比特！

数字化必定会深刻革命我们的办公和生活！

简道云，中小企业数字化之路的绝佳伴侣！

本公众号将分享数字化的实践、学习、思考。也许涉及信息化系统设计、各种办公软件、数据分析、理论知识、实践案例…… 感谢你与我一同成长……

如果有关企业数字化的疑问、思考和讨论 或者 关于简道云的应用搭建、数据工厂、仪表盘等疑问咨询或者合作，欢迎与我联系。(关注公众号，可以找到我的联系方式)