This is a prerelease version.

INSERT INTO/SINK INTO

The INSERT INTO/SINK INTO statements write data to storage systems that are connected to Hazelcast through mappings.

When you submit a standard 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 insert 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 create a mapping to a new map, use the CREATE MAPPING statement.

CREATE MAPPING myMap (name varchar EXTERNAL NAME "__key", age INT EXTERNAL NAME "this") TYPE IMap OPTIONS ('keyFormat'='varchar','valueFormat'='int');
OK

This example creates a new map whose keys are strings and whose values are integers. The key (__key) is aliased as name and the value (this) is aliased as age, using the EXTERNAL NAME keyword.

SELECT * FROM myMap;
+--------------------+------------+
|name                |         age|
+--------------------+------------+
+--------------------+------------+

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

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

Insert Objects into a Map

To insert objects into a map, you can use one of the following serialization formats that the IMap connector supports:

  • portable

  • json

  • java

For example, to insert Java objects as value into 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. You can either add it to the members' classpaths by creating a JAR file and adding it to a member’s lib folder, or you can use user code deployment.
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|
+--------------------+------------+