查询系统数据

InfluxDB 3 Core 将与数据库服务器、查询和表相关的数据存储在系统表中。您可以查询系统表以获取有关正在运行的服务器、数据库和表模式的信息。

查询系统表

• 使用 HTTP 查询 API
  • 示例
    • 显示表
    • 查看表的列信息

使用 HTTP 查询 API

使用 HTTP API /api/v3/query_sql 端点来检索有关 InfluxDB 3 Core 中数据库服务器和表模式的系统信息。

要执行查询，请发送 GET 或 POST 请求到端点

• GET：在 URL 查询字符串中传递参数（用于简单查询）
• POST：在 JSON 对象中传递参数（用于复杂查询和代码可读性）

包括以下参数

• q: (必需) 要执行的 SQL 查询。
• db: (必需) 要对其执行查询的数据库。
• params: 一个 JSON 对象，包含要在参数化查询中使用的参数。
• format: 响应的格式 (json, jsonl, csv, pretty, 或 parquet)。JSONL (jsonl) 是首选格式，因为它将结果流式传输回客户端。pretty 用于人类可读的输出。默认为 json。

示例

显示表

以下示例发送一个 GET 请求，该请求执行 show tables 查询，以检索数据库的所有用户创建的表 ("table_schema":"iox")、系统表和信息模式表

curl "http://localhost:8181/api/v3/query_sql?db=mydb&format=jsonl&q=show%20tables"

响应正文包含以下 JSONL

{"table_catalog":"public","table_schema":"iox","table_name":"system_cpu","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_cpu_cores","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_memory","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_memory_faults","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_disk_usage","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_disk_io","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_disk_performance","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_network","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"system","table_name":"distinct_caches","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"system","table_name":"last_caches","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"system","table_name":"parquet_files","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"system","table_name":"processing_engine_plugins","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"system","table_name":"processing_engine_triggers","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"system","table_name":"queries","table_type":"BASE TABLE"}
{"table_catalog":"public","table_schema":"information_schema","table_name":"tables","table_type":"VIEW"}
{"table_catalog":"public","table_schema":"information_schema","table_name":"views","table_type":"VIEW"}
{"table_catalog":"public","table_schema":"information_schema","table_name":"columns","table_type":"VIEW"}
{"table_catalog":"public","table_schema":"information_schema","table_name":"df_settings","table_type":"VIEW"}
{"table_catalog":"public","table_schema":"information_schema","table_name":"schemata","table_type":"VIEW"}

一个表具有以下 table_schema 值之一

• iox: 数据库用户创建的表。
• system: 系统使用的表，用于显示有关正在运行的数据库服务器的信息。其中一些表显示存储的信息（例如配置），而另一些表（例如 queries 表）则在内存中保存短暂状态。
• information_schema: 显示数据库中表模式信息的视图。

查看表的列信息

以下查询发送一个 POST 请求，该请求执行 SQL 查询以检索示例 system_swap 表模式中列的信息

注意：当您在 JSON 中发送查询时，您必须转义包围字段名称的单引号。

curl "http://localhost:8181/api/v3/query_sql" \
  --header "Content-Type: application/json" \
  --json '{
    "db": "mydb",
    "q": "SELECT * FROM information_schema.columns WHERE table_schema = '"'iox'"' AND table_name = '"'system_swap'"'",
    "format": "jsonl"
  }'

输出如下

{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","column_name":"free","ordinal_position":0,"is_nullable":"YES","data_type":"UInt64"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","column_name":"host","ordinal_position":1,"is_nullable":"NO","data_type":"Dictionary(Int32, Utf8)"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","column_name":"percent","ordinal_position":2,"is_nullable":"YES","data_type":"Float64","numeric_precision":24,"numeric_precision_radix":2}
{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","column_name":"sin","ordinal_position":3,"is_nullable":"YES","data_type":"UInt64"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","column_name":"sout","ordinal_position":4,"is_nullable":"YES","data_type":"UInt64"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","column_name":"time","ordinal_position":5,"is_nullable":"NO","data_type":"Timestamp(Nanosecond, None)"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","column_name":"total","ordinal_position":6,"is_nullable":"YES","data_type":"UInt64"}
{"table_catalog":"public","table_schema":"iox","table_name":"system_swap","column_name":"used","ordinal_position":7,"is_nullable":"YES","data_type":"UInt64"}

最近执行的查询

要查看最近执行的查询，请查询 queries 系统表

curl "http://localhost:8181/api/v3/query_sql" \
  --header "Content-Type: application/json" \
  --json '{
    "db": "mydb",
    "q": "SELECT * FROM system.queries LIMIT 2",
    "format": "jsonl"
  }'

输出类似于以下内容

{"id":"cdd63409-1822-4e65-8e3a-d274d553dbb3","phase":"success","issue_time":"2025-01-20T17:01:40.690067","query_type":"sql","query_text":"show tables","partitions":0,"parquet_files":0,"plan_duration":"PT0.032689S","permit_duration":"PT0.000202S","execute_duration":"PT0.000223S","end2end_duration":"PT0.033115S","compute_duration":"P0D","max_memory":0,"success":true,"running":false,"cancelled":false}
{"id":"47f8d312-5e75-4db2-837a-6fcf94c09927","phase":"success","issue_time":"2025-01-20T17:02:32.627782","query_type":"sql","query_text":"show tables","partitions":0,"parquet_files":0,"plan_duration":"PT0.000583S","permit_duration":"PT0.000015S","execute_duration":"PT0.000063S","end2end_duration":"PT0.000662S","compute_duration":"P0D","max_memory":0,"success":true,"running":false,"cancelled":false}