In this tutorial, you will learn how to

• Create tables
• Run simple and advanced queries

As an example, we will look at hypothetical temperature readings from a variety of sensors.

:::info

All commands are run through the Web Console accessible at .

You can also run the same SQL via the Postgres endpoint or the .

:::

The first step is to create tables. One will contain the metadata of our sensors, the other will contain the readings from these sensors.

For more information about this statement, please refer to the CREATE TABLE reference documentation.

Let’s populate our sensors table with procedurally-generated data:

INSERT INTO sensors
    SELECT
        x ID, --increasing integer
        rnd_str('Eberle', 'Honeywell', 'Omron', 'United Automation', 'RS Pro') make,
        rnd_str('New York', 'Miami', 'Boston', 'Chicago', 'San Francisco') city
    FROM long_sequence(10000) x

For more information about this statement, please refer to the reference documentation. About the functions, please refer to the random generator and the pages.

Our sensors table now contains 10,000 randomly generated sensor values of different makes and in various cities. It should look like the below:

Let’s now create some sensor readings. In this case, we will generate the table and the data at the same time:

CREATE TABLE readings
AS(
    SELECT
        x ID,
        timestamp_sequence(to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'), rnd_long(1,10,2) * 100000L) ts,
        rnd_long(0, 10000, 0) sensorId
    FROM long_sequence(10000000) x)
TIMESTAMP(ts)
PARTITION BY MONTH;

While creating this table, we did the following:

• TIMESTAMP(ts) elected ts as designated timestamp. This will enable time partitioning.
• PARTITION BY MONTH created a monthly partition strategy. Our data will be sharded in monthly files.

The generated data will look like the below:

Let’s also select the count of records from readings:

SELECT count() FROM readings;

and the average reading:

We can now leverage our sensors table to get more interesting data:

Results should look like the data below:

FROM readings
JOIN(
    SELECT ID sensId, city
    FROM sensors)
ON readings.sensorId = sensId;

Results should look like the data below:

SELECT ts, city, make, avg(temp)
FROM readings
JOIN (
    SELECT ID sensId, city, make
    FROM sensors
    WHERE city='Miami' AND make='Omron')
ON readings.sensorId = sensId
SAMPLE BY 1h;

Results should look like the data below:

For more information about these statements, please refer to the and JOIN pages.