Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account
List archives
Videos
Answers

Simulating an auto-incrementing column

How are nulls represented in data?
(20 lines)		 Errors while fetching data through Hive.
(35 lines)
Aug 9, 2010
Lars Francke
Lars Francke
 
Hi,

I have a problem and I hope someone has an idea on how to solve it.

My dataset consists of just very simple key-value pairs of strings
coming from PostgreSQL using Sqoop.

1) I need to count how often a key occurs -> Easy
2) I need to count how often a key-value pair occurs -> Easy

I need to output this data to PostgreSQL again, into two tables:

a) "keys" with the columns: id, key_name, count
b) "values" with the columns: id, key_id, value_name, count

Now the ids I'm referring to don't exist yet and I'm looking into
solutions to generate them. They have to be integers/longs but they
don't have to be in any order/pattern. I'm not concerned about
performance either as this query will be run monthly at most.

Do you have any idea how I could introduce this new column into the
output of query 1)? I could easily introduce it into 2) with a join
then. I thought about using a custom reducer script but apart from the
fact that I've never done it so far it would require that there is
only one reducer so that I can simulate an auto-incrementer. My
current best idea is to write a regular MR job that processes the Hive
output but I'd love to do everything in Hive if possible.

I might very well approach this problem completely wrong so don't
hesitate to propose a better solution or bash me for my poor
understanding of Hive :)

Thanks for any input and help.

Cheers,
Lars
Reply
Tags: easyoccurs
Messages in this thread
Simulating an auto-incrementing column
reply Re: Simulating an auto-incrementing column
(54 lines) Aug 10, 2010 01:29
reply Re: Simulating an auto-incrementing column
(20 lines) Aug 10, 2010 04:31
Similar Threads
column="{prop1=col1,prop2=col2} not auto generate a java.util.Map
Hello everybody

When I don't specify the parameterType on the nested select statement
for a association (nested select)  I get this error :

Exception in thread "main"
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause:
org.apache.ibatis.reflection.ReflectionException: There is no setter
for property named 'id' in 'class java.lang.Object'
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: org.apache.ibatis.reflection.ReflectionException: There is
no setter for property named 'id' in 'class java.lang.Object'
	at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
	at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
61)
	at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:
53)
	at
org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:
82)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
63)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)

my mapper.xml

    <resultMap id="result-building" type="beans.Building">
        <id property="id"/>
        <result property="label"/>
    </resultMap>

    <select id="selectBuilding" resultMap="result-building">
        select
            RFBAT00F.EUKLCD as "id",
            RFBAT00F.EUS0TX as "label"
        from BFREF.RFBAT00F
        <where>
            <if test="id != null">
                RFBAT00F.EUKLCD = #{id}
            </if>
        </where>
    </select>

    <resultMap id="result-room" type="beans.Room">
        <id property="id.building" column="buildingId"/>
        <id property="id.floor" column="floorId"/>
        <id property="id.room" column="roomId"/>
        <association property="building" column="{id=buildingId}"
select="selectBuilding" />
    </resultMap>

    <select id="selectAllRoom" resultMap="result-room">
        select
            rfsal00f.evklcd as "buildingId",
            rfsal00f.evkmcd as "floorId",
            rfsal00f.evkncd as "roomId"
        from bfref.rfsal00f
    </select>

I need to add parameterType="java.util.Map" on the select statement
"selectBuilding" to work. On iBatis2 it's not necessary and why
column="{id=building}" not auto generate a java.util.Map  if the
select statement don't have a parameterType ?

Best regards

Duto
Simulating SQL 'LIKE %' using Regular Expressions
Hi, so in my couchDB, what I want to happen is when you query a view in 
the design doc and providing ?key="C" at the end, bring up all shop names 
that start with C.
to do this I have made the following map function:

function(doc) 
{ 

  for (id in doc.Stores)
  {
 
  var strqry = doc.Stores[id]["name"].match(/^.*/);
 
    if (strqry)
    { 
      emit(strqry, doc.product_name + " qty: " + 
doc.Stores[id].item_count);
    }
  } 
}

