## <strong>创建业务视图-SQL查询</strong>
- <strong>接口调用-方式1</strong>
```java
/**
* 创建业务视图-SQL查询示例
*/
public class createBizViewDemo {
public static void main(String[] args) {
String user = "admin"; // 用户名
String password = "admin"; // 密码
String smartbiURL = "http://10.10.31.49:11000/smartbi"; // Smartbi链接
ClientConnector conn = null;
try {
conn = new ClientConnector(smartbiURL); // 创建Smartbi链接对象
// 建立此连接时，就对smartbi进行了登录
conn.open(user, password);
// POST请求的请求体
// pid必须是数据源中业务视图下的节点，包括业务视图节点和目录节点
String body = "{\r\n" + "\"pid\": \"I8a8a4ca2017a18e018e0a2b3017a19209a7c13a3\",\r\n"
+ " \"name\": \"SQL查询1\",\r\n" + " \"alias\": \"SQL查询1\",\r\n"
+ " \"sql\": \"select OrderID as a from orders\"\r\n" + "}";
JSONObject json = JSONObject.fromString(body);
// 创建业务视图-SQL查询
InvokeResult result = conn.remoteInvoke("AugmentedDataSetForVModule", "createDataModelBizView",
new Object[] { json });
if (result != null && result.isSucceed()) {
JSONObject object = JSONObject.fromObject(result.getResult());
String id = (String) object.optString("id");
System.out.println("SQL查询的id: " + id);
}
} finally {
if (conn != null) {
conn.close();
}
}
}
}
```
- <strong>接口调用-方式2（仅支持在已登录的情况下）</strong>
http://host:port/smartbi/smartbix/api/dataModel/createBizView
- <strong>接口请求类型</strong>
POST
- <strong>输入</strong>
DataModelBizViewCreatedVO
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ---------------------------------------- |
| pid | Stirng | 业务视图的父节点，必须是数据源中业务视图下的节点，包括业务视图节点和目录节点，如果不是会抛异常
| id | String | 业务视图的id |
| name | String | 业务视图的名称 |
| alias | String | 业务视图的别名 |
| sql | String | 业务视图的sql, 暂不支持参数 |
- <strong>返回值</strong>
CheckResult
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ---------------------------------------- |
| id | Stirng | 模型 id |
| errorCode | String | 错误码，成功时错误码、错误信息同时为空 |
| errorMessage | String | 错误信息，成功时错误码、错误信息同时为空 |
| detail | String | 错误详细信息，成功时错误码、错误信息同时为空 |
### <strong>简单示例</strong>
```json
{
"name": "SimpleModelDemo",
"alias": "模型简单示例",
"views": [{
"name": "orders",
"alias": "订单表",
"type": "BASIC_TABLE",
"define": {
"tableId": "TAB.northwind.null.orders"
}
}],
"dimensions":[{
"name": "ShipRegion",
"alias": "区域",
"viewName": "orders",
"fieldName": "ShipRegion",
"valueType": "STRING"
}],
"measures": [{
"name": "Freight",
"alias": "运费",
"viewName": "orders",
"aggregator": "sum",
"fieldName": "Freight"
}]
}
```
### 简单示例 2：SQL 私有查询
```json
{
"name": "SimpleModelDemo2",
"alias": "模型简单示例2",
"views": [{
"name": "orders",
"alias": "订单表",
"type": "SQL_VIEW",
"define": {
"dataSourceId": "DS.notthwind",
"sql": " select * from orders"
}
}]
}
```
### 简单示例3：Java私有查询带参数
```json
{
"name": "DataModel_java11",
"alias": "DataModel_java11",
"views": [{
"name": "javaDemo",
"alias": "java示例",
"type": "JAVA_VIEW",
"define": {
"className": "smartbi.composite.AccessibleFunctionQueryData",
"paramDefaultValues": {
"Department": "DEPARTMENT",
"UserName": "ADMIN"
}
},"extractSetting": {
"type": "REALTIME"
}
}],
"dimensions":[{
"name": "FunctionAlias",
"alias": "功能别名",
"viewName": "javaDemo",
"fieldName": "FunctionAlias",
"valueType": "STRING"
}, {
"name": "UserAlias",
"alias": "用户别名",
"viewName": "javaDemo",
"fieldName": "UserAlias",
"valueType": "STRING"
}],
"measures": [{
"name": "FunctionId",
"alias": "功能ID",
"viewName": "javaDemo",
"aggregator": "count",
"fieldName": "FunctionId"
}]
}
```
### 简单示例4：Java私有查询带配置信息
```json
{
"name": "DataModel_csv3",
"alias": "DataModel_csv3",
"views": [{
"name": "javaDemo",
"alias": "java示例",
"type": "JAVA_VIEW",
"define": {
"className": "smartbi.CSVJavaQuery",
"configs": {
"FileName": "D:/foodmart_store.csv",
"Encoding": "utf-8"
}
}
}],
"dimensions":[{
"name": "store_street_address",
"alias": "地址",
"viewName": "javaDemo",
"fieldName": "store_street_address",
"valueType": "ASCII"
}],
"measures": [{
"name": "store_number",
"alias": "数量",
"viewName": "javaDemo",
"aggregator": "sum",
"fieldName": "store_number"
}]
}
```
### 简单示例5：SQL私有查询带参数
```json
{
"name": "SimpleModelDemo5",
"alias": "SQL私有查询带参数",
"views": [
{
"name": "orders",
"alias": "订单表",
"type": "SQL_VIEW",
"define": {
"dataSourceId": "DS.notthwind",
"sql": " select * from orders where {[orderId = ${param}]}"
},
"parameters": [
{
"name": "param",
"valueType": "STRING",
"defaultValue": {
"value": [
[
"SQL参数默认值",
""
]
],
"dataSource": "DS.northwind"
}
}
]
}
]
}
```
### 简单示例6：SQL私有查询带参数，有私有参数进行映射
```json
{
"name": "SimpleModelDemo6",
"alias": "私有参数映射SQL参数",
"views": [
{
"name": "orders",
"alias": "订单表",
"type": "SQL_VIEW",
"define": {
"dataSourceId": "DS.notthwind",
"sql": " select * from orders where {[orderId = ${param}]}"
},
"parameters": [
{
"name": "param",
"valueType": "STRING",
"defaultValue": {
"value": [
[
"",
""
]
],
"dataSource": "DS.northwind"
}
}
]
}
],
"parameters": [
{
"visible": true,
"name": "参数-1726717634521",
"refMapping": [
{
"refViewName": "orders",
"refParamName": "param"
}
],
"valueType": "STRING",
"componentType": "LIST_SINGLE",
"defaultType": "CONSTLIST",
"defaultValue": {
"value": [
[
"默认值真实值",
"默认值显示值"
]
]
},
"standByType": "CONSTLIST",
"standByValue": {
"value": [
[
"备选值真实值",
"备选值显示值"
]
]
},
"alias": "参数"
}
]
}
```
### 简单示例7：SQL私有查询带参数，有私有参数进行映射（私有参数含有SQL）
```json
{
"name": "SimpleModelDemo7",
"alias": "私有参数含SQL映射SQL参数",
"views": [
{
"name": "orders",
"alias": "订单表",
"type": "SQL_VIEW",
"define": {
"dataSourceId": "DS.notthwind",
"sql": " select * from orders where {[orderId = ${param}]}"
},
"parameters": [
{
"name": "param",
"valueType": "STRING",
"defaultValue": {
"value": [
[
"",
""
]
],
"dataSource": "DS.northwind"
}
}
]
}
],
"parameters": [
{
"visible": true,
"name": "参数-1726717634521",
"refMapping": [
{
"refViewName": "orders",
"refParamName": "param"
}
],
"valueType": "STRING",
"componentType": "LIST_SINGLE",
"defaultType": "SQL",
"defaultValue": {
"SQL": "select * from `orderdetails`",
"nameColumn": "OrderID",
"aliasColumn": "OrderID",
"dataSource": "DS.northwind"
},
"standByType": "CONSTLIST",
"standByValue": {
"value": [
[
"备选值真实值",
"备选值显示值"
]
]
},
"alias": "参数"
}
]
}
```
### 简单示例8：创建树形私有参数
```json
{
"name": "SimpleModelDemo8",
"alias": "创建树形私有参数",
"views": [
{
"name": "orders",
"alias": "订单表",
"type": "SQL_VIEW",
"define": {
"dataSourceId": "DS.notthwind",
"sql": " select * from orders where {[orderId = ${param}]}"
},
"parameters": [
{
"name": "param",
"valueType": "STRING",
"defaultValue": {
"value": [
[
"",
""
]
],
"dataSource": "DS.northwind"
}
}
]
}
],
"parameters": [
{
"visible": true,
"name": "参数-1726717634521",
"refMapping": [
{
"refViewName": "orders",
"refParamName": "param"
}
],
"valueType": "STRING",
"componentType": "TREE_SINGLE",
"defaultType": "SQL",
"defaultValue": {
"SQL": "select * from `orderdetails`",
"nameColumn": "OrderID",
"aliasColumn": "OrderID",
"dataSource": "DS.northwind"
},
"standByType": "SQL",
"standByValue": {
"SQL": "select * from `orderdetails`",
"nameColumn": "OrderID",
"aliasColumn": "OrderID",
"dataSource": "DS.northwind",
"pidColumn": "OrderID",
"idColumn": "OrderID",
"orderBy": [
{
"colName": "ProductID",
"type": "asc"
}
]
},
"rootType": "SQL",
"rootValue": {
"SQL": "select * from `orderdetails`",
"nameColumn": "OrderID",
"aliasColumn": "UnitPrice",
"dataSource": "DS.northwind"
},
"alias": "参数"
}
]
}
```
### <strong>详细示例：2 表关联、定义层次、日期层次、计算度量、计算成员</strong>
```json
注意：需要把注释去掉
{
"name": "ModelDemo",
"alias": "模型示例",
// 私有查询列表
"views": [{
"name": "orderdetails",
"alias": "订单明细表",
"type": "BASIC_TABLE",
"define": {
"tableId": "TAB.northwind.null.orderdetails"
}
/*,
"storeType": "EXTRACT",
"extractSetting": {
"type": "FULL"
}*/
},{
"name": "orders",
"alias": "订单表",
"type": "BASIC_TABLE",
"define": {
"tableId": "TAB.northwind.null.orders"
}
/*,
"storeType": "EXTRACT",
"extractSetting": {
"type": "FULL"
}*/
}],
// 层次结构列表
"hierarchies": [{
"name":"hierArea",
"alias": "地区",
"levels": [
{
"viewName": "orders",
"name": "ShipRegionLevel",
"alias": "区域",
"fieldName": "ShipRegion",
"levelType": "LEVEL"
},
{
"viewName": "orders",
"name": "ShipProvinceLevel",
"alias": "省份",
"fieldName": "ShipProvince",
"levelType": "LEVEL"
}
]
}],
// 日期层次结构列表
"dateHierarchies": [{
"name":"hierDate",
"alias": "时间维度",
"levels": [
{
"viewName": "orders",
"name": "orderYear",
"alias": "年",
"fieldName": "OrderDate",
"levelType": "LEVEL_TIME_YEAR"
},
{
"viewName": "orders",
"name": "orderQuarter",
"alias": "季",
"fieldName": "OrderDate",
"levelType": "LEVEL_TIME_QUARTER"
},
{
"viewName": "orders",
"name": "orderMonth",
"alias": "月",
"fieldName": "OrderDate",
"levelType": "LEVEL_TIME_MONTH"
},
{
"viewName": "orders",
"name": "orderDay",
"alias": "日",
"fieldName": "OrderDate",
"levelType": "LEVEL_TIME_DAY"
}
]
}],
// 维度结构列表
"dimensions":[{
"name": "ShipRegion",
"alias": "区域",
"viewName": "orders",
"fieldName": "ShipRegion",
"valueType": "STRING"
}],
// 表关系图
"relationGraph": {
"relations": [{
"srcViewName": "orderdetails",
"destViewName": "orders",
"linkType": "LEFTJOIN",
"fieldRelations": [{
"srcFieldName": "OrderID",
"destFieldName": "OrderID"
}]
}]
},
// 度量列表
"measures": [{
"name": "Quantity",
"alias": "Quantity",
"viewName": "orderdetails",
"aggregator": "sum",
"fieldName": "Quantity"
},{
"name": "UnitPrice",
"alias": "UnitPrice",
"viewName": "orderdetails",
"aggregator": "sum",
"fieldName": "UnitPrice"
}],
// 计算度量列表
"calcMeasures": [
{
"name":"calc1",
"alias":"数量加1",
"expression": "[Measures].[Quantity] + 1"
}
],
// 计算成员列表
"calcMembers": [
{
"name":"north",
"alias":"北方",
"expression": "[hierArea].[东北] + [hierArea].[华北]",
"hierName": "[ShipRegion]"
}
]
}
```
- <strong>接口参数及其说明</strong>
pid：父节点 id;
post 内容的结构如下，详细说明如下：
##### <strong>DataModelVO 模型</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | ------------------------------ | ---------------------------- |
| id | String | 模型 id，更新时才需要填 |
| name | String | 模型名称 |
| alias | String | 模型别名 |
| views | LIst\<DataModelViewVO\> | 私有查询列表 |
| measures | LIst\<DataModelMeasureVO\> | 度量列表 |
| relationGraph | DataModelRelationGraphVO | 表关系图 |
| hierarchies | List\<DataModelHierarchyVO\> | 层次结构列表 |
| dateHierarchies | List\<DataModelDateHierarchyVO\> | 基于日期字段生成时间层次列表 |
| calcMeasures | LIst\<DataModelCalcMeasureVO\> | 计算度量列表 |
| calcMembers | LIst\<DataModelCalcMemberVO\> | 计算成员列表 |
| namedSets | LIst\<DataModelNamedSetVO\> | 命名集列表 |
| parameters | LIst\<DataModelParameterVO\> | 私有参数列表 |
##### <strong>DataModelViewVO 私有查询</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| name | String | 名称 |
| alias | String | 别名 |
| type | ViewType | 类型：SQL_VIEW、JAVA_VIEW、BASIC_TABLE，分别代表SQL查询、Java查询、基础表 。以下暂不支持：PROC_VIEW、JS_VIEW、ETL_VIEW、EXCEL_VIEW、COMBINEDQUERY_VIEW、TIME_TABLE |
| define | ObjectNode | 定义，有对应具体的 VO，如基础表为<strong>BasicTableViewDefineVO</strong> |
| storeType | StoreType | 存储方式：DIRECT、EXTRACT，分别代表直连、抽取 |
| extractSetting | ExtractSettingVO | 抽取设置
| parameters | List\<DataModelParameterFieldVO\> | 参数列表定义，支持设置SQL查询中参数的类型及默认值
##### <strong>ExtractSettingVO 抽取设置定义</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ------------------------------------------------ |
| type | ExtractType | 抽取方式：FULL、REALTIME，分别代表全量、按次抽取 |
##### <strong>DataModelParameterFieldVO 参数列表定义</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ------------------------------------------------ |
| name | String | 参数名称 |
| valueType | ValueType | 数据类型：INTEGER、DOUBLE、LONG、STRING等 |
| defaultValue | ObjectNode | 参数类型，可参考案例5中的定义 |
##### <strong>BasicTableViewDefineVO 基础表定义</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | --------------------- |
| tableId | String | 基础表 Id |
##### <strong>SQLViewDefineVO </strong><strong>SQL</strong><strong>查询定义</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | --------------------- |
| dataSourceId | String | 数据源 Id |
| sql | String | sql 语句 |
##### <strong>DataModelMeasureVO 度量</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | --------------------------------------------------- |
| name | String | 名称 |
| alias | String | 别名 |
| aggregator | String | 聚合方式:SUM、COUNT、 AVG、DISTINCT_COUNT、MAX、MIN |
| fieldName | String | 私有查询字段名 |
| viewName | String | 私有查询名 |
##### <strong>DataModelRelationGraphVO 表关系图</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | --------------------- |
| relations: | List\<RelationVO\> | 表关系列表 |
##### <strong>RelationVO 表关系</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | --------------------------------------------------------------------------------------------------------------- |
| srcViewName | String | 名称 |
| destViewName | String | 别名 |
| linkType | TableLinkType | 连接方式：LEFTJOIN、RIGHTJOIN、INNERJOIN、FULLJOIN，分别代表 1 对多、多对 1、1 对 1（内连接）、1 对 1（外连接） |
| fieldRelations | List\<FieldRelationVO\> | 私有查询字段名 |
##### <strong>FieldRelationVO 字段关系</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | --------------------- |
| srcFieldName | String | 源字段名 |
| destFieldName | String | 目标字段名 |
##### <strong>DataModelHierarchyVO 层次结构</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | ---------------------- | --------------------- |
| name | String | 名称 |
| alias | String | 别名 |
| levels | List\<DataModelLevelVO\> | 层次列表 |
##### <strong>DataModelLevelVO 层次</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| name | String | 名称 |
| alias | String | 别名 |
| fieldName | String | 字段名 |
| viewName | String | 私有查询名 |
| levelType | FieldTreeNodeType | 层次类型：LEVEL、LEVEL_TIME_YEAR、LEVEL_TIME_QUARTER、LEVEL_TIME_MONTH、LEVEL_TIME_WEEK、LEVEL_TIME_DAY、LEVEL_GEO，分别代表普通、年、季、月、周、日、地理层次 |
##### <strong>DataModelDateHierarchyVO 基于日期字段生成层次结构</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | ---------------------- | --------------------- |
| name | String | 名称 |
| alias | String | 别名 |
| levels | List\<DataModelLevelVO\> | 层次列表 |
##### <strong>DataModelCalcMeasureVO 计算度量</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ------------------------------------------------------------------ |
| name | String | 名称 |
| alias | String | 别名 |
| expression | String | 表达式 |
| dataFormat | String | 数据格式名，可以在<strong>公共设置-数据格式</strong>中查询需要的名 |
##### <strong>DataModelCalcMemberVO 计算成员</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ---------------------------------------- |
| name | String | 名称 |
| alias | String | 别名 |
| expression | String | 表达式 |
| hierName | String | 层次结构名，如：[ShipRegion] |
| parentName | String | 父成员名：[ShipRegion].[All ShipRegions] |
##### <strong>DataModelNamedSetVO 命名集</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ---------------------------- |
| name | String | 名称 |
| alias | String | 别名 |
| expression | String | 表达式 |
| hierName | String | 层次结构名，如：[ShipRegion] |
##### <strong>DataModelDimensionVO 字段属性</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ------------------------------------------------------------------ |
| viewName | String | 私有查询名 |
| fieldName | String | 字段名 |
| visible | String | 可见性 |
| dataFormat | String | 数据格式名，可以在<strong>公共设置-数据格式</strong>中查询需要的名 |
| maskingRule | String | 脱敏规则 |
| valueType | ValueType | 数据类型：INTEGER、DOUBLE、LONG、STRING 等 |
##### <strong>DataModelParameterVO 字段属性</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ------------------------------------------------------------------ |
| name | String | 私有查询名 |
| alias | String | 字段名 |
| visible | boolean | 可见性 |
| valueType | ValueType | 控件类型：INPUT、INPUT_MULTIPLE、LIST_SINGLE、TREE_SINGLE（树形）等 |
| defaultType | ParamExprType | 默认值类型：SQL、CONSTLIST（静态列表） |
| defaultValue | ObjectNode | 参数默认值，有静态列表与SQL，有ConstiListObjectNode 与 SQLObjectNode |
| standByType | ParamExprType | 备选值类型
| standByValue | ObjectNode | 备选值 |
| rootType | ParamExprType | 根节点类型 |
| rootValue | ObjectNode | 根节点值 |
| refMapping | List\<DataModelParameterMappingVO\> | 映射表参数 |
##### <strong>ConstiListObjectNode字段属性</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| --------------------- | --------------------- | ---------------------------- |
| value | List<List<String>> | value中有多个List，每个List有两个元素，第一个元素为真实值，第二个元素为显示值 |
##### <strong>SQLObjectNode字段属性</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| ---------------- | ------------------- | ---------------------------- |
| SQL | String | value中有多个List，每个List有两个元素，第一个元素为真实值，第二个元素为显示值 |
| nameColumn | String | 真实值字段 |
| aliasColumn | String | 显示值字段 |
| dataSource | String | 查询SQL的数据源 |
| pidColumn | String | 父ID |
| idColumn | String | ID |
| orderBy | List\<OrderByVO\> | 排序信息 |
##### <strong>OrderByVO字段属性</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| ---------------- | ------------------- | ---------------------------- |
| colName | String | 排序字段 |
| type | String | 排序类型：asc、desc |
##### <strong>DataModelParameterMappingVO字段属性</strong>
| <strong>属性</strong> | <strong>类型</strong> | <strong>说明</strong> |
| ---------------- | ------------- | ---------------------------- |
| refViewName | String | 引用参数所在表的名称 |
| refParamName | String | 引用参数名 |
