Quarterly Tech Tips – April 2017

Here is an unedited transcript of the webinar “InfluxDB Tech Tips – April 2017.” This is provided for those who prefer to read than watch the webinar. Please note that the transcript is raw. We apologize for any transcribing errors.

Speakers:
• Regan Kuchan: Technical Writer, InfluxData

Regan Kuchan 00:00.373 All right. Hi. I am Regan, and I’ll be the one giving the webinar this morning. I work at InfluxData, and I write a weekly Tech Tips blog that comes out every Thursday. The blog gives an overview of some of the frequently asked questions that are submitted to our support team or to our community sites, and also covers elements and behaviors that may be unexpected when using the InfluxData products. Typically, I focus on InfluxDB, the Time Series Database component of our TICK stack, but the other products do occasionally sneak in there. Today, I’ll be giving an overview and going over the highlights of what I’ve covered in the Tech Tips blog in this past quarter.

Regan Kuchan 00:43.382 So here’s a short overview of the content that I’ll be talking about. The first section is going to focus on subqueries. Subqueries are new to InfluxDB in version 1.2 and offer a lot of the functionality that you may have been looking for or didn’t even know you needed, so I will definitely be going into detail about those. Next, INTO queries have a very specific, unexpected behavior that can present itself in several different ways. I’ll show you what to be careful with INTO queries and how to avoid any issues with them. Third, version 1.2 included some updates to InfluxDB’s command line interface, the CLI, and they make it that much better and easier to use. And then finally, I’ll go into detail about a couple known issues with InfluxDB and some other random facts that you might want to be aware of.

Regan Kuchan 01:35.597 So let’s start with subqueries. As I mentioned earlier, subqueries are new to InfluxQL in version 1.2. If you take anything away from this webinar, just know that subqueries allow you to get more granular, meaningful insights into your data. The syntax for subqueries is pretty straightforward. The subquery is the query that’s surrounded by parentheses, and it’s nested in the FROM clause of the main query. The main query is everything outside of those parentheses. When InfluxDB executes a query with a subquery, it first runs the subquery, and then it runs the main part of the query on the results of that subquery. I won’t go into this today, but know that InfluxQL does support nested subqueries, that is, a single query can have subqueries nested within subqueries nested within subqueries and so on. They can get a bit complex when you do that, but sometimes you need it.

Now we’re getting to the more interesting stuff, which is why and how you would actually use subqueries. The first main use case is to get the same functionality as nested functions. If you’re a seasoned InfluxQL user, then you probably know that most of our functions do not support nesting in the SELECT clause. So it’s something like nesting mean and max in the SELECT clause is not supported. A couple functions like derivative, difference, and moving average do support nesting, but most do not. Luckily, InfluxQL has subqueries which allow you to nest functions to get the information that you’re after.

Regan Kuchan 03:24.302 Here’s an example of a query with a subquery that calculates the maximum mean value of the passengers field in the schedule measurement grouped by the subway tag. To give you a little more insight into how subqueries actually work, let’s take a look at the results of just a subquery. Remember that InfluxDB first executes the subquery and then the main query on the results of the subquery. The subquery in this case calculates the mean value of passengers in the schedule measurement grouped by the subway tag. The subquery returns two results, 1 for where subway equals U2, and 1 for where the subway equals U6. Notice that the subquery outputs the results with mean as the column headers. This is important because to reference these results in the main query, you need to use main as the field key. Once the system gets the result for the subquery, it runs the main query, which just calculates the maximum of those two mean values. Notice that we use mean as the field key in the main queries max function. So that’s how you get nested functions with subqueries.

Regan Kuchan 04:38.572 Another great use case for subqueries is for performing mathematical operations within a function. As some of you may know, this syntax is not allowed in InfluxQL. You can’t divide fields, add fields, or do any kind of math with fields inside a function. Enter subqueries. This query returns the average number of spilled copies per passenger grouped by the subway tag and the schedule measurement. Again, to give you some insight into how queries and subqueries work underneath it all, here’s the result of just the subquery. The subquery returns the number of spilled copies per passenger from the schedule measurement and grouped by the subway tag. Subway has two tag values, so the subquery returns two tables of results. Notice, that this time, I included an AS clause in the subquery. That clause determines the column headers in the output. So instead of having the default column header, the results now show spills per person as the column header. Once InfluxDB runs a subquery, it runs the main query. In this case, the main query is fairly simple and just calculates the mean value of the spills per person results.

