专栏首页Python数据科学构建AI前的数据准备,SQL要比Python强

构建AI前的数据准备,SQL要比Python强

机器之心编译 参与:高璇、路

Python 可以完成某项任务,并不意味着这个任务就应该使用 Python 来做。

作为一名 Web 开发人员,我第一次与数据库和 SQL 产生交集是使用对象关系映射(ORM)。我使用的是 Django 查询集 API,这个界面用户体验很好。之后,我转向数据工程方向,更多地利用数据集来构建 AI。我的职责是从用户应用程序中获取数据,并将其转换为数据科学家可利用的内容,这一过程通常称为 ETL (extract, transform and load)。

随着产业发展,生产系统中的数据非常混乱,需要进行大量转换才能用于构建 AI。有些 JSON 列每行模式都不相同,有些列包含混合数据类型,有些行有错误值。此外,还需要计算「用户成为访问者的时间」以及「他们在两次访问间的等待时间」等特征。当我着手清理、聚合和管理数据特征时,我想确定哪种语言最适合该任务。在之前的工作中我每天都使用 Python,我知道它可以完成工作。但是,这次经历使我了解到,Python 可以完成一项任务并不意味着这个任务就应该使用 Python 来做。

我对 SQL 的第一个误解是:SQL 无法进行复杂的转换

我们正在处理一个时间序列数据集,我们希望能够跟踪特定用户。隐私法规不允许获取用户访问的具体日期,因此我们决定将记录日期归一化为用户首次访问的日期(如首次访问后 5 天等)。对于我们的分析,重要的是要知道离上次访问过去了多久以及离首次访问过去了多久。A 有两个样本数据集,一个有大约 750 万行,大小为 6.5 GB,另一个有 55 万行,大小为 900MB。

我使用下面的 Python 和 SQL 代码先在较小的数据集上测试转换。Python 和 SQL 分别花费 591 秒和 40.9 秒完成了任务。这意味着 SQL 的速度是 Python 的大约 14.5 倍!

# PYTHON
# connect to db using wrapper around psycopg2
db = DatabaseConnection(db='db', user='username', password='password')# grab data from db and load into memory
df = db.run_query("SELECT * FROM cleaned_table;")
df = pd.DataFrame(df, columns=['user_id', 'series_id', 'timestamp'])# calculate time since first visit
df = df.assign(time_since_first=df.groupby('user_id', sort=False).timestamp.apply(lambda x: x - x.min()))# calculate time since last visit
df = df.assign(time_since_last=df.sort_values(['timestamp'], ascending=True).groupby('user_id', sort=False)['timestamp'].transform(pd.Series.diff))# save df to compressed csv
df.to_csv('transform_time_test.gz', compression='gzip')

-- SQL equivalent
-- increase the working memory (be careful with this)
set work_mem='600MB';-- create a dual index on the partition
CREATE INDEX IF NOT EXISTS user_time_index ON table(user_id, timestamp);-- calculate time since last visit and time since first visit in one pass 
SELECT *, AGE(timestamp, LAG(timestamp, 1, timestamp) OVER w) AS time_since_last, AGE(timestamp, FIRST_VALUE(timestamp) OVER w) AS time_since_first FROM table WINDOW w AS (PARTITION BY user_id ORDER BY timestamp);

SQL 转换不仅速度更快,而且代码也更易读,更易于维护。在这里,我使用 lag 和 first_value 函数来查找用户历史记录中的特定记录(即分区)。然后使用 age 函数来确定两次访问间的时间差。

更有趣的是,当这些转换脚本应用于 6.5 GB 的数据集时,Python 完全失败。在 3 次尝试中,Python 崩溃了 2 次,第三次我的计算机完全崩溃...... 而 SQL 只耗时 226 秒。

更多信息参见:

  • https://www.postgresql.org/docs/9.5/functions-window.html
  • http://www.postgresqltutorial.com/postgresql-window-function/

我对 SQL 的第一个误解是:SQL 无法扁平化不规则的 json

对我来说,另一个改变是我意识到 Postgres 可以很好地处理 json。我最初认为用 Postgres 扁平化或解析 json 是不可能的...... 我不敢相信自己竟然如此愚蠢。如果你想关联 json 并且它的模式在行间是一致的,那么最好的选择可能就是使用 Postgres 内置功能来解析 json。

-- SQL (the -> syntax is how you parse json)
SELECT user_json->'info'->>'name' as user_name FROM user_table;

另一方面,我的样本数据集中一半 json 不是有效的,因此存储为文本。在这种情况下,我要么重新编码数据使其有效,或者删除无效的行。为此,我创建了一个名为 is_json 的新 SQL 函数,然后使用该函数来验证 WHERE 子句中的 json 是否有效。

