@Prabhu Muthaiyan You can write query similar to this to get count of numberic values: select count(*) from <tableName> where cast(<colName> as double) is not NULL ... View more

Hi @Devender Yadav, what version of hive are you running? It might be due to known vectorization issue mentioned in this jira: https://issues.apache.org/jira/browse/HIVE-9862 ... View more

@zkfs, mask_show_last_n udf can be used for the scenario you mentioned. You can run this query from the beeline to get additional details about udf: DESCRIBE FUNCTION extended mask_show_last_n; +---------------------------------------------------------------------------------------------------------------------------------+--+ | tab_name | +---------------------------------------------------------------------------------------------------------------------------------+--+ | masks all but last n characters of the value | | Examples: | | mask_show_last_n(ccn, 8) | | mask_show_last_n(ccn, 8, 'x', 'x', 'x') | | Arguments: | | mask_show_last_n(value, charCount, upperChar, lowerChar, digitChar, otherChar, numberChar) | | value - value to mask. Supported types: TINYINT, SMALLINT, INT, BIGINT, STRING, VARCHAR, CHAR | | charCount - number of characters. Default value: 4 | | upperChar - character to replace upper-case characters with. Specify -1 to retain original character. Default value: 'X' | | lowerChar - character to replace lower-case characters with. Specify -1 to retain original character. Default value: 'x' | | digitChar - character to replace digit characters with. Specify -1 to retain original character. Default value: 'n' | | otherChar - character to replace all other characters with. Specify -1 to retain original character. Default value: -1 | | numberChar - character to replace digits in a number with. Valid values: 0-9. Default value: '1' | | NULL | +---------------------------------------------------------------------------------------------------------------------------------+--+ Following query returns string with all the characters masked except last 4: select mask_show_last_n('AAbb1234567', 4, 'X', 'X', 'X'); +--------------+--+ | _c0 | +--------------+--+ | XXXXXXX4567 | +--------------+--+ 1 row selected (0.085 seconds ... View more

From the error message it seems you are trying to read acid orc table from spark sql. There are certain limitations while reading this type of table from spark sql. You can find more details in these jiras: https://issues.apache.org/jira/browse/SPARK-16996 https://issues.apache.org/jira/browse/HIVE-15189 You can force compaction by running "alter table compact" query before reading data from spark sql to workaround this issue. ... View more

@Reddy, You need to specify serialization.encoding property along with LazySimpleSerDe while creating table to load non-utf formatted data. Here is one example: create table table_with_non_utf8_encoding (name STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('serialization.encoding'='ISO8859_1'); load data local inpath '../encoding-ISO8859_1.txt' overwrite into table table_with_non_utf8_encoding; More details in this jira: https://issues.apache.org/jira/browse/HIVE-7142 ... View more

@vj pan, you can use lateral view in your CTAS query to separate the fields from Details_str. You can create table like this in database A create table databaseA.testtable( event_ts timestamp, cust_id int, ban int, detail_str string); Then, use following CTAS query with lateral view to create second table in database B create table databaseB.testtable as select event_ts, cust_id, ban, detail_str, lv_detail_str.type_cd, lv_detail_str.head_id, lv_detail_str.out_id, lv_detail_str.org_type_des from databaseA.testtable a lateral view json_tuple(a.detail_str,'type_cd','head_id','out_id','org_type_des') lv_detail_str as type_cd, head_id, out_id, org_type_des; ... View more