hive> CREATE TABLE pokes (foo INT, bar STRING);  

Creates a table called pokes with two columns, the first being an integer and the other a string

  hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);  

Creates a table called invites with two columns and a partition column called ds. The partition column is a virtual column. It is not part of the data itself but is derived from the partition that a particular dataset is loaded into.

By default, tables are assumed to be of text input format and the delimiters are assumed to be ^A(ctrl-a).

  hive> SHOW TABLES;

lists all the tables

  hive> SHOW TABLES '.*s';

lists all the table that end with ‘s’. The pattern matching follows Java regular expressions. Check out this link for documentation http://java.sun.com/javase/6/docs/api/java/util/regex/Pattern.html

hive> DESCRIBE invites;

shows the list of columns

As for altering tables, table names can be changed and additional columns can be dropped:

  hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);

  hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

  hive> ALTER TABLE events RENAME TO 3koobecaf;

Dropping tables:

  hive> DROP TABLE pokes;

Loading data from flat files into Hive:

  hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes; 

Loads a file that contains two columns separated by ctrl-a into pokes table. ‘local’ signifies that the input file is on the local file system. If ‘local’ is omitted then it looks for the file in HDFS.

The keyword ‘overwrite’ signifies that existing data in the table is deleted. If the ‘overwrite’ keyword is omitted, data files are appended to existing data sets.

NOTES:

• NO verification of data against the schema is performed by the load command.

• If the file is in hdfs, it is moved into the Hive-controlled file system namespace. The root of the Hive directory is specified by the option ‘hive.metastore.warehouse.dir’ in hive-default.xml. We advise users to create this directory before trying to create tables via Hive.

  hive> LOAD DATA LOCAL INPATH './examples/files/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

  hive> LOAD DATA LOCAL INPATH './examples/files/kv3.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-08');

The two LOAD statements above load data into two different partitions of the table invites. Table invites must be created as partitioned by the key ds for this to succeed.

SELECTS and FILTERS

  hive> SELECT a.foo FROM invites a WHERE a.ds='<DATE>';

selects column ‘foo’ from all rows of partition <DATE> of invites table. The results are not stored anywhere, but are displayed on the console.

Note that in all the examples that follow, INSERT (into a hive table, local directory or HDFS directory) is optional.

  hive> INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>';

selects all rows from partition <DATE> OF invites table into an HDFS directory. The result data is in files (depending on the number of mappers) in that directory. NOTE: partition columns if any are selected by the use of *. They can also be specified in the projection clauses.

Partitioned tables must always have a partition selected in the WHERE clause of the statement.

  hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;

Selects all rows from pokes table into a local directory

  hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;

  hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100; 

  hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;

  hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_4' select a.invites, a.pokes FROM profiles a;

  hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT COUNT(1) FROM invites a WHERE a.ds='<DATE>';

  hive> INSERT OVERWRITE DIRECTORY '/tmp/reg_5' SELECT a.foo, a.bar FROM invites a;

  hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/sum' SELECT SUM(a.pc) FROM pc1 a;

Sum of a column. avg, min, max can also be used

CREATE TABLE table1 ( c1 VARCHAR, c2 INT) COLUMN SEP ‘|’
This creates an empty table- table1 with 2 columns- c1, c2 having VARCHAR and INT datatypes respectively. The column sep (‘|’) indicates the files that will be added into this table will have ‘|’ as the delimiter.

CREATE TABLE table1 ( c1 VARCHAR, c2 INT) COLUMN SEP ‘|’ HDFS PATH ‘logs/weblogs’
This creates a table- table1 and attach this name with the path- ‘logs/weblogs’ on Hadoop File System. If this path exists and has files then those files will constigute the table data. The column sep indicates that the ‘|’ char is used as delimter char in these files. If the path does not exist, it will be created. In that case the table won’t have any data, i.e. select query on this table will return 0 rows.

CREATE TABLE table1 ( c1 VARCHAR, c2 FLOAT) COLUMN SEP ‘t’ IMPORT DATA FROM ‘/home/logs/weblogs’
This creates table- table1 and copy data from the given local path. TAB (‘t’) is used as a delimter char. The data is loaded on Hadoop File Sysem in the directory- cloudbase/data/<tablename>

