timescaledb学习

原创已于 2024-03-05 08:55:04 修改 · 491 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#学习 #数据库

于 2023-09-29 16:47:39 首次发布

本文介绍了如何在PostGIS中创建扩展，如topology、地址处理和地理编码插件，以及在TimescaleDB中创建和管理时间分区表的过程，包含数据插入和查询示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create extension postgis;
create extension postgis_topology;
create extension fuzzystrmatch;
create extension address_standardizer;
create extension address_standardizer_data_us;
create extension postgis_tiger_geocoder;
CREATE TABLE sensor_data2 (
time timestamptz,
sensor_id integer,
location geometry(Point),
temperature double precision
);
INSERT INTO sensor_data2 (time, sensor_id, location, temperature)
VALUES ('2023-09-29 12:00:00', 1, ST_GeomFromText('POINT(123.333 31.555)', 4326), 25.5);

-- 创建超表，使用 "time" 作为时间列，使用 "mssi" 作为分布键，并指定分区数为 10
SELECT create_hypertable('public.shiphistory', 'time', 'mssi', 32767);

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
-- 创建一个新的超表
CREATE TABLE "public"."shiphistory" (
"time" timestamptz(6) NOT NULL,
"name" varchar(128) COLLATE "pg_catalog"."default" NOT NULL,
"mssi" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
"create_time" timestamptz(6) NOT NULL,
"lat" float8 NOT NULL DEFAULT 0.0,
"lon" float8 NOT NULL DEFAULT 0.0,
"heading" float8,
"speed" float8,
"location" geometry(Point)
);
-- 插入数据，如果子表不存在，它将自动创建
INSERT INTO public.shiphistory ("time", "name", "mssi", "create_time", "lat", "lon", "heading", "speed","location")
VALUES ('2023-09-29 12:00:00', 'Ship1', '1234', '2023-09-29 12:00:00', 38.123, -77.456, 90.0, 10.0);

INSERT INTO sensor_data2 (time, sensor_id, location, temperature)
VALUES ('2023-09-29 12:00:00', 1, ST_GeomFromText('POINT(123.333 31.555)', 4326), 25.5);

INSERT INTO public.shiphistory ("time", "name", "mssi", "create_time", "lat", "lon", "heading", "speed","location")
SELECT now(),name,
mssi,
create_time,
lat,
lon,
heading,
speed, ST_GeomFromText('POINT(' || lon || ' ' || lat || ')', 4326) from shipdynamicpath

SELECT now(),name,
mssi,
create_time,
lat,
lon,
heading,
speed, ST_GeomFromText('POINT(' || lon || ' ' || lat || ')', 4326) from shipdynamicpath limit 10;

-- 将新的超表转换为分布式超表
SELECT create_distributed_hypertable('public.new_shiphistory', 'new_distribution_key');
INSERT INTO "_timescaledb_internal"."_hyper_1_10_chunk" ("time", "name", "mssi", "create_time", "lat", "lon", "heading", "speed", "location") VALUES ('2023-09-29 14:49:34.980999+08', '航拖4002', '413374950', '2023-09-29 15:00:00.543994+08', '32.252695', '121.71682', '222', '9.60000038146973', ST_GeomFromText('POINT(121.61682 32.252695)'));

有个操蛋的问题，随机生成的表都是_hyper_2_50_chunk这种，然后我要根据mssi号来查询，拼接了下。创建
DROP TABLE IF EXISTS "_timescaledb_internal"."mssi_result";
CREATE TABLE "_timescaledb_internal"."mssi_result" (
"table_name" text COLLATE "pg_catalog"."default",
"mssi" varchar(64) COLLATE "pg_catalog"."default"
)
;

SELECT table_name,
(SELECT * FROM timescaledb_internal || '.' || table_name LIMIT 1) AS first_row
FROM "_timescaledb_internal".tables
WHERE table_schema = 'timescaledb_internal'
AND table_type = 'BASE TABLE';

SELECT table_name,
(SELECT * FROM "_timescaledb_internal" || '.' || table_name LIMIT 1) AS first_row
FROM information_schema.tables
WHERE table_schema = '_timescaledb_internal'
AND table_type = 'BASE TABLE';

select * from "_timescaledb_internal"."_hyper_2_33_chunk"

