Sharma, Thank you for your help. Here is the error from Ambari log 04 Aug 2016 17:20:36,026 ERROR [pool-9-thread-9] BaseProvider:240 - Caught exception getting JMX metrics : Connection refused, skipping same exceptions for next 5 minutes One of the agent log has the following error ERROR 2016-08-04 16:21:36,953 HostInfo.py:229 - Checking java processes failed Please let me know if you need more information. Thank you Ram ... View more

Thank you for your reply. The tried the above a) /var/logs and grep for ERROR I have identified in ambari agent logs ERROR 2016-08-03 16:19:01,144 Controller.py:350 - Connection to hdp-cent7-01 was lost (details=Request to https://hdp-cent7-01:8441/agent/v1/heartbeat/hdp-cent7-02 failed due to Error occured during connecting to the server: ('The read operation timed out',)) ERROR 2016-08-03 16:20:27,315 Controller.py:350 - Connection to hdp-cent7-01 was lost (details=Request to https://hdp-cent7-01:8441/agent/v1/heartbeat/hdp-cent7-02 failed due to Error occured during connecting to the server: ('The read operation timed out',)) based on the above, I followed the following article https://community.hortonworks.com/articles/49075/heartbeat-lost-due-to-ambari-agent-error-unable-to.html un-installed ambari-agent as well as ambar-server, reinstalled again. However, it is not working and I noticed the following error in the ambari server log. 04 Aug 2016 17:21:07,213 WARN [C3P0PooledConnectionPoolManager[identityToken->2w0zzb9io96x8a18kxg2w|3fc2959f]-HelperThread-#2] BasicResourcePool:223 - com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@1d91d05d -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections" at sun.reflect.GeneratedConstructorAccessor174.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1112) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2488) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2521) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306) at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:839) at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49) at sun.reflect.GeneratedConstructorAccessor171.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:421) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:350) at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125) at com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44) at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696) b) I tested SSH to all nodes from Ambari server and I did not find any issues. Here is the final error I am seeing WARN [qtp-ambari-client-29] ServletHandler:563 - /api/v1/clusters/txhubdevcluster01/hosts/hdp-cent7-03.rd.allscripts.com/host_components/FLUME_HANDLER javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.v20151217-774c696): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (Unknown error code) Thanks Ram ... View more

Here are the details : a) The following is the show create table testtable results ( this table is created with Spark SQL CREATE TABLE `testtabletmp1`( `person_key` bigint, `pat_last` string, `pat_first` string, `pat_dob` timestamp, `pat_zip` string, `pat_gender` string, `pat_chksum1` bigint, `pat_chksum2` bigint, `dimcreatedgmt` timestamp, `pat_mi` string, `h_keychksum` string, `patmd5` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' LOCATION 'hdfs://hdp-cent7-01:8020/apps/hive/warehouse/datawarehouse.db/testtabledimtmp1' | TBLPROPERTIES ( 'orc.compress'='SNAPPY', 'transient_lastDdlTime'='1469207216') 2. The original table create when we scooped the data from SQL server using SQOOP import CREATE TABLE `testtabledim`( `person_key` bigint, `pat_last` varchar(35), `pat_first` varchar(35), `pat_dob` timestamp, `pat_zip` char(5), `pat_gender` char(1), `pat_chksum1` bigint, `pat_chksum2` bigint, `dimcreatedgmt` timestamp, `pat_mi` char(1), `h_keychksum` string, `patmd5` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' LOCATION 'hdfs://hdp-cent7-01:8020/apps/hive/warehouse/datawarehouse.db/testtabledim' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='false', 'last_modified_by'='hdfs', 'last_modified_time'='1469026541', 'numFiles'='1', 'numRows'='-1', 'orc.compress'='SNAPPY', 'rawDataSize'='-1', 'totalSize'='11144909', 'transient_lastDdlTime'='1469026541') If use the first script using spark sql and store the file as ORC with snappy compression it is working. if I store ORC file with snappy compression and use hive to create table using script 1 then it is working fine. But I use an existing table alter table with a new coulmn using the Spark Hive context and save as ORC with snappy compression, I am getting the following error ORC does not support type conversion from STRING to VARCHAR. if use the same ORC but use hive to create a table using second query even then I am getting the same error. I noticed some columns are defined as VARCHAR(35) and I think those columns may be the issue. After I made the change from VARCHAR to String and CHAR to String, it worked fine. I am still investigating what is the best way to handle VARCHAR/CHAR types through Spark dataframe. Please let me know if you need more information. Thank you for your help. ... View more

I executed the above statement and I indentified that we created a table with TBLPROPERTIES ( | | 'COLUMN_STATS_ACCURATE'='false', | | 'last_modified_by'='hdfs', | | 'last_modified_time'='1469026541', | | 'numFiles'='1', | | 'numRows'='-1', | | 'orc.compress'='SNAPPY', | | 'rawDataSize'='-1', | | 'totalSize'='11144909', | | 'transient_lastDdlTime'='1469026541' I noticed that while storing ORC file I did not provide compress option and I used option("compression", "snappy") while saving the file and it appears the compression is not working. can you please help. thanks Ram ... View more

Hi, thank you for your reply. I will post the results. However I followed these steps. a) Loaded the data from existing table testtable into dataframe using HiveContext b) Added a column using withColumn to dataframe c) Created the new table (testtabletmp) using Spark SQL with new column that saves as ORC d) Save the data frame as ORC dataframe.write.format("orc").save("testtabletmp") With the above steps, I am able to access the table from Hive. I will post the results related to SHOW CREATE TABLE testtable tomorrow. thanks Ram ... View more