Start coding with Tiger Data

Integrate your app with Tiger Cloud using your preferred programming language

Easily integrate your app with or . Use your favorite programming language to connect to your , create and manage s, then ingest and query data.

Ruby

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

• Install Rails.

Connect a Rails app to your service

Every is a 100% database hosted in with extensions such as . You connect to your from a standard Rails app configured for .

1. Create a new Rails app configured for

   Rails creates and bundles your app, then installs the standard Gems.

   rails new my_app -d=postgresql
   cd my_app
   

2. Install the gem

   1. Open Gemfile, add the following line, then save your changes:

      gem 'timescaledb'
      

   2. In Terminal, run the following command:

      bundle install
      

3. Connect your app to your

   1. In <my_app_home>/config/database.yml update the configuration to read securely connect to your by adding url: <%= ENV['DATABASE_URL'] %> to the default configuration:

      default: &default
        adapter: postgresql
        encoding: unicode
        pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
        url: <%= ENV['DATABASE_URL'] %>
      

   2. Set the environment variable for DATABASE_URL to the value of Service URL from your connection details

      export DATABASE_URL="value of Service URL"
      

   3. Create the database:

      • : nothing to do. The database is part of your .

      • , create the database for the project:

        rails db:create
        

   4. Run migrations:

      rails db:migrate
      

   5. Verify the connection from your app to your :

      echo "\dx" | rails dbconsole
      

      The result shows the list of extensions in your

      Name	Version	Schema	Description
      pg_buffercache	1.5	public	examine the shared buffer cache
      pg_stat_statements	1.11	public	track planning and execution statistics of all SQL statements executed
      plpgsql	1.0	pg_catalog	PL/pgSQL procedural language
      postgres_fdw	1.1	public	foreign-data wrapper for remote servers
      timescaledb	2.24.0	public	Enables scalable inserts and complex queries for time-series data (Community Edition)
      timescaledb_toolkit	1.22.0	public	Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities

Optimize time-series data in hypertables

s are tables designed to simplify and accelerate data analysis. Anything you can do with regular tables, you can do with s - but much faster and more conveniently.

In this section, you use the helpers in the gem to create and manage a .

1. Generate a migration to create the page loads table

   rails generate migration create_page_loads
   

   This creates the <my_app_home>/db/migrate/<migration-datetime>_create_page_loads.rb migration file.

2. Add hypertable options

   Replace the contents of <my_app_home>/db/migrate/<migration-datetime>_create_page_loads.rb with the following:

   class CreatePageLoads < ActiveRecord::Migration[8.0]
     def change
       hypertable_options = {
         time_column: 'created_at',
         chunk_time_interval: '1 day',
         compress_segmentby: 'path',
         compress_orderby: 'created_at',
         compress_after: '7 days',
         drop_after: '30 days'
       }
   
       create_table :page_loads, id: false, primary_key: [:created_at, :user_agent, :path], hypertable: hypertable_options do |t|
         t.timestamptz :created_at, null: false
         t.string :user_agent
         t.string :path
         t.float :performance
       end
     end
   end
   

   The id column is not included in the table. This is because requires that any UNIQUE or PRIMARY KEY indexes on the table include all partitioning columns. In this case, this is the time column.

   A new Rails model includes a PRIMARY KEY index for id by default: either remove the column or make sure that the index includes time as part of a “composite key.”

   For more information, check the Ruby docs around composite primary keys.

3. Create a PageLoad model

   Create a new file called <my_app_home>/app/models/page_load.rb and add the following code:

   class PageLoad < ApplicationRecord
     extend Timescaledb::ActsAsHypertable
     include Timescaledb::ContinuousAggregatesHelper
   
     acts_as_hypertable time_column: "created_at",
       segment_by: "path",
       value_column: "performance"
   
     # Basic scopes for filtering by browser
     scope :chrome_users, -> { where("user_agent LIKE ?", "%Chrome%") }
     scope :firefox_users, -> { where("user_agent LIKE ?", "%Firefox%") }
     scope :safari_users, -> { where("user_agent LIKE ?", "%Safari%") }
   
     # Performance analysis scopes
     scope :performance_stats, -> {
       select("stats_agg(#{value_column}) as stats_agg")
     }
   
     scope :slow_requests, -> { where("performance > ?", 1.0) }
     scope :fast_requests, -> { where("performance < ?", 0.1) }
   
     # Set up continuous aggregates for different timeframes
     continuous_aggregates scopes: [:performance_stats],
       timeframes: [:minute, :hour, :day],
       refresh_policy: {
         minute: {
           start_offset: '3 minute',
           end_offset: '1 minute',
           schedule_interval: '1 minute'
         },
         hour: {
           start_offset: '3 hours',
           end_offset: '1 hour',
           schedule_interval: '1 minute'
         },
         day: {
           start_offset: '3 day',
           end_offset: '1 day',
           schedule_interval: '1 minute'
         }
       }
   end
   

4. Run the migration

   rails db:migrate
   

Insert data your service

The gem provides efficient ways to insert data into s. This section shows you how to ingest test data into your .

1. Create a controller to handle page loads

   Create a new file called <my_app_home>/app/controllers/application_controller.rb and add the following code:

   class ApplicationController < ActionController::Base
     around_action :track_page_load
   
     private
   
     def track_page_load
       start_time = Time.current
       yield
       end_time = Time.current
   
       PageLoad.create(
         path: request.path,
         user_agent: request.user_agent,
         performance: (end_time - start_time)
       )
     end
   end
   

2. Generate some test data

   Use bin/console to join a Rails console session and run the following code to define some random page load access data:

   def generate_sample_page_loads(total: 1000)
     time = 1.month.ago
     paths = %w[/ /about /contact /products /blog]
     browsers = [
       "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.114 Safari/537.36",
       "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0",
       "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Safari/605.1.15"
     ]
   
     total.times.map do
       time = time + rand(60).seconds
       {
         path: paths.sample,
         user_agent: browsers.sample,
         performance: rand(0.1..2.0),
         created_at: time,
         updated_at: time
       }
     end
   end
   

3. Insert the generated data into your

   # Insert the data in batches
   PageLoad.insert_all(generate_sample_page_loads, returning: false)
   

