{"id":523,"date":"2023-09-22T15:25:34","date_gmt":"2023-09-22T22:25:34","guid":{"rendered":"https:\/\/scalptrader.com\/?page_id=523"},"modified":"2023-09-22T15:57:07","modified_gmt":"2023-09-22T22:57:07","slug":"building-ohlc-trade-data-using-influxdbv2-flux-tasks","status":"publish","type":"page","link":"https:\/\/scalptrader.com\/building-ohlc-trade-data-using-influxdbv2-flux-tasks\/","title":{"rendered":"Building OHLC Trade Data Using Influxdbv2 Flux Tasks"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-lmv6z55w-5f883c31573414546e2cf518d80702d0\">\n#top .av-special-heading.av-lmv6z55w-5f883c31573414546e2cf518d80702d0{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-lmv6z55w-5f883c31573414546e2cf518d80702d0 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-lmv6z55w-5f883c31573414546e2cf518d80702d0 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-lmv6z55w-5f883c31573414546e2cf518d80702d0 av-special-heading-h2  avia-builder-el-0  el_before_av_image  avia-builder-el-first '><h2 class='av-special-heading-tag'  itemprop=\"headline\"  >Building OHLC Trade Data Using Influxdbv2 Flux Tasks<\/h2><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-lmv711s4-0ffe63fe3fddbf0c1598f10a5d19409b\">\n.avia-image-container.av-lmv711s4-0ffe63fe3fddbf0c1598f10a5d19409b img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-lmv711s4-0ffe63fe3fddbf0c1598f10a5d19409b .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-lmv711s4-0ffe63fe3fddbf0c1598f10a5d19409b av-styling- avia-align-center  avia-builder-el-1  el_after_av_heading  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-544 avia-img-lazy-loading-not-544 avia_image ' src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/blogpostheader.png\" alt='' title='blogpostheader'  height=\"500\" width=\"1500\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/blogpostheader.png 1500w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/blogpostheader-300x100.png 300w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/blogpostheader-1030x343.png 1030w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/blogpostheader-768x256.png 768w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/blogpostheader-705x235.png 705w\" sizes=\"(max-width: 1500px) 100vw, 1500px\" \/><\/div><\/div><\/div>\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-lmv6jiog-5043b0e6cf9892137c4ddc77668cbbb7\">\n#top .av_textblock_section.av-lmv6jiog-5043b0e6cf9892137c4ddc77668cbbb7 .avia_textblock{\nfont-size:18px;\n}\n<\/style>\n<section  class='av_textblock_section av-lmv6jiog-5043b0e6cf9892137c4ddc77668cbbb7'  itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/CreativeWork\" ><div class='avia_textblock'  itemprop=\"text\" ><p>If you are streaming financial market data you will quickly accumulate a large amount of tick data which will start to consume large amounts of storage. Often it&#8217;s desirable to resample the data to a format that will be easier to store<\/p>\n<p>One possible solution is to deploy micro services to stream data into InfluxDB. We are not going to be talking about those components in this article. In this article we discuss how we can use the stream data processing task features from InfluxDB and the Flux language to process market data into more manageable OHLC (Open, High, Low, Close) data bars. We will also discuss some of the initial setup steps required to get InfluxDB up and running as it will help later to have that knowledge when we dive into the data sampling and the terminology used there.<\/p>\n<p>If you have ever used InfluxDB before you have probably used the TICK stack from InfluxDB v1. With the introduction of the InfluxDB v2 platform InfluxData introduced a platform in a single binary which makes setup and configuration much easier especially using Docker containers. This article is going to assume you already have Docker and Docker Compose tools installed. If you need to do that head over to <a href=\"https:\/\/docs.docker.com\/get-docker\">https:\/\/docs.docker.com\/get-docker<\/a> and install those first<\/p>\n<p>InfluxData hosts Docker containers for InfluxDB at <a href=\"https:\/\/quay.io\/influxdb\/influxdb\">https:\/\/quay.io\/influxdb\/influxdb<\/a><\/p>\n<p>At the time of writing this article the latest version available was v2.0.4. Once you have Docker setup it&#8217;s simple to spin up an instance of InfluxDB v2 using a Docker compose file to grab the container from quay.io and run it<\/p>\n<p>There&#8217;s not much to getting things setup as you can see. A couple of interesting parts are the volume mapping from the container to the host that allows data to persist between runs of the database so we don&#8217;t have it reset every time. We also expose port 8086 so we can communicate with the InfluxDB API. If you run this config with Docker compose you will be able to visit http:\/\/localhost:8086 and you should see the initial InfluxDB welcome page<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-191 size-full\" src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx1.png\" alt=\"\" width=\"1223\" height=\"683\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx1.png 1223w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx1-300x168.png 300w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx1-1030x575.png 1030w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx1-768x429.png 768w\" sizes=\"(max-width: 1223px) 100vw, 1223px\" \/><\/p>\n<p>The next step is to setup the initial database parameters by providing and organization name and a data bucket name. The data bucket is where you store your time series data. For this article we are going to be using the organization &#8216;test&#8217; and our initial data bucket will be called \u2018marketdata&#8217;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-192\" src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx2.png\" alt=\"\" width=\"1131\" height=\"606\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx2.png 1131w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx2-300x161.png 300w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx2-1030x552.png 1030w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx2-768x412.png 768w\" sizes=\"(max-width: 1131px) 100vw, 1131px\" \/><\/p>\n<p>After setting that up you can view the &#8216;Data&#8217; panel and you should see the bucket listed. The default retention policy is forever which is fine for our test. The retention policy can easily be modified, to 30 days for example.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-193\" src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx3.png\" alt=\"\" width=\"1224\" height=\"546\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx3.png 1224w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx3-300x134.png 300w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx3-1030x459.png 1030w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx3-768x343.png 768w\" sizes=\"(max-width: 1224px) 100vw, 1224px\" \/><\/p>\n<p>InfluxDB API calls require a token and you can find this token in the same &#8216;Data&#8217; section under &#8216;Tokens&#8217;. This token will need to be passed to your code making the API calls to InfluxDB.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-194\" src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx4.png\" alt=\"\" width=\"1226\" height=\"483\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx4.png 1226w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx4-300x118.png 300w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx4-1030x406.png 1030w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx4-768x303.png 768w\" sizes=\"(max-width: 1226px) 100vw, 1226px\" \/><\/p>\n<p>One way to do this for development purposes is to create an environment variable section in your Docker compose file for the service that&#8217;s going to call the API<\/p>\n<p>You can then use this API token with one of the InfluxDB client libraries to send data to be ingested. There are client libraries for many languages here <a href=\"https:\/\/docs.influxdata.com\/influxdb\/cloud\/tools\/client-libraries\">https:\/\/docs.influxdata.com\/influxdb\/cloud\/tools\/client-libraries<\/a><\/p>\n<p>E.g. For exmple in Golang you could write data using your token into the &#8216;marketdata&#8217; bucket for organization &#8216;test&#8217; like this<\/p>\n<pre>client := influxdb2.NewClient(\"http:\/\/localhost\", DB_TOKEN)\n\nwriteAPI := client.WriteAPIBlocking(\"test\", \"marketdata\")<\/pre>\n<p>It&#8217;s as easy as that! Once you have written some data out you will be able to view the measurements via the Influx UI<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-195\" src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx6.png\" alt=\"\" width=\"1226\" height=\"374\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx6.png 1226w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx6-300x92.png 300w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx6-1030x314.png 1030w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/01\/Influx6-768x234.png 768w\" sizes=\"(max-width: 1226px) 100vw, 1226px\" \/><\/p>\n<p>Okay so at this point we are going to assume that the InfluxDB is up and running and there is some service that is sending some tick data to be ingested by the database using the InfluxDB API. Now we can look at how to use Flux to resample that data. Flux is designed for querying, analyzing, and acting on data. This data comes from buckets. We setup a test bucket previously during database setup so that&#8217;s what we will use here.<\/p>\n<p>You tell Flux which bucket to use by using the from() function<\/p>\n<pre>dataset = from(bucket: \u201ctest\")<\/pre>\n<p>You also need to specify a time range for the data query. A time range is required so that the query to InfluxDB isn&#8217;t unbounded as that can be unlimited in size. We will specify a start of the range to be 3 minutes ago<\/p>\n<pre>dataset = from(bucket: \u201cmarketdata\")\n\n|&gt; range(start: -3)<\/pre>\n<p>Each bucket has measurements that acts as a container for tags, fields, and timestamps. A measurement name should describe your data. We chose to have a measurement name of &#8216;tradedata&#8217; which is set when the market data service sends data to be ingested over the API. We need to tell the Flux query that we are interested in this measurement. The Flux filter function filters data based on conditions defined in a predicate function (fn)<\/p>\n<pre>filter(fn: (r) =&gt;\n\n(r[\"_measurement\"] == \u201ctradedata\"))<\/pre>\n<p>For our OHLC calculation we are going to grab the &#8216;Last&#8217; field that we stored in our time series data and also the &#8216;Size&#8217; field to keep a track of the volume.<\/p>\n<pre>filter(fn: (r) =&gt;\n\n(r[\"_field\"] == \"Size\" or r[\"_field\"] == \u201cLast\"))`}<\/pre>\n<p>So putting it all together we have our first part of our Flux program complete to extract a new table of results with just the data that we need for the OHLC calculations<\/p>\n<pre>dataset = from(bucket: \u201ctest\")\n\n|&gt; range(start: -3)\n\n|&gt; filter(fn: (r) =&gt;\n\n(r[\"_measurement\"] == \u201ctradedata\"))\n\n|&gt; filter(fn: (r) =&gt;\n\n(r[\"_field\"] == \"Size\" or r[\"_field\"] == \u201cLast\"))<\/pre>\n<p>The next step will be to start calculating the OHLC values from the &#8216;Last&#8217; field values. We are going to create one minute OHLC bars. This is where the InfluxDB Flux functions are so powerful. We can pass the table &#8216;dataset&#8217; as an input to an aggregation function that will use the Flux functions first(), max(), min() and last() to calculate the open, high, low and close values. The aggregation function works on a fixed window of time. In our case we will use a one minute window.<\/p>\n<p>For example, to get the first value from our &#8216;Last&#8217; values during a one minute window we would use the Flux aggregateWindow<\/p>\n<pre>dataset |&gt; filter(fn: (r) =&gt;\n\n(r[\"_field\"] == \"Last\"))\n\n|&gt; aggregateWindow(every: 1m, fn: first)`<\/pre>\n<p>Because we want to create a new data set and place it into a different bucket from the original tradedata bucket an additional step is required to map records from the input table to new table and then output those value to the new bucket. Because in this step we&#8217;re getting the first value this will be the &#8216;open&#8217; value for our OHLC records. So we map the value to the &#8216;open&#8217; field in the new table<\/p>\n<pre><code>|&gt; map(fn: (r) =&gt;\\n\\t({<\/code> <code>_time: r._time,<\/code> <code>_field: \"open\",<\/code> <code>_measurement: r._measurement,<\/code> <code>_value: r._value,<\/code> <code>topic: r.topic,<\/code> <code>symbol: r.symbol,<\/code> <code>}))<\/code> <code>|&gt; limit(n: 3)<\/code> <code>|&gt; to(bucket: \"ohlc_1m_data\", org: \"test\")<\/code><\/pre>\n<p>Remember this is only the &#8216;open&#8217; part of the OHLC data. The calculations logic for the rest of the OHLC data is essentially the same apart from we swap out the function passed to the aggregateWindow function and write a different field in the mapping. Adding all of the code here would make this article way too long, so you can find the complete Flux script over at our Github page\u00a0<a href=\"https:\/\/github.com\/scalptrader\/InfluxDBOHLC\">InfluxDB OHLC Flux Code<\/a><\/p>\n<p>Now we are going to add the Flux script to our InfluxDB. The first step is to create a new bucket to hold the one minute data that our script is going to generate. We can do this via the InfluxDB API or via the InfluxDB UI. We will use the UI here. Go to the &#8216;Explore&#8217; tab and find the option &#8216;Create Bucket&#8217;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-528\" src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket.png\" alt=\"\" width=\"1156\" height=\"261\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket.png 1156w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket-300x68.png 300w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket-1030x233.png 1030w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket-768x173.png 768w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket-705x159.png 705w\" sizes=\"(max-width: 1156px) 100vw, 1156px\" \/><\/p>\n<p>We are going to call our bucket &#8216;ohlc_1m_data&#8217;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-529\" src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket2.png\" alt=\"\" width=\"667\" height=\"288\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket2.png 667w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/create_bucket2-300x130.png 300w\" sizes=\"(max-width: 667px) 100vw, 667px\" \/><\/p>\n<p>Next go to the &#8216;Tasks&#8217; tab, create a new task, paste in the Flux code and save it. For the scheduling we setup the Cron parameter to be *\/1 * * * 1-5 which in English means &#8216;At every minute on every day-of-week from Monday through Friday&#8217;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-530\" src=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/flux_task.png\" alt=\"\" width=\"1638\" height=\"1281\" srcset=\"https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/flux_task.png 1638w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/flux_task-300x235.png 300w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/flux_task-1030x806.png 1030w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/flux_task-768x601.png 768w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/flux_task-1536x1201.png 1536w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/flux_task-1500x1173.png 1500w, https:\/\/scalptrader.com\/wp-content\/uploads\/2023\/09\/flux_task-705x551.png 705w\" sizes=\"(max-width: 1638px) 100vw, 1638px\" \/> Once you have created the task, InfluxDB will add a block into your script with the Cron task parameters included<\/p>\n<p>When you run the task InfluxDB will execute it according to the Cron job and OHLC data will start to be generated, in this case every minute. You can see the data by going to the &#8216;Explore&#8217; tab and selecting the &#8216;ohlc_1m_data&#8217; bucket.<\/p>\n<p>Hopefully this article has been a good introduction to using the powerful InfluxDB Flux features. We are really only just scratching the surface with the Flux capabilities in this article. Head over to the official InfluxDB Flux docs\u00a0<a href=\"https:\/\/docs.influxdata.com\/influxdb\/v2.0\/query-data\/get-started\/\">here<\/a>\u00a0to take a deeper dive<\/p>\n<\/div><\/section>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"_links":{"self":[{"href":"https:\/\/scalptrader.com\/wp-json\/wp\/v2\/pages\/523"}],"collection":[{"href":"https:\/\/scalptrader.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/scalptrader.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/scalptrader.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/scalptrader.com\/wp-json\/wp\/v2\/comments?post=523"}],"version-history":[{"count":12,"href":"https:\/\/scalptrader.com\/wp-json\/wp\/v2\/pages\/523\/revisions"}],"predecessor-version":[{"id":551,"href":"https:\/\/scalptrader.com\/wp-json\/wp\/v2\/pages\/523\/revisions\/551"}],"wp:attachment":[{"href":"https:\/\/scalptrader.com\/wp-json\/wp\/v2\/media?parent=523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}