A newer version of Hazelcast Platform is available.

View latest

CREATE MAPPING

6.0-SNAPSHOT 5.5 5.4 5.3 5.2 5.1 5.0

The CREATE MAPPING statement creates a table that stores essential metadata about a source’s data model, connection parameters, and serialization formats so that the SQL service can connect to the data source and query it.

Syntax Summary

This code block is a quick reference with all the parameters that you can use with the CREATE MAPPING statement.

See some practical examples.

CREATE [OR REPLACE] MAPPING [IF NOT EXISTS] mapping_name
[EXTERNAL NAME external_name]
[ ( column_name column_type [EXTERNAL NAME external_column_name] [, ...] ) ]
TYPE type_identifier
[ OPTIONS ( 'option_name' = 'option_value' [, ...] ) ]

Parameters

The CREATE MAPPING statement accepts the following parameters.

You must provide the following:

  • mapping_name

  • type_identifier

Parameter Description Example

OR REPLACE

Overwrite an existing mapping of the same name, if it exists.

 
CREATE OR REPLACE MAPPING myMap
TYPE IMap
OPTIONS ('keyFormat'='varchar','valueFormat'='int');

IF NOT EXISTS

Create a new mapping only if a mapping of the same name does not already exist.

 
CREATE MAPPING IF NOT EXISTS myMap
TYPE IMap
OPTIONS ('keyFormat'='varchar','valueFormat'='int');

mapping_name

Name of the mapping, which you can use to query the data.

 
CREATE MAPPING myMappingName
TYPE IMap
OPTIONS ('keyFormat'='varchar','valueFormat'='int');

external_name

An optional name that identifies the object in the external system. For example, for a Kafka connector, the identifier is the topic name. By default, this parameter is equal to the mapping name.

-

column_name, column_type

The name and data type of the column.

 
CREATE MAPPING myMap (
    name VARCHAR,
    id INT
)
TYPE IMap
OPTIONS ('keyFormat'='varchar','valueFormat'='json-flat');

external_column_name

An optional name of a column. If omitted, Hazelcast assumes this name is equal to the column_name parameter. For key-value connectors (Kafka, IMap), the key’s and value’s fields are prefixed by __key and this.

type_identifier

The connector to use to map the data. For example, if you use the IMap type, data is mapped to a Hazelcast map.

 
CREATE MAPPING myMap
TYPE IMap
OPTIONS (
    'keyFormat'='varchar',
    'valueFormat'='int');

'option_name', 'option_value'

Connector parameters. Check the reference page for your connector to find out what values you can use. These parameters must be enclosed in single quotes (a SQL VARCHAR literal).

 
CREATE MAPPING my_topic(
    __key BIGINT,
    ticker VARCHAR,
    amount INT)
TYPE Kafka
OPTIONS (
    'keyFormat' = 'bigint',
    'valueFormat' = 'json-flat',
    'bootstrap.servers' = '127.0.0.1:9092');

Permissions

Enterprise

If security is enabled, you can grant or deny permission for certain clients to use this statement. See SQL Permissions.

Auto-resolving Columns and Options

Some connectors can resolve the column names and values of an object either by using the options you provide or by sampling a random record in the input. For example, if you give Hazelcast the name of a map that contains Java objects, Hazelcast will resolve the columns by reflecting that class.

If the connector fails to resolve the columns, the statement will fail.

Examples

This section lists some example SQL queries that show you how to use the CREATE MAPPING statement.

Create a Mapping to a Map

See Mapping to Hazelcast Maps.

Create a Mapping to a Kafka Topic

See Mapping to Kafka.

Create a Mapping to a File

See Creating a Mapping to a File System.

Create a Mapping to a Database

See Mapping to JDBC.

Change a Mapping

If you want to overwrite an existing mapping, use the CREATE OR REPLACE MAPPING statement.

Changing or removing a mapping does not affect any active query that is already using it. After changing a mapping, only new jobs will use the new mapping. 
CREATE OR REPLACE MAPPING myMap TYPE IMap OPTIONS ('keyFormat'='varchar','valueFormat'='int');
lifebuoy

Help and support