支持多数据源联合查询的本地运行SQL执行引擎syncany-SQL安装和配置

在微服务和云原生愈发流行的今天，数据的分布也愈发脱离单库单机而更加复杂，使用的数据库类型也会更多，但业务的复杂依然会带来了大量的数据查询和导出需求，而很多时候我们很难为数据量的大部分系统创建完整的BI数仓系统，这时候你是不是觉得为这些需求查询和导出数据就会是一个十分困难且耗时的工作？Syncany-SQL就是这样一个工具，来在不依赖数据库的情况下完成不同库表、不同机器和不同数据库类型间直接关联查询和聚合计算后直接导出到常用文件的工具。

项目地址：https://github.com/snower/syncany-sql

使用示例：https://github.com/snower/syncany-sql/tree/main/examples

• 在本地运行MySQL语法结构的SQL
• 支持查询常用mysql、mongodb、postgresql、sqlserver、elasticsearch、influxdb、clickhouse、sqlite数据库及execl、csv、json和普通文本文件
• 支持本地临时数据表逻辑做中间结果保存
• 数据库数据加载使用简单条件过滤及IN条件查询
• 因由本地完成Join匹配所以支持不同库表、不同主机及不同类型数据库间Join关联查询
• Group By分组聚合计算及Order By排序也由本地执行，保证数据库安全性
• 数据写Insert Into支持 ”仅插入 I“、”存在更新否则插入 UI“、”存在更新否则插入其余删除 UDI“、”删除后插入 DI“四种模式
• 大数据量支持批次执行，有Group By或Having条件过滤自动执行Reduce合并结果
• 支持流式执行

安装

pip安装

直接使用pip一键即完成安装。

pip3 install syncanysql

执行成功后，执行以下测试命令成功输出当前时间即安装成功

echo 'select now();' | syncany-sql

之后执行”syncany-sql“即可可进入交互式命令行模式。

安装数据库Driver库

为了减少安装依赖和启动需要导入的模块数，加快安装和启动速度，默认不安装数据库相关Driver库，需要依据需要查询的数据库类型安装响应数据库Driver库，并且已安装的库不实际查询数据库也不会导入该数据库Driver库。

依赖数据库Driver库版本信息，可依据需要使用的数据库类型使用pip进行安装：

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

docker安装

也可以使用docker镜像。

docker pull sujin190/syncany-sql

启动docker镜像可进入交互式命令行模式

docker run --rm -it sujin190/syncany-sql syncany-sql

配置

默认从当前目录和用户目录下".syncany“中加载配置文件和扩展模块，配置文件名都是”config.json“或”config.yaml”，支持json和yaml语法配置文件，如果同时存在多种多个配置文件时，yaml覆盖json文件，当前目录覆盖用户目录配置文件。

以下是yaml的配置文件示例：

# syncany-sql配置文件定义全局日志、时区、编码格式及数据库参数定义和全局导入包信息
# 支持json和yaml格式配置文件
# 默认加载当前目录的"./config.[json|yaml]”和当前用户目录下"~/.syncany/config.[json|yaml]"文件
# 当前目录配置文件优先级高于用户目录，合并配置项后为最终加载配置

# 日志文件地址，未配置、配置空字符串或'-'都为标准输出
logfile: '-'
#  日志格式，请参照Python标准库logging配置
logformat: ''
# 日志输出级别 CRITICAL ERROR WARNING INFO DEBUG，默认INFO
loglevel: 'INFO'

# 文件编码，默认utf-8
encoding: 'utf-8'
# 默认日期时间格式化格式，请参照Python标准库datetime设置日期时间输出格式
datetime_format: '%Y-%m-%d %H:%M:%S'
# 默认日期格式化格式，请参照Python标准库datetime设置日期输出格式
date_format: '%Y-%m-%d'
# 默认时间格式化格式，请参照Python标准库datetime设置时间输出格式
time_format: '%H:%M:%S'

