phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "M. Aaron Bossert" <maboss...@gmail.com>
Subject Re: inconsistent commit behavior when using JDBC
Date Thu, 23 May 2019 02:38:15 GMT
Folks, a quick update.  It appears that I am not the only one to bump into
this issue.  There is an old question about this behavior that seems to
suggest that it is related specifically to columns updated with a NULL
value.
https://dba.stackexchange.com/questions/208544/apache-phoenix-upsert-not-working-while-trying-to-update-null-empty-columns

Unfortunately, the stackexchange question never got an answer, so not much
help.

On Wed, May 22, 2019 at 1:10 PM M. Aaron Bossert <mabossert@gmail.com>
wrote:

> I am using Phoenix 5 as shipped with Hortonworks HDP 3.1.  I am storing 3+
> million file names in a table and then using the table to keep track of
> which files I have processed using a Storm topology.  I have been doing
> some testing to make sure that everything is working correctly and as part
> of that, tried my code on single files to ensure that columns were being
> updated as expected and that my logic was working properly.
>
> I have found that when I do an UPSERT to set a column to the current
> timestamp (now()) the first time, it works as expected, then I reset that
> column to NULL so that I can redo the test, but find that the exact same
> UPSERT statement fails to make a change even though I get a response that
> confirms one record was affected.
>
> In order to eliminate potential red herrings, here is an example of the
> problem that is reproducible using just the phoenix-sqlline interface (I
> get same exact behavior when running equivalent Scala code).
>
> Here is the DDL for the table:
>
> CREATE TABLE DEFAULT.FILE_INDEX(
>     MTIME TIMESTAMP NOT NULL,
>     FILENAME VARCHAR NOT NULL,
>     TYPE VARCHAR NOT NULL,
>     SUBTYPE VARCHAR NOT NULL,
>     SENSOR VARCHAR NOT NULL,
>     SIZE BIGINT NOT NULL,
>     OWNER VARCHAR NOT NULL,
>     GROUP_OWNER VARCHAR NOT NULL,
>     PERMISSIONS VARCHAR NOT NULL,
>     STARTED TIMESTAMP,
>     PROCESSED TIMESTAMP,
>     EVENT_COUNT BIGINT
>     CONSTRAINT PK PRIMARY KEY(MTIME ROW_TIMESTAMP,FILENAME,TYPE,SUBTYPE,SENSOR,SIZE,OWNER,GROUP_OWNER,PERMISSIONS))
COMPRESSION='SNAPPY',DEFAULT_COLUMN_FAMILY='F';
>
> and here is an example set of queries to reproduce the issue I am seeing:
>
> 0: jdbc:phoenix:master-1.> UPSERT INTO DEFAULT.FILE_INDEX(MTIME,FILENAME,TYPE,SUBTYPE,SENSOR,SIZE,OWNER,GROUP_OWNER,PERMISSIONS,STARTED)
SELECT MTIME,FILENAME,TYPE,SUBTYPE,SENSOR,SIZE,OWNER,GROUP_OWNER,PERMISSIONS,now() AS STARTED
FROM DEFAULT.FILE_INDEX WHERE FILENAME='hdfs://filename.log';1 row affected (5.041 seconds)0:
jdbc:phoenix:master-1.> select * from default.file_index where started is not null;+--------------------------+-----------------------------------------------------------------------------------------------------------------------------+-------+----------+-----------------+---------+------------+--------------+------+|
         MTIME           |                                                          FILENAME
                                                          | TYPE  | SUBTYPE  |     SENSOR
     |  SIZE   |   OWNER    | GROUP_OWNER  | PERM |+--------------------------+-----------------------------------------------------------------------------------------------------------------------------+-------+----------+-----------------+---------+------------+--------------+------+|
2018-11-01 00:00:00.000  | hdfs://filename.log  | BRO   | DNS      | something  | 224500 
| somebody  | hdfs         | rw-r |+--------------------------+-----------------------------------------------------------------------------------------------------------------------------+-------+----------+-----------------+---------+------------+--------------+------+1
row selected (4.046 seconds)0: jdbc:phoenix:master-1.> UPSERT INTO DEFAULT.FILE_INDEX(MTIME,FILENAME,TYPE,SUBTYPE,SENSOR,SIZE,OWNER,GROUP_OWNER,PERMISSIONS,STARTED)
SELECT MTIME,FILENAME,TYPE,SUBTYPE,SENSOR,SIZE,OWNER,GROUP_OWNER,PERMISSIONS,NULL AS STARTED
FROM DEFAULT.FILE_INDEX WHERE FILENAME='hdfs://filename.log';1 row affected (4.541 seconds)0:
jdbc:phoenix:master-1.> select * from default.file_index where started is not null;+--------+-----------+-------+----------+---------+-------+--------+--------------+--------------+----------+------------+--------------+|
MTIME  | FILENAME  | TYPE  | SUBTYPE  | SENSOR  | SIZE  | OWNER  | GROUP_OWNER  | PERMISSIONS
 | STARTED  | PROCESSED  | EVENT_COUNT  |+--------+-----------+-------+----------+---------+-------+--------+--------------+--------------+----------+------------+--------------++--------+-----------+-------+----------+---------+-------+--------+--------------+--------------+----------+------------+--------------+No
rows selected (4.782 seconds)0: jdbc:phoenix:master-1.> UPSERT INTO DEFAULT.FILE_INDEX(MTIME,FILENAME,TYPE,SUBTYPE,SENSOR,SIZE,OWNER,GROUP_OWNER,PERMISSIONS,STARTED)
SELECT MTIME,FILENAME,TYPE,SUBTYPE,SENSOR,SIZE,OWNER,GROUP_OWNER,PERMISSIONS,now() AS STARTED
FROM DEFAULT.FILE_INDEX WHERE FILENAME='hdfs://filename.log';1 row affected (5.254 seconds)0:
jdbc:phoenix:master-1.> select * from default.file_index where started is not null;+--------+-----------+-------+----------+---------+-------+--------+--------------+--------------+----------+------------+--------------+|
MTIME  | FILENAME  | TYPE  | SUBTYPE  | SENSOR  | SIZE  | OWNER  | GROUP_OWNER  | PERMISSIONS
 | STARTED  | PROCESSED  | EVENT_COUNT  |+--------+-----------+-------+----------+---------+-------+--------+--------------+--------------+----------+------------+--------------++--------+-----------+-------+----------+---------+-------+--------+--------------+--------------+----------+------------+--------------+No
rows selected (4.389 seconds)
>
> I would greatly appreciate any insights into what I might be doing wrong
> here.  My assumption is that this behavior, though undesirable, is expected
> and has to do with versions or perhaps autocommit settings...but for the
> life of me, I have tried every permutation of autocommit settings and have
> not been able to get around this behavior...
>
>
>
>
>
>
>

Mime
View raw message