4. Validate the test data in your

   PageLoad.count
   PageLoad.first
   

Reference

This section lists the most common tasks you might perform with the gem.

Query scopes

The gem provides several convenient scopes for querying your time-series data.

• Built-in time-based scopes:

  PageLoad.last_hour.count
  PageLoad.today.count
  PageLoad.this_week.count
  PageLoad.this_month.count
  

• Browser-specific scopes:

  # Count requests by browser
  PageLoad.chrome_users.last_hour.count
  PageLoad.firefox_users.last_hour.count
  PageLoad.safari_users.last_hour.count
  
  # Performance analysis
  PageLoad.slow_requests.last_hour.count
  PageLoad.fast_requests.last_hour.count
  

• Query s:

  This query fetches the average and standard deviation from the performance stats for the /products path over the last day.

  # Access aggregated performance stats through generated classes
  PageLoad::PerformanceStatsPerMinute.last_hour
  PageLoad::PerformanceStatsPerHour.last_day
  PageLoad::PerformanceStatsPerDay.last_month
  
  # Get statistics for a specific path
  stats = PageLoad::PerformanceStatsPerHour.last_day.where(path: '/products').select("average(stats_agg) as average, stddev(stats_agg) as stddev").first
  puts "Average: #{stats.average}"
  puts "Standard Deviation: #{stats.stddev}"
  

TimescaleDB features

The gem provides utility methods to access and information. Every model that uses the acts_as_hypertable method has access to these methods.

Access hypertable and chunk information

• View or information:

  PageLoad.chunks.count
  PageLoad.hypertable.detailed_size
  

• Compress/Decompress s:

  PageLoad.chunks.uncompressed.first.compress!
  PageLoad.chunks.compressed.first.decompress!
  PageLoad.hypertable.compression_stats
  

Access hypertable stats

You collect stats using methods that provide insights into your ‘s structure, size, and compression status:

• Get basic information:

  hypertable = PageLoad.hypertable
  hypertable.hypertable_name
  hypertable.schema_name
  

• Get detailed size information:

  hypertable.detailed_size
  hypertable.compression_stats
  hypertable.chunks_detailed_size
  hypertable.approximate_row_count
  hypertable.dimensions.map(&:column_name)
  hypertable.continuous_aggregates.map(&:view_name)
  

Continuous aggregates

The continuous_aggregates method generates a class for each .

• Get all the classes:

  PageLoad.descendants
  

• Manually refresh a :

  PageLoad.refresh_aggregates
  

• Create or drop a :

  Create or drop all the s in the proper order to build them hierarchically. See more about how it works in this blog post.

  PageLoad.create_continuous_aggregates
  PageLoad.drop_continuous_aggregates
  

Next steps

Now that you have integrated the ruby gem into your app:

• Learn more about the gem.
• Check out the official docs.
• Follow the LTTB, Open AI long-term storage, and candlesticks tutorials.

Python

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

• Install the psycopg2 library.

  For more information, see the psycopg2 documentation.

• Create a Python virtual environment. (Optional)

Connect to TimescaleDB

In this section, you create a connection to using the psycopg2 library. This library is one of the most popular libraries for Python. It allows you to execute raw SQL queries efficiently and safely, and prevents common attacks such as SQL injection.

1. Import the psycogpg2 library:

   import psycopg2
   

2. Locate your credentials and use them to compose a connection string for psycopg2.

   You’ll need:

   • password
   • username
   • host URL
   • port
   • database name

3. Compose your connection string variable as a libpq connection string, using this format:

   CONNECTION = "postgres://username:password@host:port/dbname"
   

   If you’re using a hosted version of , or generally require an SSL connection, use this version instead:

   CONNECTION = "postgres://username:password@host:port/dbname?sslmode=require"
   

   Alternatively you can specify each parameter in the connection string as follows

   CONNECTION = "dbname=tsdb user=tsdbadmin password=secret host=host.com port=5432 sslmode=require"
   
   This method of composing a connection string is for test or development purposes only. For production, use environment variables for sensitive details like your password, hostname, and port number.

4. Use the psycopg2 connect function to create a new database session and create a new cursor object to interact with the database.

   In your main function, add these lines:

   CONNECTION = "postgres://username:password@host:port/dbname"
   with psycopg2.connect(CONNECTION) as conn:
       cursor = conn.cursor()
       # use the cursor to interact with your database
       # cursor.execute("SELECT * FROM table")
   

   Alternatively, you can create a connection object and pass the object around as needed, like opening a cursor to perform database operations:

   CONNECTION = "postgres://username:password@host:port/dbname"
   conn = psycopg2.connect(CONNECTION)
   cursor = conn.cursor()
   # use the cursor to interact with your database
   cursor.execute("SELECT 'hello world'")
   print(cursor.fetchone())
   

Create a relational table

In this section, you create a table called sensors which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called sensor_data which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors.

1. Compose a string which contains the SQL statement to create a relational table. This example creates a table called sensors, with columns id, type and location:

   query_create_sensors_table = """CREATE TABLE sensors (
                                       id SERIAL PRIMARY KEY,
                                       type VARCHAR(50),
                                       location VARCHAR(50)
                                   );
                                   """
   

2. Open a cursor, execute the query you created in the previous step, and commit the query to make the changes persistent. Afterward, close the cursor to clean up:

   cursor = conn.cursor()
   # see definition in Step 1
   cursor.execute(query_create_sensors_table)
   conn.commit()
   cursor.close()
   

Create a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

1. Create a string variable that contains the CREATE TABLE SQL statement for your hypertable. Notice how the hypertable has the compulsory time column:

   # create sensor data hypertable
   query_create_sensordata_table = """CREATE TABLE sensor_data (
                                           time TIMESTAMPTZ NOT NULL,
                                           sensor_id INTEGER,
                                           temperature DOUBLE PRECISION,
                                           cpu DOUBLE PRECISION,
                                           FOREIGN KEY (sensor_id) REFERENCES sensors (id)
                                       );
                                       """
   

2. Formulate a SELECT statement that converts the sensor_data table to a hypertable. You must specify the table name to convert to a hypertable, and the name of the time column as the two arguments. For more information, see the create_hypertable docs:

   query_create_sensordata_hypertable = "SELECT create_hypertable('sensor_data', by_range('time'));"
   
   The by_range dimension builder is an addition to 2.13.

