Supported SQL

ODAS enables the use of SQL for defining datasets. This is done by creating base datasets and then defining views. ODAS is not a full massively parallel processing (MPP) analytics engine, as only a subset of SQL is permitted. This document describes the supported SQL subset.

ODAS in general supports the identical data model as Apache Hive and is generally compatible with HiveQL. Supported functionality may differ depending on the client.

Data Definition Language (DDL) Statements

ODAS generally supports and is compatible with HiveQL DDL statements. In some cases, ODAS is not compatible. In other cases, the supported SQL has been extended for ODAS-specific capabilities – including all statements that modify the catalog and do not read any data (for example, CREATE, DROP, ALTER).

MSCK REPAIR

ODAS does not support the Hive MSCK REPAIR TABLE <table_name>. Instead it supports the alternative, ALTER TABLE <table_name> RECOVER PARTITIONS. This command otherwise behaves identically, automatically adding partitions to the table based on the storage directory structure.

Extensions

Optionally Drop Permissions

There are use cases where it is valid to retain or drop permissions when the corresponding Catalog object (database, table, or view) is dropped. ODAS extends the DROP DATABASE, DROP TABLE, and DROP VIEW statements to optionally specify whether the associated permissions should also be dropped.

DROP DATABASE [IF EXISTS] db [CASCADE] [(INCLUDING | EXCLUDING) PERMISSIONS];
DROP TABLE [IF EXISTS] [db.]tbl [(INCLUDING | EXCLUDING) PERMISSIONS];
DROP VIEW [IF EXISTS] [db.]v [(INCLUDING | EXCLUDING) PERMISSIONS];

If INCLUDING PERMISSIONS is specified, the corresponding permissions are also dropped. Otherwise, permissions will not be dropped, and they will be applied to future catalog objects with that name. If CASCADE is specified, then all permissions on the tables and views in the database are dropped.

It is recommended that users default to the INCLUDING PERMISSIONS behavior and update existing workflows so as to not rely on permissions being retained longer than the object for which they are created.

Example: DROP directives with INCLUDING PERMISSIONS

DROP DATABASE IF EXISTS database1 CASCADE INCLUDING PERMISSIONS;
DROP TABLE IF EXISTS database2.table1 INCLUDING PERMISSIONS;
DROP VIEW IF EXISTS database2.view1 INCLUDING PERMISSIONS;

For users to drop the permissions, they must have grant permissions on the catalog object. For example, to be able to drop a database and its permissions, the user must be able to issue GRANT/REVOKE statements on the database. The user needs to be a catalog admin or having been granted grant permissions.

CREATE TABLE AS SELECT

ODAS supports tables created by this method with the following restrictions:

  • The table must be created from Hive in EMR.

  • The Hive warehouse must have been configured to use S3 (as opposed to using the EMR-local HDFS cluster).

Note: This functionality is not supported from odb.

Registering Hive Serialization/Deserialization (SerDe) Libraries

See Extending ODAS for the DDL grammar and other SerDe considerations.

Creating User Defined Functions (UDFs)

See Extending ODAS for the DDL grammar and other UDF considerations.

Internal versus External Views

Okera views can be defined as either internal or external. This distinction defines how Okera evaluates data at runtime. It could have a profound effect when evaluating joins between tables and views.

In both internal and external cases, data resides in its source systems. Okera managed data continues to be managed in Okera. External views continue to be managed by their non-Okera source.

The primary difference between internal and external views is that external data is not evaluated during a ODAS query. External data does not have fine-grained access control, UDFs, and other features that Okera provides to managed datasets.

It is because of this property that joins are handled differently in internal views verses external views.

Okera manages the join of views defined in ODAS created from two tables or views, internal or external, at the query level. Okera evaluates the join prior to it being sent to the analytics/compute engine for further processing. This allows for fine-grained access control and UDF functionality to be applied to the entire view, regardless of where the source data resides.

Note

ODAS is not a compute engine. Full SQL functionality is not available through the ODAS SQL interface. The use of a compute engine for full analytics functionality is required. For a list of known SQL incompatibilities, refer to the Known Incompatibilities section in this document.

External views created of two tables or views, internal or external, are evaluated in a slightly different way. Data managed by ODAS continues to be evaluated within the ODAS cluster. But, the join between the two tables or views occurs in the analytics/compute engine. The advantage of this approach is that ODAS continues to provide fine-grained access control and UDFs on ODAS managed data, while allowing the sometimes heavy compute of a join to be done outside the ODAS system.

