; Mon, 9 Mar 2015 02:35:34 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: svn commit: r1665114 [18/30] - in /tajo/site/docs: 0.10.0/ 0.10.0/_sources/ 0.10.0/_sources/backup_and_restore/ 0.10.0/_sources/configuration/ 0.10.0/_sources/functions/ 0.10.0/_sources/getting_started/ 0.10.0/_sources/index/ 0.10.0/_sources/partitioni... Date: Mon, 09 Mar 2015 02:35:30 -0000 To: commits@tajo.apache.org From: hyunsik@apache.org X-Mailer: svnmailer-1.0.9 Message-Id: <20150309023534.CEFADAC006E@hades.apache.org> Added: tajo/site/docs/0.10.0/sql_language/ddl.html URL: http://svn.apache.org/viewvc/tajo/site/docs/0.10.0/sql_language/ddl.html?rev=1665114&view=auto ============================================================================== --- tajo/site/docs/0.10.0/sql_language/ddl.html (added) +++ tajo/site/docs/0.10.0/sql_language/ddl.html Mon Mar 9 02:35:26 2015 @@ -0,0 +1,350 @@ + + + + + + + + + + Data Definition Language — Apache Tajo 0.8.0 documentation + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +

+ + + + +

+ + + + + + +

+ +

Data Definition LanguageÂ¶

CREATE DATABASEÂ¶

Synopsis

CREATE DATABASE [IF NOT EXISTS] <database_name>
+

+

IF NOT EXISTS allows CREATE DATABASE statement to avoid an error which occurs when the database exists.

DROP DATABASEÂ¶

Synopsis

DROP DATABASE [IF EXISTS] <database_name>
+

+

IF EXISTS allows DROP DATABASE statement to avoid an error which occurs when the database does not exist.

CREATE TABLEÂ¶

Synopsis

CREATE TABLE [IF NOT EXISTS] <table_name> [(<column_name> <data_type>, ... )]
+[using <storage_type> [with (<key> = <value>, ...)]] [AS <select_statement>]
+
+CREATE EXTERNAL TABLE [IF NOT EXISTS] <table_name> (<column_name> <data_type>, ... )
+using <storage_type> [with (<key> = <value>, ...)] LOCATION '<path>'
+

+

IF NOT EXISTS allows CREATE [EXTERNAL] TABLE statement to avoid an error which occurs when the table does not exist.

CompressionÂ¶

If you want to add an external table that contains compressed data, you should give ‘compression.code’ parameter to CREATE TABLE statement.

create EXTERNAL table lineitem (
+L_ORDERKEY bigint,
+L_PARTKEY bigint,
+...
+L_COMMENT text)
+
+USING csv WITH ('text.delimiter'='|','compression.codec'='org.apache.hadoop.io.compress.DeflateCodec')
+LOCATION 'hdfs://localhost:9010/tajo/warehouse/lineitem_100_snappy';
+

+

compression.codec parameter can have one of the following compression codecs:

org.apache.hadoop.io.compress.BZip2Codec
org.apache.hadoop.io.compress.DeflateCodec
org.apache.hadoop.io.compress.GzipCodec
org.apache.hadoop.io.compress.SnappyCodec

DROP TABLEÂ¶

Synopsis

DROP TABLE [IF EXISTS] <table_name> [PURGE]
+

+

IF EXISTS allows DROP DATABASE statement to avoid an error which occurs when the database does not exist. DROP TABLE statement removes a table from Tajo catalog, but it does not remove the contents. If PURGE option is given, DROP TABLE statement will eliminate the entry in the catalog as well as the contents.

CREATE INDEXÂ¶

Synopsis

