| --! qt:dataset:srcpart |
| --! qt:dataset:src1 |
| --! qt:dataset:src |
| set hive.mapred.mode=nonstrict; |
| set hive.support.concurrency = true; |
| |
| -- SORT_QUERY_RESULTS |
| |
| SHOW DATABASES; |
| |
| -- CREATE with comment |
| CREATE DATABASE test_db COMMENT 'Hive test database'; |
| SHOW DATABASES; |
| |
| -- CREATE INE already exists |
| CREATE DATABASE IF NOT EXISTS test_db; |
| SHOW DATABASES; |
| |
| -- SHOW DATABASES synonym |
| SHOW SCHEMAS; |
| |
| -- DROP |
| DROP DATABASE test_db; |
| SHOW DATABASES; |
| |
| -- CREATE INE doesn't exist |
| CREATE DATABASE IF NOT EXISTS test_db COMMENT 'Hive test database'; |
| SHOW DATABASES; |
| |
| -- DROP IE exists |
| DROP DATABASE IF EXISTS test_db; |
| SHOW DATABASES; |
| |
| -- DROP IE doesn't exist |
| DROP DATABASE IF EXISTS test_db; |
| |
| -- SHOW |
| CREATE DATABASE test_db; |
| SHOW DATABASES; |
| |
| -- SHOW pattern |
| SHOW DATABASES LIKE 'test%'; |
| |
| -- SHOW pattern |
| SHOW DATABASES LIKE '%ef%'; |
| |
| -- SHOW pattern |
| SHOW DATABASES LIKE 'test_d_'; |
| SHOW DATABASES LIKE 'test__'; |
| |
| USE test_db; |
| SHOW DATABASES; |
| |
| -- CREATE table in non-default DB |
| CREATE TABLE test_table_n2 (col1 STRING) STORED AS TEXTFILE; |
| SHOW TABLES; |
| |
| -- DESCRIBE table in non-default DB |
| DESCRIBE test_table_n2; |
| |
| -- DESCRIBE EXTENDED in non-default DB |
| DESCRIBE EXTENDED test_table_n2; |
| |
| -- CREATE LIKE in non-default DB |
| CREATE TABLE test_table_like LIKE test_table_n2; |
| SHOW TABLES; |
| DESCRIBE EXTENDED test_table_like; |
| |
| -- LOAD and SELECT |
| LOAD DATA LOCAL INPATH '../../data/files/test.dat' |
| OVERWRITE INTO TABLE test_table_n2; |
| SELECT * FROM test_table_n2; |
| |
| -- DROP and CREATE w/o LOAD |
| DROP TABLE test_table_n2; |
| SHOW TABLES; |
| |
| CREATE TABLE test_table_n2 (col1 STRING) STORED AS TEXTFILE; |
| SHOW TABLES; |
| |
| SELECT * FROM test_table_n2; |
| |
| -- CREATE table that already exists in DEFAULT |
| USE test_db; |
| CREATE TABLE src (col1 STRING) STORED AS TEXTFILE; |
| SHOW TABLES; |
| |
| SELECT * FROM src LIMIT 10; |
| |
| USE default; |
| SELECT * FROM src LIMIT 10; |
| |
| -- DROP DATABASE |
| USE test_db; |
| |
| DROP TABLE src; |
| DROP TABLE test_table_n2; |
| DROP TABLE test_table_like; |
| SHOW TABLES; |
| |
| USE default; |
| DROP DATABASE test_db; |
| SHOW DATABASES; |
| |
| -- DROP EMPTY DATABASE CASCADE |
| CREATE DATABASE to_drop_db1; |
| SHOW DATABASES; |
| USE default; |
| DROP DATABASE to_drop_db1 CASCADE; |
| SHOW DATABASES; |
| |
| -- DROP NON-EMPTY DATABASE CASCADE |
| CREATE DATABASE to_drop_db2; |
| SHOW DATABASES; |
| USE to_drop_db2; |
| CREATE TABLE temp_tbl (c STRING); |
| CREATE TABLE temp_tbl2 LIKE temp_tbl; |
| INSERT OVERWRITE TABLE temp_tbl2 SELECT COUNT(*) FROM temp_tbl; |
| USE default; |
| DROP DATABASE to_drop_db2 CASCADE; |
| SHOW DATABASES; |
| |
| -- DROP NON-EMPTY DATABASE CASCADE IF EXISTS |
| CREATE DATABASE to_drop_db3; |
| SHOW DATABASES; |
| USE to_drop_db3; |
| CREATE TABLE temp_tbl (c STRING); |
| USE default; |
| DROP DATABASE IF EXISTS to_drop_db3 CASCADE; |
| SHOW DATABASES; |
| |
| -- DROP NON-EXISTING DATABASE CASCADE IF EXISTS |
| DROP DATABASE IF EXISTS non_exists_db3 CASCADE; |
| SHOW DATABASES; |
| |
| -- DROP NON-EXISTING DATABASE RESTRICT IF EXISTS |
| DROP DATABASE IF EXISTS non_exists_db3 RESTRICT; |
| |
| -- DROP EMPTY DATABASE RESTRICT |
| CREATE DATABASE to_drop_db4; |
| SHOW DATABASES; |
| DROP DATABASE to_drop_db4 RESTRICT; |
| SHOW DATABASES; |
| |
| |
| -- |
| -- Canonical Name Tests |
| -- |
| |
| CREATE DATABASE db1; |
| CREATE DATABASE db2; |
| |
| -- CREATE foreign table |
| CREATE TABLE db1.src(key STRING, value STRING) |
| STORED AS TEXTFILE; |
| |
| -- LOAD into foreign table |
| LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' |
| OVERWRITE INTO TABLE db1.src; |
| |
| -- SELECT from foreign table |
| SELECT * FROM db1.src; |
| |
| -- CREATE Partitioned foreign table |
| CREATE TABLE db1.srcpart(key STRING, value STRING) |
| PARTITIONED BY (ds STRING, hr STRING) |
| STORED AS TEXTFILE; |
| |
| -- LOAD data into Partitioned foreign table |
| LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' |
| OVERWRITE INTO TABLE db1.srcpart |
| PARTITION (ds='2008-04-08', hr='11'); |
| |
| -- SELECT from Partitioned foreign table |
| SELECT key, value FROM db1.srcpart |
| WHERE key < 100 AND ds='2008-04-08' AND hr='11'; |
| |
| -- SELECT JOINed product of two foreign tables |
| USE db2; |
| SELECT a.* FROM db1.src a JOIN default.src1 b |
| ON (a.key = b.key); |
| |
| -- CREATE TABLE AS SELECT from foreign table |
| CREATE TABLE conflict_name AS |
| SELECT value FROM default.src WHERE key = 66; |
| |
| -- CREATE foreign table |
| CREATE TABLE db1.conflict_name AS |
| SELECT value FROM db1.src WHERE key = 8; |
| |
| -- query tables with the same names in different DBs |
| SELECT * FROM ( |
| SELECT value FROM db1.conflict_name |
| UNION ALL |
| SELECT value FROM conflict_name |
| ) subq ORDER BY value; |
| |
| USE default; |
| SELECT * FROM ( |
| SELECT value FROM db1.conflict_name |
| UNION ALL |
| SELECT value FROM db2.conflict_name |
| ) subq; |
| |
| -- TABLESAMPLES |
| CREATE TABLE bucketized_src (key INT, value STRING) |
| CLUSTERED BY (key) SORTED BY (key) INTO 1 BUCKETS; |
| |
| INSERT OVERWRITE TABLE bucketized_src |
| SELECT key, value FROM src WHERE key=66; |
| |
| SELECT key FROM bucketized_src TABLESAMPLE(BUCKET 1 out of 1); |
| |
| -- CREATE TABLE LIKE |
| CREATE TABLE db2.src1 LIKE default.src; |
| |
| USE db2; |
| DESC EXTENDED src1; |
| |
| -- character escaping |
| SELECT key FROM `default`.src ORDER BY key LIMIT 1; |
| SELECT key FROM `default`.`src` ORDER BY key LIMIT 1; |
| SELECT key FROM default.`src` ORDER BY key LIMIT 1; |
| |
| USE default; |