Can't get values of columns in phoenix query but can see them through hbase shell.

[#1]

Hi team,

Thanks for your time in advance.

I have an issue bothering me a long time. Can somebody point me the right direction?

I created a hbase instance in my local - no distribution, with phoenix coprocessors, then go to phoenix, run a sample DDL and upsert. For a simply select * from thetable, It shows the ids but null values for all of non-id columns. 

Settings:

1: hbase-2.6.0-hadoop3: I copied phoenix-server-hbase-2.6.jar to hbase_home/lib; then add coprocessors to hbase-site.xml:

  <property>

    <name>hbase.coprocessor.region.classes</name>

    <value>

      org.apache.phoenix.index.GlobalIndexChecker,

      org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver,

      org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver,

      org.apache.phoenix.coprocessor.ScanRegionObserver,

        org.apache.phoenix.coprocessor.ServerCachingEndpointImpl

    </value>

  </property>

2: phoenix-5.3.0: I introduced org.apache.phoenix:phoenix-core:5.3.0-SNAPSHOT to my local project.

3: then a simple DDL, upsert and query, can't see the values as in the resultset.

DDL: CREATE TABLE IF NOT EXISTS A_TEST_1728428788967 (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR);

UPSET: UPSERT INTO A_TEST_1728428788967 (ID, NAME) VALUES ('123', 'A test for a_test_1728428788967');

SELECT: SELECT * FROM A_TEST_1728428788967;

--ID    NAME

--123 null       <<<<<--------Here expecting to see "A test for a_test_1728428788967" but not.

4: I can see the values in hbase shell:

hbase:030:0> scan 'A_TEST_1728428788967';

ROW     COLUMN+CELL                                                                                                                                   

\x80\x000\x19      column=0:\x00\x00\x00\x00, timestamp=2024-10-08T19:06:29.756, value=x    

\x80\x000\x19      column=0:\x80\x0B, timestamp=2024-10-08T19:06:29.756, value=A test for a_test_1728428788967 

5: here is the describe of the table:

hbase:030:0> desc 'A_TEST_1728428788967';

Table A_TEST_1728428788967 is ENABLED                                                                                                

A_TEST_1728428788967, {TABLE_ATTRIBUTES => {coprocessor$1 => '|org.apache.phoenix.coprocessor.ScanRegionObserver|805306366|', coprocessor$2 => '|org.apache.phoenix.coprocessor.UngroupedAggregateRegionObserver|80530

6366|', coprocessor$3 => '|org.apache.phoenix.coprocessor.GroupedAggregateRegionObserver|805306366|', coprocessor$4 => '|org.apache.phoenix.coprocessor.ServerCachingEndpointImpl|805306366|', coprocessor$5 => '|org.

apache.phoenix.hbase.index.IndexRegionObserver|805306366|index.builder=org.apache.phoenix.index.PhoenixIndexBuilder,org.apache.hadoop.hbase.index.codec.class=org.apache.phoenix.index.PhoenixIndexCodec', METADATA =>

{'hbase.store.file-tracker.impl' => 'DEFAULT'}}}                                                                                           

COLUMN FAMILIES DESCRIPTION                                                                                                               

{NAME => '0', INDEX_BLOCK_ENCODING => 'NONE', VERSIONS => '1', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'FAST_DIFF', TTL => 'FOREVER', MIN_VERSIONS => '0', REPLICATION_SCOPE => '0', BLOOMFILTER => 'ROW

', IN_MEMORY => 'false', COMPRESSION => 'NONE', BLOCKCACHE => 'true', BLOCKSIZE => '65536 B (64KB)'}     

Guessing maybe three issues, the coprocessors are not right, or I should not use local hbase instance, or I should downgrade version hbase to 2.5 and phoenix 5.2?

Any suggestion will be appreciated greatly!

[#2]

@manyquestions Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Phoenix experts @jromero @smdas  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

[#3 · Accepted solution]

Hi @manyquestions 

Use the below statement to insert and let us know if you are facing the issue -

UPSERT INTO A_TEST_1728428788967 (ID, NAME) VALUES (123, 'A test for a_test_1728428788967');

Values for integer should not be with quotes.

Was your question answered? Please take some time to click on "Accept as Solution" -- If you find a reply useful, say thanks by clicking on the thumbs up button below this post.

[#4]

@manyquestions Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. Thanks.