数据连接

数据连接说明

进行数据分析的第一步即获取数据，获取数据的第一步即定义数据连接。数据连接是衡石系统连接用户数据库的途径。

数据连接的类型

衡石系统支持多种数据源的多种版本，详情见数据源版本支持

数据连接的结构说明

字段	类型	描述
id	LONG	权限条目的 id
title	STRING	权限条目的标题
accessCount	INTEGER	访问次数
tenantId	LONG	数据连接所属的租户
options	OBJECT	数据连接的配置信息
options.username	STRING	连接数据源的用户名
options.password	STRING	连接数据源的用户密码
options.host	STRING	数据源的机器名或者机器IP
options.port	INTEGER	数据源的端口号
options.encoding	STRING	连接数据源的编码
options.type	STRING	数据源的类型
options.category	STRING	数据源所属的分类
options.database	STRING	数据库的名字
options.maxConnNum	INTEGER	最大连接数
options.outputAble	BOOL	是否可以用于数据集成的输出
options.fileOutputPath	数组	可用于上传文件的指定路径

Mysql 数据源说明

Category: Database

Type: mysql

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 3306
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "encoding": {
    "name": "编码",
    "optionList": [
      "UTF-8",
      "GBK",
      "Big5",
      "EUC_CN",
      "Cp1252",
      "UnicodeBig"
    ]
  },
  "supportWrite": true,
  "mysql8OnAzure": {
    "name": "使用Azure MySQL 8.0.15",
    "required": false,
    "message": "由于Azure MySQL 8.0.15存在版本号传递错误的bug(<a href=\"https://docs.microsoft.com/zh-cn/azure/mysql/concepts-limits\" target=\"_blank\">官方说明</a>)，若当前您需要连接Azure上的该版本Mysql，请将该参数设置为'开启'。",
    "optionList": [
      "false",
      "true"
    ]
  }
}

Tidb 数据源说明

Category: NoSQL / NewSQL

Type: tidb

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 4000
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "encoding": {
    "name": "编码",
    "optionList": [
      "UTF-8",
      "GBK",
      "Big5",
      "EUC_CN",
      "Cp1252",
      "UnicodeBig"
    ]
  }
}

PostgreSQL 数据源说明

Category: Database

Type: postgresql

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 5432
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "encoding": {
    "name": "编码",
    "optionList": [
      "UTF-8",
      "GBK",
      "Big5",
      "EUC_CN",
      "Cp1252",
      "UnicodeBig"
    ]
  },
  "supportWrite": true
}

Greenplum 数据源说明

Category: Database

Type: greenplum

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 5432
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "encoding": {
    "name": "编码",
    "optionList": [
      "UTF-8",
      "GBK",
      "Big5",
      "EUC_CN",
      "Cp1252",
      "UnicodeBig"
    ]
  },
  "supportWrite": true
}

Oracle 数据源说明

Category: Database

Type: oracle

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 1521
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  }
}

DB2 数据源说明

Category: Database

Type: db2

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 50000
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "schema": {
    "name": "模式",
    "required": false
  }
}

Hive 数据源说明

Category: SQL on Hadoop

Type: hive

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 10000
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true,
    "dlefault": "default",
    "default": "default"
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "hiveExecutionEngine": {
    "name": "hive 执行引擎",
    "required": true,
    "optionList": [
      "mr",
      "tez",
      "spark"
    ]
  },
  "hadoopAuthentication": {
    "name": "hadoop 认证方式",
    "required": true,
    "optionList": [
      "simple",
      "kerberos",
      "tbds"
    ]
  },
  "kerberosRealm": {
    "name": "realm",
    "required": true
  },
  "kerberosKdc": {
    "name": "kdc",
    "required": true
  },
  "serverPrincipal": {
    "name": "server principal",
    "required": true
  }
}

Spark SQL 数据源说明

Category: SQL on Hadoop

Type: spark_sql

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 10001
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true,
    "dlefault": "default",
    "default": "default"
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "encoding": {
    "name": "编码",
    "optionList": [
      "UTF-8",
      "GBK",
      "Big5",
      "EUC_CN",
      "Cp1252",
      "UnicodeBig"
    ]
  }
}

微软 SQL Server 数据源说明

Category: Database

Type: sqlserver

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 1433
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  }
}

Cloudera Impala 数据源说明

Category: SQL on Hadoop

