Skip to content

sandabuliu/iQuery

BranchesTags

Folders and files

NameName
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
 
 

Repository files navigation

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

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

About

The service for general query.

Resources

Readme
Activity

Stars

0 stars

Watchers

2 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published

Languages