DO $$
DECLARE
table_name_text text;
first_row_record record;
first_row_jsonb jsonb;
query text;
BEGIN
-- 如果临时表已经存在，先删除它
--IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'temp_result') THEN
-- DROP TABLE temp_result;
--END IF;

-- 创建临时表用于存储结果
--CREATE TEMP TABLE temp_result (table_name text, first_row jsonb);

       truncate table mssi_result;

FOR table_name_text IN
SELECT table_name
FROM information_schema.tables
WHERE table_schema = '_timescaledb_internal'
AND table_type = 'BASE TABLE' AND table_name LIKE '%_hyper%'

LOOP
-- 构建查询
--query := 'SELECT * FROM ' || table_name_text || ' LIMIT 1';
               query := 'SELECT mssi FROM ' || table_name_text || ' LIMIT 1';

-- 执行查询并将结果存储在 first_row_record 中
EXECUTE query INTO first_row_record;

-- 将 record 转换为 jsonb
--first_row_jsonb := to_jsonb(first_row_record);

-- 将 table_name_text 和 first_row_jsonb 插入到 temp_result 表中
--INSERT INTO temp_result (table_name, first_row) VALUES (table_name_text, first_row_jsonb);
               INSERT INTO mssi_result (table_name, mssi) VALUES (table_name_text, first_row_record.mssi);
END LOOP;

-- 输出 temp_result 表中的所有记录
--FOR first_row_record IN SELECT * FROM temp_result
--LOOP
-- 现在您可以在 first_row_record 变量中处理结果
-- 例如，您可以使用 first_row_record.table_name 和 first_row_record.first_row 访问具体字段

--RAISE NOTICE 'Table: %, First Row: %', first_row_record.table_name, first_row_record.first_row;
--END LOOP;
END $$;

整理下，在public 下创建函数
CREATE OR REPLACE FUNCTION process_hyper_tables_and_mssi()
RETURNS void AS $$
DECLARE
table_name_text text;
first_row_record record;
query text;
BEGIN
-- 清空表 _timescaledb_internal.mssi_result
TRUNCATE TABLE _timescaledb_internal.mssi_result;

FOR table_name_text IN
SELECT table_name
FROM information_schema.tables
WHERE table_schema = '_timescaledb_internal'
AND table_type = 'BASE TABLE' AND table_name LIKE '%_hyper%'
LOOP
-- 构建查询
query := 'SELECT mssi FROM _timescaledb_internal.' || table_name_text || ' LIMIT 1';

-- 执行查询并将结果存储在 first_row_record 中
EXECUTE query INTO first_row_record;

-- 将结果插入表 _timescaledb_internal.mssi_result
INSERT INTO _timescaledb_internal.mssi_result (table_name, mssi) VALUES (table_name_text, first_row_record.mssi);
END LOOP;
END;
$$ LANGUAGE plpgsql;

.修改分区时间 set_chunk_time_interval（）
1.修改分区时间

SELECT set_chunk_time_interval('超表名', interval '24 hours');
1
2.插入数据验证

INSERT INTO 超表名("collect_time", "code", "value", "create_time") VALUES ('2021-1-14 16:35:00', '375222D001', '27.7932', '2020-10-14 16:35:15.011');
INSERT INTO 超表名("collect_time", "code", "value", "create_time") VALUES ('2021-1-15 16:35:00', '3715044111', '0.0000', '2020-10-14 16:35:20.389');
INSERT INTO 超表名("collect_time", "code", "value", "create_time") VALUES ('2021-1-16 16:35:00', '202Q0019QT001', '0.3663', '2020-10-14 16:35:19.087');
INSERT INTO 超表名("collect_time", "code", "value", "create_time") VALUES ('2021-1-17 16:35:00', '3702000284441', '22.2946', '2020-10-14 16:35:15.035');
INSERT INTO 超表名("collect_time", "code", "value", "create_time") VALUES ('2021-1-18 16:35:00', '37075225555501', '0.3022', '2020-10-14 16:35:24.041');
INSERT INTO 超表名("collect_time", "code", "value", "create_time") VALUES ('2021-1-19 16:35:00', '25555222206001', '0.0000', '2020-10-14 16:35:23.956');
1
2
3
4
5
6
三.查看修改结果
查看_timescaledb_catalog.dimension 表
变成 86400000000 了
2.查看分区
分区也多了

还有第2种（未测试）
我想能不能直接"_timescaledb_catalog".“dimension” 表的 interval_length 字段直接改为86400000000