> ## Documentation Index > Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # Start coding with Tiger Data > Integrate your app with Tiger Cloud using your preferred programming language export const SERVICE_SHORT = 'service'; export const TIMESCALE_DB = 'TimescaleDB'; export const SELF_LONG_CAP = 'Self-hosted TimescaleDB'; export const PG = 'Postgres'; export const HYPERTABLE_CAP = 'Hypertable'; export const HYPERTABLE = 'hypertable'; export const COMPANY = 'Tiger Data '; export const CLOUD_LONG = 'Tiger Cloud'; export const CHUNK = 'chunk'; export const CAGG = 'continuous aggregate'; export const SERVICE_LONG = 'Tiger Cloud service'; export const SELF_LONG = 'self-hosted TimescaleDB'; Easily integrate your app with {CLOUD_LONG} or {SELF_LONG}. Use your favorite programming language to connect to your {SERVICE_LONG}, create and manage {HYPERTABLE}s, then ingest and query data. ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services [enable-timescaledb]: /deploy-and-operate/self-hosted/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 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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: ```go theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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-a-hypertable]: ```go theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} //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 theme={"dark"} //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 theme={"dark"} 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`: ```go theme={"dark"} // 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 theme={"dark"} //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 theme={"dark"} //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 {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. * Want fast inserts on CSV data? Check out [{TIMESCALE_DB} parallel copy][timescaledb-parallel-copy], a tool for fast inserts, written in Go. [create-a-hypertable]: #generate-a-hypertable [golang-install]: https://golang.org/doc/install [libpq-docs]: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING [pgx-docs]: https://pkg.go.dev/github.com/jackc/pgx [pgx-driver-github]: https://github.com/jackc/pgx [timescaledb-parallel-copy]: https://github.com/timescale/timescaledb-parallel-copy ## Prerequisites To follow the steps on this page: * Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.

You need [your connection details][connection-info]. This procedure also works for [{SELF_LONG}][enable-timescaledb]. [create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services [enable-timescaledb]: /deploy-and-operate/self-hosted/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 In this section, you create a connection to your {SERVICE_SHORT} 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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} from Java. 7. Locate your {TIMESCALE_DB} 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: ```java theme={"dark"} 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 theme={"dark"} 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()); } } ``` 9. Run the code: ```bash theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} SELECT create_hypertable('sensor_data', by_range('time')); ``` The `by_range` and `by_hash` dimension builder is an addition to {TIMESCALE_DB} 2.13. 3. Execute the two statements you created, and commit your changes to the database: ```java theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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. ```sql theme={"dark"} 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 theme={"dark"} 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 theme={"dark"} 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} instance from your Java application, and generate the scaffolding necessary to build a new application from an existing {TIMESCALE_DB} instance, be sure to check out these advanced {TIMESCALE_DB} tutorials: * [Continuous Aggregates][caggs] * [Migrate Your own Data][migrate] ## Complete code samples This section contains complete code samples. ### Complete code sample ```java theme={"dark"} 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 theme={"dark"} 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) { } } ``` [caggs]: /open-source/timescaledb/continuous-aggregates [jdk]: https://openjdk.java.net [migrate]: /deploy-and-operate/migrate/migrate-to-tiger-cloud [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 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