External views require an analytics engine such as Hive or Spark to process aggregate requests and complete the join prior to execution.

Creating External Views

This section provides a number of example common EXTERNAL view uses.

Example: Creating an external view that requires no evaluation in ODAS

CREATE EXTERNAL VIEW random_user_subset AS SELECT *
FROM all_users WHERE rand() % 10 = 0

Note: Views on aggregate functions need to be created as EXTERNAL views, since the aggregates are calculated in compute applications like Hive or Spark.

Example: Creating external views with aggregate functions

CREATE EXTERNAL VIEW revenue_cal
AS SELECT min(revenue) as minRevenue,
max(revenue) as maxRevenue
FROM sales_transactions
WHERE region = 'california'

Since compute applications do not accept the “EXTERNAL view” syntax, it can be executed using odb or Okera Web UI.

By default, views without EXTERNAL are evaluated in Okera, maintaining backwards compatibility with all previous ODAS functions.

LAST PARTITIONS/LAST FILES

ODAS extends the SQL grammar to easily restrict a table scan to just the most recent parts of a dataset. This can be very useful for development or adhoc queries that only need to scan the most recent data, for example the last day or weeks worth. It is expected that these clauses can be used during development, switching to the complete dataset once the application is developed.

OKERA supports the LAST N PARTITIONS, LAST N FILES and LAST PARTITION. LAST PARTITION is just an alias for LAST 1 PARTITIONS.

For example, if the data is partitioned by day, LAST PARTITION can be used to always return the results for the last day. LAST 30 PARTITIONS will return the last month and LAST 100 FILES will return the most recent 100 files worth of data.

These clauses can be added after the table name as an extension of the FROM clause.

Example: Returning the last days results using LAST N PARTITIONS

SELECT * from part_tbl;
SELECT * from part_tbl(LAST PARTITION);
SELECT * from part_tbl(LAST 10 PARTITIONS);
SELECT * from fact_tbl(LAST 20 FILES);

For the last partition clauses, the partitions returned refer to the newest partitions of the table after sorting by partition. Note that this may not be the partition from when data was most recently added. It is not the last-modified partition.

As an example, if the table is partitioned by year, month, and day, it will always return the latest dates regardless of when the partitions were added or when the data in the partitions changed.

These LAST clause computation occurs after partition pruning is done.

Example: Filtering the window for which to select the last partition

SELECT * from part_tbl(LAST PARTITION);
SELECT * from part_tbl(LAST PARTITION) where year = 2010;
SELECT * from part_tbl(LAST 3 PARTITIONS) where month = 6;

These queries respectively return the last partition, the last partition in 2010, and the last 3 partitions in June.

Since the LAST clauses are specified per table, it is possible to specify it for each table in a query independently.

For example:

SELECT * from t1(LAST 10 PARTITIONS)
JOIN t2 ...
JOIN t3(LAST PARTITION)
LAST clause on views

If the LAST clause is specified on a view, then it is propagated to each base table (recursively) in the view. It is invalid to specify the LAST clause on the view and on the LAST clause on an object in the view definition as this can be ambiguous.

Example:

CREATE VIEW v1 AS SELECT * FROM base_tbl;

-- This automatically propagates last to the base table and will only scan 1 partition.
SELECT * FROM v1(LAST PARTITION);

-- If the view is a UNION ALL, it will do so on each base table.
CREATE VIEW v2 AS SELECT * FROM base_tbl1 UNION ALL SELECT * FROM base_tbl2;

-- This will select 1 partition from each table
SELECT * FROM v2(LAST PARTITION);

-- Views can be created ontop of these with the last clause
CREATE VIEW v3 AS SELECT * FROM v1(LAST 10 PARTITIONS);

-- This will select up to 10 partitions each.
SELECT * FROM v3;

-- Both of these are invalid as it is not possible to have multiple LAST clauses
SELECT * FROM v3(LAST PARTITION); -- INVALID: last from v3 clause and SELECT clause
CREATE VIEW v3 AS SELECT * FROM v1(LAST 10 FILES); -- INVALID for same reason.
LAST clause as catalog metadata

In the previous example, the last clause would need to be explicitly specified, either as part of the query, or as explicit views that users use. While the explicit view definition (e.g. sales_data.transactions_dev_sample, which is a view ontop of sales_data.transactions) has benefits, it may be easier to just set it as a property on the base tables or views. This can be done by setting the okera.default-last-n-partitions property on the ODAS catalog object. The value should be an integer, which specifies a default LAST N PARTITIONS clause.