-- SQL
create or replace function is_json(text)
returns boolean language plpgsql immutable as $$
begin
    perform $1::json;
    return true;
exception
    when invalid_text_representation then 
        return false;
end $$;SELECT user_json->'info'->>'name' as user_name FROM user_table WHERE is_json(user_json);

不幸的是,我发现 user_json 具有不同的模式,具体取决于用户所使用的 app 版本。虽然从应用程序开发的角度来看这是有道理的,但是有条件地解析每行的每种可能性代价是很高昂的。难道我的最终归宿还是 Python?不不不!我在 Stack Overflow 上找到了一个由 Postgres 大神编写的 klin 函数(https://stackoverflow.com/users/1995738/klin)。

-- SQL
create or replace function create_jsonb_flat_view
    (table_name text, regular_columns text, json_column text)
    returns text language plpgsql as $$
declare
    cols text;
begin
    execute format ($ex$
        select string_agg(format('%2$s->>%%1$L "%%1$s"', key), ', ')
        from (
            select distinct key
            from %1$s, jsonb_each(%2$s)
            order by 1
            ) s;
        $ex$, table_name, json_column)
    into cols;
    execute format($ex$
        drop view if exists %1$s_view;
        create view %1$s_view as 
        select %2$s, %3$s from %1$s
        $ex$, table_name, regular_columns, cols);
    return cols;
end $$;

这个函数能够成功地扁平化 json,轻松解决我的噩梦。

结语

有一种说法叫「Python 是做任何事情的第二好语言」。我相信这是真的,并且在某些情况下 Python 和「最好」语言之间的性能差异可以忽略不计。但是在本文介绍的情况下,Python 无法与 SQL 比肩。这些发现完全改变了我做 ETL 的方法。我现在的工作模式是「不要将数据移动到代码中,而是将代码移动到数据中」。Python 将数据移动到代码中,而 SQL 执行后者。更重要的是,我知道我只是触及了 SQL 和 postgres 的皮毛。我期待能发掘出更多出色的功能,使用分析库实现加速。

原文链接:https://towardsdatascience.com/python-vs-sql-comparison-for-data-pipelines-8ca727b34032

本文分享自微信公众号 - Python数据科学(PyDataScience),作者:Marc Laforet

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 3 个适合新人上手的Python项目

    人生苦短,我用Python!废话不多说,今天给大家分享三个极实用的Python爬虫案例。

    用户2769421
  • 收藏 | 49个Python学习资源

    How to Run Your Python Scripts – Real Python

    用户2769421
  • 干货 | 14张高清Python数据科学家(数据分析&机器学习)代码速查表

    来源 | https://www.jianshu.com/u/be48b04ecc3e

    用户2769421
  • 构建AI前的数据准备,SQL要比Python强

    作为一名 Web 开发人员,我第一次与数据库和 SQL 产生交集是使用对象关系映射(ORM)。我使用的是 Django 查询集 API,这个界面用户体验很好。之...

    机器之心
  • 聊聊 Python 的内置电池

    最近,我突然想到一个问题:相比其它语言,有哪些概念或习惯叫法是 Python 特有的?

    Python猫
  • ENS域名如何由测试注册器升级到正式注册器[ 2020 年 5 月 4 日有效]

    摘要:如果你持有 .eth 域名,你需要在 2019 年 5 月 4 日到 2020 年 5 月 4 日期间访问 manager.ens.domains,把你的...

    辉哥
  • Python入门学习(一)

    看完了莫烦Python的视频,对于Python有了一点感觉,接下来打算把小甲鱼的视频啃完,附上学习网址:http://blog.fishc.com/catego...

    闪电gogogo
  • PaddlePaddle踩坑指北系列——MacOS安装

    本周我们精选出社区问答进行整理汇总,各位开发者在使用PaddlePaddle过程中遇到技术难题,可以到PaddlePaddle公众号【常见问答】专栏(点击主菜单...

    用户1386409
  • #4 编写第一个程序

    前面三节为Python程序的运行打下了坚实的基础,是骡子是马拉出来遛遛,所以从这节开始,将开始正式进入Python的编程环节,唯有不忘初心,才能方得始终!

    用户2398817
  • 看我如何在前期踩点过程中发现价值$4500的漏洞

    首先,我用来测试子域名漏洞的工具是Aquatone(洛克希德U2侦察机代号也叫这个),这个工具非常好用,可算是众多白帽的必备利器了。非常幸运的是,我不经意地用它...

    FB客服

扫码关注云+社区

领取腾讯云代金券