When I run this from the temp view in Futon, I get a list of all stores 
and products (which made me think 'yay it worked, all it needs is a 
parameter') but when I use:
http://host:5984/db/design/name/_view/function?key="C" I get back: 
{"total_rows":30,"offset":0,"rows":[]}

my ultimate aim is to get it working similar to an SQL Like % so if for 
example I say ?key="C" it will return "Computer Store A" and so on..
I created my function based on this tutorial: 
http://books.couchdb.org/relax/refere...s-for-sql-jockeys
Query on delete a column inside a super column
Hi All,

I have a query related to deleting a column inside a super column

The following is my cassandra schema

[cache_pages_key_hash] => Array
        (
            [hash_1] => Array
                (
                    [1] => 4c330e95195f9
                    [2] => 4c330e951f18b
                    [3] => 4c330e9521f3d
                )

        )


No I wanted to remove the index [1] => 4c330e95195f9 from the
supercolumn [hash_1]

Through phpCassa I am doing the following

$updatedRecord   =  array("hash_1" => Array
                                (
                                    2 => "4c330e951f18b"
                                    3 => "4c330e9521f3d"
                               )
                             )


$cassandraInstance->insert('cache_pages_key_hash',$updateRecord );

But while I fetch the record again

I was getting the original records ie the column 1 is not removed from


[cache_pages_key_hash] => Array
        (
            [hash_1] => Array
                (
                    [1] => 4c330e95195f9
                    [2] => 4c330e951f18b
                    [3] => 4c330e9521f3d
                )

        )


But at the same time If I am updating the index 1

ie
$updateRecord   =  array("hash_1" => Array
                                (
                                   1  => ' able to update'
                                    2 => "4c330e951f18b"
                                    3 => "4c330e9521f3d"
                               )
                             )

$cassandraInstance->insert('cache_pages_key_hash',$updateRecord );

The records is being updated, Only problem is that  deleting dosent
happens.

My question is that is this behavior is expected as explained in the
article
Distributed deletes in the Cassandra database
http://spyced.blogspot.com/2010/02/di...in-cassandra.html

or I am doing wrong.


Thanks,
Moses.
Why derby do not auto commit?
I found that derby do not auto commit data change on my computer, even I
set
auto commit to true explicitly. nothing written to database, unless I call
commit().

java version "1.6.0_19"
Java(TM) SE Runtime Environment (build 1.6.0_19-b04)
Java HotSpot(TM) Client VM (build 16.2-b04, mixed mode, sharing)
derby 10.6.1 embedded driver

Code:
    Connection con =
DriverManager.getConnection("jdbc:derby:D:/test/testdb");
//    con.setAutoCommit(true);
    Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
    ResultSet rst = stm.executeQuery("SELECT * FROM test_table");
    rst.moveToInsertRow();
    rst.updateString(1, "test line");
    rst.insertRow();
    rst.beforeFirst();
    while (rst.next()) {
      System.out.println(rst.getString(1));
    }
    System.out.println(con.getAutoCommit());
//    con.commit();
    con.close();
Auto mapping and performance
At first I started using ibatis almost exclusively with auto mapping
(after I solved the camel case<->underscore problem).

Unfortunately later I had to convert to explicit result maps to
achieve the
performance that was required.
Now I have hundreds of boilerplate lines like these:

    <resultMap id="CompanyMap"
type="com.acme.myapp.dtos.CompanyDto">
        <id column="id" property="id"/>
        <result column="city" property="city"/>
        <result column="contact_person" property="contactPerson"/>
        <result column="country" property="country"/>
        <result column="department" property="department"/>
        <result column="is_customer" property="isCustomer"/>
        <result column="name" property="name"/>
        <result column="sap_debitor_no" property="sapDebitorNo"/>
        <result column="short_name" property="shortName"/>
        <result column="state" property="state"/>
        <result column="street" property="street"/>
        <result column="tlc" property="tlc"/>
        ...

I would very much like to get rid of all these superfluous xml!

IMHO there is no fundamental reason why auto mapping should be
noticeably slower
than explicit result maps. Or am I missing something?

I had a look at what's going on and it seems to me that reflection is
done
much too often. Shouldn't it be sufficient to reflect only once on a
class and
from there on treat it exactly like explicitly mapped?
update auto-generated pk before insert
hi,

I need to determinate the value of an auto-generated PK before the 
INSERT statement. I'm  trying to use
this SQLs:

1) LOCK TABLE sys.
columns FOR EXCLUSIVE READ
2) SELECT referenceid,
autoincrementvalue+autoincrementinc FROM sys.syscolumns AS sc INNER 
JOIN sys.systables AS st ON sc.referenceid=st.tableid WHERE 
tablename='EMAIL2FOLDER' AND columnname='ID';
3) UPDATE sys.syscolumns 
SET autoincrementvalue=autoincrementvalue+autoincrementinc WHERE 
tableid='f91a0030-0129-e07e-88ec-0000016e72a0' AND idColumnName='ID';