3. Open a cursor with the connection, execute the statements from the previous steps, commit your changes, and close the cursor:

   cursor = conn.cursor()
   cursor.execute(query_create_sensordata_table)
   cursor.execute(query_create_sensordata_hypertable)
   # commit changes to the database to make changes persistent
   conn.commit()
   cursor.close()
   

Insert rows of data

You can insert data into your hypertables in several different ways. In this section, you can use psycopg2 with prepared statements, or you can use pgcopy for a faster insert.

1. This example inserts a list of tuples, or relational data, called sensors, into the relational table named sensors. Open a cursor with a connection to the database, use prepared statements to formulate the INSERT SQL statement, and then execute that statement:

   sensors = [('a', 'floor'), ('a', 'ceiling'), ('b', 'floor'), ('b', 'ceiling')]
   cursor = conn.cursor()
   for sensor in sensors:
     try:
       cursor.execute("INSERT INTO sensors (type, location) VALUES (%s, %s);",
                   (sensor[0], sensor[1]))
     except (Exception, psycopg2.Error) as error:
       print(error.pgerror)
   conn.commit()
   

2. (Optional) Alternatively, you can pass variables to the cursor.execute function and separate the formulation of the SQL statement, SQL, from the data being passed with it into the prepared statement, data:

   SQL = "INSERT INTO sensors (type, location) VALUES (%s, %s);"
   sensors = [('a', 'floor'), ('a', 'ceiling'), ('b', 'floor'), ('b', 'ceiling')]
   cursor = conn.cursor()
   for sensor in sensors:
     try:
       data = (sensor[0], sensor[1])
       cursor.execute(SQL, data)
     except (Exception, psycopg2.Error) as error:
       print(error.pgerror)
   conn.commit()
   

   If you choose to use pgcopy instead, install the pgcopy package using pip, and then add this line to your list of import statements:

   from pgcopy import CopyManager
   

3. Generate some random sensor data using the generate_series function provided by . This example inserts a total of 480 rows of data (4 readings, every 5 minutes, for 24 hours). In your application, this would be the query that saves your time-series data into the hypertable:

   # for sensors with ids 1-4
   for id in range(1, 4, 1):
       data = (id,)
       # create random data
       simulate_query = """SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
                               %s as sensor_id,
                               random()*100 AS temperature,
                               random() AS cpu;
                               """
       cursor.execute(simulate_query, data)
       values = cursor.fetchall()
   

4. Define the column names of the table you want to insert data into. This example uses the sensor_data hypertable created earlier. This hypertable consists of columns named time, sensor_id, temperature and cpu. The column names are defined in a list of strings called cols:

   cols = ['time', 'sensor_id', 'temperature', 'cpu']
   

5. Create an instance of the pgcopy CopyManager, mgr, and pass the connection variable, hypertable name, and list of column names. Then use the copy function of the CopyManager to insert the data into the database quickly using pgcopy.

   mgr = CopyManager(conn, 'sensor_data', cols)
   mgr.copy(values)
   

6. Commit to persist changes:

   conn.commit()
   

7. (Optional) The full sample code to insert data into using pgcopy, using the example of sensor data from four sensors:

   # insert using pgcopy
   def fast_insert(conn):
       cursor = conn.cursor()
   
       # for sensors with ids 1-4
       for id in range(1, 4, 1):
           data = (id,)
           # create random data
           simulate_query = """SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
                                   %s as sensor_id,
                                   random()*100 AS temperature,
                                   random() AS cpu;
                                   """
           cursor.execute(simulate_query, data)
           values = cursor.fetchall()
   
           # column names of the table you're inserting into
           cols = ['time', 'sensor_id', 'temperature', 'cpu']
   
           # create copy manager with the target table and insert
           mgr = CopyManager(conn, 'sensor_data', cols)
           mgr.copy(values)
   
       # commit after all sensor data is inserted
       # could also commit after each sensor insert is done
       conn.commit()
   

8. (Optional) You can also check if the insertion worked:

   cursor.execute("SELECT * FROM sensor_data LIMIT 5;")
   print(cursor.fetchall())
   

Execute a query

This section covers how to execute queries against your database.

The first procedure shows a simple SELECT * query. For more complex queries, you can use prepared statements to ensure queries are executed safely against the database.

For more information about properly using placeholders in psycopg2, see the basic module usage document. For more information about how to execute more complex queries in psycopg2, see the psycopg2 documentation.

Execute a query

1. Define the SQL query you’d like to run on the database. This example is a simple SELECT statement querying each row from the previously created sensor_data table.

   query = "SELECT * FROM sensor_data;"
   

2. Open a cursor from the existing database connection, conn, and then execute the query you defined:

   cursor = conn.cursor()
   query = "SELECT * FROM sensor_data;"
   cursor.execute(query)
   

3. To access all resulting rows returned by your query, use one of pyscopg2’s results retrieval methods, such as fetchall() or fetchmany(). This example prints the results of the query, row by row. Note that the result of fetchall() is a list of tuples, so you can handle them accordingly:

   cursor = conn.cursor()
   query = "SELECT * FROM sensor_data;"
   cursor.execute(query)
   for row in cursor.fetchall():
       print(row)
   cursor.close()
   

4. (Optional) If you want a list of dictionaries instead, you can define the cursor using DictCursor:

   cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
   

   Using this cursor, cursor.fetchall() returns a list of dictionary-like objects.

For more complex queries, you can use prepared statements to ensure queries are executed safely against the database.

Execute queries using prepared statements

Write the query using prepared statements:

# query with placeholders
cursor = conn.cursor()
query = """
           SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
           FROM sensor_data
           JOIN sensors ON sensors.id = sensor_data.sensor_id
           WHERE sensors.location = %s AND sensors.type = %s
           GROUP BY five_min
           ORDER BY five_min DESC;
           """
location = "floor"
sensor_type = "a"
data = (location, sensor_type)
cursor.execute(query, data)
results = cursor.fetchall()

Node.js

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

• Install Node.js.
• Install the Node.js package manager npm.

Connect to TimescaleDB

