@dz902 as this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post. ... View more

Hi @colorsoflife    Consider using CTE is possible, with the sequence as below for your references. This is Hive script incorporate into Ozzie workflow.   set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; with test_CTE as (select * from table1), testone_CTE as (select col1, col2, col3 from test_CTE) insert into table mytablename partition(biz_dt) select col1 as name1, col2 as name2, col3 as name3 from testOne_CTE   ... View more

have u tried move out (or delete the folder for that partition ) from hdfs, then run: msck repair table <tablename> ... View more

I found other simple solution for this issue, Simply find faulty partition from partition list by using command. show partitions table table_name;   then rename the faulty partition to some other name in correct format of your partition. In my case, I used ALTER table table_name partition (date_flag='2021-11-25_bak') rename to partition (date_flag='2021-01-01'); ... View more

I think this was due to non runnning metstore service from hive.   You should run command "hive --service metastore & " first and then start hive console.  ... View more

Below export command worked for me. CREATE table departments_export (departmentid int(11), department_name varchar(45), created_date T1MESTAMP); sqoop export --connect jdbc:mysql://<host>:3306/DB --username cloudera --password *** \ --table departments_export \ --export-dir '/user/cloudera/departments_new/*' \ -m 1 \ --input-fields-terminated-by ','; Sample input: 103,Finance,2020-10-10 10:10:00 ... View more

I'm trying to run a dag with airflow 1.10.12 and HDP 3.0.0 when i run the dag it gets stuck in ```Connecting to jdbc:hive2://[Server2_FQDN]:2181,[Server1_FQDN]:2181/default;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2``` when i run ```beeline -u "jdbc:hive2://[Server1_FQDN]:2181,[Server2_FQDN]:2181/default;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2"``` from shell, it connect to hive with no problem. I've also made a connection like this ``` Conn Id * hive_jdbc ------------- Conn Type ------------- Connection URL jdbc:hive2://centosserver.son.ir:2181,centosclient.son.ir:2181/default;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2 ------------- Login hive ------------- Password ****** ------------- Driver Path /usr/hdp/3.0.0.0-1634/hive/jdbc/hive-jdbc-3.1.0.3.0.0.0-1634-standalone.jar ------------- Driver Class org.apache.hive.jdbc.HiveDriver ``` and I'm not using kerberos I've also added ```hive.security.authorization.sqlstd.confwhitelist.append``` in the ambari ```Custom hive-site``` ``` radoop\.operation\.id|mapred\.job\.name||airflow\.ctx\.dag_id|airflow\.ctx\.task_id|airflow\.ctx\.execution_date|airflow\.ctx\.dag_run_id|airflow\.ctx\.dag_owner|airflow\.ctx\.dag_email|hive\.warehouse\.subdir\.inherit\.perms|hive\.exec\.max\.dynamic\.partitions|hive\.exec\.max\.dynamic\.partitions\.pernode|spark\.app\.name ``` any suggestions? I'm desperate, I've tried every way that i know but still nothing @nsabharwal @agillan @msumbul1 @deepesh1  ... View more

Hi @deepesh1,   Please find the output:   /usr/hdp/current/hive-server2/bin/schematool -dbType mysql -initSchema -verbose SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/3.1.4.0-315/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/3.1.4.0-315/hadoop/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory] Initializing the schema to: 3.1.1000 Metastore connection URL: jdbc:mysql://fgpoc.ambarisvr.com/ Metastore Connection Driver : com.mysql.jdbc.Driver Metastore connection User: ambari_user Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. Starting metastore schema initialization to 3.1.1000 Initialization script hive-schema-3.1.1000.mysql.sql Connecting to jdbc:mysql://fgpoc.ambarisvr.com/ Connected to: MySQL (version 5.7.31-0ubuntu0.16.04.1) Driver: MySQL Connector/J (version mysql-connector-java-8.0.21 (Revision: 33f65445a1bcc544eb0120491926484da168f199)) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:mysql://fgpoc.ambarisvr.com/> !autocommit on Autocommit status: true 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ No rows affected (0.01 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */ No rows affected (0.001 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */ No rows affected (0.001 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40101 SET NAMES utf8 */ No rows affected (0.001 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ No rows affected (0.001 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40103 SET TIME_ZONE='+00:00' */ No rows affected (0.001 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */ No rows affected (0 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ No rows affected (0.004 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ No rows affected (0.001 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */ No rows affected (0.002 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40101 SET @saved_cs_client = @@character_set_client */ No rows affected (0.002 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> /*!40101 SET character_set_client = utf8 */ No rows affected (0.001 seconds) 0: jdbc:mysql://fgpoc.ambarisvr.com/> CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( `SD_ID` bigint(20) NOT NULL, `BUCKET_COL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), KEY `BUCKETING_COLS_N49` (`SD_ID`), CONSTRAINT `BUCKETING_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Error: No database selected (state=3D000,code=1046) Closing: 0: jdbc:mysql://fgpoc.ambarisvr.com/ Schema initialization FAILED! Metastore state would be inconsistent! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent! at org.apache.hadoop.hive.metastore.tools.SchemaToolTaskInit.execute(SchemaToolTaskInit.java:66) at org.apache.hadoop.hive.metastore.tools.MetastoreSchemaTool.run(MetastoreSchemaTool.java:446) at org.apache.hive.beeline.schematool.HiveSchemaTool.main(HiveSchemaTool.java:138) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:318) at org.apache.hadoop.util.RunJar.main(RunJar.java:232) Caused by: java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.schematool.HiveSchemaTool.execSql(HiveSchemaTool.java:105) at org.apache.hive.beeline.schematool.HiveSchemaTool.execSql(HiveSchemaTool.java:83) at org.apache.hadoop.hive.metastore.tools.SchemaToolTaskInit.execute(SchemaToolTaskInit.java:62) ... 8 more *** schemaTool failed *** ... View more

... you have to adapt the configuration to your needs, of course, by changing metastore host and warehouse dir, accordingly ... View more