从表数据集

从表数据集说明

从表数据集定义

需要用某个数据集 b 扩充数据集 a 的字段时，需要创建一个从表数据集 c ，从表数据集 c 的主表是 a，基础表是 b。从表数据集是对一个数据集的引用，只存在于主表的关系中，不可独立使用。

从表数据集结构说明

从表数据集结构和数据集的结构基本相同，详情见数据集结构说明。下表列出从表数据集的特殊结构。

字段	类型	描述
options.extendOptions	OBJECT	从表数据集的引用定义
options.extendOptions.baseId	INTEGER	从表数据集的基础数据集
options.extendOptions.upstreamId	INTEGER	从表数据集的上游数据集，可以是主表或者其它从表数据集
options.extendOptions.joinType	STRING	上游数据集与从表数据集的多表联合关系，见多表联合类型
options.extendOptions.joinExpr	HE	多表联合的关联表达式
options.extendOptions.cardinality	OBJECT	上游数据集对当前数据集的基数关系，见关联关系基数说明
options.extendOptions.isShared	BOOLEAN	是不是共享关联表
options.extendOptionsList	LIST	从表定义的列表，每个成员是独立的 extendOptions。当模型中有共享关联表关联到了多个数据集上，它用来描述所有的关联信息

多表联合类型说明

状态值	意义
LEFT_JOIN	左联接
RIGHT_JOIN	右联接
INNER_JOIN	内联接
FULL_JOIN	全联接
CROSS_JOIN	交叉联接
LEFT_LOOP_JOIN	左嵌套循环联接，sql server 数据集特有的

关联关系基数说明

状态值	意义
ONE_TO_ONE	1 对 1
ONE_TO_MANY	1 对多
MANY_TO_ONE	多对 1
MANY_TO_MANY	多对多

接口说明

创建从表数据集

请求URL

http

POST /api/apps/{appId}/extend-datasets HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

URL 参数

字段	类型	是否必须	说明
appId	INTEGER	是	指定在appId对应的应用中创建数据集

Request Body 参数

见从表数据集结构说明。

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
data	OBJECT	见从表数据集结构说明

接口示例: 创建从表数据集

RequestResponse

http

POST /api/apps/1/extend-datasets HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

// Request Body:
{
	"options":{
		"extendOptions":{
			"baseId":2,
			"upstreamId":1,
			"joinType":"LEFT_JOIN",
			"joinExpr":{"kind":"function","op":"=","args":[{"kind":"field","op":"id","dataset":1},{"kind":"field","op":"id"}]}
		}
	},
	"mainDataset":1
}

http

HTTP/1.1 200 Ok
Content-Type: application/json

{
    "version": "version@9a5e106#6730f0d",
    "data": {
        "id": 3,
        "createdBy": 1,
        "createdAt": "2020-05-21 11:22:58",
        "updatedBy": 1,
        "updatedAt": "2020-05-21 11:22:58",
        "appId": 1,
        "options": {
            "extendOptions": {
                "baseId": 2,
                "upstreamId": 1,
                "joinType": "LEFT_JOIN",
                "joinExpr": {
                    "kind": "function",
                    "op": "=",
                    "args": [
                        {
                            "kind": "field",
                            "op": "id",
                            "dataset": 1
                        },
                        {
                            "kind": "field",
                            "op": "id"
                        }
                    ]
                }
            }
        },
        "datasetAcl": {
            "level": "FULLACCESS",
            "dataFilters": []
        },
        "mainDataset": 1
    }
}

接口示例: 创建共享的从表数据集

RequestResponse

http

POST /api/apps/1/extend-datasets HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

// Request Body:
{
	"options":{
		"extendOptions":{
			"baseId":2,
			"upstreamId":1,
			"joinType":"LEFT_JOIN",
			"isShared": true,
			"joinExpr":{"kind":"function","op":"=","args":[{"kind":"field","op":"id","dataset":1},{"kind":"field","op":"id"}]}
		}
	},
	"mainDataset":1
}

http

HTTP/1.1 200 Ok
Content-Type: application/json

