title: Unable to initialize hive with Derby from Brew install
body: <p>It had been my understanding that <code>Derby</code> creates file(s) in the current directory. But there are none there.</p>
  
  <p>So I had tried to do the <code>hive</code> initialization using <code>Derby</code>: but .. it seems there <em>is</em> a derby database already.</p>
  
  <pre><code> schematool --verbose -initSchema -dbType derby
  
  
  Starting metastore schema initialization to 2.1.0
  Initialization script hive-schema-2.1.0.derby.sql
  Connecting to jdbc:derby:;databaseName=metastore_db;create=true
  Connected to: Apache Derby (version 10.10.2.0 - (1582446))
  Driver: Apache Derby Embedded JDBC Driver (version 10.10.2.0 - (1582446))
  Transaction isolation: TRANSACTION_READ_COMMITTED
  0: jdbc:derby:&gt; !autocommit on
  Autocommit status: true
  0: jdbc:derby:&gt; CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii'
  Error: FUNCTION 'NUCLEUS_ASCII' already exists. (state=X0Y68,code=30000)
  
  Closing: 0: jdbc:derby:;databaseName=metastore_db;create=true
  org.apache.hadoop.hive.metastore.HiveMetaException: 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.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:291)
      at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:264)
      at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:505)
      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:221)
      at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
  Caused by: java.io.IOException: Schema script failed, errorcode 2
      at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:390)
      at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:347)
      at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:287)
  </code></pre>
  
  <p>So .. where is it?</p>
  
  <p><strong>Update</strong>  I have <em>reinstalled hive from scratch</em>  using</p>
  
  <pre><code>  brew reinstall hive
  </code></pre>
  
  <p>And the same error occurs.</p>
  
  <p><strong>Another update</strong>  Given the new direction of this error it now is answered by within another question:  </p>
  
  <p>An answer to a non-os/x - but similar otherwise - question was found that can serve here:</p>
  
  <p><a href="https://stackoverflow.com/a/40017753/1056563">https://stackoverflow.com/a/40017753/1056563</a></p>
  
  <pre><code>I installed hive with HomeBrew(MacOS) at /usr/local/Cellar/hive and afer running schematool -dbType derby -initSchema I get the following error message:
  
  Starting metastore schema initialization to 2.0.0 Initialization script hive-schema-2.0.0.derby.sql Error: FUNCTION 'NUCLEUS_ASCII' already exists. (state=X0Y68,code=30000) org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
  
  However, I can't find either metastore_db or metastore_db.tmp folder under install path, so I tried:
  
  find /usr/ -name hive-schema-2.0.0.derby.sql
  vi /usr/local/Cellar/hive/2.0.1/libexec/scripts/metastore/upgrade/derby/hive-schema-2.0.0.derby.sql
  comment the 'NUCLEUS_ASCII' function and 'NUCLEUS_MATCHES' function
  rerun schematool -dbType derby -initSchema, then everything goes well!
  </code></pre>