Блог

Я использую SQL-плагин Grafana для запроса базы данных TimescaleDB.

БД хранит информацию о погоде в виде

 | timestamp | location_id | data_type_id | value |
 

где location_id и data_type_id являются внешними ключами к таблице locations , описывающей местоположения и weather_data_types определяющей типы измерений (температура, относительная влажность, …).

Я хотел бы запросить данные в диапазоне времени, сгруппированные по местоположению и типу.

Мне удается группироваться по одному из них, но не по обоим.

Это работает и группируется по местоположению:

 SELECT
  $__timeGroupAlias("timestamp", $__interval),
  avg(value),
  locations.name
FROM weather_data
JOIN locations ON weather_data.location_id = locations.id
GROUP BY 1, locations.name
ORDER BY 1
 

Это работает и группируется по типу:

 SELECT
  $__timeGroupAlias("timestamp", $__interval),
  avg(value),
  weather_data_types.name
FROM weather_data
JOIN weather_data_types ON weather_data.type_id = weather_data_types.id
GROUP BY 1, weather_data_types.name
ORDER BY 1
 

Это не работает:

 SELECT
  $__timeGroupAlias("timestamp", $__interval),
  avg(value),
  locations.name,
  weather_data_types.name
FROM weather_data
JOIN locations ON weather_data.location_id = locations.id
JOIN weather_data_types ON weather_data.type_id = weather_data_types.id
GROUP BY 1, locations.name, weather_data_types.name
ORDER BY 1
 

Более конкретно, я получаю следующую ошибку

 Value column must have numeric datatype, column: name type: string value: relative_humidity
 

Кажется, что третий groupby (молча) не выполняется и weather_data_types.name возвращается, на что жалуется Grafana, потому что он не может отображать строки.

Изменение этого параметра на возврат (целого) идентификатора вместо этого удаляет сообщение об ошибке

 SELECT
  $__timeGroupAlias("timestamp", $__interval),
  avg(value),
  locations.name,
  weather_data_types.id
FROM weather_data
JOIN locations ON weather_data.location_id = locations.id
JOIN weather_data_types ON weather_data.type_id = weather_data_types.id
GROUP BY 1, locations.name, weather_data_types.id
ORDER BY 1
 

но отображаются две серии: avg и id , которые показывают, что тип groupby не применяется.

Что-то не так в моем запросе? Это проблема с плагином Grafana?

Я не думаю, что это имеет значение, но вот модель, определенная с помощью SQLAlchemy и, надеюсь, понятная.

 class Location(Base):
    __tablename__ = "locations"

    id = sqla.Column(sqla.Integer, primary_key=True)
    name = sqla.Column(sqla.String(80), unique=True, nullable=False)
    country = sqla.Column(sqla.String(80), nullable=False)
    latitude = sqla.Column(sqla.Float(), nullable=False)
    longitude = sqla.Column(sqla.Float(), nullable=False)


class WeatherDataTypes(Base):
    __tablename__ = "weather_data_types"

    id = sqla.Column(sqla.Integer, primary_key=True)
    name = sqla.Column(sqla.String(80), unique=True, nullable=False)
    description = sqla.Column(sqla.String(500), nullable=False)
    unit = sqla.Column(sqla.String(20), nullable=False)
    min_value = sqla.Column(sqla.Float)
    max_value = sqla.Column(sqla.Float)


class WeatherData(Base):
    __tablename__ = "weather_data"

    timestamp = sqla.Column(sqla.DateTime(timezone=True), primary_key=True)
    location_id = sqla.Column(
        sqla.Integer,
        sqla.ForeignKey('locations.id'),
        nullable=False,
        primary_key=True
    )
    location = sqla.orm.relationship('Location')
    type_id = sqla.Column(
        sqla.Integer,
        sqla.ForeignKey('weather_data_types.id'),
        nullable=False,
        primary_key=True
    )
    type = sqla.orm.relationship('WeatherDataTypes')
    value = sqla.Column(sqla.Float)
 

Отправка запросов непосредственно в postgresql помогла мне понять, что происходит.

По-видимому, когда запрос возвращает столбец значений и столбец строк, плагин Grafana предполагает, что значения должны быть отображены, а столбец string предназначен для использования в качестве меток для графиков.

Я думал, что плагин использовал groupby для сортировки извлечения столбца, чтобы сделать его информацией о метках, но эта магия не работает с двумя строковыми столбцами, поскольку плагин не будет сам объединять значения. Поэтому плагин жалуется на то, что второй строковый столбец не является числами, что вводит в заблуждение, потому что он не будет жаловаться на первый строковый столбец.

Я мог бы заставить его работать, объединив значения, которые я использую для groupby, в один столбец:

 SELECT
  time_bucket('21600s',"timestamp") AS "time",
  avg(value),
  CONCAT(locations.name, ' ', weather_data_types.name) AS "name"
FROM weather_data
JOIN locations ON weather_data.location_id = locations.id
JOIN weather_data_types ON weather_data.type_id = weather_data_types.id
GROUP BY 1, locations.name, weather_data_types.name
ORDER BY 1
 

Это возвращает

           time          |        avg         |           name            
------------------------ -------------------- ---------------------------
 

который правильно интерпретируется плагином.