Saving all output to "!!{outputDirectory}!!/udf_concat_ws.q.raw". Enter "record" with no arguments to stop it. >>> !run !!{qFileDirectory}!!/udf_concat_ws.q >>> DESCRIBE FUNCTION concat_ws; 'tab_name' 'concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator.' 1 row selected >>> DESCRIBE FUNCTION EXTENDED concat_ws; 'tab_name' 'concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator.' 'Example:' ' > SELECT concat_ws('.', 'www', array('facebook', 'com')) FROM src LIMIT 1;' ' 'www.facebook.com'' 4 rows selected >>> >>> CREATE TABLE dest1(c1 STRING, c2 STRING, c3 STRING); No rows affected >>> >>> FROM src INSERT OVERWRITE TABLE dest1 SELECT 'abc', 'xyz', '8675309' WHERE src.key = 86; '_c0','_c1','_c2' No rows selected >>> >>> EXPLAIN SELECT concat_ws(dest1.c1, dest1.c2, dest1.c3), concat_ws(',', dest1.c1, dest1.c2, dest1.c3), concat_ws(NULL, dest1.c1, dest1.c2, dest1.c3), concat_ws('**', dest1.c1, NULL, dest1.c3) FROM dest1; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME dest1))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION concat_ws (. (TOK_TABLE_OR_COL dest1) c1) (. (TOK_TABLE_OR_COL dest1) c2) (. (TOK_TABLE_OR_COL dest1) c3))) (TOK_SELEXPR (TOK_FUNCTION concat_ws ',' (. (TOK_TABLE_OR_COL dest1) c1) (. (TOK_TABLE_OR_COL dest1) c2) (. (TOK_TABLE_OR_COL dest1) c3))) (TOK_SELEXPR (TOK_FUNCTION concat_ws TOK_NULL (. (TOK_TABLE_OR_COL dest1) c1) (. (TOK_TABLE_OR_COL dest1) c2) (. (TOK_TABLE_OR_COL dest1) c3))) (TOK_SELEXPR (TOK_FUNCTION concat_ws '**' (. (TOK_TABLE_OR_COL dest1) c1) TOK_NULL (. (TOK_TABLE_OR_COL dest1) c3))))))' '' 'STAGE DEPENDENCIES:' ' Stage-1 is a root stage' ' Stage-0 is a root stage' '' 'STAGE PLANS:' ' Stage: Stage-1' ' Map Reduce' ' Alias -> Map Operator Tree:' ' dest1 ' ' TableScan' ' alias: dest1' ' Select Operator' ' expressions:' ' expr: concat_ws(c1, c2, c3)' ' type: string' ' expr: concat_ws(',', c1, c2, c3)' ' type: string' ' expr: concat_ws(null, c1, c2, c3)' ' type: string' ' expr: concat_ws('**', c1, null, c3)' ' type: string' ' outputColumnNames: _col0, _col1, _col2, _col3' ' File Output Operator' ' compressed: false' ' GlobalTableId: 0' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' '' ' Stage: Stage-0' ' Fetch Operator' ' limit: -1' '' '' 37 rows selected >>> >>> SELECT concat_ws(dest1.c1, dest1.c2, dest1.c3), concat_ws(',', dest1.c1, dest1.c2, dest1.c3), concat_ws(NULL, dest1.c1, dest1.c2, dest1.c3), concat_ws('**', dest1.c1, NULL, dest1.c3) FROM dest1; '_c0','_c1','_c2','_c3' 'xyzabc8675309','abc,xyz,8675309','','abc**8675309' 1 row selected >>> >>> -- evalutes function for array of strings >>> EXPLAIN SELECT concat_ws('.', array('www', 'face', 'book', 'com'), '1234'), concat_ws('-', 'www', array('face', 'book', 'com'), '1234'), concat_ws('F', 'www', array('face', 'book', 'com', '1234')), concat_ws('_', array('www', 'face'), array('book', 'com', '1234')), concat_ws('**', 'www', array('face'), array('book', 'com', '1234')), concat_ws('[]', array('www'), 'face', array('book', 'com', '1234')), concat_ws('AAA', array('www'), array('face', 'book', 'com'), '1234') FROM dest1 LIMIT 1; 'Explain' 'ABSTRACT SYNTAX TREE:' ' (TOK_QUERY (TOK_FROM (TOK_TABREF (TOK_TABNAME dest1))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (TOK_FUNCTION concat_ws '.' (TOK_FUNCTION array 'www' 'face' 'book' 'com') '1234')) (TOK_SELEXPR (TOK_FUNCTION concat_ws '-' 'www' (TOK_FUNCTION array 'face' 'book' 'com') '1234')) (TOK_SELEXPR (TOK_FUNCTION concat_ws 'F' 'www' (TOK_FUNCTION array 'face' 'book' 'com' '1234'))) (TOK_SELEXPR (TOK_FUNCTION concat_ws '_' (TOK_FUNCTION array 'www' 'face') (TOK_FUNCTION array 'book' 'com' '1234'))) (TOK_SELEXPR (TOK_FUNCTION concat_ws '**' 'www' (TOK_FUNCTION array 'face') (TOK_FUNCTION array 'book' 'com' '1234'))) (TOK_SELEXPR (TOK_FUNCTION concat_ws '[]' (TOK_FUNCTION array 'www') 'face' (TOK_FUNCTION array 'book' 'com' '1234'))) (TOK_SELEXPR (TOK_FUNCTION concat_ws 'AAA' (TOK_FUNCTION array 'www') (TOK_FUNCTION array 'face' 'book' 'com') '1234'))) (TOK_LIMIT 1)))' '' 'STAGE DEPENDENCIES:' ' Stage-1 is a root stage' ' Stage-0 is a root stage' '' 'STAGE PLANS:' ' Stage: Stage-1' ' Map Reduce' ' Alias -> Map Operator Tree:' ' dest1 ' ' TableScan' ' alias: dest1' ' Select Operator' ' expressions:' ' expr: concat_ws('.', array('www','face','book','com'), '1234')' ' type: string' ' expr: concat_ws('-', 'www', array('face','book','com'), '1234')' ' type: string' ' expr: concat_ws('F', 'www', array('face','book','com','1234'))' ' type: string' ' expr: concat_ws('_', array('www','face'), array('book','com','1234'))' ' type: string' ' expr: concat_ws('**', 'www', array('face'), array('book','com','1234'))' ' type: string' ' expr: concat_ws('[]', array('www'), 'face', array('book','com','1234'))' ' type: string' ' expr: concat_ws('AAA', array('www'), array('face','book','com'), '1234')' ' type: string' ' outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6' ' Limit' ' File Output Operator' ' compressed: false' ' GlobalTableId: 0' ' table:' ' input format: org.apache.hadoop.mapred.TextInputFormat' ' output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' '' ' Stage: Stage-0' ' Fetch Operator' ' limit: 1' '' '' 44 rows selected >>> >>> SELECT concat_ws('.', array('www', 'face', 'book', 'com'), '1234'), concat_ws('-', 'www', array('face', 'book', 'com'), '1234'), concat_ws('F', 'www', array('face', 'book', 'com', '1234')), concat_ws('_', array('www', 'face'), array('book', 'com', '1234')), concat_ws('**', 'www', array('face'), array('book', 'com', '1234')), concat_ws('[]', array('www'), 'face', array('book', 'com', '1234')), concat_ws('AAA', array('www'), array('face', 'book', 'com'), '1234') FROM dest1 LIMIT 1; '_c0','_c1','_c2','_c3','_c4','_c5','_c6' 'www.face.book.com.1234','www-face-book-com-1234','wwwFfaceFbookFcomF1234','www_face_book_com_1234','www**face**book**com**1234','www[]face[]book[]com[]1234','wwwAAAfaceAAAbookAAAcomAAA1234' 1 row selected >>> >>> SELECT concat_ws(NULL, array('www', 'face', 'book', 'com'), '1234'), concat_ws(NULL, 'www', array('face', 'book', 'com'), '1234'), concat_ws(NULL, 'www', array('face', 'book', 'com', '1234')), concat_ws(NULL, array('www', 'face'), array('book', 'com', '1234')), concat_ws(NULL, 'www', array('face'), array('book', 'com', '1234')), concat_ws(NULL, array('www'), 'face', array('book', 'com', '1234')), concat_ws(NULL, array('www'), array('face', 'book', 'com'), '1234') FROM dest1 LIMIT 1; '_c0','_c1','_c2','_c3','_c4','_c5','_c6' '','','','','','','' 1 row selected >>> !record