438 lines
13 KiB
HTML
438 lines
13 KiB
HTML
|
||
|
||
<!DOCTYPE html>
|
||
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
|
||
<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]-->
|
||
<head>
|
||
<meta charset="utf-8">
|
||
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
||
|
||
<title>Structure of InfluxDB — Plutonium reporter 1.0 documentation</title>
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
<script type="text/javascript" src="_static/js/modernizr.min.js"></script>
|
||
|
||
|
||
<script type="text/javascript" id="documentation_options" data-url_root="./" src="_static/documentation_options.js"></script>
|
||
<script type="text/javascript" src="_static/jquery.js"></script>
|
||
<script type="text/javascript" src="_static/underscore.js"></script>
|
||
<script type="text/javascript" src="_static/doctools.js"></script>
|
||
<script type="text/javascript" src="_static/language_data.js"></script>
|
||
|
||
<script type="text/javascript" src="_static/js/theme.js"></script>
|
||
|
||
|
||
|
||
|
||
<link rel="stylesheet" href="_static/css/theme.css" type="text/css" />
|
||
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
|
||
<link rel="index" title="Index" href="genindex.html" />
|
||
<link rel="search" title="Search" href="search.html" />
|
||
</head>
|
||
|
||
<body class="wy-body-for-nav">
|
||
|
||
|
||
<div class="wy-grid-for-nav">
|
||
|
||
<nav data-toggle="wy-nav-shift" class="wy-nav-side">
|
||
<div class="wy-side-scroll">
|
||
<div class="wy-side-nav-search" >
|
||
|
||
|
||
|
||
<a href="index.html" class="icon icon-home"> Plutonium reporter
|
||
|
||
|
||
|
||
</a>
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
<div role="search">
|
||
<form id="rtd-search-form" class="wy-form" action="search.html" method="get">
|
||
<input type="text" name="q" placeholder="Search docs" />
|
||
<input type="hidden" name="check_keywords" value="yes" />
|
||
<input type="hidden" name="area" value="default" />
|
||
</form>
|
||
</div>
|
||
|
||
|
||
</div>
|
||
|
||
<div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
|
||
|
||
|
||
|
||
|
||
|
||
|
||
<p class="caption"><span class="caption-text">Contents:</span></p>
|
||
<ul>
|
||
<li class="toctree-l1"><a class="reference internal" href="introduction.html">Introduction to Plutonium</a></li>
|
||
</ul>
|
||
|
||
|
||
|
||
</div>
|
||
</div>
|
||
</nav>
|
||
|
||
<section data-toggle="wy-nav-shift" class="wy-nav-content-wrap">
|
||
|
||
|
||
<nav class="wy-nav-top" aria-label="top navigation">
|
||
|
||
<i data-toggle="wy-nav-top" class="fa fa-bars"></i>
|
||
<a href="index.html">Plutonium reporter</a>
|
||
|
||
</nav>
|
||
|
||
|
||
<div class="wy-nav-content">
|
||
|
||
<div class="rst-content">
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
<div role="navigation" aria-label="breadcrumbs navigation">
|
||
|
||
<ul class="wy-breadcrumbs">
|
||
|
||
<li><a href="index.html">Docs</a> »</li>
|
||
|
||
<li>Structure of InfluxDB</li>
|
||
|
||
|
||
<li class="wy-breadcrumbs-aside">
|
||
|
||
|
||
<a href="_sources/db_structure.rst.txt" rel="nofollow"> View page source</a>
|
||
|
||
|
||
</li>
|
||
|
||
</ul>
|
||
|
||
|
||
<hr/>
|
||
</div>
|
||
<div role="main" class="document" itemscope="itemscope" itemtype="http://schema.org/Article">
|
||
<div itemprop="articleBody">
|
||
|
||
<div class="section" id="structure-of-influxdb">
|
||
<h1>Structure of InfluxDB<a class="headerlink" href="#structure-of-influxdb" title="Permalink to this headline">¶</a></h1>
|
||
<blockquote>
|
||
<div><p>influxDB SCHEMA:</p>
|
||
<p>DB weather
|
||
measure wind
|
||
—————-</p>
|
||
<blockquote>
|
||
<div><p>field tag</p>
|
||
</div></blockquote>
|
||
<blockquote>
|
||
<div><p>field field tag field</p>
|
||
</div></blockquote>
|
||
<blockquote>
|
||
<div><p>field tag field(int)</p>
|
||
</div></blockquote>
|
||
<p>DB status</p>
|
||
<blockquote>
|
||
<div><p>field tag field tag</p>
|
||
</div></blockquote>
|
||
<blockquote>
|
||
<div><p>field | tag</p>
|
||
</div></blockquote>
|
||
<p>SQLite SCHEMA:</p>
|
||
<p>DB status</p>
|
||
<blockquote>
|
||
<div><p>INT | INT | VARCHAR(50) | VARCHAR(20)</p>
|
||
<blockquote>
|
||
<div><dl class="simple">
|
||
<dt>CREATE TABLE raspi(</dt><dd><p>t_stamp INT,
|
||
usage INT,
|
||
host VARCHAR(50),
|
||
type VARCHAR(20));</p>
|
||
</dd>
|
||
</dl>
|
||
</div></blockquote>
|
||
</div></blockquote>
|
||
<blockquote>
|
||
<div><p>INT | INT | VARCHAR(20) | VARCHAR(20) | VARCHAR(50)</p>
|
||
<blockquote>
|
||
<div><dl class="simple">
|
||
<dt>CREATE TABLE network(</dt><dd><p>t_stamp INT,
|
||
count INT,
|
||
type VARCHAR(20),
|
||
nic VARCHAR(20),
|
||
host VARCHAR(50));</p>
|
||
</dd>
|
||
</dl>
|
||
<p>TABLE vantage_vue_iss</p>
|
||
</div></blockquote>
|
||
</div></blockquote>
|
||
<blockquote>
|
||
<div><p>INT | INT | VARCHAR(20) | TINYINT | TINYINT | BOOL</p>
|
||
</div></blockquote>
|
||
</div></blockquote>
|
||
<div class="section" id="energy">
|
||
<h2>ENERGY<a class="headerlink" href="#energy" title="Permalink to this headline">¶</a></h2>
|
||
<dl>
|
||
<dt>CREATE CONTINUOUS QUERY cq_power_1m on voltage BEGIN</dt><dd><dl class="simple">
|
||
<dt>SELECT max(power) AS p_max, min(power) AS p_min,</dt><dd><p>mean(power) as power, mean(voltage) AS voltage</p>
|
||
</dd>
|
||
</dl>
|
||
<p>INTO voltage.monthly.mppt_aggregated
|
||
FROM voltage.realtime.mppt
|
||
GROUP BY time(1m),type</p>
|
||
</dd>
|
||
</dl>
|
||
<p>END
|
||
CREATE CONTINUOUS QUERY “cq_power_1h” ON “voltage” BEGIN</p>
|
||
<blockquote>
|
||
<div><dl class="simple">
|
||
<dt>SELECT max(“power”) AS p_max,min(power) AS p_min,</dt><dd><p>mean(power) as power, mean(voltage) AS voltage</p>
|
||
</dd>
|
||
</dl>
|
||
<p>INTO “yearly”.”mppt_aggregated”
|
||
FROM realtime.mppt
|
||
GROUP BY time(1h), type</p>
|
||
</div></blockquote>
|
||
<p>END
|
||
CREATE CONTINUOUS QUERY “cq_power_6h” ON “voltage” BEGIN</p>
|
||
<blockquote>
|
||
<div><dl class="simple">
|
||
<dt>SELECT max(“power”) AS p_max,min(power) AS p_min,</dt><dd><p>mean(power) as power, mean(voltage) AS voltage</p>
|
||
</dd>
|
||
</dl>
|
||
<p>INTO “infinite”.”mppt_aggregated”
|
||
FROM realtime.mppt
|
||
GROUP BY time(6h), type</p>
|
||
</div></blockquote>
|
||
<p>END</p>
|
||
<p>drop continuous query cq_power_30m on voltage
|
||
show retention policies on weather_v2</p>
|
||
<p>create retention policy realtime on weather_v2 duration 168h replication 1 shard duration 1h
|
||
create retention policy monthly on weather_v2 duration 720h replication 1 shard duration 24h
|
||
create retention policy yearly on weather_v2 duration 8760h replication 1 shard duration 168h
|
||
create retention policy infinite on weather_v2 duration 0s replication 1 shard duration 720h</p>
|
||
<p>select time,value into realtime.rain from autogen.rain where time > now()-1w group by *
|
||
select time,humidity,pressure,temperature into realtime.temphumi from autogen.temphumi where time > now()-1w group by *
|
||
select time,humidity,temperature into realtime.usense from autogen.usense where time > now()-1w group by *
|
||
select time,value into realtime.wind from autogen.wind where time > now()-1w group by *</p>
|
||
<p>alter retention policy realtime on weather_v2 default</p>
|
||
</div>
|
||
<div class="section" id="wind-done">
|
||
<h2>WIND - DONE<a class="headerlink" href="#wind-done" title="Permalink to this headline">¶</a></h2>
|
||
<dl class="simple">
|
||
<dt>CREATE CONTINUOUS QUERY “cq_rain_10m” ON “weather_v2” BEGIN</dt><dd><p>SELECT max(“rainrate”) AS rrate_max, mean(rainrate) AS rrate, max(rain_bucket_tips) AS rain_tips
|
||
INTO “monthly”.”rainrate_aggregated”
|
||
FROM realtime.rain
|
||
GROUP BY time(10m)</p>
|
||
</dd>
|
||
</dl>
|
||
<p>END
|
||
CREATE CONTINUOUS QUERY “cq_rain_1h” ON “weather_v2” BEGIN</p>
|
||
<blockquote>
|
||
<div><p>SELECT max(“rainrate”) AS rrate_max, mean(rainrate) AS rrate, max(rain_bucket_tips) AS rain_tips
|
||
INTO “yealy”.”rainrate_aggregated”
|
||
FROM realtime.rain
|
||
GROUP BY time(10m)</p>
|
||
</div></blockquote>
|
||
<p>END</p>
|
||
</div>
|
||
<div class="section" id="rain-done">
|
||
<h2>RAIN - DONE<a class="headerlink" href="#rain-done" title="Permalink to this headline">¶</a></h2>
|
||
<dl class="simple">
|
||
<dt>CREATE CONTINUOUS QUERY “cq_rain_10m” ON “weather_v2” BEGIN</dt><dd><p>SELECT max(“value”) AS val_max, mean(value) AS value
|
||
INTO “monthly”.”rainrate_aggregated”
|
||
FROM realtime.rain
|
||
GROUP BY type,time(10m)</p>
|
||
</dd>
|
||
</dl>
|
||
<p>END
|
||
CREATE CONTINUOUS QUERY “cq_rain_1h” ON “weather_v2” BEGIN</p>
|
||
<blockquote>
|
||
<div><p>SELECT max(“value”) AS val_max, mean(value) AS value
|
||
INTO “yearly”.”rainrate_aggregated”
|
||
FROM realtime.rain
|
||
GROUP BY type,time(1h)</p>
|
||
</div></blockquote>
|
||
<p>END
|
||
CREATE CONTINUOUS QUERY “cq_rain_6h” ON “weather_v2” BEGIN</p>
|
||
<blockquote>
|
||
<div><p>SELECT max(“value”) AS val_max, mean(value) AS value
|
||
INTO “infinite”.”rainrate_aggregated”
|
||
FROM realtime.rain
|
||
GROUP BY type,time(6h)</p>
|
||
</div></blockquote>
|
||
<div class="section" id="end">
|
||
<h3>END<a class="headerlink" href="#end" title="Permalink to this headline">¶</a></h3>
|
||
</div>
|
||
<div class="section" id="temphumi-done">
|
||
<h3>TEMPHUMI - DONE<a class="headerlink" href="#temphumi-done" title="Permalink to this headline">¶</a></h3>
|
||
<dl>
|
||
<dt>CREATE CONTINUOUS QUERY “cq_temphumi_10m” ON “weather_v2” BEGIN</dt><dd><dl class="simple">
|
||
<dt>SELECT</dt><dd><p>max(“humidity”) AS humidity_max,
|
||
min(“humidity”) AS humidity_min,
|
||
mean(“humidity”) AS humidity,
|
||
max(“temperature”) AS temperature_max,
|
||
min(“temperature”) AS temperature_min,
|
||
mean(“temperature”) AS temperature</p>
|
||
</dd>
|
||
</dl>
|
||
<p>INTO “monthly”.”temphumi_aggregated”
|
||
FROM realtime.temphumi
|
||
GROUP BY type, time(10m)</p>
|
||
</dd>
|
||
</dl>
|
||
<p>END</p>
|
||
<dl>
|
||
<dt>CREATE CONTINUOUS QUERY “cq_temphumi_1h” ON “weather_v2” BEGIN</dt><dd><dl class="simple">
|
||
<dt>SELECT</dt><dd><p>max(“humidity”) AS humidity_max,
|
||
min(“humidity”) AS humidity_min,
|
||
mean(“humidity”) AS humidity,
|
||
max(“temperature”) AS temperature_max,
|
||
min(“temperature”) AS temperature_min,
|
||
mean(“temperature”) AS temperature</p>
|
||
</dd>
|
||
</dl>
|
||
<p>INTO “yearly”.”temphumi_aggregated”
|
||
FROM realtime.temphumi
|
||
GROUP BY type, time(1h)</p>
|
||
</dd>
|
||
</dl>
|
||
<p>END</p>
|
||
<dl>
|
||
<dt>CREATE CONTINUOUS QUERY “cq_temphumi_6h” ON “weather_v2” BEGIN</dt><dd><dl class="simple">
|
||
<dt>SELECT</dt><dd><p>max(“humidity”) AS humidity_max,
|
||
min(“humidity”) AS humidity_min,
|
||
mean(“humidity”) AS humidity,
|
||
max(“temperature”) AS temperature_max,
|
||
min(“temperature”) AS temperature_min,
|
||
mean(“temperature”) AS temperature</p>
|
||
</dd>
|
||
</dl>
|
||
<p>INTO “infinite”.”temphumi_aggregated”
|
||
FROM realtime.temphumi
|
||
GROUP BY type, time(6h)</p>
|
||
</dd>
|
||
</dl>
|
||
<p>END</p>
|
||
</div>
|
||
</div>
|
||
<div class="section" id="usense-done">
|
||
<h2>USENSE - DONE<a class="headerlink" href="#usense-done" title="Permalink to this headline">¶</a></h2>
|
||
<dl class="simple">
|
||
<dt>CREATE CONTINUOUS QUERY “cq_usense_6h” ON “weather_v2” BEGIN</dt><dd><p>SELECT mean(“battery”) AS battery, mean(humidity) AS humidity, mean(temperature) AS temperature
|
||
INTO “yearly”.”usense_aggregated”
|
||
FROM realtime.usense
|
||
GROUP BY type,time(6h)</p>
|
||
</dd>
|
||
</dl>
|
||
<p>END</p>
|
||
<dl class="simple">
|
||
<dt>CREATE CONTINUOUS QUERY “cq_usense_12h” ON “weather_v2” BEGIN</dt><dd><p>SELECT mean(“battery”) AS battery, mean(humidity) AS humidity, mean(temperature) AS temperature
|
||
INTO “infinite”.”usense_aggregated”
|
||
FROM realtime.usense
|
||
GROUP BY type,time(12h)</p>
|
||
</dd>
|
||
</dl>
|
||
<p>END</p>
|
||
</div>
|
||
<div class="section" id="wind">
|
||
<h2>WIND<a class="headerlink" href="#wind" title="Permalink to this headline">¶</a></h2>
|
||
</div>
|
||
<div class="section" id="status">
|
||
<h2>STATUS<a class="headerlink" href="#status" title="Permalink to this headline">¶</a></h2>
|
||
<blockquote>
|
||
<div></div></blockquote>
|
||
<p>create retention policy realtime on status duration 168h replication 1 shard duration 1h
|
||
create retention policy monthly on status duration 720h replication 1 shard duration 24h
|
||
create retention policy yearly on status duration 8760h replication 1 shard duration 168h
|
||
create retention policy infinite on status duration 0s replication 1 shard duration 720h</p>
|
||
<p>select time,usage into realtime.RasPI_aggregated from autogen.RasPI where time > now()-1w group by *
|
||
select time,voltage into realtime.iss_aggregated from autogen.iss where time > now()-1w group by *</p>
|
||
<p>–
|
||
SELECT NON_NEGATIVE_DERIVATIVE(max(*)) as traffic INTO “monthly”.”net_aggregated” FROM autogen.net WHERE time > now()-2w GROUP BY time(30s)
|
||
CQ
|
||
–
|
||
CREATE CONTINUOUS QUERY “cq_net_1m” ON “status” BEGIN</p>
|
||
<blockquote>
|
||
<div><p>SELECT NON_NEGATIVE_DERIVATIVE(max(*)) as traffic
|
||
INTO “monthly”.”net_aggregated”
|
||
FROM realtime.net
|
||
WHERE time > now()-1m
|
||
GROUP BY time(30s)</p>
|
||
</div></blockquote>
|
||
<p>END</p>
|
||
<p>alter retention policy realtime on status default</p>
|
||
</div>
|
||
</div>
|
||
|
||
|
||
</div>
|
||
|
||
</div>
|
||
<footer>
|
||
|
||
|
||
<hr/>
|
||
|
||
<div role="contentinfo">
|
||
<p>
|
||
© Copyright 2019, Milan 'Ventil' Toman
|
||
|
||
</p>
|
||
</div>
|
||
Built with <a href="http://sphinx-doc.org/">Sphinx</a> using a <a href="https://github.com/rtfd/sphinx_rtd_theme">theme</a> provided by <a href="https://readthedocs.org">Read the Docs</a>.
|
||
|
||
</footer>
|
||
|
||
</div>
|
||
</div>
|
||
|
||
</section>
|
||
|
||
</div>
|
||
|
||
|
||
|
||
<script type="text/javascript">
|
||
jQuery(function () {
|
||
SphinxRtdTheme.Navigation.enable(true);
|
||
});
|
||
</script>
|
||
|
||
|
||
|
||
|
||
|
||
|
||
</body>
|
||
</html> |