InfluxQL Shorts Part 1

Here is an unedited transcript of the webinar “InfluxQL Shorts Part 1.” 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:01.182 Hi, and welcome to Part 1 of InfluxQL Shorts. In this series, we introduce the basics of InfluxQL, InfluxDB’s SQL-like query language. In this segment, we’re going to talk about the SELECT clause and the FROM clause. Anyone who has worked with SQL will recognize this string of text as a SELECT statement. The select statement is a query that selects the data that you’ve written to your database. InfluxDB’s query language is very similar to SQL, and you’re looking at InfluxQL SELECT statement. Our select statement is made up of several clauses. Some of them are required. Some clauses are optional. And some clauses rely on other clauses.

Regan Kuchan 00:44.074 For this segment, we’re going to focus on the two clauses that are fundamental to every select statement, the SELECT clause and the FROM clause. Before getting into the syntax, there are a couple of things that we want to highlight. These are the main ideas that you should take away from the segment. First, the SELECT clause and the FROM clause are required in every select statement. Every select statement that you write will include those two clauses. Next, you specify field keys and tag keys in the SELECT clause. And finally, you specify the measurement in the FROM clause.

Regan Kuchan 01:22.121 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 the 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. Anyone working with the sample data set alongside this video should know that I’ve had to manually shorten some of the query returns for simplicity and legibility.

Regan Kuchan 01:59.681 Okay. Let’s start with the “Hello, World!” equivalent of the select statement using our sample data. The query is SELECT star from “h2o feet”. The star tells InfluxDB to select all fields and tags from the specified measurement. In this case, the measurement is “h2o feet”, and the measurement is specified in the From clause. Notice that we’ve double quoted the measurement name. In general, we recommend double quoting all identifiers in the select statement. Identifiers include things like database names, retention policy names, measurement names, tag keys, and field keys. Double quotes aren’t required, unless your identifier is also an InfluxQL keyword or if the identifier includes special characters. But it’s generally a good idea to include double quotes just in case.

Regan Kuchan 02:51.176 Because this may be the first InfluxQL query that you’ve seen, let’s take some time to understand the query return. InfluxDB reports the measurement at the top of the query return. The rest of the data, so the fields, tags, and timestamps are returned in tabular format. The Select statement will always return some timestamp in the first time column. In this example, level description, location, and water level are the fields and tags. It’s good to know early on that there’s no way to differentiate between a tag or a field in the query return. Level description and water level are fields in this example, and location is the only tag.

Regan Kuchan 03:38.277 Now that you’ve seen the basics, let’s get into all of the syntax options for the SELECT clause. You’ve already seen the first option on a previous slide. SELECT star returns all fields and tags from the measurement in the FROM clause. A second option is to include a single field key in the SELECT clause. InfluxDB will return all field values that are associated with that field key. Next, you can specify multiple fields or fields and tags by separating them with a comma in the SELECT clause. Yet another option is to specify the identifier type in the SELECT clause. So, in this case, we ask for values of water level only if water level is a field and values of location only if location is a tag. This syntax isn’t necessary for most use cases. You only need this syntax if your schema includes a field key and tag key with the same name. But it’s good to know that this syntax does exist. Finally, the last option is to use a regular expression to specify fields and tags. In this case, we ask for all field keys and tag keys that include the letter L. One item that I’m not mentioning here is InfluxQL functions. The SELECT clause also supports functions like mean, max, and derivative, but they require an InfluxQL short segment of their own. If you’re interested in functions, check out the functions page in the InfluxDB documentation.

Regan Kuchan 05:16.428 There are also several ways to specify measurements in the FROM clause. The first option is something you’ve already seen. Just include the single measurement that you want in the FROM clause. Another option is to specify several measurements. You can separate multiple measurement names with a comma. In this example, we create two measurements, “h2o feet” and “h2o pH”. Next, you can fully qualify the measurement. Fully qualifying a measurement just means specifying the database and retention policy in the FROM clause. Just separate each component with a period. So here, we’re creating data in the NOAA “water database” database, the “autogen” retention policy, and the “h2o feet” measurement. Finally, InfluxQL supports using regular expressions to specify measurements in the FROM clause. In this example, we calculate the average value of degrees for every measurement that includes the word temperature.

Regan Kuchan 06:20.829 In these last few slides, we’ll cover some of the common issues that we’ve seen with the SELECT clause and the FROM clause. First, you can’t specify just a tag key in the SELECT clause. You need to include at least one field key for InfluxDB to return query results. If you only specify a tag key, InfluxDB won’t return any results even if the measurement does have that tag. In the example on the slide, we’ve specified only the location tag in the SELECT clause, and InfluxDB doesn’t return anything for that query. This is a good thing to remember when you’re working with regular expressions in the SELECT clause.

Regan Kuchan 07:01.722 A second common issue is how InfluxDB responds to queries that include field keys, tag keys, or measurement names that don’t exist. InfluxDB doesn’t return an error if you misspell an identifier or if you write one that doesn’t exist in the database. Here, we specify a field key called currents. Currents doesn’t exist in the “h2o feet” measurement, so InfluxDB doesn’t return any query results. Finally, selecting too many data can wreak havoc on your machine. Running something like this query, so SELECT star from all measurements in the database, can overload your system and cause outages. We recommend restricting queries by measurements and time ranges. If you’re only working with the sample data, this query won’t take down your instance because the data set isn’t large enough. But this is something to be aware of as you start working with your own data and storing large amounts of data.

Regan Kuchan 07:58.832 The next InfluxQL short, about the Where clause, will include ways to restrict query coverage. That’s it for this InfluxQL short. If you’d like to find out more about the Select statement and its clauses, 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 the next essential component of the Select statement, the where clause. Happy querying.