Get started querying data

Query InfluxDB v3 using SQL and the influx3 CLI.

The following steps include setting up a Python virtual environment already covered in Get started writing data. If your project’s virtual environment is already running, skip to step 3.

1. Create a directory for your project and change into it:

   mkdir influx3-query-example && cd $_
   

2. To create and activate a Python virtual environment, run the following command:

   python -m venv envs/virtual-env && . envs/virtual-env/bin/activate
   

3. Install the CLI package (already installed in the Write data section).

   pip install influxdb3-python-cli
   

   Installing influxdb3-python-cli also installs the pyarrow library for working with Arrow data returned from queries.

4. Create the config.json configuration.

   influx3 config \
     --name="config-dedicated" \
     --database="get-started" \
     --host="cluster-id.influxdb.io" \
     --token="DATABASE_TOKEN" \
     --org="ORG_ID"
   

   Replace the following:

   • DATABASE_TOKEN: a database token with read access to the get-started database
   • ORG_ID: any non-empty string (InfluxDB ignores this parameter, but the client requires it)

5. Enter the influx3 sql command and your SQL query statement.

influx3 sql "SELECT *
              FROM home
              WHERE time >= '2022-01-01T08:00:00Z'
              AND time <= '2022-01-01T20:00:00Z'"

influx3 displays query results in your terminal.

Use the influxdb_client_3 client library module to integrate InfluxDB Cloud Dedicated with your Python code. The client library supports writing data to InfluxDB and querying data using SQL or InfluxQL.

The following steps include setting up a Python virtual environment already covered in Get started writing data. If your project’s virtual environment is already running, skip to step 3.

1. Open a terminal in the influxdb_py_client module directory you created in the Write data section:

   1. To create and activate your Python virtual environment, enter the following command in your terminal:

      python -m venv envs/virtual-env && . ./envs/virtual-env/bin/activate
      

   2. Install the following dependencies:

      * Already installed in the Write data section

      • influxdb3-python*: Provides the InfluxDB influxdb_client_3 Python client library module and also installs the pyarrow package for working with Arrow data returned from queries.
      • pandas: Provides pandas functions, modules, and data structures for analyzing and manipulating data.
      • tabulate: Provides the tabulate function for formatting tabular data. pandas requires this module for formatting data as Markdown.

      In your terminal, enter the following command:

      pip install influxdb3-python pandas tabulate
      

   3. In your terminal or editor, create a new file for your code–for example: query.py.

2. In query.py, enter the following sample code:

   from influxdb_client_3 import InfluxDBClient3
   
   client = InfluxDBClient3(
       host=f"cluster-id.influxdb.io",
       token=f"DATABASE_TOKEN",
       database=f"get-started",
   )
   
   sql = '''
     SELECT
       *
     FROM
       home
     WHERE
       time >= '2022-01-01T08:00:00Z'
       AND time <= '2022-01-01T20:00:00Z'
   '''
   
   table = client.query(query=sql)
   assert table['room'], "Expect table to have room column."
   print(table.to_pandas().to_markdown())
   

The sample code does the following:

1. Imports the InfluxDBClient3 constructor from the influxdb_client_3 module.

