I&#39;m interested in finding out how the recently-released (http://mirror.facebook.com/facebook/hive/hadoop-0.17/) Hive compares to HBase in terms of performance. The SQL-like interface used by Hive is very much preferable to the HBase API we have implemented.

How does Hive compare to HBase?

I am looking for a Built-in UDF to convert values of a string column to integer in my hive table for sorting using SELECT and ORDER BY. I searched in the Language Manual, but no use. Any other suggestions also welcome.

Hive: Convert String to Integer

I am looking for a in-built String split function in Hive? e.g. if String is:

```
A|B|C|D|E
```

Then I want to have a function like:

```
array&lt;string&gt; split(string input, char delimiter)
```

So that I get back:

```
[A,B,C,D,E]
```

Does such a in-built split function exist in Hive.

I can only see `regexp_extract` and `regexp_replace`. I would love to see a `indexOf()` and `split()` string functions.

Does Hive have a String split function?

I was trying to build hive-0.13.

When using `-Dmaven.test.skip=true`, it will not build the test jars but it will check test dependency.

When using `-DskipTests`, it will not build the test jars and also not check test dependency.

What&#39;s the difference between `-DskipTests` and `-Dmaven.test.skip=true`?

What&#39;s the difference between -DskipTests and -Dmaven.test.skip=true

I know that you can get column names from a table via the following trick in hive: 

    hive&gt; set hive.cli.print.header=true;
    hive&gt; select * from tablename;

Is it also possible to **just** get the column names from the table? 

I dislike having to change a setting for something I only need once. 

My current solution is the following: 

    hive&gt; set hive.cli.print.header=true;
    hive&gt; select * from tablename;
    hive&gt; set hive.cli.print.header=false;

This seems too verbose and against the DRY-principle. 


Just get column names from hive table

I have Hadoop 2.7.1 and apache-hive-1.2.1 versions installed on ubuntu 14.0. 

   1. Why this error is occurring ?
   2. Is any metastore installation required?
   3. When we typing hive command on terminal how the xml&#39;s internally called, what is the flow of those xml&#39;s?
   4. Any other configuration&#39;s required?
    
     
    
