Sunday 11 February 2018

Install Hive 2.3.3 on Hadoop 3.0.0 multi-node cluster on Ubuntu

Note: Contact for college projects (real-time) on BigData and Cloud Computing (Amazon Web Services & Google Cloud Platform)
1. Update your OS (Ubuntu 16.04.3 LTS).

 sudo apt-get update
2. Download Hive files from Apache Software Foundation Distribution Directory.

 https://www.apache.org/dist/hive/hive-2.3.3/

 wget https://www.apache.org/dist/hive/hive-2.3.3/apache-hive-2.3.3-bin.tar.gz

3. Extract it using Tape Archive command

 sudo tar -xvf apache-hive-2.3.3-bin.tar.gz -C /usr/local/


4. Update .bashrc file with paths for Hive.

 sudo gedit ~/.bashrc


Note: Hive uses Hadoop, so, you must have Hadoop in your path

Paste following lines at the end of the file:

 export HIVE_HOME=/usr/local/apache-hive-2.3.3-bin
export HIVE_CONF_DIR=/usr/local/apache-hive-2.3.3-bin/conf
export PATH=$PATH:$HIVE_HOME/bin
export CLASSPATH=$CLASSPATH:/usr/local/hadoop-3.0.0/lib/*:.
export CLASSPATH=$CLASSPATH:/usr/local/apache-hive-2.3.3-bin/lib/*:.


5. In addition, you must use below HDFS commands to create /tmp and /user/hive/warehouse (aka hive.metastore.warehouse.dir) and set them chmod 777 before you can create a table in Hive.

hadoopuser@ub16043lts00:~$ hdfs dfs -mkdir /tmp
hadoopuser@ub16043lts00:~$ hdfs dfs -chmod 777 /tmp

hadoopuser@ub16043lts00:~$ hdfs dfs -mkdir -p /user/hive/warehouse
hadoopuser@ub16043lts00:~$ hdfs dfs -chmod 777 /user/hive/warehouse

Note: Metastore is the central repository of Apache Hive metadata. It stores metadata for Hive tables (like their schema and location) and partitions in a relational database. It provides client access to this information by using metastore service API.

 There are three modes for Hive Metastore deployment:
  • Embedded Metastore - In Hive by default, metastore service runs in the same JVM as the Hive service. It uses embedded derby database stored on the local file system in this mode.
  • Local Metastore - Hive does not prefer single session, so to overcome this limitation of Embedded Metastore, for Local Metastore was introduced. This mode allows us to have many Hive sessions i.e. many users can use the metastore at the same time. We can achieve by using any JDBC compliant like MySQL which runs in a separate JVM or different machines than that of the Hive service and metastore service which are running in the same JVM.
  • Remote Metastore - In this mode, metastore runs on its own separate JVM, not in the Hive service JVM. If other processes want to communicate with the metastore server they can communicate using Thrift Network APIs.

7. Running Hiveserver2 and Beeline.

Starting from Hive 2.1, we need to run the schematool command below as an initialization step. For example, we can use "Derby" or "MySQL" as db type.

Hive supports 5 backend databases:
  • Derby
  • MySQL
  • MS SQL Server
  • Oracle
  • Postgres

Note 1:  

Need to install Derby or MySQL separately. Check my other tutorial for Derby and MySQL database installation. After installation of any of the above mentioned database, create an user as "any_user" and provide a pass_word. 

See below for reference:

 hadoopuser@ub16043lts00:~$ mysql -u root -h ub16043lts00 -p
Enter password: 1234

Note 2: 

a. Installing MySQL Java Connector. This is for java dependencies and connection purpose

hadoopuser@ub16043lts00:~$ sudo apt-get install libmysql-java



b. Creating soft link for connector in Hive lib directory. This is for soft link between Java and MySql.

hadoopuser@ub16043lts00:~$ ln -s /usr/share/java/mysql-connector-java.jar $HIVE_HOME/lib/mysql-connector-java.jar



c. Edit Hive configuration files, hive-site.xml and hive-env.sh respectively.
  
Execute below command to copy hive-default.xml.template to hive-site.xml

hadoopuser@ub16043lts00:~$ cp /usr/local/apache-hive-2.3.3-bin/conf/hive-default.xml.template /usr/local/apache-hive-2.3.3-bin/conf/hive-site.xml

Update with below values

====hive-site.xml====
 
<configuration>

  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <!--<value>jdbc:derby:;databaseName=metastore_db_derby;create=true</value>-->
    <!--<value>jdbc:mysql:;databaseName=metastore_db_mysql;create=true</value>-->
    <value>jdbc:mysql://ub16043lts00/metastore_db_mysql?createDatabaseIfNotExist=true</value>
    <description>
      JDBC connect string for a JDBC metastore.
      To use SSL to encrypt/authenticate the connection, provide database-specific SSL flag in the connection URL.
      For example, jdbc:postgresql://myhost/db?ssl=true for postgres database.
    </description>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <!--<value>org.apache.derby.jdbc.EmbeddedDriver</value>-->
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
    <description>user name for connecting to mysql server</description>
  </property>

  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>1234</value>
    <description>password for connecting to mysql server</description>
  </property>

  <property>
    <name>hive.metastore.use.SSL</name>
    <value>false</value>
    <description>Set this to true for using SSL encryption in HMS server.</description>
  </property>

  <property>
    <name>hive.server2.thrift.min.worker.threads</name>
    <value>5</value>
    <description>Minimum number of worker threads, default 5.</description>
  </property>

  <property>
    <name>hive.server2.thrift.max.worker.threads</name>
    <value>5</value>
    <description>Maximum number of worker threads, default 500.</description>
  </property>

  <property>
    <name>hive.server2.thrift.port</name>
    <value>10000</value>
    <description>TCP port number to listen on, default 10000.</description>
  </property>

  <property>
    <name>hive.server2.thrift.bind.host</name>
    <value>ub16043lts00</value>
    <description>Bind host on which to run the HiveServer2 Thrift interface. Default Value: localhost</description>
  </property>

  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>hdfs://ub16043lts00/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
  </property>

  <property>
    <name>hive.execution.engine</name>
    <value>mr</value>
    <description>
      Expects one of [mr, tez, spark].
      Chooses execution engine. Options are: mr (Map reduce, default), tez, spark. While MR
      remains the default engine for historical reasons, it is itself a historical engine
      and is deprecated in Hive 2 line. It may be removed without further warning.
    </description>
  </property>

  <property>
    <name>spark.master</name>
    <value>spark://10.0.1.1:7077</value>
    <description></description>
  </property>

  <property>
    <name>hive.merge.sparkfiles</name>
    <value>false</value>
    <description>Merge small files at the end of a Spark DAG Transformation</description>
  </property>

  <property>
    <name>hive.spark.client.future.timeout</name>
    <value>60s</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.
      Timeout for requests from Hive client to remote Spark driver.
    </description>
  </property>

  <property>
    <name>hive.spark.job.monitor.timeout</name>
    <value>60s</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.
      Timeout for job monitor to get Spark job state.
    </description>
  </property>

  <property>
    <name>hive.metastore.schema.verification</name>
    <value>false</value>
    <description>
      Enforce metastore schema version consistency.
      True: Verify that version information stored in is compatible with one from Hive jars.  Also disable automatic
            schema migration attempt. Users are required to manually migrate schema after Hive upgrade which ensures
            proper metastore schema migration. (Default)
      False: Warn if the version information stored in metastore doesn't match with one from in Hive jars.
    </description>
  </property>

  <property>
    <name>hive.server2.authentication</name>
    <value>NONE</value>
    <description>
      Expects one of [nosasl, none, ldap, kerberos, pam, custom].
      Client authentication types.
        NONE: no authentication check
        LDAP: LDAP/AD based authentication
        KERBEROS: Kerberos/GSSAPI authentication
        CUSTOM: Custom authentication provider
                (Use with property hive.server2.custom.authentication.class)
        PAM: Pluggable authentication module
        NOSASL:  Raw transport
    </description>
  </property>

  <property>
    <name>hive.server2.enable.doAs</name>
    <value>false</value>
    <description>
      Setting this property to true will have HiveServer2 execute
      Hive operations as the user making the calls to it.
    </description>
  </property> 

  <property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
  </property>

</configuration>
   
Execute below command to copy hive-env.sh.template to hive-env.sh

hadoopuser@ub16043lts00:~$ cp /usr/local/apache-hive-2.3.3-bin/conf/hive-env.sh.template /usr/local/apache-hive-2.3.3-bin/conf/hive-env.sh


Update with below values

====hive-env.sh====
 
# Set HADOOP_HOME to point to a specific hadoop install directory
# HADOOP_HOME=${bin}/../../hadoop
export HADOOP_HOME=/usr/local/hadoop-3.0.0

# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/usr/local/apache-hive-2.3.3-bin/conf
export HIVE_AUX_JARS_PATH=$HIVE_AUX_JARS_PATH


d. SchemaTool Initialization
==== schematool command for Derby db  ====

hadoopuser@ub16043lts00:~$ $HIVE_HOME/bin/schematool -dbType derby -initSchema
 
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-3.0.0-alpha4/share/hadoop/common/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]
Metastore connection URL:     jdbc:derby:;databaseName=metastore_db;create=true
Metastore Connection Driver :     org.apache.derby.jdbc.EmbeddedDriver
Metastore connection User:     APP
Starting metastore schema initialization to 2.3.3
Initialization script hive-schema-2.3.3.derby.sql
Initialization script completed
schemaTool completed 

  
==== Verify "derby" schemaTool creation  ====

hadoopuser@ub16043lts00:~$ $HIVE_HOME/bin/schematool -dbType derby -info
 
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-3.0.0-alpha4/share/hadoop/common/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]
Metastore connection URL:     jdbc:derby:;databaseName=metastore_db;create=true
Metastore Connection Driver :     org.apache.derby.jdbc.EmbeddedDriver
Metastore connection User:     APP
Hive distribution version:     2.3.3
Metastore schema version:     2.3.3
schemaTool completed

==== schematool command for MySQL db  ====

hadoopuser@ub16043lts00:~$ $HIVE_HOME/bin/schematool -dbType mysql -initSchema -userName root -passWord '1234' -verbose

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-3.0.0-alpha4/share/hadoop/common/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]
Metastore connection URL:     jdbc:mysql://ub16043lts00/metastore_db_mysql?createDatabaseIfNotExist=true
Metastore Connection Driver :     com.mysql.jdbc.Driver
Metastore connection User:     hiveuser
Wed Sep 13 13:09:04 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Starting metastore schema initialization to 2.3.3
Initialization script hive-schema-2.3.3.mysql.sql
Connecting to jdbc:mysql://ub16043lts00/metastore_db_mysql?createDatabaseIfNotExist=true
Wed Sep 13 13:09:05 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Connected to: MySQL (version 5.7.19-0ubuntu0.16.04.1)
Driver: MySQL Connector Java (version mysql-connector-java-5.1.38 ( Revision: ${revinfo.commit} ))
Transaction isolation: TRANSACTION_READ_COMMITTED
0: jdbc:mysql://ub16043lts00/metastore_db_mys> !autocommit on
Autocommit status: true
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
No rows affected (0.017 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
No rows affected (0.004 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET NAMES utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */
No rows affected (0.011 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40103 SET TIME_ZONE='+00:00' */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */
No rows affected (0.006 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
No rows affected (0.009 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
No rows affected (0.008 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
No rows affected (0.005 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.007 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.004 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> 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
No rows affected (0.449 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `CDS` ( `CD_ID` bigint(20) NOT NULL, PRIMARY KEY (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.28 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.015 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.004 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `COLUMNS_V2` ( `CD_ID` bigint(20) NOT NULL, `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TYPE_NAME` MEDIUMTEXT DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`CD_ID`,`COLUMN_NAME`), KEY `COLUMNS_V2_N49` (`CD_ID`), CONSTRAINT `COLUMNS_V2_FK1` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.329 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( `DB_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`DB_ID`,`PARAM_KEY`), KEY `DATABASE_PARAMS_N49` (`DB_ID`), CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.376 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.004 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `DBS` ( `DB_ID` bigint(20) NOT NULL, `DESC` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `DB_LOCATION_URI` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `OWNER_TYPE` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`DB_ID`), UNIQUE KEY `UNIQUE_DATABASE` (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.29 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.021 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.004 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `DB_PRIVS` ( `DB_GRANT_ID` bigint(20) NOT NULL, `CREATE_TIME` int(11) NOT NULL, `DB_ID` bigint(20) DEFAULT NULL, `GRANT_OPTION` smallint(6) NOT NULL, `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`DB_GRANT_ID`), UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), KEY `DB_PRIVS_N49` (`DB_ID`), CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.358 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.005 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.01 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` ( `USER_GRANT_ID` bigint(20) NOT NULL, `CREATE_TIME` int(11) NOT NULL, `GRANT_OPTION` smallint(6) NOT NULL, `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`USER_GRANT_ID`), UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.387 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `IDXS` ( `INDEX_ID` bigint(20) NOT NULL, `CREATE_TIME` int(11) NOT NULL, `DEFERRED_REBUILD` bit(1) NOT NULL, `INDEX_HANDLER_CLASS` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INDEX_TBL_ID` bigint(20) DEFAULT NULL, `LAST_ACCESS_TIME` int(11) NOT NULL, `ORIG_TBL_ID` bigint(20) DEFAULT NULL, `SD_ID` bigint(20) DEFAULT NULL, PRIMARY KEY (`INDEX_ID`), UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`), KEY `IDXS_N51` (`SD_ID`), KEY `IDXS_N50` (`INDEX_TBL_ID`), KEY `IDXS_N49` (`ORIG_TBL_ID`), CONSTRAINT `IDXS_FK1` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`), CONSTRAINT `IDXS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`), CONSTRAINT `IDXS_FK3` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.485 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` ( `INDEX_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`), KEY `INDEX_PARAMS_N49` (`INDEX_ID`), CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `IDXS` (`INDEX_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.449 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `NUCLEUS_TABLES` ( `CLASS_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TABLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TYPE` varchar(4) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `OWNER` varchar(2) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `VERSION` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `INTERFACE_NAME` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`CLASS_NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.246 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.004 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.004 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `PARTITIONS` ( `PART_ID` bigint(20) NOT NULL, `CREATE_TIME` int(11) NOT NULL, `LAST_ACCESS_TIME` int(11) NOT NULL, `PART_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `SD_ID` bigint(20) DEFAULT NULL, `TBL_ID` bigint(20) DEFAULT NULL, PRIMARY KEY (`PART_ID`), UNIQUE KEY `UNIQUEPARTITION` (`PART_NAME`,`TBL_ID`), KEY `PARTITIONS_N49` (`TBL_ID`), KEY `PARTITIONS_N50` (`SD_ID`), CONSTRAINT `PARTITIONS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`), CONSTRAINT `PARTITIONS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.484 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `PARTITION_EVENTS` ( `PART_NAME_ID` bigint(20) NOT NULL, `DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `EVENT_TIME` bigint(20) NOT NULL, `EVENT_TYPE` int(11) NOT NULL, `PARTITION_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `TBL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`PART_NAME_ID`), KEY `PARTITIONEVENTINDEX` (`PARTITION_NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.32 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` ( `TBL_ID` bigint(20) NOT NULL, `PKEY_COMMENT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PKEY_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PKEY_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`TBL_ID`,`PKEY_NAME`), KEY `PARTITION_KEYS_N49` (`TBL_ID`), CONSTRAINT `PARTITION_KEYS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.342 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` ( `PART_ID` bigint(20) NOT NULL, `PART_KEY_VAL` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`PART_ID`,`INTEGER_IDX`), KEY `PARTITION_KEY_VALS_N49` (`PART_ID`), CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.49 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `PARTITION_PARAMS` ( `PART_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`PART_ID`,`PARAM_KEY`), KEY `PARTITION_PARAMS_N49` (`PART_ID`), CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.3 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.005 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` ( `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `CREATE_TIME` int(11) NOT NULL, `GRANT_OPTION` smallint(6) NOT NULL, `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PART_ID` bigint(20) DEFAULT NULL, `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`PART_COLUMN_GRANT_ID`), KEY `PART_COL_PRIVS_N49` (`PART_ID`), KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.452 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `PART_PRIVS` ( `PART_GRANT_ID` bigint(20) NOT NULL, `CREATE_TIME` int(11) NOT NULL, `GRANT_OPTION` smallint(6) NOT NULL, `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PART_ID` bigint(20) DEFAULT NULL, `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`PART_GRANT_ID`), KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), KEY `PART_PRIVS_N49` (`PART_ID`), CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.391 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.005 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `ROLES` ( `ROLE_ID` bigint(20) NOT NULL, `CREATE_TIME` int(11) NOT NULL, `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`ROLE_ID`), UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.307 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `ROLE_MAP` ( `ROLE_GRANT_ID` bigint(20) NOT NULL, `ADD_TIME` int(11) NOT NULL, `GRANT_OPTION` smallint(6) NOT NULL, `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `ROLE_ID` bigint(20) DEFAULT NULL, PRIMARY KEY (`ROLE_GRANT_ID`), UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`), KEY `ROLE_MAP_N49` (`ROLE_ID`), CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ROLE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.38 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SDS` ( `SD_ID` bigint(20) NOT NULL, `CD_ID` bigint(20) DEFAULT NULL, `INPUT_FORMAT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `IS_COMPRESSED` bit(1) NOT NULL, `IS_STOREDASSUBDIRECTORIES` bit(1) NOT NULL, `LOCATION` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `NUM_BUCKETS` int(11) NOT NULL, `OUTPUT_FORMAT` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `SERDE_ID` bigint(20) DEFAULT NULL, PRIMARY KEY (`SD_ID`), KEY `SDS_N49` (`SERDE_ID`), KEY `SDS_N50` (`CD_ID`), CONSTRAINT `SDS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `SERDES` (`SERDE_ID`), CONSTRAINT `SDS_FK2` FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.33 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.009 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SD_PARAMS` ( `SD_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`SD_ID`,`PARAM_KEY`), KEY `SD_PARAMS_N49` (`SD_ID`), CONSTRAINT `SD_PARAMS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.378 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SEQUENCE_TABLE` ( `SEQUENCE_NAME` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `NEXT_VAL` bigint(20) NOT NULL, PRIMARY KEY (`SEQUENCE_NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.263 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SERDES` ( `SERDE_ID` bigint(20) NOT NULL, `NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `SLIB` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`SERDE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.259 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SERDE_PARAMS` ( `SERDE_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`), KEY `SERDE_PARAMS_N49` (`SERDE_ID`), CONSTRAINT `SERDE_PARAMS_FK1` FOREIGN KEY (`SERDE_ID`) REFERENCES `SERDES` (`SERDE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.592 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SKEWED_COL_NAMES` ( `SD_ID` bigint(20) NOT NULL, `SKEWED_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 `SKEWED_COL_NAMES_N49` (`SD_ID`), CONSTRAINT `SKEWED_COL_NAMES_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.304 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SKEWED_COL_VALUE_LOC_MAP` ( `SD_ID` bigint(20) NOT NULL, `STRING_LIST_ID_KID` bigint(20) NOT NULL, `LOCATION` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`SD_ID`,`STRING_LIST_ID_KID`), KEY `SKEWED_COL_VALUE_LOC_MAP_N49` (`STRING_LIST_ID_KID`), KEY `SKEWED_COL_VALUE_LOC_MAP_N50` (`SD_ID`), CONSTRAINT `SKEWED_COL_VALUE_LOC_MAP_FK2` FOREIGN KEY (`STRING_LIST_ID_KID`) REFERENCES `SKEWED_STRING_LIST` (`STRING_LIST_ID`), CONSTRAINT `SKEWED_COL_VALUE_LOC_MAP_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.441 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SKEWED_STRING_LIST` ( `STRING_LIST_ID` bigint(20) NOT NULL, PRIMARY KEY (`STRING_LIST_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.255 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.004 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.005 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SKEWED_STRING_LIST_VALUES` ( `STRING_LIST_ID` bigint(20) NOT NULL, `STRING_LIST_VALUE` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`STRING_LIST_ID`,`INTEGER_IDX`), KEY `SKEWED_STRING_LIST_VALUES_N49` (`STRING_LIST_ID`), CONSTRAINT `SKEWED_STRING_LIST_VALUES_FK1` FOREIGN KEY (`STRING_LIST_ID`) REFERENCES `SKEWED_STRING_LIST` (`STRING_LIST_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.294 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SKEWED_VALUES` ( `SD_ID_OID` bigint(20) NOT NULL, `STRING_LIST_ID_EID` bigint(20) NOT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`SD_ID_OID`,`INTEGER_IDX`), KEY `SKEWED_VALUES_N50` (`SD_ID_OID`), KEY `SKEWED_VALUES_N49` (`STRING_LIST_ID_EID`), CONSTRAINT `SKEWED_VALUES_FK2` FOREIGN KEY (`STRING_LIST_ID_EID`) REFERENCES `SKEWED_STRING_LIST` (`STRING_LIST_ID`), CONSTRAINT `SKEWED_VALUES_FK1` FOREIGN KEY (`SD_ID_OID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.414 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `SORT_COLS` ( `SD_ID` bigint(20) NOT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `ORDER` int(11) NOT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`SD_ID`,`INTEGER_IDX`), KEY `SORT_COLS_N49` (`SD_ID`), CONSTRAINT `SORT_COLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.32 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.005 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` ( `TBL_ID` bigint(20) NOT NULL, `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARAM_VALUE` MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`TBL_ID`,`PARAM_KEY`), KEY `TABLE_PARAMS_N49` (`TBL_ID`), CONSTRAINT `TABLE_PARAMS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.299 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `TBLS` ( `TBL_ID` bigint(20) NOT NULL, `CREATE_TIME` int(11) NOT NULL, `DB_ID` bigint(20) DEFAULT NULL, `LAST_ACCESS_TIME` int(11) NOT NULL, `OWNER` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `RETENTION` int(11) NOT NULL, `SD_ID` bigint(20) DEFAULT NULL, `TBL_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `TBL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `VIEW_EXPANDED_TEXT` mediumtext, `VIEW_ORIGINAL_TEXT` mediumtext, `IS_REWRITE_ENABLED` bit(1) NOT NULL, PRIMARY KEY (`TBL_ID`), UNIQUE KEY `UNIQUETABLE` (`TBL_NAME`,`DB_ID`), KEY `TBLS_N50` (`SD_ID`), KEY `TBLS_N49` (`DB_ID`), CONSTRAINT `TBLS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`), CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.448 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` ( `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `CREATE_TIME` int(11) NOT NULL, `GRANT_OPTION` smallint(6) NOT NULL, `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `TBL_ID` bigint(20) DEFAULT NULL, PRIMARY KEY (`TBL_COLUMN_GRANT_ID`), KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), KEY `TBL_COL_PRIVS_N49` (`TBL_ID`), CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.427 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `TBL_PRIVS` ( `TBL_GRANT_ID` bigint(20) NOT NULL, `CREATE_TIME` int(11) NOT NULL, `GRANT_OPTION` smallint(6) NOT NULL, `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `TBL_ID` bigint(20) DEFAULT NULL, PRIMARY KEY (`TBL_GRANT_ID`), KEY `TBL_PRIVS_N49` (`TBL_ID`), KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.402 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `TAB_COL_STATS` ( `CS_ID` bigint(20) NOT NULL, `DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TABLE_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `COLUMN_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TBL_ID` bigint(20) NOT NULL, `LONG_LOW_VALUE` bigint(20), `LONG_HIGH_VALUE` bigint(20), `DOUBLE_HIGH_VALUE` double(53,4), `DOUBLE_LOW_VALUE` double(53,4), `BIG_DECIMAL_LOW_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin, `BIG_DECIMAL_HIGH_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin, `NUM_NULLS` bigint(20) NOT NULL, `NUM_DISTINCTS` bigint(20), `AVG_COL_LEN` double(53,4), `MAX_COL_LEN` bigint(20), `NUM_TRUES` bigint(20), `NUM_FALSES` bigint(20), `LAST_ANALYZED` bigint(20) NOT NULL, PRIMARY KEY (`CS_ID`), CONSTRAINT `TAB_COL_STATS_FK` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.308 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `PART_COL_STATS` ( `CS_ID` bigint(20) NOT NULL, `DB_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `TABLE_NAME` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PARTITION_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `COLUMN_NAME` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `COLUMN_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `PART_ID` bigint(20) NOT NULL, `LONG_LOW_VALUE` bigint(20), `LONG_HIGH_VALUE` bigint(20), `DOUBLE_HIGH_VALUE` double(53,4), `DOUBLE_LOW_VALUE` double(53,4), `BIG_DECIMAL_LOW_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin, `BIG_DECIMAL_HIGH_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin, `NUM_NULLS` bigint(20) NOT NULL, `NUM_DISTINCTS` bigint(20), `AVG_COL_LEN` double(53,4), `MAX_COL_LEN` bigint(20), `NUM_TRUES` bigint(20), `NUM_FALSES` bigint(20), `LAST_ANALYZED` bigint(20) NOT NULL, PRIMARY KEY (`CS_ID`), CONSTRAINT `PART_COL_STATS_FK` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.372 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME) USING BTREE
No rows affected (0.323 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `TYPES` ( `TYPES_ID` bigint(20) NOT NULL, `TYPE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `TYPE1` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `TYPE2` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`TYPES_ID`), UNIQUE KEY `UNIQUE_TYPE` (`TYPE_NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.292 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET @saved_cs_client     = @@character_set_client */
No rows affected (0 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = utf8 */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `TYPE_FIELDS` ( `TYPE_NAME` bigint(20) NOT NULL, `COMMENT` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `FIELD_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `FIELD_TYPE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `INTEGER_IDX` int(11) NOT NULL, PRIMARY KEY (`TYPE_NAME`,`FIELD_NAME`), KEY `TYPE_FIELDS_N49` (`TYPE_NAME`), CONSTRAINT `TYPE_FIELDS_FK1` FOREIGN KEY (`TYPE_NAME`) REFERENCES `TYPES` (`TYPES_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.303 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `MASTER_KEYS` ( `KEY_ID` INTEGER NOT NULL AUTO_INCREMENT, `MASTER_KEY` VARCHAR(767) BINARY NULL, PRIMARY KEY (`KEY_ID`) ) ENGINE=INNODB DEFAULT CHARSET=latin1
No rows affected (0.367 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `DELEGATION_TOKENS` ( `TOKEN_IDENT` VARCHAR(767) BINARY NOT NULL, `TOKEN` VARCHAR(767) BINARY NULL, PRIMARY KEY (`TOKEN_IDENT`) ) ENGINE=INNODB DEFAULT CHARSET=latin1
No rows affected (0.238 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `VERSION` ( `VER_ID` BIGINT NOT NULL, `SCHEMA_VERSION` VARCHAR(127) NOT NULL, `VERSION_COMMENT` VARCHAR(255), PRIMARY KEY (`VER_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.244 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `FUNCS` ( `FUNC_ID` BIGINT(20) NOT NULL, `CLASS_NAME` VARCHAR(4000) CHARACTER SET latin1 COLLATE latin1_bin, `CREATE_TIME` INT(11) NOT NULL, `DB_ID` BIGINT(20), `FUNC_NAME` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_bin, `FUNC_TYPE` INT(11) NOT NULL, `OWNER_NAME` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_bin, `OWNER_TYPE` VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_bin, PRIMARY KEY (`FUNC_ID`), UNIQUE KEY `UNIQUEFUNCTION` (`FUNC_NAME`, `DB_ID`), KEY `FUNCS_N49` (`DB_ID`), CONSTRAINT `FUNCS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.472 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `FUNC_RU` ( `FUNC_ID` BIGINT(20) NOT NULL, `RESOURCE_TYPE` INT(11) NOT NULL, `RESOURCE_URI` VARCHAR(4000) CHARACTER SET latin1 COLLATE latin1_bin, `INTEGER_IDX` INT(11) NOT NULL, PRIMARY KEY (`FUNC_ID`, `INTEGER_IDX`), CONSTRAINT `FUNC_RU_FK1` FOREIGN KEY (`FUNC_ID`) REFERENCES `FUNCS` (`FUNC_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.275 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `NOTIFICATION_LOG` ( `NL_ID` BIGINT(20) NOT NULL, `EVENT_ID` BIGINT(20) NOT NULL, `EVENT_TIME` INT(11) NOT NULL, `EVENT_TYPE` varchar(32) NOT NULL, `DB_NAME` varchar(128), `TBL_NAME` varchar(256), `MESSAGE` longtext, `MESSAGE_FORMAT` varchar(16), PRIMARY KEY (`NL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.259 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `NOTIFICATION_SEQUENCE` ( `NNI_ID` BIGINT(20) NOT NULL, `NEXT_EVENT_ID` BIGINT(20) NOT NULL, PRIMARY KEY (`NNI_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.317 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE IF NOT EXISTS `KEY_CONSTRAINTS` ( `CHILD_CD_ID` BIGINT, `CHILD_INTEGER_IDX` INT(11), `CHILD_TBL_ID` BIGINT, `PARENT_CD_ID` BIGINT NOT NULL, `PARENT_INTEGER_IDX` INT(11) NOT NULL, `PARENT_TBL_ID` BIGINT NOT NULL, `POSITION` BIGINT NOT NULL, `CONSTRAINT_NAME` VARCHAR(400) NOT NULL, `CONSTRAINT_TYPE` SMALLINT(6)  NOT NULL, `UPDATE_RULE` SMALLINT(6), `DELETE_RULE` SMALLINT(6), `ENABLE_VALIDATE_RELY` SMALLINT(6) NOT NULL, PRIMARY KEY (`CONSTRAINT_NAME`, `POSITION`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.291 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE INDEX `CONSTRAINTS_PARENT_TABLE_ID_INDEX` ON KEY_CONSTRAINTS (`PARENT_TBL_ID`) USING BTREE
No rows affected (0.278 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE TXNS ( TXN_ID bigint PRIMARY KEY, TXN_STATE char(1) NOT NULL, TXN_STARTED bigint NOT NULL, TXN_LAST_HEARTBEAT bigint NOT NULL, TXN_USER varchar(128) NOT NULL, TXN_HOST varchar(128) NOT NULL, TXN_AGENT_INFO varchar(128), TXN_META_INFO varchar(128), TXN_HEARTBEAT_COUNT int ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.337 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE TXN_COMPONENTS ( TC_TXNID bigint NOT NULL, TC_DATABASE varchar(128) NOT NULL, TC_TABLE varchar(128) NOT NULL, TC_PARTITION varchar(767), TC_OPERATION_TYPE char(1) NOT NULL, FOREIGN KEY (TC_TXNID) REFERENCES TXNS (TXN_ID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.311 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID)
No rows affected (0.334 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE COMPLETED_TXN_COMPONENTS ( CTC_TXNID bigint NOT NULL, CTC_DATABASE varchar(128) NOT NULL, CTC_TABLE varchar(256), CTC_PARTITION varchar(767) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.269 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE NEXT_TXN_ID ( NTXN_NEXT bigint NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.276 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> INSERT INTO NEXT_TXN_ID VALUES(1)
1 row affected (0.088 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE HIVE_LOCKS ( HL_LOCK_EXT_ID bigint NOT NULL, HL_LOCK_INT_ID bigint NOT NULL, HL_TXNID bigint, HL_DB varchar(128) NOT NULL, HL_TABLE varchar(128), HL_PARTITION varchar(767), HL_LOCK_STATE char(1) not null, HL_LOCK_TYPE char(1) not null, HL_LAST_HEARTBEAT bigint NOT NULL, HL_ACQUIRED_AT bigint, HL_USER varchar(128) NOT NULL, HL_HOST varchar(128) NOT NULL, HL_HEARTBEAT_COUNT int, HL_AGENT_INFO varchar(128), HL_BLOCKEDBY_EXT_ID bigint, HL_BLOCKEDBY_INT_ID bigint, PRIMARY KEY(HL_LOCK_EXT_ID, HL_LOCK_INT_ID), KEY HIVE_LOCK_TXNID_INDEX (HL_TXNID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.359 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE INDEX HL_TXNID_IDX ON HIVE_LOCKS (HL_TXNID)
No rows affected (0.29 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE NEXT_LOCK_ID ( NL_NEXT bigint NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.278 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> INSERT INTO NEXT_LOCK_ID VALUES(1)
1 row affected (0.033 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE COMPACTION_QUEUE ( CQ_ID bigint PRIMARY KEY, CQ_DATABASE varchar(128) NOT NULL, CQ_TABLE varchar(128) NOT NULL, CQ_PARTITION varchar(767), CQ_STATE char(1) NOT NULL, CQ_TYPE char(1) NOT NULL, CQ_TBLPROPERTIES varchar(2048), CQ_WORKER_ID varchar(128), CQ_START bigint, CQ_RUN_AS varchar(128), CQ_HIGHEST_TXN_ID bigint, CQ_META_INFO varbinary(2048), CQ_HADOOP_JOB_ID varchar(32) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.285 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE COMPLETED_COMPACTIONS ( CC_ID bigint PRIMARY KEY, CC_DATABASE varchar(128) NOT NULL, CC_TABLE varchar(128) NOT NULL, CC_PARTITION varchar(767), CC_STATE char(1) NOT NULL, CC_TYPE char(1) NOT NULL, CC_TBLPROPERTIES varchar(2048), CC_WORKER_ID varchar(128), CC_START bigint, CC_END bigint, CC_RUN_AS varchar(128), CC_HIGHEST_TXN_ID bigint, CC_META_INFO varbinary(2048), CC_HADOOP_JOB_ID varchar(32) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.274 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE NEXT_COMPACTION_QUEUE_ID ( NCQ_NEXT bigint NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.32 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> INSERT INTO NEXT_COMPACTION_QUEUE_ID VALUES(1)
1 row affected (0.03 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE AUX_TABLE ( MT_KEY1 varchar(128) NOT NULL, MT_KEY2 bigint NOT NULL, MT_COMMENT varchar(255), PRIMARY KEY(MT_KEY1, MT_KEY2) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.255 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> CREATE TABLE WRITE_SET ( WS_DATABASE varchar(128) NOT NULL, WS_TABLE varchar(128) NOT NULL, WS_PARTITION varchar(767), WS_TXNID bigint NOT NULL, WS_COMMIT_ID bigint NOT NULL, WS_OPERATION_TYPE char(1) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
No rows affected (0.254 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> INSERT INTO VERSION (VER_ID, SCHEMA_VERSION, VERSION_COMMENT) VALUES (1, '2.3.3', 'Hive release version 2.3.3')
1 row affected (0.075 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET character_set_client = @saved_cs_client */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET SQL_MODE=@OLD_SQL_MODE */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */
No rows affected (0.002 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */
No rows affected (0.006 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */
No rows affected (0.001 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
No rows affected (0.003 seconds)
0: jdbc:mysql://ub16043lts00/metastore_db_mys> !closeall
Closing: 0: jdbc:mysql://ub16043lts00/metastore_db_mysql?createDatabaseIfNotExist=true
beeline>
beeline>
Initialization script completed
Wed Sep 13 13:09:27 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
schemaTool completed
hadoopuser@ub16043lts00:~$


==== Verify "mysql" schemaTool creation ====

hadoopuser@ub16043lts00:~$ $HIVE_HOME/bin/schematool -dbType mysql -info -userName root -passWord '1234' -verbose

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-3.0.0-alpha4/share/hadoop/common/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]
Metastore connection URL:     jdbc:mysql://ub16043lts00/metastore_db_mysql?createDatabaseIfNotExist=true
Metastore Connection Driver :     com.mysql.jdbc.Driver
Metastore connection User:     hiveuser
Wed Sep 13 13:10:42 IST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Hive distribution version:     2.3.3
Metastore schema version:     2.3.3
schemaTool completed
hadoopuser@ub16043lts00:~$



8. Start Hiveserver2

hadoopuser@ub16043lts00:~$ hiveserver2
 
2018-02-05 23:25:41: Starting HiveServer2
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-3.0.0/share/hadoop/common/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]
Mon Feb 05 23:25:54 IST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

HiveServer2: (http://machine_hostname:port) -- Default port is 10002




9. Start Beeline (in a separate terminal)

hadoopuser@ub16043lts00:~$ beeline -u jdbc:hive2://ub16043lts00:10000
 
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-3.0.0/share/hadoop/common/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]
Connecting to jdbc:hive2://ub16043lts00:10000
Connected to: Apache Hive (version 2.3.3)
Driver: Hive JDBC (version 2.3.3)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.3.3 by Apache Hive
0: jdbc:hive2://ub16043lts00:10000> 

jdbc:hive2://ub16043lts00:10000> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
+----------------+
1 row selected (0.688 seconds)
0: jdbc:hive2://ub16043lts00:10000>

jdbc:hive2://ub16043lts00:10000> show tables;
+-----------+
| tab_name  |
+-----------+
+-----------+
No rows selected (2.431 seconds)
0: jdbc:hive2://ub16043lts00:10000> 

jdbc:hive2://ub16043lts00:10000> CREATE TABLE tbl_pokes (foo INT, bar STRING);
No rows affected (4.664 seconds)
0: jdbc:hive2://ub16043lts00:10000> show tables;
+------------+
|  tab_name  |
+------------+
| tbl_pokes  |
+------------+
1 row selected (0.314 seconds)
0: jdbc:hive2://ub16043lts00:10000> 

jdbc:hive2://ub16043lts00:10000> CREATE TABLE tbl_invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);
No rows affected (0.966 seconds)

0: jdbc:hive2://ub16043lts00:10000> SHOW TABLES '.*s';
+--------------+
|   tab_name   |
+--------------+
| tbl_invites  |
| tbl_pokes    |
+--------------+
2 rows selected (0.173 seconds)
0: jdbc:hive2://ub16043lts00:10000> 

jdbc:hive2://ub16043lts00:10000> DESCRIBE tbl_invites;
+--------------------------+-----------------------+-----------------------+
|         col_name         |       data_type       |        comment        |
+--------------------------+-----------------------+-----------------------+
| foo                      | int                   |                       |
| bar                      | string                |                       |
| ds                       | string                |                       |
|                          | NULL                  | NULL                  |
| # Partition Information  | NULL                  | NULL                  |
| # col_name               | data_type             | comment               |
|                          | NULL                  | NULL                  |
| ds                       | string                |                       |
+--------------------------+-----------------------+-----------------------+
8 rows selected (0.711 seconds)
0: jdbc:hive2://ub16043lts00:10000> 



List of Beeline queries in HiveServer2:





Default location of Hive tables is at:

hdfs://ub16043lts00:9820/user/hive/warehouse/


Posted by at
Labels: , , , , , ,
Location: Delhi, India

4 comments:

  1. Tejuteju20 August 2018 at 05:53

    It was really a nice article and i was really impressed by reading thiS Big Data Hadoop Online Training Bangalore

    ReplyDelete
  2. Alfred Avina29 January 2020 at 01:15

    As the growth of AWS big data consultant , it is essential to spread knowledge in people. This meetup will work as a burst of awareness.

    ReplyDelete
  3. Padminiprwatech6 March 2020 at 02:18

    You are doing a great job by sharing useful information about Hadoop course. It is one of the post to read and improve my knowledge in Hadoop.You can check our Hadoop multi node cluster setup on ubuntu,tutorial for more information about Hadoop multi node cluster setup.

    ReplyDelete
  4. Kathy15 April 2021 at 01:48

    very nice and provide me informative content thanks for sharing for more information.

    Best Data Migration tools
    Penetration testing companies USA
    What is Data Lake
    Artificial Intelligence in Banking
    What is Data analytics
    Big data Companies USA
    What is Data Migration
    What is Data Science
    Software testing Companies

    ReplyDelete

Subscribe to: Post Comments (Atom)