In this section, you create a connection to with a common Node.js ORM (object relational mapper) called Sequelize.

1. At the command prompt, initialize a new Node.js app:

   npm init -y
   

   This creates a package.json file in your directory, which contains all of the dependencies for your project. It looks something like this:

   {
     "name": "node-sample",
     "version": "1.0.0",
     "description": "",
     "main": "index.js",
     "scripts": {
       "test": "echo \"Error: no test specified\" && exit 1"
     },
     "keywords": [],
     "author": "",
     "license": "ISC"
   }
   

2. Install Express.js:

   npm install express
   

3. Create a simple web page to check the connection. Create a new file called index.js, with this content:

   const express = require('express')
   const app = express()
   const port = 3000;
   
   app.use(express.json());
   app.get('/', (req, res) => res.send('Hello World!'))
   app.listen(port, () => console.log(`Example app listening at http://localhost:${port}`))
   

4. Test your connection by starting the application:

   node index.js
   

   In your web browser, navigate to http://localhost:3000. If the connection is successful, it shows “Hello World!”

5. Add Sequelize to your project:

   npm install sequelize sequelize-cli pg pg-hstore
   

6. Locate your credentials and use them to compose a connection string for Sequelize.

   You’ll need:

   • password
   • username
   • host URL
   • port
   • database name

7. Compose your connection string variable, using this format:

   'postgres://<user>:<password>@<host>:<port>/<dbname>'
   

8. Open the index.js file you created. Require Sequelize in the application, and declare the connection string:

   const Sequelize = require('sequelize')
   const sequelize = new Sequelize('postgres://<user>:<password>@<host>:<port>/<dbname>',
       {
           dialect: 'postgres',
           protocol: 'postgres',
           dialectOptions: {
               ssl: {
                   require: true,
                   rejectUnauthorized: false
               }
           }
       })
   

   Make sure you add the SSL settings in the dialectOptions sections. You can’t connect to using SSL without them.

9. You can test the connection by adding these lines to index.js after the app.get statement:

   sequelize.authenticate().then(() => {
       console.log('Connection has been established successfully.');
   }).catch(err => {
       console.error('Unable to connect to the database:', err);
   });
   

   Start the application on the command line:

   node index.js
   

   If the connection is successful, you’ll get output like this:

   Example app listening at http://localhost:3000
   Executing (default): SELECT 1+1 AS result
   Connection has been established successfully.
   

Create a relational table

In this section, you create a relational table called page_loads.

1. Use the Sequelize command line tool to create a table and model called page_loads:

   npx sequelize model:generate --name page_loads \
   --attributes userAgent:string,time:date
   

   The output looks similar to this:

   Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
   
   New model was created at <PATH>.
   New migration was created at <PATH>.
   

2. Edit the migration file so that it sets up a migration key:

   'use strict';
   module.exports = {
     up: async (queryInterface, Sequelize) => {
       await queryInterface.createTable('page_loads', {
         userAgent: {
           primaryKey: true,
           type: Sequelize.STRING
         },
         time: {
           primaryKey: true,
           type: Sequelize.DATE
         }
       });
     },
     down: async (queryInterface, Sequelize) => {
       await queryInterface.dropTable('page_loads');
     }
   };
   

3. Migrate the change and make sure that it is reflected in the database:

   npx sequelize db:migrate
   

   The output looks similar to this:

   Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
   
   Loaded configuration file "config/config.json".
   Using environment "development".
   == 20200528195725-create-page-loads: migrating =======
   == 20200528195725-create-page-loads: migrated (0.443s)
   

4. Create the PageLoads model in your code. In the index.js file, above the app.use statement, add these lines:

   let PageLoads = sequelize.define('page_loads', {
       userAgent: {type: Sequelize.STRING, primaryKey: true },
       time: {type: Sequelize.DATE, primaryKey: true }
   }, { timestamps: false });
   

5. Instantiate a PageLoads object and save it to the database.

Create a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

1. Create a migration to modify the page_loads relational table, and change it to a hypertable by first running the following command:

   npx sequelize migration:generate --name add_hypertable
   

   The output looks similar to this:

   Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
   
   migrations folder at <PATH> already exists.
   New migration was created at <PATH>/20200601202912-add_hypertable.js .
   

2. In the migrations folder, there is now a new file. Open the file, and add this content:

   'use strict';
   
   module.exports = {
     up: (queryInterface, Sequelize) => {
       return queryInterface.sequelize.query("SELECT create_hypertable('page_loads', by_range('time'));");
     },
   
     down: (queryInterface, Sequelize) => {
     }
   };
   
   The by_range dimension builder is an addition to 2.13.

3. At the command prompt, run the migration command:

   npx sequelize db:migrate
   

   The output looks similar to this:

   Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11]
   
   Loaded configuration file "config/config.json".
   Using environment "development".
   == 20200601202912-add_hypertable: migrating =======
   == 20200601202912-add_hypertable: migrated (0.426s)
   

Insert rows of data

This section covers how to insert data into your hypertables.

1. In the index.js file, modify the / route to get the user-agent from the request object (req) and the current timestamp. Then, call the create method on PageLoads model, supplying the user agent and timestamp parameters. The create call executes an INSERT on the database:

   app.get('/', async (req, res) => {
       // get the user agent and current time
       const userAgent = req.get('user-agent');
       const time = new Date().getTime();
   
       try {
           // insert the record
           await PageLoads.create({
               userAgent, time
           });
   
           // send response
           res.send('Inserted!');
       } catch (e) {
           console.log('Error inserting data', e)
       }
   })
   

Execute a query

This section covers how to execute queries against your database. In this example, every time the page is reloaded, all information currently in the table is displayed.

1. Modify the / route in the index.js file to call the Sequelize findAll function and retrieve all data from the page_loads table using the PageLoads model:

   app.get('/', async (req, res) => {
       // get the user agent and current time
       const userAgent = req.get('user-agent');
       const time = new Date().getTime();
   
       try {
           // insert the record
           await PageLoads.create({
               userAgent, time
           });
   
           // now display everything in the table
           const messages = await PageLoads.findAll();
           res.send(messages);
       } catch (e) {
           console.log('Error inserting data', e)
       }
   })
   

Now, when you reload the page, you should see all of the rows currently in the page_loads table.