Type: cloudera_impala

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 21050
  },
  "username": {
    "name": "用户名",
    "required": false
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true,
    "dlefault": "default",
    "default": "default"
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "encoding": {
    "name": "编码",
    "optionList": [
      "UTF-8",
      "GBK",
      "Big5",
      "EUC_CN",
      "Cp1252",
      "UnicodeBig"
    ]
  }
}

MongoDB 数据源说明

Category: NoSQL / NewSQL

Type: mongodb

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 3307
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "schema": {
    "name": "认证数据库",
    "required": false
  }
}

麒麟企业版数据源说明

Category: SQL on Hadoop

Type: kap

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 7070
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "ssl": {
    "name": "ssl",
    "required": true,
    "optionList": [
      "false",
      "true"
    ]
  }
}

Amazon Redshift 数据源说明

Category: Cloud

Type: amazon_redshift

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 5439
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  },
  "encoding": {
    "name": "编码",
    "optionList": [
      "UTF-8",
      "GBK",
      "Big5",
      "EUC_CN",
      "Cp1252",
      "UnicodeBig"
    ]
  },
  "ssl": {
    "name": "ssl",
    "required": true,
    "optionList": [
      "false",
      "true"
    ]
  },
  "supportWrite": true
}

Alibaba Maxcompute 数据源说明

Category: Cloud

Type: alibaba_maxcompute

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 443
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  }
}

Presto 数据源说明

Category: SQL on Hadoop

Type: presto

连接属性说明：

json

{
  "host": {
    "name": "机器地址",
    "required": true
  },
  "port": {
    "name": "端口",
    "required": true,
    "default": 8080
  },
  "username": {
    "name": "用户名",
    "required": true
  },
  "password": {
    "name": "密码"
  },
  "catalog": {
    "name": "Catalog",
    "required": true
  },
  "database": {
    "name": "数据库",
    "required": true
  },
  "maxConnNum": {
    "name": "最大连接数",
    "required": true,
    "default": 10
  }
}

数据源内的数据表结构说明

字段	类型	描述
pathType	STRING	该节点的类型，path 是路径，table 是表
name	STRING	节点名
tableType	STRING	table 是表， view 是视图
children	数组	下游节点的数组，每个节点的描述结构和该节点相同
priority	STRING	权限条目的权限级别, 详情见priority值说明
hasAuth	BOOL	标记当前节点上是否设置过链接权限
ignoreNewlyCreatedTable	BOOL	是否忽略后续新增的表，用于批量同步任务配置
inputStrategy	STRING	当前表的同步策略，用于批量同步任务配置，详见数据集成的“输入节点的输入策略”部分
incrementalField	STRING 数组	当前表的增量字段，仅inputStrategy是增量时生效
keyFields	STRING 数组	当前表同步时的键字段字段，仅inputStrategy是增量时生效
includeStatus	STRING	节点包含状态，见包含状态
isDirExpand	BOOL	当前节点是否处于展开状态
createTableProperties	STRING	当作输出表时的建表属性

包含状态

值	说明
ALL	目录子项目全部被包含，对于table节点，ALL表示包含本节点
NONE	目录子项目没有一个被包含，对于table节点，NONE表示不包含本节点
SOME	目录子项目至少有一个被包含，但是不是全部包含，对于table节点，SOME不适用

预览数据源中数据表的结构

预览

字段	类型	描述
where	OBJECT 数组	过滤条件
customSql	STRING	自定义查询语句
table	STRING	要预览的表名
path	STRING 数组	要预览的表所在的路径数组

接口说明

验证数据连接

请求URL

http

POST /api/connections/verify HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

URL 参数

无

Request Body 参数

参照上述各数据源的连接属性说明

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
msg	OBJECT	请求成功返回 success

接口示例1

RequestResponse

http

POST /api/connections/verify HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

// Request Body:
{
   "type": "postgresql",
   "category": "Database",
   "title": "测试连接",
   "host": "***",
   "port": ***,
   "username": "***",
   "password": "***",
   "database": "***",
   "maxConnNum": 10,
   "encoding": "UTF-8"
}

http

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

{
   "version": "version@9a5e106#6730f0d",
   "code": 0,
   "msg": "success",
   "data": {
      "dbMajorVersion": 10,
      "dbMinorVersion": 4,
      "dbProductName": "PostgreSQL",
      "dbProductVersion": "10.4 (Debian 10.4-2.pgdg90+1)"
   }
}

