iQuey

数据仓库通用查询服务

安装依赖

pip install -r requirements.txt

根据所需要连接的数据仓库, 安装对应的扩展库

pip install sqlalchemy-hana      # hana
pip install ibm_db_sa            # db2
pip install PyHive               # hive
pip install impyla               # impala
pip install sqlalchemy-redshift  # redshift
pip install sqlalchemy-sqlany    # driver for SAP Sybase SQL Anywhere, developed by SAP
...

根据所需要连接的数据仓库, 安装对应的连接驱动

pip install MySQL-python       # mysql
pip install cx_Oracle          # oracle
pip install pymssql            # mssql
pip install psycopg2           # postgresql
pip install PyHive             # hive
pip install impyla             # impala
pip install pypyodbc           # odbc
pip install pyhdb              # hana
...

配置文件

[config]
log_cfg='conf/logging.conf'     # 日志配置文件
log_dir='./logs'                # 日志路径文件夹
port=8000                       # 服务默认端口
pool_size=50                    # 线程池数目

启动服务

python main.py

启动参数

main.py options:

  --debug                          debug模式 (default: False)
  --port                           服务端口
  --processors                     进程数 (default: 1)

API 接口文档

计算接口

`GET` `/api/calculate/result` 获取计算结果

参数名	类型	含义	参数位置	是否必选	示例
type	str	数据库类型	URL	√
host	str	数据库地址	URL	√
port	int	数据库端口	URL
username	str	数据库用户名	URL
password	str	数据库密码	URL
alias	str	数据库其他连接参数	URL
schema	str	数据库名称	URL	√
kwargs	str - `json`	数据库其他配置参数	URL
xAxis	`str`/`json`	维度	BODY		`name` `["name", "age"]` `[{"params": ["cdatetime"], "func": "cast_year"}]`
yAxis	`str`/`json`	维度	BODY	√	`name` `[{"name": "name", "func": "count"]` `{"name": "name", "func": "count", "calculate": "percert"`
table	`str`/`json`	表名	BODY	√	`table_name` `{"table": "test", "schema": "schema"}`
where	`str`/`json`	过滤条件	BODY		`age<2`
order	str - `json(list)`	排序	BODY		`[0]` `[(0, "DESC"), 1]`

yAxis中calculate对应取值：

percent: 百分比
accumulate: 累加计算
same: 同比
annular: 环比

请求示例

curl -i -X POST 'http://127.0.0.1:8000/api/calculate/result?schema=bin_test&type=mysql&host=192.168.1.1&username=root&password=123456' -d '{"table": "faker91", "yAxis": [{"name": "name", "func": "count", "calculate": "same", "result_type": "rate"}], "xAxis": [{"params": ["cdatetime"], "func": "cast_year"}]}'

结果返回：

{"data": [[0, null], [1970, null], [2009, -5], [2010, 4], [2011, 0], [2012, -6], [2013, 0], [2014, 3], [2015, 1], [2016, -1], [2017, 0], [2018, -4], [2019, 3], [2020, 1]], "columns": [{"type": "long", "name": "x_0"}, {"type": "long", "name": "anon_1"}]}

数据库接口

`GET` `/api/schemas` 获取全部SCHEMA

参数名	类型	含义	参数位置	是否必选
type	str	数据库类型	URL	√
host	str	数据库地址	URL	√
port	int	数据库端口	URL
username	str	数据库用户名	URL
password	str	数据库密码	URL
alias	str	数据库其他连接参数	URL
kwargs	str - `json`	数据库其他配置参数	URL

请求示例

curl -i -X GET 'http://127.0.0.1:8000/api/db/schema?type=mysql&host=127.0.0.1&port=3306&username=root&password=123456'

结果返回：

{"databases": ["information_schema", "mysql", "performance_schema"]}

`GET` `/api/tables` 获取数据库中全部表（含视图）

参数名	类型	含义	参数位置	是否必选
type	str	数据库类型	URL	√
host	str	数据库地址	URL	√
port	int	数据库端口	URL
username	str	数据库用户名	URL
password	str	数据库密码	URL
alias	str	数据库其他连接参数	URL
schema	str	数据库名称	URL	√
kwargs	str - `json`	数据库其他配置参数	URL

请求示例

curl -i -X GET 'http://127.0.0.1:8000/api/tables?type=mysql&host=127.0.0.1&port=3306&username=root&password=12345&schema=mysql'

结果返回：