4) use autoinc value, from step-4 select, in my INSERT on the 
destination table

but I obtain an error due to the insufficient 
permission of the connected user (derby is used in embedded mode)
for 
the statement 1 and 3.

Is there any way to escale privileges or to 
create a special permission user-function?

TIA



Risparmia con Tutto Incluso Light: telefono + adsl 8 mega a soli 19,95 €
al mese per un anno!

SCONTO DI 120 EURO! 

L’offerta è valida solo se attivi entro il 29/07/10

http://abbonati.tiscali.it/telefono-a...ht/?WT.mc_id=01fw
AUTO: Rob High is out of the office. (returning 07/06/2010)

I am out of the office until 07/06/2010.

I'm on vacation, returning 06-Jul-2010.  In an emergency please contact
Bob
Johnson at 1-914-766-1315 or John Falkl at 1-914-766-1244.


Note: This is an automated response to your message  "Re: [DISCUSSION]
change 2.2 branch build JDK from 6 to 5" sent on 6/17/10 19:59:27].

This is the only notification you will receive while this person is away.
Auto Start up issue when adding RouteDefintions
Apache Camel version 2.4 

The camel Context is already started and im adding route definitions like
below. 

RouteDefinition routeDefinition = (RouteDefinition)
unmarshaller.unmarshal(stringReader); 
ArrayList arrayList = new ArrayList(); 
arrayList.add(routeDefinition);               
camelContext.addRouteDefinitions(arrayList); 

I need to load the routes this way as each route is dynamically loaded
from
xml route files getting submitted to our app. 

Here is a unit test that fails that shows the Issue.

package sandbox; 

import org.apache.activemq.ActiveMQConnectionFactory; 
import org.apache.camel.*; 
import org.apache.camel.component.jms.JmsComponent; 
import org.apache.camel.component.mock.MockEndpoint; 
import org.apache.camel.model.Constants; 
import org.apache.camel.model.RouteDefinition; 
import org.apache.camel.spring.SpringCamelContext; 
import org.apache.camel.test.junit4.CamelTestSupport; 
import org.junit.Assert; 
import org.junit.Before; 
import org.springframework.context.support.GenericApplicationContext; 

import javax.xml.bind.JAXBContext; 
import javax.xml.bind.Unmarshaller; 
import java.net.URL; 
import java.util.ArrayList; 

/** 
 * Unit test for simple App. 
 */ 
public class AppTest extends CamelTestSupport 
{ 
    private ActiveMQConnectionFactory factory; 
    private SpringCamelContext camelContext; 
    @Before 
    public void setUp() throws Exception 
    { 
        
        factory = new ActiveMQConnectionFactory("vm://local"); 
        super.setUp(); 
    } 
    @EndpointInject(uri = "mock:result") 
    protected MockEndpoint resultEndpoint; 

    @Produce(uri = "jms:start") 
    protected ProducerTemplate template; 


    @org.junit.Test 
    public void test() throws Exception 
    { 
        JAXBContext jaxbContext = null; 
        Unmarshaller unmarshaller = null; 
        RouteDefinition input = null; 
        jaxbContext =
JAXBContext.newInstance(Constants.JAXB_CONTEXT_PACKAGES); 
        unmarshaller = jaxbContext.createUnmarshaller(); 
        input = (RouteDefinition) unmarshaller.unmarshal(new
URL("file:input.xml")); 
        Assert.assertTrue(input != null); 
        Assert.assertTrue(context.getStatus() == ServiceStatus.Started); 
        ArrayList<RouteDefinition> routes = new ArrayList(); 
        routes.add(input); 
        resultEndpoint.setExpectedMessageCount(0); 
        camelContext.addRouteDefinitions(routes); 
        template.sendBody("test"); 
        resultEndpoint.setSleepForEmptyTest(1000);         
        resultEndpoint.assertIsSatisfied(); 
    } 


    @Override 
    protected CamelContext createCamelContext() throws Exception 
    { 
        GenericApplicationContext applicationContext = new
GenericApplicationContext(); 
        camelContext = new SpringCamelContext(applicationContext); 
        JmsComponent jmsComponent = new JmsComponent(); 
        jmsComponent.setConnectionFactory(factory); 
       
applicationContext.getBeanFactory().registerSingleton("jms",jmsComponent);

        return camelContext; 
    } 

} 


