def _write_point(self):
self.write_client = self.client.write_api(write_options=SYNCHRONOUS)
bucket = self.create_test_bucket()
measurement = "h2o_feet"
field_name = "water_level"
val = "1.0"
tag = "location"
tag_value = "creek level"
p = Point(measurement)
p.field(field_name, val)
p.tag(tag, tag_value)
record_list = [p]
self.write_client.write(bucket.name, self.org, record_list)
query = 'from(bucket:"' + bucket.name + '") |> range(start: 1970-01-01T00:00:00.000000001Z)'
flux_result = self.client.query_api().query(query)
self.assertEqual(1, len(flux_result))
rec = flux_result[0].records[0]
self.assertEqual(self.id_tag, rec["id"])
self.assertEqual(self.customer_tag, rec["customer"])
self.assertEqual("LA", rec[self.data_center_key])
self.delete_test_bucket(bucket)
def getGRCPoints(self, bucket):
points = []
dt = datetime.now(tz=pytz.timezone('US/Pacific')).isoformat()
mag = self.explainMagnitude()
mining = self.getMiningInfo()
for prj in mag.magnitude:
point = Point(measurement_name='magnitude')
point.time(time=dt)
point.tag('project_name', prj.project)
point.field('rac', prj.rac)
point.field('magnitude', prj.magnitude)
points.append({"bucket": bucket, "point": point})
mining_point = Point(measurement_name='mining')
mining_point.time(time=dt)
mining_point.tag('CPID', mining.CPID)
mining_point.field('blocks', mining.blocks)
mining_point.field('magnitude', mining.current_magnitude)
mining_point.field('current_difficulty', mining.difficulty.current)
mining_point.field('pending_reward', mining.BoincRewardPending)
mining_point.field('stake_weight', mining.stakeweight.valuesum)
mining_point.field('time_to_stake', mining.time_to_stake_days)
mining_point.field('staking_efficiency', mining.staking_efficiency)
points.append({"bucket": bucket, "point": mining_point})
return points
def parse(measurement: Measurement) -> Point:
point = Point(measurement.name)
if measurement.groups is not None:
for (key, value) in measurement.groups.items():
point.tag(key, value)
point.field("value", measurement.value).time(measurement.time)
return point
def test_write_points_unicode(self):
bucket = self.create_test_bucket()
measurement = "h2o_feet_ěščřĚŠČŘ"
field_name = "field_ěščř"
utf8_val = "Přerov 🍺"
tag = "tag_ěščř"
tag_value = "tag_value_ěščř"
p = Point(measurement)
p.field(field_name, utf8_val)
p.tag(tag, tag_value)
record_list = [p]
self.write_client.write(bucket.name, self.org, record_list)
query = 'from(bucket:"' + bucket.name + '") |> range(start: 1970-01-01T00:00:00.000000001Z)'
flux_result = self.client.query_api().query(query)
self.assertEqual(1, len(flux_result))
rec = flux_result[0].records[0]
self.assertEqual(self.id_tag, rec["id"])
self.assertEqual(self.customer_tag, rec["customer"])
self.assertEqual("LA", rec[self.data_center_key])
self.assertEqual(measurement, rec.get_measurement())
self.assertEqual(utf8_val, rec.get_value())
self.assertEqual(field_name, rec.get_field())
def write(self,bucket,measurement : str,
time ,
field_list : list,
tag_list : list = [],
**kwargs):
'''
:param bucket : the bucket on which write the data
:param measurement: name of measurement
:param time: timestamp
:param field_list: field list : containing tuple (key,value)
:param tag_list: tag_lisit : containing tuple (key,value)
optionnal parameter
:return:
'''
point = Point(measurement)
point.time(time,WritePrecision.MS)
if not field_list :
# TODO : Create an exception NoDataException
raise Exception("Not point to write in database.")
for field_tuple in field_list:
point.field(field_tuple[0],field_tuple[1])
for tag_tuple in tag_list:
point.tag(tag_tuple[0], tag_tuple[1])
self.write_api.write(bucket=bucket, record=point, org= self.org, **kwargs)
def map_data(self, fields: dict, tags: dict):
point = Point('ups_status')
for k in fields.keys():
point.field(k, fields[k])
for k in tags.keys():
point.tag(k, tags[k])
return point
def _post_telemetry(self) -> None:
"""Post telemetry points"""
if self.telem_logger is not None:
p = Point('sensor_fusion')
p.field('blind_target_bias_mag', np.abs(self.blind_target_bias))
p.field('blind_target_bias_angle',
np.degrees(np.angle(self.blind_target_bias)))
p.tag('units', 'degrees')
p.tag('class', type(self).__name__)
p.time(datetime.utcnow())
self.telem_logger.post_points(p)
def process_camera_frame(self) -> Tuple[Time, Angle, Angle]:
"""Get frame from camera and find target using computer vision
Args:
telem: Dict into which telemetry channels will be added
Returns:
Tuple containing:
- The approximate time that the camera frame was captured.
- The position of the target within the camera frame where the first element is the X
position and the second is the Y position and the origin is the center of the camera
frame.
"""
# This time isn't going to be exceptionally accurate, but unfortunately most cameras do not
# provide a means of determining the exact time when the frame was captured by the sensor.
# There are probably ways to estimate the frame time more accurately but this is likely
# good enough.
target_time = Time.now()
frame = self.camera.get_frame(timeout=self.camera_timeout)
if frame is None:
raise self.IndeterminatePosition(
'Timeout waiting for frame from camera')
keypoints = find_features(frame)
if not keypoints:
self.preview_window.show_annotated_frame(frame)
raise self.IndeterminatePosition(
'No target detected in most recent frame')
# select the keypoint that is most likely to be the target of interest
target_keypoint = self._select_one_keypoint(keypoints)
self.preview_window.show_annotated_frame(frame, keypoints,
target_keypoint)
# convert target position units from pixels to degrees
target_x_px, target_y_px = self._get_keypoint_xy(target_keypoint)
target_x = Angle(target_x_px * self.camera.pixel_scale *
self.camera.binning * u.deg)
target_y = Angle(target_y_px * self.camera.pixel_scale *
self.camera.binning * u.deg)
if self.telem_logger is not None:
p = Point('camera_target')
p.field('x', target_x.deg)
p.field('y', target_y.deg)
p.tag('units', 'degrees')
p.tag('class', type(self).__name__)
p.time(target_time.to_datetime())
self.telem_logger.post_points(p)
return target_time, target_x, target_y
def report_data_list(self, category, host, data):
try:
point = Point(category)
point.tag("host", host)
for field_key, field_value in data:
point.field(field_key, field_value)
point.time(datetime.datetime.utcnow(), WritePrecision.NS)
self._write_client.write(self._settings.bucket,
self._settings.tenant, point)
except Exception as e:
logger.error(f"Failed to report data to InfluxDB: {e}")
def publishData(measurement, tags, time, fields):
p = Point(measurement).time(time)
#log.info("Will plublish %s" % (str(p)))
for key, value in tags.items():
p.tag(key, value)
for key, value in fields.items():
p.field(key, value)
write_api.write(bucket=INFLUX_DB_NAME, record=p)
log.info("Published data point in influxdb.")
def _process_data_row(rowIn, tblFlds, tblName):
point = Point(tblName)
point.time(rowIn['timestamp'], WritePrecision.NS)
for key in tblFlds:
if tblFlds[key] == 'field':
point.field(key, rowIn[key])
elif tblFlds[key] == 'tag':
point.tag(key, rowIn[key])
return point
def save(self, measurement: str, fields: dict, tags: dict):
point = Point(measurement)
for key in fields:
point.field(key, fields[key])
for key in tags:
point.tag(key, tags[key])
point.time(datetime.utcnow(), WritePrecision.NS)
self.__write_api__.write(INFLUX_BUCKET, INFLUX_ORG, point)
def update_influx(self):
pp = []
for date, fields in DATA.italy.to_dict('index').items():
p = Point("italia")
p.time(date.value)
for k, v in fields.items():
if type(v) is str:
p.tag(k, v)
if not np.isnan(v):
p.field(k, v)
pp.append(p)
self.write.write("coviddi", "coviddi", pp)
def point(reg):
fields = struct.unpack('I32B', reg)
words = fields[1:]
words_idx = [w for w in range(31) if w % 5 != 0]
p = Point('frame')
p.time(fields[0])
for idx in words_idx:
p.field(f'word_{idx:02d}', words[idx])
frame_number = (words[2] >> 2) & 0x3
p.tag('frame_number', frame_number)
if frame_number == 0 and words[7] == 0b11110100:
p.tag('start_of_group', True)
return p
def write_point(measurement):
point = Point(measurement["name"])
for tag in measurement["tags"]:
point = point.tag(tag, measurement["tags"][tag])
for field in measurement["fields"]:
point = point.field(field, measurement["fields"][field])
point = point.time(datetime.utcnow(), WritePrecision.NS)
write_api.write(bucket, org, point)
def writeData(self, database, name, data, tag=None):
self.connect()
write_api = self.client.write_api(write_options=SYNCHRONOUS)
try:
p = Point(name)
if tag is not None:
p.tag(*tag)
for key, value in data.items():
p.field(key, value)
logger.debug("Writing Data to DB: %s", p)
write_api.write(bucket=database, record=p)
except Exception:
logger.exception("Exception writing data: %s", p)
self.client = None
def add_data_point(self, field_name, field_value, tags=None):
point = Point(self.name)
point.field(field_name, field_value)
if tags is not None:
for tag_key, tag_value in tags.items():
point = point.tag(tag_key, tag_value)
self.records.append(point)
def main():
global device_details
#get_devices()
#print(device_details)
get_devices_refresh()
cache_timestamp = datetime.datetime.now()
for line in sys.stdin:
#May be able to adjust this logic to have seperate timers for each app_id
#Update cache if older than 1 hour
if (datetime.datetime.now() -
cache_timestamp).seconds >= 3600 and len(device_details) > 0:
#refresh every hour
get_devices_refresh()
#re-Set timestamp
cache_timestamp = datetime.datetime.now()
#Parse timeprotocol to Points type
lproto = line_protocol_parser.parse_line(line)
#parse topic tag to return the device_id and app_id eg: v3/fort-digital-agri@ttn/devices/2027a0023/up v3/{{app_id}}@{{tenant}}/devices/{{device_id}}/up
topic_match = topic_re.match(lproto['tags']['topic'])
app_id = topic_match[1]
#tenant = topic_match[2]
device_id = topic_match[3]
point = Point(lproto['measurement']).time(lproto['time'])
if app_id not in device_details or device_id not in device_details[
app_id]:
get_devices(app_id)
if app_id in device_details:
for key, value in lproto['fields'].items():
point = point.field(key, will_it_float(value))
for key, value in lproto['tags'].items():
if key != 'name':
point = point.tag(key, value)
#get details from global variable
device = get_device(app_id, device_id)
#Add additional tags
point = point.tag("device_id", device_id)
for key, value in device.items():
point = point.tag(key, value)
#Print lineprotocol to stdout
print(point.to_line_protocol())
#flushstdout
sys.stdout.flush()
else:
print("Device Details not found")
def send_dict_influxdb(user, pointName, mainObject, field=False):
"""
format dict to send data at influxDb
:param user: name on the user
:param pointName: name on the point
:param mainObject: object to iterate
:param field: optional tag
"""
if field:
toIterate = mainObject.get(field)
else:
toIterate = mainObject
for key in toIterate:
point = Point(pointName).tag("host", user)
if field:
point.tag("data", field)
point.field(key, toIterate.get(key)).time(datetime.utcnow(), WritePrecision.NS)
write_api.write(bucket, org, point)
def test_write_using_default_tags(self):
bucket = self.create_test_bucket()
measurement = "h2o_feet"
field_name = "water_level"
val = "1.0"
val2 = "2.0"
tag = "location"
tag_value = "creek level"
p = Point(measurement)
p.field(field_name, val)
p.tag(tag, tag_value)
p.time(1)
p2 = Point(measurement)
p2.field(field_name, val2)
p2.tag(tag, tag_value)
p2.time(2)
record_list = [p, p2]
self.write_client.write(bucket.name, self.org, record_list)
query = 'from(bucket:"' + bucket.name + '") |> range(start: 1970-01-01T00:00:00.000000001Z)'
flux_result = self.client.query_api().query(query)
self.assertEqual(1, len(flux_result))
rec = flux_result[0].records[0]
rec2 = flux_result[0].records[1]
self.assertEqual(self.id_tag, rec["id"])
self.assertEqual(self.customer_tag, rec["customer"])
self.assertEqual("LA", rec[self.data_center_key])
self.assertEqual(self.id_tag, rec2["id"])
self.assertEqual(self.customer_tag, rec2["customer"])
self.assertEqual("LA", rec2[self.data_center_key])
self.delete_test_bucket(bucket)
def write(self, results):
points = []
for result in results:
point = Point(result[Check.Result.NAME]) \
.tag("host", result[Check.Result.HOST]) \
.time(result[Check.Result.TIME].isoformat())
if Check.Result.DEVICE in result:
point = point.tag("device", result[Check.Result.DEVICE])
for field in result[Check.Result.FIELDS]:
point = point.field(field[Check.Field.NAME], field[Check.Field.VALUE])
if Check.Field.UNIT in field:
point = point.field(f"{field[Check.Field.NAME]}_unit", field[Check.Field.UNIT])
points.append(point)
self.write_api.write(self.bucket, self.org, points)
class InfluxDBWrapper:
def __init__(self, name, field_name, field_value, tags=None):
self.name = name
self.point = Point(name)
tags = tags or {}
self.record = self._record(field_name, field_value, tags)
self.write_api = influxdb_client.write_api(write_options=SYNCHRONOUS)
def _record(self, field_name, field_value, tags):
for tag_key, tag_value in tags.items():
self.point = self.point.tag(tag_key, tag_value)
return self.point.field(field_name, field_value)
def write(self):
self.write_api.write(bucket=settings.INFLUXDB_BUCKET, record=self.record)
def panel_info_to_influx_points(panel_info):
points = []
for control in panel_info['controls']:
point = Point("cellar_panel_read")
point.tag("slot", control["slot"])
point.tag("vessel", control["label"])
point.tag("batch_number", control['batch_info']['Batch #'])
point.field("temp", control['temp'])
point.field("set_point", control['set_point'])
point.field("valve_open", control['valve_open'])
point.field("days_in_vessel", control['batch_info']['Days in Vessel'])
point.time(panel_info['read_at'], WritePrecision.NS)
points.append(point)
return points
def send_results(results):
"""
Formats the payload to send to InfluxDB
:rtype: None
"""
result_dict = results.dict()
pt = Point("speed_test_results")
pt.field('download', result_dict['download'])
pt.field('upload', result_dict['upload'])
pt.field('ping', result_dict['server']['latency'])
pt.tag('server', result_dict['server']['id'])
pt.tag('server_name', result_dict['server']['name'])
pt.tag('server_country', result_dict['server']['country'])
if getenv("INFLUXDB_V2_URL"):
client = InfluxDBClient.from_env_properties()
write_api = client.write_api(write_options=SYNCHRONOUS)
if write_api.write("speedtests/autogen", 'patrickjmcd', pt):
logging.debug('Data written to InfluxDB')
else:
logging.error("Data not written to influxdb")
def _finish_control_cycle(
self,
cycle_period: Optional[float],
mount_state: MountState,
rate_command: Optional[SlewRateCommand] = None,
rate_command_time_error: Optional[float] = None,
callback_override: bool = False,
) -> "Tracker.StopReason":
"""Final tasks to perform at the end of each control cycle."""
# list of telemetry points to be populated
points = []
# timestamp to use for all telemetry points that don't correspond to sensor readings
# or other events that occur at well-defined times
cycle_timestamp = datetime.utcnow()
# coordinate system transformations
position_mount_topo = self.mount_model.encoders_to_topocentric(
mount_state.position)
try:
# get target position for the same time as mount state was queried
position_target = self.target.get_position(
mount_state.time_queried)
except Target.IndeterminatePosition:
stop_reason = self._check_stopping_conditions()
else:
# on-sky separation between target and mount positions
error_magnitude = separation(position_target.topo,
position_mount_topo)
stop_reason = self._check_stopping_conditions(error_magnitude)
if self.telem_logger is not None:
error_enc = {
axis: float(
smallest_allowed_error(
mount_state.position[axis].deg,
position_target.enc[axis].deg,
self.mount.no_cross_encoder_positions()[axis].deg,
))
for axis in self.axes
}
# target position
pt = Point('target_position')
pt.field('azimuth', position_target.topo.az.deg)
pt.field('altitude', position_target.topo.alt.deg)
for axis in self.axes:
pt.field(f'encoder_{axis}', position_target.enc[axis].deg)
pt.tag('units', 'degrees')
pt.tag('class', type(self).__name__)
pt.time(position_target.time.to_datetime())
points.append(pt)
# mount position error
pt = Point('mount_position_error')
pt.field('magnitude', error_magnitude.deg)
for axis in self.axes:
pt.field(f'enoder_{axis}', error_enc[axis])
pt.tag('units', 'degrees')
pt.tag('class', type(self).__name__)
pt.time(cycle_timestamp)
points.append(pt)
if self.telem_logger is not None:
pt = Point('control_cycle_stats')
pt.field('period', cycle_period)
pt.field('cycle_count', self.num_iterations)
pt.field('callback_override', callback_override)
pt.tag('class', type(self).__name__)
pt.time(cycle_timestamp)
points.append(pt)
# mount positions
pt = Point('mount_position')
for axis in self.axes:
pt.field(f'encoder_{axis}', mount_state.position[axis].deg)
pt.field('azimuth', position_mount_topo.az.deg)
pt.field('altitude', position_mount_topo.alt.deg)
pt.tag('units', 'degrees')
pt.tag('class', type(self).__name__)
pt.time(mount_state.time_queried.to_datetime())
points.append(pt)
# mount slew rate
pt = Point('mount_rate')
for axis in self.axes:
pt.field(f'axis_{axis}', mount_state.rates[axis])
pt.tag('units', 'degrees/s')
pt.tag('class', type(self).__name__)
pt.time(mount_state.time_queried.to_datetime())
points.append(pt)
# controller commands
if rate_command is not None:
pt = Point('controller_commands')
for axis in self.axes:
pt.field(f'rate_axis_{axis}', rate_command.rates[axis])
if rate_command_time_error is not None:
pt.field('time_error', rate_command_time_error.sec)
pt.tag('units', 'degrees/s')
pt.tag('class', type(self).__name__)
pt.time(cycle_timestamp)
points.append(pt)
self.telem_logger.post_points(points)
self.num_iterations += 1
return stop_reason
def to_target(self, target: str):
"""
Emit Pandas DataFrame to target. A target
is identified by a connection string.
Examples:
- duckdb://dwd.duckdb?table=weather
- influxdb://localhost/?database=dwd&table=weather
- crate://localhost/?database=dwd&table=weather
Dispatch data to different data sinks. Currently, SQLite, DuckDB,
InfluxDB and CrateDB are implemented. However, through the SQLAlchemy
layer, it should actually work with any supported SQL database.
- https://docs.sqlalchemy.org/en/13/dialects/
:param target: Target connection string.
:return: self
"""
log.info(f"Exporting records to {target}\n{self.df.count()}")
connspec = ConnectionString(target)
protocol = connspec.url.scheme
database = connspec.get_database()
tablename = connspec.get_table()
if target.startswith("file://"):
filepath = connspec.get_path()
if target.endswith(".xlsx"):
log.info(f"Writing to spreadsheet file '{filepath}'")
# Convert all datetime columns to ISO format.
df = convert_datetimes(self.df)
df.to_excel(filepath, index=False)
elif target.endswith(".feather"):
# https://arrow.apache.org/docs/python/feather.html
log.info(f"Writing to Feather file '{filepath}'")
import pyarrow.feather as feather
feather.write_feather(self.df, filepath, compression="lz4")
elif target.endswith(".parquet"):
"""
# Acquire data and store to Parquet file.
alias fetch="wetterdienst dwd observations values --station=1048,4411 --parameter=kl --resolution=daily --period=recent"
fetch --target="file://observations.parquet"
# Check Parquet file.
parquet-tools schema observations.parquet
parquet-tools head observations.parquet
# References
- https://arrow.apache.org/docs/python/parquet.html
"""
log.info(f"Writing to Parquet file '{filepath}'")
import pyarrow as pa
import pyarrow.parquet as pq
table = pa.Table.from_pandas(self.df)
pq.write_table(table, filepath)
elif target.endswith(".zarr"):
"""
# Acquire data and store to Zarr group.
alias fetch="wetterdienst dwd observations values --station=1048,4411 --parameter=kl --resolution=daily --period=recent"
fetch --target="file://observations.zarr"
# References
- https://xarray.pydata.org/en/stable/generated/xarray.Dataset.from_dataframe.html
- https://xarray.pydata.org/en/stable/generated/xarray.Dataset.to_zarr.html
"""
log.info(f"Writing to Zarr group '{filepath}'")
import xarray
df = self.df
# Problem: `ValueError: Cannot setitem on a Categorical with a new category, set the categories first`.
# Solution: Let's convert all categorical columns back to their designated type representations.
# https://stackoverflow.com/questions/32011359/convert-categorical-data-in-pandas-dataframe/32011969#32011969
if "quality" in df:
df.quality = df.quality.astype("Int64")
categorical_columns = df.select_dtypes(["category"]).columns
df[categorical_columns] = df[categorical_columns].astype("str")
# Problem: `TypeError: float() argument must be a string or a number, not 'NAType'`.
# Solution: Fill gaps in the data.
df = df.fillna(-999)
# Convert pandas DataFrame to xarray Dataset.
dataset = xarray.Dataset.from_dataframe(df)
log.info(f"Converted to xarray Dataset. Size={dataset.sizes}")
# Export to Zarr format.
# TODO: Add "group" parameter.
# Group path. (a.k.a. `path` in zarr terminology.)
# TODO: Also use attributes: `store.set_attribute()`
store = dataset.to_zarr(
filepath,
mode="w",
group=None,
encoding={"date": {
"dtype": "datetime64"
}},
)
# Reporting.
dimensions = store.get_dimensions()
variables = list(store.get_variables().keys())
log.info(
f"Wrote Zarr file with dimensions={dimensions} and variables={variables}"
)
log.info(f"Zarr Dataset Group info:\n{store.ds.info}")
else:
raise KeyError("Unknown export file type")
return
if target.startswith("duckdb://"):
"""
====================
DuckDB database sink
====================
Install Python driver::
pip install duckdb
Acquire data::
wetterdienst dwd observations values --station=1048,4411 --parameter=kl --resolution=daily --period=recent --target="duckdb:///dwd.duckdb?table=weather"
Example queries::
python -c 'import duckdb; c = duckdb.connect(database="dwd.duckdb"); print(c.table("weather"))' # noqa
python -c 'import duckdb; c = duckdb.connect(database="dwd.duckdb"); print(c.execute("SELECT * FROM weather").df())' # noqa
"""
log.info(
f"Writing to DuckDB. database={database}, table={tablename}")
import duckdb
connection = duckdb.connect(database=database, read_only=False)
connection.register("origin", self.df)
connection.execute(f"DROP TABLE IF EXISTS {tablename};")
connection.execute(
f"CREATE TABLE {tablename} AS SELECT * FROM origin;"
) # noqa:S608
weather_table = connection.table(tablename)
print(weather_table) # noqa: T001
print("Cardinalities:") # noqa: T001
print(weather_table.to_df().count()) # noqa: T001
connection.close()
log.info("Writing to DuckDB finished")
elif protocol.startswith("influxdb"):
"""
==========================
InfluxDB 1.x database sink
==========================
Install Python driver::
pip install influxdb
Run database::
docker run -it --rm --publish=8086:8086 influxdb:1.8
Acquire data::
alias fetch="wetterdienst values --provider=dwd --network=observation --parameter=kl --resolution=daily --period=recent --station=1048,4411"
fetch --target="influxdb://localhost/?database=dwd&table=weather"
Example queries::
http 'localhost:8086/query?db=dwd&q=SELECT * FROM weather;'
http 'localhost:8086/query?db=dwd&q=SELECT COUNT(*) FROM weather;'
==========================
InfluxDB 2.x database sink
==========================
Install Python driver::
pip install influxdb_client
Run database::
docker run -it --rm --publish=8086:8086 influxdb:2.0
influx setup --name=default --username=root --password=12345678 --org=acme --bucket=dwd --retention=0 --force
Acquire data::
INFLUXDB_ORGANIZATION=acme
INFLUXDB_TOKEN=t5PJry6TyepGsG7IY_n0K4VHp5uPvt9iap60qNHIXL4E6mW9dLmowGdNz0BDi6aK_bAbtD76Z7ddfho6luL2LA==
alias fetch="wetterdienst values --provider=dwd --network=observation --parameter=kl --resolution=daily --period=recent --station=1048,4411"
fetch --target="influxdb2://${INFLUXDB_ORGANIZATION}:${INFLUXDB_TOKEN}@localhost/?database=dwd&table=weather"
Example queries::
influx query 'from(bucket:"dwd") |> range(start:-2d) |> limit(n: 10)'
"""
if protocol in [
"influxdb", "influxdbs", "influxdb1", "influxdb1s"
]:
version = 1
elif protocol in ["influxdb2", "influxdb2s"]:
version = 2
else:
raise KeyError(
f"Unknown protocol variant '{protocol}' for InfluxDB")
log.info(
f"Writing to InfluxDB version {version}. database={database}, table={tablename}"
)
# 1. Mungle the data frame.
# Use the "date" column as appropriate timestamp index.
df = self.df.set_index(pd.DatetimeIndex(self.df["date"]))
df = df.drop(["date"], axis=1)
# Compute designated tag fields from some candidates.
tag_columns = []
tag_candidates = [
Columns.STATION_ID.value,
Columns.QUALITY.value,
Columns.QUALITY_PREFIX.value,
Columns.DATASET.value,
Columns.PARAMETER.value,
]
for tag_candidate in tag_candidates:
tag_candidate = tag_candidate.lower()
for column in df.columns:
if column.startswith(tag_candidate):
tag_columns.append(column)
# Setup the connection.
if version == 1:
from influxdb import InfluxDBClient
client = InfluxDBClient(
host=connspec.url.hostname,
port=connspec.url.port or 8086,
username=connspec.url.username,
password=connspec.url.password,
database=database,
ssl=protocol.endswith("s"),
)
client.create_database(database)
elif version == 2:
from influxdb_client import InfluxDBClient, Point
from influxdb_client.client.write_api import SYNCHRONOUS
ssl = protocol.endswith("s")
url = f"http{ssl and 's' or ''}://{connspec.url.hostname}:{connspec.url.port or 8086}"
client = InfluxDBClient(url=url,
org=connspec.url.username,
token=connspec.url.password)
write_api = client.write_api(write_options=SYNCHRONOUS)
points = []
for items in chunker(df, chunksize=50000):
for date, record in items.iterrows():
time = date.isoformat()
tags = {
tag: record.pop(tag)
for tag in tag_columns if tag in record
}
fields = record.dropna().to_dict()
if not fields:
continue
if version == 1:
point = {
"measurement": tablename,
"time": time,
"tags": tags,
"fields": fields,
}
elif version == 2:
point = Point(tablename).time(date.isoformat())
for tag, value in tags.items():
point = point.tag(tag, value)
for field, value in fields.items():
point = point.field(field, value)
points.append(point)
# Write to InfluxDB.
if version == 1:
client.write_points(
points=points,
batch_size=50000,
)
elif version == 2:
write_api.write(bucket=database, record=points)
write_api.close()
log.info("Writing to InfluxDB finished")
elif target.startswith("crate://"):
"""
=====================
CrateDB database sink
=====================
Install Python driver::
pip install crate[sqlalchemy] crash
Run database::
docker run -it --rm --publish=4200:4200 --env CRATE_HEAP_SIZE=2048M crate/crate:nightly
Acquire data::
wetterdienst dwd observations values --station=1048,4411 --parameter=kl --resolution=daily --period=recent --target="crate://crate@localhost/dwd?table=weather"
Example queries::
psql postgres://crate@localhost --command 'SELECT * FROM dwd.weather;'
crash -c 'select * from dwd.weather;'
crash -c 'select count(*) from dwd.weather;'
crash -c "select *, date_format('%Y-%m-%dT%H:%i:%s.%fZ', date) as datetime from dwd.weather order by datetime limit 10;" # noqa
"""
log.info(f"Writing to CrateDB. target={target}, table={tablename}")
# CrateDB's SQLAlchemy driver doesn't accept `database` or `table` query parameters.
cratedb_url = connspec.url._replace(path="", query=None)
cratedb_target = urlunparse(cratedb_url)
# Convert timezone-aware datetime fields to naive ones.
# FIXME: Omit this as soon as the CrateDB driver is capable of supporting timezone-qualified timestamps.
self.df.date = self.df.date.dt.tz_localize(None)
self.df.to_sql(
name=tablename,
con=cratedb_target,
schema=database,
if_exists="replace",
index=False,
chunksize=5000,
)
log.info("Writing to CrateDB finished")
else:
"""
================================
Generic SQLAlchemy database sink
================================
Install Python driver::
pip install sqlalchemy
Examples::
# Prepare
alias fetch='wetterdienst dwd observations values --station=1048,4411 --parameter=kl --resolution=daily --period=recent'
# Acquire data.
fetch --target="sqlite:///dwd.sqlite?table=weather"
# Query data.
sqlite3 dwd.sqlite "SELECT * FROM weather;"
"""
# Honour SQLite's SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999
# for SQLite versions prior to 3.32.0 (2020-05-22),
# see https://www.sqlite.org/limits.html#max_variable_number.
chunksize = 5000
if target.startswith("sqlite://"):
import sqlite3
if sqlite3.sqlite_version_info < (3, 32, 0):
chunksize = int(999 / len(self.df.columns))
log.info("Writing to SQL database")
self.df.to_sql(
name=tablename,
con=target,
if_exists="replace",
index=False,
method="multi",
chunksize=chunksize,
)
log.info("Writing to SQL database finished")
def get_gateway_details(gateway):
#print(gateway)
gateway_id = gateway['ids']['gateway_id']
point = Point("TTN_Gateways").tag("gateway_id",
gateway_id).tag("name", gateway['name'])
if 'antennas' in gateway:
for dimension in ['latitude', 'longitude', 'altitude']:
if dimension in gateway['antennas'][0]['location']:
value = gateway['antennas'][0]['location'][dimension]
else:
value = 0
point = point.tag(
dimension, value
) #body['gateway']['antennas'][0]['location'][dimension] = antenna_locations[dimension]
#point = point.tag('latitude',gateway['antennas'][0]['location']['latitude']).tag('longitude',gateway['antennas'][0]['location']['longitude']).tag('altitude',gateway['antennas'][0]['location']['altitude'])
#for key,value in gateway['antennas'][0]['location']:
# point = point.tag(key,value)
gateway_stats = (requests.get(base_uri + "/api/v3/gs/gateways/" +
gateway_id + "/connection/stats",
params=gateway_stats_params,
headers=http_headers)).json()
#https://eu1.cloud.thethings.network/api/v3/gs/gateways/fort-digital-80029c641ef8/connection/stats
if 'attributes' in gateway:
for key, value in gateway['attributes'].items():
point = point.tag(key, value)
#Need to consider how to handle last_status_received_at not updating but not getting a 'gateway not connected' message yet to mark a site as 'down'
#Can probably handle this in the query?
if "connected_at" in gateway_stats:
#print(gateway_stats)
point = point.field("status", 1)
if 'last_status_received_at' in gateway_stats:
point = point.time(gateway_stats['last_status_received_at'])
if 'uplink_count' in gateway_stats:
point = point.field("uplink_count",
will_it_float(gateway_stats['uplink_count']))
if 'downlink_count' in gateway_stats:
point = point.field("downlink_count",
will_it_float(gateway_stats['downlink_count']))
if 'last_status' in gateway_stats:
if 'metrics' in gateway_stats['last_status']:
for key, value in gateway_stats['last_status'][
'metrics'].items():
point = point.field(key, will_it_float(value))
#Could use the latest antenna location to automatically update gateway location as its ignored from UDP gateway_stats['last_status']['antenna_locations']['latitude/longitude/altitude']
#print(gateway_stats)
if 'antenna_locations' in gateway_stats['last_status']:
if 'antennas' not in gateway or gateway['antennas'][0][
'location']['latitude'] != gateway_stats[
'last_status']['antenna_locations'][0][
'latitude'] or gateway['antennas'][0][
'location']['longitude'] != gateway_stats[
'last_status']['antenna_locations'][0][
'longitude']:
update_gateway(
gateway_id,
gateway_stats['last_status']['antenna_locations'][0])
else:
#Gateway Not Connected
point = point.field("status", 0)
print(point.to_line_protocol())
#flushstdout
sys.stdout.flush()
port=server_config.get(server, 'port'),
username=server_config.get(server, 'username'),
password=server_config.get(server, 'password')))
power_ok = True
write_api = client.write_api(write_options=SYNCHRONOUS)
for server_data in ups_data:
for ups in server_data:
if ups == False:
continue
if ups['ups.status'] != 'OL' or ups['battery.charge'] < 100:
power_ok = False
point = Point("ups")
point.tag("ups_name", ups['name'])
for entry in ups:
if isinstance(ups[entry], (float, int)):
point.field(entry, ups[entry])
point.time(datetime.utcnow(), WritePrecision.NS)
try:
write_api.write(bucket=bucket, org=org, record=point)
print(datetime.now().strftime("%d/%m/%Y@%H:%M:%S"), end=':')
print('data sent for: ' + ups['name'])
except Exception as e:
print(datetime.now().strftime("%d/%m/%Y@%H:%M:%S"), end=':')
print('Could not send data to influx for:' + ups['name'])
print(e)
print(influx_url, token, org, bucket)
def extract_transform_load_time_series_json(swift_result, swift_container,
swift_id, process_type):
"""
Fonction de traitement d'un fichier Json Time Series
"""
client = InfluxDBClient(url=config.url_influxdb,
token=config.token_influxdb)
write_api = client.write_api(write_options=SYNCHRONOUS)
time_format = "%Y-%m-%dT%H:%M:%S.%fZ"
points = []
for m in json.loads(swift_result):
topic = m.get("topic")
subID = m.get("subID")
unitID = m.get("unitID")
input_v = m.get("input")
value_units = m.get("value_units")
value = m.get("value")
time = datetime.strptime(m.get("time"), time_format)
m_points = []
if "energy" in topic:
if config.influxdb_measurement == "topic":
point = Point(topic) \
.tag("subID", subID) \
.tag("unitID", unitID) \
.time(time, WritePrecision.MS)
if "input" in m:
point.tag("input", input_v)
for i in range(len(value_units)):
point.field(value_units[i], value[i])
m_points.append(point)
else:
for i in range(len(value_units)):
point = Point(value_units[i]) \
.field("value", value[i]) \
.tag("topic", topic) \
.tag("subID", subID) \
.tag("unitID", unitID) \
.time(time, WritePrecision.MS)
if "input" in m:
point.tag("input", input_v)
m_points.append(point)
else:
if config.influxdb_measurement == "topic":
point = Point(topic) \
.field(value_units, value)
else:
point = Point(value_units) \
.field("value", value) \
.tag("topic", topic)
point.tag("subID", subID) \
.tag("unitID", unitID) \
.time(time, WritePrecision.MS)
if "input" in m:
point.tag("input", input_v)
m_points.append(point)
history_data(process_type, swift_container, swift_id,
"mqtt json to influxdb data points", m,
[p.to_line_protocol() for p in m_points])
points += m_points
write_api.write(config.bucket_influxdb, config.org_influxdb, points)