Regan Kuchan 05:59.954 The final use case for subqueries is to use them to perform the same functionality as an SQL having clause. This query calculates the minimum value of passengers and returns only those minimum values that are greater than 15. This syntax is not supported in InfluxQL. You can, however, use subqueries to get the same result. This subquery returns the minimum values of the passenger field calculated for a specific time range and at 10-minute intervals, and it returns the minimum values that are greater than 15. Focusing on just the subquery portion of the query, the subquery calculates the minimum values of passengers from the schedule measurement for that time range and at 10-minute intervals. The results of the subquery show two values, 15 and 19. Notice that, this time, I did not include an AS clause in the query, so the column header is the default header. Once InfluxDB runs a subquery, it runs the main query, which asks for all minimum values that are greater than 15. So all we see in the final results is that 19 value. So those are the three primary resources for subqueries: nested functions, mathematical operations within functions, and having clauses. They’re a pretty cool feature in InfluxQL, so I highly recommend getting to know them if you expect to be working extensively with InfluxQL.

Regan Kuchan 07:34.947 The next main topic for today’s webinar is INTO queries. INTO queries are just InfluxQL select statements that include an INTO clause. They’re great for moving data within an InfluxDB instance, so to a different database or measurement or both. And they’re also great for downsampling data, doing something like taking five-second resolution data, aggregating those data to the one-hour resolution, and storing those one-hour resolution data in a different measurement. They are cool, but today, I’m not going to go into how to use them for those purposes. My goal today is to highlight a behavior of INTO queries that may strike you as odd at first. Once you’re aware of it, it’s easier to avoid any issues with this behavior. But it is essential enough that you do need to know about it to avoid some weirdness. The behavior I’m talking about is, if you don’t explicitly GROUP BY a tag, INTO queries turn any tag in the source measurement into fields in the destination measurement. I’m going to walk through two ways that this issue can pop up.

Regan Kuchan 08:44.358 The first scenario is probably the least scary one of the two. On this slide, I have the data in the source measurement. The concentration measurement has one field, that’s chlorophyll, and one tag, that’s ID. To really bring this home, here’s a query on the source measurement that groups by the ID tag. Remember that GROUP BY clauses only support grouping by time or tags, not by fields. Now, say, for some reason, I want to create a duplicate of the concentration measurement. To do that, I just write an INTO query that writes everything in the concentration measurement to a destination measurement called duplicate concentration. If you go ahead and query the duplicate concentration measurement, you see that the chlorophyll values are there and the ID values are there, so everything looks good. But if you attempt to run that same GROUP BY query that we ran on the previous slide, you see that the query results return something different. Because I didn’t id in the INTO query, id in the duplicate concentration measurement is no longer a tag. It’s now a field, so, unfortunately, we can’t GROUP BY id anymore. The fix for this is pretty straightforward. Just be sure to GROUP BY the id tag in the INTO query. Then, when you go to GROUP BY that tag in the destination measurement, you’ll get the expected results, and all is well.

Regan Kuchan 10:24.325 The second scenario in which this into query behavior comes into play is a little more subtle and is definitely something to watch out for. On this slide, I have a slightly different version of the concentration measurement, which is two points. Once again, chlorophyll is a field and ID is a tag. I decide to use an INTO query to write the data in the concentration measurement to a new measurement called duplicate concentration. Notice in the output that InfluxDB says it writes two points. When I go to query the data in duplicate concentration, however, there’s only one point in that new measurement. So what happened? If you remember the InfluxDB data model, a single point is defined by its measurement, tag set, and time stamp. The chlorophyll points in the original concentration measurement have the same measurement and time stamp. The only thing that differentiates them is the ID tag, which is A for the first point and B for the next point. When the INTO query writes data to the duplicate concentration measurement, it turns the ID tag into a field, so the chlorophyll points now have the same measurement, time stamp, and tag set. So they no longer have a tag set. When InfluxDB encounters a duplicate point like this, it simply overwrites the first point with the second point, and that’s why I ended up with just one point in the duplicate concentration measurement.