新增数据连接

请求URL

http

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

请求参数

Request Body 参数

参照上述各数据源的连接属性说明

返回对象的格式说明

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

接口示例1 新建 Postgres 数据连接，而且此数据连接可以用于数据集成的输出,并且指定了文件上传路径

RequestResponse

http

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

// Request Body:
{
   "title": "apitestcoo",
   "type": "postgresql",
   "category": "Database",
   "host": "192.168.2.250",
   "port": 5410,
   "username": "postgres",
   "password": "postgres",
   "database": "postgres",
   "maxConnNum": 10,
   "encoding": "UTF-8",
   "outputAble": true,
   "fileOutputPath": [
      "test"
   ]
}

http

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

{
   "version": "version@9a5e106#6730f0d",
   "code": 0,
   "msg": "success",
   "data": {
      "id": 3261,
      "options": {
         "username": "***",
         "password": "***",
         "host": "***",
         "port": ***,
         "encoding": "UTF-8",
         "type": "postgresql",
         "database": "***",
         "maxConnNum": 10,
         "config": {},
         "category": "Database",
         "protocol": "http",
         "outputAble": true
      },
      "createdBy": 6,
      "createdAt": "2020-06-03 15:20:02",
      "updatedBy": 6,
      "updatedAt": "2020-06-03 15:20:02",
      "visible": true,
      "title": "apitestcoo"
   }
}

分页查询数据连接

请求URL

http

GET /api/connections HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

字段	类型	是否必须	说明
orderBy	STRING	否	排序字段
connectionType	STRING	否	数据连接的类型，见上文各类型说明中的 Type
auth	BOOL	否	只列出我被授权的
createdByCurrent	BOOL	否	只列出我创建的

返回对象的格式说明

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

接口示例1 分页查询数据连接

RequestResponse

http

GET /api/connections?createdByCurrent=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",
   "code": 0,
   "msg": "success",
   "data": [
      {
         "id": 3261,
         "options": {
            "username": "***",
            "password": "***",
            "host": "***",
            "port": ***,
            "encoding": "UTF-8",
            "type": "postgresql",
            "database": "***",
            "maxConnNum": 10,
            "config": {},
            "category": "Database",
            "protocol": "http",
            "outputAble": true,
            "metaConfig": {
               "presetVersion": "3c4e177b4b7d3fb81e3de9da2428d59c",
               "presetDbVersion": "default",
               "dbMajorVersion": 10,
               "dbMinorVersion": 4,
               "dbProductName": "PostgreSQL",
               "dbProductVersion": "10.4 (Debian 10.4-2.pgdg90+1)"
            }
         },
         "createdBy": 6,
         "createdAt": "2020-06-03 15:20:02",
         "updatedBy": 6,
         "updatedAt": "2020-06-03 15:20:02",
         "visible": true,
         "title": "apitestcoo"
      }
   ]
}

根据ID查询数据连接

请求URL

http

GET /api/connections/{connectionId} HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

字段	类型	说明
connectionId	INTEGER	数据连接的 id
queryFunctions	BOOL	返回结果是否包含数据连接支持的functions信息

返回对象的格式说明

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

接口示例1 查询 id 为 1 的数据连接

RequestResponse

http

GET /api/connections/1 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",
   "code": 0,
   "msg": "success",
   "data": {
      "id": 3261,
      "options": {
         "username": "***",
         "password": "***",
         "host": "***",
         "port": ***,
         "encoding": "UTF-8",
         "type": "postgresql",
         "database": "***",
         "maxConnNum": 10,
         "config": {},
         "category": "Database",
         "protocol": "http",
         "outputAble": true
      },
      "createdBy": 6,
      "createdAt": "2020-06-03 15:20:02",
      "updatedBy": 6,
      "updatedAt": "2020-06-03 15:20:02",
      "visible": true,
      "title": "apitestcoo"
   }
}

更新已有数据连接的信息

请求URL

http

PUT /api/connections/{connectionId} HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

字段	类型	说明
connectionId	INTEGER	数据连接的 id

Request Body 参数

参照上述各数据源的连接属性说明

返回对象的格式说明

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

接口示例1 更新 id 为 1 的数据连接，不允许上传文件到该数据连接

RequestResponse

http

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

