掌握数据的齿轮： sq 数据整理员 查询之美

生活中，我们都需要一个强大而灵活的工具来帮助我们从复杂的数据源中提取有价值的见解。这就是 sq 数据整理工具的用武之地。sq 不仅是一个命令行工具，它能够将 SQL 数据库、CSV 文件、Excel 电子表格等不同格式的数据源转化为易于理解和操作的信息。本文将带您深入了解 sq 的强大功能，探索它如何帮助我们以一种优雅、高效的方式处理数据。

sq 执行类似 jq 的查询^[1]，或数据库原生的SQL^[2]。它可以跨数据源联接^[3]：将 CSV 文件与 Postgres 表联接，或将 MySQL 与 Excel 联接。

sq 支持输出多种格式^[4]，包括 JSON^[5]、Excel^[6]、CSV^[7]、HTML^[8]、Markdown^[9] 和 XML^[10]，并且可以将查询结果直接插入^[11]到 SQL 数据库中。

sq 还可以检查^[12]数据源，查看有关源结构的元数据（表格、列、大小）。您可以使用 `sq diff`^[13] 比较表格或整个数据库。sq 包含用于常见数据库操作的命令，如复制^[14]、截断^[15]和删除^[16]表格。

更多信息请访问 sq.io^[17]。

安装

macOS

brew install neilotoole/sq/sq

Linux

/bin/sh -c "$(curl -fsSL https://sq.io/install.sh)"

Windows

scoop bucket add sq https://github.com/neilotoole/sq
scoop install sq

Go

go install github.com/neilotoole/sq

Docker

ghcr.io/neilotoole/sq 镜像预装了 sq 和一些相关工具，如 jq。

本地

# 进入一次性容器。
$ docker run -it ghcr.io/neilotoole/sq zsh

# 启动一个名为 "sq-shell" 的后台容器。
$ docker run -d --name sq-shell ghcr.io/neilotoole/sq
# 进入该容器。
$ docker exec -it sq-shell zsh

Kubernetes

在 Kubernetes 环境中运行 sq 对于数据库迁移以及一般的数据整理都很有用。

# 启动一个名为 "sq-shell" 的 pod。
$ kubectl run sq-shell --image ghcr.io/neilotoole/sq
# 进入该 pod。
$ kubectl exec -it sq-shell -- zsh

查看其他安装选项^[18]。

概览

使用 sq help 查看命令帮助。文档在 sq.io^[19]。阅读概览^[20]和教程^[21]。食谱^[22]包含了常见任务的配方，查询指南^[23]涵盖了 sq 的查询语言。

主要概念是：sq 对数据源进行操作，数据源被视为 SQL 数据库（即使源实际上是 CSV 或 XLSX 文件等）。

简而言之，你用 `sq add`^[24] 添加一个数据源（给它一个句柄^[25]），然后对数据源执行命令。

数据源

最初没有数据源^[26]。

$ sq ls

让我们添加^[27]一个数据源。首先我们将添加一个 SQLite^[28] 数据库，但这也可能是 Postgres^[29]、SQL Server^[30] 等，或文档数据源，比如 Excel^[31] 或 CSV^[32]。

下载示例数据库，并添加 sq 数据源。

$ wget https://sq.io/testdata/sakila.db

$ sq add ./sakila.db
@sakila  sqlite3  sakila.db

$ sq ls -v
HANDLE   ACTIVE  DRIVER   LOCATION                         OPTIONS
@sakila  active  sqlite3  sqlite3:///Users/demo/sakila.db

$ sq ping @sakila
@sakila       1ms  pong

$ sq src
@sakila  sqlite3  sakila.db

`sq ping`^[33] 命令简单地 ping 数据源以验证其可用性。

`sq src`^[34] 列出了激活的数据源^[35]，在我们的例子中是 @sakila。 你可以使用 sq src @other_src 更改激活的数据源。 当指定了激活的数据源时，你通常可以在 sq 命令中省略句柄。 因此，你也可以这样做：

$ sq ping
@sakila  1ms  pong

[!TIP] 像 CSV 或 Excel 这样的文档数据源可以从本地文件系统添加，或者从 HTTP URL 添加。

$ sq add https://acme.s3.amazonaws.com/sales.csv

查阅文档了解更多关于数据源^[36]的信息。

查询

从根本上说，sq 用于查询数据。jq 风格的语法在 查询指南^[37]中有详细说明。

上面的查询从 actor 表中选择了一些行。你也可以使用原生 SQL^[38]，例如：

但我们在这里有点盲目：我们是如何知道 actor 表的？

检查

`sq inspect`^[39] 是你的朋友。

使用 `sq inspect -v`^[40] 查看更多细节。 或者使用 `-j`^[41] 获取 JSON 输出：

结合使用 sq inspect 和 jq^[42] 以获得一些有用的功能。 以下是如何列出激活数据源中的所有表名：

$ sq inspect -j | jq -r '.tables[] | .name'
actor
address
category
city
country
customer
[...]

以下是如何将每个表导出^[43]为 CSV 文件：

$ sq inspect -j | jq -r '.tables[] | .name' | xargs -I % sq .% --csv --output %.csv
$ ls
actor.csv     city.csv     customer_list.csv  film_category.csv  inventory.csv  rental.csv       staff.csv
address.csv   country.csv   film.csv        film_list.csv   language.csv  sales_by_film_category.csv  staff_list.csv
category.csv  customer.csv  film_actor.csv     film_text.csv   payment.csv  sales_by_store.csv      store.csv

注意，你也可以检查单个表：

阅读更多关于 `sq inspect`^[44] 的信息。

差异

