使用Python DataFrameClient查询InfluxDB返回空字典问题

背景信息 #

• 已通过Python成功创建InfluxDB数据库，代码如下：

from influxdb import InfluxDBClient

host     = 'xx.xxx.xx.xxx'
port     = 8086
username = 'user'
password = 'password'
database = 'dbname'

client = InfluxDBClient(host       = host, 
                        port       = port, 
                        username   = username, 
                        password   = password,
                        verify_ssl = True)

client.create_database(database)

• 调用client.get_list_database()确认数据库创建成功，返回结果为[{'name':'dbname'}]
• 已通过DataFrameClient将时序数据写入Hourly_data measurement，且Grafana确认数据已成功存储（写入的DataFrame以date为datetime类型索引，包含多变量时序数据），写入代码如下：

def data_to_influxdb(data,table_name):
    connection_info = {'host'         : "XX.XXX.XX.XXX",
                       'port'        : 8086,
                       'user'        : 'user',
                       'password'    : 'password',
                       'database'    : 'dbname',
                       'server_tz'   : 'Atlantic/Canary',
                       'reference_tz': 'UTC'}

    client = DataFrameClient(connection_info['host'], connection_info['port'], \
                            connection_info['user'], connection_info['password'], \
                            connection_info['database'])
    
    client.write_points(data, measurement=table_name, \
                        database=connection_info['database'], protocol='json',
                        batch_size = 20000)
    
    # Close session
    client.close()
    
    return


table_name = 'Hourly_data'
data_to_influxdb(dataFrame, table_name)

问题现象 #

使用DataFrameClient查询数据时始终返回空字典，尝试多种查询语句均无效：

connection_info = {'host'        : "XX.XXX.XX.XXX",
                    'port'        : 8086,
                    'user'        : 'user',
                    'password'    : 'password',
                    'database'    : 'dbname'}

client = DataFrameClient(
                         host       = connection_info['host'], 
                         port       = connection_info['port'],
                         username   = connection_info['user'], 
                         password   = connection_info['password'], 
                         database   = connection_info['database'],
                         )

query = 'SELECT "*" FROM "dbname"."autogen"."Hourly_data"'
client.query(query = query)

返回结果为{}。

尝试其他查询语句也返回空字典：

query = 'SELECT * FROM dbname'
query = 'SELECT * FROM XXX' # 故意使用错误名称仍返回空字典{}

已排查项 #

• 检查保留策略，结果显示正常：

client.query('SHOW RETENTION POLICIES')
# 返回：ResultSet({'('results', None)': [{'name': 'autogen', 'duration': '0s', 'shardGroupDuration': '168h0m0s', 'replicaN': 1, 'default': True}]})

解决方案 #

1. 修正查询语句格式 #

当DataFrameClient初始化时已指定数据库，查询语句无需重复包含数据库名，且measurement名称需用双引号包裹（如果名称包含大小写或特殊字符），正确语句示例：

# 方式1：指定默认保留策略autogen
query = 'SELECT * FROM "autogen"."Hourly_data"'
# 方式2：省略默认保留策略（autogen是默认值）
query = 'SELECT * FROM "Hourly_data"'

2. 保持时区配置一致 #

写入数据时指定了server_tz和reference_tz，查询时需同步配置这两个参数，避免时区偏移导致数据查询不到：

client = DataFrameClient(
    host=connection_info['host'],
    port=connection_info['port'],
    username=connection_info['user'],
    password=connection_info['password'],
    database=connection_info['database'],
    server_tz='Atlantic/Canary',
    reference_tz='UTC'
)

3. 用基础InfluxDBClient验证查询 #

先切换到InfluxDBClient测试查询是否能返回数据，排除DataFrameClient的适配问题：

from influxdb import InfluxDBClient
client = InfluxDBClient(
    host=connection_info['host'],
    port=connection_info['port'],
    username=connection_info['user'],
    password=connection_info['password'],
    database=connection_info['database']
)
result = client.query('SELECT * FROM "Hourly_data" LIMIT 1')
print(result.raw)

如果此代码能返回数据，说明问题出在DataFrameClient的配置；如果仍返回空，需检查写入数据时的时间格式、标签/字段是否符合InfluxDB要求。

4. 获取最新数据时间的高效查询 #

当查询正常后，可使用以下语句直接获取最新数据的时间：

用InfluxDBClient：

query = 'SELECT last(*) FROM "Hourly_data"'
result = client.query(query)
for point in result.get_points():
    print("最新数据时间：", point['time'])

用DataFrameClient：

df_result = client.query('SELECT * FROM "Hourly_data"')['Hourly_data']
latest_time = df_result.index[-1]
print("最新数据时间：", latest_time)

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