{"tables": [{"type": "table", "name": "columns_priv"}, {"type": "table", "name": "db"}, {"type": "table", "name": "event"}, {"type": "table", "name": "func"}, {"type": "table", "name": "general_log"}, {"type": "table", "name": "help_category"}, {"type": "table", "name": "help_keyword"}, {"type": "table", "name": "help_relation"}, {"type": "table", "name": "help_topic"}, {"type": "table", "name": "host"}, {"type": "table", "name": "ndb_binlog_index"}, {"type": "table", "name": "plugin"}, {"type": "table", "name": "proc"}, {"type": "table", "name": "procs_priv"}, {"type": "table", "name": "proxies_priv"}, {"type": "table", "name": "servers"}, {"type": "table", "name": "slow_log"}, {"type": "table", "name": "tables_priv"}, {"type": "table", "name": "time_zone"}, {"type": "table", "name": "time_zone_leap_second"}, {"type": "table", "name": "time_zone_name"}, {"type": "table", "name": "time_zone_transition"}, {"type": "table", "name": "time_zone_transition_type"}, {"type": "table", "name": "user"}]}

`GET` `/api/columns` 查看数据库表字段信息

参数名	类型	含义	参数位置	是否必选
type	str	数据库类型	URL	√
host	str	数据库地址	URL	√
port	int	数据库端口	URL
username	str	数据库用户名	URL
password	str	数据库密码	URL
alias	str	数据库其他连接参数	URL
schema	str	数据库名称	URL	√
table	str	数据表名称	URL	√
kwargs	str - `json`	数据库其他配置参数	URL

请求示例

curl -i -X GET 'http://127.0.0.1:8000/api/schema?type=mysql&host=127.0.0.1&port=3306&username=root&password=123456&schema=mysql&table=user'

结果返回：

