Hi Mich! Nice to meet you and thanks for the answer! I don't know what you exactly mean with the size of the table and partition columns, but here are some relevant information about it:
The table that we are trying to add the column to has 600,000 partitions and the total size of the partitions table in the metastore is 9,100,000. The partitions are divided by the following levels region=/dt=/country= Which are: region: 3 dt: YYYY-mm-dd-HH ts string format that is divided around 2160 Country: ~138 The number that varies is countries by region, but multiplying these 3 numbers above, reaches the 600,000 partitions told before. I tried to add a column to another table with 770,000 partitions and didn't work as well. Also, the tables in question are EXTERNAL, so AFAIK, the size of it shouldn't affect the DDL right? Trying to execute the DDL in a table, with 4,000 partitions in it, that is in the same schema works fine. I've tried what you suggested and it didn't work, the log of it is below. Seems that the timeout setting wasn't taken by Hive, because in the error log we can see that it is telling that exceeds the 600s default that you've mentioned. Do you have a clue on what could have happened? +--------------------------------------------+--+ | set | +--------------------------------------------+--+ | hive.metastore.client.socket.timeout=3600 | +--------------------------------------------+--+ 1 row selected (0,014 seconds) 0: jdbc:hive2://emr-prd-etl.jampp.com:10000/d> ALTER TABLE schema.table ADD COLUMNS (column STRING); INFO : Compiling command(queryId=hive_20210422131340_3163e6b9-1739-4c4f-8241-442c507e8e4f): ALTER TABLE schema.table ADD COLUMNS (column STRING) INFO : Concurrency mode is disabled, not creating a lock manager INFO : Semantic Analysis Completed (retrial = false) INFO : Returning Hive schema: Schema(fieldSchemas:null, properties:null) INFO : Completed compiling command(queryId=hive_20210422131340_3163e6b9-1739-4c4f-8241-442c507e8e4f); Time taken: 0.101 seconds INFO : Concurrency mode is disabled, not creating a lock manager INFO : Executing command(queryId=hive_20210422131340_3163e6b9-1739-4c4f-8241-442c507e8e4f): ALTER TABLE schema.table ADD COLUMNS (column STRING) INFO : Starting task [Stage-0:DDL] in serial mode ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. Timeout when executing method: alter_table_with_environment_context; 1611675ms exceeds 600000ms INFO : Completed executing command(queryId=hive_20210422131340_3163e6b9-1739-4c4f-8241-442c507e8e4f); Time taken: 1617.779 seconds INFO : Concurrency mode is disabled, not creating a lock manager Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Unable to alter table. Timeout when executing method: alter_table_with_environment_context; 1611675ms exceeds 600000ms (state=08S01,code=1) Thanks in advance, Vinícius Em ter., 20 de abr. de 2021 às 06:26, Mich Talebzadeh < mich.talebza...@gmail.com> escreveu: > Hi, > > Just a small point, what you are doing is DDL not a query. You are > altering the table and adding a new column. > > First can you confirm that your table has 600,000 partitions!. If so, what > is the size of the table and the partition column? > > The process seems to be timing out on alter. table. Your table has too > many partitions, then the chance is that timeout happens. You can check > this parameter > > hive.metastore.client.socket.timeout > > in hive-site.xml. In my case it is set to 600s, > > <property> > <name>hive.metastore.client.socket.timeout</name> > *<value>600s</value>* > <description> > Expects a time value with unit (d/day, h/hour, m/min, s/sec, ms/msec, > us/usec, ns/nsec), which is sec if not specified. > MetaStore Client socket timeout in seconds > </description> > </property> > > and set it at session level before running ALTER TABLE command > > set hive.metastore.client.socket.timeout=600 -- or larger value > . . . . . . . . . . . . . . . . . . > No rows affected (0.002 seconds) > > HTH > > > view my Linkedin profile > <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/> > > > > *Disclaimer:* Use it at your own risk. Any and all responsibility for any > loss, damage or destruction of data or any other property which may arise > from relying on this email's technical content is explicitly disclaimed. > The author will in no case be liable for any monetary damages arising from > such loss, damage or destruction. > > > > > On Mon, 19 Apr 2021 at 15:27, Vinícius Matheus Olivieri < > olivierivi...@gmail.com> wrote: > >> Hey guys! >> >> I am using HIVE 3.1.2 in a recently updated cluster of EMR 6.0.0 hosted >> in AWS and I am experiencing some problems when trying to execute a simple >> query in hive. >> >> The query is in question is the following: >> >> ALTER TABLE schema.table ADD COLUMNS (new_column STRING); >> >> The table that we are executing the query has approximately 600k >> partitions. >> >> The version of Hive was updated recently to 3.1.2 as the whole package >> included in EMR 6.0.0 >> <https://docs.aws.amazon.com/emr/latest/ReleaseGuide/emr-release-6x.html> >> . >> >> The curious thing is that when we were using Hive 2.3.6, the query worked >> with no worries or any hard work. So I searched if the version update >> changed something on the execution of an ALTER TABLE but I didn’t find >> anything relevant that could be the root cause of the problem. >> >> Could you guys help me see the light at the end of the tunnel? >> >> >> The log that is showed in the server side is the following: >> >> 2021-04-14T14:34:45,176 ERROR [HiveServer2-Background-Pool: >> Thread-221871([])]: exec.DDLTask (:()) - Failed >> >> org.apache.hadoop.hive.ql.metadata.HiveException: Unable to alter table. >> java.net.SocketTimeoutException: Read timed out >> >> at org.apache.hadoop.hive.ql.metadata.Hive.alterTable(Hive.java:721) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.metadata.Hive.alterTable(Hive.java:698) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.exec.DDLTask.alterTable(DDLTask.java:3966) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:455) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:205) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:97) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:2664) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:2335) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:2011) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1709) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1703) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:157) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:224) >> ~[hive-service-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hive.service.cli.operation.SQLOperation.access$700(SQLOperation.java:87) >> ~[hive-service-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork$1.run(SQLOperation.java:316) >> ~[hive-service-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at java.security.AccessController.doPrivileged(Native Method) >> ~[?:1.8.0_242] >> >> at javax.security.auth.Subject.doAs(Subject.java:422) ~[?:1.8.0_242] >> >> at >> org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1730) >> ~[hadoop-common-3.2.1-amzn-0.jar:?] >> >> at >> org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork.run(SQLOperation.java:330) >> ~[hive-service-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) >> ~[?:1.8.0_242] >> >> at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_242] >> >> at >> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) >> ~[?:1.8.0_242] >> >> at >> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) >> ~[?:1.8.0_242] >> >> at java.lang.Thread.run(Thread.java:748) [?:1.8.0_242] >> >> Caused by: org.apache.thrift.transport.TTransportException: >> java.net.SocketTimeoutException: Read timed out >> >> at >> org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:129) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:429) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:318) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:219) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_alter_table_with_environment_context(ThriftHiveMetastore.java:2270) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.alter_table_with_environment_context(ThriftHiveMetastore.java:2254) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.alter_table_with_environmentContext(HiveMetaStoreClient.java:405) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.alter_table_with_environmentContext(SessionHiveMetaStoreClient.java:376) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> ~[?:1.8.0_242] >> >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >> ~[?:1.8.0_242] >> >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >> ~[?:1.8.0_242] >> >> at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_242] >> >> at >> org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:212) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at com.sun.proxy.$Proxy35.alter_table_with_environmentContext(Unknown >> Source) ~[?:?] >> >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> ~[?:1.8.0_242] >> >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >> ~[?:1.8.0_242] >> >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >> ~[?:1.8.0_242] >> >> at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_242] >> >> at >> org.apache.hadoop.hive.metastore.HiveMetaStoreClient$SynchronizedHandler.invoke(HiveMetaStoreClient.java:2773) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at com.sun.proxy.$Proxy35.alter_table_with_environmentContext(Unknown >> Source) ~[?:?] >> >> at org.apache.hadoop.hive.ql.metadata.Hive.alterTable(Hive.java:717) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> ... 23 more >> >> Caused by: java.net.SocketTimeoutException: Read timed out >> >> at java.net.SocketInputStream.socketRead0(Native Method) ~[?:1.8.0_242] >> >> at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) >> ~[?:1.8.0_242] >> >> at java.net.SocketInputStream.read(SocketInputStream.java:171) >> ~[?:1.8.0_242] >> >> at java.net.SocketInputStream.read(SocketInputStream.java:141) >> ~[?:1.8.0_242] >> >> at java.io.BufferedInputStream.fill(BufferedInputStream.java:246) >> ~[?:1.8.0_242] >> >> at java.io.BufferedInputStream.read1(BufferedInputStream.java:286) >> ~[?:1.8.0_242] >> >> at java.io.BufferedInputStream.read(BufferedInputStream.java:345) >> ~[?:1.8.0_242] >> >> at >> org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:127) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:429) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:318) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:219) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_alter_table_with_environment_context(ThriftHiveMetastore.java:2270) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.alter_table_with_environment_context(ThriftHiveMetastore.java:2254) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.alter_table_with_environmentContext(HiveMetaStoreClient.java:405) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at >> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.alter_table_with_environmentContext(SessionHiveMetaStoreClient.java:376) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> ~[?:1.8.0_242] >> >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >> ~[?:1.8.0_242] >> >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >> ~[?:1.8.0_242] >> >> at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_242] >> >> at >> org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:212) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at com.sun.proxy.$Proxy35.alter_table_with_environmentContext(Unknown >> Source) ~[?:?] >> >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> ~[?:1.8.0_242] >> >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >> ~[?:1.8.0_242] >> >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >> ~[?:1.8.0_242] >> >> at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_242] >> >> at >> org.apache.hadoop.hive.metastore.HiveMetaStoreClient$SynchronizedHandler.invoke(HiveMetaStoreClient.java:2773) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> at com.sun.proxy.$Proxy35.alter_table_with_environmentContext(Unknown >> Source) ~[?:?] >> >> at org.apache.hadoop.hive.ql.metadata.Hive.alterTable(Hive.java:717) >> ~[hive-exec-3.1.2-amzn-0.jar:3.1.2-amzn-0] >> >> ... 23 more >> >
