@Paras Mehta I've been dealing with the same issue for a couple of weeks. The work around that I found was to use the Phoenix Query Server and JDBC Thin Client instead. It doesn't require any of the hadoop resources. However there does appear to be a performance penalty for large numbers of inserts. I'm still trying to track down if it's possible to add the hbase-site.xml to the NiFi class path as hinted at in the Hive Connection Pool but that wouldn't work if you have multiple Hadoop Clusters you're working with. Based on my research the last couple of weeks the NiFi community seems to be pretty anti Phoenix anyway so expect to have to fight with all of the processors due to the slight changes in syntax. ... View more

What does the query look like in the "db.view"? How many joins, formats of underlying table, where clauses etc. If you're just doing a straight "select * from table limit 10" I'd expect it to return almost immediately but any kind of processing will take a few seconds. Have you tried LLAP/Interactive Queries? ... View more

If it works with a test file then it has to be a syntax error in your real data. Unfortunately without seeing the real statements there isn't anyway for us to troubleshoot. I'd try running some of the sql statements from the flowfile in beeline and see if you get a better error. ... View more

@Bala Vignesh N V This is a perfect use case for the Hive Regular Expression Serde. Below is an example external table that could read this. If you're not familiar with regular expressions take a look here. With this serde each capture group is a column and I've basically told it to look for any set of characters other than the | or , delimiters. For additional information about the Hive Regex Serde see here. create external table example_table ( col1 string, col2 string, col3 string ) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties ('input.regex'='^([^,\|]+),([^,\|]+),([^,\|]+)\|([^,\|]+)$') stored as textfile; ... View more

This won't actually work as the Hive and Ambari database structure doesn't support group replication. Several tables are missing primary keys and will lead to problems in replication. See Group Replication Requirements. ... View more

There are actually several tables missing Primary Keys all related to transactions. I've submitted HIVE-17306 to address this as currently I don't know the impact of adding Primary Keys to all of these tables. On HDP 2.6 it appears that the following tables don't have Primary Keys. On another note I'm not sure you can add surrogate keys as it looks like we have some unqualified inserts in Transaction Metastore that don't explicity list what columns are being inserted. This prevents just adding an extra column. completed_txn_components next_compaction_queue_id next_lock_id next_txn_id txn_components write_set ... View more

According to this post HDP uses UTC as default but a simple Hive Statement like this and multiple JIRA Issues proves that isn't true. select concat('Example: ',cast(cast('2017-03-12 02:00:00' as timestamp) as string)); Example: 2017-03-12 03:00:00 Can someone provide guidance on how to set the JVMs Timezone? ... View more