使用 `sq diff`^[45] 比较数据源或单个表的元数据或行数据。

默认行为是对比表模式和行数。在此模式下不比较表行数据。

使用 `--data`^[46] 对比行数据。

提供了许多其他选项。请参阅差异文档^[47]。

插入查询结果

sq 查询结果可以输出^[48]为各种格式（`text`^[49]、`json`^[50]、`csv`^[51] 等）。这些结果也可以作为_插入_^[52]到数据库表中。

也就是说，你可以使用 sq 将 Postgres 查询的结果插入到 MySQL 表中，或将 Excel 工作表复制到 SQLite 表中，或将 CSV 文件推送到 SQL Server 表中等。

[!TIP] 如果你想要在同一个（数据库）数据源内复制表格，使用 `sq tbl copy`^[53]，它使用数据库的原生表复制功能。

这里我们查询一个 CSV 文件，并将结果插入到 Postgres 表中。

跨数据源联接

sq 可以执行常规的联接^[54]。以下是你如何联接 actor、film_actor 和 film 表：

$ sq '.actor | join(.film_actor, .actor_id) | join(.film, .film_id) | .first_name, .last_name, .title'

但 sq 也可以跨数据源联接。也就是说，你可以将 Excel 工作表与 Postgres 表联接，或将 CSV 文件与 MySQL 联接等。

这个例子联接了 Postgres 数据库、Excel 工作表和 CSV 文件。

在查询指南^[55]中阅读更多关于跨数据源联接的信息。

表格命令

sq 提供了几个用于处理表格的便捷命令：`tbl copy`^[56]、`tbl truncate`^[57] 和 `tbl drop`^[58]。注意，这些命令直接针对 SQL 数据库数据源，使用它们的原生 SQL 命令。

$ sq tbl copy .actor .actor_copy
Copied table: @sakila.actor --> @sakila.actor_copy (200 rows copied)

$ sq tbl truncate .actor_copy
Truncated 200 rows from @sakila.actor_copy

$ sq tbl drop .actor_copy
Dropped table @sakila.actor_copy

UNIX 管道

对于基于文件的数据源（如 CSV 或 XLSX），你可以 sq add 添加源文件，但你也可以管道传输它：

$ cat ./example.xlsx | sq .Sheet1

同样，你可以检查：

$ cat ./example.xlsx | sq inspect

驱动程序

sq 通过驱动程序^[59]实现知道如何处理数据源类型。要查看已安装/支持的驱动程序：

$ sq driver ls
DRIVER     DESCRIPTION
sqlite3    SQLite
postgres   PostgreSQL
sqlserver  Microsoft SQL Server / Azure SQL Edge
mysql      MySQL
csv        Comma-Separated Values
tsv        Tab-Separated Values
json       JSON
jsona      JSON Array: LF-delimited JSON arrays
jsonl      JSON Lines: LF-delimited JSON objects
xlsx       Microsoft Excel XLSX

输出格式

sq 有许多输出格式^[60]：

• --text：文本^[61]
• --json：JSON^[62]
• --jsona：JSON 数组^[63]
• --jsonl：JSON 行^[64]
• --csv / --tsv：CSV^[65] / TSV^[66]
• --xlsx：XLSX^[67]（Microsoft Excel）
• --html：HTML^[68]
• --xml：XML^[69]
• --yaml：YAML^[70]
• --markdown：Markdown^[71]
• --raw：原始^[72]（字节）

更新日志

请参阅 CHANGELOG.md^[73]。

致谢

• 感谢 Diego Souza^[74] 创建了 Arch Linux 软件包^[75]，以及 `@icp`^[76] 创建了 Void Linux 软件包^[77]。
• 许多灵感来自 jq^[78]。
• 请参阅 `go.mod`^[79] 了解第三方软件包列表。
• 此外，sq 包含了修改后的版本： - `olekukonko/tablewriter`^[80] - `segmentio/encoding`^[81] 用于 JSON 编码。
• _Sakila_^[82] 示例数据库取自 jOOQ^[83]，后者又继承自早期 Sakila 的工作。
• 日期渲染通过 `ncruces/go-strftime`^[84]。
• 修改后的版本 `dolmen-go/contextio`^[85] 已合并到代码库中。
• `djherbis/buffer`^[86] 用于缓存。
• 合并了分支版本 `nightlyone/lockfile`^[87]。
• 用户友好的 text 日志格式处理器是 `lmittmann/tint`^[88] 的分支版本。

类似、相关或值得注意的项目

• `usql`^[89]
• `textql`^[90]
• `golang-migrate`^[91]
• `octosql`^[92]
• `rq`^[93]
• `miller`^[94]
• `jsoncolor`^[95] 是为 sq 创建的 JSON 着色器。
• `streamcache`^[96] 是为 sq 创建的 Go 内存字节缓存机制。
• `fifomu`^[97] 是一个 FIFO 互斥锁，被 streamcache 使用，因此也在 sq 中上游使用。
• `tailbuf`^[98] 是为 sq 创建的固定大小对象尾缓冲区。
• `oncecache`^[99] 是为 sq 创建的内存对象缓存。

结语

通过本文的介绍，我们可以看到 sq 数据整理工具是一个多功能的数据查询和处理利器。它不仅可以简化数据源的管理和操作，还通过其强大的跨源联接、灵活的输出格式和直观的查询语言，极大地提高了数据处理的效率和灵活性。无论您是数据库专家还是数据处理的新手，sq 都能成为您工作中的得力助手。随着您对 sq 的进一步探索和使用，您将发现它的更多强大功能，这些功能将使您的数据整理工作变得更加轻松和愉快。