Go

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

• Install Go.
• Install the PGX driver for Go.

Connect to your service

In this section, you create a connection to using the PGX driver. PGX is a toolkit designed to help Go developers work directly with . You can use it to help your Go application interact directly with TimescaleDB.

1. Locate your credentials and use them to compose a connection string for PGX.

   You’ll need:

   • password
   • username
   • host URL
   • port number
   • database name

2. Compose your connection string variable as a libpq connection string, using this format:

   connStr := "postgres://username:password@host:port/dbname"
   

   If you’re using a hosted version of TimescaleDB, or if you need an SSL connection, use this format instead:

   connStr := "postgres://username:password@host:port/dbname?sslmode=require"
   

3. (Optional) You can check that you’re connected to your database with this hello world program:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5"
   )
   
   //connect to database using a single connection
   func main() {
       /***********************************************/
       /* Single Connection to TimescaleDB/ PostgreSQL */
       /***********************************************/
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       conn, err := pgx.Connect(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer conn.Close(ctx)
   
       //run a simple query to check our connection
       var greeting string
       err = conn.QueryRow(ctx, "select 'Hello, Timescale!'").Scan(&greeting)
       if err != nil {
           fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
           os.Exit(1)
       }
       fmt.Println(greeting)
   }
   
   

   If you’d like to specify your connection string as an environment variable, you can use this syntax to access it in place of the connStr variable:

   os.Getenv("DATABASE_CONNECTION_STRING")
   

Alternatively, you can connect to using a connection pool. Connection pooling is useful to conserve computing resources, and can also result in faster database queries:

1. To create a connection pool that can be used for concurrent connections to your database, use the pgxpool.New() function instead of pgx.Connect(). Also note that this script imports github.com/jackc/pgx/v5/pgxpool, instead of pgx/v5 which was used to create a single connection:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
   
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       //run a simple query to check our connection
       var greeting string
       err = dbpool.QueryRow(ctx, "select 'Hello, Tiger Data (but concurrently)'").Scan(&greeting)
       if err != nil {
           fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
           os.Exit(1)
       }
       fmt.Println(greeting)
   }
   

Create a relational table

In this section, you create a table called sensors which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called sensor_data which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors.

1. Compose a string that contains the SQL statement to create a relational table. This example creates a table called sensors, with columns for ID, type, and location:

   queryCreateTable := `CREATE TABLE sensors (id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50));`
   

2. Execute the CREATE TABLE statement with the Exec() function on the dbpool object, using the arguments of the current context and the statement string you created:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       /********************************************/
       /* Create relational table                      */
       /********************************************/
   
       //Create relational table called sensors
       queryCreateTable := `CREATE TABLE sensors (id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50));`
       _, err = dbpool.Exec(ctx, queryCreateTable)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to create SENSORS table: %v\n", err)
           os.Exit(1)
       }
       fmt.Println("Successfully created relational table SENSORS")
   }
   

Generate a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

1. Create a variable for the CREATE TABLE SQL statement for your hypertable. Notice how the hypertable has the compulsory time column:

   queryCreateTable := `CREATE TABLE sensor_data (
           time TIMESTAMPTZ NOT NULL,
           sensor_id INTEGER,
           temperature DOUBLE PRECISION,
           cpu DOUBLE PRECISION,
           FOREIGN KEY (sensor_id) REFERENCES sensors (id));
           `
   

2. Formulate the SELECT statement to convert the table into a hypertable. You must specify the table name to convert to a hypertable, and its time column name as the second argument. For more information, see the create_hypertable docs:

   queryCreateHypertable := `SELECT create_hypertable('sensor_data', by_range('time'));`
   
   The by_range dimension builder is an addition to 2.13.

3. Execute the CREATE TABLE statement and SELECT statement which converts the table into a hypertable. You can do this by calling the Exec() function on the dbpool object, using the arguments of the current context, and the queryCreateTable and queryCreateHypertable statement strings:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       /********************************************/
       /* Create Hypertable                        */
       /********************************************/
       // Create hypertable of time-series data called sensor_data
       queryCreateTable := `CREATE TABLE sensor_data (
           time TIMESTAMPTZ NOT NULL,
           sensor_id INTEGER,
           temperature DOUBLE PRECISION,
           cpu DOUBLE PRECISION,
           FOREIGN KEY (sensor_id) REFERENCES sensors (id));
           `
   
       queryCreateHypertable := `SELECT create_hypertable('sensor_data', by_range('time'));`
   
       //execute statement
       _, err = dbpool.Exec(ctx, queryCreateTable+queryCreateHypertable)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to create the `sensor_data` hypertable: %v\n", err)
           os.Exit(1)
       }
       fmt.Println("Successfully created hypertable `sensor_data`")
   }
   

Insert rows of data

You can insert rows into your database in a couple of different ways. Each of these example inserts the data from the two arrays, sensorTypes and sensorLocations, into the relational table named sensors.

The first example inserts a single row of data at a time. The second example inserts multiple rows of data. The third example uses batch inserts to speed up the process.

1. Open a connection pool to the database, then use the prepared statements to formulate an INSERT SQL statement, and execute it:

   package main
   
   import (
       "context"
       "fmt"
       "os"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       /********************************************/
       /* INSERT into  relational table            */
       /********************************************/
       //Insert data into relational table
   
       // Slices of sample data to insert
       // observation i has type sensorTypes[i] and location sensorLocations[i]
       sensorTypes := []string{"a", "a", "b", "b"}
       sensorLocations := []string{"floor", "ceiling", "floor", "ceiling"}
   
       for i := range sensorTypes {
           //INSERT statement in SQL
           queryInsertMetadata := `INSERT INTO sensors (type, location) VALUES ($1, $2);`
   
           //Execute INSERT command
           _, err := dbpool.Exec(ctx, queryInsertMetadata, sensorTypes[i], sensorLocations[i])
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to insert data into database: %v\n", err)
               os.Exit(1)
           }
           fmt.Printf("Inserted sensor (%s, %s) into database \n", sensorTypes[i], sensorLocations[i])
       }
       fmt.Println("Successfully inserted all sensors into database")
   }
   

Instead of inserting a single row of data at a time, you can use this procedure to insert multiple rows of data, instead:

