Hi Vamsi,
Running upsert statements like that will indeed not work in Phoenix
(that grammar isn't supported).
What you're trying to accomplish is technically the same as executing
multiple upsert statements and then committing at the end of the
batch. This can be accomplished by running multiple individual
statements, or (better) creating a single prepared statement and using
the addBatch method to do batching. In any case, by running multiple
upserts and then committing every 1000 records or so, Phoenix will
send the data in batches to HBase, which I believe is the intention of
the multiple value sets in a single insert/upsert statement.
I'm not really familiar with MyBatis -- is it possible/feasible to
used batched PreparedStatements and/or multiple statements between
commits with it?
- Gabriel
On Fri, Dec 19, 2014 at 8:46 PM, Vamsi Krishna <vamsi.attl...@gmail.com> wrote:
> Hi,
>
> I'm trying to do a batch insert using MyBatis & Phoenix and I'm ending up in
> an exception (org.springframework.jdbc.BadSqlGrammarException:).
>
> ---------------------------------------------------------------------------------------------------------------------
>
> Here is an example of what I'm doing:
>
> I have two entities:
> Authors { authorId, firstName, lastName }
> Books { bookId, bookTitle, authorId }
>
> Data:
> Authors: Record-1: 001, john, henry
> Books: Record-1: 001, A database primer, 001
> Books: Record-2: 002, Building a datawarehouse, 001
>
> Model object:
> AuthorsBooksModel { authorId, firstName, lastName, booksList {bookId,
> bookTitle} }
>
> Phoenix table (denormalized):
>
> authorsbooks { authorid, firstname, lastname, bookid, booktitle }
>
> Create script: create table authorsbooks (authorid varchar, firstname
> varchar, lastname varchar, bookid varchar, booktitle varchar, constraint
> ab_pk primary key(authorid, bookid));
>
> Query:
> Using MyBatis batching, I create upsert statement from my nested model
> object (AuthorsBooksModel) which is passed to phoenix:
> upsert into authorsbooks (authorid, firstname, lastname, bookid, booktitle)
> values ('001', 'john', 'henry', '001', 'A database primer'), ('001', 'john',
> 'henry', '002', 'Building a datawarehouse');
>
> The above statement fails in the application with BadSqlGrammarException.
>
> Phoenix command line:
>
> When I execute the same upsert statement directly at Phoenix console, I see
> the following error:
>
> 0: jdbc:phoenix:test.abc.def.com,sfdv> upsert into authorsbooks (authorid,
> firstname, lastname, bookid, booktitle) values ('001', 'john', 'henry',
> '001', 'A database primer'), ('001', 'john', 'henry', '002', 'Building a
> datawarehouse');
>
> Error: ERROR 602 (42P00): Syntax error. Missing "EOF" at line 1, column 136.
> (state=42P00,code=602)
>
> ---------------------------------------------------------------------------------------------------------------------
>
> The same scenario works well with MyBatis - PostgreSQL.
> Create script: create table authorsbooks (authorid varchar, firstname
> varchar, lastname varchar, bookid varchar, booktitle varchar, constraint
> ab_pk primary key(authorid, bookid));
> Insert: insert into authorsbooks (authorid, firstname, lastname, bookid,
> booktitle) values ('001', 'john', 'henry', '001', 'A database primer'),
> ('001', 'john', 'henry', '002', 'Building a datawarehouse');
>
> ---------------------------------------------------------------------------------------------------------------------
>
> In my realtime application when I denormalize my nested model object I will
> have hundreds of thousands of records and executing one statement at a time
> from MyBatis to Phoenix is going to cause a network concern.
>
> Can someone please take a look at the above scenario and help me fix it or
> suggest me an alternative?
>
> Thanks,
> Vamsi Attluri.
