在微服务和云原生愈发风行的明天，数据的散布也愈发脱离单库单机而更加简单，应用的数据库类型也会更多，但业务的简单仍然会带来了大量的数据查问和导出需要，而很多时候咱们很难为数据量的大部分零碎创立残缺的BI数仓零碎，这时候你是不是感觉为这些需要查问和导出数据就会是一个十分困难且耗时的工作？Syncany-SQL就是这样一个工具，来在不依赖数据库的状况下实现不同库表、不同机器和不同数据库类型间间接关联查问和聚合计算后间接导出到罕用文件的工具。

间接在本地运行MySQL语法结构的SQL的执行引擎，只应用查看查问从罕用MySQL、MongoDB、PostgreSQL、sqlserver、elasticsearch、influxdb、clickhouse、sqlite数据库加载数据及读取execl、csv、json和一般文本文件后，在内存中实现join和聚合运算，单条SQL的执行后果能够长期保留在内存中以便作为后续SQL的输出表持续解决，并且INSERT INTO语句执行指定”仅插入 I“、”存在更新否则插入 UI“、”存在更新否则插入其余删除 UDI“、”删除后插入 DI“四种合并数据模式，针对大数据量也能够分批执行。

整个程序共有两个我的项目组成，其中

https://github.com/snower/syncany-sql 负责解析SQL

https://github.com/snower/syncany 负责执行

个性

  • Join关联查问，反对在不同库表、不同机器和不同数据库类型之间Join查问
  • Having在内存中执行，反对简单条件过滤，只蕴含聚合计算字段过滤时运行在聚合运算之前，否则在聚合计算之后执行
  • 齐全在内存中实现Group By分组聚合计算和distinct去重聚合运算
  • Order By排序，如果排序齐全是主表字段则应用数据库实现，否则内存中实现排序
  • 反对子查问
  • Where或join on条件反对简略子查问(仅反对and，比拟操作符仅反对==、!=、>、>=、<、<=、in)，其条件会被发送到数据库执行
  • Insert Into除反对失常写入数据库表外可间接写入到execl、json和csv中
  • Insert Into反对指定数据合并类型，共反对4中合并算法 （插入 I，更新否则插入 UI，更新否则插入其余删除 UDI，先删除再插入 DI）
  • 能够在设定每批数量后反对按批次执行
  • WHere、Join on和Having条件值都反对子查问返回
  • 反对set设置变量值，反对select info给变量赋值，执行拜访变量
  • 反对应用罕用MySQL函数

限度

  • Where和Join on条件仅可应用==、>、>=、<、<=、!=、in简略条件且仅可用and
  • Join仅反对Left Join模式，且关联条件中仅被关联表能增加常量条件，被关联表字段不能够是计算值，主表可是计算条件值
  • Join查问及子查问各表必须有别名
！！！特地留神：为何会有应用限度，来源于数据库加载数据的根本流程
  • 为简化数据库拜访接口，每种数据库实现仅反对简略查问条件和排序来加载数据
  • JOIN关联查问应用IN查问从数据库加载数据后在内存中实现计算

装置

可应用pip间接装置

pip3 install syncanysql# 执行胜利后，执行echo "select now(), 'hello world';" | syncany-sql# 看到正确输入即为装置胜利

因大多数状况应用数据库Driver很少，所以默认不装置数据库Driver，须要根据本人应用的数据库类型自行装置数据库Driver

pip3 install pymongo>=3.6.1pip3 install PyMySQL>=0.8.1pip3 install openpyxl>=2.5.0pip3 install psycopg2>=2.8.6pip3 install elasticsearch>=6.3.1pip3 install influxdb>=5.3.1pip3 install clickhouse_driver>=0.1.5pip3 install redis>=3.5.3pip3 install pymssql>=2.2.7

配置

配置文件反对json和yaml格局，默认加载当前目录的"config.[json|yaml]”和用户目录下"~/.syncany/config.[json|yaml]"文件，当前目录配置文件优先级高于用户目录，合并配置项后为最终加载配置。

具体介绍可查问示例配置文件信息 https://github.com/snower/syncany-sql/blob/main/docs/configure.md

配置实现后，命令行启动时会主动加载配置文件，并且增加的数据库信息将会在真正应用到的时候才会发动连贯，并不会在程序启动就会尝试连贯配置的数据库。

！！！特地留神的是，SQL中应用的数据库名称是配置文件中为每个数据库连贯指定的名称，而不是数据库中真正的名称。如：
# 咱们在配置文件中增加了以下数据库配置databases:  - name: test_db    driver: mysql    host: '127.0.0.1'    port: 3306    user: 'root'    passwd: '123456'    db: 'test'    charset: 'utf8mb4'     # 编写SQL是，如果咱们须要查问127.0.0.1的test库，咱们应该这样编写SQL  select * from test_db;

示例

查问Nginx日志

-- 查问访问量最高的3个IPSELECT seg0 AS ip, COUNT(*) AS cnt FROM `file://data/access.log?sep= ` GROUP BY seg0 ORDER BY cnt DESC LIMIT 3;

查问JSON文件

# 一个查问json文件的例子 https://github.com/snower/syncany-sql/tree/main/examples/demoSELECT    a.site_id,    b.name AS site_name,    IF(c.site_amount > 0, c.site_amount, 0) AS site_amount,    MAX(a.timeout_at) AS timeout_at,    MAX(a.vip_timeout_at) AS vip_timeout_at,    now() as `created_at?`FROM    (SELECT        YIELD_DATA(sites) AS site_id,            IF(vip_type = '2', GET_VALUE(rules, 0, 'timeout_time'), '') AS timeout_at,            IF(vip_type = '1', GET_VALUE(rules, 0, 'timeout_time'), '') AS vip_timeout_at    FROM        `data/demo.json`    WHERE        start_date >= '2021-01-01') a        JOIN    `data/sites.json` b ON a.site_id = b.site_id        JOIN    (SELECT        site_id, SUM(amount) AS site_amount    FROM        `data/orders.json`    WHERE        status <= 0    GROUP BY site_id) c ON a.site_id = c.site_idGROUP BY a.site_id;

更多罕用例子可查看 https://github.com/snower/syncany-sql/tree/main/examples

Python调用接口

from syncanysql import ScriptEnginewith ScriptEngine() as engine:    # 执行SQL    engine.execute('''        INSERT INTO `top_ips` SELECT            ip, cnt        FROM            (SELECT                seg0 AS ip, COUNT(*) AS cnt            FROM                `file:///var/log/nginx/access.log?sep= `            GROUP BY seg0) a        ORDER BY cnt DESC        LIMIT 3;    ''')    # 获取执行后果    print(engine.pop_memory_datas("top_ips"))

罕用用处

数据导出

因为间接能够把查问后果通过insert into语句间接写入csv或excel文件，以及能够十分不便的间接在不同库表、不同机器和不同数据库类型间间接join和进行聚合运算，也能够不便的应用内存保留两头后果，所以能够大幅简化编写简单SQL和数据导出的效率。

ETL工具

在指定insert into数据合并类型后能够十分不便的用于数据同步，在数据量级不高的零碎中，配合superset之类的图表零碎能够十分快的实现BI零碎搭建，命令行的执行模式也能够应用Jenkins或者云函数之类的零碎大幅提高稳定性或动静扩大能力。