Spark SQL statement types for application development - AnalyticDB

AnalyticDB for MySQL lets you submit Spark SQL applications directly in the console to run data analysis — no JAR packages or Python code required. A Spark SQL application consists of three statement types: CONF statements, ADD JAR statements, and SQL statements.

Development tool

Use the SQL editor to create and run Spark SQL applications.

Sample code

The following example reads data from Object Storage Service (OSS). Each section uses a comment to mark its purpose: CONF statements configure the Spark engine, ADD JAR loads the required JAR package, and the SQL statements create a database, define an external table, insert a row, and query it back.

-- Configure the Spark engine.
conf spark.driver.resourceSpec=medium;
conf spark.executor.resourceSpec=medium;
conf spark.app.name=Spark SQL Test;
conf spark.adb.connectors=oss;

-- Load the required JAR package.
add jar oss://example/jars/hive-hcatalog-core-2.3.9.jar;

-- Create a database backed by an OSS path.
CREATE DATABASE IF NOT EXISTS testdb LOCATION 'oss://<bucket_name>/test';

-- Define an external table using the JSON SerDe.
CREATE EXTERNAL TABLE if not EXISTS `testdb`.`catalog_json_test` (
  `a` string COMMENT 'from deserializer',
  `b` string COMMENT 'from deserializer')
ROW format serde
'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION
'oss://<bucket_name>/test/catalog_json_test/';

-- Insert a row and verify with a query.
INSERT INTO `testdb`.`catalog_json_test` values('key','value');

SELECT * FROM `testdb`.`catalog_json_test`;

Replace <bucket_name> with your OSS bucket name.

Statement types

CONF statements

CONF statements configure the Spark engine and are typically placed before all SQL statements.

Syntax

conf <key>=<value>;

Rules

Each CONF statement sets one Spark parameter value and must be separated by a semicolon (;).

Keys and values cannot be enclosed in single (') or double (") quotation marks.

For supported parameters, see Conf configuration parameters.

ADD JAR statements

ADD JAR statements load JAR packages required by the application — such as user-defined function (UDF) JARs and data source connector JARs. Place them before your SQL statements. JAR packages must be stored in OSS.

Syntax

add jar <oss-path>;

Rules

Each ADD JAR statement specifies the OSS path of one JAR package and must be separated by a semicolon (;).

Strings in the OSS path cannot be enclosed in single (') or double (") quotation marks.

SQL statements

Spark SQL supports DDL, DML, and DQL statements, including SELECT, INSERT, and SHOW DATABASE. For syntax details, see Use Spark SQL to create a C-Store table.