{
    "version": "version@9a5e106#6730f0d",
    "data": {
        "id": 3,
        "createdBy": 1,
        "createdAt": "2020-05-21 11:22:58",
        "updatedBy": 1,
        "updatedAt": "2020-05-21 11:22:58",
        "appId": 1,
        "options": {
            "extendOptions": {
                "baseId": 2,
                "upstreamId": 1,
                "joinType": "LEFT_JOIN",
                "isShared": true,
                "joinExpr": {
                    "kind": "function",
                    "op": "=",
                    "args": [
                        {
                            "kind": "field",
                            "op": "id",
                            "dataset": 1
                        },
                        {
                            "kind": "field",
                            "op": "id"
                        }
                    ]
                }
            }
        },
        "datasetAcl": {
            "level": "FULLACCESS",
            "dataFilters": []
        },
        "mainDataset": 1
    }
}

查询主表数据集的从表数据集列表

请求URL

http

GET /api/apps/{appId}/extend-datasets/{datasetId} HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

URL 参数

字段	类型	是否必须	说明
appId	INTEGER	是	指定在appId对应的应用中创建数据集
datasetId	INTEGER	是	指定主表数据集ID
joinExcept	STRING	否	是否需要从表数据集的schema信息，默认是需要，不需要就传 schema
needMainDataset	BOOL	否	是否需要主表信息，默认是否，需要就传true
needHideField	BOOL	否	是否需要隐藏字段，默认是是，不需要隐藏字段就传否

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
data	OBJECT	见从表数据集结构说明

接口示例: 查询主表数据集的从表数据集列表

RequestResponse

http

GET /api/apps/1/extend-datasets/1?joinExcept=schema&needMainDataset=true HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

http

HTTP/1.1 200 Ok
Content-Type: application/json

{
    "version": "version@9a5e106#6730f0d",
    "data": [
        {
            "id": 3,
            "title": "2",
            "appId": 2613,
            "options": {
                "cache": false,
                "totalSize": 0,
                "rowCount": 0,
                "refreshHours": [],
                "refreshMinute": 0,
                "transpose": false,
                "header": 0,
                "padHeader": false,
                "extendOptions": {
                    "baseId": 2,
                    "upstreamId": 1,
                    "joinType": "LEFT_JOIN",
                    "joinExpr": {
                        "kind": "function",
                        "op": "=",
                        "args": [
                            {
                                "op": "id",
                                "kind": "field",
                                "dataset": 1
                            },
                            {
                                "op": "id",
                                "kind": "field"
                            }
                        ]
                    }
                },
                "metrics": [],
                "schema": []
            },
            "datasetAcl": {
                "level": "FULLACCESS",
                "dataFilters": []
            },
            "hsVersion": 0,
            "mainDataset": 1,
            "importSwitchable": false,
            "refreshSchema": true,
            "emptyDataset": false
        },
        {
            "id": 1,
            "title": "A_IVT_MOVIE",
            "createdBy": 1,
            "createdAt": "2020-05-21 11:02:56",
            "updatedBy": 1,
            "updatedAt": "2020-05-21 11:02:57",
            "visible": true,
            "appId": 2613,
            "options": {
                "cache": false,
                "type": "connection",
                "totalSize": 57404,
                "rowCount": 250,
                "rowCountValid": true,
                "connectionTitle": "250-pg",
                "refreshHours": [],
                "refreshMinute": 0,
                "connectionId": 261,
                "connectionCategory": "Database",
                "origin": "postgresql",
                "table": "A_IVT_MOVIE",
                "path": [
                    "public"
                ],
                "transpose": false,
                "header": 0,
                "padHeader": false,
                "storageType": "postgresql",
                "dialectOptions": {
                    "dialectName": "PostgresqlDialect",
                    "majorVersion": 10,
                    "minorVersion": 4
                },
                "storageConnectionId": 261,
                "storageConnectionTitle": "250-pg",
                "metrics": []
            },
            "importType": 0,
            "importStatus": 0,
            "importOptions": {},
            "status": 3,
            "refreshStats": {
                "refreshAt": "2020-05-21 11:02:57",
                "executeRefreshAt": "2020-05-21 11:02:57",
                "executeRefreshRowCountAt": 1590030177503
            },
            "datasetAcl": {
                "level": "FULLACCESS",
                "dataFilters": []
            },
            "hsVersion": 5,
            "creator": {
                "id": 1,
                "name": "trial",
                "email": "trial@hengshi.io"
            },
            "updater": {
                "id": 1,
                "name": "trial",
                "email": "trial@hengshi.io"
            },
            "importSwitchable": false,
            "type": "connection",
            "refreshSchema": false,
            "origin": "postgresql",
            "public": true,
            "emptyDataset": false
        }
    ]
}