{"columns": [{"default": "''", "type": "str", "name": "Host", "nullable": false}, {"default": "''", "type": "str", "name": "User", "nullable": false}, {"default": "''", "type": "str", "name": "Password", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Select_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Insert_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Update_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Delete_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Create_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Drop_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Reload_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Shutdown_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Process_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "File_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Grant_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "References_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Index_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Alter_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Show_db_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Super_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Create_tmp_table_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Lock_tables_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Execute_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Repl_slave_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Repl_client_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Create_view_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Show_view_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Create_routine_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Alter_routine_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Create_user_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Event_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Trigger_priv", "nullable": false}, {"default": "'N'", "type": "unicode", "name": "Create_tablespace_priv", "nullable": false}, {"default": "''", "type": "unicode", "name": "ssl_type", "nullable": false}, {"default": null, "type": "str", "name": "ssl_cipher", "nullable": false}, {"default": null, "type": "str", "name": "x509_issuer", "nullable": false}, {"default": null, "type": "str", "name": "x509_subject", "nullable": false}, {"default": "'0'", "autoincrement": false, "type": "int", "name": "max_questions", "nullable": false}, {"default": "'0'", "autoincrement": false, "type": "int", "name": "max_updates", "nullable": false}, {"default": "'0'", "autoincrement": false, "type": "int", "name": "max_connections", "nullable": false}, {"default": "'0'", "autoincrement": false, "type": "int", "name": "max_user_connections", "nullable": false}, {"default": "''", "type": "str", "name": "plugin", "nullable": true}, {"default": null, "type": "str", "name": "authentication_string", "nullable": true}]}

`GET` `/api/query/result` 获取查询结果

参数名	类型	含义	参数位置	是否必选
type	str	数据库类型	URL	√
host	str	数据库地址	URL	√
port	int	数据库端口	URL
username	str	数据库用户名	URL
password	str	数据库密码	URL
alias	str	数据库其他连接参数	URL
schema	str	数据库名称	URL
sql	str	SQL 语句，标准SQL	URL	√
kwargs	str - `json`	数据库其他配置参数	URL

请求示例

curl -i -X GET 'http://127.0.0.1:8000/api/query/result?type=mysql&host=127.0.0.1&port=3306&username=root&password=123456& schema=test&sql=select+*+from+test+limit+2'

结果返回：

{
    "schema": [
        {"type": "number", "name": "id"},
        {"type": "string", "name": "name"},
        {"type": "string", "name": "addr"},
        {"type": "number", "name": "age"},
        {"type": "string", "name": "info"},
        {"type": "date",   "name": "birthday"}
    ],
    "data": [
        [5903367, "zhangsan", "beijing", 1, null, "1997-08-24 11:56:08"],
        [5903369, "lisi", "shanghai", 1, null, "1995-08-24 11:56:08"]
    ]
}

`GET` `/api/query/sql` 获取查询SQL

参数名	类型	含义	参数位置	是否必选
type	str	数据库类型	URL	√
sql	str	SQL 语句，标准SQL	URL	√
schema	str	数据库名	URL

请求示例

curl -i -X GET 'http://127.0.0.1:8000/api/query/sql?sql=SELECT+*+FROM+faker91+limit+10&type=oracle'
curl -i -X GET 'http://127.0.0.1:8000/api/query/sql?sql=SELECT+age,sum(age)+over(order+by+age)+FROM+faker91&type=mysql'

结果返回：

{"sql": "SELECT *  FROM (SELECT *  FROM faker91)  WHERE ROWNUM <= 10"}
{"sql": "SELECT age, (SELECT sum(age) AS sum_1  FROM faker91 AS tb_ad1f41  WHERE tb_ad1f41.age <= faker91.age) AS fd_7e4a47  FROM faker91"}

`GET` `/api/query/tree` 获取查询语法树

参数名	类型	含义	参数位置	是否必选
sql	str	SQL 语句，标准SQL	URL	√

请求示例

curl -i -X GET 'http://127.0.0.1:8000/api/query/tree?sql=SELECT+*+FROM+faker91+limit+10'

结果返回：

{"tree": {"query": {"windowDecls": {"list": [], "pos": {"columnNumber": 1, "endLineNumber": 1, "endColumnNumber": 21, "lineNumber": 1}}, "keywordList": {"list": [], "pos": {"columnNumber": 1, "endLineNumber": 1, "endColumnNumber": 6, "lineNumber": 1}}, "from": {"pos": {"columnNumber": 15, "endLineNumber": 1, "endColumnNumber": 21, "lineNumber": 1}, "names": ["faker91"], "componentPositions": [{"columnNumber": 15, "endLineNumber": 1, "endColumnNumber": 21, "lineNumber": 1}]}, "selectList": {"list": [{"pos": {"columnNumber": 8, "endLineNumber": 1, "endColumnNumber": 8, "lineNumber": 1}, "names": [""], "componentPositions": [{"columnNumber": 8, "endLineNumber": 1, "endColumnNumber": 8, "lineNumber": 1}]}], "pos": {"columnNumber": 8, "endLineNumber": 1, "endColumnNumber": 8, "lineNumber": 1}}, "pos": {"columnNumber": 1, "endLineNumber": 1, "endColumnNumber": 21, "lineNumber": 1}}, "fetch": {"scale": 0, "isExact": true, "pos": {"columnNumber": 29, "endLineNumber": 1, "endColumnNumber": 30, "lineNumber": 1}, "value": 10, "typeName": "DECIMAL", "prec": 2}, "pos": {"columnNumber": 29, "endLineNumber": 1, "endColumnNumber": 30, "lineNumber": 1}}}

TodoList

数据分析
数据导出
可视化界面

Name		Name	Last commit message	Last commit date
Latest commit History 16 Commits
api		api
conf		conf
query		query
resource		resource
utils		utils
.gitignore		.gitignore
README.md		README.md
main.py		main.py
requirements.txt		requirements.txt
server.py		server.py

sandabuliu/iQuery

Folders and files

Latest commit

History

Repository files navigation

iQuey

最近更新

安装依赖

配置文件

启动服务

API 接口文档

计算接口

GET /api/calculate/result 获取计算结果

请求示例

数据库接口

GET /api/schemas 获取全部SCHEMA

请求示例

GET /api/tables 获取数据库中全部表（含视图）

请求示例

GET /api/columns 查看数据库表字段信息

请求示例

GET /api/query/result 获取查询结果

请求示例

GET /api/query/sql 获取查询SQL

请求示例

GET /api/query/tree 获取查询语法树

请求示例

TodoList

About

Resources

Stars

Watchers

Forks

Languages

`GET` `/api/calculate/result` 获取计算结果

`GET` `/api/schemas` 获取全部SCHEMA

`GET` `/api/tables` 获取数据库中全部表（含视图）

`GET` `/api/columns` 查看数据库表字段信息

`GET` `/api/query/result` 获取查询结果

`GET` `/api/query/sql` 获取查询SQL

`GET` `/api/query/tree` 获取查询语法树