1. This example uses to generate some sample time-series to insert into the sensor_data hypertable. Define the SQL statement to generate the data, called queryDataGeneration. Then use the .Query() function to execute the statement and return the sample data. The data returned by the query is stored in results, a slice of structs, which is then used as a source to insert data into the hypertable:

   package main
   
   import (
       "context"
       "fmt"
       "os"
       "time"
   
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       // Generate data to insert
   
       //SQL query to generate sample data
       queryDataGeneration := `
           SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
           floor(random() * (3) + 1)::int as sensor_id,
           random()*100 AS temperature,
           random() AS cpu
           `
       //Execute query to generate samples for sensor_data hypertable
       rows, err := dbpool.Query(ctx, queryDataGeneration)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err)
           os.Exit(1)
       }
       defer rows.Close()
   
       fmt.Println("Successfully generated sensor data")
   
       //Store data generated in slice results
       type result struct {
           Time        time.Time
           SensorId    int
           Temperature float64
           CPU         float64
       }
   
       var results []result
       for rows.Next() {
           var r result
           err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU)
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
               os.Exit(1)
           }
           results = append(results, r)
       }
   
       // Any errors encountered by rows.Next or rows.Scan are returned here
       if rows.Err() != nil {
           fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
           os.Exit(1)
       }
   
       // Check contents of results slice
       fmt.Println("Contents of RESULTS slice")
       for i := range results {
           var r result
           r = results[i]
           fmt.Printf("Time: %s | ID: %d | Temperature: %f | CPU: %f |\n", &r.Time, r.SensorId, r.Temperature, r.CPU)
       }
   }
   

2. Formulate an SQL insert statement for the sensor_data hypertable:

   //SQL query to generate sample data
   queryInsertTimeseriesData := `
       INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4);
       `
   

3. Execute the SQL statement for each sample in the results slice:

   //Insert contents of results slice into TimescaleDB
   for i := range results {
       var r result
       r = results[i]
       _, err := dbpool.Exec(ctx, queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to insert sample into TimescaleDB %v\n", err)
           os.Exit(1)
       }
       defer rows.Close()
   }
   fmt.Println("Successfully inserted samples into sensor_data hypertable")
   

Inserting multiple rows of data using this method executes as many insert statements as there are samples to be inserted. This can make ingestion of data slow. To speed up ingestion, you can batch insert data instead.

Here’s a sample pattern for how to do so, using the sample data you generated in the previous procedure. It uses the pgx Batch object:

1. This example batch inserts data into the database:

   package main
   
   import (
       "context"
       "fmt"
       "os"
       "time"
   
       "github.com/jackc/pgx/v5"
       "github.com/jackc/pgx/v5/pgxpool"
   )
   
   func main() {
       /********************************************/
       /* Connect using Connection Pool            */
       /********************************************/
       ctx := context.Background()
       connStr := "yourConnectionStringHere"
       dbpool, err := pgxpool.New(ctx, connStr)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
       defer dbpool.Close()
   
       // Generate data to insert
   
       //SQL query to generate sample data
       queryDataGeneration := `
           SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time,
           floor(random() * (3) + 1)::int as sensor_id,
           random()*100 AS temperature,
           random() AS cpu
           `
   
       //Execute query to generate samples for sensor_data hypertable
       rows, err := dbpool.Query(ctx, queryDataGeneration)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to generate sensor data: %v\n", err)
           os.Exit(1)
       }
       defer rows.Close()
   
       fmt.Println("Successfully generated sensor data")
   
       //Store data generated in slice results
       type result struct {
           Time        time.Time
           SensorId    int
           Temperature float64
           CPU         float64
       }
       var results []result
       for rows.Next() {
           var r result
           err = rows.Scan(&r.Time, &r.SensorId, &r.Temperature, &r.CPU)
           if err != nil {
               fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
               os.Exit(1)
           }
           results = append(results, r)
       }
       // Any errors encountered by rows.Next or rows.Scan are returned here
       if rows.Err() != nil {
           fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
           os.Exit(1)
       }
   
       //Insert contents of results slice into TimescaleDB
       //SQL query to generate sample data
       queryInsertTimeseriesData := `
           INSERT INTO sensor_data (time, sensor_id, temperature, cpu) VALUES ($1, $2, $3, $4);
           `
   
       /********************************************/
       /* Batch Insert into TimescaleDB            */
       /********************************************/
       //create batch
       batch := &pgx.Batch{}
       //load insert statements into batch queue
       for i := range results {
           var r result
           r = results[i]
           batch.Queue(queryInsertTimeseriesData, r.Time, r.SensorId, r.Temperature, r.CPU)
       }
       batch.Queue("select count(*) from sensor_data")
   
       //send batch to connection pool
       br := dbpool.SendBatch(ctx, batch)
       //execute statements in batch queue
       _, err = br.Exec()
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to execute statement in batch queue %v\n", err)
           os.Exit(1)
       }
       fmt.Println("Successfully batch inserted data")
   
       //Compare length of results slice to size of table
       fmt.Printf("size of results: %d\n", len(results))
       //check size of table for number of rows inserted
       // result of last SELECT statement
       var rowsInserted int
       err = br.QueryRow().Scan(&rowsInserted)
       fmt.Printf("size of table: %d\n", rowsInserted)
   
       err = br.Close()
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to closer batch %v\n", err)
           os.Exit(1)
       }
   }
   

Execute a query

This section covers how to execute queries against your database.

1. Define the SQL query you’d like to run on the database. This example uses a SQL query that combines time-series and relational data. It returns the average CPU values for every 5 minute interval, for sensors located on location ceiling and of type a:

   // Formulate query in SQL
   // Note the use of prepared statement placeholders $1 and $2
   queryTimebucketFiveMin := `
       SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu)
       FROM sensor_data
       JOIN sensors ON sensors.id = sensor_data.sensor_id
       WHERE sensors.location = $1 AND sensors.type = $2
       GROUP BY five_min
       ORDER BY five_min DESC;
       `
   

