SET STREAMING

❗️

This is a legacy Apache Ignite documentation

The new documentation is hosted here: https://ignite.apache.org/docs/latest/

Stream data in bulk from a file into a SQL table.

SET STREAMING [OFF|ON];

Description

Using the SET command, you can stream data in bulk into a SQL table on your Ignite cluster. When streaming is enabled, JDBC/ODBC driver will pack your commands in batches and send them to the server (Ignite cluster). On the server side, the batch is converted into a stream of cache update commands which are distributed asynchronously between server nodes. Asynchrony increases peak throughput because at any given time all cluster nodes are busy with data loading.

Usage

To stream data into your Ignite cluster, prepare a file with SET STREAMING ON command followed by INSERT commands for data that needs to be loaded. For example:

SET STREAMING ON;

INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (3,'Herat','AFG','Herat',186800);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (5,'Amsterdam','NLD','Noord-Holland',731200);
-- More INSERT commands --

Note that before executing the above statements, you should have the tables created in the cluster. Run CREATE TABLE commands, or provide the commands as part of the file that is used for inserting data, before the SET STREAMING ON command, like so:

CREATE TABLE City (
  ID INT(11),
  Name CHAR(35),
  CountryCode CHAR(3),
  District CHAR(20),
  Population INT(11),
  PRIMARY KEY (ID, CountryCode)
) WITH "template=partitioned, backups=1, affinityKey=CountryCode, CACHE_NAME=City, KEY_TYPE=demo.model.CityKey, VALUE_TYPE=demo.model.City";

SET STREAMING ON;

INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (3,'Herat','AFG','Herat',186800);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (5,'Amsterdam','NLD','Noord-Holland',731200);
-- More INSERT commands --

🚧

Flush all data to the cluster

When you have finished loading data, make sure to close the JDBC/ODBC connection so that all data is flushed to the cluster.

📘

Known Limitations

While streaming mode allows you to load data much faster than other data loading techniques mentioned below, it has some limitations which you should consider:

1. Only INSERT commands are allowed; any attempt to execute SELECT or any other DML or DDL command would lead to exception.
2. Due to streaming mode's asynchronous nature, you cannot know update counts for every statement executed; all JDBC/ODBC commands returning update counts will return 0.

Example

As an example, you can use the sample world.sql file that is shipped with the latest Apache Ignite distribution. It can be found in the [IGNITE_HOME]/examples/sql/ directory. You can use therun command of SQLLine, as shown below:

!run /apache_ignite_version/examples/sql/world.sql

After executing the above command and closing the JDBC connection, all data will be loaded into the cluster and ready to be queried.