When I am writing the hive command on ubuntu 14.0 terminal it is throwing the below exception.
    
   

     $ hive
        
        Logging initialized using configuration in jar:file:/usr/local/hive/apache-hive-1.2.1-bin/lib/hive-common-1.2.1.jar!/hive-log4j.properties
        Exception in thread &quot;main&quot; java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
        	at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:522)
        	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:677)
        	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
        	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:520)
        	at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
        	at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
        Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
        	at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1523)
        	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.&lt;init&gt;(RetryingMetaStoreClient.java:86)
        	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:132)
        	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:104)
        	at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3005)
        	at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3024)
        	at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:503)
        	... 8 more
        Caused by: java.lang.reflect.InvocationTargetException
        	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        	at java.lang.reflect.Constructor.newInstance(Constructor.java:426)
        	at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1521)
        	... 14 more
        Caused by: javax.jdo.JDOFatalInternalException: Error creating transactional connection factory
        NestedThrowables:
        java.lang.reflect.InvocationTargetException
        	at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:587)
        	at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:788)
        	at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.createPersistenceManagerFactory(JDOPersistenceManagerFactory.java:333)
        	at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.getPersistenceManagerFactory(JDOPersistenceManagerFactory.java:202)
        	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:520)
        	at javax.jdo.JDOHelper$16.run(JDOHelper.java:1965)
        	at java.security.AccessController.doPrivileged(Native Method)
        	at javax.jdo.JDOHelper.invoke(JDOHelper.java:1960)
        	at javax.jdo.JDOHelper.invokeGetPersistenceManagerFactoryOnImplementation(JDOHelper.java:1166)
        	at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:808)
        	at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:701)
        	at org.apache.hadoop.hive.metastore.ObjectStore.getPMF(ObjectStore.java:365)
        	at org.apache.hadoop.hive.metastore.ObjectStore.getPersistenceManager(ObjectStore.java:394)
        	at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:291)
        	at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:258)
        	at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:76)
        	at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136)
        	at org.apache.hadoop.hive.metastore.RawStoreProxy.&lt;init&gt;(RawStoreProxy.java:57)
        	at org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:66)
        	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:593)
        	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:571)
        	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:624)
        	at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:461)
        	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.&lt;init&gt;(RetryingHMSHandler.java:66)
        	at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:72)
        	at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:5762)
        	at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.&lt;init&gt;(HiveMetaStoreClient.java:199)
        	at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.&lt;init&gt;(SessionHiveMetaStoreClient.java:74)
        	... 19 more
        Caused by: java.lang.reflect.InvocationTargetException
        	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        	at java.lang.reflect.Constructor.newInstance(Constructor.java:426)
        	at org.datanucleus.plugin.NonManagedPluginRegistry.createExecutableExtension(NonManagedPluginRegistry.java:631)
        	at org.datanucleus.plugin.PluginManager.createExecutableExtension(PluginManager.java:325)
        	at org.datanucleus.store.AbstractStoreManager.registerConnectionFactory(AbstractStoreManager.java:282)
        	at org.datanucleus.store.AbstractStoreManager.&lt;init&gt;(AbstractStoreManager.java:240)
        	at org.datanucleus.store.rdbms.RDBMSStoreManager.&lt;init&gt;(RDBMSStoreManager.java:286)
        	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        	at java.lang.reflect.Constructor.newInstance(Constructor.java:426)
        	at org.datanucleus.plugin.NonManagedPluginRegistry.createExecutableExtension(NonManagedPluginRegistry.java:631)
        	at org.datanucleus.plugin.PluginManager.createExecutableExtension(PluginManager.java:301)
        	at org.datanucleus.NucleusContext.createStoreManagerForProperties(NucleusContext.java:1187)
        	at org.datanucleus.NucleusContext.initialise(NucleusContext.java:356)
        	at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:775)
        	... 48 more
        Caused by: org.datanucleus.exceptions.NucleusException: Attempt to invoke the &quot;BONECP&quot; plugin to create a ConnectionPool gave an error : The specified datastore driver (&quot;com.mysql.jdbc.Driver&quot;) was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
        	at org.datanucleus.store.rdbms.ConnectionFactoryImpl.generateDataSources(ConnectionFactoryImpl.java:259)
        	at org.datanucleus.store.rdbms.ConnectionFactoryImpl.initialiseDataSources(ConnectionFactoryImpl.java:131)
        	at org.datanucleus.store.rdbms.ConnectionFactoryImpl.&lt;init&gt;(ConnectionFactoryImpl.java:85)
        	... 66 more
        Caused by: org.datanucleus.store.rdbms.connectionpool.DatastoreDriverNotFoundException: The specified datastore driver (&quot;com.mysql.jdbc.Driver&quot;) was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
        	at org.datanucleus.store.rdbms.connectionpool.AbstractConnectionPoolFactory.loadDriver(AbstractConnectionPoolFactory.java:58)
        	at org.datanucleus.store.rdbms.connectionpool.BoneCPConnectionPoolFactory.createConnectionPool(BoneCPConnectionPoolFactory.java:54)
        	at org.datanucleus.store.rdbms.ConnectionFactoryImpl.generateDataSources(ConnectionFactoryImpl.java:238)
        	... 68 more
    

    