接口示例: 查询包含了共享关联表的从表数据集列表

RequestResponse

http

GET /api/apps/1/extend-datasets/1?joinExcept=schema&needMainDataset=true HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

http

HTTP/1.1 200 Ok
Content-Type: application/json

[
    {
        "id": 4,
        "title": "B",
        "createdBy": 2,
        "createdAt": "2025-03-26 14:47:27",
        "updatedBy": 2,
        "updatedAt": "2025-03-26 14:47:27",
        "visible": true,
        "isDelete": false,
        "appId": 978,
        "options": {
            "cache": false,
            "type": "connection",
            "totalSize": 0,
            "rowCount": 0,
            "refreshHours": [],
            "refreshMinute": 0,
            "origin": "postgresql",
            "transpose": false,
            "padHeader": false,
            "extendOptions": {
                "baseId": 2,
                "upstreamId": 1,
                "joinType": "LEFT_JOIN",
                "joinExpr": {
                    "kind": "function",
                    "op": "and",
                    "args": [
                        {
                            "op": "=",
                            "args": [
                                {
                                    "op": "id",
                                    "kind": "field",
                                    "dataset": 1
                                },
                                {
                                    "op": "id",
                                    "kind": "field"
                                }
                            ],
                            "kind": "function"
                        }
                    ]
                },
                "baseType": "connection",
                "baseOrigin": "postgresql",
                "cardinality": "ONE_TO_ONE",
                "totalMetricsCount": 0
            },
            "hide": false,
            "schema": [
                {
                    "datasetId": 4,
                    "fieldName": "id",
                    "appId": 978,
                    "tags": {},
                    "comment": "",
                    "visible": true,
                    "type": "number",
                    "label": "id",
                    "originType": "integer",
                    "config": {
                        "dialectName": "PostgresqlDialect"
                    },
                    "defaultAggrType": "sum",
                    "basicType": "number",
                    "suggestedTypes": [
                        "number",
                        "string",
                        "date"
                    ],
                    "hideValue": false,
                    "nativeType": "int8",
                    "detectedType": "integer"
                },
                {
                    "datasetId": 4,
                    "fieldName": "zh_name",
                    "appId": 978,
                    "tags": {},
                    "comment": "",
                    "visible": true,
                    "type": "string",
                    "label": "zh_name",
                    "originType": "string",
                    "config": {},
                    "defaultAggrType": "count",
                    "basicType": "string",
                    "suggestedTypes": [
                        "string"
                    ],
                    "hideValue": false,
                    "nativeType": "bpchar",
                    "detectedType": "string"
                }
            ],
            "metrics": [],
            "isFile": false
        },
        "importType": 0,
        "importStatus": 0,
        "importOptions": {
            "importAsMaterializedView": false
        },
        "status": 0,
        "refreshStats": {},
        "isPublic": true,
        "datasetAcl": {
            "level": "FULLACCESS",
            "dataFilters": []
        },
        "mainDataset": 1,
        "fieldGroups": [],
        "metricGroups": [],
        "includeInAppScope": false,
        "jobStatus": "NONE",
        "dataControl": false,
        "canPushToMemory": false,
        "schemaStale": false,
        "dataStale": false,
        "chatWeight": 100,
        "type": "extend",
        "origin": "postgresql",
        "emptyDataset": false
    },
    {
        "id": 6,
        "title": "C",
        "createdBy": 2,
        "createdAt": "2025-03-26 14:47:59",
        "updatedBy": 2,
        "updatedAt": "2025-03-26 14:47:59",
        "visible": true,
        "isDelete": false,
        "appId": 978,
        "options": {
            "cache": false,
            "type": "connection",
            "totalSize": 0,
            "rowCount": 0,
            "origin": "postgresql",
            "transpose": false,
            "padHeader": false,
            "extendOptions": {
                "baseId": 3,
                "upstreamId": 4,
                "joinType": "LEFT_JOIN",
                "joinExpr": {
                    "kind": "function",
                    "op": "and",
                    "args": [
                        {
                            "op": "=",
                            "args": [
                                {
                                    "op": "id",
                                    "kind": "field",
                                    "dataset": 4
                                },
                                {
                                    "op": "id",
                                    "kind": "field"
                                }
                            ],
                            "kind": "function"
                        }
                    ]
                },
                "baseType": "connection",
                "baseOrigin": "postgresql",
                "cardinality": "ONE_TO_ONE",
                "totalMetricsCount": 0,
                "isShared": true
            },
            "extendOptionsList": [
                {
                    "baseId": 3,
                    "upstreamId": 4,
                    "joinType": "LEFT_JOIN",
                    "joinExpr": {
                        "kind": "function",
                        "op": "and",
                        "args": [
                            {
                                "op": "=",
                                "args": [
                                    {
                                        "op": "id",
                                        "kind": "field",
                                        "dataset": 4
                                    },
                                    {
                                        "op": "id",
                                        "kind": "field"
                                    }
                                ],
                                "kind": "function"
                            }
                        ]
                    },
                    "baseType": "connection",
                    "baseOrigin": "postgresql",
                    "cardinality": "ONE_TO_ONE",
                    "totalMetricsCount": 0,
                    "isShared": true
                },
                {
                    "baseId": 3,
                    "upstreamId": 1,
                    "joinType": "LEFT_JOIN",
                    "joinExpr": {
                        "kind": "function",
                        "op": "and",
                        "args": [
                            {
                                "op": "=",
                                "args": [
                                    {
                                        "op": "id",
                                        "kind": "field",
                                        "dataset": 1
                                    },
                                    {
                                        "op": "id",
                                        "kind": "field"
                                    }
                                ],
                                "kind": "function"
                            }
                        ]
                    },
                    "baseType": "connection",
                    "baseOrigin": "postgresql",
                    "cardinality": "ONE_TO_ONE",
                    "totalMetricsCount": 0,
                    "shadowId": 6,
                    "isShared": true
                }
            ],
            "hide": false,
            "schema": [
                {
                    "datasetId": 6,
                    "fieldName": "id",
                    "appId": 978,
                    "tags": {},
                    "comment": "",
                    "visible": true,
                    "type": "number",
                    "label": "id",
                    "originType": "integer",
                    "config": {
                        "dialectName": "PostgresqlDialect"
                    },
                    "defaultAggrType": "sum",
                    "basicType": "number",
                    "suggestedTypes": [
                        "number",
                        "string",
                        "date"
                    ],
                    "hideValue": false,
                    "nativeType": "int8",
                    "detectedType": "integer"
                },
                {
                    "datasetId": 6,
                    "fieldName": "zh_name",
                    "appId": 978,
                    "tags": {},
                    "comment": "",
                    "visible": true,
                    "type": "string",
                    "label": "zh_name",
                    "originType": "string",
                    "config": {},
                    "defaultAggrType": "count",
                    "basicType": "string",
                    "suggestedTypes": [
                        "string"
                    ],
                    "hideValue": false,
                    "nativeType": "bpchar",
                    "detectedType": "string"
                }
            ],
            "metrics": [],
            "isFile": false
        },
        "importType": 0,
        "importStatus": 0,
        "importOptions": {
            "importAsMaterializedView": false
        },
        "status": 0,
        "refreshStats": {},
        "isPublic": true,
        "datasetAcl": {
            "level": "FULLACCESS",
            "dataFilters": []
        },
        "mainDataset": 1,
        "fieldGroups": [],
        "metricGroups": [],
        "includeInAppScope": false,
        "jobStatus": "NONE",
        "dataControl": false,
        "canPushToMemory": false,
        "schemaStale": false,
        "dataStale": false,
        "chatWeight": 100,
        "type": "extend",
        "origin": "postgresql",
        "emptyDataset": false
    },
    {
        "id": 1,
        "title": "A",
        "createdBy": 2,
        "createdAt": "2025-03-26 14:47:02",
        "updatedBy": 2,
        "updatedAt": "2025-03-26 14:47:02",
        "visible": true,
        "isDelete": false,
        "appId": 978,
        "options": {
            "cache": false,
            "type": "connection",
            "totalSize": 59734,
            "rowCount": 250,
            "rowCountValid": true,
            "connectionTitle": "pg54112",
            "refreshHours": [],
            "refreshMinute": 0,
            "connectionId": 4,
            "connectionCategory": "Database",
            "origin": "postgresql",
            "table": "A_IVT_MOVIE",
            "path": [
                "public"
            ],
            "transpose": false,
            "padHeader": false,
            "storageType": "postgresql",
            "dialectOptions": {
                "dialectName": "PostgresqlDialect",
                "majorVersion": 11,
                "minorVersion": 5
            },
            "storageConnectionId": 4,
            "hide": false,
            "schema": [
                {
                    "datasetId": 1,
                    "fieldName": "id",
                    "appId": 978,
                    "tags": {},
                    "comment": "",
                    "visible": true,
                    "type": "number",
                    "label": "id",
                    "originType": "integer",
                    "config": {
                        "dialectName": "PostgresqlDialect"
                    },
                    "defaultAggrType": "sum",
                    "basicType": "number",
                    "suggestedTypes": [
                        "number",
                        "string",
                        "date"
                    ],
                    "hideValue": false,
                    "nativeType": "int8",
                    "detectedType": "integer"
                },
                {
                    "datasetId": 1,
                    "fieldName": "zh_name",
                    "appId": 978,
                    "tags": {},
                    "comment": "",
                    "visible": true,
                    "type": "string",
                    "label": "zh_name",
                    "originType": "string",
                    "config": {},
                    "defaultAggrType": "count",
                    "basicType": "string",
                    "suggestedTypes": [
                        "string"
                    ],
                    "hideValue": false,
                    "nativeType": "bpchar",
                    "detectedType": "string"
                }
            ],
            "metrics": [],
            "isFile": false
        },
        "importType": 0,
        "importStatus": 0,
        "importOptions": {
            "importAsMaterializedView": false
        },
        "status": 3,
        "isPublic": true,
        "datasetAcl": {
            "level": "FULLACCESS",
            "dataFilters": []
        },
        "dataControl": false,
        "canPushToMemory": false,
        "type": "connection",
        "origin": "postgresql",
        "emptyDataset": false
    }
]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423