// Request Body:
{
   "title": "apitestcoo",
   "type": "postgresql",
   "category": "Database",
   "host": "192.168.2.250",
   "port": 5410,
   "username": "postgres",
   "password": "postgres",
   "database": "postgres",
   "maxConnNum": "10",
   "encoding": "UTF-8",
   "outputAble": true,
   "fileOutputPath": null
}

http

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

{
   "version": "version@9a5e106#6730f0d",
   "code": 0,
   "msg": "success",
   "data": {
      "id": 3261,
      "options": {
         "username": "***",
         "password": "***",
         "host": "***",
         "port": ***,
         "encoding": "UTF-8",
         "type": "postgresql",
         "database": "***",
         "maxConnNum": 10,
         "config": {},
         "category": "Database",
         "protocol": "http",
         "outputAble": true
      },
      "createdBy": 6,
      "createdAt": "2020-06-03 15:20:02",
      "updatedBy": 6,
      "updatedAt": "2020-06-03 15:20:02",
      "visible": true,
      "title": "apitestcoo"
   }
}

删除已有数据连接

请求URL

http

DELETE /api/connections/{connectionId} HTTP/1.1

请求参数

字段	类型	说明
connectionId	INTEGER	数据连接的 id

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
msg	STRING	成功返回 success

根据id查询所有路径的所有table

请求URL

http

GET /api/connections/{connectionId}/path-tables HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

字段	类型	是否必须	说明
connectionId	INTEGER	是	数据连接的 id
pathOnly	BOOL	否	数据连接的 id
targetType	STRING	否	user 表示按用户查看；organization 表示按组织查看
targetId	INTEGER	否	指定的访问者用户或者组织的 id

返回对象的格式说明

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

接口示例1 更新 id 为 1 的数据连接，不允许上传文件到该数据连接

RequestResponse

http

GET /api/connections/1/path-tables 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",
   "code": 0,
   "msg": "success",
   "data": [
      {
         "pathType": "path",
         "name": "default",
         "children": [
            {
               "pathType": "table",
               "name": "a_ivt_audit_heis",
               "tableType": "TABLE"
            }
         ]
      },
      {
         "pathType": "path",
         "name": "test",
         "children": [
            {
               "pathType": "table",
               "name": "mytest",
               "tableType": "TABLE"
            }
         ]
      }
   ]
}

查询table中的数据

请求URL

http

POST /api/connections/{connectionId}/path-table-data HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

字段	类型	是否必须	说明
connectionId	INTEGER	是	数据连接的 id
orderBy	STRING	否	排序字段
orderType	STRING	否	排序的方向
targetType	STRING	否	user 表示按用户查看；organization 表示按组织查看
targetId	INTEGER	否	指定的访问者用户或者组织的 id

request body 请求体

见预览数据源中数据表的结构

返回对象的格式说明

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

接口示例1 查询数据连接中 a_ivt_audit_heis 的数据

RequestResponse

http

POST /api/connections/1/path-table-data HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

// Request Body:
{
   "table": "a_ivt_audit_heis",
   "path": [
      "default"
   ]
}

http

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

{
   "version": "version@9a5e106#6730f0d",
   "code": 0,
   "msg": "success",
   "data": {
      "data": [
         [
            1,
            "北京市海淀区职工大学",
            "4211050001",
            "北京市",
            "北京市",
            ""
         ]
      ],
      "schema": [
         {
            "fieldName": "id",
            "originType": "integer",
            "config": {
               "dialectName": "ImpalaDialect"
            },
            "type": "number",
            "basicType": "number",
            "defaultAggrType": "sum",
            "visible": true,
            "nativeType": "BIGINT",
            "suggestedTypes": [
               "number",
               "string"
            ],
            "detectedType": "integer"
         },
         {
            "fieldName": "name",
            "originType": "string",
            "config": {},
            "type": "string",
            "basicType": "string",
            "defaultAggrType": "count",
            "visible": true,
            "nativeType": "STRING",
            "suggestedTypes": [
               "string"
            ],
            "detectedType": "string"
         },
         {
            "fieldName": "code",
            "originType": "string",
            "config": {
               "seperator": " ",
               "dialectName": "ImpalaDialect"
            },
            "type": "string",
            "basicType": "string",
            "defaultAggrType": "count",
            "visible": true,
            "nativeType": "STRING",
            "suggestedTypes": [
               "number",
               "string"
            ],
            "detectedType": "number"
         },
         {
            "fieldName": "department",
            "originType": "string",
            "config": {},
            "type": "string",
            "basicType": "string",
            "defaultAggrType": "count",
            "visible": true,
            "nativeType": "STRING",
            "suggestedTypes": [
               "string"
            ],
            "detectedType": "string"
         },
         {
            "fieldName": "province",
            "originType": "string",
            "config": {},
            "type": "string",
            "basicType": "string",
            "defaultAggrType": "count",
            "visible": true,
            "nativeType": "STRING",
            "suggestedTypes": [
               "string"
            ],
            "detectedType": "string"
         },
         {
            "fieldName": "comment",
            "originType": "string",
            "config": {},
            "type": "string",
            "basicType": "string",
            "defaultAggrType": "count",
            "visible": true,
            "nativeType": "STRING",
            "suggestedTypes": [
               "string"
            ],
            "detectedType": "string"
         }
      ],
      "pagable": false,
      "importSwitchable": false,
      "randomable": false
   }
}