CREATE INDEX [ name ] ON table_name [ USING method ]
+( { column_name | ( expression ) } [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
+[ WHERE predicate ]
+

+

Index methodÂ¶

Currently, Tajo supports only one type of index.

Index methods:

TWO_LEVEL_BIN_TREE: This method is used by default in Tajo. For more information about its structure, please refer to Index Types.

DROP INDEXÂ¶

Synopsis

DROP INDEX name
+

+

+ + +

+ +

+ + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file Added: tajo/site/docs/0.10.0/sql_language/insert.html URL: http://svn.apache.org/viewvc/tajo/site/docs/0.10.0/sql_language/insert.html?rev=1665114&view=auto ============================================================================== --- tajo/site/docs/0.10.0/sql_language/insert.html (added) +++ tajo/site/docs/0.10.0/sql_language/insert.html Mon Mar 9 02:35:26 2015 @@ -0,0 +1,268 @@ + + + + + + + + + + INSERT (OVERWRITE) INTO — Apache Tajo 0.8.0 documentation + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +

+ + + + +

+ + + + + + +

+ +

INSERT (OVERWRITE) INTOÂ¶

INSERT OVERWRITE statement overwrites a table data of an existing table or a data in a given directory. Tajo’s INSERT OVERWRITE statement follows INSERT INTO SELECT statement of SQL. The examples are as follows:

create table t1 (col1 int8, col2 int4, col3 float8);
+
+-- when a target table schema and output schema are equivalent to each other
+INSERT OVERWRITE INTO t1 SELECT l_orderkey, l_partkey, l_quantity FROM lineitem;
+-- or
+INSERT OVERWRITE INTO t1 SELECT * FROM lineitem;
+
+-- when the output schema are smaller than the target table schema
+INSERT OVERWRITE INTO t1 SELECT l_orderkey FROM lineitem;
+
+-- when you want to specify certain target columns
+INSERT OVERWRITE INTO t1 (col1, col3) SELECT l_orderkey, l_quantity FROM lineitem;
+

+

In addition, INSERT OVERWRITE statement overwrites table data as well as a specific directory.

INSERT OVERWRITE INTO LOCATION '/dir/subdir' SELECT l_orderkey, l_quantity FROM lineitem;
+

+

+ + +

+ +

+ + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file Added: tajo/site/docs/0.10.0/sql_language/predicates.html URL: http://svn.apache.org/viewvc/tajo/site/docs/0.10.0/sql_language/predicates.html?rev=1665114&view=auto ============================================================================== --- tajo/site/docs/0.10.0/sql_language/predicates.html (added) +++ tajo/site/docs/0.10.0/sql_language/predicates.html Mon Mar 9 02:35:26 2015 @@ -0,0 +1,405 @@ + + + + + + + + + + Predicates — Apache Tajo 0.8.0 documentation + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +

+ + + + +

+ + + + + + +

+ +

PredicatesÂ¶

IN PredicateÂ¶

IN predicate provides row and array comparison.

Synopsis

column_reference IN (val1, val2, ..., valN)
+column_reference NOT IN (val1, val2, ..., valN)
+

+

Examples are as follows:

-- this statement filters lists down all the records where col1 value is 1, 2 or 3:
+SELECT col1, col2 FROM table1 WHERE col1 IN (1, 2, 3);
+
+-- this statement filters lists down all the records where col1 value is neither 1, 2 nor 3:
+SELECT col1, col2 FROM table1 WHERE col1 NOT IN (1, 2, 3);
+

+

You can use ‘IN clause’ on text data domain as follows:

SELECT col1, col2 FROM table1 WHERE col2 IN ('tajo', 'hadoop');
+
+SELECT col1, col2 FROM table1 WHERE col2 NOT IN ('tajo', 'hadoop');
+

+

String Pattern Matching PredicatesÂ¶

LIKEÂ¶

LIKE operator returns true or false depending on whether its pattern matches the given string. An underscore (_) in pattern matches any single character. A percent sign (%) matches any sequence of zero or more characters.

Synopsis

string LIKE pattern
+string NOT LIKE pattern
+

+

ILIKEÂ¶

ILIKE is the same to LIKE, but it is a case insensitive operator. It is not in the SQL standard. We borrow this operator from PostgreSQL.

Synopsis

string ILIKE pattern
+string NOT ILIKE pattern
+

+

SIMILAR TOÂ¶

Synopsis

string SIMILAR TO pattern
+string NOT SIMILAR TO pattern
+

+

It returns true or false depending on whether its pattern matches the given string. Also like LIKE, SIMILAR TO uses _ and % as metacharacters denoting any single character and any string, respectively.

In addition to these metacharacters borrowed from LIKE, ‘SIMILAR TO’ supports more powerful pattern-matching metacharacters borrowed from regular expressions:

+ ++++ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +

metacharacter	description
\|	denotes alternation (either of two alternatives).
+ + +	denotes repetition of the previous item zero or more times.
+ + +	denotes repetition of the previous item one or more times.
?	denotes repetition of the previous item zero or one time.
{m}	denotes repetition of the previous item exactly m times.
{m,}	denotes repetition of the previous item m or more times.
{m,n}	denotes repetition of the previous item at least m and not more than n times.
[]	A bracket expression specifies a character class, just as in POSIX regular expressions.
()	Parentheses can be used to group items into a single logical item.

Note that .` is not used as a metacharacter in SIMILAR TO operator.

Regular expressionsÂ¶

Regular expressions provide a very powerful means for string pattern matching. In the current Tajo, regular expressions are based on Java-style regular expressions instead of POSIX regular expression. The main difference between java-style one and POSIX’s one is character class.

Synopsis

string ~ pattern
+string !~ pattern
+
+string ~* pattern
+string !~* pattern
+

+

+ ++++ + + + + + + + + + + + + + + + + + + + +

operator	Description
~	It returns true if a given regular expression is matched to string. Otherwise, it returns false.
!~	It returns false if a given regular expression is matched to string. Otherwise, it returns true.
~*	It is the same to ‘~’, but it is case insensitive.
!~*	It is the same to ‘!~’, but it is case insensitive.

Here are examples:

'abc'   ~   '.*c'               true
+'abc'   ~   'c'                 false
+'aaabc' ~   '([a-z]){3}bc       true
+'abc'   ~*  '.*C'               true
+'abc'   !~* 'B.*'               true
+

+

Regular expressions operator is not in the SQL standard. We borrow this operator from PostgreSQL.

Synopsis for REGEXP and RLIKE operators

string REGEXP pattern
+string NOT REGEXP pattern
+
+string RLIKE pattern
+string NOT RLIKE pattern
+

+

But, they do not support case-insensitive operators.

+ + +

+ +

+ + + + + + + + + + + + + + + + + + + + + + + + \ No newline at end of file