4.QusetDB常用SQL

欢迎大家来到IT世界,在知识的湖畔探索吧!

QusetDB常用SQL

1.SHOW

此关键字提供表、列和分区信息，包括元数据。SHOW关键字可用于检查指定的时间戳设置列、分区附件设置和磁盘上的分区存储大小。

描述：

SHOW – 返回所有表格。

SHOW COLUMNS – 返回所选表的所有列及其元数据。

SHOW PARTITIONS – 返回所选表的分区信息。

SHOW PARAMETERS – 显示配置键及其匹配的 env_var_name、值和值的来源

SHOW SERVER_VERSION – 显示 PostgreSQL 兼容版本

1.1示例

1.1.1SHOW TABLES

SHOW TABLES;

欢迎大家来到IT世界,在知识的湖畔探索吧!

1.1.2SHOW COLUMNS

欢迎大家来到IT世界,在知识的湖畔探索吧!SHOW COLUMNS FROM my_table;

1.1.3SHOW PARTITIONS

SHOW PARTITIONS FROM my_table;

1.1.4SHOW PARAMETERS

欢迎大家来到IT世界,在知识的湖畔探索吧!SHOW PARAMETERS;

输出显示：

• property_path：配置密钥
• env_var_name：键的匹配 env-var
• value：键的当前值
• value_source：如何设置值（默认值、conf或env）

您可以选择将SHOW参数与其他子句链接起来：

-- This query will return all parameters where the value contains 'C:' SHOW PARAMETERS WHERE value ILIKE '%C:%'; -- This query will return all parameters where the property_path is not 'cairo.root' or 'cairo.sql.backup.root', ordered by the first column SHOW PARAMETERS WHERE property_path NOT IN ('cairo.root', 'cairo.sql.backup.root') ORDER BY 1; -- This query will return all parameters where the value_source is 'env' SHOW PARAMETERS WHERE value_source = 'env';

1.1.5SHOW SERVER_VERSION

显示PostgreSQL兼容版本。

欢迎大家来到IT世界,在知识的湖畔探索吧!SHOW SERVER_VERSION;

2.INSERT

INSERT将所选数据写入数据库表。

注：如果目标分区是通过符号链接连接的，则该分区是只读的。在只读分区上执行INSERT操作会触发服务器中的关键级别日志，并且插入是无操作。

直接插入值或使用子查询：

• VALUE：直接定义要插入的值。
• SELECT：根据 SELECT 查询的结果插入值

设置子查询别名：

• WITH AS：根据子查询插入值，使用 WITH 为其提供别名。

参数：

• batch – 需要一个 batchCount（整数）值，定义一次要处理多少条记录。

2.1插入值

插入所有列：

INSERT INTO trades VALUES( '2021-10-05T11:31:35.878Z', 'AAPL', 255, 123.33, 'B');

批量插入：

欢迎大家来到IT世界,在知识的湖畔探索吧!INSERT INTO trades VALUES ('2021-10-05T11:31:35.878Z', 'AAPL', 245, 123.4, 'C'), ('2021-10-05T12:31:35.878Z', 'AAPL', 245, 123.3, 'C'), ('2021-10-05T13:31:35.878Z', 'AAPL', 250, 123.1, 'C'), ('2021-10-05T14:31:35.878Z', 'AAPL', 250, 123.0, 'C');

指定 schema：

INSERT INTO trades (timestamp, symbol, quantity, price, side) VALUES( to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'), 'AAPL', 255, 123.33, 'B');

注：在INSERT过程中可以省略列，在这种情况下，值将为NULL。

仅插入特定列：

欢迎大家来到IT世界,在知识的湖畔探索吧!INSERT INTO trades (timestamp, symbol, price) VALUES(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),'AAPL','B');

2.2插入查询结果

此方法允许您一次插入查询返回的行数。

按查询插入：

INSERT INTO confirmed_trades SELECT timestamp, instrument, quantity, price, side FROM unconfirmed_trades WHERE trade_id = '';

使用WITH关键字为SELECT子查询设置别名：

欢迎大家来到IT世界,在知识的湖畔探索吧!WITH confirmed_id AS ( SELECT * FROM unconfirmed_trades WHERE trade_id = '' ) INSERT INTO confirmed_trades SELECT * FROM confirmed_id;

注：

