Dr Mich Talebzadeh
LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com *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. I don't know much about MySQL but assuming it has tools to see the activity in the back end, what locks are you seeing in the database itself plus the duration of time that the command is executed on RDBMS etc. Does this only apply to the DDL statements? It is either some locking/blocking in the back end or the network connection between your Hadoop and the RDBMS causing the issue I just tested DDL for external table in Hive through Oracle database and there was no issue. HTH On Thu, 16 May 2019 at 08:16, Iulian Mongescu <iulian.monge...@grx.ro> wrote: > Hi Alan, > > > > I’m using MySQL (Mariadb) for the metastore and I was thinking on this > possibility too but from all my tests on metastore database that I run, > every query is almost instant. > > For example : > > SELECT * FROM `TBLS` -> Query took 0.0001 seconds. > > INSERT INTO `TBLS` -> Query took 0.0020 seconds > > DELETE FROM `TBLS` -> Query took 0.0021 seconds > > > > Thank you, > > Iulian > > > > *From:* Alan Gates <alanfga...@gmail.com> > *Sent:* Wednesday, May 15, 2019 9:51 PM > *To:* user@hive.apache.org > *Subject:* Re: Any HIVE DDL statement takes minutes to execute > > > > What are you using as the RDBMS for your metastore? A first place I'd > look is if the communications with the RDBMS are slow for some reason. > > > > Alan. > > > > On Wed, May 15, 2019 at 10:34 AM Iulian Mongescu <iulian.monge...@grx.ro> > wrote: > > Hello, > > > > I'm working on a HDP-2.6.5.0 cluster with kerberos enabled and I have a > problem with hive as any DDL statement that I run takes minutes to execute > but any DML run in normal limits. I checked the logs but I didn’t find > anything that seems related with this problem and I would appreciate any > help to debug this issue. > > > > Please find bellow some examples with DDL&DML queries and their durations: > > > > ------------------------------------ > > 0: jdbc:hive2://hdpx03:10000/> CREATE EXTERNAL TABLE IF NOT EXISTS agenti1 > (...) STORED AS ORC LOCATION > '/staging/core/agenti/2019-03-18/29d52a54eecae3731b31a3d6ef45d012'; > > No rows affected (184.191 seconds) > > ------------------------------------ > > 0: jdbc:hive2://hdpx03:10000/> show tables; > > +-----------+--+ > > | tab_name | > > +-----------+--+ > > | agenti1 | > > +-----------+--+ > > 1 row selected (0.358 seconds) > > --------------------------------- > > 0: jdbc:hive2://hdpx03:10000/> select count(*) as total from agenti1 where > 1; > > INFO : Tez session hasn't been created yet. Opening session > > INFO : Dag name: select count(*) as total from agenti1 wh...1(Stage-1) > > INFO : Status: Running (Executing on YARN cluster with App id > application_1552674174918_0002) > > > > > -------------------------------------------------------------------------------- > > VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED > > > -------------------------------------------------------------------------------- > > Map 1 .......... SUCCEEDED 1 1 0 0 0 0 > > Reducer 2 ...... SUCCEEDED 1 1 0 0 0 0 > > > -------------------------------------------------------------------------------- > > VERTICES: 02/02 [==========================>>] 100% ELAPSED TIME: 5.48 s > > > -------------------------------------------------------------------------------- > > +--------+--+ > > | total | > > +--------+--+ > > | 1960 | > > +--------+--+ > > 1 row selected (15.853 seconds) > > > > ------------------------------------------------------- > > 0: jdbc:hive2://hdpx03:10000/> drop table agenti1; > > No rows affected (184.164 seconds) > > -------------------------------------------------------- > > 0: jdbc:hive2://hdpx03:10000/> CREATE EXTERNAL TABLE IF NOT EXISTS agenti1 > (...) STORED AS ORC LOCATION > '/staging/core/agenti/2019-03-18/29d52a54eecae3731b31a3d6ef45d012'; > > No rows affected (190.288 seconds) > > > > Thanks, > > > > Iulian > > > >
