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:
SELECT * -- copies all values INTO "dbTo"."autogen".:MEASUREMENT -- magically keeps the data structure intact FROM "dbFrom"."autogen"./.*/ -- copies all measurements GROUP BY * -- copies all tags
A few details that are not so great about the vanilla SELECT INTO approach:
What this means:
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.
SELECT -- explicit value selecting and casting time, Humidity::integer, Temperature::integer, State INTO "dbTo"."autogen"."sensor" -- explicit target db FROM "dbFrom"."autogen"."sensor" -- explicit source db WHERE time > '2018-03-25' AND time <= '2018-03-26' -- explicit time range GROUP BY * -- copies all tags
I really hope this helps and saves you some time. If you have any questions, feel free to leave a comment.