Zetaris SQL Guide

CURRENTLY IN REVISION

The Zetaris SQL Guide will help you to write queries natively within the Zetaris SQL Editor, but also when connecting through external tools.

Data Source Connection and Registration Statements

Pipeline and View Statements

DDL Statements (connected data sources and lakehouses)

DML Statements (connected data sources and lakehouses)

DCL Statements (for Zetaris)

Data Retrieval Statements (connected data sources and lakehouses)

Auxiliary Statements

Connection and Registration Statements

Connection and Registration statements are used to connect your data sources and lakehouses, and subsequently register the nominated tables/files.

NOTE: For Object Store, File Source, or RESTful API connections, you will need to create a LIGHTNING DATABASE first.

CREATE DATALAKE
CREATE DATASOURCE
CREATE LIGHTNING DATABASE (Filesource, Object Store, and RESTful API connections)
CREATE TABLE
- CREATE LIGHTNING FILESTORE TABLE
- CREATE LIGHTNING REST TABLE
DROP DATASOURCE
DROP LIGHTNING DATABASE
DROP DATALAKE
REGISTER DATASOURCE TABLES
REGISTER METASTORE
REGISTER STREAMING DATASOURCE

Pipeline and View Statements

Pipeline and View statements are used to create, and manage the virtual pipelines and views that are created in Zetaris..

NOTE: Any Pipeline or View must be stored in a Container

ALTER VIEW
CREATE CONTAINER
- CREATE PIPELINE CONTAINER
- CREATE SCHEMASTORE CONTAINER
CREATE SCHEMASTORE VIEW
DROP VIEW
DROP CONTAINER
- DROP PIPELINE CONTAINER
- DROP SCHEMASTORE CONTAINER

DDL Statements (connected databases and lakehouses)

Data Definition statements (Data Definition Language, DDL) are used to create or modify the structure of database objects in a database or in Zetaris. Zetaris supports for followings Data Definition Statements

ALTER TABLE
CREATE TABLE
- CREATE TABLE (Database)
- CREATE TABLE (Lakehouse)
TRUNCATE TABLE

DML Statements (connected databases and lakehouses)

Data Manipulation statements (Data Manipulation Language, DML) are used to add, change, or delete data. Spark SQL supports the following Data Manipulation Statements:

INSERT TABLE

DCL Statements

Data Control statements (Data Control Language, DCL) allow you to, through SQL, manage security elements across the Zetaris platform.

GRANT
REVOKE
UPSERT AUTH (for RESTful API connections)

Data Retrieval Statements

SELECT
EXPLAIN (shows query plan)

Auxiliary Statements

ANALYZE
DESCRIBE
- DESCRIBE MASTER DATASOURCE
- DESCRIBE TABLES
SHOW
- SHOW CACHE TABLES
- SHOW DATASOURCE TABLES

DCL Statements (connected databases and lakehouses)

UPSERT

UPSERT AUTH salesforce_auth REQUEST(
endpoint "https://login.salesforce.com/services/oauth2/token",
method "post",
http_encoding "urlencoded"
) HEADER (
) BODY (
grant_type "password",
username "ben.tzannes@zetaris.com",
password "gac7XTK1tvg*vmx@pazMfSMCBJSt16VqRSGamFghtca5",
client_id "3MVG9fe4g9fhX0E4_R1XeMebamQmo1snRTSKhSmyJhPzfl5sexvO3gUSwg_F5D6wvyxjaKpViYxuqkP7.3Ntr",
client_secret "0B07E7FA2AB52A88C783BEA76579BBA72757A114662866B0771672770FF10FB9"
);

Auxiliary Statements

ANALYZE

OPTIMIZER ON/OFF

To turn the Zetaris Optimization on and off, execute the following command in the SQL Editor

TURN OPTIMIZER “PUSHDOWN_DIFFERENT_TYPE_PREDICATE” ON | OFF <datasource>

TURN OPTIMIZER "PUSHDOWN_AGGREGATION" ON | OFF datasource