将DataFrame写入InfluxDB 2.x异常：数据丢失、仪表盘无显示

我在用Python把DataFrame写入InfluxDB时遇到了多个问题：有时写入后生成空表但无报错；有时部分数据丢失也无报错。用Point对象写入的示例完全正常，说明环境配置没问题，核心需求是让DataFrame数据能成功写入，且能在Python查询和InfluxDB仪表盘中正常查看。

1. DataFrame写入丢失数据的复现 #

我用以下代码创建DataFrame：

from datetime import datetime
from datetime import timedelta

_now = datetime.utcnow()
_data_frame = pd.DataFrame(data=[["coyote_creek", 1.0], ["coyote_creek", 2.0]],
                           index=[_now, _now + timedelta(hours=1)],
                           columns=["location", "water_level"])

生成的DataFrame如下：

_data_frame
Out[24]: 
                                location  water_level
2023-03-07 02:04:11.642867  coyote_creek          1.0
2023-03-07 03:04:11.642867  coyote_creek          2.0

执行写入代码：

write_api.write("testing_for_dataframe", "org", record=_data_frame, data_frame_measurement_name='h2o_feet',
                            data_frame_tag_columns=['location'])

预期写入2条数据，但用以下查询仅得到1条：

query_api.query_data_frame('from(bucket:"testing_for_dataframe")|> range(start: -10m)')

查询结果：

Out[31]: 
    result  table  ... _measurement      location
0  _result      0  ...     h2o_feet  coyote_creek
[1 rows x 9 columns]

同时在InfluxDB仪表盘中也只能看到1条数据。

2. Point对象写入正常的对比 #

用Point对象写入的示例完全正常，代码如下：

from influxdb_client import InfluxDBClient, Point, Dialect
from influxdb_client.client.write_api import SYNCHRONOUS

client = InfluxDBClient(url="http://localhost:8086", token="my-token", org="my-org")

write_api = client.write_api(write_options=SYNCHRONOUS)
query_api = client.query_api()

# 准备数据
_point1 = Point("my_measurement").tag("location", "Prague").field("temperature", 25.3)
_point2 = Point("my_measurement").tag("location", "New York").field("temperature", 24.3)

write_api.write(bucket="my-bucket", record=[_point1, _point2])

# 查询：用Pandas DataFrame
data_frame = query_api.query_data_frame('from(bucket:"my-bucket") '
                                        '|> range(start: -10m) '
                                        '|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") '
                                        '|> keep(columns: ["location", "temperature"])')
print(data_frame.to_string())

# 关闭客户端
client.close()

这个示例写入的2条数据在Python查询和仪表盘中都能正常显示。

3. 官方DataFrame写入示例的异常：Python可查但仪表盘不可见 #

我测试了官方的DataFrame写入示例，代码如下：

import pandas as pd

from influxdb_client import InfluxDBClient
from influxdb_client.client.write_api import SYNCHRONOUS, PointSettings

# 从CSV加载DataFrame
df = pd.read_csv("vix-daily.csv")
print(df.head())

with InfluxDBClient(url="http://localhost:8086", token="my-token", org="my-org") as client:
    # 用默认标签导入DataFrame
    point_settings = PointSettings(**{"type": "vix-daily"})
    point_settings.add_default_tag("example-name", "ingest-data-frame")

    write_api = client.write_api(write_options=SYNCHRONOUS, point_settings=point_settings)
    write_api.write(bucket="my-bucket", record=df, data_frame_measurement_name="financial-analysis-df")

    # 查询导入的数据
    query = 'from(bucket:"my-bucket")' \
            ' |> range(start: 0, stop: now())' \
            ' |> filter(fn: (r) => r._measurement == "financial-analysis-df")' \
            ' |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")' \
            ' |> limit(n:10, offset: 0)'
    result = client.query_api().query(query=query)

    # 处理结果
    print()
    print("=== results ===")
    print()
    for table in result:
        for record in table.records:
            print('{4}: Open {0}, Close {1}, High {2}, Low {3}'.format(record["VIX Open"], record["VIX Close"],
                                                                       record["VIX High"], record["VIX Low"],
                                                                       record["type"]))

运行结果显示数据能被Python查询到：

Date  VIX Open  VIX High  VIX Low  VIX Close
0  2004-01-02     17.96     18.68    17.54      18.22
1  2004-01-05     18.45     18.49    17.44      17.49
2  2004-01-06     17.66     17.67    16.19      16.73
3  2004-01-07     16.72     16.75    15.50      15.50
4  2004-01-08     15.42     15.68    15.32      15.61
=== results ===
vix-daily: Open 17.96, Close 18.22, High 18.68, Low 17.54
vix-daily: Open 18.45, Close 17.49, High 18.49, Low 17.44
vix-daily: Open 17.66, Close 16.73, High 17.67, Low 16.19
vix-daily: Open 16.72, Close 15.5, High 16.75, Low 15.5
vix-daily: Open 15.42, Close 15.61, High 15.68, Low 15.32
vix-daily: Open 16.15, Close 16.75, High 16.88, Low 15.57
vix-daily: Open 17.32, Close 16.82, High 17.46, Low 16.79
vix-daily: Open 16.6, Close 18.04, High 18.33, Low 16.53
vix-daily: Open 17.29, Close 16.75, High 17.3, Low 16.4
vix-daily: Open 17.07, Close 15.56, High 17.31, Low 15.49

但这些数据在InfluxDB仪表盘中完全无法看到，这是为什么？

内容的提问来源于stack exchange，提问作者Gusty2000