| set hive.mapred.mode=nonstrict; |
| |
| dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}; |
| dfs -copyFromLocal ../../data/files/header_footer_table_4 ${system:test.tmp.dir}/header_footer_table_4; |
| |
| CREATE TABLE numbrs (numbr int); |
| INSERT INTO numbrs VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (NULL); |
| CREATE EXTERNAL TABLE header_footer_table_4 (header_int int, header_name string, header_choice varchar(10)) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '${system:test.tmp.dir}/header_footer_table_4' tblproperties ("skip.header.line.count"="1", "skip.footer.line.count"="2"); |
| |
| SELECT * FROM header_footer_table_4; |
| |
| SELECT * FROM header_footer_table_4 ORDER BY header_int LIMIT 8; |
| |
| -- should return nothing as title is correctly skipped |
| SELECT * FROM header_footer_table_4 WHERE header_choice = 'header_choice'; |
| SELECT * FROM header_footer_table_4 WHERE header_choice = 'monthly'; |
| SELECT COUNT(*) FROM header_footer_table_4; |
| |
| -- shpuld return nothing |
| SELECT * FROM header_footer_table_4 WHERE header_choice is NULL; |
| |
| |
| SELECT AVG(header_int) FROM header_footer_table_4 GROUP BY header_choice; |
| |
| -- should not include any header and footer |
| SELECT * FROM header_footer_table_4 A, header_footer_table_4 B ORDER BY A.header_int, B.header_int; |
| |
| -- no join variant should include header or footer |
| SELECT header_name, header_int FROM header_footer_table_4 LEFT JOIN numbrs ON numbr = header_int; |
| SELECT header_name, header_int FROM header_footer_table_4 RIGHT JOIN numbrs ON numbr = header_int; |
| SELECT header_name, header_int FROM header_footer_table_4 INNER JOIN numbrs ON numbr = header_int; |
| SELECT header_name, header_int FROM header_footer_table_4 FULL JOIN numbrs ON numbr = header_int; |
| |
| |
| SELECT header_choice, SUM(header_int) FROM header_footer_table_4 GROUP BY header_choice; |
| |
| |
| SELECT DISTINCT header_choice, SUM(header_int) FROM header_footer_table_4 GROUP BY header_choice; |
| |
| |
| SELECT header_name, header_choice FROM header_footer_table_4 ORDER BY header_int LIMIT 8; |
| |
| -- create table from existing text file should not feed header or footer to the new table |
| CREATE TABLE transition (title VARCHAR(10), name VARCHAR(10)) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '${system:test.tmp.dir}/header_footer_table_transition'; |
| INSERT INTO transition SELECT header_choice, header_name FROM header_footer_table_4; |
| SELECT * FROM transition A, transition B ORDER BY A.title, A.name, B.title, B.name; |
| |
| CREATE TABLE transition2 (header_choice VARCHAR(10), sum_header_int int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '${system:test.tmp.dir}/test/header_footer_table_transition2'; |
| INSERT INTO transition2 SELECT header_choice, SUM(header_int) FROM header_footer_table_4 GROUP BY header_choice; |
| SELECT * FROM transition2 A, transition2 B ORDER BY A.sum_header_int, A.header_choice, B.sum_header_int, B.header_choice; |
| |
| DROP TABLE transition; |
| DROP TABLE transition2; |
| |
| -- turn off fetch conversion. This disables the additional header/footer handling logic in fetch operator. |
| set hive.fetch.task.conversion=none; |
| |
| SELECT * FROM header_footer_table_4; |
| |
| SELECT * FROM header_footer_table_4 ORDER BY header_int LIMIT 8; |
| |
| -- should return nothing as title is correctly skipped |
| SELECT * FROM header_footer_table_4 WHERE header_choice = 'header_choice'; |
| SELECT * FROM header_footer_table_4 WHERE header_choice = 'monthly'; |
| SELECT COUNT(*) FROM header_footer_table_4; |
| |
| -- shpuld return nothing |
| SELECT * FROM header_footer_table_4 WHERE header_choice is NULL; |
| |
| |
| SELECT AVG(header_int) FROM header_footer_table_4 GROUP BY header_choice; |
| |
| -- should not include any header and footer |
| SELECT * FROM header_footer_table_4 A, header_footer_table_4 B ORDER BY A.header_int, B.header_int; |
| |
| -- no join variant should include header or footer |
| SELECT header_name, header_int FROM header_footer_table_4 LEFT JOIN numbrs ON numbr = header_int; |
| SELECT header_name, header_int FROM header_footer_table_4 RIGHT JOIN numbrs ON numbr = header_int; |
| SELECT header_name, header_int FROM header_footer_table_4 INNER JOIN numbrs ON numbr = header_int; |
| SELECT header_name, header_int FROM header_footer_table_4 FULL JOIN numbrs ON numbr = header_int; |
| |
| |
| SELECT header_choice, SUM(header_int) FROM header_footer_table_4 GROUP BY header_choice; |
| |
| |
| SELECT DISTINCT header_choice, SUM(header_int) FROM header_footer_table_4 GROUP BY header_choice; |
| |
| |
| SELECT header_name, header_choice FROM header_footer_table_4 ORDER BY header_int LIMIT 8; |
| |
| -- create table from existing text file should not feed header or footer to the new table |
| CREATE TABLE transition (title VARCHAR(10), name VARCHAR(10)) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '${system:test.tmp.dir}/header_footer_table_transition'; |
| INSERT INTO transition SELECT header_choice, header_name FROM header_footer_table_4; |
| SELECT * FROM transition A, transition B ORDER BY A.title, A.name, B.title, B.name; |
| |
| CREATE TABLE transition2 (header_choice VARCHAR(10), sum_header_int int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '${system:test.tmp.dir}/test/header_footer_table_transition2'; |
| INSERT INTO transition2 SELECT header_choice, SUM(header_int) FROM header_footer_table_4 GROUP BY header_choice; |
| SELECT * FROM transition2 A, transition2 B ORDER BY A.sum_header_int, A.header_choice, B.sum_header_int, B.header_choice; |