机器学习模型的构建以结构化数据为基础，而结构化数据当然不会凭空出现——只有经过大量数据转换，高质量素材才能为我们所用。另外，在进行预测分析时，负责训练机器学习模型的各数据工程团队也需要对数据进行同样的转换处理常常提出只能通过数据转换解决的独特数据需求。但问题在于，不同团队使用的工具集往往存在巨大差异，数据科学团队用户很难在训练与推理流程之上使用统一的转换工具集。为此，我们宣布在BigQuery ML中添加一系列新功能，帮助用户通过简单的SQL函数实现数据的预处理与转换。更重要的是，BigQuery会在预测过程中自动应用数据转换功能，从而极大简化机器学习模型的生产流程。

在2003年关于数据挖掘的探讨性文章中，Dasu与Johnson发现在整个数据分析流程当中，单数据清洁一项就占去八成时长。这个问题在如今的机器学习当中同样存在。在Google Cloud上，我们意识到各类机器学习项目正把绝大部分时间花费在为模型准备数据方面，具体包括以下任务：

编写ETL流水线，立足单一位置（数据湖）从多种不同源系统处获取数据并注入到单一位置（数据湖）；
数据清洗洁，对所提取数据集合进行纠错；
对数据湖中的原始数据进行格式转换，将不同来源的数据纳入统一数据集；
数据预处理，包括清除异常值、估算缺失值、缩放数据列、嵌入稀疏列等；
利用特征交叉等操作在原始数据中引入新的特征工程，降低机器学习模型的复杂度并提高收敛速度；
将经过合并、预处理以及工程化的数据转换为TensorFlow Records等可供机器学习模型高效处理的格式；
在推理系统中重复整个数据处理流程，流程中各步骤可能是使用其他不同编程语言进行编写的；
完成训练与预测流水线的生产化转换。

充分发挥数据仓库中的内置机器学习优势

对于各类机器学习项目来说，数据整理与数据移动无疑是其中的一大重要组成部分。不少组织机构习惯于针对各个项目建立定制化ETL流水线，借此将数据移动至数据湖，同时要求机器学习项目中的各项任务独立完成数据理解与形式转换等操作。在这里，我们建议大家放弃这种固有思维，尝试建立企业数据仓库（EDW）。如此一来，只要EDW基于云端并将计算与存储等视为彼此独立的资源类别（BigQuery就采取这种方式），那么组织内的任何业务部门乃至外部合作伙伴都可轻松访问数据，且无需执行任何数据移动操作。更重要的是，全部访问活动都将受到身份与访问管理（IAM）角色的严格控制。

利用这类EDW，数据工程团队只需编写一次ETL流水线，即可随时捕捉系统中的变更并将其更新至数据仓库当中，确保机器学习团队彻底告别以往令人头痛的零散编码需求。以此为基础，数据科学家将专注于从数据中获取洞察，而不再将精力浪费在数据格式转换等缺少实际意义的任务身上。此外，如果EDW能够提供机器学习功能并与其他强大的机器学习基础设施（例如AI平台）相集成，甚至有望彻底消除数据移动需求。在Google Cloud上，用户在BigQuery ML中训练深度神经网络模型时，即可无缝对接我们的AI Platform。

例如，如果我们希望利用纽约出租车乘坐数据集训练一套用于预测出行成本的机器学习模型，只需要使用下面这条SQL查询指令：

CREATE OR REPLACE MODEL serverlessml.model1_rawdata
OPTIONS(input_label_cols=['fare_amount'], model_type='linear_reg') AS
SELECT
  (tolls_amount + fare_amount) AS fare_amount,
  pickup_longitude AS pickuplon,
  pickup_latitude AS pickuplat,
  dropoff_longitude AS dropofflon,
  dropoff_latitude AS dropofflat,
  passenger_count*1.0 AS passengers
FROM `nyc-tlc.yellow.trips`

通过计划查询推动生产化进程

在模型训练完成之后，我们可以输入接客地点与下车地点，从而计算出特定行程的具体费用：

SELECT * FROM 
ML.PREDICT(MODEL serverlessml.model1_rawdata, (SELECT 
  -75.1 AS pickuplon,
  39.1 AS pickuplat,
  -76.8 AS dropofflon,
  40.3 AS dropofflat,
  3 AS passengers
))