自QuestDB v7.4.0以来，INSERT INTO SELECT 的默认行为已更改。以前，表将以原子方式创建。对于大型表，这需要大量的 RAM，如果数据库内存不足，可能会导致错误。默认情况下，这将分批执行。如果查询失败，可能会插入部分数据。如果这是一个问题，建议使用 ATOMIC 关键字（INSERT ATOMIC INTO）。或者，启用表上的重复数据删除将允许您执行幂等插入以重新插入任何丢失的数据。

2.3ATOMIC

插入可以原子化创建，首先加载所有数据，然后在单个事务中提交。

这要求数据在内存中同时可用，因此对于大型插入，这可能会产生性能问题。

要强制执行此行为，可以使用ATOMIC关键字：

INSERT ATOMIC INTO confirmed_trades SELECT timestamp, instrument, quantity, price, side FROM unconfirmed_trades WHERE trade_id = '';

2.4BATCH

默认情况下，数据将分批插入。

可以配置批次的大小：在全局范围内，通过在server.conf中设置cairo.sql.insert.model.batch.size配置选项。在本地，通过在INSERT INTO语句中使用BATCH关键字。组合是INSERT+BATCH+行数+INTO+TABLE，后面是SELECT语句。

在我们的例子中，我们使用4096作为批量大小：

欢迎大家来到IT世界,在知识的湖畔探索吧!INSERT BATCH 4096 INTO confirmed_trades SELECT timestamp, instrument, quantity, price, side FROM unconfirmed_trades WHERE trade_id = '';

还可以使用o3MaxLag选项为这些批处理写入指定乱序提交延迟：

INSERT BATCH 4096 o3MaxLag '1s' INTO confirmed_trades SELECT timestamp, instrument, quantity, price, side FROM unconfirmed_trades WHERE trade_id = '';

3.UPDATE

欢迎大家来到IT世界,在知识的湖畔探索吧!

注：

不能在SET关键字后多次指定相同的 columnName，因为这会导致歧义

指定的时间戳列无法更新，因为这会导致时间序列数据的历史记录发生变化

如果目标分区是通过符号链接连接的，则该分区是只读的。对只读分区的UPDATE操作将失败并生成错误。

示例：

欢迎大家来到IT世界,在知识的湖畔探索吧!UPDATE trades SET price = 125.34 WHERE symbol = 'AAPL'; UPDATE book SET mid = (bid + ask)/2 WHERE symbol = 'AAPL'; UPDATE spreads s SET s.spread = p.ask - p.bid FROM prices p WHERE s.symbol = p.symbol; WITH up AS ( SELECT p.ask - p.bid AS spread, s.timestamp FROM prices p JOIN instruments i ON p.symbol = i.symbol WHERE i.type = 'BOND' ) UPDATE spreads s SET s.spread = up.spread FROM up WHERE s.timestamp = up.timestamp; WITH up AS ( SELECT symbol, spread, ts FROM temp_spreads WHERE timestamp between '2022-01-02' and '2022-01-03' ) UPDATE spreads s SET spread = up.spread FROM up WHERE up.ts = s.ts AND s.symbol = up.symbol;

4.WITH

支持通用表表达式（CTE），即命名一个或多个子查询以与SELECT、INSERT或UPDATE查询一起使用。

使用CTE可以轻松简化涉及子查询的大型或复杂语句，特别是在多次使用此类子查询时。

alias – 是为便于重用而为子查询指定的名称

subQuery – 是一个SQL查询（例如SELECT * FROM 表）

示例：

WITH first_10_users AS (SELECT * FROM users limit 10) SELECT user_name FROM first_10_users; WITH first_10_users AS (SELECT * FROM users limit 10), first_5_users AS (SELECT * FROM first_10_users limit 5) SELECT user_name FROM first_5_users; WITH avg_distance AS (SELECT avg(trip_distance) average FROM trips) SELECT pickup_datetime, trips.trip_distance > avg_distance.average longer_than_average FROM trips CROSS JOIN avg_distance; WITH up AS ( SELECT symbol, spread, ts FROM temp_spreads WHERE timestamp between '2022-01-02' and '2022-01-03' ) UPDATE spreads s SET spread = up.spread FROM up WHERE up.ts = s.ts AND s.symbol = up.symbol; WITH up AS ( SELECT symbol, spread, ts FROM temp_spreads WHERE timestamp between '2022-01-02' and '2022-01-03' ) INSERT INTO spreads SELECT * FROM up;

