timescaledb学习

本文介绍了如何在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

### TimescaleDB 使用指南与功能介绍 TimescaleDB 是一种专为时序数据设计的开源数据库扩展,基于 PostgreSQL 构建[^3]。它能够高效处理大规模时间序列数据,适用于 IoT、监控、事件记录和告警等场景。以下是关于 TimescaleDB 的使用指南和功能介绍: #### 1. 安装方法 安装 TimescaleDB 可以通过以下几种方式实现: - **二进制包安装**:大多数 Linux 发行版(如 Ubuntu/Debian 和 RHEL/CentOS)提供了官方的二进制包,可以直接通过包管理器安装。 - **源码编译**:如果需要从源码构建 TimescaleDB,可以参考官方文档中的详细步骤,包括安装必要的开发包(如 `postgresql-server-dev-xx` 对于 Ubuntu/Debian 系统)[^4]。 - **Docker 镜像**:对于快速测试或开发环境,可以通过 Docker 快速启动一个包含 TimescaleDB 的容器。 #### 2. 快速入门 在安装完成后,可以通过以下命令创建 TimescaleDB 扩展并开始使用: ```sql CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; ``` 此命令会初始化 TimescaleDB 并启用其功能[^1]。 #### 3. 核心功能 - **自动分区**:TimescaleDB 自动将数据按时间范围分区,从而提高查询性能和存储效率。 - **SQL 兼容性**:由于基于 PostgreSQL,TimescaleDB 支持标准 SQL 查询,同时提供了额外的时间序列优化函数。 - **高性能写入**:即使在高并发场景下,TimescaleDB 也能保持较高的写入吞吐量。 - **灵活的聚合查询**:支持复杂的时间序列聚合操作,例如按时间段统计、滑动窗口计算等。 - **可扩展性**:支持水平扩展和分布式部署,适合处理超大规模数据集。 #### 4. 性能特点 相比其他数据库系统(如 DolphinDB),TimescaleDB 在导入速率上可能略逊一筹,尤其是在大数据量场景下,其导入速率可能会随时间下降[^2]。然而,TimescaleDB 的优势在于其对 PostgreSQL 的深度集成,提供了强大的 SQL 功能和生态支持。 #### 5. 学习资源 - **官方文档**:提供全面的入门教程和技术细节,包括架构设计、API 参考和最佳实践。 - **示例项目**:官方仓库中包含多个示例项目,帮助用户快速上手。 - **社区支持**:活跃的开发者社区和论坛可以解答各种技术问题。 ### 示例代码 以下是一个简单的示例,展示如何创建一个时间序列表并插入数据: ```sql -- 创建时间序列表 CREATE TABLE conditions ( time TIMESTAMPTZ NOT NULL, location TEXT NOT NULL, temperature DOUBLE PRECISION NULL, humidity DOUBLE PRECISION NULL ); -- 将表转换为时间序列表 SELECT create_hypertable('conditions', 'time'); -- 插入数据 INSERT INTO conditions (time, location, temperature, humidity) VALUES (NOW(), 'Office', 70.0, 50.0); ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值