返回结果如下：

如果您使用基于云端的BigQuery等现代EDW，那么其中内置的机器学习功能将帮助各位轻松消除与数据移动相关的诸多恼人操作。需要注意的是，以上查询仅通过SELECT语句即可实现机器学习模型训练，这将一举解决我们在本文开头提出的三个重要痛点。整个机器学习模型的训练与批量预测执行流程非常简单，只需要对以上两项SQL查询进行规划调度即可，从而大大降低生产化进程的难度。

我们此次公布的BigQuery ML预处理与转换功能将帮助大家解决各类常见阻碍，使用户在顺利摆脱偏差问题的同时，高效完成数据处理、机器学习模型训练以及预测执行等工作。

BigQuery ML中的预处理机制

数据仓库在存储原始数据时，会考虑到各类数据分析任务的实际需求。例如，用户可能会通过仪表板对数据仓库内的数据进行状态跟踪，数据分析人员则经常执行各类临时查询。但是，机器学习模型在训练中往往不会使用原始数据，而是经过异常值过滤等处理的高质量素材，同时配合分桶（bucketizing）及缩放等操作改善模型的可训练性与收敛性。

我们可以利用WHERE子句在SQL中执行过滤，如下所示：

SELECT
  (tolls_amount + fare_amount) AS fare_amount,
  pickup_longitude AS pickuplon,
  pickup_latitude AS pickuplat,
  dropoff_longitude AS dropofflon,
  dropoff_latitude AS dropofflat,
  passenger_count*1.0 AS passengers
FROM `nyc-tlc.yellow.trips`
WHERE 
  trip_distance > 0
  AND fare_amount >= 2.5
  AND pickup_longitude > -78
  AND pickup_longitude < -70
  AND dropoff_longitude > -78
  AND dropoff_longitude < -70
  AND pickup_latitude > 37
  AND pickup_latitude < 45
  AND dropoff_latitude > 37
  AND dropoff_latitude < 45
  AND passenger_count > 0

在确定了必要的数据清洁与纠错操作之后，我们可以创建一个预聚合视图：

CREATE OR REPLACE PRE-AGGREGATED VIEW serverlessml.cleaned_data AS
SELECT ...

由于目前BigQuery只提供预聚合视图功能的alpha版，因此您可以选择使用逻辑视图，或者将数据导出为新表。在机器学习场景下，预聚合视图的优势在于显著降低在BigQuery中保持数据内容更新的难度。

同样的，我们亦可在SQL中实现缩放功能。例如，利用以下代码对四个输入字段执行zero-norm（0范数）：

with raw_data AS (
  SELECT *
  FROM `cloud-training-demos`.serverlessml.cleaned_training_data
),

zero_norm AS (
  SELECT 
    AVG(pickuplon) AS mean_pickuplon
    , AVG(pickuplat) AS mean_pickuplat
    , AVG(dropofflon) AS mean_dropofflon
    , AVG(dropofflat) AS mean_dropofflat
    , STDDEV(pickuplon) AS std_pickuplon
    , STDDEV(pickuplat) AS std_pickuplat
    , STDDEV(dropofflon) AS std_dropofflon
    , STDDEV(dropofflat) AS std_dropofflat
  FROM raw_data
)

SELECT 
  fare_amount
  , bqutil.fn.zeronorm(pickuplon, mean_pickuplon, std_pickuplon) AS pickuplon
  , bqutil.fn.zeronorm(pickuplat, mean_pickuplat, std_pickuplat) AS pickuplat
  , bqutil.fn.zeronorm(dropofflon, mean_dropofflon, std_dropofflon) AS dropofflon
  , bqutil.fn.zeronorm(dropofflat, mean_dropofflat, std_dropofflat) AS dropofflat  
from raw_data, zero_norm

大家当然可以把缩放后的数据存储在预聚合视图当中；但由于均值/方差会随时间变化，因此我们不建议采取这样的处理方法。缩放操作属于机器学习中的一种预处理操作类型，其中涉及analysis pass（用于确定均值与方差）。由于分析过程的结果会随着新数据的加入而改变，所以最好只在机器学习训练当中执行涉及analysis pass的预处理操作。另外，我们也借用了社区 GitHub库中的UDF（用户自定义函数）以简化操作过程。