编辑从表数据集

请求URL

http

PUT /api/apps/{appId}/extend-datasets/{datasetId} HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

URL 参数

字段	类型	是否必须	说明
appId	INTEGER	是	指定从表数据集所在的 appId
datasetId	INTEGER	是	指定从表数据集的 id

Request Body 参数

见从表数据集结构说明。

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
data	OBJECT	从表数据集的id

接口示例: 编辑从表数据集

RequestResponse

http

PUT /api/apps/1/extend-datasets/3 HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

// Request Body:
{
	"options":{
		"extendOptions":{
			"baseId":2,
			"upstreamId":1,
			"joinType":"LEFT_JOIN",
			"joinExpr":{"kind":"function","op":"=","args":[{"kind":"field","op":"id","dataset":1},{"kind":"field","op":"id"}]}
		}
	},
	"mainDataset":1,
	"title":"电影1"
}

http

HTTP/1.1 200 Ok
Content-Type: application/json

{
    "version": "version@9a5e106#6730f0d",
    "data": 3
}

删除从表数据集

请求URL

http

DELETE /api/apps/{appId}/extend-datasets/{datasetId} HTTP/1.1
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

URL 参数

字段	类型	是否必须	说明
appId	INTEGER	是	指定从表数据集所在的 appId
datasetId	INTEGER	是	指定从表数据集的 id

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
data	OBJECT	成功或者失败

接口示例: 删除从表数据集

RequestResponse

http

DELETE /api/apps/1/extend-datasets/{datasetId} HTTP/1.1

http

HTTP/1.1 200 Ok
Content-Type: application/json

{
    "version": "version@9a5e106#6730f0d",
    "code": 0,
    "msg": "success"
}

从表数据集 ​