5.COPY

对于分区表，只有在具有本地物理连接SSD的机器上才能实现最佳的COPY性能。可以使用网络块存储（如AWS EBS卷）来执行操作，但会产生以下影响：

• 用户需要为卷配置最大IOPS和吞吐量设置值。
• 所需的导入时间可能要长5-10倍。

该命令将保存在定义的根目录中的分隔文本文件中的表复制到 QuestDB 中。COPY 具有以下导入模式：

• 并行导入，用于复制分区表：
• 并行级别基于分区粒度。对于要导入的数据，正确选择时间戳列和分区类型非常重要。分区的粒度越高，导入操作完成得越快。
• 如果目标表存在并已分区，则目标表必须为空。
• 如果目标表不存在，则必须定义 TIMESTAMP 和 PARTITION BY 选项以创建分区表。PARTITION BY 值不应为 NONE。
• 当表确实存在且不为空时，不支持导入。

• 串行导入，用于复制非分区表：
• 如果目标表存在且未分区，则只要文件结构与表匹配，就会附加数据。
• 如果目标表不存在，则使用从文件数据派生的元数据创建它。

注：COPY占用所有可用资源。当一个导入正在运行时，新的请求将被拒绝。

COPY ‘

‘ CANCEL
在导入过程中取消由导入id定义的复制操作。

5.1选项

• HEADER true/false：当为true时，QuestDB会自动假设第一行是头。否则，使用模式识别来确定第一行是否用作标题。默认设置为false。
• TIMESTAMP：定义要导入的文件中的时间戳列的名称。
• FORMAT：当格式不是默认值（yyyy-MM-ddTHH:MM:ss.SSSUUUZ）或无法检测到时的时间戳列格式。
• DELIMITER：默认设置为，。
• 分割方式：分割单元。
• ON ERROR：定义对数据解析错误的响应。有效值为：
• 跳过行：跳过整行
• SKIP_COLUMN：跳过列并使用默认值（null表示可空类型，false表示布尔类型，0表示其他不可空类型）
• ABORT：在第一个错误时中止整个导入，并恢复导入前的表状态

5.2示例

欢迎大家来到IT世界,在知识的湖畔探索吧!COPY weather FROM 'weather.csv' WITH HEADER true FORMAT 'yyyy-MM-ddTHH:mm:ss.SSSUUUZ' ON ERROR SKIP_ROW;

异步启动导入并返回导入 id 字符串：

通过查询可以访问日志：

SELECT * FROM 'sys.text_import_log' WHERE id = '55ca24e5ba';

运行时，可以使用以下命令取消导入：

欢迎大家来到IT世界,在知识的湖畔探索吧!COPY '55ca24e5ba' CANCEL;

几秒钟内，导入应停止，text_import_log中应显示状态为“已取消”的消息，例如：

SELECT * FROM 'sys.text_import_log' WHERE id = '55ca24e5ba' LIMIT -1;

6.TRUNCATE TABLE

TRUNCATE TABLE永久删除表的内容，而不删除表本身。

注：此命令将不可恢复地删除目标表中的数据。如有疑问，请确保您已创建数据备份。

欢迎大家来到IT世界,在知识的湖畔探索吧!TRUNCATE TABLE ratings;

7.VACUUM TABLE

VACUUM TABLE通过扫描文件系统并删除重复的目录和文件来回收存储。

此命令提供了一种回收磁盘空间的手动机制。该实现扫描文件系统以检测重复的目录和文件。频繁使用该命令可能相对昂贵。因此，必须谨慎执行VACUUM TABLE。

当以无序方式附加表时，VACUUM table命令会将新的分区版本写入磁盘。一旦SELECT查询无法读取旧分区版本目录，就会将其删除。如果发生文件系统错误，旧文件的物理删除可能会中断，过时的分区版本可能会占用磁盘空间。

当运行UPDATE SQL语句时，它会复制所选表的列文件。旧的列文件会自动删除，但在某些情况下，它们可能会被留下。在这种情况下，VACUUM TABLE可用于重新触发旧列文件的删除过程。

VACUUM TABLE命令启动对表分区目录和列文件的新扫描。它检测消耗磁盘空间的冗余、未使用的文件并将其删除。VACUUM TABLE异步执行，也就是说，在将文件的响应返回给SQL客户端后，它可能会继续扫描和删除文件。

VACUUM TABLE trades;