THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
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. |