BigQuery以开箱即用的方式为多种常规机器学习操作提供支持，意味着用户在执行此类操作时，不再需要对数据进行单独的analysis pass。例如，以下代码将对输入进行分桶，从而计算纽约市的纬度与经度边界：

 SELECT 
  fare_amount
  , ML.BUCKETIZE(pickuplon, GENERATE_ARRAY(-78, -70, 0.01)) AS pickuplon
  , ML.BUCKETIZE(pickuplat, GENERATE_ARRAY(37, 45, 0.01)) AS pickuplat
  , ML.BUCKETIZE(dropofflon, GENERATE_ARRAY(-78, -70, 0.01)) AS dropofflon
  , ML.BUCKETIZE(dropofflat, GENERATE_ARRAY(37, 45, 0.01)) AS dropofflat
from `cloud-training-demos`.serverlessml.cleaned_training_data

请注意，这些字段经过分类，且对应于各pickup与dropoff所归属的bin：

利用TRANSFORM限制由训练产生的偏差

通过以上模型训练示例可以看到，模型的生产化过程往往相当困难，实际问题也绝不会像直接发送纬度与经度那么简单。另外，我们还需要把预处理步骤复制到预测流水线当中：

 SELECT * FROM 
ML.PREDICT(MODEL serverlessml.model1_rawdata, (SELECT 
  ML.BUCKETIZE(-75.1, GENERATE_ARRAY(-78, -70, 0.01)) AS pickuplon,
  ML.BUCKETIZE(39.1, GENERATE_ARRAY(37, 45, 0.01)) AS pickuplat,
  ML.BUCKETIZE(-76.8, GENERATE_ARRAY(-78, -70, 0.01)) AS dropofflon,
  ML.BUCKETIZE(40.3, GENERATE_ARRAY(37, 45, 0.01)) AS dropofflat,
  3 AS passengers
))

考虑到上述实际情况，我们决定支持TRANSFORM关键字。您可以将所有预处理操作全部放进特定的TRANSFORM子句当中，并由BigQuery ML在预测期间自动执行这些操作，从而有效控制训练期间可能出现的偏差问题。

以下示例涉及更为复杂的预处理操作，包括计算GIS数量、从时间戳中提取特征、执行特征交叉，并将各项特征与pickup与dropoff bin对应起来：

 CREATE OR REPLACE MODEL serverlessml.model7_geo
TRANSFORM(
  fare_amount
  , ST_Distance(ST_GeogPoint(pickuplon, pickuplat), ST_GeogPoint(dropofflon, dropofflat)) AS euclidean
  , ML.FEATURE_CROSS(STRUCT(CAST(EXTRACT(DAYOFWEEK FROM pickup_datetime) AS STRING) AS dayofweek,
                            CAST(EXTRACT(HOUR FROM pickup_datetime) AS STRING) AS hourofday), 2) AS day_hr
  , CONCAT(
     ML.BUCKETIZE(pickuplon, GENERATE_ARRAY(-78, -70, 0.01)),
     ML.BUCKETIZE(pickuplat, GENERATE_ARRAY(37, 45, 0.01)),
     ML.BUCKETIZE(dropofflon, GENERATE_ARRAY(-78, -70, 0.01)),
     ML.BUCKETIZE(dropofflat, GENERATE_ARRAY(37, 45, 0.01))
  ) AS pickup_and_dropoff
)
OPTIONS(input_label_cols=['fare_amount'], model_type='linear_reg', l2_reg=0.1, optimize_strategy='BATCH_GRADIENT_DESCENT') 
AS

SELECT * FROM `cloud-training-demos`.serverlessml.feateng_training_data

所有预处理都在构建模型时统一定义尽管操作非常复杂，但这使得预测代码本身仍然简单明了，用户也无需再重复任何这些预处理步骤：

这就是此次发布的新功能，希望大家喜欢！

文章版权归作者所有，未经允许不得转载。

创作场景

BigQuery 数据转换简化大法：让机器学习更轻松高效