Grokbase Groups Hive user June 2011
FAQ

[Hive-user] Rename Hive partition

Jon Bender
Jun 2, 2011 at 4:15 pm
Hey all,

Just wondering what the best way is to rename specific Hive table
partitions. Is there some HiveQL command for this, or will I need to insert
into new partitions to reflect the new naming convention?

Cheers,
Jon
reply

Search Discussions

6 responses

  • Eric Djatsa at Jun 2, 2011 at 5:47 pm
    Hi Jon since a partition appears as a column ( for example with *desc
    table_name* ) you could try renaming the partition as if it was a column as
    explained in the wiki :

    ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name
    column_type [COMMENT col_comment] [FIRST|AFTER column_name]

    I didn't try it myself, and I will like to know if it works I will probably
    need it too ;-)
    Regards,
    Eric


    2011/6/2 Jon Bender <jonathan.bender@gmail.com>
    Hey all,

    Just wondering what the best way is to rename specific Hive table
    partitions. Is there some HiveQL command for this, or will I need to insert
    into new partitions to reflect the new naming convention?

    Cheers,
    Jon
  • Ayon Sinha at Jun 3, 2011 at 11:53 pm
    Hi,
    I am trying to change the metastore from Derby to Mysql and following the directions.
    We changed the hive-site.xml to point to the right jdbc connection URL. The jars are available in the classpath. It seems to pick up the local box's hostname and try default username and password.
    Neither the XML change nor the command line arg is affecting it.

    I have this in xml file
    <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
    </property>

    <property>
    <name>hive.metastore.local</name>
    <value>true</value>
    </property>

    <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://internaldb001.internal.xxxxx.com/hive_metastore</value>
    </property>

    <property>
    <name>javax.job.option.ConnectionUserName</name>
    <value>app</value>
    </property>

    <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>xxxxxxxx</value>
    </property>



    hive -hiveconf  javax.jdo.option.ConnectionURL=jdbc:mysql://internaldb001/hive_metastore -e "show tables"
    Hive history file=/tmp/ayon/hive_job_log_ayon_201106032335_873482984.txt
    FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Access denied for user 'APP'@'dwdb002.internal.xxxxxxx.com' (using password: YES)
    NestedThrowables:
    java.sql.SQLException: Access denied for user 'APP'@'dwdb002.internal.shopkick.com' (using password: YES)
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

    -Ayon
    See My Photos on Flickr
    Also check out my Blog for answers to commonly asked questions.
  • Sanjeev sagar at Jun 3, 2011 at 11:59 pm
    This is your mysql id issue. I'm guessing that your id '*APP*'@'*dwdb002*.
    internal.xxxxxxx.com' domain is not same like you do in mysql user table.

    Try this: show grants for '*APP*'@'*dwdb002*.internal.xxxxxxx.com' and this
    will tell you if you've defined it right.

    Meanwhile you can have a separate hive id like hive@'%' in your mysql for
    testing. And you can mention id and pswd in the hive-site.xml file.

    -Sanjeev
    On Fri, Jun 3, 2011 at 4:52 PM, Ayon Sinha wrote:

    Hi,
    I am trying to change the metastore from Derby to Mysql and following the
    directions.
    We changed the hive-site.xml to point to the right jdbc connection URL. The
    jars are available in the classpath. It seems to pick up the local box's
    hostname and try default username and password.
    Neither the XML change nor the command line arg is affecting it.

    I have this in xml file
    <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
    </property>

    <property>
    <name>hive.metastore.local</name>
    <value>true</value>
    </property>

    <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://*internaldb001*.internal.xxxxx.com/hive_metastore
    </value>
    </property>

    <property>
    <name>javax.job.option.ConnectionUserName</name>
    <value>app</value>
    </property>

    <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>xxxxxxxx</value>
    </property>



    hive -hiveconf javax.jdo.option.ConnectionURL=jdbc:mysql://*internaldb001
    */hive_metastore -e "show tables"
    Hive history file=/tmp/ayon/hive_job_log_ayon_201106032335_873482984.txt
    FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Access
    denied for user '*APP*'@'*dwdb002*.internal.xxxxxxx.com' (using password:
    YES)
    NestedThrowables:
    java.sql.SQLException: Access denied for user 'APP'@'
    dwdb002.internal.shopkick.com' (using password: YES)
    FAILED: Execution Error, return code 1 from
    org.apache.hadoop.hive.ql.exec.DDLTask

    -Ayon
    See My Photos on Flickr <http://www.flickr.com/photos/ayonsinha/>
    Also check out my Blog for answers to commonly asked questions.<http://dailyadvisor.blogspot.com>


    --
    Sanjeev Sagar
  • Ayon Sinha at Jun 4, 2011 at 12:48 am
    Hi Sanjeev,
    I'm not sure what is the mysql id issue. Where is Hive even getting this 'APP'@'dwdb002.internal.xxxxxxx.com' ? We asked it to connect to a machine called internaldb001 with a username 'app'.
    Why is Hive picking up "dwdb02" which is the machine it is running on and 'APP' which is the default username in hive-default.xml ?

    -Ayon
    See My Photos on Flickr
    Also check out my Blog for answers to commonly asked questions.



    ________________________________
    From: sanjeev sagar <sanjeev.sagar@gmail.com>
    To: user@hive.apache.org; Ayon Sinha <ayonsinha@yahoo.com>
    Sent: Friday, June 3, 2011 4:58 PM
    Subject: Re: changing metastore of Hive to MySQL problem


    This is your mysql id issue. I'm guessing that your id 'APP'@'dwdb002.internal.xxxxxxx.com'  domain is not same like you do in mysql user table.

    Try this: show grants for 'APP'@'dwdb002.internal.xxxxxxx.com'  and this will tell you if you've defined it right.

    Meanwhile you can have a separate hive id like hive@'%' in your mysql for testing. And you can mention id and pswd in the hive-site.xml file.

    -Sanjeev


    On Fri, Jun 3, 2011 at 4:52 PM, Ayon Sinha wrote:

    Hi,
    I am trying to change the metastore from Derby to Mysql and following the directions.
    We changed the hive-site.xml to point to the right jdbc connection URL. The jars are available in the classpath. It seems to pick up the local box's hostname and try default username and password.
    Neither the XML change nor the command line arg is affecting it.


    I have this in xml file
    <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
    </property>


    <property>
    <name>hive.metastore.local</name>
    <value>true</value>
    </property>


    <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://internaldb001.internal.xxxxx.com/hive_metastore</value>
    </property>


    <property>
    <name>javax.job.option.ConnectionUserName</name>
    <value>app</value>
    </property>


    <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>xxxxxxxx</value>
    </property>






    hive -hiveconf  javax.jdo.option.ConnectionURL=jdbc:mysql://internaldb001/hive_metastore -e "show tables"
    Hive history file=/tmp/ayon/hive_job_log_ayon_201106032335_873482984.txt
    FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Access denied for user 'APP'@'dwdb002.internal.xxxxxxx.com' (using password: YES)
    NestedThrowables:
    java.sql.SQLException: Access denied for user 'APP'@'dwdb002.internal.shopkick.com' (using password: YES)
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

    -Ayon
    See My Photos on Flickr
    Also check out my Blog for answers to commonly asked questions.



    --
    Sanjeev Sagar
  • Viral Bajaria at Jun 4, 2011 at 3:01 am
    if you plan to use a user from a remote server you need to wild-card that
    user when granting rights on mysql ...

    so just like sanjeev said, you have to make sure that the user APP has the
    rights to connect from any machine and not just the localhost.

    run the command:
    select * from mysql.user where user = 'APP'

    this should show you details about the user APP and whether it is allowed
    access from any remote machine with a wild card '%' option.

    -Viral
    On Fri, Jun 3, 2011 at 5:47 PM, Ayon Sinha wrote:

    Hi Sanjeev,
    I'm not sure what is the mysql id issue. Where is Hive even getting this '
    *APP*'@'*dwdb002*.internal.xxxxxxx.com' ? We asked it to connect to a
    machine called internaldb001 with a username 'app'.
    Why is Hive picking up "dwdb02" which is the machine it is running on and
    'APP' which is the default username in hive-default.xml ?

    -Ayon
    See My Photos on Flickr <http://www.flickr.com/photos/ayonsinha/>
    Also check out my Blog for answers to commonly asked questions.<http://dailyadvisor.blogspot.com>

    ------------------------------
    *From:* sanjeev sagar <sanjeev.sagar@gmail.com>
    *To:* user@hive.apache.org; Ayon Sinha <ayonsinha@yahoo.com>
    *Sent:* Friday, June 3, 2011 4:58 PM
    *Subject:* Re: changing metastore of Hive to MySQL problem

    This is your mysql id issue. I'm guessing that your id '*APP*'@'*dwdb002*.
    internal.xxxxxxx.com' domain is not same like you do in mysql user
    table.

    Try this: show grants for '*APP*'@'*dwdb002*.internal.xxxxxxx.com' and
    this will tell you if you've defined it right.

    Meanwhile you can have a separate hive id like hive@'%' in your mysql for
    testing. And you can mention id and pswd in the hive-site.xml file.

    -Sanjeev

    On Fri, Jun 3, 2011 at 4:52 PM, Ayon Sinha wrote:

    Hi,
    I am trying to change the metastore from Derby to Mysql and following the
    directions.
    We changed the hive-site.xml to point to the right jdbc connection URL. The
    jars are available in the classpath. It seems to pick up the local box's
    hostname and try default username and password.
    Neither the XML change nor the command line arg is affecting it.

    I have this in xml file
    <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
    </property>

    <property>
    <name>hive.metastore.local</name>
    <value>true</value>
    </property>

    <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://*internaldb001*.internal.xxxxx.com/hive_metastore
    </value>
    </property>

    <property>
    <name>javax.job.option.ConnectionUserName</name>
    <value>app</value>
    </property>

    <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>xxxxxxxx</value>
    </property>



    hive -hiveconf javax.jdo.option.ConnectionURL=jdbc:mysql://*internaldb001
    */hive_metastore -e "show tables"
    Hive history file=/tmp/ayon/hive_job_log_ayon_201106032335_873482984.txt
    FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Access
    denied for user '*APP*'@'*dwdb002*.internal.xxxxxxx.com' (using password:
    YES)
    NestedThrowables:
    java.sql.SQLException: Access denied for user 'APP'@'
    dwdb002.internal.shopkick.com' (using password: YES)
    FAILED: Execution Error, return code 1 from
    org.apache.hadoop.hive.ql.exec.DDLTask

    -Ayon
    See My Photos on Flickr <http://www.flickr.com/photos/ayonsinha/>
    Also check out my Blog for answers to commonly asked questions.<http://dailyadvisor.blogspot.com>





    --
    Sanjeev Sagar

  • Sun, Michael at Jun 4, 2011 at 4:56 am
    Need help from experts.



    I am running Hadoop 0.20.2 and HBase 0.20.3, Hive 0.6.0. Here are the
    problems I got although CLI works perfectly:



    1. Got issues with following exception when running Hive jdbc
    client; more logging can be found in the attachment;

    2. I tried to use standalone mode but the startup is hanging
    there; there is no log I could find;



    11/06/03 21:18:22 INFO exec.ExecDriver: Adding input file
    hdfs://localhost:9000/user/hive/warehouse/hive_storage

    java.lang.NullPointerException

    at java.lang.Class.isAssignableFrom(Native Method)

    at
    org.apache.hadoop.hive.ql.io.HiveFileFormatUtils.getOutputFormatSubstitu
    te(HiveFileFormatUtils.java:90)

    at
    org.apache.hadoop.hive.ql.plan.PartitionDesc.setOutputFileFormatClass(Pa
    rtitionDesc.java:156)

    at
    org.apache.hadoop.hive.ql.plan.PartitionDesc.getOutputFileFormatClass(Pa
    rtitionDesc.java:150)

    at
    org.apache.hadoop.hive.ql.exec.ExecDriver.addInputPath(ExecDriver.java:1
    217)

    at
    org.apache.hadoop.hive.ql.exec.ExecDriver.addInputPaths(ExecDriver.java:
    1314)

    at
    org.apache.hadoop.hive.ql.exec.ExecDriver.execute(ExecDriver.java:673)

    at
    org.apache.hadoop.hive.ql.exec.ExecDriver.main(ExecDriver.java:1118)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
    a:57)

    at
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
    Impl.java:43)

    at java.lang.reflect.Method.invoke(Method.java:616)

    at org.apache.hadoop.util.RunJar.main(RunJar.java:156)

    Job Submission failed with exception
    'java.lang.NullPointerException(null)'

    11/06/03 21:18:22 ERROR exec.ExecDriver: Job Submission failed with
    exception 'java.lang.NullPointerException(null)'

    java.lang.NullPointerException

    at java.lang.Class.isAssignableFrom(Native Method)

    at
    org.apache.hadoop.hive.ql.io.HiveFileFormatUtils.getOutputFormatSubstitu
    te(HiveFileFormatUtils.java:90)

    at
    org.apache.hadoop.hive.ql.plan.PartitionDesc.setOutputFileFormatClass(Pa
    rtitionDesc.java:156)

    at
    org.apache.hadoop.hive.ql.plan.PartitionDesc.getOutputFileFormatClass(Pa
    rtitionDesc.java:150)

    at
    org.apache.hadoop.hive.ql.exec.ExecDriver.addInputPath(ExecDriver.java:1
    217)

    at
    org.apache.hadoop.hive.ql.exec.ExecDriver.addInputPaths(ExecDriver.java:
    1314)

    at
    org.apache.hadoop.hive.ql.exec.ExecDriver.execute(ExecDriver.java:673)

    at
    org.apache.hadoop.hive.ql.exec.ExecDriver.main(ExecDriver.java:1118)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
    a:57)

    at
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
    Impl.java:43)

    at java.lang.reflect.Method.invoke(Method.java:616)

    at org.apache.hadoop.util.RunJar.main(RunJar.java:156)



    11/06/03 21:18:23 ERROR exec.MapRedTask: Execution failed with exit
    status: 2

    FAILED: Execution Error, return code 2 from
    org.apache.hadoop.hive.ql.exec.MapRedTask

    11/06/03 21:18:23 ERROR ql.Driver: FAILED: Execution Error, return code
    2 from org.apache.hadoop.hive.ql.exec.MapRedTask

    Exception in thread "main" java.sql.SQLException: Query returned
    non-zero code: 9, cause: FAILED: Execution Error, return code 2 from
    org.apache.hadoop.hive.ql.exec.MapRedTask

    at
    org.apache.hadoop.hive.jdbc.HiveStatement.executeQuery(HiveStatement.jav
    a:197)

    at HiveJdbcClient.retrieveData(HiveJdbcClient.java:98)

    at HiveJdbcClient.main(HiveJdbcClient.java:125)







    Thanks



    Michael Sun

    User Profile Services, Sears Online

    e: ssun03@searshc.com | o:
    847-286-6203 | c: 224-400-7573



    From: Ayon Sinha
    Sent: Friday, June 03, 2011 7:48 PM
    To: sanjeev sagar; user@hive.apache.org
    Subject: Re: changing metastore of Hive to MySQL problem



    Hi Sanjeev,

    I'm not sure what is the mysql id issue. Where is Hive even getting this
    'APP'@'dwdb002.internal.xxxxxxx.com <http://internal.xxxxxxx.com/> ' ?
    We asked it to connect to a machine called internaldb001 with a username
    'app'.

    Why is Hive picking up "dwdb02" which is the machine it is running on
    and 'APP' which is the default username in hive-default.xml ?



    -Ayon
    See My Photos on Flickr <http://www.flickr.com/photos/ayonsinha/>
    Also check out my Blog for answers to commonly asked questions.
    <http://dailyadvisor.blogspot.com>

    ________________________________

    From: sanjeev sagar <sanjeev.sagar@gmail.com>
    To: user@hive.apache.org; Ayon Sinha <ayonsinha@yahoo.com>
    Sent: Friday, June 3, 2011 4:58 PM
    Subject: Re: changing metastore of Hive to MySQL problem

    This is your mysql id issue. I'm guessing that your id 'APP'@'dwdb002.
    internal.xxxxxxx.com <http://internal.xxxxxxx.com/> ' domain is not
    same like you do in mysql user table.



    Try this: show grants for 'APP'@'dwdb002.internal.xxxxxxx.com
    <http://internal.xxxxxxx.com/> ' and this will tell you if you've
    defined it right.



    Meanwhile you can have a separate hive id like hive@'%' in your mysql
    for testing. And you can mention id and pswd in the hive-site.xml file.



    -Sanjeev

    On Fri, Jun 3, 2011 at 4:52 PM, Ayon Sinha wrote:

    Hi,

    I am trying to change the metastore from Derby to Mysql and following
    the directions.

    We changed the hive-site.xml to point to the right jdbc connection URL.
    The jars are available in the classpath. It seems to pick up the local
    box's hostname and try default username and password.

    Neither the XML change nor the command line arg is affecting it.



    I have this in xml file

    <property>

    <name>javax.jdo.option.ConnectionDriverName</name>

    <value>com.mysql.jdbc.Driver</value>

    <description>Driver class name for a JDBC metastore</description>

    </property>



    <property>

    <name>hive.metastore.local</name>

    <value>true</value>

    </property>



    <property>

    <name>javax.jdo.option.ConnectionURL</name>

    <value>jdbc:mysql://internaldb001.
    internal.xxxxx.com/hive_metastore</value>

    </property>



    <property>

    <name>javax.job.option.ConnectionUserName</name>

    <value>app</value>

    </property>



    <property>

    <name>javax.jdo.option.ConnectionPassword</name>

    <value>xxxxxxxx</value>

    </property>







    hive -hiveconf
    javax.jdo.option.ConnectionURL=jdbc:mysql://internaldb001/hive_metastore
    -e "show tables"

    Hive history file=/tmp/ayon/hive_job_log_ayon_201106032335_873482984.txt

    FAILED: Error in metadata: javax.jdo.JDOFatalDataStoreException: Access
    denied for user 'APP'@'dwdb002.internal.xxxxxxx.com' (using password:
    YES)

    NestedThrowables:

    java.sql.SQLException: Access denied for user 'APP'@'
    dwdb002.internal.shopkick.com' (using password: YES)

    FAILED: Execution Error, return code 1 from
    org.apache.hadoop.hive.ql.exec.DDLTask



    -Ayon
    See My Photos on Flickr <http://www.flickr.com/photos/ayonsinha/>
    Also check out my Blog for answers to commonly asked questions.
    <http://dailyadvisor.blogspot.com>






    --
    Sanjeev Sagar

Related Discussions

Discussion Navigation
viewthread | post