CREATE XML TABLE <tablename>
( <column name> <data type> ‘XJPath’ [, <column name> <data type> ‘XJPath’ , …] )
START WITH ‘start_tag’ END WITH ‘end_tag’
[ HDFS PATH <hdfs path> ]
[ IMPORT DATA FROM <local path> ]
[ COMMENT <string> ]

If the root tag has no attribute, one can simply use the ROOT TAG clause. The START TAG along with END TAG is preferred only when xml root tag has attributes.

The input log file/data can be in any format as long as XML records can be identified by start and end tags. For example,

some text

<book> xml data record 1</book>

some text

<book> xml data record 1</book>






Drop table syntax-


DROP TABLE tablename [ DO NOT PURGE DATA ]

INSERT statement is used to insert data-

• Into an already existent CloudBase table from local files.
• Into an already existent CloudBase table from another CloudBase table.
• Into a RDBMS Table (via a database link).

Inserting data into CloudBase table from local files
INSERT INTO <tablename> IMPORT DATA FROM ‘local/path’

The local files should have the same structure as defined in table’s meta data, i.e. local files should have the same delimiter, same number of fields as defined in table’s meta data.

Inserting data into CloudBase table from an existent table
INSERT INTO <tablename>
[ ( column_name1, column_name2, …) ]
<query>

Examples:
INSERT INTO table2
SELECT c1, c2, c3 FROM table1
This will dump the output of query into table2

INSERT INTO table2
( col2, col1, col3 )
SELECT c1, c2, c3 FROM table1
This will dump the output of query into table2 in the specified column order.
If insufficient columns are specified,then INSERT statement will insert NULL values for the remaining columns.

Inserting data into an external (RDBMS) table
INSERT INTO <tablename>@database_link_name
[ ( column_name1, column_name2, …) ]
<query>

Examples:
INSERT INTO table2@sql_server_link
SELECT c1, c2, c3 FROM table1
This will dump the output of query into table2 in the RDBMS as specified by the database link- sql_server_link

If insufficient columns are specified,then INSERT statement will insert NULL values for the remaining columns.

Select statement syntax
The syntax of SELECT statement is as follows-

SELECT [ DISTINCT ] [ TOP <number> ]

column1 [ , colum2, column3, … ]

[ into_clause ]

FROM <from_list>

[ join_clause ]

[ where_clause ]

[ group_by_clause [ having_clause ] ]

[ order_by_clause ]

[ on error clause ]

Clauses present in ‘[ ]’ indicate that they are optional

FROM clause
One can use tables, views and/or sub queries in the from-clause. Aliases can be optionally set for table names and views. However, setting aliases for sub queries is mandatory.

Examples:
• SELECT * FROM test_table1
• SELECT * FROM ( SELECT c1, c2 FROM test_table1) AS T
• SELECT DISTINCT c1 FROM test_table1

JOIN clause
CloudBase supports inner and outer (left, right, full) joins via ANSI SQL’s explicit join syntax-
• SELECT * FROM test_table1 AS t1 INNER JOIN test_table2 AS t2 ON t1.c1 = t2.c1

Join Syntax-
• SELECT * FROM test_table1 AS t1 [ OUTER ] LEFT | RIGHT | FULL JOIN test_table2 AS t2 ON <join condition>

Sub queries can also be used instead of tables/views –
• SELECT * FROM test_table1 AS t1 INNER JOIN (SELECT * FROM test_table2) AS t2 ON t1.c1 = t2.c1

WHERE clause
Rows returned by queries can be filtered by using Where clause. At present CloudBase supports the following conditions in the Where Clause-

• Arithmetic condition- compare columns using arithmetic operators- <, >, =, <=, >=, !=, <> (either of !=, <> can be used for not equal to)
• Between condition- e.g. WHERE c1 BETWEEN 10 and 100. NOT can also be used with BETWEEN- WHERE c1 NOT BETWEEN 10 and 100
• IS NULL condition- e.g. WHERE c1 IS NULL. NOT can also be used- WHERE ci is NOT NULL
• Like condition- e.g. WHERE c1 LIKE ‘%abc%’. NOT can also be used- WHERE c1 NOT LIKE ‘%abc%’. To match on a single character use ‘?’. One can use regular expressions in LIKE clause. See below for details.

Boolean AND / OR can be used to tie conditions together, e.g-
• SELECT * FROM test_table1 WHERE c1 LIKE ‘z%’ AND c2 > 10
• SELECT * FROM test_table1 WHERE c2 > 40 OR c3 < 20