To avoid above error I created hive-site.xml with :
    
   

     &lt;configuration&gt;
        
          &lt;property&gt;
            &lt;name&gt;hive.metastore.warehouse.dir&lt;/name&gt;
            &lt;value&gt;/home/local/hive-metastore-dir/warehouse&lt;/value&gt;
            
          &lt;/property&gt;
        
        &lt;property&gt;
          &lt;name&gt;javax.jdo.option.ConnectionURL&lt;/name&gt;
          &lt;value&gt;jdbc:mysql://localhost:3306/hivedb?createDatabaseIfNotExist=true&lt;/value&gt;
        &lt;/property&gt;
        &lt;property&gt;
          &lt;name&gt;javax.jdo.option.ConnectionDriverName&lt;/name&gt;
          &lt;value&gt;com.mysql.jdbc.Driver&lt;/value&gt;
        &lt;/property&gt;
        &lt;property&gt;
          &lt;name&gt;javax.jdo.option.ConnectionUserName&lt;/name&gt;
          &lt;value&gt;user&lt;/value&gt;
        &lt;/property&gt;
        &lt;property&gt;
          &lt;name&gt;javax.jdo.option.ConnectionPassword&lt;/name&gt;
          &lt;value&gt;password&lt;/value&gt;
        &lt;/property&gt;
        
        &lt;configuration&gt;
    
Also provided the environment variables in `~/.bashrc file`; Still the error persist
    
    #HIVE home directory configuration
    export HIVE_HOME=/usr/local/hive/apache-hive-1.2.1-bin
    export PATH=&quot;$PATH:$HIVE_HOME/bin&quot;
    
    

java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

When load data from HDFS to Hive, using

    LOAD DATA INPATH &#39;hdfs_file&#39; INTO TABLE tablename;

command, it looks like it is moving the hdfs_file to `hive/warehouse` dir.
Is it possible (How?) to copy it  instead of moving it, in order, for the file, to be used by another process.

How to load data to hive from HDFS without removing the source file?

