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,
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.
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 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];
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.
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
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
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.
Create table over a single file¶
Hive tables (and partitions) traditionally were defined over a directory of files. In ODAS, it is possible to define a table to a single file by specifying as the location, the full path to the file. This allows creating tables without first having to create the parent directory and moving the data files.
CREATE for a table to a single file
CREATE EXTERNAL TABLE tbl(s string) LOCATION 's3://bucket/path/file.parquet'
In this example, when this table is scanned only the specified path is read, specifically, no other files in the 's3://bucket/path/' directory.
Note that tables created this way may not read correctly if the client is bypassing ODAS. For example, Hive clients that are directly reading this table without ODAS, may fail.
Create table with storage file format¶
ODAS supports specifying the underlying storage type file format when creating a table.
The storage type can be specified using the
STORED AS <format-type> command.
The Valid storage types are,
CREATE a table with
STORED AS JSON format
CREATE EXTERNAL TABLE tbl(s string, value int) STORED AS JSON LOCATION 's3://bucket/path_to_dataset_as_json/'
The above command can be used for other file formats as listed above.
Create table with auto inference of datatypes¶
ODAS supports creating tables for which fields are auto-inferred by specifying a sample data file.
CREATE command can be run with an option
LIKE <path_to_sample_file> command.
The auto inference is currently supported for, * AVRO * PARQUET * TEXTFILE * JSON
CREATE a table with
CREATE EXTERNAL TABLE tbl(s string, value int) LIKE JSON 's3://bucket/path_to_dataset_as_json/sample_json.json' STORED AS JSON LOCATION 's3://bucket/path_to_dataset_as_json/'
The above command can be used for other file formats as listed above.
The auto-inference for JSON is entirely based on the data provided in the json file and based on the sample data; the field types can vary. Its recommended to review and amend as necessary.
For example, if the sample data has values for a column named
volume as 300000, 400000, the data type for that column would be
INT and if its actually a
BIGINT, the users need to amend it post table creation.
CHANGE PARTITION LOCATION¶
ODAS allows changing the file location of any particular partition in an external table using sql, after the external table has been created. An example is shown below.
ALTER TABLE tbl PARTITION (part1="partition_name") SET LOCATION 's3://bucket/path_to_dataset'
Example: Change the location for partition
ALTER TABLE hp_test.cerebro_hr_emp PARTITION (snap_dt="20170809") SET LOCATION 's3://qhrcntrl/hr/appl/dde/cln/worker_non_api_cln.dat/snap_dt=20170809'
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.
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
EXTERNALviews, 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 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
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.
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.
LAST clauses are specified per table, it is possible to specify it for each table in a query independently.
SELECT * from t1(LAST 10 PARTITIONS) JOIN t2 ... JOIN t3(LAST PARTITION)
LAST clause on views¶
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.
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.
-- 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.
LAST PARTITIONScan 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 which can be used instead of
DROP TABLE or
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.
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).
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.
ALTER DATABASE SET DBPROPERTIES STATEMENT¶
Users can add additional properties to a catalog database using the
ALTER DATABASE SET DBPROPERTIES command.
ALTER DATABASE <db_name> SET DBPROPERTIES ('description'='Marketing database')
DESCRIBE [FORMATTED] table_name|view_name
This statement would display the definition of the table or view. If FORMATTED keyword is specified, additional details of the definition would be displayed.
Example usage on view:
DESCRIBE FORMATTED partdb.ymdata_view
DESCRIBE FORMATTED on a view has two important information about the view definition. The base tables used in the view and the information about partitioned columns in the view. In the below example, the view partdb.ymdata_view is a view on top of the table partdb.ymdata (sample year-month data). The partitioned columns on the partdb.ymdata table are year,month and that information is displayed as part of view definition as below.
Example output in the 'FORMATTED' display for a view with partitioned columns:
partitioned_cols year,month tables partdb.ymdata
DESCRIBE DATABASE STATEMENT¶
Describe sql statement has been extended to describe the properties of a database object.
DESCRIBE DATABASE jdbc_demo
dbname = jdbc_demo type = REGULAR Owner = root driver = mysql mapred.jdbc.schema.name = mapred.input.table.name = jdbc_demo.transactions_schemaed_demo mapred.jdbc.driver.class = mysql credentials.file = /opt/conf/jdbc_demo.conf mapred.jdbc.db.name = jdbc_demo mapred.jdbc.credentials.file = /opt/conf/jdbc_demo.conf
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
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 but not
Per the SQL standard,
UNION without a qualifier defaults to
UNION DISTINCT so
ALL needs to be explicitly specified.
SELECT * from okera_sample.sample UNION ALL SELECT * from okera_sample.sample
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:
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 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.