> ## Documentation Index > Fetch the complete documentation index at: https://tigerdata-747200db-chore-custom-colored-homepage.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Start coding with TigerData > Integrate Tiger Cloud with your app using your preferred programming language. Connect to a service, create and manage hypertables, then and ingest and query data export const SERVICE_SHORT_0 = undefined export const TIMESCALE_DB_3 = undefined export const HYPERCORE = 'hypercore'; export const PG = 'Postgres'; export const TIMESCALE_DB = 'TimescaleDB'; export const CLOUD_LONG = 'Tiger Cloud'; export const ACCOUNT_LONG = 'Tiger Cloud account'; export const DATA_MODE = 'data mode'; export const COLUMNSTORE = 'columnstore'; export const READ_REPLICA = 'read replica'; export const OPS_MODE = 'ops mode'; export const CONSOLE = 'Tiger Cloud Console'; export const COMPANY = 'TigerData'; export const SERVICE_SHORT = 'service'; export const TIMESCALE_DB_2 = undefined export const TIMESCALE_DB_1 = undefined export const PG_1 = undefined export const SERVICE_LONG = 'Tiger Cloud service'; export const SELF_LONG = 'self-hostedTimescaleDB'; export const PG_0 = undefined export const CLOUD_LONG_0 = undefined export const COMPANY_0 = undefined export const TIMESCALE_DB_0 = undefined export const SELF_LONG_CAP_0 = undefined Easily integrate your app with {CLOUD_LONG} or {SELF_LONG}. Use your favorite programming language to connect to your {SERVICE_LONG}, create and manage hypertables, then ingest and query data. ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with time-series and analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /cloud/tiger/get-started/create-services [enable-timescaledb]: /open-source/timescaledb/install-and-update/install-self-hosted [connection-info]: /integrations/find-connection-details * Install [Rails][rails-guide]. ## Connect a Rails app to your service Every {SERVICE_LONG} is a 100% {PG_0} database hosted in {CLOUD_LONG_0} with {COMPANY_0} extensions such as {TIMESCALE_DB_0}. You connect to your {SERVICE_LONG} from a standard Rails app configured for {PG_0}. 1. **Create a new Rails app configured for {PG_0}** Rails creates and bundles your app, then installs the standard {PG_0} Gems. ```bash rails new my_app -d=postgresql cd my_app ``` 2. **Install the {TIMESCALE_DB_0} gem** 1. Open `Gemfile`, add the following line, then save your changes: ```ruby gem 'timescaledb' ``` 2. In Terminal, run the following command: ```bash bundle install ``` 3. **Connect your app to your {SERVICE_LONG}** 1. In `/config/database.yml` update the configuration to read securely connect to your {SERVICE_LONG} by adding `url: <%= ENV['DATABASE_URL'] %>` to the default configuration: ```yaml 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][connection-info] ```bash export DATABASE_URL="value of Service URL" ``` 3. Create the database: * **{CLOUD_LONG_0}**: nothing to do. The database is part of your {SERVICE_LONG}. * **{SELF_LONG_CAP_0}**, create the database for the project: ```bash rails db:create ``` 4. Run migrations: ```bash rails db:migrate ``` 5. Verify the connection from your app to your {SERVICE_LONG}: ```bash echo "\dx" | rails dbconsole ``` The result shows the list of extensions in your {SERVICE_LONG} | 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 {PG_0} servers | | timescaledb | 2.18.1 | public | Enables scalable inserts and complex queries for time-series data (Community Edition) | | timescaledb\_toolkit | 1.19.0 | public | Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities | ## Optimize time-series data in hypertables Hypertables are {PG_0} tables designed to simplify and accelerate data analysis. Anything you can do with regular {PG_0} tables, you can do with hypertables - but much faster and more conveniently. In this section, you use the helpers in the {TIMESCALE_DB_0} gem to create and manage a [hypertable][about-hypertables]. 1. **Generate a migration to create the page loads table** ```bash rails generate migration create_page_loads ``` This creates the `/db/migrate/_create_page_loads.rb` migration file. 1. **Add hypertable options** Replace the contents of `/db/migrate/_create_page_loads.rb` with the following: ```ruby 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 {TIMESCALE_DB_0} 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 Roby docs around [composite primary keys][rails-compostite-primary-keys]. 2. **Create a `PageLoad` model** Create a new file called `/app/models/page_load.rb` and add the following code: ```ruby 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 ``` 3. **Run the migration** ```bash rails db:migrate ``` ## Insert data your service The {TIMESCALE_DB_0} gem provides efficient ways to insert data into hypertables. This section shows you how to ingest test data into your hypertable. 1. **Create a controller to handle page loads** Create a new file called `/app/controllers/application_controller.rb` and add the following code: ```ruby 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: ```ruby 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 {SERVICE_LONG}** ```bash # Insert the data in batches PageLoad.insert_all(generate_sample_page_loads, returning: false) ``` 4. **Validate the test data in your {SERVICE_LONG}** ```bash PageLoad.count PageLoad.first ``` ## Reference This section lists the most common tasks you might perform with the {TIMESCALE_DB_0} gem. ### Query scopes The {TIMESCALE_DB_0} gem provides several convenient scopes for querying your time-series data. * Built-in time-based scopes: ```ruby PageLoad.last_hour.count PageLoad.today.count PageLoad.this_week.count PageLoad.this_month.count ``` * Browser-specific scopes: ```ruby # 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 continuous aggregates: This query fetches the average and standard deviation from the performance stats for the `/products` path over the last day. ```ruby # 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}" ``` ### {TIMESCALE_DB_0} features The {TIMESCALE_DB_0} gem provides utility methods to access hypertable and chunk information. Every model that uses the `acts_as_hypertable` method has access to these methods. #### Access hypertable and chunk information * View chunk or hypertable information: ```ruby PageLoad.chunks.count PageLoad.hypertable.detailed_size ``` * Compress/Decompress chunks: ```ruby PageLoad.chunks.uncompressed.first.compress! # Compress the first uncompressed chunk PageLoad.chunks.compressed.first.decompress! # Decompress the oldest chunk PageLoad.hypertable.compression_stats # View compression stats ``` #### Access hypertable stats You collect hypertable stats using methods that provide insights into your hypertable's structure, size, and compression status: * Get basic hypertable information: ```ruby hypertable = PageLoad.hypertable hypertable.hypertable_name # The name of your hypertable hypertable.schema_name # The schema where the hypertable is located ``` * Get detailed size information: ```ruby hypertable.detailed_size # Get detailed size information for the hypertable hypertable.compression_stats # Get compression statistics hypertable.chunks_detailed_size # Get chunk information hypertable.approximate_row_count # Get approximate row count hypertable.dimensions.map(&:column_name) # Get dimension information hypertable.continuous_aggregates.map(&:view_name) # Get continuous aggregate view names ``` #### Continuous aggregates The `continuous_aggregates` method generates a class for each continuous aggregate. * Get all the continuous aggregate classes: ```ruby PageLoad.descendants # Get all continuous aggregate classes ``` * Manually refresh a continuous aggregate: ```ruby PageLoad.refresh_aggregates ``` * Create or drop a continuous aggregate: Create or drop all the continuous aggregates in the proper order to build them hierarchically. See more about how it works in this [blog post][ruby-blog-post]. ```ruby 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 [{TIMESCALE_DB_0} gem](https://github.com/timescale/timescaledb-ruby). * Check out the [official docs](https://timescale.github.io/timescaledb-ruby/). * Follow the [LTTB][LTTB], [Open AI long-term storage][open-ai-tutorial], and [candlesticks][candlesticks] tutorials. [connect]: #connect-to-timescaledb [create-a-hypertable]: #create-a-hypertable [insert]: #insert-data [query]: #execute-queries [create-aggregates]: #execute-queries [add-policies]: #manage-chunks-and-compression [manage-chunks]: #manage-chunks-and-compression [install]: /getting-started/latest/ [psql-install]: /integrations/psql/ [rails-guide]: https://guides.rubyonrails.org/install_ruby_on_rails.html#installing-rails [connection-info]: /integrations/find-connection-details/ [about-hypertables]: /use-timescale/hypertables/ [rails-compostite-primary-keys]: https://guides.rubyonrails.org/active_record_composite_primary_keys.html [ruby-blog-post]: https://www.timescale.com/blog/building-a-better-ruby-orm-for-time-series-and-analytics [LTTB]: https://timescale.github.io/timescaledb-ruby/toolkit_lttb_tutorial/ [open-ai-tutorial]: https://timescale.github.io/timescaledb-ruby/chat_gpt_tutorial/ [candlesticks]: https://timescale.github.io/timescaledb-ruby/toolkit_candlestick/ ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with time-series and analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /cloud/tiger/get-started/create-services [enable-timescaledb]: /open-source/timescaledb/install-and-update/install-self-hosted [connection-info]: /integrations/find-connection-details * Install the `psycopg2` library. For more information, see the [psycopg2 documentation][psycopg2-docs]. * Create a [Python virtual environment][virtual-env]. [](#)(optional) ## Connect to TimescaleDB In this section, you create a connection to {TIMESCALE_DB_1} using the `psycopg2` library. This library is one of the most popular {PG_1} 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: ```python import psycopg2 ``` 2. Locate your {TIMESCALE_DB_1} 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][pg-libpq-string], using this format: ```python CONNECTION = "postgres://username:password@host:port/dbname" ``` If you're using a hosted version of {TIMESCALE_DB_1}, or generally require an SSL connection, use this version instead: ```python CONNECTION = "postgres://username:password@host:port/dbname?sslmode=require" ``` Alternatively you can specify each parameter in the connection string as follows ```python 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][psycopg2-connect] to create a new database session and create a new [cursor object][psycopg2-cursor] to interact with the database. In your `main` function, add these lines: ```python 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: ```python 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`: ```python 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: ```python 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: ```python # 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][create-hypertable-docs]: ```python query_create_sensordata_hypertable = "SELECT create_hypertable('sensor_data', by_range('time'));" ``` The `by_range` dimension builder is an addition to {TIMESCALE_DB_1} 2.13. 3. Open a cursor with the connection, execute the statements from the previous steps, commit your changes, and close the cursor: ```python 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: ```python 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`: ```python 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][pgcopy-install], and then add this line to your list of `import` statements: ```python from pgcopy import CopyManager ``` 1. Generate some random sensor data using the `generate_series` function provided by {PG_1}. 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: ```python # 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() ``` 2. 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`: ```python cols = ['time', 'sensor_id', 'temperature', 'cpu'] ``` 3. 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`. ```python mgr = CopyManager(conn, 'sensor_data', cols) mgr.copy(values) ``` 4. Commit to persist changes: ```python conn.commit() ``` 5. [](#)The full sample code to insert data into {TIMESCALE_DB_1} using `pgcopy`, using the example of sensor data from four sensors: ```python # 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() ``` 6. [](#)(optional)You can also check if the insertion worked: ```python 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][psycopg2-docs-basics]. For more information about how to execute more complex queries in `psycopg2`, see the [psycopg2 documentation][psycopg2-docs-basics]. ### 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. ```python query = "SELECT * FROM sensor_data;" ``` 2. Open a cursor from the existing database connection, `conn`, and then execute the query you defined: ```python 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][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: ```python 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`][dictcursor-docs]: ```python 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 1. Write the query using prepared statements: ```python # 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() ``` [install]: /getting-started/latest/ [create-hypertable-docs]: /api/hypertable/create_hypertable [dictcursor-docs]: https://www.psycopg.org/docs/extras.html#dictionary-like-cursor [pg-libpq-string]: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING [pgcopy-install]: https://pypi.org/project/pgcopy/ [psycopg2-connect]: https://www.psycopg.org/docs/module.html?highlight=connect#psycopg2.connect [psycopg2-cursor]: https://www.psycopg.org/docs/connection.html?highlight=cursor#connection.cursor [psycopg2-docs-basics]: https://www.psycopg.org/docs/usage.html [psycopg2-docs]: https://pypi.org/project/psycopg2/ [results-retrieval-methods]: https://www.psycopg.org/docs/cursor.html [virtual-env]: https://docs.python.org/3/library/venv.html [connect]: #connect-to-timescaledb [create-table]: #create-a-relational-table [create-a-hypertable]: #create-a-hypertable [insert]: #insert-rows-of-data [query]: #execute-a-query ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with time-series and analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /cloud/tiger/get-started/create-services [enable-timescaledb]: /open-source/timescaledb/install-and-update/install-self-hosted [connection-info]: /integrations/find-connection-details * Install [Node.js][node-install]. * Install the Node.js package manager [npm][npm-install]. ## Connect to {TIMESCALE_DB_2} In this section, you create a connection to {TIMESCALE_DB_2} with a common Node.js ORM (object relational mapper) called [Sequelize][sequelize-info]. 1. At the command prompt, initialize a new Node.js app: ```bash 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: ```json { "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: ```bash npm install express ``` 3. Create a simple web page to check the connection. Create a new file called `index.js`, with this content: ```java 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: ```bash node index.js ``` In your web browser, navigate to `http://localhost:3000`. If the connection is successful, it shows "Hello World!" 1. Add Sequelize to your project: ```bash npm install sequelize sequelize-cli pg pg-hstore ``` 2. Locate your {TIMESCALE_DB_2} credentials and use them to compose a connection string for Sequelize. You'll need: * password * username * host URL * port * database name 3. Compose your connection string variable, using this format: ```java 'postgres://:@:/' ``` 4. Open the `index.js` file you created. Require Sequelize in the application, and declare the connection string: ```java const Sequelize = require('sequelize') const sequelize = new Sequelize('postgres://:@:/', { 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 {TIMESCALE_DB_2} using SSL without them. 5. You can test the connection by adding these lines to `index.js` after the `app.get` statement: ```java 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: ```bash node index.js ``` If the connection is successful, you'll get output like this: ```bash 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`: ```bash npx sequelize model:generate --name page_loads \ --attributes userAgent:string,time:date ``` The output looks similar to this: ```bash Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11] New model was created at . New migration was created at . ``` 2. Edit the migration file so that it sets up a migration key: ```java '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: ```bash npx sequelize db:migrate ``` The output looks similar to this: ```bash 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: ```java 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: ```bash npx sequelize migration:generate --name add_hypertable ``` The output looks similar to this: ```bash Sequelize CLI [Node: 12.16.2, CLI: 5.5.1, ORM: 5.21.11] migrations folder at already exists. New migration was created at /20200601202912-add_hypertable.js . ``` 2. In the `migrations` folder, there is now a new file. Open the file, and add this content: ```js '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 {TIMESCALE_DB_2} 2.13. 3. At the command prompt, run the migration command: ```bash npx sequelize db:migrate ``` The output looks similar to this: ```bash 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: ```java 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: ```java 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. [node-install]: https://nodejs.org [npm-install]: https://www.npmjs.com/get-npm [sequelize-info]: https://sequelize.org [connect]: #connect-to-timescaledb [create-table]: #create-a-relational-table [create-a-hypertable]: #create-a-hypertable [insert]: #insert-rows-of-data [query]: #execute-a-query [install]: /getting-started/latest/ ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with time-series and analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /cloud/tiger/get-started/create-services [enable-timescaledb]: /open-source/timescaledb/install-and-update/install-self-hosted [connection-info]: /integrations/find-connection-details * Install [Go][golang-install]. * Install the [PGX driver for Go][pgx-driver-github]. ## Connect to your {SERVICE_LONG} In this section, you create a connection to {CLOUD_LONG} using the PGX driver. PGX is a toolkit designed to help Go developers work directly with {PG}. You can use it to help your Go application interact directly with TimescaleDB. 1. Locate your {TIMESCALE_DB} 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][libpq-docs], using this format: ```go 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: ```go 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: ```go 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: ```go os.Getenv("DATABASE_CONNECTION_STRING") ``` Alternatively, you can connect to {TIMESCALE_DB} 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: ```go 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, TigerData (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: ```go 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: ```go 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: ```go 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][create-hypertable-docs]: ```go queryCreateHypertable := `SELECT create_hypertable('sensor_data', by_range('time'));` ``` The `by_range` dimension builder is an addition to {TIMESCALE_DB} 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: ```go 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: ```go 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 {PG} 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: ```go 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: ```go //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: ```go //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") ``` 4. [](#)(optional)This example `main.go` generates sample data and inserts it into the `sensor_data` hypertable: ```go package main import ( "context" "fmt" "os" "time" "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() /********************************************/ /* Insert data into hypertable */ /********************************************/ // 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) } //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); ` //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: ```go 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) } // 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) }*/ //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`: ```go // 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: ```go //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. ```go //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… ``` 4. [](#)This example program runs a query, and accesses the results of that query: ```go 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() /********************************************/ /* Execute a query */ /********************************************/ // 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; ` //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") //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) } } ``` ## Next steps Now that you're able to connect, read, and write to a {TIMESCALE_DB} instance from your Go application, be sure to check out these advanced {TIMESCALE_DB} tutorials: * Refer to the [pgx documentation][pgx-docs] for more information about pgx. * Get up and running with {TIMESCALE_DB} with the [Getting Started][getting-started] tutorial. * Want fast inserts on CSV data? Check out [{TIMESCALE_DB} parallel copy][parallel-copy-tool], a tool for fast inserts, written in Go. [getting-started]: /getting-started/ [golang-install]: https://golang.org/doc/install [libpq-docs]: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING [parallel-copy-tool]: https://github.com/timescale/timescaledb-parallel-copy [pgx-docs]: https://pkg.go.dev/github.com/jackc/pgx [pgx-driver-github]: https://github.com/jackc/pgx [install]: /getting-started/ [connect]: /getting-started/start-coding-with-timescale/#connect-to-timescaledb [create-table]: /getting-started/start-coding-with-timescale/#create-a-relational-table [create-a-hypertable]: /getting-started/start-coding-with-timescale/#generate-a-hypertable [insert]: /getting-started/start-coding-with-timescale/#insert-rows-of-data [query]: /getting-started/start-coding-with-timescale/#execute-a-query [create-hypertable-docs]: /use-timescale/hypertables/hypertable-crud/#create-a-hypertable [insert]: /getting-started/start-coding-with-timescale/#insert-a-row-into-your-timescale-database [query]: /getting-started/start-coding-with-timescale/#execute-a-query-on-your-timescale-database [create-hypertable]: /getting-started/start-coding-with-timescale/#generate-a-hypertable ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with time-series and analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /cloud/tiger/get-started/create-services [enable-timescaledb]: /open-source/timescaledb/install-and-update/install-self-hosted [connection-info]: /integrations/find-connection-details * Install the [Java Development Kit (JDK)][jdk]. * Install the [PostgreSQL JDBC driver][pg-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_LONG} In this section, you create a connection to your {SERVICE_SHORT_0} 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: ```java 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: ```bash 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][pg-jdbc-driver-dependency] as a dependency. 4. Download the [JAR artifact of the JDBC Driver][pg-jdbc-driver-artifact] 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: ```java 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: ```bash 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 {TIMESCALE_DB_3} from Java. 1. Locate your {TIMESCALE_DB_3} credentials and use them to compose a connection string for JDBC. You'll need: * password * username * host URL * port * database name 2. Compose your connection string variable, using this format: ```java var connUrl = "jdbc:postgresql://:/?user=&password="; ``` For more information about creating connection strings, see the [JDBC documentation][pg-jdbc-driver-conn-docs]. 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. ```java 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://:/?user=&password="; var conn = DriverManager.getConnection(connUrl); System.out.println(conn.getClientInfo()); } } ``` 3. Run the code: ```bash 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`: ```sql 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: ```java 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://:/?user=&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: ```sql 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: ```sql SELECT create_hypertable('sensor_data', by_range('time')); ``` The `by_range` and `by_hash` dimension builder is an addition to {TIMESCALE_DB_3} 2.13. 3. Execute the two statements you created, and commit your changes to the database: ```java 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://:/?user=&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: ```java final List 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: ```java 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 {TIMESCALE_DB_3} 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. ```sql 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`: ```java 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: ```bash 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 {TIMESCALE_DB_3} instance from your Java application, and generate the scaffolding necessary to build a new application from an existing {TIMESCALE_DB_3} instance, be sure to check out these advanced {TIMESCALE_DB_3} tutorials: * [Continuous Aggregates][continuous-aggregates] * [Migrate Your own Data][migrate] ## Complete code samples This section contains complete code samples. ### Complete code sample ```java 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://:/?user=&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 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 ```java 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://:/?user=&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 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) { } } ``` [jdk]: https://openjdk.java.net [pg-jdbc-driver-artifact]: https://jdbc.postgresql.org/download/ [pg-jdbc-driver-conn-docs]: https://jdbc.postgresql.org/documentation/datasource/ [pg-jdbc-driver-dependency]: https://mvnrepository.com/artifact/org.postgresql/postgresql [pg-jdbc-driver]: https://jdbc.postgresql.org [connect]: #connect-java-to-timescaledb [create-table]: #create-a-relational-table [create-a-hypertable]: #create-a-hypertable [insert]: #insert-a-batch-of-rows-into-timescaledb [query]: #execute-queries-on-timescaledb [install]: /getting-started/latest/ [continuous-aggregates]: /use-timescale/continuous-aggregates/ [migrate]: /migrate/ You are not limited to these languages. {CLOUD_LONG} is based on {PG}, you can interface with {TIMESCALE_DB} and {CLOUD_LONG} using any [{PG} client driver][postgres-drivers]. [postgres-drivers]: https://wiki.postgresql.org/wiki/List_of_drivers