This is a prerelease version.

View latest

INSERT INTO/SINK INTO

The INSERT INTO/SINK INTO statements write data to platforms that are connected to Hazelcast through connectors.

These statements requires that the data types in source and sink tables match.

When you submit an INSERT INTO/SINK INTO statement, its lifecycle is tied to the SQL client session. If the SQL client disconnects, the statement is canceled. To run these statements independently, use the CREATE JOB statement.

Syntax Summary

This code block is a quick reference with all the parameters that you can use with the INSERT INTO/SINK INTO statements.

[ INSERT | SINK ] INTO [schema_name.]table_name[(column_name [, ...])]
{ SELECT ... | VALUES(expression, [, ...]) [, ...] }

Parameters

The INSERT INTO/SINK INTO statements accept the following parameters.

Parameter Description Example

table_name

The name of the table to write data to.

Difference between INSERT INTO and SINK INTO

The INSERT INTO statement works as defined by the SQL standard. This statement is append-only, meaning that you cannot overwrite existing data. For example, you cannot add a duplicate key to a map.

The SINK INTO statement is a custom query whose behavior depends on the underlying connector. For example, when writing to a map, the value associated with the key is overwritten, and one key can be overwritten multiple times.

Examples

This section lists some example SQL queries that show you how to use the INSERT INTO/SINK INTO statements.

Add Entries to a Map

To add entries to an existing map, it must not be empty and each member must have at least one local entry. If you want to add entries to an empty map or connect to a new map, use the CREATE MAPPING statement.

This example connects to a map whose keys are strings and whose values are integers.

CREATE MAPPING myMap TYPE IMap OPTIONS ('keyFormat'='varchar','valueFormat'='int');
OK
SELECT * FROM myMap;
+--------------------+------------+
|__key               |        this|
+--------------------+------------+
+--------------------+------------+

Use the INSERT INTO statement to add an entry to the map.

INSERT INTO myMap VALUES('Jake', 29);
+--------------------+------------+
|name                |         age|
+--------------------+------------+
|Jake                |          29|
+--------------------+------------+

Add Objects to a Map

To add objects to a map, you can use one of the following serialization formats that the map connector supports:

  • portable

  • compact(BETA)

  • json-flat

  • java

For example, to add Java objects as values to a map, create a mapping where the valueFormat is java and the valueJavaClass is the name of the class.

The class must be available to the cluster. The options you have for adding classes to members' classpaths depends on how you submit jobs to the cluster. For details, see Submitting Jobs.
CREATE MAPPING people (__key BIGINT, name VARCHAR, age INT) TYPE IMap OPTIONS ('keyFormat'='bigint','valueFormat'='java','valueJavaClass' = 'sql.Person')
INSERT INTO people (__key, name, age) VALUES (1 , 'Jake', 29);

Overwrite Data in a Map

Use the SINK INTO statement to overwrite the value of an existing key in a map.

SINK INTO myMap VALUES('Jake', 21);
+--------------------+------------+
|name                |         age|
+--------------------+------------+
|Jake                |          21|
+--------------------+------------+