TRUNCATE 문
TRUNCATE 문 (TRUNCATE Statements)
배치(Batch)
TRUNCATE 문은 테이블 자체를 삭제하지 않고 테이블의 모든 행을 삭제하는 데 사용됩니다.
주의: 현재
TRUNCATE문은 배치 모드에서 지원되며, 행 삭제를 지원하려면 대상 테이블 커넥터가SupportsTruncate인터페이스를 구현해야 합니다. 관련 인터페이스를 구현하지 않은 테이블을TRUNCATE하려 하면 예외가 발생합니다.
출처: 문서
본문
TRUNCATE 문 실행 (Run a TRUNCATE statement)
TRUNCATE 문은 TableEnvironment의 executeSql() 메서드로 실행할 수 있습니다. executeSql() 메서드는 연산에 오류가 있으면 예외를 던집니다.
Java
EnvironmentSettings settings = EnvironmentSettings.newInstance().inBatchMode().build();
TableEnvironment tEnv = TableEnvironment.create(settings);
// register a table named "Orders"
tEnv.executeSql("CREATE TABLE Orders (`user` STRING, product STRING, amount INT) WITH (...)");
// insert values
tEnv.executeSql("INSERT INTO Orders VALUES ('Lili', 'Apple', 1), ('Jessica', 'Banana', 2), ('Mr.White', 'Chicken', 3)").await();
tEnv.executeSql("SELECT * FROM Orders").print();
// +--------------------------------+--------------------------------+-------------+
// | user | product | amount |
// +--------------------------------+--------------------------------+-------------+
// | Lili | Apple | 1 |
// | Jessica | Banana | 2 |
// | Mr.White | Chicken | 3 |
// +--------------------------------+--------------------------------+-------------+
// 3 rows in set
// truncate the table "Orders"
tEnv.executeSql("TRUNCATE TABLE Orders").await();
tEnv.executeSql("SELECT * FROM Orders").print();
// Empty set
Scala
val env = StreamExecutionEnvironment.getExecutionEnvironment()
val settings = EnvironmentSettings.newInstance().inBatchMode().build()
val tEnv = StreamTableEnvironment.create(env, settings)
// register a table named "Orders"
tEnv.executeSql("CREATE TABLE Orders (`user` STRING, product STRING, amount INT) WITH (...)")
// insert values
tEnv.executeSql("INSERT INTO Orders VALUES ('Lili', 'Apple', 1), ('Jessica', 'Banana', 2), ('Mr.White', 'Chicken', 3)").await()
tEnv.executeSql("SELECT * FROM Orders").print()
// +--------------------------------+--------------------------------+-------------+
// | user | product | amount |
// +--------------------------------+--------------------------------+-------------+
// | Lili | Apple | 1 |
// | Jessica | Banana | 2 |
// | Mr.White | Chicken | 3 |
// +--------------------------------+--------------------------------+-------------+
// 3 rows in set
// truncate the table "Orders"
tEnv.executeSql("TRUNCATE TABLE Orders").await()
tEnv.executeSql("SELECT * FROM Orders").print()
// Empty set
Python
env_settings = EnvironmentSettings.in_batch_mode()
table_env = TableEnvironment.create(env_settings)
# register a table named "Orders"
table_env.execute_sql("CREATE TABLE Orders (`user` STRING, product STRING, amount INT) WITH (...)")
# insert values
table_env.execute_sql("INSERT INTO Orders VALUES ('Lili', 'Apple', 1), ('Jessica', 'Banana', 2), ('Mr.White', 'Chicken', 3)").wait()
table_env.execute_sql("SELECT * FROM Orders").print()
# +--------------------------------+--------------------------------+-------------+
# | user | product | amount |
# +--------------------------------+--------------------------------+-------------+
# | Lili | Apple | 1 |
# | Jessica | Banana | 2 |
# | Mr.White | Chicken | 3 |
# +--------------------------------+--------------------------------+-------------+
# 3 rows in set
# truncate the table "Orders"
table_env.execute_sql("TRUNCATE TABLE Orders").wait()
table_env.execute_sql("SELECT * FROM Orders").print()
# Empty set
SQL CLI
Flink SQL> SET 'execution.runtime-mode' = 'batch';
[INFO] Session property has been set.
Flink SQL> CREATE TABLE Orders (`user` STRING, product STRING, amount INT) with (...);
[INFO] Execute statement succeeded.
Flink SQL> INSERT INTO Orders VALUES ('Lili', 'Apple', 1), ('Jessica', 'Banana', 1), ('Mr.White', 'Chicken', 3);
[INFO] Submitting SQL update statement to the cluster...
[INFO] SQL update statement has been successfully submitted to the cluster:
Job ID: bd2c46a7b2769d5c559abd73ecde82e9
Flink SQL> SELECT * FROM Orders;
user product amount
Lili Apple 1
Jessica Banana 2
Mr.White Chicken 3
Flink SQL> TRUNCATE TABLE Orders;
[INFO] Execute statement succeeded.
Flink SQL> SELECT * FROM Orders;
// Empty set
문법 (Syntax)
TRUNCATE TABLE [catalog_name.][db_name.]table_name