教程

交通和地理空间数据分析

Timescale Cloud：性能、规模、企业级

自托管产品

MST

实时分析是指在数据生成时即时收集、分析和解释数据的过程。这种方法使您能够跟踪和监控活动，并根据存储在 Timescale Cloud 服务中的实时数据洞察做出决策。

Real-time analytics geolocation

本页面展示了如何将 Grafana 与 Timescale Cloud 服务集成，并通过可视化列式存储中经过大小和速度优化的数据来获取洞察。

前提条件

要遵循本页的步骤

优化超表中的时间序列数据

时间序列数据表示系统、过程或行为随时间变化的方式。超表使 TimescaleDB 能够高效处理时间序列数据。超表是 PostgreSQL 表，它们根据时间自动对时间序列数据进行分区。每个超表由称为分块的子表组成。每个分块都分配一个时间范围，并且只包含该范围内的数据。当您运行查询时，TimescaleDB 会识别正确的分块并在其上运行查询，而不是遍历整个表。

Hypercore 是 Timescale 混合行-列式存储引擎，由超表使用。传统数据库需要在快速插入（行式存储）和高效分析（列式存储）之间进行权衡。Hypercore 消除了这种权衡，允许实时分析而无需牺牲事务处理能力。

Hypercore 动态地以其生命周期中最有效的格式存储数据

  • 用于最新数据的行式存储：最新的分块（可能更多）始终存储在行式存储中，确保快速插入、更新和低延迟的单记录查询。此外，行式存储还用作插入和更新到列式存储的直写（writethrough）。
  • 用于分析性能的列式存储：分块自动压缩到列式存储中，优化存储效率并加速分析查询。

与传统列式数据库不同，Hypercore 允许在任何阶段插入或修改数据，使其成为高摄取事务性工作负载和实时分析的灵活解决方案——都在单个数据库中。

由于 TimescaleDB 是 100% PostgreSQL，您可以将所有标准的 PostgreSQL 表、索引、存储过程和其他对象与您的超表一起使用。这使得创建和使用超表类似于标准的 PostgreSQL。

  1. 将时间序列数据导入超表

    1. 解压

      nyc_data.tar.gz
      到 <本地文件夹>。

      此测试数据集包含纽约黄色出租车网络的历史数据。

      要直接从当前基于 PostgreSQL 的数据库导入高达 100GB 的数据，请使用原生 PostgreSQL 工具 进行停机迁移。要无缝导入 100GB-10TB+ 的数据，请使用 Timescale 提供的 实时迁移 工具。要从非 PostgreSQL 数据源添加数据，请参阅 导入和摄取数据。

    2. 在终端中，导航到 <本地文件夹> 并使用 您的连接详情 更新以下字符串以连接到您的服务。

      psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>?sslmode=require"

    3. 为您的时间序列数据创建优化的超表

      1. 使用 CREATE TABLE 为您的时间序列数据创建一个默认启用 hypercore 的 超表。为了在列式存储中对数据进行 高效查询，请记住用您最常用于过滤数据的列进行 segmentby。

        在您的 sql 客户端中，运行以下命令

        CREATE TABLE "rides"(
          vendor_id TEXT,
          pickup_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
          dropoff_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
          passenger_count NUMERIC,
          trip_distance NUMERIC,
          pickup_longitude  NUMERIC,
          pickup_latitude   NUMERIC,
          rate_code         INTEGER,
          dropoff_longitude NUMERIC,
          dropoff_latitude  NUMERIC,
          payment_type INTEGER,
          fare_amount NUMERIC,
          extra NUMERIC,
          mta_tax NUMERIC,
          tip_amount NUMERIC,
          tolls_amount NUMERIC,
          improvement_surcharge NUMERIC,
          total_amount NUMERIC
        ) WITH (
          tsdb.hypertable,
          tsdb.partition_column='pickup_datetime',
          tsdb.create_default_indexes=false,
          tsdb.segmentby='vendor_id',
          tsdb.orderby='pickup_datetime DESC'
        );

        如果您自托管 TimescaleDB v2.19.3 及更早版本，请创建一个 PostgreSQL 关系表，然后使用 create_hypertable 进行转换。然后通过调用 ALTER TABLE 启用 hypercore。

      2. 添加另一个维度以更有效地划分超表

        SELECT add_dimension('rides', by_hash('payment_type', 2));

      3. 创建索引以支持按供应商、费率代码和乘客数量进行高效查询

        CREATE INDEX ON rides (vendor_id, pickup_datetime DESC);
        CREATE INDEX ON rides (rate_code, pickup_datetime DESC);
        CREATE INDEX ON rides (passenger_count, pickup_datetime DESC);

    4. 为关系数据创建 PostgreSQL 表

      1. 添加一个表来存储支付类型数据

        CREATE TABLE IF NOT EXISTS "payment_types"(
          payment_type INTEGER,
          description TEXT
        );
        INSERT INTO payment_types(payment_type, description) VALUES
          (1, 'credit card'),
          (2, 'cash'),
          (3, 'no charge'),
          (4, 'dispute'),
          (5, 'unknown'),
          (6, 'voided trip');

      2. 添加一个表来存储费率数据

        CREATE TABLE IF NOT EXISTS "rates"(
         rate_code   INTEGER,
         description TEXT
        );
        INSERT INTO rates(rate_code, description) VALUES
         (1, 'standard rate'),
         (2, 'JFK'),
         (3, 'Newark'),
         (4, 'Nassau or Westchester'),
         (5, 'negotiated fare'),
         (6, 'group ride');

    5. 将数据集上传到您的服务

      \COPY rides FROM nyc_data_rides.csv CSV;

  2. 快速查看您的数据

    查询超表的方式与查询关系型 PostgreSQL 表完全相同。使用以下任一 SQL 编辑器运行查询并查看您上传的数据

    • 数据模式：在 Timescale Console 中编写查询、可视化数据并分享结果，适用于您的所有 Timescale Cloud 服务。
    • SQL 编辑器：在 Timescale Console 中更快、更准确地编写、修复和组织 SQL，适用于 Timescale Cloud 服务。
    • psql：从终端轻松地在您的 Timescale Cloud 服务或自托管 TimescaleDB 部署上运行查询。

    例如

    • 显示每种票价类型的乘车次数

      SELECT rate_code, COUNT(vendor_id) AS num_trips
      FROM rides
      WHERE pickup_datetime < '2016-01-08'
      GROUP BY rate_code
      ORDER BY rate_code;

      这个简单的查询在 3 秒内运行完成。您会看到类似以下内容：

      费率代码行程次数
      12266401
      254832
      34126
      4967
      57193
      617
      9942

    • 要选择 2016 年 1 月第一周的所有乘车记录，并返回每种费率代码的总行程次数

      SELECT rates.description, COUNT(vendor_id) AS num_trips
      FROM rides
      JOIN rates ON rides.rate_code = rates.rate_code
      WHERE pickup_datetime < '2016-01-08'
      GROUP BY rates.description
      ORDER BY LOWER(rates.description);

      对于如此大量的数据，这个对行式存储数据的分析查询大约需要 59 秒。您会看到类似以下内容：

      描述行程次数
      拼车17
      JFK54832
      拿骚或威彻斯特967
      议定票价7193
      纽瓦克4126
      标准费率2266401

