InfluxQL Shorts Part 2

Here is an unedited transcript of the webinar “InfluxQL Shorts Part 2.” 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.000 Hi and welcome to Part 2 of InfluxQL Shorts. In this series, we introduce the basics of InfluxQL, InfluxDB SQL-like query language. In this segment, we’re going to talk about using the WHERE clause to filter fields and tags.

Regan Kuchan 00:18.643 Here we have a complete InfluxQL SELECT statement. In the previous InfluxQL short, we discussed the SELECT clause and the FROM clause, the two clauses that are required in every SELECT statement. In this segment, we’ll focus on a clause that’s not always required but is essential to writing efficient InfluxQL queries, the WHERE clause.

Regan Kuchan 00:40.836 Before getting into this syntax, there are a couple of things that we’d like to highlight. First, you can use the WHERE clause to filter data based on specific field key-value pairs. Second, you can use the WHERE clause to filter data based on specific tag key-value pairs. Queries that filter data based on tags are going to be more performant than queries that filter data based on fields. This is because tags are indexed in InfluxDB and fields are not indexed. When you filter fields in the WHERE clause, the system has to scan every single relevant field value, but when you filter tags in the WHERE clause, the system doesn’t have to scan every single relevant tag value. So in general, the query that filters tags will be faster. The WHERE clause also allows you to filter data based on timestamps. We won’t be covering that syntax in this segment because that deserves an InfluxQL short of its own.

Regan Kuchan 01:36.992 In the next slides, I’m going to be using sample time series data from the National Oceanic and Atmospheric Administration. We use these data throughout the InfluxQL documentation on our doc site. You can download the data and write them to your InfluxDB instance with the two commands on this slide. These instructions are also available in the docs at this link if you’d like to find them later and play around with the sample data set.

Regan Kuchan 02:03.931 Let’s start with a couple of simple SELECT statements to give you an idea of what the WHERE clause can do. On this slide, we have a basic SELECT statement without a WHERE clause. It returns all fields and tags from the h2o_feet measurement.

Regan Kuchan 02:22.914 This next query includes a WHERE clause that filters data on a field. The query asks for every tag and field from the h2o_feet measurement, and we specify that we only want data where the value of the water_level field is greater than or equal to 7.205. You’ll notice in the results that the field values in the water_level column are only greater than or equal to 7.205.

Regan Kuchan 02:53.777 Here we have a query that includes a WHERE clause and filters data on a tag. It selects every tag and field from the h2o_feet measurement and asks for only those data where the location tag key equals santa_monica. You can see in the results that the tag values in the location column are always santa_monica.

Regan Kuchan 03:15.117 Our last introductory WHERE clause query filters data on a field and tag. This query selects all tags and fields from the h2o_feet measurement and asks for data that have water_level field values greater than or equal to 7.205 and data that have the tag location equal santa_monica. In the results below, we can see that there’s only one data point that meets both of those requirements.

Regan Kuchan 03:42.344 Now that you’ve seen how it works in general, let’s get into some of the specific syntax for working with fields in the WHERE clause. Let’s start with the basic operators. Here we have equals, not equals, and not equals. So those last two in the green box do the same thing. You can use these three operators with numerical field values and string field values. You must include single quotes around the field value if it’s a string. The next set of valid operators are your standard greater than, greater than or equal to, less than, and less than or equal to. This set only works with numerical field values. The last set of operators on the screen are for regular expressions. The first one is matches against, and the second is does not match against. Finally, you can use basic arithmetic on numerical field values in the WHERE clause. This query selects values of water_level from the h2o_feet measurement when the value of water_level plus 2 is greater than 11.95.

Regan Kuchan 04:49.570 This slide covers some of the specific syntax for working with tags in the WHERE clause. We’ve seen this first set of operators in the previous slide. So equals, not equals, and another not equals. Notice that you can only use these operators with strings because all tag values are strings in InfluxDB, and those strings must be single quoted. The last two operators are for regular expressions. Again, the first one is matches against, and the second is does not match against. Unlike fields, you cannot perform basic arithmetic on tags in the WHERE clause. All tag values are strings, so InfluxDB will not allow you to perform any math on them.

Regan Kuchan 05:34.243 This last syntax slide shows the accepted binary operators in the WHERE clause. They are AND and OR, and you can use parentheses to group together logic. In this example, we count the number of water_level field values in the h2o_feet measurement, where water_level is less than zero or water_level is greater than or equal to six, and where the level description field is not between three and six feet, and the location tag is not equal to coyote_creek. The results show that 357 values of water_level meet those criteria.

Regan Kuchan 06:10.326 In these last few slides, we’ll cover some of the common issues that we’ve seen with the WHERE clause. The first issue is what happens when you forget to single quote tag values or string field values. If you forget quotes altogether, and your tag value or string field value has no white spaces, your query will simply return no results. If your tag value or string field value has white spaces, the query will return a parsing error. Next, if you double quote your tag values or string field values, your query will return no results. So be sure to single quote your tag values and string field values in the WHERE clause.

Regan Kuchan 06:53.684 Our next common issue is a bit of a pesky syntax requirement. When performing basic arithmetic on numerical field values, you do need to include a white space between the operator and the operand. If you forget that white space, your query will return a parsing error. So in this case, we forgot the white space between the plus sign and the two, and we get an error. Once you include the white space, our query will return as expected.

Regan Kuchan 07:25.245 Our last common issue is how to select data when a tag has no value. You could do this with a regular expression in the WHERE clause, but the easiest way is just to set the tag equal to two single quotation marks.

Regan Kuchan 07:41.572 That’s it for this InfluxQL short. If you’d like to find out more about the SELECT statement and the WHERE clause, check out the data exploration page in the documentation. It covers what I just talked about and more. The next short will go into detail about specifying timestamps in the WHERE clause. Happy querying.