在连接中执行sql并返回数据

请求URL

http

POST /api/connections/{connectionId}/path-sql-data HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

URL 参数

字段	类型	是否必须	说明
connectionId	INTEGER	是	数据连接 id

Request Body 参数

字段	类型	说明
customSql	STRING	要执行的sql
path	STRING 数组	数据库schema路径，不同数据源类型的路径长度不同

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
data	OBJECT	sql执行返回的数据，格式参见数据集文档中关于datasetResultDto 说明

参考示例

RequestResponse

http

POST /api/connections/1/path-sql-data HTTP/1.1
Content-Type: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

// Request Body:
{
   "customSql": "select * from movie limit 1",
   "path": [
      "public"
   ]
}

http

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

{
   "version": "version@9a5e106#6730f0d",
   "data": {
      "data": [
         [
            36,
            "海洋",
            " 雅克·克鲁奥德",
            "纪录片",
            104,
            "9",
            "90803",
            "皮尔斯·布鲁斯南",
            "",
            "2011-08-12",
            2009,
            8,
            12
         ]
      ],
      "schema": [
         {
            "fieldName": "id",
            "type": "number",
            "config": {
               "dialectName": "PostgresqlDialect"
            },
            "defaultAggrType": "sum",
            "suggestedTypes": [
               "number",
               "string"
            ],
            "originType": "integer",
            "visible": true,
            "basicType": "number",
            "detectedType": "integer",
            "nativeType": "bigserial"
         },
         {
            "fieldName": "zh_name",
            "type": "string",
            "config": {},
            "defaultAggrType": "count",
            "suggestedTypes": [
               "string"
            ],
            "originType": "string",
            "visible": true,
            "basicType": "string",
            "detectedType": "string",
            "nativeType": "varchar"
         },
         {
            "fieldName": "director",
            "type": "string",
            "config": {},
            "defaultAggrType": "count",
            "suggestedTypes": [
               "string"
            ],
            "originType": "string",
            "visible": true,
            "basicType": "string",
            "detectedType": "string",
            "nativeType": "varchar"
         },
         {
            "fieldName": "prime_genre",
            "type": "string",
            "config": {},
            "defaultAggrType": "count",
            "suggestedTypes": [
               "string"
            ],
            "originType": "string",
            "visible": true,
            "basicType": "string",
            "detectedType": "string",
            "nativeType": "varchar"
         },
         {
            "fieldName": "runtime",
            "type": "number",
            "config": {
               "dialectName": "PostgresqlDialect"
            },
            "defaultAggrType": "sum",
            "suggestedTypes": [
               "number",
               "string"
            ],
            "originType": "integer",
            "visible": true,
            "basicType": "number",
            "detectedType": "integer",
            "nativeType": "int8"
         },
         {
            "fieldName": "rate_num",
            "type": "string",
            "config": {
               "seperator": " ",
               "dialectName": "PostgresqlDialect"
            },
            "defaultAggrType": "count",
            "suggestedTypes": [
               "number",
               "string"
            ],
            "originType": "string",
            "visible": true,
            "basicType": "string",
            "detectedType": "number",
            "nativeType": "varchar"
         },
         {
            "fieldName": "votes",
            "type": "string",
            "config": {
               "seperator": " ",
               "dialectName": "PostgresqlDialect"
            },
            "defaultAggrType": "count",
            "suggestedTypes": [
               "number",
               "string"
            ],
            "originType": "string",
            "visible": true,
            "basicType": "string",
            "detectedType": "number",
            "nativeType": "varchar"
         },
         {
            "fieldName": "stars",
            "type": "string",
            "config": {},
            "defaultAggrType": "count",
            "suggestedTypes": [
               "string"
            ],
            "originType": "string",
            "visible": true,
            "basicType": "string",
            "detectedType": "string",
            "nativeType": "varchar"
         },
         {
            "fieldName": "tags",
            "type": "string",
            "config": {},
            "defaultAggrType": "count",
            "suggestedTypes": [
               "string"
            ],
            "originType": "string",
            "visible": true,
            "basicType": "string",
            "detectedType": "string",
            "nativeType": "varchar"
         },
         {
            "fieldName": "pubdate",
            "type": "date",
            "config": {
               "dialectName": "PostgresqlDialect"
            },
            "defaultAggrType": "year",
            "suggestedTypes": [
               "date",
               "string"
            ],
            "originType": "date",
            "visible": true,
            "basicType": "date",
            "detectedType": "date",
            "nativeType": "date"
         },
         {
            "fieldName": "pubyear",
            "type": "number",
            "config": {
               "dateFormat": "yyyy",
               "dialectName": "PostgresqlDialect"
            },
            "defaultAggrType": "sum",
            "suggestedTypes": [
               "number",
               "string",
               "date"
            ],
            "originType": "integer",
            "visible": true,
            "basicType": "number",
            "detectedType": "integer",
            "nativeType": "int8"
         },
         {
            "fieldName": "month",
            "type": "number",
            "config": {
               "dialectName": "PostgresqlDialect"
            },
            "defaultAggrType": "sum",
            "suggestedTypes": [
               "number",
               "string"
            ],
            "originType": "integer",
            "visible": true,
            "basicType": "number",
            "detectedType": "integer",
            "nativeType": "int8"
         },
         {
            "fieldName": "day",
            "type": "number",
            "config": {
               "dialectName": "PostgresqlDialect"
            },
            "defaultAggrType": "sum",
            "suggestedTypes": [
               "number",
               "string"
            ],
            "originType": "integer",
            "visible": true,
            "basicType": "number",
            "detectedType": "integer",
            "nativeType": "int8"
         }
      ],
      "importSwitchable": true
   }
}

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