优化数据以进行实时分析

当 Timescale Cloud 将一个分块转换为列式存储时，TimescaleDB 会自动为您的数据创建一个不同的模式。当您写入和从列式存储读取时，TimescaleDB 会创建并使用自定义索引来包含 segmentby 和 orderby 参数。

要将分析查询的速度提高 10 倍，并将存储成本降低高达 90%，请将数据转换为列式存储

  1. 连接到您的 Timescale Cloud 服务

    在 Timescale Console 中打开一个 SQL 编辑器。控制台中的编辑器会显示查询速度。您也可以使用 psql 连接到您的服务。

  2. 添加策略以在特定时间间隔将分块转换为列式存储

    例如，将超过 8 天的数据转换为列式存储

    CALL add_columnstore_policy('rides', INTERVAL '8 days');

    请参阅 add_columnstore_policy。

    您为本教程导入的数据是 2016 年的，默认情况下已添加到列式存储中。不过，您应该明白了。要查看实际的空间节省，请按照 尝试 Timescale 的主要功能 进行操作。

为了更清楚地说明这一点，通过将冷却数据转换为列式存储，您的分析查询速度提高了 10 倍，存储空间减少了高达 90%。

将 Grafana 连接到 Timescale Cloud

要可视化查询结果，请启用 Grafana 以读取服务中的数据

  1. 登录 Grafana

    在您的浏览器中，登录以下任一

    • 自托管 Grafana：位于 http://localhost:3000/。默认凭据是 admin，admin。
    • Grafana Cloud：使用您创建账户时设置的 URL 和凭据。

  2. 将您的服务添加为数据源

    1. 打开 Connections > Data sources，然后点击 Add new data source。

    2. 从列表中选择 PostgreSQL。

    3. 配置连接

      • 主机 URL、数据库名称、用户名 和 密码

        使用您的 连接详情 进行配置。主机 URL 的格式为 <host>:<port>。

      • TLS/SSL 模式：选择 require。

      • PostgreSQL 选项：启用 TimescaleDB。

      • 其他字段保留默认设置。

    4. 点击 保存并测试。

      Grafana 会检查您的详细信息是否设置正确。

监控随时间变化的性能

Grafana 仪表板表示系统性能的视图，每个仪表板由一个或多个面板组成，这些面板表示与该系统相关的特定指标信息。

