PageRenderTime 54ms CodeModel.GetById 20ms RepoModel.GetById 1ms app.codeStats 0ms

/tags/release-0.0.0-rc0/hive/external/ql/src/test/results/clientpositive/auto_join7.q.out

#
text | 295 lines | 289 code | 6 blank | 0 comment | 0 complexity | aeac7dd675771e412d943243f974c088 MD5 | raw file
Possible License(s): Apache-2.0, BSD-3-Clause, JSON, CPL-1.0
  1. PREHOOK: query: CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING, c5 INT, c6 STRING) STORED AS TEXTFILE
  2. PREHOOK: type: CREATETABLE
  3. POSTHOOK: query: CREATE TABLE dest1(c1 INT, c2 STRING, c3 INT, c4 STRING, c5 INT, c6 STRING) STORED AS TEXTFILE
  4. POSTHOOK: type: CREATETABLE
  5. POSTHOOK: Output: default@dest1
  6. PREHOOK: query: explain
  7. FROM (
  8. FROM
  9. (
  10. FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
  11. ) a
  12. FULL OUTER JOIN
  13. (
  14. FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
  15. ) b
  16. ON (a.c1 = b.c3)
  17. LEFT OUTER JOIN
  18. (
  19. FROM src src3 SELECT src3.key AS c5, src3.value AS c6 WHERE src3.key > 20 and src3.key < 25
  20. ) c
  21. ON (a.c1 = c.c5)
  22. SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6
  23. ) c
  24. INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6
  25. PREHOOK: type: QUERY
  26. POSTHOOK: query: explain
  27. FROM (
  28. FROM
  29. (
  30. FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
  31. ) a
  32. FULL OUTER JOIN
  33. (
  34. FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
  35. ) b
  36. ON (a.c1 = b.c3)
  37. LEFT OUTER JOIN
  38. (
  39. FROM src src3 SELECT src3.key AS c5, src3.value AS c6 WHERE src3.key > 20 and src3.key < 25
  40. ) c
  41. ON (a.c1 = c.c5)
  42. SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6
  43. ) c
  44. INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6
  45. POSTHOOK: type: QUERY
  46. ABSTRACT SYNTAX TREE:
  47. (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_FULLOUTERJOIN (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) src1)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src1) key) c1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src1) value) c2)) (TOK_WHERE (and (> (. (TOK_TABLE_OR_COL src1) key) 10) (< (. (TOK_TABLE_OR_COL src1) key) 20))))) a) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) src2)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src2) key) c3) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src2) value) c4)) (TOK_WHERE (and (> (. (TOK_TABLE_OR_COL src2) key) 15) (< (. (TOK_TABLE_OR_COL src2) key) 25))))) b) (= (. (TOK_TABLE_OR_COL a) c1) (. (TOK_TABLE_OR_COL b) c3))) (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME src) src3)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL src3) key) c5) (TOK_SELEXPR (. (TOK_TABLE_OR_COL src3) value) c6)) (TOK_WHERE (and (> (. (TOK_TABLE_OR_COL src3) key) 20) (< (. (TOK_TABLE_OR_COL src3) key) 25))))) c) (= (. (TOK_TABLE_OR_COL a) c1) (. (TOK_TABLE_OR_COL c) c5)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) c1) c1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL a) c2) c2) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) c3) c3) (TOK_SELEXPR (. (TOK_TABLE_OR_COL b) c4) c4) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c5) c5) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c6) c6)))) c)) (TOK_INSERT (TOK_DESTINATION (TOK_TAB (TOK_TABNAME dest1))) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c1)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c2)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c3)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c4)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c5)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) c6)))))
  48. STAGE DEPENDENCIES:
  49. Stage-1 is a root stage
  50. Stage-0 depends on stages: Stage-1
  51. Stage-2 depends on stages: Stage-0
  52. STAGE PLANS:
  53. Stage: Stage-1
  54. Map Reduce
  55. Alias -> Map Operator Tree:
  56. c:a:src1
  57. TableScan
  58. alias: src1
  59. Filter Operator
  60. predicate:
  61. expr: ((key > 10) and (key < 20))
  62. type: boolean
  63. Filter Operator
  64. predicate:
  65. expr: ((key > 10) and (key < 20))
  66. type: boolean
  67. Select Operator
  68. expressions:
  69. expr: key
  70. type: string
  71. expr: value
  72. type: string
  73. outputColumnNames: _col0, _col1
  74. Reduce Output Operator
  75. key expressions:
  76. expr: _col0
  77. type: string
  78. sort order: +
  79. Map-reduce partition columns:
  80. expr: _col0
  81. type: string
  82. tag: 0
  83. value expressions:
  84. expr: _col0
  85. type: string
  86. expr: _col1
  87. type: string
  88. c:b:src2
  89. TableScan
  90. alias: src2
  91. Filter Operator
  92. predicate:
  93. expr: ((key > 15) and (key < 25))
  94. type: boolean
  95. Filter Operator
  96. predicate:
  97. expr: ((key > 15) and (key < 25))
  98. type: boolean
  99. Select Operator
  100. expressions:
  101. expr: key
  102. type: string
  103. expr: value
  104. type: string
  105. outputColumnNames: _col0, _col1
  106. Reduce Output Operator
  107. key expressions:
  108. expr: _col0
  109. type: string
  110. sort order: +
  111. Map-reduce partition columns:
  112. expr: _col0
  113. type: string
  114. tag: 1
  115. value expressions:
  116. expr: _col0
  117. type: string
  118. expr: _col1
  119. type: string
  120. c:c:src3
  121. TableScan
  122. alias: src3
  123. Filter Operator
  124. predicate:
  125. expr: ((key > 20) and (key < 25))
  126. type: boolean
  127. Filter Operator
  128. predicate:
  129. expr: ((key > 20) and (key < 25))
  130. type: boolean
  131. Select Operator
  132. expressions:
  133. expr: key
  134. type: string
  135. expr: value
  136. type: string
  137. outputColumnNames: _col0, _col1
  138. Reduce Output Operator
  139. key expressions:
  140. expr: _col0
  141. type: string
  142. sort order: +
  143. Map-reduce partition columns:
  144. expr: _col0
  145. type: string
  146. tag: 2
  147. value expressions:
  148. expr: _col0
  149. type: string
  150. expr: _col1
  151. type: string
  152. Reduce Operator Tree:
  153. Join Operator
  154. condition map:
  155. Outer Join 0 to 1
  156. Left Outer Join0 to 2
  157. condition expressions:
  158. 0 {VALUE._col0} {VALUE._col1}
  159. 1 {VALUE._col0} {VALUE._col1}
  160. 2 {VALUE._col0} {VALUE._col1}
  161. handleSkewJoin: false
  162. outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
  163. Select Operator
  164. expressions:
  165. expr: _col0
  166. type: string
  167. expr: _col1
  168. type: string
  169. expr: _col2
  170. type: string
  171. expr: _col3
  172. type: string
  173. expr: _col4
  174. type: string
  175. expr: _col5
  176. type: string
  177. outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
  178. Select Operator
  179. expressions:
  180. expr: _col0
  181. type: string
  182. expr: _col1
  183. type: string
  184. expr: _col2
  185. type: string
  186. expr: _col3
  187. type: string
  188. expr: _col4
  189. type: string
  190. expr: _col5
  191. type: string
  192. outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
  193. Select Operator
  194. expressions:
  195. expr: UDFToInteger(_col0)
  196. type: int
  197. expr: _col1
  198. type: string
  199. expr: UDFToInteger(_col2)
  200. type: int
  201. expr: _col3
  202. type: string
  203. expr: UDFToInteger(_col4)
  204. type: int
  205. expr: _col5
  206. type: string
  207. outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
  208. File Output Operator
  209. compressed: false
  210. GlobalTableId: 1
  211. table:
  212. input format: org.apache.hadoop.mapred.TextInputFormat
  213. output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
  214. serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
  215. name: default.dest1
  216. Stage: Stage-0
  217. Move Operator
  218. tables:
  219. replace: true
  220. table:
  221. input format: org.apache.hadoop.mapred.TextInputFormat
  222. output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
  223. serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
  224. name: default.dest1
  225. Stage: Stage-2
  226. Stats-Aggr Operator
  227. PREHOOK: query: FROM (
  228. FROM
  229. (
  230. FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
  231. ) a
  232. FULL OUTER JOIN
  233. (
  234. FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
  235. ) b
  236. ON (a.c1 = b.c3)
  237. LEFT OUTER JOIN
  238. (
  239. FROM src src3 SELECT src3.key AS c5, src3.value AS c6 WHERE src3.key > 20 and src3.key < 25
  240. ) c
  241. ON (a.c1 = c.c5)
  242. SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6
  243. ) c
  244. INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6
  245. PREHOOK: type: QUERY
  246. PREHOOK: Input: default@src
  247. PREHOOK: Output: default@dest1
  248. POSTHOOK: query: FROM (
  249. FROM
  250. (
  251. FROM src src1 SELECT src1.key AS c1, src1.value AS c2 WHERE src1.key > 10 and src1.key < 20
  252. ) a
  253. FULL OUTER JOIN
  254. (
  255. FROM src src2 SELECT src2.key AS c3, src2.value AS c4 WHERE src2.key > 15 and src2.key < 25
  256. ) b
  257. ON (a.c1 = b.c3)
  258. LEFT OUTER JOIN
  259. (
  260. FROM src src3 SELECT src3.key AS c5, src3.value AS c6 WHERE src3.key > 20 and src3.key < 25
  261. ) c
  262. ON (a.c1 = c.c5)
  263. SELECT a.c1 AS c1, a.c2 AS c2, b.c3 AS c3, b.c4 AS c4, c.c5 AS c5, c.c6 AS c6
  264. ) c
  265. INSERT OVERWRITE TABLE dest1 SELECT c.c1, c.c2, c.c3, c.c4, c.c5, c.c6
  266. POSTHOOK: type: QUERY
  267. POSTHOOK: Input: default@src
  268. POSTHOOK: Output: default@dest1
  269. POSTHOOK: Lineage: dest1.c1 EXPRESSION [(src)src1.FieldSchema(name:key, type:string, comment:default), ]
  270. POSTHOOK: Lineage: dest1.c2 SIMPLE [(src)src1.FieldSchema(name:value, type:string, comment:default), ]
  271. POSTHOOK: Lineage: dest1.c3 EXPRESSION [(src)src2.FieldSchema(name:key, type:string, comment:default), ]
  272. POSTHOOK: Lineage: dest1.c4 SIMPLE [(src)src2.FieldSchema(name:value, type:string, comment:default), ]
  273. POSTHOOK: Lineage: dest1.c5 EXPRESSION [(src)src3.FieldSchema(name:key, type:string, comment:default), ]
  274. POSTHOOK: Lineage: dest1.c6 SIMPLE [(src)src3.FieldSchema(name:value, type:string, comment:default), ]
  275. PREHOOK: query: SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4,dest1.c5,dest1.c6)) FROM dest1
  276. PREHOOK: type: QUERY
  277. PREHOOK: Input: default@dest1
  278. PREHOOK: Output: file:/tmp/sdong/hive_2011-02-10_01-18-42_908_2010709528917086908/-mr-10000
  279. POSTHOOK: query: SELECT sum(hash(dest1.c1,dest1.c2,dest1.c3,dest1.c4,dest1.c5,dest1.c6)) FROM dest1
  280. POSTHOOK: type: QUERY
  281. POSTHOOK: Input: default@dest1
  282. POSTHOOK: Output: file:/tmp/sdong/hive_2011-02-10_01-18-42_908_2010709528917086908/-mr-10000
  283. POSTHOOK: Lineage: dest1.c1 EXPRESSION [(src)src1.FieldSchema(name:key, type:string, comment:default), ]
  284. POSTHOOK: Lineage: dest1.c2 SIMPLE [(src)src1.FieldSchema(name:value, type:string, comment:default), ]
  285. POSTHOOK: Lineage: dest1.c3 EXPRESSION [(src)src2.FieldSchema(name:key, type:string, comment:default), ]
  286. POSTHOOK: Lineage: dest1.c4 SIMPLE [(src)src2.FieldSchema(name:value, type:string, comment:default), ]
  287. POSTHOOK: Lineage: dest1.c5 EXPRESSION [(src)src3.FieldSchema(name:key, type:string, comment:default), ]
  288. POSTHOOK: Lineage: dest1.c6 SIMPLE [(src)src3.FieldSchema(name:value, type:string, comment:default), ]
  289. -5178357269