Regan Kuchan 11:54.360 Again, the fix for this is relatively straightforward. Just be sure to GROUP BY any tags that you want to preserve as tags in the INTO query. Then when you go to query the duplicate concentration measurement, you see two points because the ID tag is still a tag and still differentiates between the two chlorophyll points. So those are two ways that you might come across the unexpected INTO query behavior. Like I said, it’s a little strange at first, but once you know about it, it’s easy to avoid any complications. Just be sure to GROUP BY your tags. The last longer form topic for today is CLI updates. InfluxDB came out with a couple new CLI features in 1.2 that make interacting with your data just a bit easier. For those of you who don’t know, the CLI, or the command line interface, is an interactive shell providing data to InfluxDB and for querying data and viewing query output in different formats. The new 1.2 features allow you to easily set and unset the target database and or retention policy for queries and [inaudible]. If you use the CLI, you’ll know that before you write or query anything, you need to set the target database with a use command. Starting with version one 1.2, now you can also set the target retention policy with the use command. The generic syntax is in blue at the top of the slide, and the purple block shows an example of this new syntax. The first command sets the target database to a database called target. In this case, any writes or queries would go to the target database and its default retention policy. The second command in the purple block sets the target database and the retention policy—sorry, I shouldn’t have called it target [laughter].

Regan Kuchan 13:53.005 The second command in the purple block sets the target database and retention policy to the target database and the bullseye retention policy. In this case, any writes or queries would go to the target database and the bullseye retention policy. It’s a simple new feature, but it’s actually pretty useful if you’re working with the CLI. The second CLI update in version 1.2 is the all new clear command. Clear allows you to unset the target database or retention policy. The syntax is clear database, or DB, for the database part, and clear retention policy, or RP, for the retention policy part. The example in the purple block shows that, initially, target is the target database and bullseye is the target retention policy. First, we clear the database with clear DB. And then we clear the retention policy with clear RP. Running the settings command again, we see that the CLI is no longer set to target a specific database or retention policy. It’s another simple feature that makes working with the CLI at InfluxDB just that much easier.

Regan Kuchan 15:07.648 The last CLI update is actually change in behavior, not a new feature. In versions prior to 1.2, the INSERT INTO retention policy syntax set the target retention policy for not only that insert statement, but all subsequent insert statements. In version 1.2, we fixed that bug so that the INSERT INTO retention policy syntax only applies to that write, not all subsequent writes. This is just something to be aware of if you were using that bug as more of a feature. Now we’re in the miscellaneous known issues and random tips part of this webinar. The first known issue is that the syntax for microseconds is different depending on what you’re doing with InfluxDB. The cows in this table mark the proper microsecond syntax for each category. Using U to specify microseconds works for writes with the HTTP API, queries, and when specifying timestamp precision in the CLI. The “us” microseconds syntax only works in the configuration file. The “u” symbol is only applicable for queries and, finally, the “u” syntax is only applicable in the configuration file. Yes, this is strange, and we are aware of it, and we are working on fixing it. But until then, this is something to keep in mind if you are a microsecond aficionado.

Regan Kuchan 16:46.774 The next known issue has to do with a configuration setting. This is actually only going to affect you if you’re using InfluxDB version 1.2.0 and 1.2.1. It’s no longer an issue with 1.2.2 and I’ll explain why in just a bit. Oops, I’m sorry. I turned off my notifications of problems. All right. Now that you saw my calendar. Right, so max-row-limit. So this is going to be no longer an issue in 1.2.2 and I’ll explain why in just a bit. But if you are using version 1.2.0 or 1.2.1, this is something you should be aware of. In those versions, the configuration setting max-row-limit is set to 10,000 points by default. That means that queries can only return a maximum of 10,000 rows per query. This is usually most identifiable when you graph your data. Looking at the Chronograf graph on this slide, queries can only return up to 10,000 points, so the graph data become unexpectedly truncated. I can’t actually interact with the image on the slide, but just trust me that I’m expecting to see way more data than that. To disable the automatic 10,000 limit, just set the max-row-limit configuration option to 0 in the HTTP section of your configuration file, and then restart the InfluxDB process. Setting max-row-limit to zero just disables any truncation of query returns. And here’s the pretty graph that I was expecting to see at the start of this max-row-limit discussion. In version 1.2.2, we actually set the maximum row limit to 0 by default, so anyone on that version and on any subsequent versions doesn’t need to worry about this issue.

Regan Kuchan 18:46.569 The final handy tip for this session has to do with InfluxDB’s web admin console that runs on port 8083. The web admin console or interface is a UI that is deprecated in version 1.2 and we’ll be removing it from InfluxDB in a subsequent release. I’m symbolically dimming this image to highlight this fact. We know that several people use the admin interface for exploring their data with select queries, show queries, and for user management. Chronograf, InfluxData’s user interface for querying, digitalizing, and monitoring your data, supports the SELECT queries, show queries, and has a UI for user management. Chronograf is currently in beta and doesn’t support all of the functionality available in the web admin interface but it will soon. So just know that we do have a replacement for the web admin interface and it’s pretty great. Great. So that’s it for today. If you’re interested in getting more information about our products, I’ve included the links to our documentation to Chronograf to my weekly Tech Tips blog and, finally, to the InfluxData Communities. Thank you so much for listening.