2. Use the .Query() function to execute the query string. Make sure you specify the relevant placeholders:

   //Execute query on TimescaleDB
   rows, err := dbpool.Query(ctx, queryTimebucketFiveMin, "ceiling", "a")
   if err != nil {
       fmt.Fprintf(os.Stderr, "Unable to execute query %v\n", err)
       os.Exit(1)
   }
   defer rows.Close()
   
   fmt.Println("Successfully executed query")
   

3. Access the rows returned by .Query(). Create a struct with fields representing the columns that you expect to be returned, then use the rows.Next() function to iterate through the rows returned and fill results with the array of structs. This uses the rows.Scan() function, passing in pointers to the fields that you want to scan for results.

   This example prints out the results returned from the query, but you might want to use those results for some other purpose. Once you’ve scanned through all the rows returned you can then use the results array however you like.

   //Do something with the results of query
   // Struct for results
   type result2 struct {
       Bucket time.Time
       Avg    float64
   }
   
   // Print rows returned and fill up results slice for later use
   var results []result2
   for rows.Next() {
       var r result2
       err = rows.Scan(&r.Bucket, &r.Avg)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to scan %v\n", err)
           os.Exit(1)
       }
       results = append(results, r)
       fmt.Printf("Time bucket: %s | Avg: %f\n", &r.Bucket, r.Avg)
   }
   
   // Any errors encountered by rows.Next or rows.Scan are returned here
   if rows.Err() != nil {
       fmt.Fprintf(os.Stderr, "rows Error: %v\n", rows.Err())
       os.Exit(1)
   }
   
   // use results here…
   

Next steps

Now that you’re able to connect, read, and write to a instance from your Go application, be sure to check out these advanced tutorials:

• Refer to the pgx documentation for more information about pgx.
• Want fast inserts on CSV data? Check out parallel copy, a tool for fast inserts, written in Go.

Java

Prerequisites

To follow the steps on this page:

• Create a target with Real-time analytics enabled.

  You need your connection details. This procedure also works for .

• Install the Java Development Kit (JDK).
• Install the PostgreSQL JDBC driver.

All code in this quick start is for Java 16 and later. If you are working with older JDK versions, use legacy coding techniques.

Connect to your service

In this section, you create a connection to your using an application in a single file. You can use any of your favorite build tools, including gradle or maven.

1. Create a directory containing a text file called Main.java, with this content:

   package com.timescale.java;
   
   public class Main {
   
       public static void main(String... args) {
           System.out.println("Hello, World!");
       }
   }
   

2. From the command line in the current directory, run the application:

   java Main.java
   

   If the command is successful, Hello, World! line output is printed to your console.

3. Import the PostgreSQL JDBC driver. If you are using a dependency manager, include the PostgreSQL JDBC Driver as a dependency.

4. Download the JAR artifact of the JDBC Driver and save it with the Main.java file.

5. Import the JDBC Driver into the Java application and display a list of available drivers for the check:

   package com.timescale.java;
   
   import java.sql.DriverManager;
   
   public class Main {
   
       public static void main(String... args) {
           DriverManager.drivers().forEach(System.out::println);
       }
   }
   

6. Run all the examples:

   java -cp *.jar Main.java
   

   If the command is successful, a string similar to org.postgresql.Driver@7f77e91b is printed to your console. This means that you are ready to connect to from Java.

7. Locate your credentials and use them to compose a connection string for JDBC.

   You’ll need:

   • password
   • username
   • host URL
   • port
   • database name

8. Compose your connection string variable, using this format:

   var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
   

   For more information about creating connection strings, see the JDBC documentation.

   This method of composing a connection string is for test or development purposes only. For production, use environment variables for sensitive details like your password, hostname, and port number.

   package com.timescale.java;
   
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   public class Main {
   
       public static void main(String... args) throws SQLException {
           var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           var conn = DriverManager.getConnection(connUrl);
           System.out.println(conn.getClientInfo());
       }
   }
   

9. Run the code:

   java -cp *.jar Main.java
   

   If the command is successful, a string similar to {ApplicationName=PostgreSQL JDBC Driver} is printed to your console.

Create a relational table

In this section, you create a table called sensors which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called sensor_data which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors.

1. Compose a string which contains the SQL statement to create a relational table. This example creates a table called sensors, with columns id, type and location:

   CREATE TABLE sensors (
       id SERIAL PRIMARY KEY,
       type TEXT NOT NULL,
       location TEXT NOT NULL
   );
   

2. Create a statement, execute the query you created in the previous step, and check that the table was created successfully:

   package com.timescale.java;
   
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   public class Main {
   
       public static void main(String... args) throws SQLException {
           var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           var conn = DriverManager.getConnection(connUrl);
   
           var createSensorTableQuery = """
                   CREATE TABLE sensors (
                       id SERIAL PRIMARY KEY,
                       type TEXT NOT NULL,
                       location TEXT NOT NULL
                   )
                   """;
           try (var stmt = conn.createStatement()) {
               stmt.execute(createSensorTableQuery);
           }
   
           var showAllTablesQuery = "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public'";
           try (var stmt = conn.createStatement();
                var rs = stmt.executeQuery(showAllTablesQuery)) {
               System.out.println("Tables in the current database: ");
               while (rs.next()) {
                   System.out.println(rs.getString("tablename"));
               }
           }
       }
   }
   

Create a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

1. Create a CREATE TABLE SQL statement for your hypertable. Notice how the hypertable has the compulsory time column:

   CREATE TABLE sensor_data (
       time TIMESTAMPTZ NOT NULL,
       sensor_id INTEGER REFERENCES sensors (id),
       value DOUBLE PRECISION
   );
   

2. Create a statement, execute the query you created in the previous step:

   SELECT create_hypertable('sensor_data', by_range('time'));
   
   The by_range and by_hash dimension builder is an addition to 2.13.

3. Execute the two statements you created, and commit your changes to the database:

   package com.timescale.java;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.util.List;
   
   public class Main {
   
       public static void main(String... args) {
           final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
           try (var conn = DriverManager.getConnection(connUrl)) {
               createSchema(conn);
               insertData(conn);
           } catch (SQLException ex) {
               System.err.println(ex.getMessage());
           }
       }
   
       private static void createSchema(final Connection conn) throws SQLException {
           try (var stmt = conn.createStatement()) {
               stmt.execute("""
                       CREATE TABLE sensors (
                           id SERIAL PRIMARY KEY,
                           type TEXT NOT NULL,
                           location TEXT NOT NULL
                       )
                       """);
           }
   
           try (var stmt = conn.createStatement()) {
               stmt.execute("""
                       CREATE TABLE sensor_data (
                           time TIMESTAMPTZ NOT NULL,
                           sensor_id INTEGER REFERENCES sensors (id),
                           value DOUBLE PRECISION
                       )
                       """);
           }
   
           try (var stmt = conn.createStatement()) {
               stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
           }
       }
   }
   