获取系统支持的连接类型和相关配置

请求URL

http

GET /api/connections/support-types HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

URL 参数

Request Body 参数

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
data	OBJECT数组	支持的连接类型和配置要求
data[].id	STRING	连接类型唯一标识
data[].name	STRING	连接类型名称
data[].logo	STRING	连接类型的logo id
data[].category	STRING	连接类型分组
data[].setup	OBJECT	连接需要的配置项
data[].setup.host	OBJECT	主机地址
data[].setup.host.name	STRING	前端显示的项目名字，通用，其他配置项也有可能该字段
data[].setup.host.required	BOOLEAN	配置项是否是必须的，通用，其他配置项也有可能该字段
data[].setup.port	OBJECT	端口
data[].setup.port.default	OBJECT	前端默认填写的值，通用，其他配置项也有可能该字段
data[].setup.username	OBJECT	用户名
data[].setup.password	OBJECT	密码
data[].setup.database	OBJECT	连接默认用的数据库
data[].setup.maxConnNum	OBJECT	连接池最大连接数
data[].setup.encoding	OBJECT	连接编码
data[].setup.encoding.optionList	STRING数组	前端展示的可选编码列表
data[].setup.supportWrite	BOOLEAN	是否支持文件和数据集成输出

参考示例

RequestResponse

http

