• Creating duplicate InfluxDB databases/datasets in InfluxCloud using SELECT INTO

    Creating duplicate InfluxDB databases/datasets in InfluxCloud using SELECT INTO

    InfluxCloud Chronograf currently doesn’t provide a fast and easy way of creating duplicates of your existing databases. Their support (which comes with the paid service) is sadly also very slow and it’s a bit of a chore to make them do anything for you within a reasonable timeframe unless it’s something supercritical.

    So, if you’re in need of a way to create a backup and re-create a DB multiple times because you’re looking to test some destructive actions on your data (such as updating records) and you’re looking to avoid dealing with the “support”, here’s a hacky way to deal with it which might end up being the faster thing to do.

    Meet Mr. SELECT INTO. What SELECT INTO aims to do is to help you out in copying all your data from all the measurements you might have into a newly created database (has to be pre-created before running the query). It also aims to keep your existing data structure (measurement names and what records go where). In its simplest form and in ideal circumstances the query that you’ll need to execute will look like this:

    A few details that are not so great about the vanilla SELECT INTO approach:

    1. Continuous Queries will not be copied over.
    2. If you try to run the SELECT INTO query from the Chronograf “Data Explorer”, and you have a dataset which is not trivially small – the Chronograf request with your query will timeout after 60s and the query will simply stop. It won’t even continue running in the background.
    3. Data types of some values might change for no apparent reason.. For example INT columns (values) might get converted to FLOATs upon data copying although all the data stored in these columns WAS initially actually INT data.. (?!) This is interesting because it might come back later and bite you once you try to execute aggregate functions to backfill some of the downsampled measurements with new/fixed data. What exactly is the issue with that? For example the SELECT INTO query might not convert INT’s to FLOAT’s for downsampled measurements (which are typically populated by CQ’s) but only for the “lowest resolution” measurements (measurement you insert the data into explicitly). So after your happily copied over all the data, if you try to insert a max(thought_it_was_an_int) from the default measurement into a downsampled one it will now try to store a FLOAT into an INT column and this will obviously return an exception. Happy days.

     

    What this means:

    1. Probably not a big deal if you just want to mess with the data, but keep it in mind.
    2. You’ll have to break your data down into smaller datasets to copy over (for example into one day data chunks using a WHERE time … clause). You’ll probably need an SDK library (such as InfluxData.Net) to automate the process.
    3. You’ll have to ditch the wildcard SELECT from the vanilla example and manually select (and explicitly cast) all the columns that you want to copy over to the new DB. This will make the process slightly more complicated but at this point you’re probably automating everything through a small script / migration app anyway so it shouldn’t be too much additional work.

     

    So, here’s a proof-of-concept code sample that explicitly selects columns to copy over, forces casting to INT data type (or keeps the data type intact), selects specific source and target measurements (tables) and selects only a single day worth of data. You will have to iterate through 1-2 loops to inject your desired source and target table names and time ranges to cover all the data that you’ve got in your DB.

    I really hope this helps and saves you some time. If you have any questions, feel free to leave a comment.

    #FastNotFastButMaybeFaster

    Cheers!

  • InfluxDB Studio

    InfluxDB Studio

    It feels great when someone uses something you made and thinks it’s good. Every now and then someone creates a pull request for the InfuxData.Net lib I made and have been maintaining. It’s sort of a nice confirmation of your efforts and makes you warm and fuzzy on the inside. :D

    Well, this guy named meverett recently made a few pull requests with fixes and some nice lib improvements AND then told me that he’s actually using the library to make an InfluxDB database management studio (sort of what SQL Management Studio is for SQL, but for InfluxDB)!

    I find the admin dashboard that comes with InfluxDB a bit too simplistic and buggy and was actually thinking about how useful it would be if only there was a proper management app for Influx. Well, the InfluxDB Studio does more than the web admin dashboard and works great. And it made me really happy! :D

    This is how it looks:

    Thanks for the great work meverett!

  • Contributing back – InfluxData.Net NuGet

    Contributing back – InfluxData.Net NuGet

    On project that I currently work on in Dovetail, we’re using the InfluxDb time-series database. And it’s a really cool thing to be working with.

    Recently we had to move to a newer version of InfluxDb and the library that we were using at the time became a bit stale and didn’t support the newest version of InfluxDb (v0.9.6) which we had to move to. So, partially out of necessity, and partially “because I can” and wan to, I decided to update the currently existing library and then re-release it as a NuGet package under a slightly different name.

    *drumroll* – the code for the InfluxData.Net NuGet is available on Github. It already had a few code contributions which is quite exciting. The whole codebase has been refactored, additional tests written and I believe that the codebase is now quite stable. I plan on expanding the library API to support most of the stuff that InfluxDb provides, and I also plan on implementing the API’s for other InfluxData products such as Kapacitor and Telegraf.

    If people keep using it in the future, I’ll consider it a success and it will make me happy. :)

Back to top