# 数据库配置信息
databases:
  - name: mysql_example # MySQL 示例参数，除name和driver是专有参数，其余连接参数可参照 https://github.com/PyMySQL/PyMySQL 配置
    driver: mysql
    host: '127.0.0.1'
    port: 3306
    user: 'root'
    passwd: '123456'
    db: 'example'
    charset: 'utf8mb4'

  - name: mongo_example # MongoDB 示例参数，除name和driver是专有参数，其余连接参数可参照 https://github.com/mongodb/mongo-python-driver 配置
    driver: mongo
    host: "127.0.0.1"
    port: 27017
    username: 'admin'
    password: '123456'
    authSource: 'admin'
    db: 'example'

  - name: postgresql_example # PostgreSQL 示例参数，除name和driver是专有参数，其余连接参数可参照 https://github.com/psycopg/psycopg2 配置
    driver: postgresql
    host: "127.0.0.1"
    port: 5432
    username: 'user'
    password: '123456'
    dbname: 'example'

  - name: sqlserver_example # Microsoft SQL Server 示例参数，除name和driver是专有参数，其余连接参数可参照 https://github.com/pymssql/pymssql 配置
    driver: sqlserver
    host: '127.0.0.1'
    port: 1433
    user: 'sa'
    password: '123456'
    database: 'example'
    charset: 'utf8'

  - name: clickhouse_example # ClickHouse 示例参数，除name和driver是专有参数，其余连接参数可参照 https://github.com/mymarilyn/clickhouse-driver 配置
    driver: clickhouse
    host: "127.0.0.1"
    port: 9000
    username: 'default'
    password: '123456'
    database: 'example'

  - name: influxdb_example # InfluxDB 示例参数，除name和driver是专有参数，其余连接参数可参照 https://github.com/influxdata/influxdb-python 配置
    driver: influxdb
    host: "127.0.0.1"
    port: 8086
    username: 'root'
    password: '123456'
    database: 'example'

  - name: elasticsearch_example # Elasticsearch 示例参数，除name和driver是专有参数，其余连接参数可参照 https://github.com/elastic/elasticsearch-py 配置
    driver: elasticsearch
    hosts: "http://localhost:9200"
    
  - name: sqlite_example # SQLite 示例参数，除name和driver是专有参数，其余连接参数可参照 https://docs.python.org/3/library/sqlite3.html 配置
    driver: sqlite
    database: ':memory:'
    
# 配置导入扩展，可用于注册自定义database driver、自定义普通函数或自定义聚合函数及其他扩展功能
# extensions:
#  - myext

# 配置全局导入包，也可在SQL中用"use"指令导入
#imports:
#  np: numpy # 导入numpy到别名numpy，可在SQL中"np$array()"使用

# 执行初始化SQL脚本
#executes:
#  - init.sql # 执行当前目录下init.sql初始化脚本，如果该脚本在用户目录下则可用“${HOME}/init.sql”，如在syncany配置目录下则可为“${SYNCANY_HOME}/init.sql”

！！！！ 特别注意：

编写SQL时使用的数据库名是配置文件中databases配置数据库连接参数中的name，不是数据库中真实名称，所以相同数据库服务器中不同数据库需要配置多次，而不同类似数据库中有相同名称时可以在配置文件中配置不同name而不会相互冲突。

文件支持

.txt .json .csv .xls .xlsx扩展名文件

针对.txt .json .csv .xls .xlsx的select和insert into可以直接使用文件路径即可查询读写，但需注意使用`来包裹确保正确解析特殊字符，如：

select * from `aaa.csv`;
select * from `/data/test.xlxs#sheet1`;

支持的文件格式：

• txt：按行读取分割组成‘{”line": }'这样的数据结构
• csv：正常二维数据表
• execl：必须为完整二维数据表，第一行为表头，解析为字段名，文件名后用"#“拼接上sheet名称可以读取对应sheet
• json：一维数组，数据中每一项为object

其他扩展名文本文件

使用”file://" Scheme来指定是一个文本文件，且可用查询参数指定切割信息。

如读取nginx log文件并统计请求来源ip数：

SELECT seg0 AS ip, COUNT(*) AS cnt FROM `file:///var/log/nginx/access.log?sep= ` GROUP BY seg0 ORDER BY cnt DESC LIMIT 3;

sep参数指定了切割字符，切割后会按顺序赋值到seg0…segn，可在sql中访问每个分割后字符串，line字段保存完成字符串。

示例

https://github.com/snower/syncany-sql/tree/main/examples