GET /api/connections/support-types 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": "postgresql",
         "name": "PostgreSQL",
         "logo": "postgresql",
         "category": "Database",
         "setup": {
            "host": {
               "name": "机器地址",
               "required": false
            },
            "port": {
               "name": "端口",
               "required": false,
               "default": 5432
            },
            "username": {
               "name": "用户名",
               "required": true
            },
            "password": {
               "name": "密码"
            },
            "database": {
               "name": "数据库",
               "required": false
            },
            "schema": {
               "name": "模式",
               "required": false
            },
            "onlyShowSpecifiedSchema": {
               "name": "只显示指定数据库/模式下的表",
               "required": false,
               "type": "checkbox",
               "default": true
            },
            "maxConnNum": {
               "name": "最大连接数",
               "required": true,
               "default": 10
            },
            "encoding": {
               "name": "编码",
               "optionList": [
                  "UTF-8",
                  "GBK",
                  "Big5",
                  "EUC_CN",
                  "Cp1252",
                  "UnicodeBig"
               ]
            },
            "ssl": {
               "name": "ssl",
               "required": false,
               "optionList": [
                  "false",
                  "true"
               ]
            },
            "preferCommentTitle": {
               "name": "优先使用数据库comment做数据集的标题",
               "required": false,
               "optionList": [
                  "false",
                  "true"
               ]
            },
            "sslMode": {
               "name": "sslmode",
               "required": false,
               "optionList": [
                  "disable",
                  "allow",
                  "prefer",
                  "require",
                  "verify-ca",
                  "verify-full"
               ]
            },
            "pgLogicalDecodingOutputPlugin": {
               "name": "Logical Decoding Output Plugin",
               "required": false,
               "optionList": [
                  "decoderbufs",
                  "wal2json",
                  "pgoutput"
               ]
            },
            "supportWrite": true,
            "dataGateway": {
               "name": "数据网关",
               "required": true,
               "optionJsonList": [
                  {}
               ]
            },
            "url": {
               "name": "URL",
               "required": false
            },
            "readIsolation": {
               "name": "读取行为的事务隔离级别",
               "required": false,
               "optionList": [
                  "",
                  "READ_UNCOMMITTED",
                  "READ_COMMITTED",
                  "REPEATABLE_READ",
                  "SERIALIZABLE"
               ]
            }
         },
         "presetCodeMap": {
            "14": "preset code...",
            "default": "preset code..."
         }
      },
      ...
   ]
}

获取指定数据连接对应数据源类型的标识符转义字符

请求URL

http

GET /api/connections/identifier-quote-char HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

请求参数

URL 参数

字段	类型	说明
types	ARRAY	数据连接类型列表

Request Body 参数

返回对象的格式说明

字段	类型	说明
version	STRING	当前系统版本哈希值
data	OBJECT	是一个连接类型和转义字符的map，map的key是连接类型，value是一个map，有两个key，left表示左边转义字符，right表示右边转义字符

参考示例

RequestResponse

http

GET /api/connections/identifier-quote-char?types=mysql,postgresql,sqlserver 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": "5.4-SNAPSHOT@@git.commit.id.abbrev@#a98c540",
    "code": 0,
    "msg": "success",
    "data": {
        "postgresql": {
            "left": "\"",
            "right": "\""
        },
        "sqlserver": {
            "left": "[",
            "right": "]"
        },
        "mysql": {
            "left": "`",
            "right": "`"
        }
    },
    "chartDataStartTimeMillis": 0
}

执行presets

请求参数

名称	位置	类型	必选	说明
connectionId	query	long	否	连接id，如果该参数为空则认为是新链接
body	body	object	否	none

RequestResponse

http

POST /api/connections/run-presets HTTP/1.1
Accept: application/json
Cookie: csrf=183f1c4...; sid=26ee552d...; _USER_SESSION_ID=f2a01083...

// Request Body:
{
   "type": "postgresql",
   "category": "Database",
   "title": "54327",
   "host": "localhost",
   "port": "54327",
   "username": "postgres",
   "password": "postgres",
   "database": "postgres",
   "schema": "",
   "maxConnNum": "10",
   "encoding": "UTF-8",
   "ssl": "false",
   "preferCommentTitle": "false",
   "sslMode": "disable",
   "pgLogicalDecodingOutputPlugin": "decoderbufs",
   "dataGateway": null,
   "url": "",
   "readIsolation": "",
   "onlyShowSpecifiedSchema": false,
   "metaConfig": {
      "presetDbVersion": "default"
   }
}

http

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

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

数据连接 ​

数据连接说明 ​

数据连接的类型 ​

数据连接的结构说明 ​

Mysql 数据源说明 ​

Tidb 数据源说明 ​

PostgreSQL 数据源说明 ​

Greenplum 数据源说明 ​

Oracle 数据源说明 ​

DB2 数据源说明 ​

Hive 数据源说明 ​

Spark SQL 数据源说明 ​

