本文展示了如何使用InfoSphere Warehouse Data Mining将DB2的XQuery功能与存储在XML中的数据挖掘结果相结合。
InfoSphere Warehouse Data Mining提供了用于所有常见数据挖掘任务的算法，例如以下列表中的任务以及该任务的示例应用程序：
- 聚类，包括发现重要的客户群或欺诈行为
- 关联规则，包括交叉销售或商店布局改进
- 顺序模式，包括选择性营销或零件故障预测
- 分类，包括客户流失分析或医学诊断
- 回归，包括保险索赔预测或风险管理
所有这些数据挖掘任务的结果都称为数据挖掘模型 ，并使用PMML标准将它们存储在表中。 该标准基于XML，以允许在不同的数据挖掘提供程序之间轻松进行交换。 然后可以将这种模型应用于新数据，这称为计分 。
例如，您可能需要根据新客户的违约风险进行分类。 PMML模型需要存储有关挖掘结果的所有重要信息。 Intelligent Miner提供了一组SQL提取器功能，以从模型中获取最重要的信息。
如果您需要提取更多或不同的信息，本文介绍了如何完成此操作。 本文还向您展示了DB2 XQuery的功能以及如何在DB2用户定义函数（UDF）或DB2存储过程（STP）中使用它。
了解XQuery
在DB2 9中，pureXML引入了XML数据类型，并使得非常有效地查询和使用DB2数据库中的XML文档成为可能。
有两种查询语言可用于从XML文档中提取信息。 XPath语言用于浏览XML树并根据给定的限制选择节点。 XQuery是XPath的超集，它添加了For，Let，Order by，Where和Return表达式（也称为FLWOR表达式[发音为花 ]），以对XML文档启用更复杂SQL风格的查询。 您还可以使用XQuery以与XSLT类似的方式来转换XML文档。
样本数据
要使用pureXML，数据库需要使用UTF-8进行编码。 自从DB2 9.5发布以来，这是数据库的缺省值。 可以像其他所有列一样创建XML列。 清单1中的代码创建了一个带有ID列和XML列的小型测试表。
清单1.创建一个小的测试表
db2 "CREATE TABLE XMLTEST (ID int, XMLCOLUMN XML)"要将XML插入表中，请使用INSERT语句，如清单2所示。命令中的XML字符串被解析为XML列。 确保对引号使用正确的转义。
清单2.插入XML
db2 "INSERT INTO XMLTEST VALUES (1, '<Person ... />')" 您将使用本文稍后清单3中所示的示例XML数据。 每个Person XML片段都保存在XML列的单独行中。
清单3.插入XML
<Person>
<Name>John Smith</Name>
<Tel publish="false">555-8659</Tel>
</Person>
...
<Person>
<Name>Mark Muster</Name>
<Tel publish="true">555-7765</Tel>
</Person>使用XQuery检索数据
XQuery表达式可以在SQL表达式中使用。 要在SQL之外使用XQuery表达式，必须在它们之前加上XQUERY 。 如果在SQL查询之外使用XQuery，则必须使用命令db2-fn:xmlcolumn指定输入列，并为包含XML源的列指定一个参数，如清单4所示。
清单4.在SQL外部使用XQuery表达式
db2 "XQUERY db2-fn:xmlcolumn('XMLTEST.XML')"使用XML文档时，最重要的概念是XML路径，它提供了一种解决XML树中的节点和元素的方法。 清单5中的XPath表达式通过提供从XML文档根节点开始的路径来返回文档中人员的NAME节点。
清单5.返回名称节点
XQUERY db2-fn:xmlcolumn('XMLTEST.XML')/Person/Name
...
<Name>John Smith</Name>
<Name>Mark Muster</Name>
2 record(s) selected.XQuery提供了复杂SQL风格的表达式来查询多个XML文档。 清单6中的示例使用FOR和WHERE表达式查询电话号码未发布的人。 XML节点的属性可以使用@限定符进行访问。 请注意，用于检查publish属性的表达式位于方括号中。
清单6.查询人们的电话号码
XQUERY for $x IN db2-fn:xmlcolumn('XMLTEST.XML')/Person/Tel
where $x[@publish='false'] return $x
...
<Tel publish="false">555-8659</Tel>
1 record(s) selected.尽管可以使用XQuery从DB2表中存储的XML文档中提取XML节点和值，但是您需要一种将信息提取为结构化表的信息，该信息可以由无法读取XML的工具读取。 DB2中用于此目的的方法是XMLTABLE表达式。 该表达式从XQuery表达式创建一个表。 XMLTABLE用于SELECT查询的FROM语句中。 第一个参数指定返回XML片段的XQuery表达式。 COLUMNS参数指定表输出列，包括每个XML片段的列名，类型和相对的XQuery表达式。
清单7.使用XMLTABLE表达式
select * from
XMLTABLE ('db2-fn:xmlcolumn("XMLTEST.XML")/Person'
COLUMNS
name VARCHAR(20) PATH 'Name',
tel VARCHAR(20) PATH 'Tel',
published VARCHAR(5) PATH 'Tel/@publish'
) AS X;
...
NAME TEL PUBLISHED
-------------------- -------------------- ---------
John Smith 555-8659 false
Mark Muster 555-7765 true
2 record(s) selected.使用这种类型的DB2 select语句，您可以提取XML文档中的所有信息，并将其作为SQL表数据传递。 有关更多信息，请参阅参考资料 。
了解PMML
预测模型标记语言（PMML）是一种XML标记语言，用于描述数据挖掘模型并支持在不同数据挖掘提供者之间交换挖掘模型。 交换模型使其他程序可以对这些数据挖掘模型进行评分，而无需自己创建模型。 请参阅相关主题有关数据挖掘集团网站发布的PMML规范。
PMML模型通常遵循以下结构：
- PMML根注释，其中显示了所用PMML的版本
- 标头，提供有关模型的常规信息，包括名称和日期
- MiningBuildTask，它提供有关用于构建模型的配置的信息
- DataDictionary，提供有关用于创建模型的字段的信息，包括名称，类型和值
型号信息取决于所使用的型号。 清单8显示了集群模型的PMML结构。
清单8.集群模型的PMML结构
<?xml version="1.0" encoding="UTF-8" ?>
<PMML version="3.0" xmlns="http://www.dmg.org/PMML-3_0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.dmg.org/PMML-3_0 http://www.dmg.org/v3-0/pmml-3-0.xsd">
<Header copyright="Copyright IBM Corp. 2002, 2007 All Rights Reserved">
<Application name="IBM DB2 Intelligent Miner" version="9.5"/>
<Timestamp>2008-11-23 23:03:09</Timestamp>
</Header>
<MiningBuildTask>
...
<DataDictionary numberOfFields="8">
...
<ClusteringModel modelName="BANK.CUSTOMERS_SEGMENTS" modelClass="centerBased"
functionName="clustering" algorithmName="Kohonen" numberOfClusters="5"
x-execTime="0.070000171661377" x-similarityThreshold="0.5"
x-quality="0.780511795527719">
...下一个示例使用基于Kohonen算法的聚类模型。 PMML支持基于中心和基于分布的集群模型。 两者都具有ClusteringModel元素作为顶级元素。 包括群集列表和用于计算新记录到不同群集的距离或相似性的度量。 清单9显示了基于中心的Kohonen集群模型。
清单9.使用基于中心的Kohonen集群模型
<ClusteringModel modelName="BANK.CUSTOMERS_SEGMENTS" modelClass="centerBased"
functionName="clustering" algorithmName="Kohonen" numberOfClusters="5"
x-execTime="0.070000171661377" x-similarityThreshold="0.5"
x-quality="0.780511795527719">
+ <Extension name="Matrix">
+ <Extension name="X-Correlations">
+ <MiningSchema>
+ <ModelStats>
<ComparisonMeasure kind="distance">
<squaredEuclidean/>
</ComparisonMeasure>
<ClusteringField field="AGE" compareFunction="absDiff"
x-quality="0.291742616514616"/>
<ClusteringField field="AVERAGE_BALANCE" compareFunction="absDiff"
x-quality="0.223507903713586"/>
<ClusteringField field="GENDER" compareFunction="absDiff"
x-quality="0.67909000081883"/>
<ClusteringField field="ID" compareFunction="absDiff"
x-quality="0.32252228279477"/>
<ClusteringField field="MARITAL_STATUS" compareFunction="absDiff"
x-quality="0.593752740606671"/>
<ClusteringField field="NBR_YEARS" compareFunction="absDiff"
x-quality="0.34454192375915"/>
<ClusteringField field="NOT_USED" compareFunction="absDiff"
x-quality="0.198193321261319"/>
<ClusteringField field="SAVING_ACCOUNT" compareFunction="absDiff"
x-quality="0.663013938830898"/>
+ <CenterFields>
<Cluster name="1" x-quality="0.786176824083955">
... 以x-开头的所有属性都是特定于供应商的，可以忽略。 请参阅相关的主题为PMML对数据挖掘组网站的详细细节和例子。
了解智能矿工
Intelligent Miner使用UDF和STP集成在DB2中。 您可以使用一组SQL语句或InfoSphere Warehouse DesignStudio图形界面来创建数据挖掘模型。 图形界面是更方便的方法。 PMML模型存储在DB2表中，可用于对新数据评分或提取信息。
例如，设想一个场景，其中您有来自银行客户的数据，您希望使用这些数据通过聚类来确定不同的客户群。 确保将所有客户的相关信息合并到一个表或视图中，如清单10所示。
清单10.查看银行客户数据
db2 select * from bank.customers fetch first 3 rows only
ID AGE GENDER MARITAL_STATUS NBR_YEARS SAVING_ACCOUNT AVERAGE_BALANCE NOT_USED
------ --- ------- -------------- --------- ------------- -------------- --------
120921 34 m s 3 n -23 n
223423 55 m m 12 y 123 n
937212 25 m s 2 y 388 n
3 record(s) selected.要在样本数据上创建聚类模型，请使用IDMMX.BuildClusModel存储过程。 IDMMX.BuildClusModel存储过程的参数为：
- 型号名称
- 表或视图的名称
- 特定的数据挖掘参数
清单11显示了创建模型的命令。
清单11.创建模型
db2 "call IDMMX.BuildClusModel('BANK.CUSTOMERS_SEGMENTS', 'BANK.CUSTOMERS',
'DM_setAlgorithm(''Kohonen'')')"创建模型后，可以使用Intelligent Miner Visualizer以图形方式查看模型，也可以使用现有的Intelligent Miner提取函数通过SQL获取一些信息。 例如，您可以提取集群模型的概述，以显示集群的数量，使用的字段以及模型的质量。 清单12显示了要使用SQL。
清单12.使用SQL提取集群模型的概述
SELECT
"ID" AS "CLUSTERID",
CAST ("NAME" AS CHAR(20)) AS "CLUSTERNAME",
"SIZE",
CAST ("HOMOGENEITY" AS DEC(5,2)) AS "HOMOGENEITY"
FROM TABLE
(IDMMX.DM_getClusters((
SELECT "MODEL"
FROM IDMMX."CLUSTERMODELS"
WHERE "MODELNAME"='BANK.CUSTOMERS_SEGMENTS')))
AS "CLUSTERS"
ORDER BY SIZE DESC;清单13显示了清单12中SQL结果。
清单13.集群模型的结果概述
MODELNAME NUMBER_CLUSTERS NUMBER_FIELDS MODEL_QUALITY
-------------------- --------------- ------------- -------------
BANK.CUSTOMERS_SEGME 7 7 0.81
1 record(s) selected.一起使用XQuery和Intelligent Miner
可以使用可用的数据挖掘功能来提取大多数信息，但是不能提取某些信息。 您可以将XQuery函数与Intelligent Miner函数一起使用，以直接从PMML模型中读取那些隐藏的部分。
Intelligent Miner当前不允许提取群集补充字段的详细信息。 一个字段可以是活动字段，也可以是补充字段，这取决于该字段对于计算聚类的价值。 例如，作为DB2中主键的字段始终具有不同的值，因为它们不能提供对集群的见解，所以它们会自动移至补充字段。
您可以在XML节点MiningField的PMML模型中找到隐藏的信息。 如果属性usageType具有值补充，则表示补充字段。 活动字段是默认用法类型，因此在PMML文件中未指出。
清单14显示了PMML MiningSchema示例。
清单14.对PMML MiningSchema进行编码
<MiningSchema>
<MiningField name="AGE"/>
<MiningField name="AVERAGE_BALANCE"/>
...
<MiningField name="SAVING_ACCOUNT"/>
<MiningField name="ID" usageType="supplementary"/>
</MiningSchema>清单15显示了如何在DB2中实现UDF以提取所有集群字段并显示这些字段的用法类型。
清单15.在DB2中实现UDF
CREATE FUNCTION getClusterMiningFields (mname varchar(128))
RETURNS TABLE (name varchar(20),
usagetype varchar(20))
LANGUAGE SQL
BEGIN ATOMIC RETURN
select X.name,
CASE WHEN X.usageType IS NULL THEN 'active'
ELSE X.usageType END
from XMLTable('$DATA//*:MiningField' PASSING
XMLPARSE(document (
select IDMMX.DM_expClusModel(cm.MODEL)
from IDMMX.CLUSTERMODELS cm
where MODELNAME=mname)) as "DATA"
columns
name varchar(20) path '@name',
usageType varchar(20) path '@usageType') as X;
END@详细了解功能的不同部分。 UDF被声明为表函数，这意味着它返回一个临时表，而不仅仅是一个值。 因为它是用SQL编写的，所以LANGUAGE是SQL。 对于所有其他创建函数选项，将使用默认值，这通常是一个很好的起点。
UDF的主体在BEGIN和END令牌之间实现。 所有结果列均在SQL / XQuery语句的select部分中指定。 在示例中，列是X.name和X.usageType，它们是XMLTABLE表达式的输出列。 X.usageType的case语句指示未将活动XML属性写入PMML文档。
之所以需要XMLPARSE，是因为PMML文档本身并不直接存储在XML列中。 需要使用IDMMX.DM_expClusModel函数从BLOB列中导出文档，然后应使用XMLPARSE函数将文档复制到XML值中。
一旦该文档可用作XML值，您就可以使用XQuery对其进行处理，如清单15中的本机XQuery示例所示。
要调用create function语句，请将语句放入文件中，并使用清单16中所示的语法进行调用。请注意，由于UDF内需要使用该字符，因此不使用常规的语句结尾字符。
清单16.调用包含create函数语句的文件
db2 -td@ -vf createClusterFieldUDF.db2 在select语句中调用UDF时，您将获得有关字段的信息，包括哪些字段是活动字段或补充字段，如清单17所示。
清单17.阅读有关字段的信息
db2 select t1.name, t1.usagetype from table
(getClusterMiningFields('BankCustomerSegments')) t1
NAME USAGETYPE
-------------------- --------------------
AGE active
NBR_YEARS_CLI active
GENDER active
MARITAL_STATUS active
PROFESSION active
SAVINGS_ACCOUNT active
HAS_LIFE_INSURANCE active
INT_CREDITCARD active
ONLINE_ACCESS active
JOINED_ACCOUNTS active
BANKCARD active
CLIENT_ID supplementary
AVERAGE_BALANCE active
NO_CRED_TRANS active
NO_DEBIT_TRANS active
15 record(s) selected.相同的功能也可以放入STP中。 创建STP的语法看起来不同，但是代码本身完全相同，如清单18所示。
清单18.使用STP
CREATE PROCEDURE getClusterMiningFields
(
in mname varchar(128)
)
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR
select X.name,
CASE WHEN X.usageType IS NULL THEN 'active'
ELSE X.usageType END
from XMLTable('$DATA//*:MiningField' PASSING
XMLPARSE(document (
select IDMMX.DM_expClusModel(cm.MODEL)
from IDMMX.CLUSTERMODELS cm
where MODELNAME=mname)) as "DATA"
columns
name varchar(20) path '@name',
usageType varchar(20) path '@usageType') as X;
OPEN cursor1;
END@您可以使用call语句从DB2命令行窗口调用STP。 如清单19所示，输出与在UDF实现中看到的输出相同。
清单19.从DB2命令行窗口调用STP的结果
db2 call getClusterMiningFields('BankCustomerSegments')
Result set 1
--------------
NAME 2
-------------------- --------------------
AGE active
NBR_YEARS_CLI active
GENDER active
MARITAL_STATUS active
PROFESSION active
SAVINGS_ACCOUNT active
HAS_LIFE_INSURANCE active
INT_CREDITCARD active
ONLINE_ACCESS active
JOINED_ACCOUNTS active
BANKCARD active
CLIENT_ID supplementary
AVERAGE_BALANCE active
NO_CRED_TRANS active
NO_DEBIT_TRANS active
15 record(s) selected.
Return Status = 0使用InfoSphere Warehouse工具获取用户定义的函数示例
InfoSphere Warehouse DesignStudio带有集成工具，可用于开发Java®和SQL STP和UDF。 本文的第二个示例创建一个UDF，该UDF从关联模型中读取模型信息。 该信息在关联可视化器中可用，但没有相应SQL提取器功能。 该工具有助于UDF的创建，部署和测试。
使用工具：
- 打开InfoSphere Warehouse DesignStudio。
- 通过选择窗口>打开透视图>其他，切换到数据透视图。
- 如果“数据”透视图不在列表中，请选择“ 显示全部” ，然后从列表中选择“ 数据 ”。 现在，您应该在左上角看到一个Data Project Explorer。
- 通过选择文件>新建>数据开发项目，创建一个新的数据开发项目 。
- 将新项目命名为PMMLExtraction 。
- 选择一个启用了挖掘并且包含关联模型的数据库。 对于此示例，选择示例数据库DWESAMP ，然后单击Finish 。 现在，您应该有一个新的Data Development项目，该项目具有UDF，STP和其他数据库对象的子文件夹，如图1所示。
图1. DesignStudio的数据透视图
单击此处查看图1的大图。
要创建SQL用户定义的函数：
- 通过选择“ 新建”>“用户定义的函数” ，然后右键单击“ 用户定义的函数”文件夹，创建一个新的UDF。
- 将UDF的名称更改为AssocInformationExtractor ，并确保语言为SQL 。
- 点击完成 。 这将创建一个非常基本的UDF。 现在，您可以添加所需的逻辑。
关联模型包含许多全局统计信息，包括事务处理数量和项目集数量。 您可以在InfoSphere Warehouse的Association Visualizer的Statistics选项卡中查看这些统计信息，如图2所示。
图2. Association Visualizer中的全局统计信息
该信息作为关联模型节点的属性保存在PMML模型中，如清单20所示。
清单20.关联模型属性
<PMML ...
<AssociationModel
modelName="MBA.RULES"
functionName="associationRules"
algorithmName="SIDE"
numberOfTransactions="34734"
maxNumberOfItemsPerTA="242"
avgNumberOfItemsPerTA="2.87349571025508"
minimumSupport="0.001"
minimumConfidence="0.1"
lengthLimit="4"
numberOfItems="25"
numberOfItemsets="62"
numberOfRules="74"
x-quality="1">UDF本身与本文第一个示例中的操作非常相似。 它获取模型名称作为参数，并为每个统计信息返回一个包含一列的表。 关联模型与序列模型一起分组为规则模型，这些规则模型保存在IDMMX.RULEMODELS表中。 因为PMML模型中只有一个AssociationModel节点，所以它的xPath可以写为$ DATA // *：AssociationModel ，它搜索具有任何父节点的AssociationModel节点。 清单21显示了完整的UDF代码，可以输入到DesignStudio中编辑器的Source视图中。
清单21. Source视图的UDF代码
CREATE FUNCTION ASSOCINFORMATIONEXTRACTOR (mname varchar(128))
RETURNS TABLE (numberTransactions int,
averageNumberItemsPerTransaction real,
maximumNumberItemsPerTransaction int,
numberItemSets int,
numberSingletonItemSets int,
minimumRuleSupport real,
minimumRuleConfidence real,
maximumRuleLength int)
LANGUAGE SQL
MODIFIES SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
RETURN select X.numberTransactions,
X.averageNumberItemsPerTransaction,
X.maximumNumberItemsPerTransaction,
X.numberItemSets,
X.numberSingletonItemSets,
X.minimumRuleSupport,
X.minimumRuleConfidence,
X.maximumRuleLength from
XMLTable('$DATA//*:AssociationModel' PASSING XMLPARSE(document
(select IDMMX.DM_expRuleModel(cm.MODEL) FROM IDMMX.RULEMODELS cm
where MODELNAME=mname)) as "DATA"
columns
numberTransactions int path '@numberOfTransactions',
averageNumberItemsPerTransaction real path '@avgNumberOfItemsPerTA',
maximumNumberItemsPerTransaction int path '@maxNumberOfItemsPerTA',
numberItemSets int path '@numberOfItemsets',
numberSingletonItemSets int path '@numberOfItems',
minimumRuleSupport real path '@minimumSupport',
minimumRuleConfidence real path '@minimumConfidence',
maximumRuleLength int path '@lengthLimit'
) as X ;
END将此UDF部署到数据库：
- 在Data Project Explorer中右键单击UDF，然后选择Deploy 。
- 输入UDF的模式，例如示例DB2XML 。 其他默认设置将其部署到当前数据库，删除具有类似名称的UDF，并为Java UDF构建代码。
- 单击完成以部署UDF。 您会在右下方的“数据输出”视图中看到“
Deploy successful消息。 - 从命令行使用该过程，或者通过右键单击Run从DesignStudio运行该过程。
- 在“指定参数值”窗口上，输入现有关联模型的模型名称。 对于InfoSphere Warehouse示例，输入MBA.RULES 。 在数据输出视图中，您可以看到带有模型全局统计信息的结果集，如图3所示。
图3.全局统计结果集
您还可以使用InfoSphere Warehouse Data工具来开发和调试UDF和STP。
结论
文章显示了结合使用XQuery和Intelligent Miner编写自己的PMML提取函数并将结果作为关系数据传递到SQL中是多么容易。 不需要编程语言，并且创建UDF的过程更容易，因为它受图形工具的支持。
翻译自: https://www.ibm.com/developerworks/data/library/techarticle/dm-0905dataminingxquery/index.html
扫一扫
517
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