I am trying to load a CSV file into a Hive table like so:

    CREATE TABLE mytable
    (
    num1 INT,
    text1 STRING,
    num2 INT,
    text2 STRING
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY &quot;,&quot;;

    LOAD DATA LOCAL INPATH &#39;/data.csv&#39;
    OVERWRITE INTO TABLE mytable;    
&lt;br /&gt;
The csv is delimited by an comma (,) and looks like this:

    1, &quot;some text, with comma in it&quot;, 123, &quot;more text&quot;

This will return corrupt data since there is a &#39;,&#39; in the first string.&lt;br/&gt;
Is there a way to set an text delimiter or make Hive ignore the &#39;,&#39; in strings?

I can&#39;t change the delimiter of the csv since it gets pulled from an external source.

Hive load CSV with commas in quoted fields

https://cwiki.apache.org/confluence/display/Hive/HiveClient#HiveClient-Python appears to be outdated.

When I add this to /etc/profile:

    export PYTHONPATH=$PYTHONPATH:/usr/lib/hive/lib/py

I can then do the imports as listed in the link, with the exception of `from hive import ThriftHive` which actually need to be:

    from hive_service import ThriftHive

Next the port in the example was 10000, which when I tried caused the program to hang. The default Hive Thrift port is 9083, which stopped the hanging.

So I set it up like so:

    from thrift import Thrift
    from thrift.transport import TSocket
    from thrift.transport import TTransport
    from thrift.protocol import TBinaryProtocol
    try:
        transport = TSocket.TSocket(&#39;&lt;node-with-metastore&gt;&#39;, 9083)
        transport = TTransport.TBufferedTransport(transport)
        protocol = TBinaryProtocol.TBinaryProtocol(transport)
        client = ThriftHive.Client(protocol)
        transport.open()
        client.execute(&quot;CREATE TABLE test(c1 int)&quot;)

        transport.close()
    except Thrift.TException, tx:
        print &#39;%s&#39; % (tx.message)

I received the following error:

    Traceback (most recent call last):
    File &quot;&lt;stdin&gt;&quot;, line 1, in &lt;module&gt;
    File &quot;/usr/lib/hive/lib/py/hive_service/ThriftHive.py&quot;, line 68, in execute
    self.recv_execute()
    File &quot;/usr/lib/hive/lib/py/hive_service/ThriftHive.py&quot;, line 84, in recv_execute
    raise x
    thrift.Thrift.TApplicationException: Invalid method name: &#39;execute&#39;

But inspecting the ThriftHive.py file reveals the method execute within the Client class.

How may I use Python to access Hive?


How to Access Hive via Python?

My background - 4 weeks old in the Hadoop world. Dabbled a bit in Hive, Pig and Hadoop using Cloudera&#39;s Hadoop VM. Have read Google&#39;s paper on Map-Reduce and GFS ([PDF link][1]).

I understand that-

 - Pig&#39;s language Pig Latin is a shift
   from(suits the way programmers think)
   SQL like declarative style of
   programming and Hive&#39;s query language closely
   resembles SQL. 

 - Pig sits on top of Hadoop and in
   principle can also sit on top of
   Dryad. I might be wrong but Hive is
   closely coupled to Hadoop. 

 - Both Pig Latin and Hive commands
   compiles to Map and Reduce jobs.

My question - What is the goal of having both when one (say Pig) could serve the purpose. Is it just because Pig is evangelized by Yahoo! and Hive by Facebook?


 


  [1]: http://static.googleusercontent.com/media/research.google.com/en//archive/mapreduce-osdi04.pdf

Difference between Pig and Hive? Why have both?

What are the benefits of using either **Hadoop** or **HBase** or **Hive** ?

From my understanding, **HBase** avoids using map-reduce and has a column oriented storage on top of HDFS. **Hive** is a sql-like interface for **Hadoop** and **HBase**. 

I would also like to know how **Hive** compares with **Pig**.




When to use Hadoop, HBase, Hive and Pig?

I know both is performed on a column in the table but how is each operation different.

What is the difference between partitioning and bucketing a table in Hive ?

Can anyone tell me the difference between Hive&#39;s external table and internal tables.
I know the difference comes when dropping the table. I don&#39;t understand what you mean by the data and metadata is deleted in internal and only metadata is deleted in external tables.
Can anyone explain me in terms of nodes please.

Difference between Hive internal tables and external tables?

I&#39;m looking for the SQL equivalent of `SET varname = value` in Hive QL

I know I can do something like this: 

    SET CURRENT_DATE = &#39;2012-09-16&#39;;
    SELECT * FROM foo WHERE day &gt;= @CURRENT_DATE

But then I get this error:

&gt; character &#39;@&#39; not supported here



How to set variables in HIVE scripts

Assuming you have &quot;table&quot; already in Hive, is there a quick way like other databases to be able to get the &quot;CREATE&quot; statement for that table?

How to get/generate the create statement for an existing hive table?

What is the difference between an `INNER JOIN` and `LEFT SEMI JOIN`? 

In the scenario below, why am I getting two different results? 

The `INNER JOIN` result set is a lot larger. Can someone explain? I am trying to get the names within `table_1` that only appear in `table_2`.

    SELECT name
    FROM table_1 a
        INNER JOIN table_2 b ON a.name=b.name

    SELECT name
    FROM table_1 a
        LEFT SEMI JOIN table_2 b ON (a.name=b.name)

Difference between INNER JOIN and LEFT SEMI JOIN

Is it possible to save `DataFrame` in spark directly to Hive?

I have tried with converting `DataFrame` to `Rdd` and then saving as a text file and then loading in hive. But I am wondering if I can directly save `dataframe` to hive



How to save DataFrame directly to Hive?

How can I find which Hive version I am using from the command prompt. Below is the details-

I am using Putty to connect to hive table and access records in the tables. So what I did is- I opened Putty and in the host name I typed- `leo-ingesting.vip.name.com` and then I click `Open`. And then I entered my username and password and then few commands to get to Hive sql. Below is the list what I did

    $ bash
    bash-3.00$ hive
    Hive history file=/tmp/rkost/hive_job_log_rkost_201207010451_1212680168.txt
    hive&gt; set mapred.job.queue.name=hdmi-technology;
    hive&gt; select * from table LIMIT 1;

So is there any way from the command prompt I can find which hive version I am using and Hadoop version too?

How to know Hive and Hadoop versions from command prompt?

I am running a few tests on the storage formats available with Hive and using Parquet and ORC as major options. I included ORC once with default compression and once with Snappy.

I have read many a documents that state Parquet to be better in time/space complexity as compared to ORC but my tests are opposite to the documents I went through.

Follows some details of my data.

    Table A- Text File Format- 2.5GB
    
    Table B - ORC - 652MB
    
    Table C - ORC with Snappy - 802MB
    
    Table D - Parquet - 1.9 GB

Parquet was worst as far as compression for my table is concerned.

My tests with the above tables yielded following results.

**Row count operation**

    Text Format Cumulative CPU - 123.33 sec
    
    Parquet Format Cumulative CPU - 204.92 sec
    
    ORC Format Cumulative CPU - 119.99 sec 
    
    ORC with SNAPPY Cumulative CPU - 107.05 sec


**Sum of a column operation**

    Text Format Cumulative CPU - 127.85 sec   
    
    Parquet Format Cumulative CPU - 255.2 sec   
    
    ORC Format Cumulative CPU - 120.48 sec   
    
    ORC with SNAPPY Cumulative CPU - 98.27 sec

   

**Average of a column operation**

    Text Format Cumulative CPU - 128.79 sec
    
    Parquet Format Cumulative CPU - 211.73 sec    
    
    ORC Format Cumulative CPU - 165.5 sec   
    
    ORC with SNAPPY Cumulative CPU - 135.45 sec 

  
**Selecting 4 columns from a given range using where clause**

    Text Format Cumulative CPU -  72.48 sec 
    
    Parquet Format Cumulative CPU - 136.4 sec       
    
    ORC Format Cumulative CPU - 96.63 sec 
    
    ORC with SNAPPY Cumulative CPU - 82.05 sec 



Does that mean ORC is faster then Parquet? Or there is something that I can do to make it work better with query response time and compression ratio?

Thanks!   



Parquet vs ORC vs ORC with Snappy

After adding a partition to an **external table** in **Hive**, how can I update/drop it?

How to Update/Drop a Hive Partition?

we would like to put the results of a Hive query to a CSV file. I thought the command should look like this:

    insert overwrite directory &#39;/home/output.csv&#39; select books from table;

When I run it, it says it completeld successfully but I can never find the file. How do I find this file or should I be extracting the data in a different way?

How do I output the results of a HiveQL query to CSV?

How do we get the current system date in Hive? In MySQL we have select now(), can any one please help me to get the query results. I am very new to Hive, is there a proper documentation for Hive that gives the details information about the pseudo columns, and built-in functions.

How to select current date in Hive SQL

I&#39;d like to know how to find the mapping between Hive tables and the actual HDFS files (or rather, directories) that they represent. I need to access the table files directly.

Where does Hive store its files in HDFS?

Where does Hive store files in HDFS?

I have installed Hadoop, Hive, Hive JDBC. which are running fine for me. But I still have a problem. How to delete or update a single record using Hive because delete or update command of MySQL is not working in Hive.

Thanks 

    hive&gt; delete from student where id=1;
    Usage: delete [FILE|JAR|ARCHIVE] &lt;value&gt; [&lt;value&gt;]*
    Query returned non-zero code: 1, cause: null


How to delete and update a record in Hive

In Hive, when we do a query (like:  `select * from employee`), we do not get any column names in the output (like name, age, salary that we would get in **RDBMS SQL**), we only get the values.
 
Is there any way to get the column names to be displayed along with the output when you execute any query?

Is there any way to get the column name along with the output while execute any query in Hive?

I am getting:

    FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask

While trying to make a copy of a partitioned table using the commands in the hive console:

    CREATE TABLE copy_table_name LIKE table_name;
    INSERT OVERWRITE TABLE copy_table_name PARTITION(day) SELECT * FROM table_name;

I initially got some semantic analysis errors and had to set:

    set hive.exec.dynamic.partition=true
    set hive.exec.dynamic.partition.mode=nonstrict

Although I&#39;m not sure what the above properties do? 

Full ouput from hive console:


    Total MapReduce jobs = 1
    Launching Job 1 out of 1
    Number of reduce tasks determined at compile time: 1
    In order to change the average load for a reducer (in bytes):
      set hive.exec.reducers.bytes.per.reducer=&lt;number&gt;
    In order to limit the maximum number of reducers:
      set hive.exec.reducers.max=&lt;number&gt;
    In order to set a constant number of reducers:
      set mapred.reduce.tasks=&lt;number&gt;
    Starting Job = job_201206191101_4557, Tracking URL = http://jobtracker:50030/jobdetails.jsp?jobid=job_201206191101_4557
    Kill Command = /usr/lib/hadoop/bin/hadoop job  -Dmapred.job.tracker=master:8021 -kill job_201206191101_4557
    2012-06-25 09:53:05,826 Stage-1 map = 0%,  reduce = 0%
    2012-06-25 09:53:53,044 Stage-1 map = 100%,  reduce = 100%
    Ended Job = job_201206191101_4557 with errors
    FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask



What is Hive: Return Code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask

I have a table with 1000+ partitions. 

&quot;`Show partitions`&quot; command only lists a small number of partitions.

How can i show all partitions?

Update:

1. I found &quot;`show partitions`&quot; command only lists exactly 500 partitions.

2. &quot;`select ... where ...`&quot; only processes the 500 partitions!


Hive: how to show all partitions of a table?

I have created a table in hive, I would like to know which directory my table is created in? I would like to know the path...

I have created a table in hive, I would like to know which directory my table is created in?

I&#39;m trying to write a non-trivial Hive job using the Hive Thrift and JDBC interfaces, and I&#39;m having trouble setting up a decent JUnit test. By non-trivial, I mean that the job results in at least one MapReduce stage, as opposed to only dealing with the metastore.

The test should fire up a Hive server, load some data into a table, run some non-trivial query on that table, and check the results.

I&#39;ve wired up a Spring context according to the [Spring reference][1]. However, the job fails on the MapReduce phase, complaining that no Hadoop binary exists:


&gt; java.io.IOException: Cannot run program &quot;/usr/bin/hadoop&quot; (in
&gt; directory &quot;/Users/yoni/opower/workspace/intellij_project_root&quot;):
&gt; error=2, No such file or directory



The problem is that the Hive Server is running in-memory, but relies upon local installation of Hive in order to run. For my project to be self-contained, I need the Hive services to be embedded, including the HDFS and MapReduce clusters. I&#39;ve tried starting up a Hive server using the same Spring method and pointing it at [MiniDFSCluster][2] and [MiniMRCluster][3], similar to the pattern used in the Hive [QTestUtil][4] source and in [HBaseTestUtility][5]. However, I&#39;ve not been able to get that to work.

After three days of trying to wrangle Hive integration testing, I thought I&#39;d ask the community: 

 1. How do you recommend I integration test Hive jobs?
 2. Do you have a working JUnit example for integration testing Hive jobs using in-memory HDFS, MR, and Hive instances?

Additional resources I&#39;ve looked at:

* [Hive Unit Testing tutorial][6]
* [the Spring Hive example][7]


  [1]: http://static.springsource.org/spring-hadoop/docs/1.0.0.RELEASE/reference/html/hive.html &quot;Spring Hive context&quot;
  [2]: http://javasourcecode.org/html/open-source/hadoop/hadoop-0.20.203.0/org/apache/hadoop/hdfs/MiniDFSCluster.html
  [3]: http://javasourcecode.org/html/open-source/hadoop/hadoop-0.20.2/org/apache/hadoop/mapred/MiniMRCluster.html
  [4]: http://hive.apache.org/docs/r0.9.0/api/org/apache/hadoop/hive/ql/QTestUtil.html
  [5]: http://people.apache.org/~psmith/hbase/sandbox/hbase/hbase-core/testapidocs/org/apache/hadoop/hbase/HBaseTestingUtility.html
  [6]: http://dev.bizo.com/2011/04/hive-unit-testing.html &quot;Unit testing Hive&quot;
  [7]: https://github.com/SpringSource/spring-hadoop-samples/tree/master/samples/hive &quot;Spring Hive example&quot;


Edit:
I am fully aware that working against a Hadoop cluster - whether local or remote - makes it possible to run integration tests against a full-stack Hive instance. The problem, as stated, is that this is not a viable solution for effectively testing Hive workflows.

Integration testing Hive jobs

I am new to hive, and want to know if there is anyway to insert data into Hive table like we do in SQL. I want to insert my data into hive like

    INSERT INTO tablename VALUES (value1,value2..)

I have read that you can load the data from a file to hive table or you can import data from one table to hive table but is there any way to append the data as in SQL?

Hive insert query like SQL

I used this Hive query to export a table into a CSV file.

    INSERT OVERWRITE DIRECTORY &#39;/user/data/output/test&#39; select column1, column2 from table1;

The file generated &#39;000000_0&#39; does not have comma separator

Is this the right way to generate CSV file? If no, please let me know how can I generate the CSV file?

How to export a Hive table into a CSV file?

As far as I understand;

- sort by only sorts with in the reducer

- order by orders things globally but shoves everything into one reducers

- cluster by intelligently distributes stuff into reducers by the key hash and make a sort by

So my question is does cluster by guarantee a global order? distribute by puts the same keys into same reducers but what about the adjacent keys? 

The only document I can find on this is [here][1] and from the example it seems like it orders them globally. But from the definition I feel like it doesn&#39;t always do that.


 [1]:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy

Hive cluster by vs order by vs sort by

I am using Cloudera&#39;s version of Hive and trying to create an external table over a csv file that contains the column names in the first column.  Here is the code that I am using to do that.

    CREATE EXTERNAL TABLE Test ( 
      RecordId int, 
      FirstName string, 
      LastName string 
    ) 
    ROW FORMAT serde &#39;com.bizo.hive.serde.csv.CSVSerde&#39; 
    WITH SerDeProperties ( 	
      &quot;separatorChar&quot; = &quot;,&quot;
    ) 
    STORED AS TEXTFILE 
    LOCATION &#39;/user/File.csv&#39;

Sample Data

    RecordId,FirstName,LastName
    1,&quot;John&quot;,&quot;Doe&quot;
    2,&quot;Jane&quot;,&quot;Doe&quot;

Can anyone help me with how to skip the first row or do I need to add an intermediate step?


How to skip CSV header in Hive External Table?

I am working with Spark and PySpark. I am trying to achieve the result equivalent to the following pseudocode:


&lt;!-- language: python --&gt;

    df = df.withColumn(&#39;new_column&#39;, 
        IF fruit1 == fruit2 THEN 1, ELSE 0. IF fruit1 IS NULL OR fruit2 IS NULL 3.)

I am trying to do this in PySpark but I&#39;m not sure about the syntax. Any pointers? I looked into `expr()` but couldn&#39;t get it to work.

Note that `df` is a `pyspark.sql.dataframe.DataFrame`.

PySpark: withColumn() with two conditions and three outcomes

I have recently started looking into querying large sets of CSV data lying on HDFS using Hive and Impala. As I was expecting, I get better response time with Impala compared to Hive for the queries I have used so far.

I am wondering if there are some types of queries/use cases that still need Hive and where Impala is not a good fit.

How does Impala provide faster query response compared to Hive for the same data on HDFS?

All Hive Solutions on Gang of Coders

Total of 35 Hive Solutions