docs/_docs/sql-reference/operational-commands.adoc - ignite - Git at Google

 // Licensed to the Apache Software Foundation (ASF) under one or more
 // contributor license agreements.  See the NOTICE file distributed with
 // this work for additional information regarding copyright ownership.
 // The ASF licenses this file to You under the Apache License, Version 2.0
 // (the "License"); you may not use this file except in compliance with
 // the License.  You may obtain a copy of the License at
 //
 // http://www.apache.org/licenses/LICENSE-2.0
 //
 // Unless required by applicable law or agreed to in writing, software
 // distributed under the License is distributed on an "AS IS" BASIS,
 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 // See the License for the specific language governing permissions and
 // limitations under the License.
 = Operational Commands


 Ignite supports the following operational commands:

 == COPY

 Copy data from a CSV file into a SQL table.

 [source,sql]
 ----
 COPY FROM '/path/to/local/file.csv'
 INTO tableName (columnName, columnName, ...) FORMAT CSV [CHARSET '<charset-name>']
 ----


 === Parameters
 - `'/path/to/local/file.csv'` - actual path to your CSV file.
 - `tableName` - name of the table to which the data will be copied.
 - `columnName` - name of a column corresponding with the columns in the CSV file.

 === Description
 `COPY` allows you to copy the content of a file in the local file system to the server and apply its data to a SQL table. Internally, `COPY` reads the file content in a binary form into data packets, and sends those packets to the server. Then, the file content is parsed and executed in a streaming mode. Use this mode if you have data dumped to a file.

 NOTE: Currently, `COPY` is only supported via the JDBC driver and can only work with CSV format.

 === Example
 `COPY` can be executed like so:

 [source,sql]
 ----
 COPY FROM '/path/to/local/file.csv' INTO city (
   ID, Name, CountryCode, District, Population) FORMAT CSV
 ----

 In the above command, substitute `/path/to/local/file.csv` with the actual path to your CSV file. For instance, you can use `city.csv` which is shipped with the latest Ignite.
 You can find it in your `{IGNITE_HOME}/examples/src/main/resources/sql/` directory.

 == SET STREAMING

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

 [source,sql]
 ----
 SET STREAMING [OFF|ON];
 ----


 === Description
 Using the `SET` command, you can stream data in bulk into a SQL table in your cluster. When streaming is enabled, the 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. Performing this asynchronously increases peak throughput because at any given time all cluster nodes are busy with data loading.

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

 [source,sql]
 ----
 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:

 [source,sql]
 ----
 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 --
 ----

 [NOTE]
 ====
 [discrete]
 === 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 in this guide, it has some limitations:

 1. Only `INSERT` commands are allowed; any attempt to execute `SELECT` or any other DML or DDL command will cause an 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 Ignite distribution. It can be found in the `{IGNITE_HOME}/examples/sql/` directory. You can use the `run` command from link:sqlline[SQLLine, window=_blank], as shown below:

 [source,shell]
 ----
 !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.

 image::images/set-streaming.png[]


 == KILL QUERY

 The `KILL QUERY` command allows you to cancel a running query. When a query is cancelled with the `KILL` command, all
 parts of the query running on all other nodes are terminated as well.

 [tabs]
 --

 tab:SQL[]
 [source,sql]
 ----
 KILL QUERY {ASYNC} 'query_id'
 ----

 tab:JMX[]
 [source,java]
 ----
 QueryMXBean mxBean = ...;
 mxBean.cancelSQL(queryId);
 ----

 tab:Unix[]
 [source,bash]
 ----
 ./control.sh --kill SQL query_id
 ----

 tab:Windows[]
 [source,bash]
 ----
 control.bat --kill SQL query_id
 ----

 --

 === Parameters

 * `query_id` - can be retrived via the link:monitoring-metrics/system-views#sql_queries[SQL_QUERIES] view.
 * `ASYNC` - is an optional parameter that returns control immediately without waiting for the cancellation to finish.

 == KILL TRANSACTION

 The `KILL TRANSACTION` command allows you to cancel a running transaction.

 [tabs]
 --
 tab:SQL[]
 [source,sql]
 ----
 KILL TRANSACTION 'xid'
 ----

 tab:JMX[]
 [source,java]
 ----
 TransactionMXBean mxBean = ...;
 mxBean.cancel(xid);
 ----

 tab:Unix[]
 [source,bash]
 ----
 ./control.sh --kill TRANSACTION xid
 ----

 tab:Windows[]
 [source,bash]
 ----
 control.bat --kill TRANSACTION xid
 ----
 --

 === Parameters

 * `xid` - the transaction id that can be retrived via the link:monitoring-metrics/system-views#transactions[TRANSACTIONS] view.


 == KILL SCAN

 The `KILL SCAN` command allows you to cancel a running scan query.

 [tabs]
 --

 tab:SQL[]
 [source,sql]
 ----
 KILL SCAN 'origin_node_id' 'cache_name' query_id
 ----

 tab:JMX[]
 [source,java]
 ----
 QueryMXBean mxBean = ....;
 mxBean.cancelScan(originNodeId, cacheName, queryId);
 ----

 tab:Unix[]
 [source,bash]
 ----
 ./control.sh --kill SCAN origin_node_id cache_name query_id
 ----

 tab:Windows[]
 [source,bash]
 ----
 control.bat --kill SCAN origin_node_id cache_name query_id
 ----

 --

 === Parameters

 * `origin_node_id`, `cache_name`, `query_id` - can be retrived via the link:monitoring-metrics/system-views#scan_queries[SCAN_QUERIES] view.

 === Example

 [source,sql]
 ----
 KILL SCAN '6fa749ee-7cf8-4635-be10-36a1c75267a7_54321' 'cache-name' 1
 ----

 == KILL COMPUTE

 The `KILL COMPUTE` command allows you to cancel a running compute.

 [tabs]
 --

 tab:SQL[]
 [source,sql]
 ----
 KILL COMPUTE 'session_id'
 ----

 tab:JMX[]
 [source,java]
 ----
  ComputeMXBean#cancel
 ----

 tab:Unix[]
 [source,bash]
 ----
 ./control.sh --kill COMPUTE session_id
 ----

 tab:Windows[]
 [source,bash]
 ----
 control.bat --kill COMPUTE session_id
 ----

 --

 === Parameters

 * `session_id` - can be retrived via the link:monitoring-metrics/system-views#tasks[TASKS] or
 link:monitoring-metrics/system-views#jobs[JOBS] views.

 == KILL CONTINUOUS

 The `KILL CONTINUOUS` command allows you to cancel a running continuous query.

 [tabs]
 --

 tab:SQL[]
 [source,sql]
 ----
 KILL CONTINUOUS 'origin_node_id', 'routine_id'
 ----

 tab:JMX[]
 [source,java]
 ----
 QueryMXBean mxBean = ...;
 mxBean.cancelContinuous(originNodeId, routineId);
 ----

 tab:Unix[]
 [source,bash]
 ----
 ./control.sh --kill CONTINUOUS origin_node_id routine_id
 ----

 tab:Windows[]
 [source,bash]
 ----
 control.bat --kill CONTINUOUS origin_node_id routine_id
 ----

 --

 === Parameters

 * `origin_node_id` and `routine_id` - can be retrived via the link:monitoring-metrics/system-views#continuous_queries[CONTINUOUS_QUERIES] view.

 === Example

 [source,sql]
 ----
 KILL CONTINUOUS '6fa749ee-7cf8-4635-be10-36a1c75267a7_54321' '6fa749ee-7cf8-4635-be10-36a1c75267a7_12345'
 ----

 == KILL SERVICE

 The `KILL SERVICE` command allows you to cance a running service.

 [tabs]
 --

 tab:SQL[]
 [source,sql]
 ----
 KILL SERVICE 'name'
 ----

 tab:JMX[]
 [source,java]
 ----
 ServiceMXBean mxBean = ...;
 mxBean.cancel(name);
 ----

 tab:Unix[]
 [source,bash]
 ----
 ./control.sh --kill SERVICE name
 ----

 tab:Windows[]
 [source,bash]
 ----
 control.bat --kill SERVICE name
 ----

 --

 === Parameters

 * `name` - corresponds to the name you selected for the service upon the deployment time.
 You can always find it with the link:monitoring-metrics/system-views#services[SERVICES] view.
	// Licensed to the Apache Software Foundation (ASF) under one or more
	// contributor license agreements. See the NOTICE file distributed with
	// this work for additional information regarding copyright ownership.
	// The ASF licenses this file to You under the Apache License, Version 2.0
	// (the "License"); you may not use this file except in compliance with
	// the License. You may obtain a copy of the License at
	//
	// http://www.apache.org/licenses/LICENSE-2.0
	//
	// Unless required by applicable law or agreed to in writing, software
	// distributed under the License is distributed on an "AS IS" BASIS,
	// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	// See the License for the specific language governing permissions and
	// limitations under the License.
	= Operational Commands


	Ignite supports the following operational commands:

	== COPY

	Copy data from a CSV file into a SQL table.

	[source,sql]
	----
	COPY FROM '/path/to/local/file.csv'
	INTO tableName (columnName, columnName, ...) FORMAT CSV [CHARSET '<charset-name>']
	----


	=== Parameters
	- `'/path/to/local/file.csv'` - actual path to your CSV file.
	- `tableName` - name of the table to which the data will be copied.
	- `columnName` - name of a column corresponding with the columns in the CSV file.

	=== Description
	`COPY` allows you to copy the content of a file in the local file system to the server and apply its data to a SQL table. Internally, `COPY` reads the file content in a binary form into data packets, and sends those packets to the server. Then, the file content is parsed and executed in a streaming mode. Use this mode if you have data dumped to a file.

	NOTE: Currently, `COPY` is only supported via the JDBC driver and can only work with CSV format.

	=== Example
	`COPY` can be executed like so:

	[source,sql]
	----
	COPY FROM '/path/to/local/file.csv' INTO city (
	ID, Name, CountryCode, District, Population) FORMAT CSV
	----

	In the above command, substitute `/path/to/local/file.csv` with the actual path to your CSV file. For instance, you can use `city.csv` which is shipped with the latest Ignite.
	You can find it in your `{IGNITE_HOME}/examples/src/main/resources/sql/` directory.

	== SET STREAMING

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

	[source,sql]
	----
	SET STREAMING [OFF\|ON];
	----


	=== Description
	Using the `SET` command, you can stream data in bulk into a SQL table in your cluster. When streaming is enabled, the 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. Performing this asynchronously increases peak throughput because at any given time all cluster nodes are busy with data loading.

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

	[source,sql]
	----
	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:

	[source,sql]
	----
	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 --
	----

	[NOTE]
	====
	[discrete]
	=== 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 in this guide, it has some limitations:

	1. Only `INSERT` commands are allowed; any attempt to execute `SELECT` or any other DML or DDL command will cause an 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 Ignite distribution. It can be found in the `{IGNITE_HOME}/examples/sql/` directory. You can use the `run` command from link:sqlline[SQLLine, window=_blank], as shown below:

	[source,shell]
	----
	!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.

	image::images/set-streaming.png[]


	== KILL QUERY

	The `KILL QUERY` command allows you to cancel a running query. When a query is cancelled with the `KILL` command, all
	parts of the query running on all other nodes are terminated as well.

	[tabs]
	--

	tab:SQL[]
	[source,sql]
	----
	KILL QUERY {ASYNC} 'query_id'
	----

	tab:JMX[]
	[source,java]
	----
	QueryMXBean mxBean = ...;
	mxBean.cancelSQL(queryId);
	----

	tab:Unix[]
	[source,bash]
	----
	./control.sh --kill SQL query_id
	----

	tab:Windows[]
	[source,bash]
	----
	control.bat --kill SQL query_id
	----

	--

	=== Parameters

	* `query_id` - can be retrived via the link:monitoring-metrics/system-views#sql_queries[SQL_QUERIES] view.
	* `ASYNC` - is an optional parameter that returns control immediately without waiting for the cancellation to finish.

	== KILL TRANSACTION

	The `KILL TRANSACTION` command allows you to cancel a running transaction.

	[tabs]
	--
	tab:SQL[]
	[source,sql]
	----
	KILL TRANSACTION 'xid'
	----

	tab:JMX[]
	[source,java]
	----
	TransactionMXBean mxBean = ...;
	mxBean.cancel(xid);
	----

	tab:Unix[]
	[source,bash]
	----
	./control.sh --kill TRANSACTION xid
	----

	tab:Windows[]
	[source,bash]
	----
	control.bat --kill TRANSACTION xid
	----
	--

	=== Parameters

	* `xid` - the transaction id that can be retrived via the link:monitoring-metrics/system-views#transactions[TRANSACTIONS] view.


	== KILL SCAN

	The `KILL SCAN` command allows you to cancel a running scan query.

	[tabs]
	--

	tab:SQL[]
	[source,sql]
	----
	KILL SCAN 'origin_node_id' 'cache_name' query_id
	----

	tab:JMX[]
	[source,java]
	----
	QueryMXBean mxBean = ....;
	mxBean.cancelScan(originNodeId, cacheName, queryId);
	----

	tab:Unix[]
	[source,bash]
	----
	./control.sh --kill SCAN origin_node_id cache_name query_id
	----

	tab:Windows[]
	[source,bash]
	----
	control.bat --kill SCAN origin_node_id cache_name query_id
	----

	--

	=== Parameters

	* `origin_node_id`, `cache_name`, `query_id` - can be retrived via the link:monitoring-metrics/system-views#scan_queries[SCAN_QUERIES] view.

	=== Example

	[source,sql]
	----
	KILL SCAN '6fa749ee-7cf8-4635-be10-36a1c75267a7_54321' 'cache-name' 1
	----

	== KILL COMPUTE

	The `KILL COMPUTE` command allows you to cancel a running compute.

	[tabs]
	--

	tab:SQL[]
	[source,sql]
	----
	KILL COMPUTE 'session_id'
	----

	tab:JMX[]
	[source,java]
	----
	ComputeMXBean#cancel
	----

	tab:Unix[]
	[source,bash]
	----
	./control.sh --kill COMPUTE session_id
	----

	tab:Windows[]
	[source,bash]
	----
	control.bat --kill COMPUTE session_id
	----

	--

	=== Parameters

	* `session_id` - can be retrived via the link:monitoring-metrics/system-views#tasks[TASKS] or
	link:monitoring-metrics/system-views#jobs[JOBS] views.

	== KILL CONTINUOUS

	The `KILL CONTINUOUS` command allows you to cancel a running continuous query.

	[tabs]
	--

	tab:SQL[]
	[source,sql]
	----
	KILL CONTINUOUS 'origin_node_id', 'routine_id'
	----

	tab:JMX[]
	[source,java]
	----
	QueryMXBean mxBean = ...;
	mxBean.cancelContinuous(originNodeId, routineId);
	----

	tab:Unix[]
	[source,bash]
	----
	./control.sh --kill CONTINUOUS origin_node_id routine_id
	----

	tab:Windows[]
	[source,bash]
	----
	control.bat --kill CONTINUOUS origin_node_id routine_id
	----

	--

	=== Parameters

	* `origin_node_id` and `routine_id` - can be retrived via the link:monitoring-metrics/system-views#continuous_queries[CONTINUOUS_QUERIES] view.

	=== Example

	[source,sql]
	----
	KILL CONTINUOUS '6fa749ee-7cf8-4635-be10-36a1c75267a7_54321' '6fa749ee-7cf8-4635-be10-36a1c75267a7_12345'
	----

	== KILL SERVICE

	The `KILL SERVICE` command allows you to cance a running service.

	[tabs]
	--

	tab:SQL[]
	[source,sql]
	----
	KILL SERVICE 'name'
	----

	tab:JMX[]
	[source,java]
	----
	ServiceMXBean mxBean = ...;
	mxBean.cancel(name);
	----

	tab:Unix[]
	[source,bash]
	----
	./control.sh --kill SERVICE name
	----

	tab:Windows[]
	[source,bash]
	----
	control.bat --kill SERVICE name
	----

	--

	=== Parameters

	* `name` - corresponds to the name you selected for the service upon the deployment time.
	You can always find it with the link:monitoring-metrics/system-views#services[SERVICES] view.