{"id":14594,"date":"2023-07-26T12:16:39","date_gmt":"2023-07-26T19:16:39","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=14594"},"modified":"2024-02-07T11:56:01","modified_gmt":"2024-02-07T19:56:01","slug":"introducing-couchbase-time-series","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/introducing-couchbase-time-series\/","title":{"rendered":"Introducing Couchbase Time Series"},"content":{"rendered":"

Couchbase is excited to announce the release of a new time-series feature as part of Couchbase 7.2. This feature is built on top of the robust Couchbase distributed database architecture, which is designed to scale horizontally as your data grows, all while providing built-in redundancy and\u00a0 high availability. This means that as your business grows and your time series data needs increase, Couchbase can effortlessly expand to meet those needs, making it an ideal solution for businesses of all sizes.<\/span><\/p>\n

This innovative new technique to manage time series data opens up a whole new world of possibilities for Couchbase users. With the ability to store and analyze vast amounts of time series data using Couchbase SQL++ and SDKs. This allows users to leverage their existing knowledge and infrastructure, making it easy to set up and unlock powerful insights to explore data trends with ease.\u00a0<\/span><\/p>\n

Key benefits of time series\u00a0<\/span><\/h2>\n

Time series data are stored in JSON documents in the Couchbase Multi Model database. It provides the same high performance, advanced caching for fast data retrieval and low latency. Couchbase Query SQL++ and Index service enhance the data retrieval capabilities to allow complex analytical query use cases.<\/span><\/p>\n

Time Series data support in Couchbase provides these additional benefits:<\/span><\/p>\n

    \n
  1. \n
      \n
    1. Efficient storage for large volume of time series data points<\/span><\/li>\n
    2. Optimized data structure storage for time stamped data points<\/span><\/li>\n
    3. New advanced time series query capabilities<\/span><\/li>\n
    4. Low index storage requirement<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

      Examples of time series use cases<\/span><\/h2>\n

      Financial Trading – <\/strong>Financial trading relies on the analysis of large amounts of real-time data, including stock prices, currency rates, and commodity prices. Time-series data analysis can help traders identify trends and make informed decisions about buying and selling.<\/span><\/p>\n

      Internet of Things (IoT) Monitoring – <\/strong>IoT devices generate a large amount of time-series data, including temperature readings, energy consumption, and sensor data. This data can be analyzed in real-time to detect anomalies and predict equipment failures before they occur.<\/span><\/p>\n

      Predictive Maintenance – <\/strong>Many industries rely on expensive equipment and machinery, and downtime can be costly. By analyzing time-series data from sensors and other sources, organizations can predict when equipment is likely to fail, and proactively schedule maintenance to minimize downtime and maximize efficiency.<\/span><\/p>\n

      Key features of Couchbase Time Series<\/span><\/h2>\n

      You can store time series data in Couchbase, using our SDK\/SQL++ to load, and the advanced Analytical query capability with Global Secondary Index to query\/analyze the data in the same way as with normal JSON documents.<\/span><\/p>\n

      Storage efficiency<\/span><\/h3>\n

      Time series datasets are typically very large, with each data point consisting of attributes, such as timestamp, value(s), granularity and other related information. Efficient storage is critical as it can determine how fast the data can be queried for analysis.\u00a0<\/span><\/p>\n

      Couchbase time series uses two specifications to improve storage efficiency.<\/span><\/p>\n

      The use of arrays for data points<\/b> – By its very nature, time series data are a series of data points. These data points share a common structure, e.g., time and values, or any other attributes that are associated with the time when the data point was collected.\u00a0\u00a0<\/span><\/p>\n

      Using an array to store a set of the data points for a given range can greatly reduce the storage cost, compared to having to store each individual data point as a separate document in the database.\u00a0<\/span><\/p>\n

      The use of array position<\/b> – Also note that data point array elements do not have an associated field name, but instead it relies on the position of the element in the array. In this example, the three elements in the array are: the observation date, the opening value, and the closing value of the stock.<\/span><\/p>\n

      docid:\u00a0 \"stock:XYZ:d1\"\r\n{\r\n\u00a0 \"ts_data\": [ [1672531200000, 92.08, 95.62],[1672531201000, 95.62, 99.25],..]\r\n}<\/pre>\n
      Use EPOCH time –\u00a0<\/b>Epoch time<\/a> is used instead of ISO date string in order to reduce the size of each data point, and improve processing time.\u00a0<\/span><\/p>\n
      docid:\u00a0 \"stock:XYZ:d1\"\r\n{\r\n\u00a0 \"ts_data\": [ [1672531200000, 92.08, 95.62],[1672531201000, 95.62, 99.25],..]\r\n\u00a0 \"ts_start\": 1672531200000 \/* dstart and dend denote the *\/\r\n\u00a0 \"ts_end\": \u00a0 1672617599999,\/* time range of all data points in ts_data *\/\u00a0 \u00a0 \u00a0\r\n\u00a0 \"ticker\": \u00a0 \"XYZ\"\r\n}<\/pre>\n
      Optimized query with a new _timeseries function<\/span><\/h3>\n
      Couchbase Time Series feature includes a new<\/span> _timeseries<\/b> function. The function serves several purposes:<\/span><\/p>\n
        \n
      1. \n
          \n
        1. It dynamically generates the time series objects from the <\/span>ts_data <\/span>array of arrays<\/span><\/li>\n
        2. It efficiently streams the results when it is used with UNNEST, optimizes response time and memory usage<\/span><\/li>\n
        3. It can automatically generate the timestamp for each data point when the ts_interval parameter is used<\/span><\/li>\n
        4. It supports more advanced time series use cases for irregular time series data points interval, such as instead of a multiple sets of data points per timestamp<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
          Please refer to the <\/span>Couchbase time series documentation<\/span><\/a> for more detail.<\/span><\/p>\n
          Optimized index storage<\/span><\/h3>\n
          Due to the way Couchbase stores time series data, with each data point stored as an element in an array within a JSON document, we can optimize our database indexing strategy. Specifically, since each document contains both the start and end time of all the data points in the array, we can create a single index definition for each document. This means that even for large time series data sets with millions of data points, we can store the data in just a few documents rather than one document per data point.<\/span><\/p>\n
          For example, a time series data set with 1 million data points can be stored in just 1,000 documents, assuming that each ts_data<\/em> array can store up to 1,000 data elements and the document size remains below the 20MB Couchbase JSON document limit. This not only reduces the number of documents needed to store the data, but it also leads to smaller index sizes, improving database performance and reducing disk space requirements.<\/span><\/p>\n
          In short, by taking advantage of Couchbase’s ability to store time series data as arrays within JSON documents, we can optimize our indexing strategy to significantly reduce the number of documents needed and reduce the index size, resulting in faster query performance and more efficient use of storage resources.<\/span><\/p>\n
          CREATE INDEX ix1 ON docs(ticker, ts_end, ts_start);<\/pre>\n
          Data Retention\u00a0<\/span><\/h3>\n
          Time Series documents are stored as standard JSON documents in Couchbase. So the same Time-To-Live (TTL) can be set during the data load process.<\/span><\/p>\n
          \/* The document will be automatically removed in 30 days *\/\r\n\r\nINSERT INTO coll1 (KEY, VALUE)\u00a0\r\n\r\n\u00a0\u00a0\u00a0VALUES (\"stock:XYZ:d1\", {\"ticker\":\"XYZ\",..}, {\"expiration\":60*60*24*30});<\/pre>\n
          Example walkthrough<\/span><\/h2>\n
          So let’s walk through the process of loading an actual stock price data set into Couchbase using the time series data model as defined by the feature.<\/span><\/p>\n
          As I have described above, the Couchbase time series feature requires the JSON document in this specific format:<\/span><\/p>\n
          docid:\u00a0 \"stock:XYZ:d1\"\r\n{\r\n\u00a0 \"ts_data\": [ [1672531200000, 92.08, 95.62],[1672531201000, 95.62, 99.25],..]\r\n\u00a0 \"ts_start\": 1672531200000 \/* dstart and dend denote the *\/\r\n\u00a0 \"ts_end\": \u00a0 1672617599999,\/* time range of all data points in ts_data *\/\u00a0 \u00a0 \u00a0\r\n\u00a0 \"ticker\": \u00a0 \"XYZ\"\r\n}<\/pre>\n
          Follow the steps below if you need to convert your own time series data into the above format.<\/span><\/p>\n
          The data set used here is for the XYZ Inc stock price for 2013-2015.\u00a0<\/span><\/p>\n
          XYZ_data.csv<\/b><\/p>\n
          date,open,high,low,close,volume,Name\r\n2013-02-08,27.285,27.595,27.24,27.295,5100734,XYZ\r\n2013-02-11,27.64,27.95,27.365,27.61,8916290,XYZ\r\n2013-02-12,27.45,27.605,27.395,27.545,3866508,XYZ<\/pre>\n
          Migrate Data into Couchbase time series data structure<\/span><\/h3>\n
            \n
          1. Load the CSV file into a Couchbase collection:<\/span><\/li>\n<\/ol>\n
            cbimport csv --infer-types -c https:\/\/<cluster>:8091 -u <login> -p <password> -d 'file:\/\/XYZ_data.csv' -b 'ts' --scope-collection-exp \"s1.c1\" -g \"#UUID#\"<\/pre>\n
            The import will create a JSON document in collection c1<\/em> for each data point.<\/span><\/p>\n
            {\r\n\u00a0 \u00a0 \"c1\": {\r\n\u00a0 \u00a0 \u00a0 \"Name\": \"XYZ\",\r\n\u00a0 \u00a0 \u00a0 \"close\": 55.99,\r\n\u00a0 \u00a0 \u00a0 \"date\": \"2016-05-25T00:00:00.000Z\",\r\n\u00a0 \u00a0 \u00a0 \"high\": 56.69,\r\n\u00a0 \u00a0 \u00a0 \"low\": 55.7699,\r\n\u00a0 \u00a0 \u00a0 \"open\": 56.47,\r\n\u00a0 \u00a0 \u00a0 \"volume\": 9921707\r\n\u00a0 \u00a0 }\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"c1\": {\r\n\u00a0 \u00a0 \u00a0 \"Name\": \"XYZ\",\r\n\u00a0 \u00a0 \u00a0 \"close\": 31.075,\r\n\u00a0 \u00a0 \u00a0 \"date\": \"2013-06-11T00:00:00.000Z\",\r\n\u00a0 \u00a0 \u00a0 \"high\": 31.47,\r\n\u00a0 \u00a0 \u00a0 \"low\": 30.985,\r\n\u00a0 \u00a0 \u00a0 \"open\": 31.15,\r\n\u00a0 \u00a0 \u00a0 \"volume\": 5540312\r\n\u00a0 \u00a0 }\r\n\u00a0 },\u00a0\u00a0\u2026<\/pre>\n
            2. Use SQL++ to transform the collection c1<\/em> into the time series data structure, then insert into the collection c3:<\/em><\/span><\/p>\n
            INSERT INTO ts.s1.c3 (KEY _k, VALUE _v)\r\nSELECT \"stock:XYZ:2013\" _k,\r\n{\"ticker\": a.Name ,\r\n\u00a0 \"ts_start\" : MIN(STR_TO_MILLIS(a.date)),\r\n\u00a0 \"ts_end\" : MAX(STR_TO_MILLIS(a.date)),\r\n\u00a0 \"ts_data\" : ARRAY_AGG([STR_TO_MILLIS(a.date), a.close]) } _v\u00a0\r\nFROM ts.s1.c1 a\r\nWHERE a.date BETWEEN \"2013-01-01\" AND \"2013-12-31\"\r\nGROUP BY a.Name;<\/pre>\n
            The SQL++ performs an INSERT SELECT<\/em> and creates a single document with the structure required for Couchbase time series processing. Note that the ts_data<\/em> array contains the entire 2013 daily closing price data points.\u00a0<\/span><\/p>\n
            3. Repeat the INSERT\/SELECT for 2014 and 2015.<\/span><\/p>\n
            [\r\n\u00a0 {\r\n\u00a0 \u00a0 \"id\": \"stock:XYZ:2013\",\r\n\u00a0 \u00a0 \"ticker\": \"XYZ\",\r\n\u00a0 \u00a0 \"ts_start\": 1387497600000,\r\n\u00a0 \u00a0 \"ts_end\": 1365465600000,\r\n\u00a0 \u00a0 \"ts_data\": [\r\n\u00a0 \u00a0 \u00a0 [ 1387497600000, 38.67 ],\r\n\u00a0 \u00a0 \u00a0 [ 1380585600000, 36.21 ],\r\n\u00a0 \u00a0 \u00a0 ...]\r\n\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"id\": \"stock:XYZ:2014\",\r\n\u00a0 \u00a0 \"ticker\": \"XYZ\",\r\n\u00a0 \u00a0 \"ts_start\": 1413331200000,\r\n\u00a0 \u00a0 \"ts_end\": 1402444800000,\r\n\u00a0 \u00a0 \"ts_data\": [\r\n\u00a0 \u00a0 \u00a0 [ 1413331200000, 42.59 ],\r\n\u00a0 \u00a0 \u00a0 [ 1399507200000, 36.525],\r\n\u00a0 \u00a0 \u00a0 ...]\r\n\u00a0 },\r\n\u00a0 {\r\n\u00a0 \u00a0 \"id\": \"stock:XYZ:2015\",\r\n\u00a0 \u00a0 \"ticker\": \"XYZ\",\r\n\u00a0 \u00a0 \"ts_start\": 1444780800000,\r\n\u00a0 \u00a0 \"ts_end\": 1436313600000,\r\n\u00a0 \u00a0 \"ts_data\": [\r\n\u00a0 \u00a0 \u00a0 [ 1444780800000, 62.92 ],\r\n\u00a0 \u00a0 \u00a0 [ 1421280000000, 46.405],\r\n\u00a0 \u00a0 \u00a0 ...]\r\n\r\n\u00a0 }\r\n]<\/pre>\n
            Data Ingestion Strategies<\/span><\/h3>\n
            There are several scenarios that you may have for incremental loading of your Time Series JSON documents.<\/span><\/p>\n
              \n
            1. \n
                \n
              1. Adding a range of data points as a new JSON document – For this scenario you can use the above SQL++ INSERT. You just need to ensure that the data points ranges do not overlap existing documents.<\/span><\/li>\n
              2. Adding a range of data points to <\/span>an existing JSON document<\/b> – Here you have two options:<\/span>\n