要可视化监控出租车乘车量的变化

  1. 创建仪表板

    1. 在 Dashboards 页面上，点击 New 并选择 New dashboard。

    2. 点击 添加可视化。

    3. 选择连接到您的 Timescale Cloud 服务的数据源。默认选择 时间序列 可视化。

      Grafana create dashboard

    4. 在 查询 部分，选择 代码，然后在 格式 中选择 时间序列。

    5. 选择可视化数据范围：数据集来自 2016 年。点击面板上方的日期范围并设置

      • 从：2016-01-01 01:00:00
      • 到：2016-01-30 01:00:00

  2. 结合 TimescaleDB 和 Grafana 功能分析您的数据

    结合 TimescaleDB 的 time_bucket 函数与 Grafana 的 $__timefilter() 函数，将 pickup_datetime 列设置为可视化的过滤范围。

    SELECT
      time_bucket('1 day', pickup_datetime) AS "time",
      COUNT(*)
    FROM rides
    WHERE $__timeFilter(pickup_datetime)
    GROUP BY time
    ORDER BY time;

    此查询按天分组结果并按时间排序。

    Grafana real-time analytics

  3. 点击 保存仪表板

优化收入潜力

拥有所有这些数据固然很好，但您如何利用它呢？监控数据对于检查已发生的事情很有用，但您如何分析这些信息以发挥您的优势呢？本节解释了如何创建可视化，展示如何最大限度地提高潜在收入。

设置您的数据以进行地理空间查询

为了向您的乘车次数可视化添加地理空间分析，您需要地理空间数据来确定行程的始发地。由于 TimescaleDB 与所有 PostgreSQL 扩展兼容，因此使用 PostGIS 按时间和位置对数据进行切片。

  1. 连接到您的 Timescale Cloud 服务 并添加 PostGIS 扩展

    CREATE EXTENSION postgis;

  2. 为上车和下车位置添加几何列

    ALTER TABLE rides ADD COLUMN pickup_geom geometry(POINT,2163);
    ALTER TABLE rides ADD COLUMN dropoff_geom geometry(POINT,2163);

  3. 将经纬度点转换为与 PostGIS 兼容的几何坐标

    UPDATE rides SET pickup_geom = ST_Transform(ST_SetSRID(ST_MakePoint(pickup_longitude,pickup_latitude),4326),2163),
       dropoff_geom = ST_Transform(ST_SetSRID(ST_MakePoint(dropoff_longitude,dropoff_latitude),4326),2163);

    这将更新两列共 10,906,860 行数据，需要一些时间。喝杯咖啡吧。

可视化您可以赚取最多钱的区域

在本节中，您将可视化一个查询，该查询返回时代广场 2 公里范围内行程超过 5 英里的乘车记录。数据包括行程距离，并按 trip_distance 和位置进行 GROUP BY，以便 Grafana 可以正确绘制数据。

这使您能够看到出租车司机最有可能在哪里接到想要长途乘车的乘客，从而赚取更多钱。

  1. 创建地理定位仪表板

    1. 在 Grafana 中，创建一个连接到 Timescale Cloud 服务数据源并使用 Geomap 可视化的新仪表板。

    2. 在 查询 部分，选择 代码，然后在 格式 中选择 时间序列。

      Real-time analytics geolocation

    3. 要在曼哈顿查找超过 5 英里的乘车记录，请粘贴以下查询

      SELECT time_bucket('5m', rides.pickup_datetime) AS time,
             rides.trip_distance AS value,
             rides.pickup_latitude AS latitude,
             rides.pickup_longitude AS longitude
      FROM rides
      WHERE rides.pickup_datetime BETWEEN '2016-01-01T01:41:55.986Z' AND '2016-01-01T07:41:55.986Z' AND
        ST_Distance(pickup_geom,
                    ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)
        ) < 2000
      GROUP BY time,
               rides.trip_distance,
               rides.pickup_latitude,
               rides.pickup_longitude
      ORDER BY time
      LIMIT 500;

      您会看到一个世界地图，上面有一个点在纽约。

    4. 放大您的地图以清晰地看到可视化效果。

  2. 自定义可视化

    1. 在 Geomap 选项中，在 地图图层 下，点击 + 添加图层 并选择 热力图。现在您可以看到出租车司机最有可能接到想要长途乘车的乘客，从而赚取更多钱的区域。

      Real-time analytics geolocation

您已将 Grafana 与 Timescale Cloud 服务集成，并根据数据可视化获得了洞察。

关键词

物联网模拟

本页有任何问题吗？报告问题 或在 GitHub 上编辑此页。

上一页教程下一页能源消耗分析

相关内容

模拟 IoT 传感器数据集
在测试新系统时，通常需要模拟 IoT 数据集。在您的 Timescale Cloud 服务中模拟 IoT 数据集
Timescale Cloud 和 Grafana 的实时分析
在您的 Timescale Cloud 服务中模拟 IoT 数据集
教程
Timescale Cloud 可用于各种行业和各种目的。请跟随我们的教程，了解它在能源、金融、物联网及其他行业的实时数据分析中的应用
Timescale 手册
浏览 Timescale Cloud 社区的代码示例，解决常见问题并提供非标准任务的解决方案
查询比特币区块链
了解如何使用 Timescale Cloud 查询和分析区块链上的交易，就像分析任何其他时间序列数据一样。在本教程中，您将使用 Timescale Cloud 摄取、存储和分析交易
分析比特币区块链
使用 Timescale Cloud hyperfunctions 分析比特币区块链上的交易。本教程演示了如何分析费用、对 BTC-USD 的影响、平均矿工收入等