Apache Phoenix Upsert Into Table Select * from View

I have created a Phoenix View from an HBase table, then created a Phoenix view from it, and finally tried to upsert into a new :

create view "personal" (k VARCHAR primary key, "personal_data"."name" VARCHAR);

select * from "personal";

+--------+-------------------------------+
|   K    |             name              |
+--------+-------------------------------+
| 0      | Darrell Clark                 |
| 1      | Elizabeth Baker               |
| 10     | Brad Alexander                |
| 100    | Douglas Morris                |
| 1000   | Joel Boyd                     |
| 10000  | Christine Wood                |
| 10001  | Thomas Wilson                 |
| 10002  | Laura Salinas                 |
| 10003  | Audrey Norris                 |
| 10004  | Kristen Klein                 |
| 10005  | Vanessa Brooks                |
| 10006  | Mary Flynn                    |
| 10007  | Margaret Mullen               |

I then create a Phoenix table:

create table if not exists personal_table (name VARCHAR, k VARCHAR CONSTRAINT my_pk PRIMARY KEY (name, k));

I then try to Upsert:

UPSERT INTO personal_table SELECT * FROM personal;

Error: ERROR 1012 (42M03): Table undefined. tableName=PERSONAL (state=42M03,code=1012)
org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): Table undefined. tableName=PERSONAL
    at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:582)
    at org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.<init>(FromCompiler.java:396)
    at org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:228)
    at org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:504)
    at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:784)
    at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:770)
    at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:401)
    at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391)
    at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
    at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:390)
    at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)
    at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825)
    at sqlline.Commands.execute(Commands.java:822)
    at sqlline.Commands.sql(Commands.java:732)
    at sqlline.SqlLine.dispatch(SqlLine.java:813)
    at sqlline.SqlLine.begin(SqlLine.java:686)
    at sqlline.SqlLine.start(SqlLine.java:398)
    at sqlline.SqlLine.main(SqlLine.java:291)

What could be the problem? Is it possible to Upsert into table select * from view? Thanks!

Solution

Is it possible to Upsert into table select * from view?

No, it's not the case. Missing quotation marks in upsert statement caused this. "As with CREATE TABLE, the table, column family, and column names are uppercased unless they are double quoted"

UPSERT INTO personal_table SELECT * FROM personal;

should be ->

UPSERT INTO personal_table SELECT * FROM "personal";

I tried your case on my local (with fix). Here is what I executed on HBase Shell:

// create personel table on hbase
create 'personel', {NAME => 'personal_data', VERSIONS => 5}

put 'personel','1','personal_data:name','quaresma'
put 'personel','2','personal_data:name','cenk'
put 'personel','3','personal_data:name','fabri'
put 'personel','4','personal_data:name','pepe'
put 'personel','5','personal_data:name','talisca'

scan 'personel'

Then I switched to the phoenix-sqlline and executed following commands:

CREATE VIEW "personel" (k VARCHAR PRIMARY KEY, "personal_data"."name" VARCHAR);

SELECT * FROM "personel";

create table if not exists personal_table (name VARCHAR, k VARCHAR CONSTRAINT my_pk PRIMARY KEY (name, k));

UPSERT INTO personal_table SELECT * FROM "personel";

select * from personal_table;