Insert data

You can insert data into your hypertables in several different ways. In this section, you can insert single rows, or insert by batches of rows.

1. Open a connection to the database, use prepared statements to formulate the INSERT SQL statement, then execute the statement:

   final List<Sensor> sensors = List.of(
           new Sensor("temperature", "bedroom"),
           new Sensor("temperature", "living room"),
           new Sensor("temperature", "outside"),
           new Sensor("humidity", "kitchen"),
           new Sensor("humidity", "outside"));
   for (final var sensor : sensors) {
       try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
           stmt.setString(1, sensor.type());
           stmt.setString(2, sensor.location());
           stmt.executeUpdate();
       }
   }
   

If you want to insert a batch of rows by using a batching mechanism. In this example, you generate some sample time-series data to insert into the sensor_data hypertable:

1. Insert batches of rows:

   final var sensorDataCount = 100;
   final var insertBatchSize = 10;
   try (var stmt = conn.prepareStatement("""
           INSERT INTO sensor_data (time, sensor_id, value)
           VALUES (
               generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
               floor(random() * 4 + 1)::INTEGER,
               random()
           )
           """)) {
       for (int i = 0; i < sensorDataCount; i++) {
           stmt.addBatch();
   
           if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
               stmt.executeBatch();
           }
       }
   }
   

Execute a query

This section covers how to execute queries against your database.

Execute queries on TimescaleDB

1. Define the SQL query you’d like to run on the database. This example combines time-series and relational data. It returns the average values for every 15 minute interval for sensors with specific type and location.

   SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
   FROM sensor_data
   JOIN sensors ON sensors.id = sensor_data.sensor_id
   WHERE sensors.type = ? AND sensors.location = ?
   GROUP BY bucket
   ORDER BY bucket DESC;
   

2. Execute the query with the prepared statement and read out the result set for all a-type sensors located on the floor:

   try (var stmt = conn.prepareStatement("""
           SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
           FROM sensor_data
           JOIN sensors ON sensors.id = sensor_data.sensor_id
           WHERE sensors.type = ? AND sensors.location = ?
           GROUP BY bucket
           ORDER BY bucket DESC
           """)) {
       stmt.setString(1, "temperature");
       stmt.setString(2, "living room");
   
       try (var rs = stmt.executeQuery()) {
           while (rs.next()) {
               System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
           }
       }
   }
   

   If the command is successful, you’ll see output like this:

   2021-05-12 23:30:00.0: 0,508649
   2021-05-12 23:15:00.0: 0,477852
   2021-05-12 23:00:00.0: 0,462298
   2021-05-12 22:45:00.0: 0,457006
   2021-05-12 22:30:00.0: 0,568744
   ...
   

Next steps

Now that you’re able to connect, read, and write to a instance from your Java application, and generate the scaffolding necessary to build a new application from an existing instance, be sure to check out these advanced tutorials:

• Continuous Aggregates
• Migrate Your own Data

Complete code samples

This section contains complete code samples.

Complete code sample

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
        }
    }

    private static void insertData(final Connection conn) throws SQLException {
        final List<Sensor> sensors = List.of(
                new Sensor("temperature", "bedroom"),
                new Sensor("temperature", "living room"),
                new Sensor("temperature", "outside"),
                new Sensor("humidity", "kitchen"),
                new Sensor("humidity", "outside"));
        for (final var sensor : sensors) {
            try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
                stmt.setString(1, sensor.type());
                stmt.setString(2, sensor.location());
                stmt.executeUpdate();
            }
        }

        final var sensorDataCount = 100;
        final var insertBatchSize = 10;
        try (var stmt = conn.prepareStatement("""
                INSERT INTO sensor_data (time, sensor_id, value)
                VALUES (
                    generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
                    floor(random() * 4 + 1)::INTEGER,
                    random()
                )
                """)) {
            for (int i = 0; i < sensorDataCount; i++) {
                stmt.addBatch();

                if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
                    stmt.executeBatch();
                }
            }
        }
    }

    private record Sensor(String type, String location) {
    }
}

Execute more complex queries

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
            executeQueries(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
        }
    }

    private static void insertData(final Connection conn) throws SQLException {
        final List<Sensor> sensors = List.of(
                new Sensor("temperature", "bedroom"),
                new Sensor("temperature", "living room"),
                new Sensor("temperature", "outside"),
                new Sensor("humidity", "kitchen"),
                new Sensor("humidity", "outside"));
        for (final var sensor : sensors) {
            try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
                stmt.setString(1, sensor.type());
                stmt.setString(2, sensor.location());
                stmt.executeUpdate();
            }
        }

        final var sensorDataCount = 100;
        final var insertBatchSize = 10;
        try (var stmt = conn.prepareStatement("""
                INSERT INTO sensor_data (time, sensor_id, value)
                VALUES (
                    generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
                    floor(random() * 4 + 1)::INTEGER,
                    random()
                )
                """)) {
            for (int i = 0; i < sensorDataCount; i++) {
                stmt.addBatch();

                if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
                    stmt.executeBatch();
                }
            }
        }
    }

    private static void executeQueries(final Connection conn) throws SQLException {
        try (var stmt = conn.prepareStatement("""
                SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
                FROM sensor_data
                JOIN sensors ON sensors.id = sensor_data.sensor_id
                WHERE sensors.type = ? AND sensors.location = ?
                GROUP BY bucket
                ORDER BY bucket DESC
                """)) {
            stmt.setString(1, "temperature");
            stmt.setString(2, "living room");

            try (var rs = stmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
                }
            }
        }
    }

    private record Sensor(String type, String location) {
    }
}

You are not limited to these languages. is based on , you can interface with and using any client driver.