2. Calls the InfluxDBClient3() constructor method with credentials to instantiate an InfluxDB client with the following credentials:

   • host: InfluxDB Cloud Dedicated cluster URL (without https:// protocol or trailing slash)
   • token: a database token with read access to the specified database. Store this in a secret store or environment variable to avoid exposing the raw token string.
   • database: the name of the InfluxDB Cloud Dedicated database to query

3. Defines the SQL query to execute and assigns it to a query variable.

4. Calls the client.query() method with the SQL query. query() sends a Flight request to InfluxDB, queries the database, retrieves result data from the endpoint, and then returns a pyarrow.Table assigned to the table variable.

5. Calls the to_pandas() method to convert the Arrow table to a pandas.DataFrame.

6. Calls the pandas.DataFrame.to_markdown() method to convert the DataFrame to a markdown table.

7. Calls the print() method to print the markdown table to stdout.

8. Enter the following command to run the program and query your InfluxDB Cloud Dedicated cluster:

   python query.py
   

1. In the influxdb_go_client directory you created in the Write data section, create a new file named query.go.

2. In query.go, enter the following sample code:

   package main
   
   import (
     "context"
     "fmt"
     "io"
     "os"
     "time"
     "text/tabwriter"
   
     "github.com/apache/arrow/go/v13/arrow"
     "github.com/InfluxCommunity/influxdb3-go/influxdb3"
   )
   
   func Query() error {
   
     // INFLUX_TOKEN is an environment variable you created
     // for your database read token.
     token := os.Getenv("INFLUX_TOKEN")
   
     // Instantiate the client.
     client, err := influxdb3.New(influxdb3.ClientConfig{
       Host:     "https://cluster-id.influxdb.io",
       Token:    token,
       Database: "get-started",
     })
   
     // Close the client when the function returns.
     defer func(client *influxdb3.Client) {
       err := client.Close()
       if err != nil {
         panic(err)
       }
     }(client)
   
     // Define the query.
     query := `SELECT *
       FROM home
       WHERE time >= '2022-01-01T08:00:00Z'
       AND time <= '2022-01-01T20:00:00Z'`
   
     // Execute the query.
     iterator, err := client.Query(context.Background(), query)
   
     if err != nil {
       panic(err)
     }
   
     w := tabwriter.NewWriter(io.Discard, 4, 4, 1, ' ', 0)
     w.Init(os.Stdout, 0, 8, 0, '\t', 0)
     fmt.Fprintln(w, "time\troom\ttemp\thum\tco")
   
     // Iterate over rows and prints column values in table format.
     for iterator.Next() {
       row := iterator.Value()
       // Use Go arrow and time packages to format unix timestamp
       // as a time with timezone layout (RFC3339).
       time := (row["time"].(arrow.Timestamp)).
         ToTime(arrow.TimeUnit(arrow.Nanosecond)).
         Format(time.RFC3339)
       fmt.Fprintf(w, "%s\t%s\t%d\t%.1f\t%.1f\n",
         time, row["room"], row["co"], row["hum"], row["temp"])
     }
   
     w.Flush()
     return nil
   }
   

   The sample code does the following:

   1. Imports the following packages:

      • context
      • fmt
      • io
      • os
      • text/tabwriter
      • github.com/apache/arrow/go/v13/arrow
      • github.com/InfluxCommunity/influxdb3-go/influxdb3

   2. Defines a Query() function that does the following:

      1. Instantiates influx.Client with InfluxDB credentials.

         • Host: your InfluxDB Cloud Dedicated cluster URL
         • Database: The name of your InfluxDB Cloud Dedicated database
         • Token: a database token with read permission on the specified database. Store this in a secret store or environment variable to avoid exposing the raw token string.

      2. Defines a deferred function to close the client after execution.

      3. Defines a string variable for the SQL query.

      4. Calls the influxdb3.Client.Query(sql string) method and passes the SQL string to query InfluxDB. Query(sql string) method returns an iterator for data in the response stream.

      5. Iterates over rows, formats the timestamp as an RFC3339 timestamp, and prints the data in table format to stdout.

3. In your editor, open the main.go file you created in the Write data section and insert code to call the Query() function–for example:

   package main
   
   func main() {	
     WriteLineProtocol()
     Query()
   }
   

4. In your terminal, enter the following command to install the necessary packages, build the module, and run the program:

   go mod tidy && go build && go run influxdb_go_client
   

   The program executes the main() function that writes the data and prints the query results to the console.

This tutorial assumes you installed Node.js and npm, and created an influxdb_js_client npm project as described in the Write data section.

1. In your terminal or editor, change to the influxdb_js_client directory you created in the Write data section.

2. If you haven’t already, install the @influxdata/influxdb3-client JavaScript client library as a dependency to your project:

   npm install --save @influxdata/influxdb3-client
   

3. Create a file named query.mjs. The .mjs extension tells the Node.js interpreter that you’re using ES6 module syntax.

4. Inside of query.mjs, enter the following sample code:

   // query.mjs
   import {InfluxDBClient} from '@influxdata/influxdb3-client'
   import {tableFromArrays} from 'apache-arrow';
   
   /**
   * Set InfluxDB credentials.
   */
   const host = "https://cluster-id.influxdb.io";
   const database = 'get-started';
   /**
   * INFLUX_TOKEN is an environment variable you assigned to your
   * database READ token value.
   */
   const token = process.env.INFLUX_TOKEN;
   
   /**
   * Query InfluxDB with SQL using the JavaScript client library.
   */
   export async function querySQL() {
     /**
     * Instantiate an InfluxDBClient
     */
     const client = new InfluxDBClient({host, token})
     const sql = `
     SELECT *
     FROM home
     WHERE time >= '2022-01-01T08:00:00Z'
       AND time <= '2022-01-01T20:00:00Z'
     `
   
     const data = {time: [], room: [], co: [], hum: [], temp: []};
     const result = client.query(query, database);
   
     for await (const row of result) {
       data.time.push(new Date(row._time))
       data.room.push(row.room)
       data.co.push(row.co);
       data.hum.push(row.hum);
       data.temp.push(row.temp);
     }
   
     console.table([...tableFromArrays(data)])
   
     client.close()
   }
   

   The sample code does the following:

   1. Imports the following:

      • InfluxDBClient class
      • tableFromArrays function

   2. Calls new InfluxDBClient() and passes a ClientOptions object to instantiate a client configured with InfluxDB credentials.

      • host: your InfluxDB Cloud Dedicated cluster URL
      • token: a database token with read permission on the database you want to query. Store this in a secret store or environment variable to avoid exposing the raw token string.

   3. Defines a string variable (sql) for the SQL query.

   4. Defines an object (data) with column names for keys and array values for storing row data.

   5. Calls the InfluxDBClient.query() method with the following arguments:

      • sql: the query to execute
      • database: the name of the InfluxDB Cloud Dedicated database to query

      query() returns a stream of row vectors.

   6. Iterates over rows and adds the column data to the arrays in data.

   7. Passes data to the Arrow tableFromArrays() function to format the arrays as a table, and then passes the result to the console.table() method to output a highlighted table in the terminal.

5. Inside of index.mjs (created in the Write data section), enter the following sample code to import the modules and call the functions:

   // index.mjs
   import { writeLineProtocol } from "./write.mjs";
   import { querySQL } from "./query.mjs";
   
   /**
   * Execute the client functions.
   */
   async function main() {
     /** Write line protocol data to InfluxDB. */
     await writeLineProtocol();
     /** Query data from InfluxDB using SQL. */
     await querySQL();
   }
   
   main();
   

6. In your terminal, execute index.mjs to write to and query InfluxDB Cloud Dedicated:

   node index.mjs
   

1. In the influxdb_csharp_client directory you created in the Write data section, create a new file named Query.cs.

2. In Query.cs, enter the following sample code:

   // Query.cs
   
   using System;
   using System.Threading.Tasks;
   using InfluxDB3.Client;
   using InfluxDB3.Client.Query;
   
   namespace InfluxDBv3;
   
   public class Query
   {
     /**
       * Queries an InfluxDB database using the C# .NET client
       * library.
       **/
     public static async Task QuerySQL()
     {
       /** INFLUX_TOKEN is an environment variable you assigned to your
         * database READ token value.
         **/
       string? token = System.Environment
           .GetEnvironmentVariable("INFLUX_TOKEN");
   
       /**
         * Instantiate the InfluxDB client with credentials.
         **/
       using var client = new InfluxDBClient(
           "https://cluster-id.influxdb.io", token: token, database: database);
   
       const string sql = @"
         SELECT time, room, temp, hum, co
         FROM home
         WHERE time >= '2022-01-01T08:00:00Z'
         AND time <= '2022-01-01T20:00:00Z'
       ";
   
       Console.WriteLine("{0,-30}{1,-15}{2,-15}{3,-15}{4,-15}",
           "time", "room", "co", "hum", "temp");
   
       await foreach (var row in client.Query(query: sql))
       {
         {
           /** 
             * Iterate over rows and print column values in table format.
             * Format the timestamp as sortable UTC format.
             */
           Console.WriteLine("{0,-30:u}{1,-15}{4,-15}{3,-15}{2,-15}",
               row[0], row[1], row[2], row[3], row[4]);
         }
       }
       Console.WriteLine();
     }
   }
   

   The sample code does the following:

   1. Imports the following classes:

      • System
      • System.Threading.Tasks;
      • InfluxDB3.Client;
      • InfluxDB3.Client.Query;

   2. Defines a Query class with a QuerySQL() method that does the following:

      1. Calls the new InfluxDBClient() constructor to instantiate a client configured with InfluxDB credentials.

         • host: your InfluxDB Cloud Dedicated cluster URL.
         • database: the name of the InfluxDB Cloud Dedicated database to query
         • token: a database token with read permission on the specified database. Store this in a secret store or environment variable to avoid exposing the raw token string.

      2. Defines a string variable for the SQL query.

      3. Calls the InfluxDBClient.Query() method to send the query request with the SQL string. Query() returns batches of rows from the response stream as a two-dimensional array–an array of rows in which each row is an array of values.

      4. Iterates over rows and prints the data in table format to stdout.

3. In your editor, open the Program.cs file you created in the Write data section and insert code to call the Query() function–for example:

   // Program.cs
   
   using System;
   using System.Threading.Tasks;
   
   namespace InfluxDBv3;
   
   public class Program
   {
     public static async Task Main()
     {
       await Write.WriteLineProtocol();
       await Query.QuerySQL();
     }
   }
   

4. To build and execute the program and query InfluxDB Cloud Dedicated, enter the following commands in your terminal:

   dotnet run
   

This tutorial assumes using Maven version 3.9, Java version >= 15, and an influxdb_java_client Maven project created in the Write data section.

1. In your terminal or editor, change to the influxdb_java_client directory you created in the Write data section.

2. Inside of the src/main/java/com/influxdbv3 directory, create a new file named Query.java.

3. In Query.java, enter the following sample code:

   // Query.java
   package com.influxdbv3;
   
   import com.influxdb.v3.client.InfluxDBClient;
   import java.util.stream.Stream;
   
   /**
     * Queries an InfluxDB database using the Java client
     * library.
     **/
   public final class Query {
   
       private Query() {
           //not called
       }
   
       /**
       * @throws Exception
       */
       public static void querySQL() throws Exception {
           /**
           * Query using SQL.
           */
   
           /** Set InfluxDB credentials. **/
           final String host = "https://cluster-id.influxdb.io";
           final String database = "get-started";
   
           /** INFLUX_TOKEN is an environment variable you assigned to your
             * database READ token value.
             **/
           final char[] token = (System.getenv("INFLUX_TOKEN")).
           toCharArray();
   
           try (InfluxDBClient client = InfluxDBClient.getInstance(host,
           token, database)) {
               String sql =
                   """
                   SELECT time, room, temp, hum, co
                   FROM home
                   WHERE time >= '2022-01-01T08:00:00Z'
                   AND time <= '2022-01-01T20:00:00Z'""";
   
               String layoutHead = "| %-16s | %-12s | %-6s | %-6s | %-6s |%n";
               System.out.printf(
               "--------------------------------------------------------%n");
               System.out.printf(layoutHead,
               "time", "room", "co", "hum", "temp");
               System.out.printf(
               "--------------------------------------------------------%n");
               String layout = "| %-16s | %-12s | %-6s | %.1f | %.1f |%n";
   
               try (Stream<Object[]> stream = client.query(sql)) {
                   stream.forEach(row -> 
                     System.out.printf(layout,
                     row[0], row[1], row[4], row[3], row[2])
                   );
               }
           }
       }
   }
   

   The sample code does the following:

   1. Assigns the com.influxdbv3 package name (the Maven groupId).

   2. Imports the following classes:

      • com.influxdb.v3.client.InfluxDBClient
      • java.util.stream.Stream

   3. Defines a Query class with a querySQL() method that does the following:

      1. Calls InfluxDBClient.getInstance() to instantiate a client configured with InfluxDB credentials.

         • host: your InfluxDB Cloud Dedicated cluster URL
         • database: the name of the InfluxDB Cloud Dedicated database to write to
         • token: a database token with read permission on the specified database. Store this in a secret store or environment variable to avoid exposing the raw token string.

      2. Defines a string variable (sql) for the SQL query.

      3. Defines a Markdown table format layout for headings and data rows.

      4. Calls the InfluxDBClient.query() method to send the query request with the SQL string. query() returns a stream of rows.

      5. Iterates over rows and prints the data in the specified layout to stdout.

4. In your editor, open the src/main/java/com/influxdbv3/App.java file and replace its contents with the following sample code:

   // App.java
   
   package com.influxdbv3;
   
   /**
   * Execute the client functions.
   *
   */
   public class App {
   
       /**
       * @param args
       * @throws Exception
       */
       public static void main(final String[] args) throws Exception {
           // Write data to InfluxDB v3.
           Write.writeLineProtocol();
           // Run the SQL query.
           Query.querySQL();
       }
   }
   

   • The App, Write, and Query classes belong to the com.influxdbv3 package (your project groupId).
   • App defines a main() function that calls Write.writeLineProtocol() and Query.querySQL().

5. In your terminal or editor, use Maven to to install dependencies and compile the project code–for example:

   mvn compile
   

6. Set the --add-opens=java.base/java.nio=ALL-UNNAMED Java option for your environment. The Apache Arrow Flight library requires this setting for access to the java.nio API package.

   For example, enter the following command in your terminal:

   Linux/MacOS

   export MAVEN_OPTS="--add-opens=java.base/java.nio=ALL-UNNAMED"
   

   Windows PowerShell

   $env:MAVEN_OPTS="--add-opens=java.base/java.nio=ALL-UNNAMED"
   

7. To run the app to write to and query InfluxDB Cloud Dedicated, execute App.main()–for example, using Maven:

   mvn exec:java -Dexec.mainClass="com.influxdbv3.App"