{"id":4475,"date":"2025-01-20T11:24:26","date_gmt":"2025-01-20T19:24:26","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/couchbase-shell-time-series-data\/"},"modified":"2025-01-20T11:24:26","modified_gmt":"2025-01-20T19:24:26","slug":"couchbase-shell-time-series-data","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/pt\/couchbase-shell-time-series-data\/","title":{"rendered":"Graph Time Series Data From the Comfort of Your Terminal"},"content":{"rendered":"\n

If you have been following the earlier posts on the Couchbase blog, you have seen content about Couchbase Shell<\/a> latest release. I am trying different things and today I wanted to see what I could do with time series datasets.<\/span><\/p>\n\n\n\n

Ingest time series data<\/span><\/h2>\n\n\n\n

Browsing Kaggle, I found temperature data per City<\/a><\/span>. I have downloaded it. It’s about 500Mb and that makes it hard to manipulate as a whole. But, of course, it’s a text file so we can easily peek at the data structure like so:<\/span><\/p>\n\n\n

[crayon line-height=”12″ nums=”false” wrap-toggle=”false” lang=”sh” decode=”true”]> open GlobalLandTemperaturesByCity.csv | first 10<\/p>\n

\u256d\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256e
\n\u2502 # \u2502 \u00a0 \u00a0 dt \u00a0 \u00a0 \u2502 AverageTemperature \u2502 AverageTemperatureUncertainty \u2502 City\u00a0 \u2502 Country \u2502 Latitude \u2502 Longitude \u2502
\n\u251c\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524
\n\u2502 0 \u2502 1743-11-01 \u2502 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 6.07 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1.74 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 1 \u2502 1743-12-01 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 2 \u2502 1744-01-01 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 3 \u2502 1744-02-01 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 4 \u2502 1744-03-01 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 5 \u2502 1744-04-01 \u2502 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 5.79 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3.62 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 6 \u2502 1744-05-01 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 10.64 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1.28 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 7 \u2502 1744-06-01 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 14.05 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1.35 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 8 \u2502 1744-07-01 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 16.08 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1.40 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2502 9 \u2502 1744-08-01 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 \u00c5rhus \u2502 Denmark \u2502 57.05N \u00a0 \u2502 10.33E\u00a0 \u00a0 \u2502
\n\u2570\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256f[\/crayon]<\/p>\n\n\n

[crayon line-height=”12″ nums=”false” lang=”default” decode=”true”]> open GlobalLandTemperaturesByCity.csv | length<\/p>\n

8599212[\/crayon]<\/p>\n\n\n\n

8599212 lines, this is going to be 8599211 documents. My end goal is to see a temperature graph of several cities over the years. To do that, I am going to import everything to an import bucket first, then I will transform the data into time series. <\/span><\/p>\n\n\n\n

If I import this na\u00efvely, it gives me one doc per city\/month group. So the key of my doc will be like \u00c5rhus:1743-11-01<\/em>. Say I only want to upload the first line, it should look like this:<\/span><\/p>\n\n\n

[crayon line-height=”12″ nums=”false” lang=”default” decode=”true”]> open GlobalLandTemperaturesByCity.csv | first 1 | wrap content | insert id { |x| $”($x.content.City):($x.content.dt)” }<\/p>\n

\u256d\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256e
\n\u2502 # \u2502\u00a0 \u00a0 \u00a0 content\u00a0 \u00a0 \u00a0 \u2502\u00a0 \u00a0 \u00a0 \u00a0 id\u00a0 \u00a0 \u00a0 \u00a0 \u2502
\n\u251c\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524
\n\u2502 0 \u2502 {record 7 fields} \u2502 \u00c5rhus:1743-11-01 \u2502
\n\u2570\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256f<\/p>\n

> open GlobalLandTemperaturesByCity.csv | first 1 | wrap content | insert id { |x| $”($x.content.City):($x.content.dt)” } |doc upsert<\/p>\n

\u256d\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256e
\n\u2502 # \u2502 processed \u2502 success \u2502 failed \u2502 failures \u2502 cluster \u2502
\n\u251c\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524
\n\u2502 0 \u2502 \u00a0 \u00a0 \u00a0 \u00a0 1 \u2502 \u00a0 \u00a0 \u00a0 1 \u2502\u00a0 \u00a0 \u00a0 0 \u2502\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2502 capella \u2502
\n\u2570\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u256f
\n[\/crayon]<\/p>\n\n\n\n

And now that I know it works, let’s get everything, and batch it:<\/span><\/p>\n\n\n

[crayon line-height=”12″ nums-toggle=”false” wrap=”true” lang=”default” decode=”true”]open GlobalLandTemperaturesByCity.csv | par-each -t 5 {|x| wrap content | insert id {$”($x.City):($x.dt)” } } | doc upsert<\/p>\n

[\/crayon]<\/p>\n\n\n\n

I have created a <\/span>series<\/span> collection in which I import the result of a SELECT<\/em> aggregation, getting all the dates as timestamps and all the <\/span>AverageTemperature <\/span><\/i>as value:<\/span><\/p>\n\n\n

[crayon line-height=”12″ nums=”false” lang=”default” decode=”true”]query ‘<\/p>\n

INSERT INTO series(KEY _k, VALUE _v)
\n\u00a0\u00a0\u00a0\u00a0SELECT a.City _k,
\n\u00a0\u00a0\u00a0\u00a0{“City”: a.City ,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“ts_start” : MIN(STR_TO_MILLIS(a.dt)),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“ts_end” : MAX(STR_TO_MILLIS(a.dt)),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“ts_data” : ARRAY_AGG([STR_TO_MILLIS(a.dt), a.AverageTemperature]) } _v
\n\u00a0\u00a0\u00a0\u00a0FROM import a
\n\u00a0\u00a0\u00a0\u00a0WHERE a.dt BETWEEN “1700-01-01” AND “2020-12-31”
\n\u00a0\u00a0\u00a0\u00a0GROUP BY a.City;
\n\u00a0\u00a0\u00a0\u00a0‘<\/p>\n

\u00a0\u00a0\u00a0\u00a0INSERT INTO series(KEY _k, VALUE _v)
\n\u00a0\u00a0\u00a0\u00a0SELECT a.City _k,
\n\u00a0\u00a0\u00a0\u00a0{“City”: a.City ,
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“ts_start” : MIN(STR_TO_MILLIS(a.dt)),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“ts_end” : MAX(STR_TO_MILLIS(a.dt)),
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“ts_data” : ARRAY_AGG([STR_TO_MILLIS(a.dt), a.AverageTemperature]) } _v
\n\u00a0\u00a0\u00a0\u00a0FROM import a
\n\u00a0\u00a0\u00a0\u00a0WHERE SUBSTR(a.City,0,1) = “Z” AND a.dt BETWEEN “2010-01-01” AND “2020-12-31”\u00a0
\n\u00a0\u00a0\u00a0\u00a0GROUP BY a.City;
\n[\/crayon]<\/p>\n\n\n\n

And now all the data is available as time series. Say I want the data from Paris, I can use the _timeseries<\/em> function like this:<\/span><\/p>\n\n\n

[crayon nums=”false” lang=”default” decode=”true”]> let ts_ranges = [946684800000,1375315200000]; query $”SELECT t._t time, t._v0 `value` FROM series AS d UNNEST _timeseries(d, {“ts_ranges”:($ts_ranges)}) AS t WHERE d.city = “Paris” AND (d.ts_start <= ($ts_ranges.1) AND d.ts_end >= ($ts_ranges.0) );” | reject cluster | to csv\u00a0\u00a0<\/p>\n

time,value
\n946684800000,3.845
\n949363200000,6.587000000000001
\n951868800000,7.872000000000001
\n954547200000,10.067
\n957139200000,15.451
\n959817600000,17.666
\n962409600000,16.954
\n965088000000,19.512
\n967766400000,16.548000000000002
\n970358400000,11.675999999999998
\n….[\/crayon]<\/p>\n\n\n\n

To speed things up you can create the following index: <\/span>CREATE INDEX ix1 ON series(City, ts_end, ts_start);<\/code><\/span><\/p>\n\n\n\n

Notice the use of the ts_ranges<\/em> variable at the beginning. You can easily reuse those values in a template String<\/em>. They start with a $<\/em> sign and variables must be in parenthesis like ($my_variable)<\/em>. Which also means that now you need to escape parenthesis character as well as double quotes.<\/span><\/p>\n\n\n\n

Plot time series<\/span><\/h2>\n\n\n\n

There are a variety of terminal plotting libraries, here I am using <\/span>youplot<\/span><\/a>:<\/p>\n\n\n

[crayon line-height=”12″ nums=”false” wrap=”true” lang=”default” decode=”true”]> let ts_ranges = [946684800000,1375315200000]; query $”SELECT t._t date, t._v0 `value` FROM series AS d UNNEST _timeseries(d, {“ts_ranges”:($ts_ranges)}) AS t WHERE d.city = “Paris” AND (d.ts_start <= ($ts_ranges.1) AND d.ts_end >= ($ts_ranges.0) );” | reject cluster | to csv\u00a0 | uplot line -d, –xlim 946684800000,1375315200000 –ylim -20,35 –title “global temperature in Paris” –xlabel date –ylabel temperature`[\/crayon]<\/p>\n\n\n\n

\"\"<\/a><\/p>\n\n\n\n

Now that is all great but ideally I want to have several cities in there to compare them. Any additional CSV columns are picked up automatically as long as you run <\/span>youplot lines<\/span>. Doing things gradually, let’s start with the support of multiple cities in the query. So a couple changes here:<\/span><\/p>\n\n\n\n