Obsolete
This page is obsolete. It has been replaced with http://trafodion.apache.org/documentation.html
The first release of Trafodion provides an operational SQL engine on top of Hadoop. Features include:
- Fully functional ANSI SQL language support
- Full ACID support for read/write queries including distributed transaction protection for multiple rows, tables, and statements
- Heterogeneous storage engine access including native access to data stores
- Enhanced High Availability support for client applications
- Support for large data sets using optimized intra-query parallelism
- Performance improvements for OLTP workloads via compile and runtime optimizations
Transaction management features include:
- Transaction serializability using the HBase-Trx implementation of Multi-Version Concurrency Control
- Transaction recovery to achieve database consistency
- Thread-aware transaction management support to work with multi-threaded SQL clients
- Non-transactional/direct access to HBase tables
The sections below give more details.
Key Features
SQL Data Definition Language (DDL) Statements
Statement | Description |
---|---|
CREATE SCHEMA | This statement runs but does not actually create a schema. A schema is created when you qualify the table name with a new schema name in a CREATE TABLE statement. |
CREATE TABLE | Creates a table; includes support for variations like CREATE TABLE AS, CREATE TABLE LIKE, and CREATE VOLATILE TABLE |
CREATE INDEX | Creates an index on an existing table. |
CREATE VIEW | Creates a view. |
CREATE FUNCTION | Creates a user-defined function (UDF) written in C. |
CREATE PROCEDURE | Creates a stored procedure written in Java (SPJ). |
CREATE LIBRARY | Creates a library object for an SPJ's JAR file or a UDF's library file in the database schema. |
DROP TABLE | Drops a table; includes support for both RESTRICT and CASCADE options. |
DROP INDEX | Drops an index. |
DROP VIEW | Drops a view. |
DROP FUNCTION | Drops a user-defined function (UDF) written in C. |
DROP PROCEDURE | Drops a stored procedure written in Java (SPJ). |
DROP LIBRARY | Drops a library object for SPJs or UDFs from the database schema. |
ALTER TABLE | Alters table by adding or removing columns to an existing table. |
SQL Data Manipulation Language (DML) Statements
Both read (SELECT) and write (INSERT/UPDATE/DELETE) statements are supported. The CALL statement (for invoking Java stored procedures) is also supported.
SELECT Statement
Feature | Description |
---|---|
SELECT list | Cardinality can be specified using ANY, FIRST, ALL clauses. DISTINCT clause is supported to remove duplicates. Multiple DISTINCT clauses are supported. Column names can reference correlated names. |
FROM clause | Can include table or view references. Joins can be natural or cross product joins. Following join types are supported:
|
WHERE clause | Allows user to specify search condition to select rows. |
Subquery support | Subquery allowed as part of search condition. Supported as both nested as well as correlated. |
GROUP BY clause | Allows user to return the rows in grouping columns that define groups. Both column names and ordinals are allowed. |
HAVING clause | Allows user to specify a search condition to apply to each group resulting from GROUP BY clause. |
ORDER BY clause | Allows user to specify ordering for the rows returned. Both column names and ordinals are allowed. |
UNION clause | Allows users to merge result output from two SELECT statements. |
SAMPLE clause | Trafodion extension that allows user to select a subset of rows from result table. |
TRANSPOSE clause | Trafodion extension that allows users functionality similar to Pivot command in Oracle and SQL server products. |
SEQUENCE BY clause | Trafodion extension to specify the sort order for result table for calculating sequence functions. |
INSERT/UPDATE/DELETE Statements
Statement | Description |
---|---|
INSERT | Inserts data into tables and views. |
UPDATE | Updates values of columns of a table or view. |
DELETE | Deletes rows from a table or view. |
UPSERT | Updates a table if the row exists and inserts into a table if the row does not exist. |
MERGE | Performs UPSERT and additionally updates matching rows from one table to another. |
CALL Statement
Statement | Description |
---|---|
CALL | Calls a stored procedure in Java (SPJ). |
SQL Transaction Control Statements
Statement | Description |
---|---|
BEGIN WORK | Starts a transaction. |
COMMIT WORK | Commits any changes and ends the transaction. |
ROLLBACK WORK | Undoes any changes and ends the transaction. |
SET TRANSACTION | Sets attributes for the next transaction. |
SQL Data Types
The CAST function can be used to convert values from one data type to another.
Data Type | Description |
---|---|
Numeric | Support for the following numeric data types:
|
Character | Support for the following string data types:
Character data type support includes international character set support. |
Date/time | Support for the following data types:
|
Interval | INTERVAL |
SQL Language Feature Support
Feature | Description |
---|---|
Character sets | ISO88591, UTF8, and UCS2 character sets are supported for user data. Metadata is stored using UTF8 encoding. The TRANSLATE function supports translation of a character string between compatible character sets. |
Constraints | Primary key, not null, check, and unique constraints are supported. Foreign key constraints (referential integrity) are also supported. |
Correlation names | Correlation names for tables, views, or subqueries are supported. |
Cursors | Support for non-holdable cursors only. |
Expressions | Support for data-type–based value expressions. |
Identifiers | Support for both regular and delimited identifiers. |
Keys | Support for primary keys (but primary keys cannot be dropped), clustering keys, and hash partition keys. Additionally, a system-generated syskey can be used as key. |
Literals | Support for data-type–based literals. |
Predicates | Support for following types of predicates: BETWEENComparison operatorsEXISTSINLIKEIS [NOT] NULLQuantified comparison operators |
Subquery | Support for nested and correlated subqueries. |
SQL Clauses
Clause Type | Description |
---|---|
DEFAULT | Allows a default value for a column in a CREATE TABLE or ALTER TABLE ADD COLUMN statement. |
FORMAT | Allows one to specify the output format of a column. |
SQL Functions
Function type | Description | Details |
---|---|---|
Aggregates | Aggregate functions | Supported functions: AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE |
Character | Character string functions on character data type values | Supported functions: ASCII, CHAR, CHAR_LENGTH, CODE_VALUE, CONCAT, INSERT, LCASE, LEFT, LOCATE, LOWER, LPAD, LTRIM, OCTET_LENGTH, POSITION, REPEAT, REPLACE, RIGHT, RPAD, RTRIM, SPACE, SUBSTRING/SUBSTR, TRANSLATE, TRIM, UCASE, UPPER, UPSHIFT |
Date/time | Time-related functions on date/timestamp data type | Supported functions: ADD_MONTHS, CONVERTTIMESTAMP, CURRENT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DATE_ADD, DATE_PART, DATE_SUB, DATE_TRUNC, DATEADD, DATEDIFF, DATEFORMAT, DAY, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, EXTRACT, HOUR, JULIANTIMESTAMP, MINUTE, MONTH, MONTHNAME, QUARTER, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, WEEK, YEAR |
Mathematical | Math and statistical functions on numerical data type | Supported functions: ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COSH, DEGRESS, EXP, FLOOR, LOG, LOG10, MOD, NULLIFZERO, PI, POWER, RADIANS, SIGN, SIN, SINH, SQRT, TAN, TANH, ZEROIFNULL |
Sequence | Trafodion extension that provides a subset of aggregate functions on an intermediate table of ordered rows | Supported functions:
|
Miscellaneous | Utility functions related to NULL, security, data type conversion, and so on. | Supported functions: BITAND, CASE, CAST, COALESCE, CONVERTTOHEX, CURRENT_USER, DECODE, EXPLAIN, ISNULL, NULLIF, NVL, SESSION_USER, USER |
User-defined functions | User-defined functions (UDFs) written in C are supported. | Functions may return scalar values, rows, or tables. |
SQL Utilities
Statement | Description |
---|---|
UPDATE STATISTICS | Trafodion extension that updates the histogram statistics for one or more groups of columns within a table |
EXPLAIN | Trafodion extension function that generates a result table describing the access plan for a DML statement |
CONTROL QUERY DEFAULT | Trafodion extension that allows users to change the default settings for the current process |
CONTROL QUERY SHAPE | Trafodion extension that allows users to influence the optimizer in picking either a fragment or complete query plan of the user's choice |
Trafodion Limits
Item | Limit Description |
---|---|
Names | Schema, table, columns names are limited to 256 bytes. (However, many UTF8 characters will fit in 256 bytes.) |
Table | No set limit for maximum length for a row |
Indexes | No set limit for combined length of columns in an index key |
Constraints | No set limit for combined length of columns in a PRIMARY KEY |
VARCHAR | Column length of up to 100 KB is supported. |