微软 SQL Server 数据源说明 ​

Cloudera Impala 数据源说明 ​

MongoDB 数据源说明 ​

麒麟企业版 数据源说明 ​

Amazon Redshift 数据源说明 ​

Alibaba Maxcompute 数据源说明 ​

Presto 数据源说明 ​

数据源内的数据表结构说明 ​

包含状态 ​

预览数据源中数据表的结构 ​

接口说明 ​

验证数据连接 ​

请求URL ​

请求参数 ​

URL 参数 ​

Request Body 参数 ​

返回对象的格式说明 ​

接口示例1 ​

新增数据连接 ​

请求URL ​

请求参数 ​

Request Body 参数 ​

返回对象的格式说明 ​

接口示例1 新建 Postgres 数据连接，而且此数据连接可以用于数据集成的输出,并且指定了文件上传路径 ​

分页查询数据连接 ​

请求URL ​

请求参数 ​

返回对象的格式说明 ​

接口示例1 分页查询数据连接 ​

根据ID查询数据连接 ​

请求URL ​

请求参数 ​

返回对象的格式说明 ​

接口示例1 查询 id 为 1 的数据连接 ​

更新已有数据连接的信息 ​

请求URL ​

请求参数 ​

Request Body 参数 ​

返回对象的格式说明 ​

接口示例1 更新 id 为 1 的数据连接，不允许上传文件到该数据连接 ​

删除已有数据连接 ​

请求URL ​

请求参数 ​

返回对象的格式说明 ​

根据id查询所有路径的所有table ​

请求URL ​

请求参数 ​

返回对象的格式说明 ​

接口示例1 更新 id 为 1 的数据连接，不允许上传文件到该数据连接 ​

查询table中的数据 ​

请求URL ​

请求参数 ​

request body 请求体 ​

返回对象的格式说明 ​

接口示例1 查询数据连接中 a_ivt_audit_heis 的数据 ​

在连接中执行sql并返回数据 ​

请求URL ​

请求参数 ​

URL 参数 ​

Request Body 参数 ​

返回对象的格式说明 ​

参考示例 ​

获取系统支持的连接类型和相关配置 ​

请求URL ​

请求参数 ​

URL 参数 ​

Request Body 参数 ​

返回对象的格式说明 ​

数据连接

数据连接说明

数据连接的类型

数据连接的结构说明

Mysql 数据源说明

Tidb 数据源说明

PostgreSQL 数据源说明

Greenplum 数据源说明

Oracle 数据源说明

DB2 数据源说明

Hive 数据源说明

Spark SQL 数据源说明

微软 SQL Server 数据源说明

Cloudera Impala 数据源说明

MongoDB 数据源说明

麒麟企业版数据源说明

Amazon Redshift 数据源说明

Alibaba Maxcompute 数据源说明

Presto 数据源说明

数据源内的数据表结构说明

包含状态

预览数据源中数据表的结构

接口说明

验证数据连接

请求URL

请求参数

URL 参数

Request Body 参数

返回对象的格式说明

接口示例1

新增数据连接

请求URL

请求参数

Request Body 参数

返回对象的格式说明

接口示例1 新建 Postgres 数据连接，而且此数据连接可以用于数据集成的输出,并且指定了文件上传路径

分页查询数据连接

请求URL

请求参数

返回对象的格式说明

接口示例1 分页查询数据连接

根据ID查询数据连接

请求URL

请求参数

返回对象的格式说明

接口示例1 查询 id 为 1 的数据连接

更新已有数据连接的信息

请求URL

请求参数

Request Body 参数

返回对象的格式说明

接口示例1 更新 id 为 1 的数据连接，不允许上传文件到该数据连接

删除已有数据连接

请求URL

请求参数

返回对象的格式说明

根据id查询所有路径的所有table

请求URL

请求参数

返回对象的格式说明

接口示例1 更新 id 为 1 的数据连接，不允许上传文件到该数据连接

查询table中的数据

请求URL

请求参数

request body 请求体

返回对象的格式说明

接口示例1 查询数据连接中 a_ivt_audit_heis 的数据

在连接中执行sql并返回数据

请求URL

请求参数

URL 参数

Request Body 参数

返回对象的格式说明

参考示例

获取系统支持的连接类型和相关配置

请求URL

请求参数

URL 参数

Request Body 参数

返回对象的格式说明