Google Git
Sign in
apache / hive / c23958bee3fc22a063bb4268824c037028a35fb1 / . / ql / src / test / results / clientpositive / llap / merge_test_dummy_operator.q.out
blob: 22c64a16defea6ff01f2c1fae26afbb2fe64091d [file] [log] [blame]
PREHOOK: query: create table common_join_table (id string,
col1 string,
date_created date,
col2 string,
col3 string,
time_stamp timestamp,
col4 date,
col4key bigint,
col5 date,
col6 string,
col7 string,
col8 smallint)
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@common_join_table
POSTHOOK: query: create table common_join_table (id string,
col1 string,
date_created date,
col2 string,
col3 string,
time_stamp timestamp,
col4 date,
col4key bigint,
col5 date,
col6 string,
col7 string,
col8 smallint)
POSTHOOK: type: CREATETABLE
POSTHOOK: Output: database:default
POSTHOOK: Output: default@common_join_table
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
PREHOOK: type: QUERY
PREHOOK: Input: _dummy_database@_dummy_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: insert into common_join_table values ('id', '109515', null, 'test', 'test', '2018-01-10 15:03:55.0', '2018-01-10', 109515, null, '45045501', 'id', null)
POSTHOOK: type: QUERY
POSTHOOK: Input: _dummy_database@_dummy_table
POSTHOOK: Output: default@common_join_table
POSTHOOK: Lineage: common_join_table.col1 SCRIPT []
POSTHOOK: Lineage: common_join_table.col2 SCRIPT []
POSTHOOK: Lineage: common_join_table.col3 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4 SCRIPT []
POSTHOOK: Lineage: common_join_table.col4key SCRIPT []
POSTHOOK: Lineage: common_join_table.col5 EXPRESSION []
POSTHOOK: Lineage: common_join_table.col6 SCRIPT []
POSTHOOK: Lineage: common_join_table.col7 SCRIPT []
POSTHOOK: Lineage: common_join_table.col8 EXPRESSION []
POSTHOOK: Lineage: common_join_table.date_created EXPRESSION []
POSTHOOK: Lineage: common_join_table.id SCRIPT []
POSTHOOK: Lineage: common_join_table.time_stamp SCRIPT []
PREHOOK: query: WITH temp_tbl_1 AS (
SELECT col7
,col4KEY
,COUNT(*) AS temp_result_1
FROM common_join_table
GROUP BY col7, col4KEY
),
temp_tbl_2 AS (
SELECT col7
,col4KEY
,temp_result_1
,ROW_NUMBER() OVER(PARTITION BY col7 ORDER BY col4KEY ASC) AS temp_result_2
FROM temp_tbl_1
),
temp_tbl_3 AS (
SELECT col7
,MIN(col4KEY) AS START_DATE
,MAX(col4KEY) AS END_DATE
FROM temp_tbl_2
GROUP BY col7
),
temp_tbl_4 AS (
SELECT D1.col7
,D1.col4KEY
,D1.temp_result_2
,D1.temp_result_1
,CASE WHEN D2.col4KEY-D1.col4KEY > 30 THEN D1.col4KEY
WHEN D1.col4KEY = M.END_DATE THEN D1.col4KEY ELSE 0 END AS temp_result_3
,CASE WHEN D2.col4KEY-D1.col4KEY > 30 THEN D2.col4KEY
WHEN D1.col4KEY = M.START_DATE THEN D1.col4KEY ELSE 0 END AS temp_result_4
FROM temp_tbl_2 D1
INNER JOIN temp_tbl_3 M
ON D1.col7 = M.col7
LEFT JOIN temp_tbl_2 D2
ON D1.col7 = D2.col7
AND D1.temp_result_2 = D2.temp_result_2+1
),
temp_tbl_5 AS (
SELECT S1.col7
,S1.col4KEY
,S1.temp_result_2
,S1.temp_result_1
,CASE WHEN S1.col4KEY >= S2.temp_result_4
AND S1.col4KEY <= S3.temp_result_3
THEN 1 ELSE 0 END AS temp_result_5
FROM temp_tbl_4 S1
LEFT JOIN temp_tbl_4 S2
ON S1.col7 = S2.col7
AND S2.temp_result_4 != 0
LEFT JOIN temp_tbl_4 S3
ON S1.col7 = S3.col7
AND S3.temp_result_3 != 0
),
temp_tbl_6 AS (
SELECT col7
,col4KEY
,temp_result_2
,temp_result_1
,SUM(temp_result_5) AS temp_result_5
FROM temp_tbl_5
GROUP BY col7
,col4KEY
,temp_result_2
,temp_result_1
),
temp_tbl_7 AS (
SELECT col7
,SUM(temp_result_2) AS temp_result_6
,SUM(temp_result_1) AS temp_result_1
FROM temp_tbl_6
GROUP BY col7
)
SELECT S.*
FROM temp_tbl_6 S
INNER JOIN
temp_tbl_7 F
ON S.col7 = F.col7
--WHERE F.temp_result_6 < 40
--AND F.temp_result_1 < 200
PREHOOK: type: QUERY
PREHOOK: Input: default@common_join_table
PREHOOK: Output: hdfs://### HDFS PATH ###
POSTHOOK: query: WITH temp_tbl_1 AS (
SELECT col7
,col4KEY
,COUNT(*) AS temp_result_1
FROM common_join_table
GROUP BY col7, col4KEY
),
temp_tbl_2 AS (
SELECT col7
,col4KEY
,temp_result_1
,ROW_NUMBER() OVER(PARTITION BY col7 ORDER BY col4KEY ASC) AS temp_result_2
FROM temp_tbl_1
),
temp_tbl_3 AS (
SELECT col7
,MIN(col4KEY) AS START_DATE
,MAX(col4KEY) AS END_DATE
FROM temp_tbl_2
GROUP BY col7
),
temp_tbl_4 AS (
SELECT D1.col7
,D1.col4KEY
,D1.temp_result_2
,D1.temp_result_1
,CASE WHEN D2.col4KEY-D1.col4KEY > 30 THEN D1.col4KEY
WHEN D1.col4KEY = M.END_DATE THEN D1.col4KEY ELSE 0 END AS temp_result_3
,CASE WHEN D2.col4KEY-D1.col4KEY > 30 THEN D2.col4KEY
WHEN D1.col4KEY = M.START_DATE THEN D1.col4KEY ELSE 0 END AS temp_result_4
FROM temp_tbl_2 D1
INNER JOIN temp_tbl_3 M
ON D1.col7 = M.col7
LEFT JOIN temp_tbl_2 D2
ON D1.col7 = D2.col7
AND D1.temp_result_2 = D2.temp_result_2+1
),
temp_tbl_5 AS (
SELECT S1.col7
,S1.col4KEY
,S1.temp_result_2
,S1.temp_result_1
,CASE WHEN S1.col4KEY >= S2.temp_result_4
AND S1.col4KEY <= S3.temp_result_3
THEN 1 ELSE 0 END AS temp_result_5
FROM temp_tbl_4 S1
LEFT JOIN temp_tbl_4 S2
ON S1.col7 = S2.col7
AND S2.temp_result_4 != 0
LEFT JOIN temp_tbl_4 S3
ON S1.col7 = S3.col7
AND S3.temp_result_3 != 0
),
temp_tbl_6 AS (
SELECT col7
,col4KEY
,temp_result_2
,temp_result_1
,SUM(temp_result_5) AS temp_result_5
FROM temp_tbl_5
GROUP BY col7
,col4KEY
,temp_result_2
,temp_result_1
),
temp_tbl_7 AS (
SELECT col7
,SUM(temp_result_2) AS temp_result_6
,SUM(temp_result_1) AS temp_result_1
FROM temp_tbl_6
GROUP BY col7
)
SELECT S.*
FROM temp_tbl_6 S
INNER JOIN
temp_tbl_7 F
ON S.col7 = F.col7
--WHERE F.temp_result_6 < 40
--AND F.temp_result_1 < 200
POSTHOOK: type: QUERY
POSTHOOK: Input: default@common_join_table
POSTHOOK: Output: hdfs://### HDFS PATH ###
id 109515 1 10 1
PREHOOK: query: drop table common_join_table
PREHOOK: type: DROPTABLE
PREHOOK: Input: default@common_join_table
PREHOOK: Output: default@common_join_table
POSTHOOK: query: drop table common_join_table
POSTHOOK: type: DROPTABLE
POSTHOOK: Input: default@common_join_table
POSTHOOK: Output: default@common_join_table