input.xml 

<?xml version="1.0" encoding="UTF-8"?> 
<route xmlns="http://camel.apache.org/schema/spring"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
       xsi:schemaLocation="http://camel.apache.org/schema/spring
http://camel.apache.org/schema/spring/camel-spring.xsd" 
       id="v_from_app1_app1" autoStartup="false" trace="true"  group="o1"
 
    <from uri="jms:start"/> 
    <to uri="mock:result"/> 
</route> 

pom dependencies 
<dependency> 
      <groupId>junit</groupId> 
      <artifactId>junit</artifactId> 
      <version>4.7</version> 
      <scope>test</scope> 
    </dependency> 
    <dependency> 
      <groupId>org.apache.camel</groupId> 
      <artifactId>camel</artifactId> 
      <version>2.4.0</version> 
    </dependency> 
      <dependency> 
      <groupId>org.apache.camel</groupId> 
      <artifactId>camel-core</artifactId> 
      <version>2.4.0</version> 
    </dependency> 
       <dependency> 
      <groupId>org.apache.camel</groupId> 
      <artifactId>camel-jms</artifactId> 
      <version>2.4.0</version> 
    </dependency> 
       <dependency> 
      <groupId>org.apache.camel</groupId> 
      <artifactId>camel-test</artifactId> 
      <version>2.4.0</version> 
    </dependency> 
     <dependency> 
      <groupId>org.apache.camel</groupId> 
      <artifactId>camel-spring</artifactId> 
      <version>2.4.0</version> 
    </dependency> 
       <dependency> 
      <groupId>org.apache.activemq</groupId> 
      <artifactId>activemq-core</artifactId> 
      <version>5.3.0</version> 
    </dependency> 
  </dependencies> 

Is there an issue or am i doing something wrong
Re: Auto Start up issue when adding RouteDefintions
As you can see the route supplied has autostart = false .

Yet the test receives a message. I should not receive a message until the
route is started.
Unsupported feature for JDBC 3.3 auto generated key
Hi

I am trying to use auto-generated keys feature.

My environment:
Oracle 10g
Driver: 11g JDBC Driver (ojdbc6.jar)

My mapper file looks as below.

<insert useGeneratedKeys="true" id="insert"
parameterType="test.ContactDdo"	keyProperty="contactId">
		insert into SR1CUST1.CONTACT_INFORMATION(CONTACT_TYPE, CONTACT_MODE,
		VALUE)
		values(#{contactType},#{contactMode}, #{value})
	</insert>

But, i try to execute this query I get below error.

Exception in thread "main"
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause:
org.apache.ibatis.executor.ExecutorException: Error getting generated
key or setting result to parameter object. Cause:
java.sql.SQLException: Unsupported feature
### The error may involve CONTACT_INFORMATION.insert-Inline
### The error occurred while setting parameters
### Cause: org.apache.ibatis.executor.ExecutorException: Error getting
generated key or setting result to parameter object. Cause:
java.sql.SQLException: Unsupported feature
	at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
	at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
100)
	at
org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:
87)
	at test.Test.main(Test.java:54)
Caused by: org.apache.ibatis.executor.ExecutorException: Error getting
generated key or setting result to parameter object. Cause:
java.sql.SQLException: Unsupported feature
	at
org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:
53)
	at
org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:
26)
	at
org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:
51)
	at
org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:
29)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:
72)
	at
org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:
43)
	at
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:
98)
	... 2 more
Caused by: java.sql.SQLException: Unsupported feature
	at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:
70)
	at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:
133)
	at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
199)
	at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
263)
	at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
271)
	at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
445)
	at
oracle.jdbc.driver.OracleReturnResultSet.findColumn(OracleReturnResultSet.java:
1341)
	at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:
440)
	at
org.apache.ibatis.type.LongTypeHandler.getNullableResult(LongTypeHandler.java:
17)
	at
org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:
29)
	at
org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:
38)
	... 8 more


Can you please advice me, what is the problem here?

Thanks in advance.

Regards,
Prashant
how to get auto generated key after data is inserted in table
Hi
I have a table with a column id key which is auto generated, is there a
way
in myibatis i can get this auto generated id after inserting data in this
table?
AUTO: Dikran S Meliksetian is out of the office (returning 07/12/2010)

I am out of the office until 07/12/2010.




Note: This is an automated response to your message  "how to get the
current input file in a Mapper" sent on 6/27/10 10:59:46.

This is the only notification you will receive while this person is away.