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, data connection or 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 | DATA CONNECTION data_connection_name [OBJECT TYPE resource_type]}
[ OPTIONS ( 'option_name' = 'option_value' [, ...] ) ]

Parameters

The CREATE MAPPING statement accepts the following parameters.

You must provide the following:

  • mapping_name

  • type_identifier or data_connection_name

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 or resource in the external system for which the mapping is created. For example, for a Kafka connector, the identifier is the topic name. For a JDBC connector, the identifier may be the name of a table, view, or other relation. 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');

data_connection_name

An existing data connection that is used to connect to the external system.

 
CREATE MAPPING my_transactions_topic
DATA CONNECTION myKafkaConnection
OPTIONS (
    'keyFormat' = 'bigint',
    'valueFormat' = 'json-flat');

resource_type

An optional name of the type of resource in the external system. For a JDBC connector, the resource may be a table, view, or other data structure. For a Kafka connector, the resource is a topic.

 
CREATE MAPPING my_transactions_topic (
    __key BIGINT
    transactionType VARCHAR,
    amount INT)
DATA CONNECTION myKafkaConnection
OBJECT TYPE topic
OPTIONS (
    'keyFormat' = 'bigint',
    'valueFormat' = 'json-flat');

'option_name', 'option_value'

Data connection or 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 Edition

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