For example,

-- Returns all partitions
SELECT * FROM sales_data.transactions;

-- Set catalog metadata so that queries by default will only scan 3 partitions
ALTER TABLE sales_data.transactions SET TBLPROPERTIES('okera.default-last-n-partitions', 3);

-- Now the same query will automatically scan only the last 3 partitions.
SELECT * FROM sales_data.transactions;

The default value in the metadata is only used if

  • No predicates are specified on the partition columns. If a query explicitly specified a partitioning column, this value is ignored.
  • No explicit LAST clause is specified.

This property can be set on base tables and views.

The expected usage of this feature is to set this property on the largest datasets to implement automatic sampling. This can dramatically improve BI or ad-hoc use cases and end users can get sampling transparently. As this is intended to be transparent to the end user, it may be difficult for users to know this is happening and this should be reserved for tables where sampling is appropriate.

Limitations
  • LAST PARTITIONS can only be used in internal views as other compute engines may not be able to support it.

Example: Improper query with no view

SELECT * from (SELECT * from t) (LAST PARTITION)

Example: Recommended alternative with view creation

CREATE VIEW t_last_partition
AS SELECT * FROM t (LAST PARTITION);

SELECT * FROM t_last_partition;
  • Catalog metadata configuration only supports partitions, not files.

DROP_TABLE_OR_VIEW

ODAS supports DROP_TABLE_OR_VIEW which can be used instead of DROP TABLE or DROP VIEW. This DDL statement will drop the dataset regardless of whether the dataset is a table or view. Since this extends the SQL grammar, this is only available from tools that directly interact with ODAS, such as odb or the UI workspace. This is not available from, for example, hive CLI.

Known Incompatibilities

Stricter Type Promotion**

Hive/HiveQL is very permissive in type promotions, allowing implicit conversions between most types. In ODAS, only lossless type promotion is implicit (for example, INT -> BIGINT). Explicit cast() functions may need to be added for existing SQL statements.

Disallowing Explicit Partitioning Clause when Creating Views**

Hive/HiveQL is used for creating views with an explicit partitioning clause.

Example: Creating views with an explicit partitioning clause

CREATE VIEW v as SELECT ... FROM base_tbl
PARTITIONED BY c1

ODAS does not allow partitioning to be specified for views. Partitioning is instead inferred based on the view statement and base table. The partitioning on the base table is preserved for the view.

This is disallowed. It is unclear what the semantics are, if the partitioning specified in the view is different from the base table, and what the resulting performance implications might be.

Data Manipulation Language (DML) Statements

ODAS is not a distributed SQL engine. It only supports a subset of SQL statements. It does not support the other DML statements (such as INSERT, DELETE, UPDATE, etc). And even for SELECT statements, only a subset of the SQL standard is supported. A typical configuration is to run a SQL engine (on top of Spark or Presto) on top of ODAS.

SELECT statements with projection, filters and union all are fully supported.

COUNT(*) with no grouping is the only aggregation supported. Multiple records are returned for this query, each containing a partial count. Summing up all the counts returns the complete result.

UNION ALL

ODAS supports UNION ALL but not UNION DISTINCT. Per the SQL standard, UNION without a qualifier defaults to UNION DISTINCT so ALL needs to be explicitly specified.

For example:

SELECT * from okera_sample.sample UNION ALL SELECT * from okera_sample.sample

JOINs

When using views, ODAS supports a limited set of joins for the purpose of restricting access to specific rows for particular users. A canonical use case could be having a fact dataset for user transactions, which contains a column for the user ID. Another, much-smaller dataset, contains the set of user ids that allow analytics to be carried out on their activity. ODAS would support filtering the transactions dataset by creating a view that is a join over the two.

The specific limitations are:

  • Only INNER and LEFT (optionally with OUTER, SEMI, ANTI) joins are allowed.
  • The smaller tables must be under a maximum configured size. If the smaller tables exceed the maximum configured size, the request fails at runtime.

Subquery rewrites are supported but must be executable subject to the same constraints.

Configurations

Configurations can be specified at cluster creation time by using the CLI.

By default, joins are enabled with a maximum memory of 128MB per join.

Example: Disabling joins entirely during cluster creation

ocadm clusters create --plannerConfigs "enable_joins=false" ...

Example: Setting the maximum memory during cluster creation to 256MB

ocadm clusters create --workerConfigs "join_max_mem=256000000" ...

See the Cluster Sizing document for more information on how much memory joins need and how that affects cluster node requirements.