CloudBase supports SQL wild cards- ‘%’ and ‘?’ in LIKE conditions. For conditions that can not be expressed using these wild cards, one can use regular expressions. CloudBase supports regular expression similar to Java programming language. One can read about Java regular expressions here

Examples:
• SELECT c6 FROM test_table3 where c6 like ‘b[ea]ta’
   matches ‘beta’, ‘bata’

• SELECT * FROM test_table3 WHERE c like ‘(l|L)earning’
   matches ‘learning’, ‘Learning’

• SELECT c FROM test_table WHERE c LIKE ‘(z|Z)o{3}+m’
   matches all values of column c that begins with z or Z followed by EXACTLY 3 o’s and then m

• SELECT c FROM test_table WHERE c LIKE ‘(z|Z)o{3,}+m’
   matches all values of column c that begins with z or Z followed by AT LEAST 3 o’s and then m

• SELECT c FROM test_table WHERE c LIKE ‘(z|Z)o{3,}+m’
   matches all values of column c that begins with z or Z followed by AT LEAST 3 o’s and then m

• SELECT c FROM test_table WHERE c LIKE ‘(z|Z)o{3,6}+m’
   matches all values of column c that begins with z or Z followed by AT LEAST 3 but NOT MORE THAN 6 o’s and then m

To escape regular expression construct, use ‘’. For example-
• SELECT c FROM test_table WHERE c LIKE ‘[abc]’
   matches literal string- ‘[abc]’

Aggregate functions and GROUP BY clause
CloudBase supports aggregate functions and group by clause. The aggregate functions supported by CloudBase are- SUM, COUNT, MAX, MIN, and AVG. One can use GROUP BY cluase in conjunction with the aggregate functions to group the result-set by one or more columns. In Group by clause, one can use column names, alias or index of column present in the select clause. e.g.-

Examples:
• SELECT c4, COUNT(*) FROM test_table1 GROUP BY c4
• SELECT c1, SUM(c2) FROM test_table1 GROUP BY c1
• SELECT c1 as a, SUM(c2) FROM test_table1 GROUP BY a
• SELECT c1, c4, SUM(c2) FROM test_table1 GROUP BY 1,2
• SELECT COUNT(c1),MAX(c2),MIN(c3), c4 FROM test_table1 GROUP BY c4

DISTINCT in Aggregate functions
• SELECT COUNT( DISTINCT c1), MAX(c2) FROM test_table1
• SELECT COUNT( DISTINCT c1), MAX(c2), MIN(c3), COUNT(DISTINCT c4) FROM test_table1

DISTINCT in Aggregate functions with GROUP BY
• SELECT COUNT( DISTINCT c1), COUNT( DISTINCT c2), COUNT( DISTINCT c3), COUNT( DISTINCT c4), MAX(c2), MIN(c2), MAX(c3), MIN(c3), c4 FROM test_table1 GROUP BY c4

HAVING clause with GROUP BY
• SELECT COUNT(c1) cnt_c1, COUNT( DISTINCT c2) cnt_d_c1, SUM(c2) sum_c2, SUM(c3) sum_c3, c4 FROM test_table1 GROUP BY c4 HAVING cnt_d_c1 > 2 AND sum_c3 > 100

• SELECT COUNT(c1) cnt_c1, MAX(c4) max_c4, MIN(c4) min_c4, MAX(c5) max_c5, MIN(c5) min_c5, SUM(c4) sum_c4, SUM(c5) sum_c5, COUNT(DISTINCT c1), SUM(DISTINCT c4), c6 FROM test_table3 GROUP BY c6 HAVING c6 LIKE ‘%p%’

ORDER BY clause
ORDER BY clause can be used to sort result set on one or more columns. One can sort in ascending order (default behavior) or in descending order. Just like Group By clause, one can use column name, alias or column index in the ORDER BY clause.

Examples:
• SELECT c1, c2, c3, c4 FROM test_table1 ORDER BY c1
• SELECT c1 as a, c2 as b, c3 as c, c4 as d FROM test_table1 ORDER BY a, b
• SELECT c1, c2, c3, c4 FROM test_table1 ORDER BY 3
• SELECT * FROM test_table1 ORDER BY c4 DESC, c2