Chris Churilo 20:07.649 Okay. That was super-fast. A lot of details behind that. And we’re going to stay here and be available for questions. And as we wait for you guys to type your questions in, I’m just going to ask Regan a couple of questions. So you shared with us a number of really great tips and tricks for being able to use your InfluxDB database efficiently. Of all these tips that you presented, what do you think is probably the trickiest for people to understand?

Regan Kuchan 20:43.593 I actually think the INTO queries are the hardest one, when they turn tags into fields, because it’s when InfluxDB intentionally overwrites points that it thinks are duplicate points, that it can be a little bit scary, to be honest. You kind of have to get back to the fundamentals and really understand how the system identifies a single point and why it’s really doing that. And, again, once you know about it, it’s pretty easy to avoid. But that first moment where you think, “Where are my data going,” it’s a little bit scary.

Chris Churilo 21:17.332 Excellent. And then you’ve also been working on Chronograf, so why don’t we just talk about that a little bit. You also just wrote a blog about it entering into the beta seven phase. So just do a quick comparison of Chronograf versus the web console.

Regan Kuchan 21:35.698 So the web console is actually—it’s super old and hasn’t been touched for a long time, but one of the great things about it is that it does have kind of—you can select templates for queries, and Chronograf actually does a way better job of allowing you to build queries, select queries. It has a query builder that specializes in letting you kind of explore your data. You see all the databases that are available, the measurements, the field keys, and tag keys. So Chronograf actually really takes it to another level. And one thing that Chronograf does that the web console does not is it actually graphs your data. And one of the main features in beta seven was actually dashboard functionality. So starting with beta seven, which is currently out there, you can create dashboards and it has a lot of different graph types. You can do things like single-step, you can do a combination of line and single-step, and you can also just do stacked graphs, I think, is another one. So it’s just really cool for interacting with your data. Yeah. And then another feature of the beta seven release was user management, which I actually showed in this presentation. So it works with the open source user management, so you can create users and delete database users. And it also works with the enterprise version of InfluxDB, and the enterprise version of InfluxDB just has a lot more permissions available for users.

Chris Churilo 23:17.685 So we have a question in the Q&A section. And so the question is the Kapacitor TICK scripts don’t appear to show up in Chronograf. Is that true?

Regan Kuchan 23:30.651 They do not, but this is something that we’re aware of and the team is working on. So just to fill people in, Kapacitor is Influx Data’s monitoring and alerting product. And something that Chronograf does is it provides a UI for creating alerts, rules, and linking Kapacitor with things like Slack so you can send your alerts to Slack. And the TICK scripts are just how Kapacitor determines how to send out an alert and where to send it to. So we are working on kind of integrating the two and making it easy for people who are used to working with Kapacitor on its own, versus working with Kapacitor in more of a UI setting like Chronograf.

Chris Churilo 24:20.761 Excellent. I think one thing that everyone might—that people might enjoy understanding is how do you choose these different tips that you present in your blog?

Regan Kuchan 24:38.433 So a lot of them actually come from the documentation because I kind of write most of the documentation about InfluxDB. And one thing is I like to play around with the software quite a bit. And working with the database, there are some things that I find that are just kind of weird and you have to point them out. Some of them are kind of cool that are difficult to find, so I like to write about those as well. And a lot of times, people write in and you can see some of the questions are actually pretty similar. CONTINUOUS QUERIES is a big one that people write in a lot about. So I try to cover topics that come up in our forums and community.

Chris Churilo 25:20.285 So how would you recommend to everybody on the call today, if they do have areas where maybe the documentation is giving them a little bit of trouble, how do you recommend them making sure that they can get their questions or their areas covered in your weekly Tech Tips?

Regan Kuchan 25:40.061 Yeah. So, actually, our documentation is all open source, so if you do come across something that doesn’t make sense, please do open an issue on GitHub. We really appreciate the feedback, and that’s kind of how we determine what we need to work on, what we need to improve on maintaining our current documentation because stuff does change around a lot, so we’re working on both creating new documentation, also improving what we have. So please do open an issue if you do read something and it makes no sense to you. Another thing that we have is the community site that I mentioned earlier, and that’s where people can just write in with their questions. And sometimes, if enough people kind of start discussing a topic or are obviously struggling with one thing, we’ll create a whole part of our documentation dedicated to that area.