CDA-Community Data Access
(来源:https://community.hitachivantara.com/s/article/community-data-access)
简介
CDA为多种数据源提供了灵活性,它可以同时连接多个数据源来检索数据,通过API将数据提供给第三方。
主要功能:
- 通过编辑XML就可以从多个不同数据源中联合查询数据。
- 缓存查询机制可以大大提高查询性能
- 避免CDF中的sql注入问题
- 在PUC使用CDA,查询结果可以导出多种数据格式(csv,xls,etc)。
- 在数据服务器端可以进行排序和分页
CDA结构
CDA有两个组成部分:
- 数据源连接(Connections)
- 数据接入方式(Data Access)
CDA文件存放在pentaho的资源库里,它本质是一个xml文件,数据查询是通过调用指定的DataAcces ID来实现的。
每一种数据接入方式都可以带参数或列计算来操作查询结果。同样,CDA也允许多种数据接入方式联合查询,相当于SQL中的Join或Union。
CDA主要有以下特征:
- 一个用于查询数据结果的API
- 一个用于修改CDA的编辑器
- 一个用于以表形式可视化数据的预览器
- 导出:查询结果能从API以多种格式返回。目前支持的数据格式为:JSON,XML,CSV,XLS及HTML。
CDA文件
CDA文件是一个包含数据源连接(DataSources)和数据查询(DataAccess)的XML文件。CDA内容必须包含于<CDADescriptor>代码块中
<!--CDA基本框架-->
<?xml version="1.0" encoding="utf-8"?><CDADescriptor><DataSources><Connection id="数据源唯一标识符" type="数据源连接方式">(...)</Collection></DataSources><DataAccess id="数据源唯一标识符" connection="..." type="数据源接入方式" access="..." cache="..." cacheDuration="...">(...)</DataAccess></CDADescriptor>
在主代码块内我们要定义数据源,数据接入方式或联合数据接入(union,join查询)
可以单独编写XML来创建CDA;也可以在PUC新建CDE,然后在data sources层进行配置,保存后即可生成CDA文件。
<!--举个例子 jndi连接sql查询-->
<?xml version="1.0" encoding="utf-8"?>
<CDADescriptor><!-- DataSource definition. Type controls what the datasource type is.The connection and query controls how the data is fetched. These valuesare specific to each access type--><DataSources><Connection id="1" type="sql.jndi"><Jndi>SampleData</Jndi></Connection></DataSources><!-- DataAccess object controls the query itselfAccess controls if the datasource can be seen from the outside or is tobe used from a Compound datasource only--><DataAccess id="1" connection="1" type="sql" access="public" cache="true" cacheDuration="3600"><Name>Sql Query on SampleData - Jndi</Name><Query>select o.YEAR_ID, o.STATUS, sum(o.TOTALPRICE) as price from orderfact ogroup by o.YEAR_ID, o.STATUS</Query></DataAccess>
</CDADescriptor>
Datasources和DataAccess对应关系表
Datasources | DataAccess |
---|---|
metadata.metadata | mql |
sql.jdbc | sql |
sql.jndi | sql |
mondrian.jdbc | Mdx,denormalizedMdx |
mondrian.jndi | Mdx,denormalizedMdx |
olap4j.jdbc | olap4j |
scripting.scripting | jsonScriptable,scriptable |
kettle.TransFromFile | kettle |
xpath.xPath | xPath |
dataservices.dataservices | streaming |
##Datasources
数据源在<Datasources>代码块中定义。每个数据源连接的必要信息都将单独放在一个<Connection>代码块中。对于每一个连接而言,都必须有一个唯一的ID和类型。
###CDA支持的连接方式
- metadata.metadata
- sql.jdbc
- sql.jndi
- mondrian.jdbc
- mondrian.jndi
- olap4j.jdbc
- scripting.scripting
- kettle.TransFromFile
- xpath.xPath
- dataservices.dataservices
metadata.metadata
- ID: 数据源唯一标识符
- XmiFile: 该文件包含metadata所有定义,可用pme来编写,即pentaho metadata editor(参见教程),该文件需上传至pentaho数据源。
- DomainId: Pentaho metadata层定义的域(提前定义一个metadata数据源)
<!-- 举例说明 -->
<DataSources><Connection id="1" type="metadata.metadata"><DomainId>steel-wheels</DomainId><XmiFile>metadata.xml</XmiFile></Connection>
</DataSources>
CDE编辑器: MQL Quries -> mql over metadata
sql.jdbc
- ID:数据源唯一标识符
- Driver: 数据源JDBC驱动
- URL: 数据源连接
- User Name:数据源用户名
- Password: 数据源密码
<!-- 举例说明 -->
<DataSources><Connection id="1" type="sql.jdbc"><Driver>org.hsqldb.jdbcDriver</Driver><Url>jdbc:hsqldb:mem:SampleData</Url><User>sa</User><Pass></Pass></Connection>
</DataSources>
CDE编辑器: SQL Quries -> sql over sqlJdbc
sql.Jndi
- ID: 数据源唯一标识符
- Jndi: 在pentaho的context.xml文件定义的数据源连接名,或者是pentaho平台上的数据源名。
<!-- 举例说明 -->
<DataSources><Connection id="1" type="sql.jndi"><Jndi>SampleData</Jndi></Connection>
</DataSources>
CDE编辑器: SQL Quries -> sql over sqlJndi
mondrian.jdbc
- ID: 数据源唯一标识符
- Mondrian schema: mondrian schema文件,该文件需上传至pentaho数据源.可用psw来编写,即pentaho schema workbench(参见教程), 或直接编写xml(参见教程)
- Driver: 数据源JDBC驱动
- URL: 数据源连接
- User Name:数据源用户名
- Password: 数据源密码
- Banded Mode: compact 或者 classic. 区别将在Data access中指出。
<!-- 举例说明 -->
<DataSources><Connection id="1" type="mondrian.jdbc"><Driver>org.hsqldb.jdbcDriver</Driver><Url>jdbc:hsqldb:mem:SampleData</Url><User>sa</User><Pass></Pass><Catalog>mondrian:/SteelWheels</Catalog><Cube>SteelWheelsSales</Cube></Connection>
</DataSources>
CDE编辑器:MDX Queries -> mdx over mondrianJdbc/denormalizedMdx over mondrianJdbc, 两者区别将在Data access中指出。
(备注:CDE编辑器不能选择cube)
mondrian.jndi
- ID: 数据源唯一标识符
- Mondrian schema: mondrian schema文件
- Jndi: 数据源JNDI
- Banded Mode: compact or classic.
<!-- 举例说明 -->
<DataSources><Connection id="1" type="mondrian.jndi"><Jndi>SampleData</Jndi><Catalog>mondrian:/SteelWheels</Catalog><Cube>SteelWheelsSales</Cube></Connection>
</DataSources>
CDE编辑器:MDX Queries -> mdx over mondrianJndi/denormalizedMdx over mondrianJndi, 两者区别将在Data access中指出。
(备注:CDE编辑器中mdx over mondrianJndi不可以选择选择cube,选择cube只能使用Wizards -> OLAP Selctor Wizards)
olap4j.jdbc
olap4j 是mondrian主要的API,Java接口标准,类似于JDBC 是sql 的接口标准。
- ID: the identifier of the connection.
- Driver: olap4j驱动(如mondrian.olap4j.MondrianOlap4jDriver).
- Url: 连接url, 如:jdbc:mondrian:.
- (Property)User name for the underlying database: 底层数据库的用户名.
- (Property)Password for the underlying database : 数据库密码。
- (Property)Driver for the underlying database: 底层数据库驱动
- (Property)URL pointing at the underlying database: 底层数据库url
- (Property)Catalog to apply to the underlying database:mondrian schema文件,该文件需上传至pentaho数据源(导入Analyse)
<!-- 举例说明 -->
<Connection id="1" type="olap4j"><Driver>mondrian.olap4j.MondrianOlap4jDriver</Driver><Url>jdbc:mondrian:</Url><Property name="JdbcUser">pentaho_user</Property><Property name="JdbcPassword">password</Property><Property name="Jdbc">jdbc:hsqldb:hsql://localhost:9001/Sampledata</Property><Property name="JdbcDrivers">org.hsqldb.jdbcDriver</Property><Property name="Catalog">mondrian:/SteelWheels</Property>
</Connection>
CDE编辑器:OLAP4J Quries -> olap4j over olap4j/denormalizedOlap4j over olap4j, 两者区别将在Data access中指出。
scripting.scripting
- ID: 数据源唯一标识符
- Language: 使用的脚本语言;目前只支持beanshell。beanshell是Java轻量化脚本语言,参考教程
- InitScript: 要在查询之前运行的初始化脚本
<!-- 举例说明 -->
<DataSources><Connection id="1" type="scripting.scripting"><Language>beanshell</Language><InitScript/></Connection>
</DataSources>
CDE编辑器: SCRIPTING Queries -> jsonScriptable over scripting/scritable over scripting. 其中Datasources配置是一样的,区别在Data Access的query语言。
备注:用CDE编辑Query时会自动加<![CDATA[ ]]>
标签,平台不会解析代码,导致CDA查不出来。只能单独编写CDA.xml来实现这种接入方式。
kettle.TransFromFile
- ID: 数据源唯一标识符
- Kettle Transformation File:用到的Kettle文件,可使用相对路径。
- Variable: 输入kettle文件的变量,可以包含很多变量,每个变量单独标签。
<!-- 举例说明 -->
<DataSources><Connection id="1" type="kettle.TransFromFile"><KtrFile>sample-trans.ktr</KtrFile><variables datarow-name="myRadius"/><variables datarow-name="ZipCode" variable-name="myZip"/></Connection>
</DataSources>
CDE编辑器: KETTLE Queries -> kettle over kettle TransFormFile
xpath.xpath
- ID: 数据源唯一标识符
- DataFile: xml格式的数据查询文件,可使用相对路径。xPath语法参考教程.
<!-- 举例说明 -->
<DataSources><Connection id="1" type="xpath.xPath"><DataFile>customer.xml</DataFile></Connection>
</DataSources>
CDE编辑器: XPATH Queries -> xPath over xPath
dataservices.dataservices
- ID: 数据源唯一标识符
<!-- 举例说明 -->
<DataSources><Connection id="realtime_chart" type="dataservices.dataservices"/>
</DataSources>
CDE编辑器:Dataservices Queries -> StreamingDataServiceName
数据接入方式(Data Access)
Simple Data Access
所谓的简单的数据接入方式即是对连接的数据源做查询.这一部分定义在<DataAccess>(...)</DataAccess>
块中。
CDA支持以下的数据接入方式:
- SQL;
- MQL
- MDX;
- denormalizedMdx;
- MetaData;
- Kettle;
- Streaming;
- JsonScriptable;
- Scriptable;
- 等等
- 以上查询的组合
DataAccess需定义的属性包括:
属性 | 说明 |
---|---|
Access Level | 必填项:public/private。 public类型允许外部调用; private类型只能在CDA内被其他接入方式调用 |
Parameters | 非必填项:参数列表。 name:自定义参数名; default value:如果在调用数据访问时未指定参数值,则为默认值; 参数类型:String, Integer, Numeric, Date, StringArray, IntegerArray, NumericArray and DateArray |
Output | 输出哪些查询字段并定义输出字段的顺序,若不填则按Query查询结果输出 |
Output mode | include/exclude。输出结果是否包含Output中定义的字段。默认include |
Columns | 列的名称,若不填则为Query查询的列名 |
Calculated Columns | 需要计算的列名/计算公式,若不填则无计算。 |
Query | 按数据源选择合适查询语句。 |
Cache | True/False。是否缓存结果。 |
Cache Duration | 整型,单位秒。 |
Parameters
Parameters是传给查询(即Query块)的参数,例如可以在Query中加入参数
<Query>select o.YEAR_ID, o.STATUS, sum(o.TOTALPRICE) as price from orderfact owhere o.STATUS = ${status} and o.ORDERDATE > ${orderDate}group by o.YEAR_ID, o.STATUS
</Query>
其中${status}
和${orderDate}
就是传参。传参在CDA中被<Parameters>
块定义。定义包括:
- type:必填项。可选择String, Integer, Numeric (double), Date, StringArray, IntegerArray, NumericArray and DateArray;
- default: 必填项。调用数据访问时未指定参数值的默认值。
- Pattern:日期格式。SimpleDateFormat Java类支持的日期格式
<!--举个例子-->
<Parameters><Parameter name="status" type="String" default="Shipped"/><Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2003-03-01"/>
</Parameters>
Columns
包含<Column>和<CaculatedColumn>.
<Column>代码块支持修改列名、定义新的列。查询出的每一列都可以在<colume>在重命名
<!--举个例子,将第二列重命名为Year-->
<Columns><Column idx="1"><Name>Year</Name></Column>
<Columns>
<CaculatedColumn>代码块支持列计算,其中定义了新列的名称和要使用的公式。每个<CaculatedColumn>包含两个属性:
- Name:CDA最终输出的列名
- Formula: 自定义,参考公式。
<!--举个例子-->
<Columns><Column idx="1"><Name>Year</Name></Column><CalculatedColumn><Name>PriceInM</Name><Formula>=[price]/1000000</Formula></CalculatedColumn>
<Columns>
Output
Output标签定义输出的列和列的顺序,如果没有定义,则默认输出查询结果的列和顺序。
Output只有一个属性,即列的index。
<!--举个例子-->
<Output indexes="1,0,3"/>
特殊标签
有一些标签只存在特定的数据接入方式中
Mdx Data Accesses -> Banded mode
这个属性将会影响mondrain的输出结果形式。例如同一个mdx查询:
--查每个超市的某段时间的销量
select NON EMPTY {[Measures].[Sales]} ON COLUMNS,
NON EMPTY ({[Markets].[EMEA].Children}) ON ROWS
from [SteelWheelsSales]
WHERE [Time].[2005]
Compact mode
<!--举个例子-->
<DataAccess access="public" connection="test" id="test" type="mdx"><Name>test</Name><BandedMode>compact</BandedMode><Cache duration="3600" enabled="true"/><Columns/><Parameters><Parameter default="Shipped" name="status" type="String"/></Parameters><Query>select NON EMPTY {[Measures].[Sales]} ON COLUMNS,NON EMPTY ({[Markets].[EMEA].Children}) ON ROWSfrom [SteelWheelsSales]WHERE [Time].[2005]</Query>
</DataAccess>
这种模式会导出两列,一列Market,一列sales
Market | Sales |
---|---|
Austria | 68250.26 |
Belgium | 25040.11 |
… | … |
Classic mode
这种模式会导出完整的mondrian层级
[Markets].[(all)] | [Markets].[Territory] | [Markets].[Country] | [Measures].[Sales] |
---|---|---|---|
All Markets | EMEA | Austria | 68250.26 |
All Markets | EMEA | Belgium | 25040.11 |
Mdx Data Accesses -> Normalized vs. Denormalized MDX
CDA的MDX查询有两种类型,即标准化查询(Normalized)mdx和非标准化查询Denormalized mdx
以例子来看区别。
--查每个超市的某段时间的销量
select {[Measures].[Sales], [Measures].[Quantity]} ON COLUMNS,
NON EMPTY [Time].Children ON ROWS
from [SteelWheelsSales]
where ([Order Status].[${status}])
Normalized mdx
[Time].[(All)] | [Time].[Years] | [Measures].[Sales] | [Measures].[Quantity] |
---|---|---|---|
All Years | 2004 | 4750205.89 | 47151 |
Denormalized mdx
[Time].[(All)] | [Time].[Years] | [Measures].[MeasuresLevel] | Measure |
---|---|---|---|
All Years | 2004 | Quantity | 47151 |
All Years | 2004 | Sales | 4750205.89 |
Streaming Data Accesses
实时数据流
属性 | 说明 |
---|---|
StreamingDataServiceName | 数据服务的名称。例如,由pdi提供数据服务,需要在ktr中对输出控件设置Data service,并命名。ktr文件需上传至pentaho repository. |
WindowEvery | 滚动行间隔。 |
Window Mode | Row based/Time based(默认) Row based:按照行变化动态更新数据; Time based:按照时间变化动态更新数据 |
WindowSize | 整型,默认0。显示最大行数,0表示无限制。 |
WindowLimit | 整型,默认0。限制显示行数,0表示无限制。 |
<!-- 举例说明 -->
<?xml version="1.0" encoding="UTF-8"?>
<CDADescriptor><DataSources><Connection id="real_time_table" type="dataservices.dataservices"/></DataSources><DataAccess access="public" connection="real_time_table" id="real_time_table"type="streaming"><Name>real_time_table</Name><Columns/><DataServiceQuery>SELECT increaseDateDate, Lap, latG, longG, Kmh, Gear, Rpm, Accelerator, Brake
FROM "plugin_sample_real_time_require"</DataServiceQuery><Parameters/><StreamingDataServiceName>plugin_sample_real_time_require</StreamingDataServiceName><WindowEvery>1</WindowEvery><WindowLimit>0</WindowLimit><WindowMode>ROW_BASED</WindowMode><WindowSize>18</WindowSize></DataAccess>
</CDADescriptor>
Kettle Data Accesses -> Query
要想从kettle得到数据,必须在<Query>块中定义输出控件名。
<Query>输出控件名</Query>
对应CDE编辑器中的kettle over kettleTransFormFile,<Query>对应的是[Kettle Step name]这个配置项。
Compound Data Accesses
<Compound Data Access>定义了两个数据查询集的连接,分为Union和Join。
需要先定义用以查询的<Data Access>,再定义<Compound Data Access>
<DataAccess id="1" connection="1" type="sql" access="private" cache="true" cacheDuration="300">(...)
</DataAccess>
<DataAccess id="2" connection="1" type="sql" access="public" cache="true" cacheDuration="5">(...)
</DataAccess>
<CompoundDataAccess id="3" type="join">(...)
</CompoundDataAccess>
Union
需要以下属性:
- Name: compound结果集名称
- Top: 放在前面的查询集的ID
- Bottom: 放在后面的查询集的ID
- Parameters: 传入union集合的参数列表。
若两个查询集的列名不同,则以前面的查询集的名字为准。
<!--举个例子,第二个查询集union第一个查询集,并查year=2004的数据-->
<CompoundDataAccess id="3" type="union"><Top id="2"/><Bottom id="1"/><Parameters><Parameter name="year" type="Numeric" default="2004"/></Parameters>
</CompoundDataAccess>
Join
Join连接可设置以下属性:
- Name: compound结果集名称
- Left: 第一个查询集
- Left Keys: 第一个查询集中用以连接的字段id
- Right: 第二个查询集
- Right Keys: 第二个查询集中用以连接的字段id
- Parameters: 传入join集合的参数列表。
- Output Options: 可选择输出的列ID, 以左连接查询集在前,有连接的查询集在后的顺序确定列ID。
<!--举个例子-->
<CompoundDataAccess id="3" type="join"><Left id="1" keys="0,1"/><Right id="2" keys="0,1"/><Parameters><Parameter name="status" type="String" default="Shipped"/><Parameter name="orderDate" type="Date" pattern="yyyy-MM-dd" default="2003-03-01"/></Parameters><Output indexes="0,1,2,5,6"/>
</CompoundDataAccess>
Web API 介绍
CDA是被Http请求以$BASE_URL/pentaho/plugin/cda/api/
来调用的。
支持方法
- doQuery;
- listQueries;
- getCdaList;
- listParameters;
- clearCache;
- listDataAccessTypes.
CDA文件
以上有些方式是需要在URL中指定CDA文件的,只需要将在资源库的完整路径加入url即可。
path=public/plugin-samples/cda/cdafiles/compoundJoin.cda
Output
以上的方式(除了doQuery和clearCache)的输出结果集都是Json格式的。
- 字段值
- 字段元数据(列索引, 列名, 列类型)
doQuery可以输出多种格式,包括JSON, XML, XLS or CSV,默认JSON。
clearCache只返回string: “Cache cleared”。
方法介绍
doQuery
doQuery建立必要的连接并通过DataAccessID来指定查询集。
对于有传参的查询集,可以指定参数值进行查询,如果不指定则传入的是默认值。Url中参数将以paramParameter来传递, 其中Parameter是参数名。
举例说明:
已知条件:
DataAccessID = 2;
CDA path = $SOLUTION_REPOSITORY/public/plugin-samples/cda/cdafiles/compoundJoin.cda;
parameter = status, default value=shipped
URL:
$BASE_URL/pentaho/plugin/cda/api/doQuery?path=public/plugin-samples/cda/cdafiles/compoundJoin.cda&dataAccessId=2¶mstatus=Shipped
listQueries
该方法返回指定CDA文件中所有公共查询的JSON格式列表。
getCdaList
返回solution repository中所有CDA文件的JSON格式列表。
listDataAccessTypes
返回一个JSON格式的列表,其中包含所有支持的数据访问类型和连接及其定义。
listParameters
返回数据访问使用的参数列表。要求指定路径和数据连接。
系统参数
系统参数可直接被parameter引用,通过parameter传入query即可实现分角色分用户查询。
获取角色列表: [ s e c u r i t y : p r i n c i p a l R o l e s ] , 例 如 : A u t h e n t i c a t e d , A d m i n i s t r a t o r 获 取 当 前 用 户 : {[security:principalRoles]},例如:Authenticated,Administrator 获取当前用户: [security:principalRoles],例如:Authenticated,Administrator获取当前用户:{[security:principalName]},例如:Admin