Skip to content

Manual Ingest

Use SQL INSERT INTO for one-time addition of data to tables.

There are two ways to insert data manually:

This feature isn't intended for ongoing ingestion. See Limitations.

For ingestion that scales with load, use Stream Ingest or Batch Ingest. Use Summary Tables for aggregations.

Before you begin⚓︎

Set up a table⚓︎

If you don't have a table configured in Hydrolix, make one. See Projects & Tables to create a table.

Configure the default transform⚓︎

The table must have a default transform configured with "is_default": true. Hydrolix uses the default transform's output_columns to discover the column schema when writing data from INSERT INTO statements. The default transform must include:

  • The primary timestamp column for the table
  • Every column you intend to insert data into, with matching data types

If your table already has a default transform for streaming or batch ingest, you don't need to create a separate one. To check, use either the UI or API:

  • UI: Navigate to Data. Select your table and look for (default) in the list under Table Transforms.
  • API: List the table's transforms and look for "is_default": true in the transform settings.

If you need to create a default transform, see Write Transforms for details.

The following example transform defines a table with three columns: a timestamp primary column, a mycolumn1 string column, and a mycolumn2 unsigned integer column. The INSERT INTO examples on this page use this schema.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
{
    "name": "mytransform",
    "type": "json",
    "description": "an example for manual data insertion",
    "settings": {
        "is_default": true,
        "output_columns": [
            {
                "name": "timestamp",
                "datatype": {
                    "primary": true,
                    "resolution": "seconds",
                    "format": "2006-01-02 15:04:05",
                    "type": "datetime"
                }
            },
            {
                "name": "mycolumn1",
                "datatype": {
                    "type": "string"
                }
            },
            {
                "name": "mycolumn2",
                "datatype": {
                    "type": "uint32"
                }
            }
        ],
        "compression": "none",
        "format_details": {}
    }
}

Insert data⚓︎

When inserting data, keep these things in mind:

  • Insertion fails if the primary timestamp field is missing.
  • The current default transform (the one marked "is_default": true) is always used.
  • Manual insertion writes to the table's primary storage location. You can Specify an alternate storage location.
  • No ingestion system features execute when inserting data manually. See Limitations.

Insert from a query result⚓︎

To insert from a query result, combine the INSERT INTO statement with a SELECT statement.

1
2
3
4
5
INSERT INTO myproject.mytable (timestamp, mycolumn1, mycolumn2)

SELECT toStartOfDay(timestamp) as day, mycolumn1, mycolumn2
FROM myproject.anothertable
GROUP BY day, mycolumn1

1
2
3
4
5
INSERT INTO myproject.mytable (timestamp, mycolumn1, mycolumn2)
SETTINGS hdx_storage_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479'
SELECT toStartOfDay(timestamp) as day, mycolumn1, mycolumn2
FROM myproject.anothertable
GROUP BY day, mycolumn1

Hydrolix runs the SELECT query and inserts the resulting rows into myproject.mytable.

Insert values directly⚓︎

Insert data using literals in SQL statements.

1
2
3
INSERT INTO myproject.mytable (timestamp, mycolumn1, mycolumn2)

VALUES ('2021-11-30 15:04:05', 'aardvark', 1)

1
2
3
INSERT INTO myproject.mytable (timestamp, mycolumn1, mycolumn2)
SETTINGS hdx_storage_id = 'f47ac10b-58cc-4372-a567-0e02b2c3d479'
VALUES ('2021-11-30 15:04:05', 'aardvark', 1)

You can insert multiple rows in a single statement.

1
2
INSERT INTO myproject.mytable (timestamp, mycolumn1, mycolumn2)
VALUES ('2021-11-30 15:04:05', 'badger', 1),('2021-12-01 15:04:05', 'civet', 2)

If the table was empty before these insertions, it now looks like this:

1
2
3
4
> SELECT mycolumn1, mycolumn2, timestamp FROM myproject.mytable
aardvark        1       2021-11-30 15:04:05
badger  1       2021-11-30 15:04:05
civet   2       2021-12-01 15:04:05

The response for the INSERT INTO is a JSON object containing the bytes written, number of partitions, and rows written. Authorization credentials are omitted from the next example. See HTTP Query API for examples.

1
2
3
4
5
6
$ curl -X POST --url "$HDX_HYDROLIX_URL/query" --data-binary @insert-into.sql
{
    "bytes_written": 194315,
    "num_partitions": 1,
    "rows_written": 13872
}

Specify an alternate storage location⚓︎

By default, INSERT INTO writes data to the table's primary storage bucket. Add the SETTINGS hdx_storage_id clause to write data to another location.

Set the storage ID to the UUID of the target storage location.

To find the UUID of a storage location, use the storage API endpoints or check the storage settings in the Hydrolix UI.

The Catalog Requires the Storage ID

The catalog must contain the storage location for each partition in the storage_id metadata field of its catalog entry.

With the SETTINGS hdx_storage_id clause, the catalog uses and records the specified value.

Without the clause, the catalog uses and records the table's default storage ID.

The SETTINGS clause works when inserting from a query result and when inserting values directly.

Limitations⚓︎

Because this feature bypasses the ingestion system, automatic data routing features don't apply to INSERT INTO statements: