Spark Open Source Commands

Updated on 2025-01-22 GMT+08:00

View PDF

This section describes the open source Spark SQL syntax supported by DLI. For details about the syntax, parameters, and examples, see Spark SQL Syntax.

**Table 1** DLI Spark open source commands
Function	Syntax Example	DLI Spark 2.4.5	DLI Spark 3.3.1
Creating a database	CREATE DATABASE testDB;	Supported	Supported
Creating a DLI table	create table if not exists testDB.testTable1( id int, age int, money double );	Supported	Supported
Creating an OBS table	create table if not exists testDB.testTable2( id int, age int, money double ) LOCATION 'obs://bucketName/filePath' partitioned by (dt string) row format delimited fields terminated by ',' STORED as TEXTFILE;	Supported	Supported
Inserting test data	insert into table testDB.testTable2 values (1, 18, 3.14, "20240101" ), (2, 18, 3.15, "20240102" );	Supported	Supported
Changing database attributes	ALTER DATABASE testDB SET DBPROPERTIES ('Edited-by' = 'John');	Not supported	Not supported
Changing the path for storing database files on OBS	ALTER DATABASE testDB SET LOCATION 'obs://bucketName/filePath';	Not supported	Not supported
Changing a table name	ALTER TABLE testDB.testTable1 RENAME TO testDB.testTable101;	Supported	Supported
Changing the name of a partition in a table	ALTER TABLE testDB.testTable2 PARTITION ( dt='20240101') RENAME TO PARTITION ( dt='20240103'); It only supports renaming partitions of tables stored in OBS, and the storage path of the table in OBS will not change.	Supported	Supported
Adding a column	ALTER TABLE testDB.testTable1 ADD COLUMNS (name string);	Supported	Supported
Dropping a column	ALTER TABLE testDB.testTable1 DROP columns (money);	Not supported	Not supported
Changing the name of a column	ALTER TABLE testDB.testTable1 RENAME COLUMN age TO years_of_age;	Not supported	Not supported
Modifying column comments	ALTER TABLE testDB.testTable1 ALTER COLUMN age COMMENT "new comment";	Not supported	Supported
Replacing a specified column	ALTER TABLE testDB.testTable1 REPLACE COLUMNS (name string, ID int COMMENT 'new comment');	Not supported	Not supported
Customizing table attributes	ALTER TABLE testDB.testTable1 SET TBLPROPERTIES ('aaa' = 'bbb');	Supported	Supported
Adding or modifying table comments	ALTER TABLE testDB.testTable1 SET TBLPROPERTIES ('comment' = 'test');	Supported	Not supported
Changing the storage format of a table	ALTER TABLE testDB.testTable1 SET fileformat csv;	Not supported	Not supported
Deleting a table attribute	ALTER TABLE testDB.testTable1 UNSET TBLPROPERTIES ('test');	Supported	Supported
Deleting table comments	ALTER TABLE testDB.testTable1 UNSET TBLPROPERTIES ('comment');	Supported	Supported
Displaying column information	DESCRIBE database_name.table_name col_name; Example: DESCRIBE testDB.testTable1 id;	Supported	Supported
Displaying column information	DESCRIBE table_name table_name.col_name; Example: DESCRIBE testTable1 testTable1.id; It only supports viewing the column information for tables in the current database.	Supported	Supported
Returning metadata information of a query statement	DESCRIBE QUERY SELECT age, sum(age) FROM testDB.testTable1 GROUP BY age;	Not supported	Supported
Returning metadata information of inserted data	DESCRIBE QUERY VALUES(100, 10, 10000.20D) AS testTable1(id, age, money);	Not supported	Supported
Returning metadata information of a table	DESCRIBE QUERY TABLE testTable1;	Not supported	Supported
Returning metadata information of a column in a table	DESCRIBE FROM testTable1 SELECT age;	Not supported	Supported
Returning the table creation statements for a table	SHOW CREATE TABLE testDB.testTable1 AS SERDE; When executing this statement in Spark 3.3.1, it only applies to query the table creation statements for Hive tables.	Not supported	Supported
Returning the table creation statements for a table	SHOW CREATE TABLE testDB.testTable1;	Supported	Supported

Parent topic: Spark SQL Syntax Reference

Previous topic: SQL Syntax Overview of Batch Jobs

Next topic: Databases

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot